359 lines
10 KiB
SQL
359 lines
10 KiB
SQL
-- IDEMPIERE-6025 Purchase Credit Memo > Create Lines From not displaying open/deferred matched PO invoice quantities
|
|
SELECT register_migration_script('202402291804_IDEMPIERE-6025.sql') FROM dual;
|
|
|
|
-- Feb 29, 2024, 6:04:33 PM MYT
|
|
UPDATE AD_ViewComponent SET OtherClause='GROUP BY l.QtyOrdered,CASE WHEN l.QtyOrdered=0 THEN 0 ELSE l.QtyEntered/l.QtyOrdered END,
|
|
l.C_UOM_ID, po.VendorProductNo, l.M_Product_ID, l.C_Charge_ID, l.Line, l.C_OrderLine_ID, o.IsSOTrx,
|
|
l.AD_Client_ID, l.AD_Org_ID, l.IsActive,l.c_bpartner_id,l.C_Order_ID, m2.Qty, l.QtyInvoiced
|
|
HAVING (l.QtyOrdered-(CASE WHEN SUM(m.Qty) IS NULL THEN l.QtyInvoiced ELSE SUM(COALESCE(m.Qty,0))+COALESCE(m2.Qty,0) END) <> 0)
|
|
OR (COALESCE(m2.Qty,SUM(COALESCE(m.Qty,0))) <> 0)', FromClause='FROM C_OrderLine l
|
|
JOIN C_Order o ON o.C_Order_ID = l.C_Order_ID
|
|
LEFT JOIN M_Product_PO po ON l.M_Product_ID = po.M_Product_ID AND l.C_BPartner_ID = po.C_BPartner_ID
|
|
LEFT JOIN M_MatchPO m ON l.c_orderline_id = m.C_OrderLine_ID AND m.C_InvoiceLine_ID IS NOT NULL AND COALESCE(m.Reversal_ID,0)=0 AND m.Posted<>''d''
|
|
LEFT JOIN (
|
|
SELECT m2.C_OrderLine_ID, SUM(COALESCE(m2.Qty,0)) AS Qty
|
|
FROM M_MatchPO m2
|
|
WHERE m2.C_InvoiceLine_ID IS NOT NULL
|
|
AND COALESCE(m2.Reversal_ID,0)=0
|
|
AND m2.Posted=''d''
|
|
GROUP BY m2.C_OrderLine_ID
|
|
) m2 ON l.c_orderline_id = m2.C_OrderLine_ID
|
|
LEFT JOIN M_Product p ON l.M_Product_ID = p.M_Product_ID',Updated=TO_TIMESTAMP('2024-02-29 18:04:33','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_ViewComponent_ID=200227
|
|
;
|
|
|
|
-- Feb 29, 2024, 6:05:09 PM MYT
|
|
UPDATE AD_ViewColumn SET ColumnSQL='l.QtyOrdered-(CASE WHEN SUM(m.Qty) IS NULL THEN l.QtyInvoiced ELSE SUM(COALESCE(m.Qty,0))+COALESCE(m2.Qty,0) END)',Updated=TO_TIMESTAMP('2024-02-29 18:05:09','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_ViewColumn_ID=217539
|
|
;
|
|
|
|
-- Feb 29, 2024, 6:06:19 PM MYT
|
|
UPDATE AD_ViewColumn SET ColumnSQL='COALESCE(m2.Qty,SUM(COALESCE(m.Qty,0)))',Updated=TO_TIMESTAMP('2024-02-29 18:06:19','YYYY-MM-DD HH24:MI:SS'),UpdatedBy=100 WHERE AD_ViewColumn_ID=217538
|
|
;
|
|
|
|
-- Feb 29, 2024, 6:07:01 PM MYT
|
|
CREATE OR REPLACE VIEW C_INVOICE_CREATEFROM_V (
|
|
CREDITQTY,
|
|
QTY,
|
|
MULTIPLIER,
|
|
C_UOM_ID,
|
|
M_PRODUCT_ID,
|
|
C_CHARGE_ID,
|
|
VENDORPRODUCTNO,
|
|
LINE,
|
|
C_ORDERLINE_ID,
|
|
M_INOUTLINE_ID,
|
|
M_RMALINE_ID,
|
|
C_BPARTNER_ID,
|
|
C_ORDER_ID,
|
|
M_INOUT_ID,
|
|
M_RMA_ID,
|
|
C_INVOICE_CREATEFROM_V_ID,
|
|
AD_CLIENT_ID,
|
|
AD_ORG_ID,
|
|
ISACTIVE,
|
|
ISSOTRX,
|
|
AD_TABLE_ID
|
|
) AS
|
|
SELECT
|
|
COALESCE(M2.QTY, SUM(COALESCE(M.QTY, 0))) AS CREDITQTY,
|
|
L.QTYORDERED - (
|
|
CASE
|
|
WHEN SUM(M.QTY) IS NULL THEN L.QTYINVOICED
|
|
ELSE SUM(COALESCE(M.QTY, 0)) + COALESCE(M2.QTY, 0)
|
|
END
|
|
) AS QTY,
|
|
CASE
|
|
WHEN L.QTYORDERED = 0 THEN 0
|
|
ELSE L.QTYENTERED / L.QTYORDERED
|
|
END AS MULTIPLIER,
|
|
L.C_UOM_ID AS C_UOM_ID,
|
|
COALESCE(L.M_PRODUCT_ID, 0) AS M_PRODUCT_ID,
|
|
COALESCE(L.C_CHARGE_ID, 0) AS C_CHARGE_ID,
|
|
PO.VENDORPRODUCTNO AS VENDORPRODUCTNO,
|
|
L.LINE AS LINE,
|
|
L.C_ORDERLINE_ID AS C_ORDERLINE_ID,
|
|
0 AS M_INOUTLINE_ID,
|
|
0 AS M_RMALINE_ID,
|
|
L.C_BPARTNER_ID AS C_BPARTNER_ID,
|
|
L.C_ORDER_ID AS C_ORDER_ID,
|
|
0 AS M_INOUT_ID,
|
|
0 AS M_RMA_ID,
|
|
L.C_ORDERLINE_ID AS C_INVOICE_CREATEFROM_V_ID,
|
|
L.AD_CLIENT_ID AS AD_CLIENT_ID,
|
|
L.AD_ORG_ID AS AD_ORG_ID,
|
|
L.ISACTIVE AS ISACTIVE,
|
|
O.ISSOTRX AS ISSOTRX,
|
|
260 AS AD_TABLE_ID
|
|
FROM
|
|
C_ORDERLINE L
|
|
JOIN C_ORDER O ON O.C_ORDER_ID = L.C_ORDER_ID
|
|
LEFT JOIN M_PRODUCT_PO PO ON L.M_PRODUCT_ID = PO.M_PRODUCT_ID
|
|
AND L.C_BPARTNER_ID = PO.C_BPARTNER_ID
|
|
LEFT JOIN M_MATCHPO M ON L.C_ORDERLINE_ID = M.C_ORDERLINE_ID
|
|
AND M.C_INVOICELINE_ID IS NOT NULL
|
|
AND COALESCE(M.REVERSAL_ID, 0) = 0
|
|
AND M.POSTED <> 'd'
|
|
LEFT JOIN (
|
|
SELECT
|
|
M2.C_ORDERLINE_ID,
|
|
SUM(COALESCE(M2.QTY, 0)) AS QTY
|
|
FROM
|
|
M_MATCHPO M2
|
|
WHERE
|
|
M2.C_INVOICELINE_ID IS NOT NULL
|
|
AND COALESCE(M2.REVERSAL_ID, 0) = 0
|
|
AND M2.POSTED = 'd'
|
|
GROUP BY
|
|
M2.C_ORDERLINE_ID
|
|
) M2 ON L.C_ORDERLINE_ID = M2.C_ORDERLINE_ID
|
|
LEFT JOIN M_PRODUCT P ON L.M_PRODUCT_ID = P.M_PRODUCT_ID
|
|
GROUP BY
|
|
L.QTYORDERED,
|
|
CASE
|
|
WHEN L.QTYORDERED = 0 THEN 0
|
|
ELSE L.QTYENTERED / L.QTYORDERED
|
|
END,
|
|
L.C_UOM_ID,
|
|
PO.VENDORPRODUCTNO,
|
|
L.M_PRODUCT_ID,
|
|
L.C_CHARGE_ID,
|
|
L.LINE,
|
|
L.C_ORDERLINE_ID,
|
|
O.ISSOTRX,
|
|
L.AD_CLIENT_ID,
|
|
L.AD_ORG_ID,
|
|
L.ISACTIVE,
|
|
L.C_BPARTNER_ID,
|
|
L.C_ORDER_ID,
|
|
M2.QTY,
|
|
L.QTYINVOICED
|
|
HAVING
|
|
(
|
|
L.QTYORDERED - (
|
|
CASE
|
|
WHEN SUM(M.QTY) IS NULL THEN L.QTYINVOICED
|
|
ELSE SUM(COALESCE(M.QTY, 0)) + COALESCE(M2.QTY, 0)
|
|
END
|
|
) <> 0
|
|
)
|
|
OR (COALESCE(M2.QTY, SUM(COALESCE(M.QTY, 0))) <> 0)
|
|
UNION ALL
|
|
SELECT
|
|
CASE
|
|
WHEN IO.ISSOTRX = 'N' THEN (
|
|
L.MOVEMENTQTY - SUM(COALESCE(MI.QTY, 0)) * CASE
|
|
WHEN IO.MOVEMENTTYPE = 'V-' THEN -1
|
|
ELSE 1
|
|
END
|
|
)
|
|
ELSE (
|
|
L.MOVEMENTQTY - SUM(COALESCE(MI.QTY, 0)) * CASE
|
|
WHEN IO.MOVEMENTTYPE = 'V-' THEN -1
|
|
ELSE 1
|
|
END
|
|
)
|
|
END AS CREDITQTY,
|
|
L.MOVEMENTQTY - SUM(COALESCE(MI.QTY, 0)) * CASE
|
|
WHEN IO.MOVEMENTTYPE = 'V-' THEN -1
|
|
ELSE 1
|
|
END AS QTY,
|
|
L.QTYENTERED / L.MOVEMENTQTY AS MULTIPLIER,
|
|
L.C_UOM_ID AS C_UOM_ID,
|
|
L.M_PRODUCT_ID AS M_PRODUCT_ID,
|
|
L.C_CHARGE_ID AS C_CHARGE_ID,
|
|
PO.VENDORPRODUCTNO AS VENDORPRODUCTNO,
|
|
L.LINE AS LINE,
|
|
L.C_ORDERLINE_ID AS C_ORDERLINE_ID,
|
|
L.M_INOUTLINE_ID AS M_INOUTLINE_ID,
|
|
0 AS M_RMALINE_ID,
|
|
IO.C_BPARTNER_ID AS C_BPARTNER_ID,
|
|
0 AS C_ORDER_ID,
|
|
L.M_INOUT_ID AS M_INOUT_ID,
|
|
0 AS M_RMA_ID,
|
|
L.M_INOUTLINE_ID AS C_INVOICE_CREATEFROM_V_ID,
|
|
L.AD_CLIENT_ID AS AD_CLIENT_ID,
|
|
L.AD_ORG_ID AS AD_ORG_ID,
|
|
L.ISACTIVE AS ISACTIVE,
|
|
IO.ISSOTRX AS ISSOTRX,
|
|
320 AS AD_TABLE_ID
|
|
FROM
|
|
M_INOUTLINE L
|
|
LEFT JOIN M_PRODUCT P ON L.M_PRODUCT_ID = P.M_PRODUCT_ID
|
|
JOIN M_INOUT IO ON L.M_INOUT_ID = IO.M_INOUT_ID
|
|
LEFT JOIN M_PRODUCT_PO PO ON L.M_PRODUCT_ID = PO.M_PRODUCT_ID
|
|
AND IO.C_BPARTNER_ID = PO.C_BPARTNER_ID
|
|
LEFT JOIN M_MATCHINV MI ON L.M_INOUTLINE_ID = MI.M_INOUTLINE_ID
|
|
WHERE
|
|
L.MOVEMENTQTY <> 0
|
|
AND IO.ISSOTRX = 'N'
|
|
GROUP BY
|
|
IO.MOVEMENTTYPE,
|
|
L.MOVEMENTQTY,
|
|
L.QTYENTERED / L.MOVEMENTQTY,
|
|
L.C_UOM_ID,
|
|
PO.VENDORPRODUCTNO,
|
|
L.M_PRODUCT_ID,
|
|
L.C_CHARGE_ID,
|
|
L.LINE,
|
|
L.C_ORDERLINE_ID,
|
|
L.M_INOUTLINE_ID,
|
|
IO.C_BPARTNER_ID,
|
|
L.M_INOUT_ID,
|
|
IO.ISSOTRX,
|
|
L.AD_CLIENT_ID,
|
|
L.AD_ORG_ID,
|
|
L.ISACTIVE
|
|
HAVING
|
|
L.MOVEMENTQTY - SUM(COALESCE(MI.QTY, 0)) <> 0
|
|
UNION ALL
|
|
SELECT
|
|
L.MOVEMENTQTY - SUM(COALESCE(IL.QTYINVOICED, 0)) AS CREDITQTY,
|
|
L.MOVEMENTQTY - SUM(COALESCE(IL.QTYINVOICED, 0)) AS QTY,
|
|
L.QTYENTERED / L.MOVEMENTQTY AS MULTIPLIER,
|
|
L.C_UOM_ID AS C_UOM_ID,
|
|
L.M_PRODUCT_ID AS M_PRODUCT_ID,
|
|
L.C_CHARGE_ID AS C_CHARGE_ID,
|
|
PO.VENDORPRODUCTNO AS VENDORPRODUCTNO,
|
|
L.LINE AS LINE,
|
|
L.C_ORDERLINE_ID AS C_ORDERLINE_ID,
|
|
L.M_INOUTLINE_ID AS M_INOUTLINE_ID,
|
|
0 AS M_RMALINE_ID,
|
|
IO.C_BPARTNER_ID AS C_BPARTNER_ID,
|
|
0 AS C_ORDER_ID,
|
|
L.M_INOUT_ID AS M_INOUT_ID,
|
|
0 AS M_RMA_ID,
|
|
L.M_INOUTLINE_ID AS C_INVOICE_CREATEFROM_V_ID,
|
|
L.AD_CLIENT_ID AS AD_CLIENT_ID,
|
|
L.AD_ORG_ID AS AD_ORG_ID,
|
|
L.ISACTIVE AS ISACTIVE,
|
|
IO.ISSOTRX AS ISSOTRX,
|
|
320 AS AD_TABLE_ID
|
|
FROM
|
|
M_INOUTLINE L
|
|
LEFT JOIN M_PRODUCT P ON L.M_PRODUCT_ID = P.M_PRODUCT_ID
|
|
JOIN M_INOUT IO ON L.M_INOUT_ID = IO.M_INOUT_ID
|
|
LEFT JOIN M_PRODUCT_PO PO ON L.M_PRODUCT_ID = PO.M_PRODUCT_ID
|
|
AND IO.C_BPARTNER_ID = PO.C_BPARTNER_ID
|
|
LEFT JOIN C_INVOICELINE IL ON L.M_INOUTLINE_ID = IL.M_INOUTLINE_ID
|
|
WHERE
|
|
L.MOVEMENTQTY <> 0
|
|
AND IO.ISSOTRX = 'Y'
|
|
GROUP BY
|
|
IO.MOVEMENTTYPE,
|
|
L.MOVEMENTQTY,
|
|
L.QTYENTERED / L.MOVEMENTQTY,
|
|
L.C_UOM_ID,
|
|
PO.VENDORPRODUCTNO,
|
|
L.M_PRODUCT_ID,
|
|
L.C_CHARGE_ID,
|
|
L.LINE,
|
|
L.C_ORDERLINE_ID,
|
|
L.M_INOUTLINE_ID,
|
|
IO.C_BPARTNER_ID,
|
|
L.M_INOUT_ID,
|
|
IO.ISSOTRX,
|
|
L.AD_CLIENT_ID,
|
|
L.AD_ORG_ID,
|
|
L.ISACTIVE
|
|
HAVING
|
|
L.MOVEMENTQTY - SUM(COALESCE(IL.QTYINVOICED, 0)) <> 0
|
|
UNION ALL
|
|
SELECT
|
|
RL.QTY - COALESCE(RL.QTYINVOICED, 0) AS CREDITQTY,
|
|
RL.QTY - COALESCE(RL.QTYINVOICED, 0) AS QTY,
|
|
1 AS MULTIPLIER,
|
|
UOM.C_UOM_ID AS C_UOM_ID,
|
|
P.M_PRODUCT_ID AS M_PRODUCT_ID,
|
|
C.C_CHARGE_ID AS C_CHARGE_ID,
|
|
PO.VENDORPRODUCTNO AS VENDORPRODUCTNO,
|
|
RL.LINE AS LINE,
|
|
0 AS C_ORDERLINE_ID,
|
|
0 AS M_INOUTLINE_ID,
|
|
RL.M_RMALINE_ID AS M_RMALINE_ID,
|
|
R.C_BPARTNER_ID AS C_BPARTNER_ID,
|
|
0 AS C_ORDER_ID,
|
|
0 AS M_INOUT_ID,
|
|
R.M_RMA_ID AS M_RMA_ID,
|
|
RL.M_RMALINE_ID AS C_INVOICE_CREATEFROM_V_ID,
|
|
RL.AD_CLIENT_ID AS AD_CLIENT_ID,
|
|
RL.AD_ORG_ID AS AD_ORG_ID,
|
|
RL.ISACTIVE AS ISACTIVE,
|
|
R.ISSOTRX AS ISSOTRX,
|
|
660 AS AD_TABLE_ID
|
|
FROM
|
|
M_RMALINE RL
|
|
JOIN M_RMA R ON R.M_RMA_ID = RL.M_RMA_ID
|
|
JOIN M_INOUTLINE IOL ON RL.M_INOUTLINE_ID = IOL.M_INOUTLINE_ID
|
|
LEFT JOIN M_PRODUCT P ON P.M_PRODUCT_ID = IOL.M_PRODUCT_ID
|
|
LEFT JOIN C_UOM UOM ON UOM.C_UOM_ID = COALESCE(P.C_UOM_ID, IOL.C_UOM_ID)
|
|
LEFT JOIN C_CHARGE C ON C.C_CHARGE_ID = IOL.C_CHARGE_ID
|
|
LEFT JOIN M_PRODUCT_PO PO ON RL.M_PRODUCT_ID = PO.M_PRODUCT_ID
|
|
AND R.C_BPARTNER_ID = PO.C_BPARTNER_ID
|
|
WHERE
|
|
RL.M_INOUTLINE_ID IS NOT NULL
|
|
UNION ALL
|
|
SELECT
|
|
RL.QTY - COALESCE(RL.QTYINVOICED, 0) AS CREDITQTY,
|
|
RL.QTY - COALESCE(RL.QTYINVOICED, 0) AS QTY,
|
|
1 AS MULTIPLIER,
|
|
UOM.C_UOM_ID AS C_UOM_ID,
|
|
P.M_PRODUCT_ID AS M_PRODUCT_ID,
|
|
0 AS C_CHARGE_ID,
|
|
PO.VENDORPRODUCTNO AS VENDORPRODUCTNO,
|
|
RL.LINE AS LINE,
|
|
0 AS C_ORDERLINE_ID,
|
|
0 AS M_INOUTLINE_ID,
|
|
RL.M_RMALINE_ID AS M_RMALINE_ID,
|
|
R.C_BPARTNER_ID AS C_BPARTNER_ID,
|
|
0 AS C_ORDER_ID,
|
|
0 AS M_INOUT_ID,
|
|
R.M_RMA_ID AS M_RMA_ID,
|
|
RL.M_RMALINE_ID AS C_INVOICE_CREATEFROM_V_ID,
|
|
RL.AD_CLIENT_ID AS AD_CLIENT_ID,
|
|
RL.AD_ORG_ID AS AD_ORG_ID,
|
|
RL.ISACTIVE AS ISACTIVE,
|
|
R.ISSOTRX AS ISSOTRX,
|
|
660 AS AD_TABLE_ID
|
|
FROM
|
|
M_RMALINE RL
|
|
JOIN M_RMA R ON R.M_RMA_ID = RL.M_RMA_ID
|
|
JOIN M_PRODUCT P ON P.M_PRODUCT_ID = RL.M_PRODUCT_ID
|
|
LEFT JOIN C_UOM UOM ON UOM.C_UOM_ID = P.C_UOM_ID
|
|
LEFT JOIN M_PRODUCT_PO PO ON RL.M_PRODUCT_ID = PO.M_PRODUCT_ID
|
|
AND R.C_BPARTNER_ID = PO.C_BPARTNER_ID
|
|
WHERE
|
|
RL.M_PRODUCT_ID IS NOT NULL
|
|
AND RL.M_INOUTLINE_ID IS NULL
|
|
UNION ALL
|
|
SELECT
|
|
RL.QTY - COALESCE(RL.QTYINVOICED, 0) AS CREDITQTY,
|
|
RL.QTY - COALESCE(RL.QTYINVOICED, 0) AS QTY,
|
|
1 AS MULTIPLIER,
|
|
UOM.C_UOM_ID AS C_UOM_ID,
|
|
0 AS M_PRODUCT_ID,
|
|
C.C_CHARGE_ID AS C_CHARGE_ID,
|
|
NULL AS VENDORPRODUCTNO,
|
|
RL.LINE AS LINE,
|
|
0 AS C_ORDERLINE_ID,
|
|
0 AS M_INOUTLINE_ID,
|
|
RL.M_RMALINE_ID AS M_RMALINE_ID,
|
|
R.C_BPARTNER_ID AS C_BPARTNER_ID,
|
|
0 AS C_ORDER_ID,
|
|
0 AS M_INOUT_ID,
|
|
R.M_RMA_ID AS M_RMA_ID,
|
|
RL.M_RMALINE_ID AS C_INVOICE_CREATEFROM_V_ID,
|
|
RL.AD_CLIENT_ID AS AD_CLIENT_ID,
|
|
RL.AD_ORG_ID AS AD_ORG_ID,
|
|
RL.ISACTIVE AS ISACTIVE,
|
|
R.ISSOTRX AS ISSOTRX,
|
|
660 AS AD_TABLE_ID
|
|
FROM
|
|
M_RMALINE RL
|
|
JOIN M_RMA R ON R.M_RMA_ID = RL.M_RMA_ID
|
|
JOIN C_CHARGE C ON C.C_CHARGE_ID = RL.C_CHARGE_ID
|
|
LEFT JOIN C_UOM UOM ON UOM.C_UOM_ID = 100
|
|
WHERE
|
|
RL.C_CHARGE_ID IS NOT NULL
|
|
AND RL.M_INOUTLINE_ID IS NULL
|
|
;
|
|
|