Datanory Production

--USE [DMS_EcoSystem]

--GO

--/****** Object:  StoredProcedure [dbo].[sp_rpt_sales_quantity_by_item_report]    Script Date: 28/2/2024 4:48:53 PM ******/

--SET ANSI_NULLS ON

--GO

--SET QUOTED_IDENTIFIER ON

--GO

--ALTER PROCEDURE [dbo].[sp_rpt_sales_quantity_by_item_report]

declare

@start_date datetime ='2024-02-01',

@end_date datetime ='2024-02-26' ,

@sales_type varchar(max) = '',

@item_id varchar(max) = '',

@tenant_id varchar(50) = 'A000003',

@salesman varchar(max) = '',

@branch varchar(max) = '',

@region varchar(max) = '',

@channel varchar(max) = ''

select

@start_date='2024-02-01 00:00:00',

@end_date='2024-02-26 00:00:00',

@sales_type=N'Cash Outlet,Credit Outlet',

@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',

@tenant_id=N'A000003',

@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',

@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',

@region=N'(Empty),AA23C861526E493D9FCBF85FE941F6AB,88AF658A345E4AF78A52CE971DC98B2F,FCBD72CE-3ADA-442E-98F5-53914F406BB5,1C2FBC20C0B345388ADF756B60FB5199,7D9BE394ED344EAAB9240F44644CE7E2',

@channel=N'(Empty),DA00B52E-C4BD-45D1-8C13-1505E282764C,36BF5535-9E81-43B0-889A-5775340171D5'

--AS

--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

create table #branch

(

branch_id varchar (50) collate DATABASE_DEFAULT

)

INSERT into #branch

select * from mcc_fn_splitMVParam(@branch,',')

create table #region

(

region_id varchar (50) collate DATABASE_DEFAULT

)

INSERT into #region

select * from mcc_fn_splitMVParam(@region,',')

INSERT into #region

select '' where exists (select 1 from #region where region_id = '(Empty)')

create table #channel

(

channel_id varchar (50) collate DATABASE_DEFAULT

)

INSERT into #channel

select * from mcc_fn_splitMVParam(@channel,',')

INSERT into #channel

select '' where exists (select 1 from #channel where channel_id = '(Empty)')

create table #salesman

(

salesman_id varchar (50) collate DATABASE_DEFAULT

)

INSERT INTO #salesman 

exec sp_rpt_split_salesman_value @salesman

--select * from mcc_fn_splitMVParam(@salesman,',')

create table #sales_type

(

sales_type varchar (50) collate DATABASE_DEFAULT

)

INSERT INTO #sales_type select * from mcc_fn_splitMVParam(@sales_type,',')

create table #item

(

item_id varchar (50) collate DATABASE_DEFAULT

)

INSERT INTO #item select * from mcc_fn_splitMVParam(@item_id,',')

create table #temp_sales

(

branch_id varchar(50) collate database_default,

branch_code varchar(50) collate database_default,

customer_id varchar(50) collate database_default,

customer_name nvarchar(200) collate database_default,

invoice_no varchar(50) collate database_default,

region varchar(50) collate database_default,

channel varchar(50) collate database_default,

product_category varchar (max) collate database_default,

invoice_date date,

member_cd varchar(50) collate database_default,

first_name nvarchar(200) collate database_default,

amount decimal(18,2),

member_id varchar(50) collate database_default,

customer_type nvarchar(50) collate database_default,

sales_type varchar(50) collate database_default,

item_id varchar(50) collate database_default,

item_code nvarchar(50) collate database_default,

item_desc nvarchar(100) collate database_default,

sub_group_code nvarchar(50) collate database_default,

sub_group_name nvarchar(100) collate database_default,

group_code nvarchar(50) collate database_default,

group_name nvarchar(100) collate database_default,

qty1 decimal(18,4),

qty2 decimal(18,4),

qty3 decimal(18,4),

uom1 nvarchar(50) collate database_default,

uom2 nvarchar(50) collate database_default,

uom3 nvarchar(50) collate database_default,

stock_type varchar(50) collate database_default,

customer_code nvarchar(50) collate database_default

)

insert into #temp_sales

select b.branch_id, b.branch_code, h.customer_id, c.customer_name, h.invoice_no, mr.region_desc, ch.channel_desc,STUFF(

        (SELECT ', ' + pc.product_category_code

         FROM mst_product_category_detail pcd

         INNER JOIN mst_product_category pc ON pcd.product_category_id = pc.product_category_id

         WHERE pcd.product_category_value = i.item_id

		 ORDER BY pc.product_category_code

         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),

        1, 1, ''

    ) AS product_category,convert(varchar(10),h.invoice_date,120) as invoice_date, 

m.member_cd, m.first_name, sum(d.line_total) as amount, m.member_id, c.customer_type,'Sales' as sales_type, 

i.item_id, i.item_code, i.item_desc, sg.sub_group_code, sg.sub_group_name, g.group_code, g.group_name, 

sum(d.web_uom1_qty),sum(d.web_uom2_qty),sum(d.web_uom3_qty),

u1.product_uom_code as uom,

u2.product_uom_code as uom2,

u3.product_uom_code as uom3,

'Good' as stock_type,c.customer_code

from txn_invoice_history_header h

inner join customer c on h.customer_id = c.customer_id

inner join mst_branch b on h.branch_id = b.branch_id

inner join member m on h.member_id = m.member_id

inner join txn_invoice_history_detail d on h.invoice_id = d.invoice_id

inner join product_item i on d.item_id = i.item_id

inner join product_sub_group sg on i.sub_group_id = sg.sub_group_id

inner join product_group g on g.group_id = sg.group_id

left join mst_product_uom u1 on d.uom1_id = u1.product_uom_id

left join mst_product_uom u2 on d.uom2_id = u2.product_uom_id

left join mst_product_uom u3 on d.uom3_id = u3.product_uom_id

left join mst_customer_contact_address cca on c.customer_id = cca. customer_id and cca.address_type = 'Contact'

left join mst_region mr on mr.region_id = cca.region_id

left join channel ch on c.channel_id = ch.channel_id

where  exists (Select 1 from #branch tb where b.branch_id = tb.branch_id)

and convert(varchar(10),h.invoice_date,120) between @start_date and @end_date

and exists (Select 1 from #salesman ts where ts.salesman_id = m.member_id)

and exists (select 1 from #sales_type tst where tst.sales_type = c.customer_type)

and exists (Select 1 from #item ti where ti.item_id = d.item_id)

and exists (select 1 from mst_customer_contact_address ca inner join #region r on isnull(ca.region_id,'(Empty)')=r.region_id where ca.address_type='Contact' and c.customer_id=ca.customer_id)

and exists (select 1 from #channel ch where isnull(ch.channel_id,'(Empty)')=c.channel_id)

and h.tenant_id = @tenant_id

-- filter cancelled record

and h.invoice_status <> 'Cancelled'

group by b.branch_id, b.branch_code, h.customer_id, c.customer_name, h.invoice_no,  mr.region_desc, ch.channel_desc, convert(varchar(10),h.invoice_date,120),

 m.member_cd, m.first_name,  m.member_id, c.customer_type, 

 i.item_id, i.item_code, i.item_desc, sg.sub_group_code,  sg.sub_group_name, g.group_code, g.group_name,  u1.product_uom_code,

u2.product_uom_code,

u3.product_uom_code,c.customer_code, i.item_id

 insert into #temp_sales

select b.branch_id, b.branch_code, h.customer_id, c.customer_name, h.credit_note_no, mr.region_desc, ch.channel_desc, STUFF(

        (SELECT ', ' + pc.product_category_code

         FROM mst_product_category_detail pcd

         INNER JOIN mst_product_category pc ON pcd.product_category_id = pc.product_category_id

         WHERE pcd.product_category_value = i.item_id

         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),

        1, 1, ''

    ) AS product_category,convert(varchar(10),h.credit_note_date,120) as return_date, 

m.member_cd, m.first_name,

sum(d.amount) as amount,

m.member_id, c.customer_type, 'Return' as sales_type,

i.item_id, i.item_code, i.item_desc, sg.sub_group_code, sg.sub_group_name, g.group_code, g.group_name, 

sum(d.uom1_qty),sum(d.uom2_qty),sum(d.uom3_qty),

u1.product_uom_code as uom,

u2.product_uom_code as uom2,

u3.product_uom_code as uom3,

 isnull(co.stock_type,'Good') as stock_type,

c.customer_code

 from txn_credit_note_history_header h

inner join customer c on h.customer_id = c.customer_id

inner join mst_branch b on c.branch_id = b.branch_id

inner join member m on h.member_id = m.member_id

inner join txn_credit_note_history_detail d on h.credit_note_header_id = d.credit_note_id

inner join product_item i on d.item_id = i.item_id

inner join product_sub_group sg on i.sub_group_id = sg.sub_group_id

inner join product_group g on g.group_id = sg.group_id

inner join mst_condition co on d.condition = co.condition_id

left join mst_product_uom u1 on d.uom1_id = u1.product_uom_id

left join mst_product_uom u2 on d.uom2_id = u2.product_uom_id

left join mst_product_uom u3 on d.uom3_id = u3.product_uom_id

left join mst_reason r on d.reason_id = r.reason_id

left join mst_customer_contact_address cca on c.customer_id = cca. customer_id and cca.address_type = 'Contact'

left join mst_region mr on mr.region_id = cca.region_id

left join channel ch on c.channel_id = ch.channel_id

left join warehouse w on d.warehouse_id = w.warehouse_id

where exists (Select 1 from #branch tb where b.branch_id = tb.branch_id)

and convert(varchar(10),h.credit_note_date,120) between @start_date and @end_date

and exists (Select 1 from #salesman ts where ts.salesman_id = m.member_id)

and exists (select 1 from #sales_type tst where tst.sales_type = c.customer_type)

and exists (Select 1 from #item ti where ti.item_id = d.item_id)

and exists (select 1 from mst_customer_contact_address ca inner join #region r on isnull(ca.region_id,'(empty)')=r.region_id where ca.address_type='Contact' and c.customer_id=ca.customer_id)

and exists (select 1 from #channel ch where isnull(ch.channel_id,'(empty)')=c.channel_id)

and h.tenant_id = @tenant_id

-- filter cancelled record

and h.status <> 'Cancelled'

group by b.branch_id, b.branch_code, h.customer_id, c.customer_name, h.credit_note_no, mr.region_desc, ch.channel_desc,convert(varchar(10),h.credit_note_date,120), 

m.member_cd, m.first_name,

m.member_id, c.customer_type, i.item_id, i.item_code, i.item_desc, sg.sub_group_code, sg.sub_group_name, g.group_code, g.group_name,

 u1.product_uom_code,

u2.product_uom_code,

u3.product_uom_code,isnull(co.stock_type,'Good'),c.customer_code, i.item_id

select  branch_id,branch_code, customer_id, customer_name, invoice_no, region, channel, product_category,invoice_date, 

member_cd, first_name,

member_id, group_code, group_name, sub_group_code, sub_group_name, s.item_code, s.item_desc,

sum(case when customer_type ='Cash Outlet' and sales_type ='Sales' then amount else 0.00 end) as cash_sales_amt,

sum(case when customer_type ='Cash Outlet' and sales_type ='Sales' then qty1 else 0.00 end) as cash_sales_qty1,

sum(case when customer_type ='Cash Outlet' and sales_type ='Sales' then qty2 else 0.00 end) as cash_sales_qty2,

sum(case when customer_type ='Cash Outlet' and sales_type ='Sales' then qty3 else 0.00 end) as cash_sales_qty3,

max(u1.product_uom_code) as cash_sales_uom1,

max(u2.product_uom_code) as cash_sales_uom2,

max(u3.product_uom_code) as cash_sales_uom3,

sum(case when customer_type ='Credit Outlet' and sales_type ='Sales' then amount else 0.00 end) as credit_sales_amt,

sum(case when customer_type ='Credit Outlet' and sales_type ='Sales' then qty1 else 0.00 end) as credit_sales_qty1,

sum(case when customer_type ='Credit Outlet' and sales_type ='Sales' then qty2 else 0.00 end) as credit_sales_qty2,

sum(case when customer_type ='Credit Outlet' and sales_type ='Sales' then qty3 else 0.00 end) as credit_sales_qty3,

max(u1.product_uom_code) as credit_sales_uom1,

max(u2.product_uom_code) as credit_sales_uom2,

max(u3.product_uom_code) as credit_sales_uom3,

sum(case when sales_type ='Return' then amount else 0.00 end) * -1 as return_sales,

sum(case when sales_type ='Return' and stock_type ='Good' then qty1 else 0.00 end) * -1 as return_qty1_good,

sum(case when sales_type ='Return' and stock_type ='Good' then qty2 else 0.00 end) * -1 as return_qty2_good,

sum(case when sales_type ='Return' and stock_type ='Good' then qty3 else 0.00 end) * -1 as return_qty3_good,

max(u1.product_uom_code)  as return_uom1_good,

max(u2.product_uom_code)  as return_uom2_good,

max(u3.product_uom_code)  as return_uom3_good,

sum(case when sales_type ='Return' and stock_type ='Bad' then qty1 else 0.00 end) * -1 as return_qty1_bad,

sum(case when sales_type ='Return' and stock_type ='Bad' then qty2 else 0.00 end) * -1 as return_qty2_bad,

sum(case when sales_type ='Return' and stock_type ='Bad' then qty3 else 0.00 end) * -1 as return_qty3_bad,

max(u1.product_uom_code)  as return_uom1_bad,

max(u2.product_uom_code)  as return_uom2_bad,

max(u3.product_uom_code)  as return_uom3_bad

,customer_code

 into _#azharRep

from #temp_sales s

inner join product_item p on s.item_id=p.item_id

left join mst_product_uom u1 on p.stock_uom_id = u1.product_uom_id

left join mst_product_uom u2 on p.alt_uom_id = u2.product_uom_id

left join mst_product_uom u3 on p.oth_uom_id = u3.product_uom_id

group by branch_id,branch_code, customer_id, customer_name, invoice_no, region, channel, product_category, invoice_date, 

member_cd, first_name,

member_id ,group_code, group_name, sub_group_code, sub_group_name, s.item_code, s.item_desc,customer_code

order by invoice_date, branch_code,region,member_cd, invoice_no, customer_code, item_code

--drop table #temp_sales

--drop table #channel

-- drop  table #branch

-- drop table #region

-- drop table #salesman

-- drop table #sales_type

-- drop table #item

SELECT Sum(cash_sales_amt  +credit_sales_amt +return_sales) AS total_quantity_Item_report

FROM _#azharRep;

--1250592.15

--1250592.15