CREATE OR REPLACE VIEW adempiere.JP_QtyReserved_Corp_RV AS
SELECT
  AD_Client_ID
 ,AD_Org_ID
 ,JP_Corporation_ID
 ,M_Product_ID
 ,C_UOM_ID
 ,Discontinued
 ,ProductType
 ,IsStocked
 ,IsBOM
 ,IsDropShip
 ,M_Product_Category_ID
 ,JP_ProductCategoryL1_ID
 ,JP_ProductCategoryL2_ID
 ,QtyReserved
 ,QtyToDeliver
 ,QtyOnHand
 ,QtyOnHand - QtyReserved AS QtyAvailable
 ,QtyOnHand - QtyReserved + QtyToDeliver AS JP_ForecastAvailableQty
 ,CASE WHEN (QtyOnHand - QtyReserved + QtyToDeliver)*-1 >= 0 THEN (QtyOnHand - QtyReserved + QtyToDeliver)*-1 ELSE 0 END AS JP_ShortageQty
 ,CASE WHEN M_Product_Category_ID IS NULL THEN 'NO'
       WHEN ProductType <> 'I' OR IsStocked <> 'Y' THEN 'UN'
       WHEN 
	(QtyOnHand - QtyReserved) >= 0 THEN 'OK' 
       WHEN
	(QtyOnHand - QtyReserved) < 0 AND (QtyOnHand - QtyReserved + QtyToDeliver) >= 0 THEN 'WT'
       WHEN 
	(QtyOnHand - QtyReserved) < 0 AND (QtyOnHand - QtyReserved + QtyToDeliver) < 0 THEN 'PO'
       ELSE
	'NO'
       END AS JP_ReservedStatus
FROM(
SELECT 
  q.AD_Client_ID AS AD_Client_ID
 ,0 AS AD_Org_ID
 ,q.JP_Corporation_ID AS JP_Corporation_ID
 ,q.M_Product_ID AS M_Product_ID
 ,p.C_UOM_ID AS C_UOM_ID
 ,p.Discontinued AS Discontinued
 ,p.ProductType AS ProductType
 ,p.IsStocked AS IsStocked
 ,p.IsBOM AS IsBOM
 ,p.IsDropShip AS IsDropShip
 ,p.M_Product_Category_ID AS M_Product_Category_ID
 ,pc.JP_ProductCategoryL1_ID AS JP_ProductCategoryL1_ID
 ,pl1.JP_ProductCategoryL2_ID AS JP_ProductCategoryL2_ID
 ,q.QtyReserved AS QtyReserved
 ,q.QtyToDeliver AS QtyToDeliver
 ,CASE WHEN s.QtyOnHand IS NULL THEN 0 ELSE s.QtyOnHand END AS QtyOnHand
FROM adempiere.JP_QtyReserved_Corp q
	INNER JOIN adempiere.M_Product p ON (q.M_Product_ID =p.M_Product_ID )
		INNER JOIN adempiere.M_Product_Category pc ON (p.M_Product_Category_ID=pc.M_Product_Category_ID)
		LEFT OUTER JOIN adempiere.JP_ProductCategoryL1 pl1 ON (pc.JP_ProductCategoryL1_ID=pl1.JP_ProductCategoryL1_ID)
	LEFT OUTER JOIN adempiere.JP_StorageOnHand_Corp s on (q.M_Product_ID =s.M_Product_ID AND q.JP_Corporation_ID =s.JP_Corporation_ID) 
) rv
