SUM and JOIN 2 columns with different tables but do not SUM Qty

Good day!

Need help to SUM and JOIN 2 columns with different tables but do no want to SUM qty and duplicates row result

here's the sample data tables;

 

Transfer Shipment table;

TransTO - TransFROM -  ItemNo   -   Desc   -   Desc2   -   VariantCode   -   Qty

Location1      HO                Item01         Active      Black         Variant01             5           <------ Delivery

Location1      HO                 Item01         Active      Black        Variant01             5           <------ Delivery

HO                Location1       Item01         Active     Black        Variant01             2          <------ Pull-out

HO                Location1       Item01         Active      Black       Variant01             1            <------ Pull-out

 

Sales Entry table;

Loc Code   -   Item No   -   Variant Code   -   Qty

Location1       Item01          Variant01            -1       <------ PSales

Location1       Item01          Variant01            -1       <------ PSales

 

Item Entry table;

Loc Code   -   Item No   -   Desc   -   Desc2- VariantCode-   Qty - EntryType  - DocNo

Location1       Item01         Active     Black         Variant01      4         2           ItemAdj0001   <------ PAdj

Location1       Item01         Active     Black         Variant01      1          2           StrAdj0001       <------ PAdj

Location1       Item01         Active     Black         Variant01     -1          1           MSales0001     <------ MSales

Location1       Item01         Active     Black         Variant01     -1           1           MSales0002     <------ MSales

Location1       Item01         Active     Black         Variant01     -1          3           LocAdj0005      <------ NAdj

Location1       Item01         Active     Black         Variant01     -1          3           BtqAdj0008      <------ NAdj

 

SUM and JOIN (the sql query result should be like this)

Loc Code  - Item No - Desc  - Desc2 -VarCode- Delivery -PAdj- Pull-out - PSales - MSales - NAdj  -  Total

Location1    Item01   Active    Black   Variant01     10            5           3             -2             -2         -2            6

 

here's my sql query code;

WITH SumarisedTransfers AS
	(
		SELECT
		[BBI$Transfer Shipment Line].[Transfer-to Code] AS [Location]
		,[BBI$Transfer Shipment Line].[Item No_] AS [Item No]
		,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]
		,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description 2]
		,[BBI$Transfer Shipment Line].[Variant Code]
		,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Delivery]
		,MAX([BBI$Transfer Shipment Line].[Unit of Measure Code]) AS [Unit]
		,MAX([BBI$Transfer Shipment Line].[Division]) AS [Brand] 
		,MAX([BBI$Transfer Shipment Line].[Item Category Code]) AS [Item Cat]
		,MAX([BBI$Transfer Shipment Line].[Product Group Code]) AS [Product Group]
		FROM 
		[BBI$Transfer Shipment Line]
		GROUP BY
		[BBI$Transfer Shipment Line].[Transfer-to Code]
		,[BBI$Transfer Shipment Line].[Item No_]
		,[BBI$Transfer Shipment Line].[Variant Code]
	),
	Transfersfrom AS
		(
		SELECT
		[BBI$Transfer Shipment Line].[Transfer-from Code] AS [Locationfrom]
		,[BBI$Transfer Shipment Line].[Item No_]
		,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]
		,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description 2]
		,[BBI$Transfer Shipment Line].[Variant Code]
		,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Pull-out]
		,MAX([BBI$Transfer Shipment Line].[Unit of Measure Code]) AS [Unit]
		,MAX([BBI$Transfer Shipment Line].[Division]) AS [Brand] 
		,MAX([BBI$Transfer Shipment Line].[Item Category Code]) AS [Item Cat]
		,MAX([BBI$Transfer Shipment Line].[Product Group Code]) AS [Product Group]
		FROM 
		[BBI$Transfer Shipment Line]
		GROUP BY
		[BBI$Transfer Shipment Line].[Transfer-from Code]
		,[BBI$Transfer Shipment Line].[Item No_]
		,[BBI$Transfer Shipment Line].[Variant Code]
	),
	SumarisedSales AS
	(
		SELECT
		[BBI$Trans_ Sales Entry].[Store No_]
		,[BBI$Trans_ Sales Entry].[Item No_]
		,[BBI$Trans_ Sales Entry].[Variant Code]
		,SUM([BBI$Trans_ Sales Entry].[Quantity]) AS [Sales Entry]
		FROM
		[BBI$Trans_ Sales Entry]
		GROUP BY
		[BBI$Trans_ Sales Entry].[Store No_]
		,[BBI$Trans_ Sales Entry].[Item No_]
		,[BBI$Trans_ Sales Entry].[Variant Code]
	),
	Location AS
    (
		SELECT
		[BBI$Location].Name AS [Name]
		FROM [BBI$Location]
	),
	ItemLedgerMSales AS
	(
		SELECT
		[BBI$Item Ledger Entry].[Location Code]
		,[BBI$Item Ledger Entry].[Item No_]
		,[BBI$Item Ledger Entry].[Variant Code]
		,SUM([BBI$Item Ledger Entry].[Quantity]) AS [MSales]
		FROM
		[BBI$Item Ledger Entry]
		GROUP BY
		[BBI$Item Ledger Entry].[Location Code]
		,[BBI$Item Ledger Entry].[Item No_]
		,[BBI$Item Ledger Entry].[Variant Code]
	),
		ItemLedgerPAdj AS
	(
		SELECT
		[BBI$Item Ledger Entry].[Location Code]
		,[BBI$Item Ledger Entry].[Item No_]
		,[BBI$Item Ledger Entry].[Variant Code]
		,SUM([BBI$Item Ledger Entry].[Quantity]) AS [PAdj]
		FROM
		[BBI$Item Ledger Entry]
		GROUP BY
		[BBI$Item Ledger Entry].[Location Code]
		,[BBI$Item Ledger Entry].[Item No_]
		,[BBI$Item Ledger Entry].[Variant Code]
	)

SELECT	 ST.[Location],
		 ST.[Item No],
		 ST.[Description],
		 ST.[Description 2],
		ST.[Variant Code],
		ST.[Delivery],
		ISNULL(PADJ.[Quantity],0) AS [PAdj],
		ISNULL(TF.[Pull-out],0) AS [Pull-out],
		ISNULL(SS.[Sales Entry],0) AS [PSales],
		ISNULL(MS.[Quantity],0) AS [MSales],
		ST.[Delivery]+ISNULL(PADJ.[Quantity],0)-ISNULL(TF.[Pull-out],0)+ISNULL(SS.[Sales Entry],0)+ISNULL(MS.[Quantity],0) AS Total 

FROM [SumarisedTransfers] ST

LEFT JOIN [Transfersfrom] TF
ON [ST].[Location] = [TF].[Locationfrom]
AND [ST].[Item No] = [TF].[Item No_]
AND [ST].[Variant Code] = [TF].[Variant Code]

LEFT JOIN [SumarisedSales] SS
ON [SS].[Store No_] = [ST].[Location]
AND [SS].[Item No_] = [ST].[Item No]
AND [SS].[Variant Code] = [ST].[Variant Code]

LEFT JOIN [BBI$Item Ledger Entry] MS
ON [ST].[Location] = [MS].[Location Code]
AND [ST].[Item No] = [MS].[Item No_]
AND [ST].[Variant Code] = [MS].[Variant Code]

LEFT JOIN [BBI$Item Ledger Entry] PADJ
ON [ST].[Location] = [PADJ].[Location Code]
AND [ST].[Item No] = [PADJ].[Item No_]
AND [ST].[Variant Code] = [PADJ].[Variant Code]

WHERE
ST.[Location]='LOCATION1' AND
ST.[Item No]='ITEM01' AND
ST.[Variant Code]='VARIANT01' AND
MS.[Entry Type]='1' AND
MS.[Document No_] LIKE 'MSales%' AND
PADJ.[Entry Type]='2'

but the sql query did not sum qty of PAdj column and shows duplicate result;

LocCode -ItemNo -Desc   - Desc2  -VarCode   -Delivery - PAdj -Pull-out- PSales - MSales - NAdj -Total

Location1  Item01   Active   Black   Variant01     10           4             3         -2          -2         -2         5

Location1  Item01   Active   Black   Variant01     10           1             3         -2          -2         -2         2

 

Thank you!