SELECT QNE.*, IWMS.QTY, IWMS."SHOWROOM_QTY", IWMS."PENDING_QTY" FROM
	(SELECT x1.*
	FROM (call JakkerData.QnE.native('SELECT STOCK.STOCKCODE AS "ITEM NO",
		STOCK.BARCODE AS "BARCODE",
		DIYSTOCK.EVE_SKU AS "EVE SKU",
		STOCK.PRICEGROUPCSELLINGPRICE AS "EVE_PRICE",
		STOCK.STOCKNAME AS "NAME",
		STOCK.LISTPRICE AS "LIST PRICE",
		STOCK.PRICEGROUPASELLINGPRICE AS "OFFER PRICE",
		STOCK.UOM AS "UOM",
		STOCK.VOLUMNUOM AS "IC",
		STOCK.WEIGHTUOM AS "OC"
		FROM STOCK LEFT OUTER JOIN DIYSTOCK
		ON STOCK.STOCKCODE = DIYSTOCK.STOCKCODE
		WHERE LOWER(STOCK.INACTIVED) != ''t'';')
	)V1,
	ARRAYTABLE(V1.tuple COLUMNS "ITEM NO" string, "BARCODE" string,
		"EVE SKU" string, "EVE_PRICE" double,
		"NAME" string, "LIST PRICE" double,
		"OFFER PRICE" double, "UOM" string,
		"IC" string, "OC" string) AS x1
	) QNE LEFT OUTER JOIN
	(SELECT x2.*
	FROM (call JakkerData.IWMS.native('SELECT P.ITEM AS "SKU",
		(((CASE WHEN PQOH.QOH IS NULL THEN 0 ELSE PQOH.QOH END) -
		CASE WHEN ORD_PICK.QTY_ALLOC IS NULL THEN 0 ELSE ORD_PICK.QTY_ALLOC END) -
		(CASE WHEN ORD_PICK.QTY_PICK IS NULL THEN 0 ELSE ORD_PICK.QTY_PICK END)) AS "QTY",
		PQOH."SHOWROOM_QTY" AS "SHOWROOM_QTY",
		PQOH."PENDING_QTY" AS "PENDING_QTY"
		FROM PRODUCTS P LEFT OUTER JOIN
		(SELECT VIL.PROD_KEY AS "PROD_KEY",
			SUM(CASE WHEN UPPER(VIL.LOCATION) NOT IN (''SHOWROOM'', ''PENDING'') THEN VIL.QOH ELSE 0 END) AS "QOH",
			SUM(CASE WHEN VIL.LOCATION = ''SHOWROOM'' THEN VIL.QOH ELSE 0 END) AS "SHOWROOM_QTY",
			SUM(CASE WHEN VIL.LOCATION = ''PENDING'' THEN VIL.QOH ELSE 0 END) AS "PENDING_QTY"
			FROM V_INV_LIC VIL
			LEFT OUTER JOIN LOCATIONS LOC
			ON VIL."LOCATION" = LOC."LOCATION"
			WHERE VIL.STATUS = 0
			GROUP BY VIL.PROD_KEY
		) PQOH ON PQOH.PROD_KEY = P.PROD_KEY
		LEFT OUTER JOIN
		(SELECT PROD_KEY,
			(SUM(QTY_ALLOC) - SUM(QTY_PICK)) AS "QTY_ALLOC",
			(SUM(QTY_PICK) - SUM(QTY_SHIP)) AS "QTY_PICK"
			FROM V_ORD_DETL
			WHERE UPPER("STATUS") NOT IN (''SHIP'', ''CAN'')
			GROUP BY PROD_KEY) ORD_PICK
		ON ORD_PICK.PROD_KEY = P.PROD_KEY')
	)V2,
	ARRAYTABLE(V2.tuple COLUMNS "SKU" string,
		"QTY" bigdecimal, "SHOWROOM_QTY" bigdecimal,
		"PENDING_QTY" bigdecimal) AS x2
	) IWMS ON QNE."ITEM NO" = IWMS.SKU