details: https://code.openbravo.com/erp/devel/pi/rev/e7c3d311418a changeset: 18539:e7c3d311418a user: Pandeeswari Ramakrishnan <pandeeswari.ramakrishnan <at> openbravo.com> date: Tue Oct 16 17:24:07 2012 +0200 summary: Fixes Issue 19043:Inserting lines more than once is possible in goods receipt
details: https://code.openbravo.com/erp/devel/pi/rev/a7d41be10b1b changeset: 18540:a7d41be10b1b user: Pandeeswari Ramakrishnan <pandeeswari.ramakrishnan <at> openbravo.com> date: Tue Oct 16 13:13:02 2012 +0200 summary: Fixes Issue 21933 : Cascade on delete options for m_inoutline_id in m_matchpo diffstat: src-db/database/model/tables/M_MATCHPO.xml | 2 +- src-db/database/model/triggers/M_INOUTLINE_TRG.xml | 13 + src/org/openbravo/erpCommon/ad_actionButton/CreateFrom.java | 67 +++++---- src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql | 38 +++++- src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Shipment_data.xsql | 55 ++++++++ 5 files changed, 140 insertions(+), 35 deletions(-) diffs (truncated from 632 to 300 lines): diff -r 988b9a459b02 -r a7d41be10b1b src-db/database/model/tables/M_MATCHPO.xml --- a/src-db/database/model/tables/M_MATCHPO.xml Tue Nov 13 15:01:21 2012 +0100 +++ b/src-db/database/model/tables/M_MATCHPO.xml Tue Oct 16 13:13:02 2012 +0200 @@ -81,7 +81,7 @@ <foreign-key foreignTable="C_ORDERLINE" name="M_MATCHPO_C_ORDERLINE"> <reference local="C_ORDERLINE_ID" foreign="C_ORDERLINE_ID"/> </foreign-key> - <foreign-key foreignTable="M_INOUTLINE" name="M_MATCHPO_M_INOUTLINE"> + <foreign-key foreignTable="M_INOUTLINE" name="M_MATCHPO_M_INOUTLINE" onDelete="cascade"> <reference local="M_INOUTLINE_ID" foreign="M_INOUTLINE_ID"/> </foreign-key> <foreign-key foreignTable="M_PRODUCT" name="M_MATCHPO_M_PRODUCT"> diff -r 988b9a459b02 -r a7d41be10b1b src-db/database/model/triggers/M_INOUTLINE_TRG.xml --- a/src-db/database/model/triggers/M_INOUTLINE_TRG.xml Tue Nov 13 15:01:21 2012 +0100 +++ b/src-db/database/model/triggers/M_INOUTLINE_TRG.xml Tue Oct 16 13:13:02 2012 +0200 @@ -14,6 +14,7 @@ v_qtyold NUMBER; v_qtyorderold NUMBER; v_MatchInvCount NUMBER; + v_MatchPoCount NUMBER; v_STOCKED NUMBER; v_UOM_ID varchar2(32); v_AttrSetValueType M_Product.AttrSetValueType%TYPE; @@ -132,6 +133,18 @@ AND M_INOUTLINE_ID = :NEW.M_INOUTLINE_ID; END IF; + SELECT COUNT(*) INTO v_MatchPoCount + FROM M_MatchPo MO + WHERE MO.Qty = :OLD.MovementQty + AND MO.M_InOutLine_ID = :NEW.M_InOutLine_ID; + + IF (:OLD.MovementQty <> :NEW.MovementQty AND v_MatchPoCount = 1) THEN + UPDATE M_MatchPo + SET Qty = :NEW.MovementQty + WHERE Qty = :OLD.MovementQty + AND M_InOutLine_ID = :NEW.M_InOutLine_ID; + END IF; + END IF; diff -r 988b9a459b02 -r a7d41be10b1b src/org/openbravo/erpCommon/ad_actionButton/CreateFrom.java --- a/src/org/openbravo/erpCommon/ad_actionButton/CreateFrom.java Tue Nov 13 15:01:21 2012 +0100 +++ b/src/org/openbravo/erpCommon/ad_actionButton/CreateFrom.java Tue Oct 16 13:13:02 2012 +0200 @@ -487,7 +487,7 @@ if (isSOTrx.equals("Y")) data = CreateFromInvoiceData.selectFromPOSOTrx(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), - Utility.getContext(this, vars, "#User_Org", strWindowId), strPO); + Utility.getContext(this, vars, "#User_Org", strWindowId), strPO, strKey); else data = CreateFromInvoiceData.selectFromPO(this, vars.getLanguage(), strKey, Utility.getContext(this, vars, "#User_Client", strWindowId), @@ -496,7 +496,7 @@ if (isSOTrx.equals("Y")) data = CreateFromInvoiceData.selectFromPOTrlSOTrx(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), - Utility.getContext(this, vars, "#User_Org", strWindowId), strPO); + Utility.getContext(this, vars, "#User_Org", strWindowId), strPO, strKey); else data = CreateFromInvoiceData.selectFromPOTrl(this, vars.getLanguage(), strKey, Utility.getContext(this, vars, "#User_Client", strWindowId), @@ -508,23 +508,23 @@ data = CreateFromInvoiceData.selectFromShipmentSOTrx(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), Utility.getContext(this, vars, "#User_Org", strWindowId), strShipment, - strIsTaxIncluded); + strIsTaxIncluded, strKey); else data = CreateFromInvoiceData.selectFromShipment(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), Utility.getContext(this, vars, "#User_Org", strWindowId), strShipment, - strIsTaxIncluded); + strIsTaxIncluded, strKey); } else { if (isSOTrx.equals("Y")) data = CreateFromInvoiceData.selectFromShipmentTrlSOTrx(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), Utility.getContext(this, vars, "#User_Org", strWindowId), strShipment, - strIsTaxIncluded); + strIsTaxIncluded, strKey); else data = CreateFromInvoiceData.selectFromShipmentTrl(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), Utility.getContext(this, vars, "#User_Org", strWindowId), strShipment, - strIsTaxIncluded); + strIsTaxIncluded, strKey); } } } @@ -560,20 +560,20 @@ xmlDocument.setData("reportShipmentReciept", "liststructure", CreateFromInvoiceData .selectFromShipmentSOTrxCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), narturalOrgTreeList, - strBPartner, strIsTaxIncluded)); + strBPartner, strIsTaxIncluded, strKey)); xmlDocument.setData("reportPurchaseOrder", "liststructure", CreateFromInvoiceData .selectFromPOSOTrxCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), narturalOrgTreeList, - strBPartner, strIsTaxIncluded)); + strBPartner, strIsTaxIncluded, strKey)); } else { xmlDocument.setData("reportShipmentReciept", "liststructure", CreateFromInvoiceData .selectFromShipmentCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), narturalOrgTreeList, - strBPartner, strIsTaxIncluded)); + strBPartner, strIsTaxIncluded, strKey)); xmlDocument.setData("reportPurchaseOrder", "liststructure", CreateFromInvoiceData .selectFromPOCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), narturalOrgTreeList, - strBPartner, strIsTaxIncluded)); + strBPartner, strIsTaxIncluded, strKey)); } } { @@ -637,21 +637,21 @@ if (isSOTrx.equals("Y")) { data = CreateFromShipmentData.selectFromPOSOTrx(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), - Utility.getContext(this, vars, "#User_Org", strWindowId), strPO); + Utility.getContext(this, vars, "#User_Org", strWindowId), strPO, strKey); } else { data = CreateFromShipmentData.selectFromPO(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), - Utility.getContext(this, vars, "#User_Org", strWindowId), strPO); + Utility.getContext(this, vars, "#User_Org", strWindowId), strPO, strKey); } } else { if (isSOTrx.equals("Y")) data = CreateFromShipmentData.selectFromPOTrlSOTrx(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), - Utility.getContext(this, vars, "#User_Org", strWindowId), strPO); + Utility.getContext(this, vars, "#User_Org", strWindowId), strPO, strKey); else data = CreateFromShipmentData.selectFromPOTrl(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), - Utility.getContext(this, vars, "#User_Org", strWindowId), strPO); + Utility.getContext(this, vars, "#User_Org", strWindowId), strPO, strKey); } } else { if (vars.getLanguage().equals("en_US")) { @@ -711,7 +711,7 @@ xmlDocument.setData("reportPurchaseOrder", "liststructure", CreateFromShipmentData .selectFromPOSOTrxCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), narturalOrgTreeList, - strBPartner)); + strBPartner, strKey)); } else { xmlDocument.setData("reportInvoice", "liststructure", CreateFromShipmentData .selectFromInvoiceCombo(this, vars.getLanguage(), @@ -720,7 +720,7 @@ xmlDocument.setData("reportPurchaseOrder", "liststructure", CreateFromShipmentData .selectFromPOCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), narturalOrgTreeList, - strBPartner)); + strBPartner, strKey)); } } @@ -782,22 +782,22 @@ // CreateFrom if (isSOTrx.equals("Y")) { strArray = Utility.arrayEntradaSimple("arrDatos", new CreateFromShipmentData[0]); - strArray2 = Utility.arrayEntradaSimple( - "arrDatos2", - CreateFromShipmentData.selectFromPOSOTrxCombo(this, vars.getLanguage(), + strArray2 = Utility.arrayEntradaSimple("arrDatos2", CreateFromShipmentData + .selectFromPOSOTrxCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), - Utility.getContext(this, vars, "#AccessibleOrgTree", strWindowId), strBPartner)); + Utility.getContext(this, vars, "#AccessibleOrgTree", strWindowId), strBPartner, + null)); } else { strArray = Utility.arrayEntradaSimple( "arrDatos", CreateFromShipmentData.selectFromInvoiceCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), Utility.getContext(this, vars, "#AccessibleOrgTree", strWindowId), strBPartner)); - strArray2 = Utility.arrayEntradaSimple( - "arrDatos2", - CreateFromShipmentData.selectFromPOCombo(this, vars.getLanguage(), + strArray2 = Utility.arrayEntradaSimple("arrDatos2", CreateFromShipmentData + .selectFromPOCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), - Utility.getContext(this, vars, "#AccessibleOrgTree", strWindowId), strBPartner)); + Utility.getContext(this, vars, "#AccessibleOrgTree", strWindowId), strBPartner, + null)); } } else { // Loading the Combos in the Invoice's CreateFrom Invoice invoice = OBDal.getInstance().get(Invoice.class, strKey); @@ -808,23 +808,23 @@ .selectFromShipmentSOTrxCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), Utility.getContext(this, vars, "#AccessibleOrgTree", strWindowId), strBPartner, - strIsTaxIncluded)); + strIsTaxIncluded, strKey)); strArray2 = Utility.arrayEntradaSimple("arrDatos2", CreateFromInvoiceData .selectFromPOSOTrxCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), Utility.getContext(this, vars, "#AccessibleOrgTree", strWindowId), strBPartner, - strIsTaxIncluded)); + strIsTaxIncluded, strKey)); } else { strArray = Utility.arrayEntradaSimple("arrDatos", CreateFromInvoiceData .selectFromShipmentCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), Utility.getContext(this, vars, "#AccessibleOrgTree", strWindowId), strBPartner, - strIsTaxIncluded)); + strIsTaxIncluded, strKey)); strArray2 = Utility.arrayEntradaSimple("arrDatos2", CreateFromInvoiceData .selectFromPOCombo(this, vars.getLanguage(), Utility.getContext(this, vars, "#User_Client", strWindowId), Utility.getContext(this, vars, "#AccessibleOrgTree", strWindowId), strBPartner, - strIsTaxIncluded)); + strIsTaxIncluded, strKey)); } } } @@ -1833,13 +1833,14 @@ if (strInOutLineId.isEmpty()) CreateFromShipmentData.updateInvoice(conn, this, strSequence, data[i].cInvoicelineId); - else { - CreateFromShipmentData.insertMatchInv(conn, this, vars.getUser(), - data[i].cInvoicelineId, strSequence, data[i].cInvoiceId); - } - } else + CreateFromShipmentData.insertMatchInv(conn, this, vars.getUser(), + data[i].cInvoicelineId, strSequence, data[i].cInvoiceId); + } else { CreateFromShipmentData.updateInvoiceOrder(conn, this, strSequence, data[i].cOrderlineId); + CreateFromShipmentData.insertMatchPO(conn, this, vars.getUser(), + data[i].cOrderlineId, strSequence, strPO); + } } catch (final ServletException ex) { myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage()); releaseRollbackConnection(conn); diff -r 988b9a459b02 -r a7d41be10b1b src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql --- a/src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql Tue Nov 13 15:01:21 2012 +0100 +++ b/src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql Tue Oct 16 13:13:02 2012 +0200 @@ -54,7 +54,8 @@ AND m.C_InvoiceLine_ID IS NOT NULL WHERE CO.C_ORDERLINE_ID = l.c_orderline_id AND CI.C_INVOICE_ID=? GROUP BY CI.C_orderline_id , CO.QtyORDERED - HAVING (SUM(COALESCE(CI.QtyINVOICED, 0))-(COALESCE(CO.QtyORDERED,0)-SUM(COALESCE(m.Qty,0)))) >= 0 ) + HAVING (SUM(COALESCE(CI.QtyINVOICED, 0))-(COALESCE(CO.QtyORDERED,0)-SUM(COALESCE(m.Qty,0)))) >= 0 ) + AND (NOT EXISTS (SELECT 1 FROM C_InvoiceLine WHERE C_Invoice_ID = ? AND C_OrderLine_ID = l.C_OrderLine_Id)) GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,l.C_UOM_ID, uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.ad_org_id, l.taxbaseamt,l.CancelPriceAD HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0 @@ -69,6 +70,7 @@ <Parameter name="adOrgClient" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/> <Parameter name="poId"/> <Parameter name="piId"/> + <Parameter name="piId"/> </SqlMethod> <SqlMethod name="selectFromPOCombo" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> @@ -86,6 +88,7 @@ FROM C_ORDERLINE l left join M_MATCHPO m on l.C_OrderLine_ID=m.C_OrderLine_ID and m.C_InvoiceLine_ID IS NOT NULL WHERE l.C_ORDER_ID = o.C_ORDER_ID + AND (NOT EXISTS (SELECT 1 FROM C_InvoiceLine WHERE C_Invoice_ID = ? AND C_OrderLine_Id = l.C_OrderLine_Id)) GROUP BY l.QtyOrdered,l.C_OrderLine_ID HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0) ORDER BY NAME @@ -96,6 +99,7 @@ <Parameter name="adOrgClient" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/> <Parameter name="cBpartnerId"/> <Parameter name="isTaxIncluded"/> + <Parameter name="cInvoiceId"/> </SqlMethod> <SqlMethod name="selectFromPOSOTrx" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> @@ -113,6 +117,11 @@ AND ic.AD_Org_ID IN ('1') AND l.M_Product_ID=p.M_Product_ID AND ic.C_Order_ID = ? + AND NOT EXISTS (SELECT 1 + FROM C_ORDERLINE CO LEFT JOIN C_INVOICELINE CI ON CI.C_ORDERLINE_ID = CO.C_ORDERLINE_ID + left join M_MATCHPO m on CO.C_OrderLine_ID=m.C_OrderLine_ID + AND m.C_InvoiceLine_ID IS NOT NULL + WHERE CO.C_ORDERLINE_ID = l.c_orderline_id AND CI.C_INVOICE_ID = ?) GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered, l.C_UOM_ID,uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.QTYINVOICED, l.ad_org_id, l.taxbaseamt @@ -125,6 +134,7 @@ <Parameter name="adUserClient" type="replace" optional="true" after="ic.AD_Client_ID IN (" text="'1'"/> <Parameter name="adOrgClient" type="replace" optional="true" after="ic.AD_Org_ID IN (" text="'1'"/> <Parameter name="poId"/> + <Parameter name="cInvoiceId"/> </SqlMethod> <SqlMethod name="selectFromPOSOTrxCombo" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> @@ -138,6 +148,11 @@ AND ic.AD_Org_ID IN ('1') AND ic.C_BPartner_ID = ? AND pl.istaxincluded = ? ------------------------------------------------------------------------------ Monitor your physical, virtual and cloud infrastructure from a single web console. Get in-depth insight into apps, servers, databases, vmware, SAP, cloud infrastructure, etc. Download 30-day Free Trial. Pricing starts from $795 for 25 servers or applications! http://p.sf.net/sfu/zoho_dev2dev_nov _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
