SELECT
tm.INVCODE,
tm.BATCH,
tm.AuxQty - NVL(pe.Qty, 0) AS Aqty
FROM
(SELECT
INVCODE,
BATCH,
SUM(QTY) AS AuxQty
FROM
TAB_INV_STOCK inv
GROUP BY
INVCODE,
BATCH,
ID) tm LEFT OUTER JOIN (SELECT
poe.INVCODE,
poe.BATCH,
SUM(poe.QTY - poe.CHECKQTY) AS Qty
FROM
TAB_BILL_PLANOUTENTRY poe INNER JOIN TAB_BILL_PLANOUT po ON po.ID = poe.MASID
WHERE
po.ISAUDIT = 'true' AND po.ISCLOSE = 'false'
GROUP BY
poe.INVCODE,
poe.BATCH) pe ON pe.INVCODE = tm.INVCODE AND pe.BATCH = tm.BATCH
tm.INVCODE,
tm.BATCH,
tm.AuxQty - NVL(pe.Qty, 0) AS Aqty
FROM
(SELECT
INVCODE,
BATCH,
SUM(QTY) AS AuxQty
FROM
TAB_INV_STOCK inv
GROUP BY
INVCODE,
BATCH,
ID) tm LEFT OUTER JOIN (SELECT
poe.INVCODE,
poe.BATCH,
SUM(poe.QTY - poe.CHECKQTY) AS Qty
FROM
TAB_BILL_PLANOUTENTRY poe INNER JOIN TAB_BILL_PLANOUT po ON po.ID = poe.MASID
WHERE
po.ISAUDIT = 'true' AND po.ISCLOSE = 'false'
GROUP BY
poe.INVCODE,
poe.BATCH) pe ON pe.INVCODE = tm.INVCODE AND pe.BATCH = tm.BATCH
Creating a View with Constraints: Example The following statement creates a
restricted view of the sample table hr.employees and defines a unique constraint on
the email view column and a primary key constraint for the view on the emp_id
view column:
CREATE VIEW emp_sal (emp_id, last_name,
email UNIQUE RELY DISABLE NOVALIDATE,
CONSTRAINT id_pk PRIMARY KEY (emp_id) RELY DISABLE NOVALIDATE)
AS SELECT employee_id, last_name, email FROM employees;