SELECT m7.ftcompany_name, n1.ftitem_code,n1.ftpart_no,
CASE WHEN TRIM(IFNULL(n1.ftpart_no,'') != '') THEN CONCAT(n1.ftitem_name,' PART NO: ',IFNULL(n1.ftpart_no,'')) ELSE n1.ftitem_name END AS ftitem_name,
n5.fnwarehouse_type, m6.fttrans_status_name,
m1.fnsaldo_awal, m1.fnmasuk, m1.fnkeluar, m1.fnsaldo_akhir,
m1.fcbuying_price, m1.fctotal_price
FROM tlstock_hdr n1
INNER JOIN (

		SELECT xx.ftitem_code, SUM(xx.fnsaldo_awal) AS fnsaldo_awal,
		SUM(xx.fnmasuk) AS fnmasuk, SUM(xx.fnkeluar) AS fnkeluar,
		SUM(xx.fnsaldo_akhir) AS fnsaldo_akhir, xx.fcbuying_price, SUM(xx.fctotal_price) AS fctotal_price,
		xx.ftwarehouse_code
		FROM (
				SELECT a.ftitem_code, SUM(a.`fnQty`) AS fnsaldo_awal, 0 AS fnmasuk, 0 AS fnkeluar , SUM(a.`fnQty`) AS fnsaldo_akhir,
				a.`fcNet_Price` AS fcbuying_price, SUM(a.`fnQty` * a.`fcNet_Price`) AS fctotal_price, a.ftwarehouse_code
				FROM `txstockfifo` a
				WHERE a.fnstatus <> 9 AND a.`fdTrans_Date` < '2023-06-01' 
				GROUP BY a.`ftitem_code`, a.`fcNet_Price`, a.ftwarehouse_code

				UNION ALL

				SELECT a.ftitem_code, 0 AS fnsaldo_awal, a.`fnQty` AS fnmasuk, 0 AS fnkeluar , a.`fnQty` AS fnsaldo_akhir,
				a.`fcNet_Price` AS fcbuying_price, a.`fnQty` * a.`fcNet_Price` AS fctotal_price, a.ftwarehouse_code
				FROM `txstockfifo` a
				WHERE a.fnstatus <> 9 AND a.`fdTrans_Date` BETWEEN '2023-06-01' AND '2023-06-30'
				AND a.`fnPriority` = 1
				

				UNION ALL

				SELECT a.ftitem_code, 0 AS fnsaldo_awal, 0 AS fnmasuk, ABS(a.`fnQty`) AS fnkeluar , (a.`fnQty`) AS fnsaldo_akhir,
				a.`fcNet_Price` AS fcbuying_price, a.`fnQty` * a.`fcNet_Price` AS fctotal_price, a.ftwarehouse_code
				FROM `txstockfifo` a
				WHERE a.fnstatus <> 9 AND a.`fdTrans_Date` BETWEEN '2023-06-01' AND '2023-06-30'
				AND a.`fnPriority` = 0
				
			) xx
		GROUP BY xx.ftitem_Code	, xx.fcbuying_price, xx.ftwarehouse_code
			
	) m1 ON m1.ftitem_code = n1.ftitem_code
	
INNER JOIN tllocation n2 ON m1.ftwarehouse_code=n2.ftlocation_code
INNER JOIN tlwarehouse n5 ON n5.ftwarehouse_code = n2.ftwarehouse_code
LEFT JOIN tstransaction_status m6 ON fntrans_group_code = 55 AND n5.fnwarehouse_type = m6.fntrans_status_code AND m6.fnactive_status = 1 
CROSS JOIN tscompany_info m7 
WHERE n1.ftstock_category_code='inv'
AND m1.fnsaldo_awal <> 0 OR m1.fnsaldo_akhir <> 0 OR m1.fnmasuk <> 0 OR m1.fnkeluar <> 0
ORDER BY n5.fnwarehouse_type, n1.ftitem_code