-----NEW ORDER STOCK CHECK 25-03-2025 V2
SELECT
INVENTORY_PART_API.Get_Accounting_Group('AQ',X.COMPONENT_PART)ACCOUNT_GROUP,
ACCOUNTING_GROUP_API.Get_Description(INVENTORY_PART_API.Get_Accounting_Group('AQ',X.COMPONENT_PART))ACCOUNT_GROUP_DESCRIPTION,
INVENTORY_PART_API.Get_Part_Product_Family('AQ',X.COMPONENT_PART)PRODUCT_FAMILY,
CODE_C_API.Get_Description('AQ',INVENTORY_PART_API.Get_Part_Product_Family('AQ',X.COMPONENT_PART))PRODUCT_FAMILY_DESCRIPTION,
X.COMPONENT_PART,
INVENTORY_PART_api.Get_Description('AQ',X.COMPONENT_PART)DESCRIPTION ,
X.REQ_TOTAL_QTY,
X.AVAL_TOTAL_QTY_,
X.TOTAL_PO_QTY_,
--((X.AVAL_TOTAL_QTY_ + TOTAL_PO_QTY_)-(REQ_TOTAL_QTY))DIF,
CASE
WHEN
((X.AVAL_TOTAL_QTY_ + TOTAL_PO_QTY_)-(REQ_TOTAL_QTY))< 0 THEN
((X.AVAL_TOTAL_QTY_ + TOTAL_PO_QTY_)-(REQ_TOTAL_QTY))
when
((X.AVAL_TOTAL_QTY_ + TOTAL_PO_QTY_)-(REQ_TOTAL_QTY))> 0 then
0
end as shortage_qty
FROM
(
SELECT
--L.FG_PART,
--L.AC_GROUP ACCOUNT_GROUP,
--L.AC_GROUP_DESCRIPTION ACCOUNT_GROUP_DESCRIPTION,
--L.PF_CODE PRODUCT_FAMILY,
--L.PF_DESCRIPTION PRODUCT_FAMILY_DESCRIPTION,
L.COMPONENT_PART,
--L.DESCRIPTION,
SUM((L.QTY_PER_ASSEMBLY * M.BUY_QTY_DUE))REQ_TOTAL_QTY ,
CASE
WHEN
N.AVAL_TOTAL_QTY IS NULL THEN
0
ELSE
N.AVAL_TOTAL_QTY END AS AVAL_TOTAL_QTY_,
CASE
WHEN
P.total_po_qty IS NULL THEN
0
ELSE
P.total_po_qty END AS total_po_qty_
--N.DEPATMENT
FROM
(
SELECT CATALOG_NO , BUY_QTY_DUE FROM CUSTOMER_ORDER_LINE
WHERE
ORDER_NO LIKE '%&[-CS-L]ORDER_NO%')M
LEFT JOIN
(SELECT
PART_NO FG_PART,
LINE_ITEM_NO, QTY_PER_ASSEMBLY , COMPONENT_PART
--INVENTORY_PART_api.Get_Description('AQ',COMPONENT_PART)DESCRIPTION ,
--INVENTORY_PART_API.Get_Accounting_Group('AQ',COMPONENT_PART)AC_GROUP,
--ACCOUNTING_GROUP_API.Get_Description(INVENTORY_PART_API.Get_Accounting_Group('AQ',COMPONENT_PART))AC_GROUP_DESCRIPTION,
--INVENTORY_PART_API.Get_Part_Product_Family('AQ',COMPONENT_PART)PF_CODE,
--CODE_C_API.Get_Description('AQ',INVENTORY_PART_API.Get_Part_Product_Family('AQ',COMPONENT_PART))PF_DESCRIPTION
FROM PROD_STRUCTURE
WHERE
ALTERNATIVE_NO='*')L
ON
L.FG_PART = M.CATALOG_NO
LEFT JOIN
(SELECT PART_NO , SUM ((QTY_ONHAND - QTY_RESERVED)) AVAL_TOTAL_QTY
--SUBSTR(LOCATION_NO,-2)DEPATMENT
FROM INVENTORY_PART_IN_STOCK_UIV
where
QTY_ONHAND<>0
GROUP BY PART_NO )N
ON
L.COMPONENT_PART = N.PART_NO
LEFT JOIN
(select a.part_no , sum (BUY_QTY_DUE) total_po_qty from purchase_order_line a
where
a.state ='Released'
group by a.part_no )P
ON
P.part_no=L.COMPONENT_PART
--WHERE
--L.AC_GROUP NOT IN ('FG')
GROUP BY L.COMPONENT_PART ,N.AVAL_TOTAL_QTY ,P.total_po_qty
--ORDER BY L.AC_GROUP ,L.PF_CODE,L.DESCRIPTION
)X
--WHERE
--INVENTORY_PART_API.Get_Accounting_Group('AQ',X.COMPONENT_PART) NOT IN ('SF')
ORDER BY
INVENTORY_PART_API.Get_Accounting_Group('AQ',X.COMPONENT_PART) ,
INVENTORY_PART_API.Get_Part_Product_Family('AQ',X.COMPONENT_PART),
INVENTORY_PART_api.Get_Description('AQ',X.COMPONENT_PART)