/** * Find/Create PO(Inv) Match * @param iLine invoice line * @param sLine receipt line * @param dateTrx date * @param qty qty * @return Match Record */ public static MMatchPO Create(MInvoiceLine iLine, MInOutLine sLine, DateTime?dateTrx, Decimal qty) { Trx trxName = null; Ctx ctx = null; int C_OrderLine_ID = 0; if (iLine != null) { trxName = iLine.Get_Trx(); ctx = iLine.GetCtx(); C_OrderLine_ID = iLine.GetC_OrderLine_ID(); } if (sLine != null) { trxName = sLine.Get_Trx(); ctx = sLine.GetCtx(); C_OrderLine_ID = sLine.GetC_OrderLine_ID(); } MMatchPO retValue = null; String sql = "SELECT * FROM M_MatchPO WHERE C_OrderLine_ID=" + C_OrderLine_ID; // ArrayList list = new ArrayList(); DataSet ds = null; try { ds = DataBase.DB.ExecuteDataset(sql, null, trxName); foreach (DataRow dr in ds.Tables[0].Rows) { MMatchPO mpo = new MMatchPO(ctx, dr, trxName); if (qty.CompareTo(mpo.GetQty()) == 0) { if (iLine != null) { if (mpo.GetC_InvoiceLine_ID() == 0 || mpo.GetC_InvoiceLine_ID() == iLine.GetC_InvoiceLine_ID()) { mpo.SetC_InvoiceLine_ID(iLine); if (iLine.GetM_AttributeSetInstance_ID() != 0) { if (mpo.GetM_AttributeSetInstance_ID() == 0) { mpo.SetM_AttributeSetInstance_ID(iLine.GetM_AttributeSetInstance_ID()); } else if (mpo.GetM_AttributeSetInstance_ID() != iLine.GetM_AttributeSetInstance_ID()) { continue; } } } else { continue; } } if (sLine != null) { if (mpo.GetM_InOutLine_ID() == 0 || mpo.GetM_InOutLine_ID() == sLine.GetM_InOutLine_ID()) { mpo.SetM_InOutLine_ID(sLine.GetM_InOutLine_ID()); if (sLine.GetM_AttributeSetInstance_ID() != 0) { if (mpo.GetM_AttributeSetInstance_ID() == 0) { mpo.SetM_AttributeSetInstance_ID(sLine.GetM_AttributeSetInstance_ID()); } else if (mpo.GetM_AttributeSetInstance_ID() != sLine.GetM_AttributeSetInstance_ID()) { continue; } } } else { continue; } } retValue = mpo; break; } } } catch (Exception e) { s_log.Log(Level.SEVERE, sql, e); } // Create New if (retValue == null) { if (sLine != null) { retValue = new MMatchPO(sLine, dateTrx, qty); if (iLine != null) { retValue.SetC_InvoiceLine_ID(iLine); } } else if (iLine != null) { retValue = new MMatchPO(iLine, dateTrx, qty); } } return(retValue); }
/// <summary> /// Consolidate MPO entries. /// </summary> /// <param name="ctx">context</param> public static void Consolidate(Ctx ctx) { String sql = "SELECT * FROM M_MatchPO po " //jz + "WHERE EXISTS (SELECT * FROM M_MatchPO x " + "WHERE EXISTS (SELECT 1 FROM M_MatchPO x " + "WHERE po.C_OrderLine_ID=x.C_OrderLine_ID AND po.Qty=x.Qty " + "GROUP BY C_OrderLine_ID, Qty " + "HAVING COUNT(*) = 2) " + " AND AD_Client_ID=" + ctx.GetAD_Client_ID() + "ORDER BY C_OrderLine_ID, M_InOutLine_ID"; int success = 0; int errors = 0; try { DataSet ds = DataBase.DB.ExecuteDataset(sql, null, null); if (ds.Tables.Count > 0) { DataRow dr = null; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { dr = ds.Tables[0].Rows[i]; MMatchPO po1 = new MMatchPO(ctx, dr, null); i++; //if (dr.next()) if (i < ds.Tables[0].Rows.Count) { dr = ds.Tables[0].Rows[i]; MMatchPO po2 = new MMatchPO(ctx, dr, null); if (po1.GetM_InOutLine_ID() != 0 && po1.GetC_InvoiceLine_ID() == 0 && po2.GetM_InOutLine_ID() == 0 && po2.GetC_InvoiceLine_ID() != 0) { String s1 = "UPDATE M_MatchPO SET C_InvoiceLine_ID=" + po2.GetC_InvoiceLine_ID() + " WHERE M_MatchPO_ID=" + po1.GetM_MatchPO_ID(); int no1 = ExecuteQuery.ExecuteNonQuery(s1); if (no1 != 1) { errors++; //s_log.warning("Not updated M_MatchPO_ID=" + po1.GetM_MatchPO_ID()); continue; } // String s2 = "DELETE FROM Fact_Acct WHERE AD_Table_ID=473 AND Record_ID=" + po2.GetM_MatchPO_ID(); int no2 = ExecuteQuery.ExecuteNonQuery(s2); String s3 = "DELETE FROM M_MatchPO WHERE M_MatchPO_ID=" + po2.GetM_MatchPO_ID(); int no3 = ExecuteQuery.ExecuteNonQuery(s3); if (no2 == 0 && no3 == 1) { success++; } else { //s_log.warning("M_MatchPO_ID=" + po2.GetM_MatchPO_ID() // + " - Deleted=" + no2 + ", Acct=" + no3); errors++; } } } } } } catch (Exception e) { s_log.Log(Level.SEVERE, sql, e); } if (errors == 0 && success == 0) { ; } else { s_log.Info("Success #" + success + " - Error #" + errors); } }