Fixes Issues 22532, 22537. Checks before inserting a record in
authorDavid Miguelez <david.miguelez@openbravo.com>
Wed, 12 Dec 2012 19:26:20 +0100
changeset 19010 888d6b7e865a
parent 19009 4e1a6f5afaab
child 19011 0e0ca6cb8c45
Fixes Issues 22532, 22537. Checks before inserting a record in
M_MatchPO if there is an existing one. Checks in query if the
document status of the related M_InoutLine is Draft.
src-db/database/model/functions/M_INOUT_POST.xml
src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending_data.xsql
--- a/src-db/database/model/functions/M_INOUT_POST.xml	Wed Dec 12 12:43:13 2012 +0100
+++ b/src-db/database/model/functions/M_INOUT_POST.xml	Wed Dec 12 19:26:20 2012 +0100
@@ -522,29 +522,44 @@
                   AND sl.M_InOut_ID=Cur_InOut.M_InOut_ID
                 )
               LOOP
-                Ad_Sequence_Next('M_MatchPO', Cur_SLines.AD_Org_ID, v_MatchPO_ID) ;
-                -- The min qty. Modified by Ismael Ciordia
-                v_Qty:=Cur_SLines.MovementQty;
-                --IF (ABS(Cur_SLines.MovementQty) > ABS(Cur_SLines.QtyOrdered)) THEN
-                -- v_Qty := Cur_SLines.QtyOrdered;
-                --END IF;
-                v_ResultStr:='InsertMatchPO ' || v_MatchPO_ID;
-                INSERT
-                INTO M_MATCHPO
-                  (
-                    M_MatchPO_ID, AD_Client_ID, AD_Org_ID, IsActive,
-                    Created, CreatedBy, Updated, UpdatedBy,
-                    M_InOutLine_ID, C_OrderLine_ID, M_Product_ID, DateTrx,
-                    Qty, Processing, Processed, Posted
-                  )
-                  VALUES
-                  (
-                    v_MatchPO_ID, Cur_SLines.AD_Client_ID, Cur_SLines.AD_Org_ID, 'Y',
-                    now(), v_User, now(), v_User,
-                    Cur_SLines.M_InOutLine_ID, Cur_SLines.C_OrderLine_ID, Cur_SLines.M_Product_ID, now(),
-                    v_Qty, 'N', 'Y', 'N'
-                  )
-                  ;
+              
+								SELECT M_MATCHPO_ID
+								INTO v_MatchPO_ID
+								FROM M_MATCHPO
+								WHERE C_ORDERLINE_ID = Cur_SLines.C_OrderLine_ID
+								  AND M_INOUTLINE_ID = Cur_SLines.M_InOutLine_ID;             
+
+								v_Qty:=Cur_SLines.MovementQty;
+                	--IF (ABS(Cur_SLines.MovementQty) > ABS(Cur_SLines.QtyOrdered)) THEN
+                	-- v_Qty := Cur_SLines.QtyOrdered;
+                	--END IF;
+
+								IF (v_MatchPO_ID IS NULL) THEN
+                	Ad_Sequence_Next('M_MatchPO', Cur_SLines.AD_Org_ID, v_MatchPO_ID) ;
+                	-- The min qty. Modified by Ismael Ciordia
+                	v_ResultStr:='InsertMatchPO ' || v_MatchPO_ID;
+                	INSERT
+                	INTO M_MATCHPO
+                  	(
+                    	M_MatchPO_ID, AD_Client_ID, AD_Org_ID, IsActive,
+                    	Created, CreatedBy, Updated, UpdatedBy,
+                    	M_InOutLine_ID, C_OrderLine_ID, M_Product_ID, DateTrx,
+                    	Qty, Processing, Processed, Posted
+                  	)
+                  	VALUES
+                  	(
+                    	v_MatchPO_ID, Cur_SLines.AD_Client_ID, Cur_SLines.AD_Org_ID, 'Y',
+                    	now(), v_User, now(), v_User,
+                    	Cur_SLines.M_InOutLine_ID, Cur_SLines.C_OrderLine_ID, Cur_SLines.M_Product_ID, now(),
+                    	v_Qty, 'N', 'Y', 'N'
+                  	)
+                  	;
+                 ELSE
+                	v_ResultStr:='UpdateMatchPO ' || v_MatchPO_ID;
+                	UPDATE M_MATCHPO
+                	SET DateTrx = now(), Qty =v_Qty, Processing = 'N', Processed = 'Y', Posted='N'
+                	WHERE M_MATCHPO_ID = v_MatchPO_ID;
+								 END IF;
               END LOOP;
               v_ResultStr:='MatchInv';
               FOR Cur_ILines IN
--- a/src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending_data.xsql	Wed Dec 12 12:43:13 2012 +0100
+++ b/src/org/openbravo/erpCommon/ad_forms/MaterialReceiptPending_data.xsql	Wed Dec 12 19:26:20 2012 +0100
@@ -66,7 +66,9 @@
       C_ORDERLINE.QTYORDERED-SUM(COALESCE(M_MATCHPO.QTY,0)) AS QTYORDERED, '-1' AS ISACTIVE, C_ORDERLINE.LINE
       FROM C_ORDERLINE left join M_MATCHPO on C_ORDERLINE.C_ORDERLINE_ID = M_MATCHPO.C_ORDERLINE_ID
                                            and M_MATCHPO.M_INOUTLINE_ID IS NOT NULL
-                       left join M_ATTRIBUTESETINSTANCE on C_ORDERLINE.M_ATTRIBUTESETINSTANCE_ID = M_ATTRIBUTESETINSTANCE.M_ATTRIBUTESETINSTANCE_ID,
+                       left join M_ATTRIBUTESETINSTANCE on C_ORDERLINE.M_ATTRIBUTESETINSTANCE_ID = M_ATTRIBUTESETINSTANCE.M_ATTRIBUTESETINSTANCE_ID
+                       left join m_inoutline on m_inoutline.m_inoutline_id =M_MATCHPO.M_INOUTLINE_ID
+                       left join m_inout on m_inoutline.m_inout_id=m_inout.m_inout_id,
       C_ORDER,   C_BPARTNER, C_DOCTYPE
       WHERE C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
       AND C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
@@ -76,6 +78,7 @@
       AND C_ORDER.DOCSTATUS = 'CO'
       AND C_ORDER.C_DOCTYPE_ID = C_DOCTYPE.C_DOCTYPE_ID
       AND C_DOCTYPE.ISRETURN = 'N'
+      and m_inout.docstatus<>'DR'
       GROUP BY C_ORDERLINE.C_ORDERLINE_ID, C_ORDER.C_ORDER_ID, C_ORDER.DOCUMENTNO, C_ORDER.DATEORDERED, C_BPARTNER.C_BPARTNER_ID,
       C_BPARTNER.NAME, C_ORDERLINE.M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE.DESCRIPTION, C_ORDERLINE.QTYORDERED, C_ORDERLINE.LINE
       ) A