/// <summary> /// After Delete Logic /// </summary> /// <param name="success">success</param> /// <returns>result</returns> protected override bool AfterDelete(bool success) { if (!success) { return(success); } if (VAdvantage.Utility.Env.IsModuleInstalled("VA077_")) { int _count = Util.GetValueOfInt(DB.ExecuteQuery(DBFunctionCollection.UpdateLocAndNameOnBPHeader(GetC_BPartner_ID()), null, null)); if (_count < 0) { return(false); } } return(true); }
private bool CheckForPreviousCompeletedRecords() { MProfitLoss PL = new MProfitLoss(GetCtx(), GetC_ProfitLoss_ID(), null); MProfitLoss prof = new MProfitLoss(GetCtx(), GetC_ProfitLoss_ID(), Get_TrxName()); StringBuilder sql = new StringBuilder(); sql.Append("SELECT distinct CP.* FROM C_ProfitLoss CP INNER JOIN Fact_Acct ft ON ft.C_AcctSchema_ID = Cp.C_AcctSchema_ID " + " INNER JOIN c_elementvalue ev ON ft.account_id = ev.c_elementvalue_id " + " WHERE CP.ad_client_id = " + +GetAD_Client_ID()); if (prof.Get_Value("PostingType") != null) { sql.Append(" and CP.PostingType = '" + prof.Get_Value("PostingType") + "' "); } sql.Append(" AND (( " + GlobalVariable.TO_DATE(prof.GetDateFrom(), true) + " >= CP.DateFrom " + " AND " + GlobalVariable.TO_DATE(prof.GetDateFrom(), true) + " <= CP.DateTo " + " OR " + GlobalVariable.TO_DATE(prof.GetDateTo(), true) + " <= CP.DateFrom " + " AND " + GlobalVariable.TO_DATE(prof.GetDateTo(), true) + " <= CP.DateTo )) " + " AND (ev.accounttype ='E' OR ev.accounttype ='R') " + " AND ev.isintermediatecode='N' AND CP.AD_Org_ID IN ( (SELECT Ad_Org_ID FROM AD_Org WHERE isactive = 'Y' " + " AND (" + DBFunctionCollection.TypecastColumnAsInt("legalentityorg") + " =" + PL.GetAD_Org_ID() + " OR Ad_Org_ID = " + PL.GetAD_Org_ID() + ") )) AND DOCstatus in ('CO', 'CL') "); if (Util.GetValueOfInt(PL.Get_Value("C_AcctSchema_ID")) > 0) { sql.Append(" AND Cp.C_AcctSchema_ID=" + Util.GetValueOfInt(PL.Get_Value("C_AcctSchema_ID"))); } DataSet ds1 = DB.ExecuteDataset(sql.ToString(), null, Get_TrxName()); if (ds1 != null && ds1.Tables[0].Rows.Count > 0) { /// _processMsg = "Record(s) for defined criteria has already been generated"; _processMsg = Msg.GetMsg(GetCtx(), "VIS_RecordsAlreadyGenerated", true); return(false); } return(true); }
/// <summary> /// Process /// </summary> /// <returns>info</returns> protected override String DoIt() { isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx()); log.Info("M_Inventory_ID=" + _m_Inventory_ID); inventory = new MInventory(GetCtx(), _m_Inventory_ID, Get_TrxName()); if (inventory.Get_ID() == 0) { throw new SystemException("Not found: M_Inventory_ID=" + _m_Inventory_ID); } // Multiple Lines for one item //jz simple the SQL so that Derby also like it. To avoid testing Oracle by now, leave no change for Oracle String sql = null; if (DataBase.DB.IsOracle()) { sql = "UPDATE M_InventoryLine SET IsActive='N' " + "WHERE M_Inventory_ID=" + _m_Inventory_ID + " AND (M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID) IN " + "(SELECT M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID " + "FROM M_InventoryLine " + "WHERE M_Inventory_ID=" + _m_Inventory_ID + " GROUP BY M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID " + (isContainerApplicable ? " , M_ProductContainer_ID" : "") + "HAVING COUNT(*) > 1)"; } else { sql = "UPDATE M_InventoryLine SET IsActive='N' " + "WHERE M_Inventory_ID=" + _m_Inventory_ID + " AND EXISTS " + "(SELECT COUNT(*) " + "FROM M_InventoryLine " + "WHERE M_Inventory_ID=" + _m_Inventory_ID + " GROUP BY M_Product_ID, M_Locator_ID, M_AttributeSetInstance_ID " + (isContainerApplicable ? " , M_ProductContainer_ID" : "") + "HAVING COUNT(*) > 1)"; } int multiple = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); log.Info("Multiple=" + multiple); int delMA = MInventoryLineMA.DeleteInventoryMA(_m_Inventory_ID, Get_TrxName()); log.Info("DeletedMA=" + delMA); // ASI sql = "UPDATE M_InventoryLine l " + "SET (QtyBook,QtyCount) = " + "(SELECT QtyOnHand,QtyOnHand FROM M_Storage s " + "WHERE s.M_Product_ID=l.M_Product_ID AND s.M_Locator_ID=l.M_Locator_ID" + " AND s.M_AttributeSetInstance_ID=l.M_AttributeSetInstance_ID)," + " Updated=SysDate," + " UpdatedBy=" + GetAD_User_ID() // + " WHERE M_Inventory_ID=" + _m_Inventory_ID + " AND EXISTS (SELECT * FROM M_Storage s " + "WHERE s.M_Product_ID=l.M_Product_ID AND s.M_Locator_ID=l.M_Locator_ID" + " AND s.M_AttributeSetInstance_ID=l.M_AttributeSetInstance_ID)"; int no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); log.Info("Update with ASI =" + no); // No ASI //int noMA = UpdateWithMA(); // Set Count to Zero if (_inventoryCountSetZero) { sql = "UPDATE M_InventoryLine l " + "SET QtyCount=0 " + "WHERE M_Inventory_ID=" + _m_Inventory_ID; no = DataBase.DB.ExecuteQuery(sql, null, Get_TrxName()); log.Info("Set Count to Zero =" + no); } if (_AdjustinventoryCount) { // MInventoryLine[] lines = inventory.GetLines(true); // for (int i = 0; i < lines.Length; i++) // { // decimal currentQty = 0; // string query = "", qry = ""; // int result = 0; // MInventoryLine iLine = lines[i]; // int M_Product_ID = Utility.Util.GetValueOfInt(iLine.GetM_Product_ID()); // int M_Locator_ID = Utility.Util.GetValueOfInt(iLine.GetM_Locator_ID()); // int M_AttributeSetInstance_ID = Util.GetValueOfInt(iLine.GetM_AttributeSetInstance_ID()); // query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate = " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + @" // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID; // result = Util.GetValueOfInt(DB.ExecuteScalar(query)); // if (result > 0) // { // qry = @"SELECT currentqty FROM M_Transaction WHERE M_Transaction_ID = // (SELECT MAX(M_Transaction_ID) FROM M_Transaction // WHERE movementdate = (SELECT MAX(movementdate) FROM M_Transaction WHERE movementdate <= " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + @" // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @") // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @") // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID; // currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(qry)); // } // else // { // query = "SELECT COUNT(*) FROM M_Transaction WHERE movementdate < " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + @" // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID; // result = Util.GetValueOfInt(DB.ExecuteScalar(query)); // if (result > 0) // { // qry = @"SELECT currentqty FROM M_Transaction WHERE M_Transaction_ID = // (SELECT MAX(M_Transaction_ID) FROM M_Transaction // WHERE movementdate = (SELECT MAX(movementdate) FROM M_Transaction WHERE movementdate < " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + @" // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @") // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID + @") // AND M_Product_ID = " + M_Product_ID + " AND M_Locator_ID = " + M_Locator_ID + " AND M_AttributeSetInstance_ID = " + M_AttributeSetInstance_ID; // currentQty = Util.GetValueOfDecimal(DB.ExecuteScalar(qry)); // } // } // iLine.SetQtyBook(currentQty); // iLine.SetOpeningStock(currentQty); // if (iLine.GetAdjustmentType() == "A") // { // iLine.SetDifferenceQty(Util.GetValueOfDecimal(iLine.GetOpeningStock()) - Util.GetValueOfDecimal(iLine.GetAsOnDateCount())); // } // else if (iLine.GetAdjustmentType() == "D") // { // iLine.SetAsOnDateCount(Util.GetValueOfDecimal(iLine.GetOpeningStock()) - Util.GetValueOfDecimal(iLine.GetDifferenceQty())); // } // iLine.SetQtyCount(Util.GetValueOfDecimal(iLine.GetQtyBook()) - Util.GetValueOfDecimal(iLine.GetDifferenceQty())); // if (!iLine.Save()) // { // } // } // Work done by Bharat on 26/12/2016 for optimization if (isContainerApplicable) { sql = @"SELECT m.M_InventoryLine_ID, m.M_Locator_ID, m.M_Product_ID, m.M_AttributeSetInstance_ID, m.AdjustmentType, m.AsOnDateCount, m.DifferenceQty, nvl(mt.CurrentQty, 0) as CurrentQty FROM M_InventoryLine m LEFT JOIN (SELECT DISTINCT t.M_Locator_ID, t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_ProductContainer_ID, FIRST_VALUE(t.ContainerCurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID, NVL(t.M_ProductContainer_ID, 0) ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM M_Transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + " AND t.AD_Client_ID = " + inventory.GetAD_Client_ID() + @") mt ON m.M_Product_ID = mt.M_Product_ID AND nvl(m.M_AttributeSetInstance_ID, 0) = nvl(mt.M_AttributeSetInstance_ID, 0) AND m.M_Locator_ID = mt.M_Locator_ID AND nvl(m.M_ProductContainer_ID, 0) = nvl(mt.M_ProductContainer_ID, 0) WHERE m.M_Inventory_ID = " + _m_Inventory_ID + " ORDER BY m.Line"; } else { sql = @"SELECT m.M_InventoryLine_ID, m.M_Locator_ID, m.M_Product_ID, m.M_AttributeSetInstance_ID, m.AdjustmentType, m.AsOnDateCount, m.DifferenceQty, nvl(mt.CurrentQty, 0) as CurrentQty FROM M_InventoryLine m LEFT JOIN (SELECT DISTINCT t.M_Locator_ID, t.M_Product_ID, t.M_AttributeSetInstance_ID, FIRST_VALUE(t.CurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM M_Transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(inventory.GetMovementDate(), true) + " AND t.AD_Client_ID = " + inventory.GetAD_Client_ID() + @") mt ON m.M_Product_ID = mt.M_Product_ID AND nvl(m.M_AttributeSetInstance_ID, 0) = nvl(mt.M_AttributeSetInstance_ID, 0) AND m.M_Locator_ID = mt.M_Locator_ID WHERE m.M_Inventory_ID = " + _m_Inventory_ID + " ORDER BY m.Line"; } int totalRec = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(M_InventoryLine_ID) FROM ( " + sql + " ) t", null, null)); int pageSize = 500; int TotalPage = (totalRec % pageSize) == 0 ? (totalRec / pageSize) : ((totalRec / pageSize) + 1); int count = 0; DataSet ds = null; StringBuilder updateSql = new StringBuilder(); try { if (totalRec > 0) { log.Info(" =====> Physical Inventory update process Started at " + DateTime.Now.ToString()); if (!_SkipBL) { for (int pageNo = 1; pageNo <= TotalPage; pageNo++) { ds = DB.GetDatabase().ExecuteDatasetPaging(sql, pageNo, pageSize, 0); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { decimal currentQty = 0; int line_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]); currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][7]); MInventoryLine iLine = new MInventoryLine(GetCtx(), line_ID, Get_TrxName()); iLine.SetQtyBook(currentQty); iLine.SetOpeningStock(currentQty); if (iLine.GetAdjustmentType() == "A") { iLine.SetDifferenceQty(Util.GetValueOfDecimal(iLine.GetOpeningStock()) - Util.GetValueOfDecimal(iLine.GetAsOnDateCount())); } else if (iLine.GetAdjustmentType() == "D") { iLine.SetAsOnDateCount(Util.GetValueOfDecimal(iLine.GetOpeningStock()) - Util.GetValueOfDecimal(iLine.GetDifferenceQty())); } iLine.SetQtyCount(Util.GetValueOfDecimal(iLine.GetQtyBook()) - Util.GetValueOfDecimal(iLine.GetDifferenceQty())); if (!iLine.Save()) { } else { count++; } } ds.Dispose(); log.Info(" =====> records updated at " + DateTime.Now.ToString() + " are = " + count + " <===== "); } } } else { for (int pageNo = 1; pageNo <= TotalPage; pageNo++) { //updateSql.Clear(); ds = DB.GetDatabase().ExecuteDatasetPaging(sql, pageNo, pageSize, 0); if (ds != null && ds.Tables[0].Rows.Count > 0) { //updateSql.Append("BEGIN "); //for (int j = 0; j < ds.Tables[0].Rows.Count; j++) //{ // int line_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]); // int locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]); // int product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]); // string AdjustType = Util.GetValueOfString(ds.Tables[0].Rows[j][4]); // decimal AsonDateCount = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][5]); // decimal DiffQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][6]); // decimal currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][7]); // string updateQry = UpdateInventoryLine(line_ID, product_ID, locator_ID, currentQty, AdjustType, AsonDateCount, DiffQty); // if (updateQry != "") // { // updateSql.Append(updateQry); // } //} //ds.Dispose(); //updateSql.Append(" END;"); //int cnt = DB.ExecuteQuery(updateSql.ToString(), null, Get_Trx()); //log.Info(" =====> records updated at " + DateTime.Now.ToString() + " are = " + count + " <===== "); string updateQry = DBFunctionCollection.UpdateInventoryLine(GetCtx(), ds, Get_Trx()); ds.Dispose(); int cnt = DB.ExecuteQuery(updateQry, null, Get_Trx()); } } } log.Info(" =====> Physical Inventory update process end at " + DateTime.Now.ToString()); } } catch (Exception e) { if (ds != null) { ds.Dispose(); } log.Log(Level.SEVERE, sql.ToString(), e); } } inventory.SetIsAdjusted(true); if (!inventory.Save()) { } if (multiple > 0) { return("@M_InventoryLine_ID@ - #" + no + " --> @InventoryProductMultiple@"); } //return "@M_InventoryLine_ID@ - #" + (no + noMA); return("Physical Inventory Updated"); }
/** * After Save * @param newRecord new * @param success success * @return success */ protected override bool AfterSave(bool newRecord, bool success) { PO project = null; int _client_ID = 0; StringBuilder _sql = new StringBuilder(); //_sql.Append("Select count(*) from ad_table where tablename like 'FRPT_Project_Acct'"); //_sql.Append("SELECT count(*) FROM all_objects WHERE object_type IN ('TABLE') AND (object_name) = UPPER('FRPT_Project_Acct') AND OWNER LIKE '" + DB.GetSchema() + "'"); _sql.Append(DBFunctionCollection.CheckTableExistence(DB.GetSchema(), "FRPT_Project_Acct")); int count = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString())); if (count > 0) { _sql.Clear(); _sql.Append("Select L.Value From Ad_Ref_List L inner join AD_Reference r on R.AD_REFERENCE_ID=L.AD_REFERENCE_ID where r.name='FRPT_RelatedTo' and l.name='Project'"); var relatedtoProject = Convert.ToString(DB.ExecuteScalar(_sql.ToString())); _client_ID = GetAD_Client_ID(); _sql.Clear(); _sql.Append("select C_AcctSchema_ID from C_AcctSchema where AD_CLIENT_ID=" + _client_ID); DataSet ds3 = new DataSet(); ds3 = DB.ExecuteDataset(_sql.ToString(), null); if (ds3 != null && ds3.Tables[0].Rows.Count > 0) { for (int k = 0; k < ds3.Tables[0].Rows.Count; k++) { int _AcctSchema_ID = Util.GetValueOfInt(ds3.Tables[0].Rows[k]["C_AcctSchema_ID"]); _sql.Clear(); _sql.Append("Select Frpt_Acctdefault_Id,C_Validcombination_Id,Frpt_Relatedto From Frpt_Acctschema_Default Where ISACTIVE='Y' AND AD_CLIENT_ID=" + _client_ID + "AND C_Acctschema_Id=" + _AcctSchema_ID); DataSet ds = DB.ExecuteDataset(_sql.ToString(), null); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string _relatedTo = ds.Tables[0].Rows[i]["Frpt_Relatedto"].ToString(); if (_relatedTo != "" && (_relatedTo == relatedtoProject)) { _sql.Clear(); _sql.Append("Select COUNT(*) From C_Project Bp Left Join FRPT_Project_Acct ca On Bp.C_Project_ID=ca.C_Project_ID And ca.Frpt_Acctdefault_Id=" + ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"] + " WHERE Bp.IsActive='Y' AND Bp.AD_Client_ID=" + _client_ID + " AND ca.C_Validcombination_Id = " + Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Validcombination_Id"]) + " AND Bp.C_Project_ID = " + GetC_Project_ID()); int recordFound = Convert.ToInt32(DB.ExecuteScalar(_sql.ToString(), null, Get_Trx())); if (recordFound == 0) { project = MTable.GetPO(GetCtx(), "FRPT_Project_Acct", 0, null); project.Set_ValueNoCheck("AD_Org_ID", 0); project.Set_ValueNoCheck("C_Project_ID", Util.GetValueOfInt(GetC_Project_ID())); project.Set_ValueNoCheck("FRPT_AcctDefault_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"])); project.Set_ValueNoCheck("C_ValidCombination_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Validcombination_Id"])); project.Set_ValueNoCheck("C_AcctSchema_ID", _AcctSchema_ID); if (!project.Save()) { } } } } } } } } else if (newRecord & success && (String.IsNullOrEmpty(GetCtx().GetContext("#DEFAULT_ACCOUNTING_APPLICABLE")) || Util.GetValueOfString(GetCtx().GetContext("#DEFAULT_ACCOUNTING_APPLICABLE")) == "Y")) { bool sucs = Insert_Accounting("C_Project_Acct", "C_AcctSchema_Default", null); //Karan. work done to show message if data not saved in accounting tab. but will save data in current tab. // Before this, data was being saved but giving message "record not saved". if (!sucs) { log.SaveWarning("AcctNotSaved", ""); } } // Value/Name change MProject prjph = null; if (success && !newRecord && (Is_ValueChanged("Value") || Is_ValueChanged("Name"))) { MAccount.UpdateValueDescription(GetCtx(), "C_Project_ID=" + GetC_Project_ID(), Get_TrxName()); } if (GetC_Campaign_ID() != 0) { //Used transaction because total was not updating on header MCampaign cam = new MCampaign(GetCtx(), GetC_Campaign_ID(), Get_TrxName()); decimal plnAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT COALESCE(SUM(pl.PlannedAmt),0) FROM C_Project pl WHERE pl.IsActive = 'Y' AND pl.C_Campaign_ID = " + GetC_Campaign_ID(), null, Get_TrxName())); cam.SetCosts(plnAmt); cam.Save(); } else { //Used transaction because total was not updating on header prjph = new MProject(GetCtx(), GetC_Project_ID(), Get_TrxName()); if (!prjph.IsOpportunity()) { //Used transaction because total was not updating on header decimal plnAmt = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT COALESCE(SUM(PlannedAmt),0) FROM C_ProjectPhase WHERE IsActive= 'Y' AND C_Project_ID= " + GetC_Project_ID(), null, Get_TrxName())); DB.ExecuteQuery("UPDATE C_Project SET PlannedAmt=" + plnAmt + " WHERE C_Project_ID=" + GetC_Project_ID(), null, Get_TrxName()); } } return(success); }
/** * After Save * @param newRecord new record * @param success success * @return success */ protected override Boolean AfterSave(Boolean newRecord, Boolean success) { int _client_ID = 0; StringBuilder _sql = new StringBuilder(); //_sql.Append("Select count(*) from ad_table where tablename like 'FRPT_BP_Group_Acct'"); //_sql.Append("SELECT count(*) FROM all_objects WHERE object_type IN ('TABLE') AND (object_name) = UPPER('FRPT_BP_Group_Acct') AND OWNER LIKE '" + DB.GetSchema() + "'"); _sql.Append(DBFunctionCollection.CheckTableExistence(DB.GetSchema(), "FRPT_BP_Group_Acct")); int count = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString())); if (count > 0) { _sql.Clear(); _sql.Append("SELECT L.VALUE FROM AD_REF_LIST L inner join AD_Reference r on R.AD_REFERENCE_ID=L.AD_REFERENCE_ID where r.name='FRPT_RelatedTo' and l.name='Customer'"); var relatedtoCustomer = Convert.ToString(DB.ExecuteScalar(_sql.ToString())); _sql.Clear(); _sql.Append("Select L.Value From Ad_Ref_List L inner join AD_Reference r on R.AD_REFERENCE_ID=L.AD_REFERENCE_ID where r.name='FRPT_RelatedTo' and l.name='Vendor'"); var relatedtoVendor = Convert.ToString(DB.ExecuteScalar(_sql.ToString())); _sql.Clear(); _sql.Append("Select L.Value From Ad_Ref_List L inner join AD_Reference r on R.AD_REFERENCE_ID=L.AD_REFERENCE_ID where r.name='FRPT_RelatedTo' and l.name='Employee'"); var relatedtoEmployee = Convert.ToString(DB.ExecuteScalar(_sql.ToString())); PO gpact = null; _client_ID = GetAD_Client_ID(); _sql.Clear(); _sql.Append("select C_AcctSchema_ID from C_AcctSchema where AD_CLIENT_ID=" + _client_ID); DataSet ds3 = new DataSet(); ds3 = DB.ExecuteDataset(_sql.ToString(), null); if (ds3 != null && ds3.Tables[0].Rows.Count > 0) { for (int k = 0; k < ds3.Tables[0].Rows.Count; k++) { int _AcctSchema_ID = Util.GetValueOfInt(ds3.Tables[0].Rows[k]["C_AcctSchema_ID"]); _sql.Clear(); _sql.Append("Select Frpt_Acctdefault_Id,C_Validcombination_Id,Frpt_Relatedto From Frpt_Acctschema_Default Where ISACTIVE='Y' AND AD_CLIENT_ID=" + _client_ID + "AND C_Acctschema_Id=" + _AcctSchema_ID); DataSet ds = new DataSet(); ds = DB.ExecuteDataset(_sql.ToString(), null); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { //DataSet ds2 = new DataSet(); string _relatedTo = ds.Tables[0].Rows[i]["Frpt_Relatedto"].ToString(); if (_relatedTo != "") { if (_relatedTo == relatedtoCustomer || _relatedTo == relatedtoEmployee || _relatedTo == relatedtoVendor) { _sql.Clear(); //_sql.Append("Select Bp.C_BP_Group_ID,ca.Frpt_Acctdefault_Id From C_BP_Group Bp Left Join FRPT_BP_Group_Acct ca On Bp.C_BP_Group_ID=ca.C_BP_Group_ID And ca.Frpt_Acctdefault_Id=" + ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"] + " WHERE Bp.IsActive='Y' AND Bp.AD_Client_ID=" + _client_ID); _sql.Append("Select COUNT(*) From C_BP_Group Bp Left Join FRPT_BP_Group_Acct ca On Bp.C_BP_Group_ID=ca.C_BP_Group_ID And ca.Frpt_Acctdefault_Id=" + ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"] + " WHERE Bp.IsActive='Y' AND Bp.AD_Client_ID=" + _client_ID + " AND ca.C_Validcombination_Id = " + Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Validcombination_Id"]) + " AND Bp.C_BP_Group_ID = " + GetC_BP_Group_ID()); int recordFound = Convert.ToInt32(DB.ExecuteScalar(_sql.ToString(), null, Get_Trx())); // ds2 = DB.ExecuteDataset(_sql.ToString(), null); //if (ds2 != null && ds2.Tables[0].Rows.Count > 0) //{ // for (int j = 0; j < ds2.Tables[0].Rows.Count; j++) // { // int value = Util.GetValueOfInt(ds2.Tables[0].Rows[j]["Frpt_Acctdefault_Id"]); // if (value == 0) // { //gpact = new X_FRPT_BP_Group_Acct(GetCtx(), 0, null); if (recordFound == 0) { gpact = MTable.GetPO(GetCtx(), "FRPT_BP_Group_Acct", 0, null); //gpact.Set_ValueNoCheck("C_BP_Group_ID", Util.GetValueOfInt(ds2.Tables[0].Rows[j]["C_BP_Group_ID"])); gpact.Set_ValueNoCheck("C_BP_Group_ID", Util.GetValueOfInt(GetC_BP_Group_ID())); gpact.Set_ValueNoCheck("AD_Org_ID", 0); gpact.Set_ValueNoCheck("FRPT_AcctDefault_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"])); gpact.Set_ValueNoCheck("C_ValidCombination_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Validcombination_Id"])); gpact.Set_ValueNoCheck("C_AcctSchema_ID", _AcctSchema_ID); if (!gpact.Save()) { } } //} //} //} } } } } } } } else { object table = DB.ExecuteScalar("SELECT count(*) from AD_Table WHERE TableName='C_BP_Group_Acct'"); if (table == null || table == DBNull.Value || table == "" || Convert.ToInt16(table) == 0) { return(success); } if (newRecord & success && (String.IsNullOrEmpty(GetCtx().GetContext("#DEFAULT_ACCOUNTING_APPLICABLE")) || Util.GetValueOfString(GetCtx().GetContext("#DEFAULT_ACCOUNTING_APPLICABLE")) == "Y")) { success = Insert_Accounting("C_BP_Group_Acct", "C_AcctSchema_Default", null); //Karan. work done to show message if data not saved in accounting tab. but will save data in current tab. // Before this, data was being saved but giving message "record not saved". if (!success) { log.SaveWarning("AcctNotSaved", ""); return(true); } } } return(success); }
protected override string DoIt() { MProfitLoss PL = new MProfitLoss(GetCtx(), GetRecord_ID(), null); prof = new MProfitLoss(GetCtx(), GetRecord_ID(), Get_Trx()); stDate = Util.GetValueOfDateTime(DB.ExecuteScalar("select p.startdate from c_period p inner join c_year y on p.c_year_id=y.c_year_id where p.periodno='1' and p.c_year_id= " + prof.GetC_Year_ID() + " and y.ad_client_id= " + GetAD_Client_ID(), null, null)); eDate = Util.GetValueOfDateTime(DB.ExecuteScalar("select p.enddate from c_period p inner join c_year y on p.c_year_id=y.c_year_id where p.periodno='12' and p.c_year_id= " + prof.GetC_Year_ID() + " and y.ad_client_id= " + GetAD_Client_ID(), null, null)); // lock record lock (lockRecord) { sql.Clear(); sql.Append("SELECT distinct CP.* FROM C_ProfitLoss CP INNER JOIN Fact_Acct ft ON ft.C_AcctSchema_ID = Cp.C_AcctSchema_ID " + " INNER JOIN c_elementvalue ev ON ft.account_id =ev.c_elementvalue_id " + " WHERE CP.ad_client_id = " + GetAD_Client_ID()); if (prof.Get_Value("PostingType") != null) { sql.Append(" and CP.PostingType = '" + prof.Get_Value("PostingType") + "' "); } sql.Append(" AND (( " + GlobalVariable.TO_DATE(prof.GetDateFrom(), true) + " >= CP.DateFrom " + " AND " + GlobalVariable.TO_DATE(prof.GetDateFrom(), true) + " <= CP.DateTo " + " OR " + GlobalVariable.TO_DATE(prof.GetDateTo(), true) + " <= CP.DateFrom " + " AND " + GlobalVariable.TO_DATE(prof.GetDateTo(), true) + " <= CP.DateTo )) " + " AND (ev.accounttype ='E' OR ev.accounttype ='R') " + " AND ev.isintermediatecode='N' AND CP.AD_Org_ID IN ( (SELECT Ad_Org_ID FROM AD_Org WHERE isactive = 'Y' " + " AND ( " + DBFunctionCollection.TypecastColumnAsInt("legalentityorg") + " =" + PL.GetAD_Org_ID() + " OR Ad_Org_ID = " + PL.GetAD_Org_ID() + ") )) AND DOCstatus in ('CO', 'CL') "); if (Util.GetValueOfInt(PL.Get_Value("C_AcctSchema_ID")) > 0) { sql.Append(" AND Cp.C_AcctSchema_ID=" + Util.GetValueOfInt(PL.Get_Value("C_AcctSchema_ID"))); } ds1 = DB.ExecuteDataset(sql.ToString(), null, Get_Trx()); if (ds1 != null && ds1.Tables[0].Rows.Count > 0) { return(Msg.GetMsg(GetCtx(), "VIS_RecordsAlreadyGenerated")); } // Get currentNext for generating key C_ProfitLossLines_ID = MSequence.GetNextID(GetAD_Client_ID(), "C_ProfitLossLines", Get_Trx()); C_ProfitLossLines_ID -= 1; // Delete All Record from line DB.ExecuteQuery("DELETE FROM C_ProfitLossLines WHERE C_ProfitLoss_ID=" + GetRecord_ID()); insert.Clear(); insert.Append(@"INSERT INTO C_ProfitLossLines (C_ProfitLossLines_ID , AD_Client_ID , AD_Org_ID , C_ProfitLoss_ID , C_ProfitAndLoss_ID , C_AcctSchema_ID , PostingType , AccountCredit ,AccountDebit,Account_ID, C_SubAcct_ID, C_BPartner_ID , M_Product_ID , C_Project_ID , C_SalesRegion_ID , C_Campaign_ID , AD_OrgTrx_ID , C_LocFrom_ID , C_LocTo_ID , C_Activity_ID, User1_ID , User2_ID , UserElement1_ID, UserElement2_ID, UserElement3_ID, UserElement4_ID, UserElement5_ID, UserElement6_ID, UserElement7_ID, UserElement8_ID, UserElement9_ID , GL_Budget_ID, C_ProjectPhase_ID, C_ProjectTask_ID, LedgerCode,LedgerName, Line ) "); qry.Clear(); qry.Append(@"select " + (!DB.IsPostgreSQL() ? "MAX" : "") + "(" + C_ProfitLossLines_ID + " + " + DBFunctionCollection.RowNumAggregation("rownum") + ") AS C_ProfitLossLines_id, ft.AD_Client_ID , ft.AD_Org_ID , " + PL.GetC_ProfitLoss_ID() + " , " + prof.GetC_ProfitAndLoss_ID() + ", ft.C_AcctSchema_ID,ft.PostingType, SUM(NVL(ft.AmtAcctDr, 0)),SUM(NVL(ft.AmtAcctCr, 0)),ft.Account_ID,ft.C_SubAcct_ID,ft.C_BPartner_ID,ft.M_Product_ID,ft.C_Project_ID,ft.C_SalesRegion_ID,ft.C_Campaign_ID,ft.AD_OrgTrx_ID,ft.C_LocFrom_ID,ft.C_LocTo_ID,ft.C_Activity_ID,ft.User1_ID,ft.User2_ID,ft.UserElement1_ID,ft.UserElement2_ID," + " ft.UserElement3_ID,ft.UserElement4_ID, ft.UserElement5_ID, ft.UserElement6_ID, ft.UserElement7_ID,ft.UserElement8_ID, ft.UserElement9_ID,ft.GL_Budget_ID,ft.C_ProjectPhase_ID,ft.C_ProjectTask_ID," + @" ev.Value as LedgerCode,ev.Name as LedgerName , " + (!DB.IsPostgreSQL() ? "MAX" : "") + @"((SELECT NVL(MAX(Line), 0) FROM C_ProfitLossLines WHERE C_ProfitLoss_ID = " + PL.GetC_ProfitLoss_ID() + ") + (" + DBFunctionCollection.RowNumAggregation("rownum") + " * 10)) AS lineno from Fact_Acct ft inner join c_elementvalue ev on ft.account_id = ev.c_elementvalue_id where ft.ad_client_id = " + GetAD_Client_ID()); // Added by SUkhwinder on 27 Nov 2017, for filtering query on the basis of postingtype. And string variable converted to stringBuilder also. if (prof.Get_Value("PostingType") != null) { qry.Append(" and ft.PostingType = '" + prof.Get_Value("PostingType") + "'"); } qry.Append(" and ft.DateAcct >=" + GlobalVariable.TO_DATE(prof.GetDateFrom(), true) + " AND ft.DateAcct <= " + GlobalVariable.TO_DATE(prof.GetDateTo(), true) + " AND (ev.accounttype='E' OR ev.accounttype='R') and ev.isintermediatecode='N'" + " AND ft.AD_Org_ID IN ( (SELECT Ad_Org_ID FROM AD_Org WHERE isactive = 'Y' AND (" + DBFunctionCollection.TypecastColumnAsInt("legalentityorg") + " =" + PL.GetAD_Org_ID() + " OR Ad_Org_ID = " + PL.GetAD_Org_ID() + ")))"); if (Util.GetValueOfInt(PL.Get_Value("C_AcctSchema_ID")) > 0) { qry.Append(" AND ft.C_AcctSchema_ID=" + Util.GetValueOfInt(PL.Get_Value("C_AcctSchema_ID"))); } // group by the records, so that we can reduce the no of entry qry.Append(@" GROUP BY ft.AD_Client_ID , ft.AD_Org_ID , ft.C_AcctSchema_ID,ft.PostingType , ft.Account_ID,ft.C_SubAcct_ID,ft.C_BPartner_ID,ft.M_Product_ID,ft.C_Project_ID,ft.C_SalesRegion_ID, ft.C_Campaign_ID,ft.AD_OrgTrx_ID,ft.C_LocFrom_ID,ft.C_LocTo_ID,ft.C_Activity_ID,ft.User1_ID, ft.User2_ID,ft.UserElement1_ID,ft.UserElement2_ID, ft.UserElement3_ID,ft.UserElement4_ID, ft.UserElement5_ID, ft.UserElement6_ID, ft.UserElement7_ID,ft.UserElement8_ID, ft.UserElement9_ID,ft.GL_Budget_ID,ft.C_ProjectPhase_ID,ft.C_ProjectTask_ID, ev.Value,ev.Name"); insert.Append(qry.ToString()); log.Info(insert.ToString()); int no = Util.GetValueOfInt(DB.ExecuteQuery(insert.ToString(), null, Get_Trx())); if (no > 0) { // Update curentNext in AD_Sequence C_ProfitLossLines_ID += (no + 1); String updateSQL = "UPDATE AD_Sequence SET CurrentNext = " + C_ProfitLossLines_ID + ", CurrentNextSys = " + C_ProfitLossLines_ID + " " + " WHERE Upper(Name)=Upper('C_ProfitLossLines')" + " AND IsActive='Y' AND IsTableID='Y' AND IsAutoSequence='Y' "; DB.ExecuteQuery(updateSQL, null, Get_Trx()); // Update Profit Before tax on Header ProfitBeforeTax = Util.GetValueOfDecimal(DB.ExecuteScalar("SELECT SUM(AccountDebit) - SUM(AccountCredit) FROM C_ProfitLossLines WHERE C_ProfitAndLoss_ID > 0 AND IsActive='Y' AND C_ProfitLoss_ID=" + GetRecord_ID(), null, Get_Trx())); prof.SetProfitBeforeTax(ProfitBeforeTax); if (!prof.Save(Get_Trx())) { ds.Dispose(); Rollback(); // return GetReterivedError(prof, "ProfitNotSaved"); return(Msg.GetMsg(GetCtx(), "ProfitNotSaved")); } else { Get_Trx().Commit(); // insert record against Income summary acct InsertProfitLossLine(prof); } return(Msg.GetMsg(GetCtx(), "LinesGenerated")); } else { return(Msg.GetMsg(GetCtx(), "RecordNoFound")); } } }
/** * After Save * @param newRecord new * @param success success * @return success */ protected override bool AfterSave(bool newRecord, bool success) { if (!success) return success; StringBuilder _sql = new StringBuilder(""); //_sql.Append("Select count(*) from ad_table where tablename like 'FRPT_Product_Category_Acct'"); //_sql.Append("SELECT count(*) FROM all_objects WHERE object_type IN ('TABLE') AND (object_name) = UPPER('FRPT_Product_Category_Acct') AND OWNER LIKE '" + DB.GetSchema() + "'"); _sql.Append(DBFunctionCollection.CheckTableExistence(DB.GetSchema(), "FRPT_Product_Category_Acct")); int count = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString())); if (count > 0) { PO obj = null; //MFRPTProductAcct obj = null; int _MProduct_ID = GetM_Product_ID(); int _PCategory_ID = GetM_Product_Category_ID(); string sql = "SELECT L.VALUE FROM AD_REF_LIST L inner join AD_Reference r on R.AD_REFERENCE_ID=L.AD_REFERENCE_ID where r.name='FRPT_RelatedTo' and l.name='Product'"; //"select VALUE from AD_Ref_List where name='Product'"; string _RelatedToProduct = Convert.ToString(DB.ExecuteScalar(sql)); //string _RelatedToProduct = X_FRPT_AcctDefault.FRPT_RELATEDTO_Product.ToString(); _sql.Clear(); _sql.Append("Select Count(*) From FRPT_Product_Acct where M_Product_ID=" + _MProduct_ID + " AND IsActive = 'Y' AND AD_Client_ID = " + GetAD_Client_ID()); int value = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString())); if (value < 1) { _sql.Clear(); _sql.Append("Select PCA.c_acctschema_id, PCA.c_validcombination_id, PCA.frpt_acctdefault_id From FRPT_product_category_acct PCA inner join frpt_acctdefault ACC ON acc.frpt_acctdefault_id= PCA.frpt_acctdefault_id where PCA.m_product_category_id=" + _PCategory_ID + " and acc.frpt_relatedto='" + _RelatedToProduct + "' AND PCA.IsActive = 'Y' AND PCA.AD_Client_ID = " + GetAD_Client_ID()); //_sql.Append("Select C_AcctSchema_ID, C_ValidCombination_ID, FRPT_AcctDefault_ID from FRPT_product_category_acct where m_product_category_id =" + _PCategory_ID); DataSet ds = DB.ExecuteDataset(_sql.ToString()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { //obj = new MFRPTProductAcct(GetCtx(), 0, null); obj = MTable.GetPO(GetCtx(), "FRPT_Product_Acct", 0, null); obj.Set_ValueNoCheck("AD_Org_ID", 0); obj.Set_ValueNoCheck("M_Product_ID", _MProduct_ID); obj.Set_ValueNoCheck("C_AcctSchema_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_AcctSchema_ID"])); obj.Set_ValueNoCheck("C_ValidCombination_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_ValidCombination_ID"])); obj.Set_ValueNoCheck("FRPT_AcctDefault_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"])); if (!obj.Save()) { } } } } // Change by mohit amortization process //int _CountVA038 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA038_' AND IsActive = 'Y'")); //if (_CountVA038 > 0) //{ // if (GetProductType() == "E" || GetProductType() == "S") // { // if (Util.GetValueOfInt(Get_Value("VA038_AmortizationTemplate_ID")) > 0) // { // DataSet _dsAcct = DB.ExecuteDataset("SELECT C_AcctSchema_ID, FRPT_AcctDefault_ID, C_VALIDCOMBINATION_ID, SEQNO FROM VA038_Amortization_Acct " // + "WHERE IsActive='Y' AND VA038_AmortizationTemplate_ID=" + Util.GetValueOfInt(Get_Value("VA038_AmortizationTemplate_ID"))); // if (_dsAcct != null && _dsAcct.Tables[0].Rows.Count > 0) // { // for (int j = 0; j < _dsAcct.Tables[0].Rows.Count; j++) // { // obj = MTable.GetPO(GetCtx(), "FRPT_Product_Acct", 0, null); // obj.Set_ValueNoCheck("AD_Org_ID", 0); // obj.Set_ValueNoCheck("M_Product_ID", _MProduct_ID); // obj.Set_ValueNoCheck("C_AcctSchema_ID", Util.GetValueOfInt(_dsAcct.Tables[0].Rows[j]["C_AcctSchema_ID"])); // obj.Set_ValueNoCheck("C_ValidCombination_ID", Util.GetValueOfInt(_dsAcct.Tables[0].Rows[j]["C_ValidCombination_ID"])); // obj.Set_ValueNoCheck("FRPT_AcctDefault_ID", Util.GetValueOfInt(_dsAcct.Tables[0].Rows[j]["FRPT_AcctDefault_ID"])); // if (!obj.Save()) // { } // } // } // } // } //} // End amortization process } else { // By Amit //if (!success) // return success; // Value/Name change in Account if (!newRecord && (Is_ValueChanged("Value") || Is_ValueChanged("Name"))) MAccount.UpdateValueDescription(GetCtx(), "M_Product_ID=" + GetM_Product_ID(), Get_TrxName()); // Name/Description Change in Asset MAsset.setValueNameDescription if (!newRecord && (Is_ValueChanged("Name") || Is_ValueChanged("Description"))) { String sql = " UPDATE A_Asset a SET Name=(SELECT SUBSTR(bp.Name || ' - ' || p.Name,1,60) FROM M_Product p, C_BPartner bp WHERE p.M_Product_ID=a.M_Product_ID AND bp.C_BPartner_ID=a.C_BPartner_ID)," + "Description=(SELECT p.Description FROM M_Product p, C_BPartner bp WHERE p.M_Product_ID=a.M_Product_ID AND bp.C_BPartner_ID=a.C_BPartner_ID)" + "WHERE IsActive='Y' AND M_Product_ID=" + GetM_Product_ID(); int no = 0; try { no = Utility.Util.GetValueOfInt(DataBase.DB.ExecuteQuery(sql, null, Get_TrxName())); } catch { } log.Fine("Asset Description updated #" + no); } // New - Acct, Tree, Old Costing if (newRecord) { if (String.IsNullOrEmpty(GetCtx().GetContext("#DEFAULT_ACCOUNTING_APPLICABLE")) || Util.GetValueOfString(GetCtx().GetContext("#DEFAULT_ACCOUNTING_APPLICABLE")) == "Y") { bool sucs = Insert_Accounting("M_Product_Acct", "M_Product_Category_Acct", "p.M_Product_Category_ID=" + GetM_Product_Category_ID()); //Karan. work done to show message if data not saved in accounting tab. but will save data in current tab. // Before this, data was being saved but giving message "record not saved". if (!sucs) { log.SaveWarning("AcctNotSaved", ""); } } // MAcctSchema[] mass = MAcctSchema.GetClientAcctSchema(GetCtx(), GetAD_Client_ID(), Get_TrxName()); for (int i = 0; i < mass.Length; i++) { // Old MProductCosting pcOld = new MProductCosting(this, mass[i].GetC_AcctSchema_ID()); pcOld.Save(); } } // New Costing // by Amit 22-12-2015 //if (newRecord || Is_ValueChanged("M_Product_Category_ID")) //{ // MCost.Create(this); //} } //22-12-2015 //by Amit for creating records ffor product foe all Costing Element whose costing elemnt type is 'Material' //if (newRecord || Is_ValueChanged("M_Product_Category_ID")) //{ // MCost.CreateRecords(this); //} //20-12-2016 //By Vivek Chauhan saving Nutrition value against product........... object ModuleId = DB.ExecuteScalar("select ad_moduleinfo_id from ad_moduleinfo where prefix='VA019_' and isactive='Y'"); if (ModuleId != null && ModuleId != DBNull.Value) { object objNDBNo = DB.ExecuteScalar("select va019_ndbno from M_Product where m_product_ID=" + GetM_Product_ID() + ""); if (objNDBNo != null && objNDBNo != DBNull.Value) { CallNutritionApi(Convert.ToString(objNDBNo), GetM_Product_ID()); } } return success; }
/// <summary> /// After save logic for asset /// </summary> /// <param name="newRecord"></param> /// <param name="success"></param> /// <returns></returns> protected override bool AfterSave(bool newRecord, bool success) { //Cost Code Commented - As not required on Asset Save //if (newRecord) //{ // UpdateAssetCost(); //} // create default Account StringBuilder _sql = new StringBuilder(""); // check table exist or not //_sql.Append("SELECT count(*) FROM all_objects WHERE object_type IN ('TABLE') AND (object_name) = UPPER('FRPT_Asset_Group_Acct') AND OWNER LIKE '" + DB.GetSchema() + "'"); _sql.Append(DBFunctionCollection.CheckTableExistence(DB.GetSchema(), "FRPT_Asset_Group_Acct")); int count = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString())); if (count > 0) { PO obj = null; int assetId = GetA_Asset_ID(); int assetGroupId = GetA_Asset_Group_ID(); // get related to value agaisnt asset = 75 string sql = "SELECT L.VALUE FROM AD_REF_LIST L inner join AD_Reference r on R.AD_REFERENCE_ID=L.AD_REFERENCE_ID where r.name='FRPT_RelatedTo' and l.name='Asset'"; string _RelatedToProduct = Convert.ToString(DB.ExecuteScalar(sql)); _sql.Clear(); _sql.Append("Select Count(*) From FRPT_Asset_Acct where A_Asset_ID=" + assetId + " AND IsActive = 'Y' AND AD_Client_ID = " + GetAD_Client_ID()); int value = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString())); if (value < 1) { _sql.Clear(); _sql.Append(@"Select PCA.c_acctschema_id, PCA.c_validcombination_id, PCA.frpt_acctdefault_id " + " From FRPT_Asset_Group_Acct PCA " + " inner join frpt_acctdefault ACC ON acc.frpt_acctdefault_id= PCA.frpt_acctdefault_id " + " where PCA.A_Asset_Group_ID=" + assetGroupId + " and acc.frpt_relatedto='" + _RelatedToProduct + "' AND PCA.IsActive = 'Y' AND PCA.AD_Client_ID = " + GetAD_Client_ID()); DataSet ds = DB.ExecuteDataset(_sql.ToString()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { obj = MTable.GetPO(GetCtx(), "FRPT_Asset_Acct", 0, null); obj.Set_ValueNoCheck("AD_Org_ID", 0); obj.Set_ValueNoCheck("A_Asset_ID", assetId); obj.Set_ValueNoCheck("C_AcctSchema_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_AcctSchema_ID"])); obj.Set_ValueNoCheck("C_ValidCombination_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_ValidCombination_ID"])); obj.Set_ValueNoCheck("FRPT_AcctDefault_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"])); if (!obj.Save()) { ValueNamePair pp = VLogger.RetrieveError(); _log.Log(Level.SEVERE, "Could Not create FRPT_Asset_Acct. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName()); } } } } } return(true); }
/// <summary> /// Complete Document /// </summary> /// <returns>new status (Complete, In Progress, Invalid, Waiting ..)</returns> public String CompleteIt() { // Re-Check if (!_justPrepared) { String status = PrepareIt(); if (!DocActionVariables.STATUS_INPROGRESS.Equals(status)) { return(status); } } // Implicit Approval if (!IsApproved()) { ApproveIt(); } log.Info("CompleteIt - " + ToString()); // MMovement move = new MMovement(GetCtx(), GetM_Movement_ID(), Get_TrxName()); MMovementLineConfirm[] lines = GetLines(false); #region [Change By Sukhwinder on 11th October,2017 To Check if VA Material Quality Control Module exists or not, and then check if actual value at Quality Control tab exists or not] if (Env.IsModuleInstalled("VA010_")) { try { StringBuilder sb = new StringBuilder(); for (int i = 0; i < lines.Length; i++) { sb.Append(lines[i].Get_ID() + ","); } string mMovementLinesConfirm = sb.ToString().Trim(','); if (!string.IsNullOrEmpty(mMovementLinesConfirm)) { string qry = DBFunctionCollection.MoveConfirmNoActualValue(mMovementLinesConfirm); string productsNoActualValue = Util.GetValueOfString(DB.ExecuteScalar(qry)); if (!string.IsNullOrEmpty(productsNoActualValue)) { //_processMsg = productsNoActualValue + " is/are not verified with all the Quality Parameters." + // " Please fill actual value for the missing Quality Parameters in Quality Control. "; _processMsg = productsNoActualValue + " " + Msg.GetMsg(GetCtx(), "VIS_NoActualValueInQC"); return(DocActionVariables.STATUS_INVALID); } } } catch (Exception ex) { log.Severe("Exception When Checking actual value at Quality Control Tab - " + ex.Message); } } #endregion for (int i = 0; i < lines.Length; i++) { MMovementLineConfirm confirm = lines[i]; confirm.Set_TrxName(Get_TrxName()); //Added By Arpit if (GetDescription() != null && GetDescription().Contains("{->")) { //if (!confirm.ProcessLineReverse()) //{ // _processMsg = "ShipLine not saved - " + confirm; // return DocActionVariables.STATUS_INVALID; //} } else if (!confirm.ProcessLine()) { _processMsg = "ShipLine not saved - " + confirm; return(DocActionVariables.STATUS_INVALID); } if (confirm.IsFullyConfirmed()) { confirm.SetProcessed(true); confirm.Save(Get_TrxName()); } else { if (CreateDifferenceDoc(move, confirm)) { confirm.SetProcessed(true); confirm.Save(Get_TrxName()); } else { log.Log(Level.SEVERE, "completeIt - Scrapped=" + confirm.GetScrappedQty() + " - Difference=" + confirm.GetDifferenceQty()); _processMsg = "Differnce Doc not created"; return(DocActionVariables.STATUS_INVALID); } } } // for all lines if (_inventoryInfo != null) { _processMsg = " @M_Inventory_ID@: " + _inventoryInfo; AddDescription(Msg.Translate(GetCtx(), "M_Inventory_ID") + ": " + _inventoryInfo); } //Amit 21-nov-2014 (Reduce reserved quantity from requisition and warehouse distribution center) Tuple <String, String, String> mInfo = null; if (Env.HasModulePrefix("DTD001_", out mInfo)) { MMovementLine movementLine = null; MRequisitionLine requisitionLine = null; MStorage storage = null; for (int i = 0; i < lines.Length; i++) { MMovementLineConfirm confirm = lines[i]; if (confirm.GetDifferenceQty() > 0) { movementLine = new MMovementLine(GetCtx(), confirm.GetM_MovementLine_ID(), Get_Trx()); if (movementLine.GetM_RequisitionLine_ID() > 0) { requisitionLine = new MRequisitionLine(GetCtx(), movementLine.GetM_RequisitionLine_ID(), Get_Trx()); requisitionLine.SetDTD001_ReservedQty(decimal.Subtract(requisitionLine.GetDTD001_ReservedQty(), confirm.GetDifferenceQty())); if (!requisitionLine.Save(Get_Trx())) { _processMsg = Msg.GetMsg(GetCtx(), "DTD001_ReqNotUpdate"); // _processMsg = "Requisitionline not updated"; return(DocActionVariables.STATUS_INVALID); } storage = MStorage.Get(GetCtx(), movementLine.GetM_Locator_ID(), movementLine.GetM_Product_ID(), movementLine.GetM_AttributeSetInstance_ID(), Get_Trx()); if (storage == null) { storage = MStorage.Get(GetCtx(), movementLine.GetM_Locator_ID(), movementLine.GetM_Product_ID(), 0, Get_Trx()); } storage.SetQtyReserved(decimal.Subtract(storage.GetQtyReserved(), confirm.GetDifferenceQty())); if (!storage.Save(Get_Trx())) { Get_Trx().Rollback(); _processMsg = Msg.GetMsg(GetCtx(), "DTD001_StorageNotUpdate"); //_processMsg = "Storage From not updated (MA)"; return(DocActionVariables.STATUS_INVALID); } } } } } //Amit SetProcessed(true); SetDocAction(DOCACTION_Close); Save(Get_Trx()); //Adde by Arpit To complete The Inventory Move if Move Confirmation is completed ,16th Dec,2016 MMovement Mov = new MMovement(GetCtx(), GetM_Movement_ID(), Get_Trx()); if (move.GetDocStatus() != DOCSTATUS_Completed) { string Status = Mov.CompleteIt(); if (Status == "CO") { move.SetDocStatus(DOCSTATUS_Completed); move.SetDocAction("CL"); move.SetProcessed(true); move.Save(Get_Trx()); } else { Get_Trx().Rollback(); _processMsg = Mov.GetProcessMsg(); return(DocActionVariables.STATUS_INVALID); } } //End Here return(DocActionVariables.STATUS_COMPLETED); }
/// <summary> /// Process /// </summary> /// <returns>info</returns> protected override String DoIt() { // is used to check Container applicable into system isContainerApplicable = MTransaction.ProductContainerApplicable(GetCtx()); log.Info("M_Inventory_ID=" + _m_Inventory_ID + ", M_Locator_ID=" + _m_Locator_ID + ", LocatorValue=" + _locatorValue + ", ProductValue=" + _productValue + ", M_Product_Category_ID=" + _m_Product_Category_ID + ", QtyRange=" + _qtyRange + ", DeleteOld=" + _deleteOld); _inventory = new MInventory(GetCtx(), _m_Inventory_ID, Get_TrxName()); if (_inventory.Get_ID() == 0) { throw new SystemException("Not found: M_Inventory_ID=" + _m_Inventory_ID); } if (_inventory.IsProcessed()) { throw new SystemException("@M_Inventory_ID@ @Processed@"); } // String sqlQry = ""; StringBuilder sql = null; int count = 0; if (_deleteOld) { sqlQry = "DELETE FROM M_InventoryLine WHERE Processed='N' " + "AND M_Inventory_ID=" + _m_Inventory_ID; int no = DataBase.DB.ExecuteQuery(sqlQry, null, Get_TrxName()); log.Fine("doIt - Deleted #" + no); } if (!isContainerApplicable) { sql = new StringBuilder( @"WITH mt AS (SELECT m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, SUM(CurrentQty) AS CurrentQty FROM (SELECT DISTINCT t.M_Product_ID, t.M_Locator_ID, t.M_AttributeSetInstance_ID, FIRST_VALUE(t.CurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM m_transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(_inventory.GetMovementDate(), true) + @" AND t.AD_Client_ID = " + _inventory.GetAD_Client_ID() + " AND l.AD_Org_ID = " + _inventory.GetAD_Org_ID() + @" AND l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + @") t GROUP BY m_product_id, M_Locator_ID, M_AttributeSetInstance_ID ) SELECT DISTINCT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, mt.currentqty AS Qty, s.QtyOnHand, p.M_AttributeSet_ID FROM M_Product p INNER JOIN M_Storage s ON (s.M_Product_ID=p.M_Product_ID) INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) JOIN mt ON (mt.M_Product_ID = s.M_Product_ID AND mt.M_Locator_ID = s.M_Locator_ID AND mt.M_AttriButeSetInstance_ID = NVL(s.M_AttriButeSetInstance_ID,0)) WHERE l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"); } else { sql = new StringBuilder(@"WITH mt AS (SELECT m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, SUM(CurrentQty) AS CurrentQty, M_ProductContainer_ID FROM (SELECT DISTINCT t.M_Product_ID, t.M_Locator_ID, t.M_AttributeSetInstance_ID, NVL(t.M_ProductContainer_ID , 0) AS M_ProductContainer_ID, FIRST_VALUE(t.ContainerCurrentQty) OVER (PARTITION BY t.M_Product_ID, t.M_AttributeSetInstance_ID, t.M_Locator_ID, NVL(t.M_ProductContainer_ID, 0) ORDER BY t.MovementDate DESC, t.M_Transaction_ID DESC) AS CurrentQty FROM m_transaction t INNER JOIN M_Locator l ON t.M_Locator_ID = l.M_Locator_ID WHERE t.MovementDate <= " + GlobalVariable.TO_DATE(_inventory.GetMovementDate(), true) + @" AND t.AD_Client_ID = " + _inventory.GetAD_Client_ID() + " AND l.AD_Org_ID = " + _inventory.GetAD_Org_ID() + @" AND l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + @") t GROUP BY m_product_id, M_Locator_ID, M_AttributeSetInstance_ID, M_ProductContainer_ID ) SELECT DISTINCT s.M_Product_ID, s.M_Locator_ID, s.M_AttributeSetInstance_ID, mt.currentqty AS Qty, mt.M_ProductContainer_ID, p.M_AttributeSet_ID FROM M_Product p INNER JOIN M_ContainerStorage s ON (s.M_Product_ID=p.M_Product_ID) INNER JOIN M_Locator l ON (s.M_Locator_ID=l.M_Locator_ID) JOIN mt ON (mt.M_Product_ID = s.M_Product_ID AND mt.M_Locator_ID = s.M_Locator_ID AND mt.M_AttriButeSetInstance_ID = NVL(s.M_AttriButeSetInstance_ID,0) AND mt.M_ProductContainer_ID = NVL(s.M_ProductContainer_ID , 0)) WHERE l.M_Warehouse_ID = " + _inventory.GetM_Warehouse_ID() + " AND p.IsActive='Y' AND p.IsStocked='Y' and p.ProductType='I'"); } // if (_m_Locator_ID != 0) { sql.Append(" AND s.M_Locator_ID=" + _m_Locator_ID); } if (_locatorValue != null && (_locatorValue.Trim().Length == 0 || _locatorValue.Equals("%"))) { _locatorValue = null; } if (_locatorValue != null) { sql.Append(" AND UPPER(l.Value) LIKE '" + _locatorValue.ToUpper() + "'"); } // if (_productValue != null && (_productValue.Trim().Length == 0 || _productValue.Equals("%"))) { _productValue = null; } if (_productValue != null) { sql.Append(" AND UPPER(p.Value) LIKE '" + _productValue.ToUpper() + "'"); } if (_m_Product_Category_ID != 0) { sql.Append(" AND p.M_Product_Category_ID=" + _m_Product_Category_ID); } // Do not overwrite existing records if (!_deleteOld) { sql.Append(" AND NOT EXISTS (SELECT * FROM M_InventoryLine il " + "WHERE il.M_Inventory_ID=" + _m_Inventory_ID + " AND il.M_Product_ID=s.M_Product_ID" + " AND il.M_Locator_ID=s.M_Locator_ID" + " AND COALESCE(il.M_AttributeSetInstance_ID,0)=COALESCE(s.M_AttributeSetInstance_ID,0)"); if (!isContainerApplicable) { sql.Append(" ) "); } else { sql.Append(@" AND COALESCE(il.M_ProductContainer_ID,0)=COALESCE(s.M_ProductContainer_ID,0) )"); } } // if (_qtyRange == "N") { sql.Append(" AND mt.currentqty != 0"); } /*SI_0631 : System is giving error ORA-00920: invalid relational operator if User does not select any value from Inventory Quantity list. * Physical Inventory Window -- button -- Create inventory count list*/ else if (!String.IsNullOrEmpty(_qtyRange)) { sql.Append(" AND mt.currentqty " + _qtyRange + " 0"); } int totalRec = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(M_Product_ID) FROM ( " + sql.ToString() + " ) t", null, null)); int pageSize = 500; int TotalPage = (totalRec % pageSize) == 0 ? (totalRec / pageSize) : ((totalRec / pageSize) + 1); StringBuilder insertSql = new StringBuilder(); DataSet ds = null; try { if (totalRec > 0) { log.Info(" =====> Physical Inventory process Started at " + DateTime.Now.ToString()); if (!_SkipBL) { for (int pageNo = 1; pageNo <= TotalPage; pageNo++) { ds = DB.GetDatabase().ExecuteDatasetPaging(sql.ToString(), pageNo, pageSize, 0); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { decimal currentQty = 0; int container_ID = 0; int M_Product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]); int M_Locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]); int M_AttributeSetInstance_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]); int M_AttributeSet_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][5]); if (isContainerApplicable) { container_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j]["M_ProductContainer_ID"]); } currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][3]); count += CreateInventoryLine(M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID, currentQty, currentQty, M_AttributeSet_ID, container_ID); } ds.Dispose(); log.Info(" =====> records inserted at " + DateTime.Now.ToString() + " are = " + count + " <===== "); } } } else { for (int pageNo = 1; pageNo <= TotalPage; pageNo++) { //insertSql.Clear(); ds = DB.GetDatabase().ExecuteDatasetPaging(sql.ToString(), pageNo, pageSize, 0); if (ds != null && ds.Tables[0].Rows.Count > 0) { sqlQry = "SELECT COALESCE(MAX(Line),0) AS DefaultValue FROM M_InventoryLine WHERE M_Inventory_ID=" + _m_Inventory_ID; int lineNo = DB.GetSQLValue(Get_Trx(), sqlQry); //insertSql.Append("BEGIN "); //for (int j = 0; j < ds.Tables[0].Rows.Count; j++) //{ // decimal currentQty = 0; // int container_ID = 0; // int M_Product_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][0]); // int M_Locator_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][1]); // int M_AttributeSetInstance_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][2]); // int M_AttributeSet_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j][5]); // if (isContainerApplicable) // { // container_ID = Util.GetValueOfInt(ds.Tables[0].Rows[j]["M_ProductContainer_ID"]); // } // currentQty = Util.GetValueOfDecimal(ds.Tables[0].Rows[j][3]); // lineNo = lineNo + 10; // string insertQry = InsertInventoryLine(M_Locator_ID, M_Product_ID, lineNo, M_AttributeSetInstance_ID, currentQty, M_AttributeSet_ID, container_ID); // if (insertQry != "") // { // insertSql.Append(insertQry); // } //} //ds.Dispose(); //insertSql.Append(" END;"); string insertQry = DBFunctionCollection.InsertInventoryLine(GetCtx(), _inventory, ds, lineNo, isContainerApplicable, Get_Trx()); ds.Dispose(); int no = DB.ExecuteQuery(insertQry, null, Get_Trx()); } } } log.Info(" =====> Physical Inventory process end at " + DateTime.Now.ToString()); } } catch (Exception e) { if (ds != null) { ds.Dispose(); } log.Log(Level.SEVERE, sql.ToString(), e); } // Set Count to Zero if (_inventoryCountSetZero) { String sql1 = ""; MInventoryLine inv = new MInventoryLine(GetCtx(), 0, null); if (inv.Get_ColumnIndex("IsFromProcess") >= 0) { sql1 = "UPDATE M_InventoryLine l " + "SET QtyCount=0,AsOnDateCount=0 " + "WHERE M_Inventory_ID=" + _m_Inventory_ID + " AND IsFromProcess = 'Y'"; } else { sql1 = "UPDATE M_InventoryLine l " + "SET QtyCount=0,AsOnDateCount=0 " + "WHERE M_Inventory_ID=" + _m_Inventory_ID; } int no = DataBase.DB.ExecuteQuery(sql1, null, Get_TrxName()); log.Info("Set Cont to Zero=" + no); } return("Physical Inventory Created"); }
/// <summary> /// After Save Logic /// </summary> /// <param name="newRecord"></param> /// <param name="success"></param> /// <returns></returns> protected override bool AfterSave(bool newRecord, bool success) { // create default Account StringBuilder _sql = new StringBuilder(); _sql.Append(DBFunctionCollection.CheckTableExistence(DB.GetSchema(), "FRPT_RevenueRecognition_Acct")); int count = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString())); if (count > 0) { _sql.Clear(); _sql.Append("Select L.Value From Ad_Ref_List L inner join AD_Reference r on R.AD_REFERENCE_ID=L.AD_REFERENCE_ID where r.name='FRPT_RelatedTo' and l.name='Revenue Recognition'"); var relatedtoProduct = Convert.ToString(DB.ExecuteScalar(_sql.ToString())); PO assetGroupAcct = null; _sql.Clear(); _sql.Append("select C_AcctSchema_ID from C_AcctSchema where IsActive = 'Y' AND AD_CLIENT_ID=" + GetAD_Client_ID()); DataSet ds3 = new DataSet(); ds3 = DB.ExecuteDataset(_sql.ToString(), null); if (ds3 != null && ds3.Tables[0].Rows.Count > 0) { for (int k = 0; k < ds3.Tables[0].Rows.Count; k++) { int _AcctSchema_ID = Util.GetValueOfInt(ds3.Tables[0].Rows[k]["C_AcctSchema_ID"]); _sql.Clear(); _sql.Append("Select Frpt_Acctdefault_Id,C_Validcombination_Id,Frpt_Relatedto From Frpt_Acctschema_Default Where ISACTIVE='Y' AND AD_CLIENT_ID=" + GetAD_Client_ID() + "AND C_Acctschema_Id=" + _AcctSchema_ID); DataSet ds = new DataSet(); ds = DB.ExecuteDataset(_sql.ToString(), null, Get_Trx()); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string _relatedTo = ds.Tables[0].Rows[i]["Frpt_Relatedto"].ToString(); if (!_relatedTo.Equals("") && _relatedTo.Equals(relatedtoProduct)) { _sql.Clear(); _sql.Append(@"Select count(*) From C_RevenueRecognition Bp Left Join FRPT_RevenueRecognition_Acct ca On Bp.C_RevenueRecognition_ID=ca.C_RevenueRecognition_ID And ca.Frpt_Acctdefault_Id=" + ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"] + " WHERE Bp.IsActive='Y' AND Bp.AD_Client_ID=" + GetAD_Client_ID() + " AND ca.C_Validcombination_Id = " + Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Validcombination_Id"]) + " AND Bp.C_RevenueRecognition_ID = " + GetC_RevenueRecognition_ID()); int recordFound = Convert.ToInt32(DB.ExecuteScalar(_sql.ToString(), null, Get_Trx())); if (recordFound == 0) { assetGroupAcct = MTable.GetPO(GetCtx(), "FRPT_RevenueRecognition_Acct", 0, null); assetGroupAcct.Set_ValueNoCheck("AD_Org_ID", 0); assetGroupAcct.Set_ValueNoCheck("C_RevenueRecognition_ID", Util.GetValueOfInt(GetC_RevenueRecognition_ID())); assetGroupAcct.Set_ValueNoCheck("FRPT_AcctDefault_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"])); assetGroupAcct.Set_ValueNoCheck("C_ValidCombination_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Validcombination_Id"])); assetGroupAcct.Set_ValueNoCheck("C_AcctSchema_ID", _AcctSchema_ID); if (!assetGroupAcct.Save()) { ValueNamePair pp = VLogger.RetrieveError(); log.Log(Level.SEVERE, "Could Not create FRPT_Asset_Groip_Acct. ERRor Value : " + pp.GetValue() + "ERROR NAME : " + pp.GetName()); } } } } } } } } return(true); }
/** * After Save * @param newRecord new record * @param success success * @return success */ protected override Boolean AfterSave(Boolean newRecord, Boolean success) { if (newRecord & success) { int _client_ID = 0; StringBuilder _sql = new StringBuilder(); //_sql.Append("Select count(*) from ad_table where tablename like 'FRPT_BankAccount_Acct'"); //_sql.Append("SELECT count(*) FROM all_objects WHERE object_type IN ('TABLE') AND (object_name) = UPPER('FRPT_BankAccount_Acct') AND OWNER LIKE '" + DB.GetSchema() + "'"); _sql.Append(DBFunctionCollection.CheckTableExistence(DB.GetSchema(), "FRPT_BankAccount_Acct")); int count = Util.GetValueOfInt(DB.ExecuteScalar(_sql.ToString())); if (count > 0) { string sql = " SELECT L.VALUE FROM AD_REF_LIST L" + " INNER JOIN AD_Reference r ON R.AD_REFERENCE_ID=L.AD_REFERENCE_ID" + " WHERE r.name='FRPT_RelatedTo' AND l.name='BankAccount'"; var relatedto = Convert.ToString(DB.ExecuteScalar(sql)); PO bnkact = null; //X_FRPT_BankAccount_Acct bnkact = null; //MAcctSchema acschma = new MAcctSchema(GetCtx(), 0, null); _client_ID = GetAD_Client_ID(); _sql.Clear(); _sql.Append("select C_AcctSchema_ID from C_AcctSchema where AD_CLIENT_ID=" + _client_ID); DataSet ds3 = new DataSet(); ds3 = DB.ExecuteDataset(_sql.ToString(), null); if (ds3 != null && ds3.Tables[0].Rows.Count > 0) { for (int k = 0; k < ds3.Tables[0].Rows.Count; k++) { int _AcctSchema_ID = Util.GetValueOfInt(ds3.Tables[0].Rows[k]["C_AcctSchema_ID"]); _sql.Clear(); _sql.Append("Select Frpt_Acctdefault_Id,C_Validcombination_Id,Frpt_Relatedto From Frpt_Acctschema_Default Where ISACTIVE='Y' AND AD_CLIENT_ID=" + _client_ID + "AND C_Acctschema_Id=" + _AcctSchema_ID); DataSet ds = new DataSet(); ds = DB.ExecuteDataset(_sql.ToString(), null); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { //DataSet ds2 = new DataSet(); string _relatedTo = ds.Tables[0].Rows[i]["Frpt_Relatedto"].ToString(); if (_relatedTo != "") { // if (_relatedTo == X_FRPT_AcctSchema_Default.FRPT_RELATEDTO_BankAccount.ToString()) if (_relatedTo == relatedto) { _sql.Clear(); _sql.Append("Select COUNT(*) From C_BankAccount Ba Left Join FRPT_BankAccount_Acct ca On Ba.C_BANKACCOUNT_ID=ca.C_BANKACCOUNT_ID And ca.Frpt_Acctdefault_Id=" + ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"] + " WHERE Ba.IsActive='Y' AND Ba.AD_Client_ID=" + _client_ID + " AND ca.C_Validcombination_Id = " + Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Validcombination_Id"]) + " AND Ba.C_BankAccount_ID = " + GetC_BankAccount_ID()); int recordFound = Convert.ToInt32(DB.ExecuteScalar(_sql.ToString(), null, Get_Trx())); //ds2 = DB.ExecuteDataset(_sql.ToString(), null); //if (ds2 != null && ds2.Tables[0].Rows.Count > 0) //{ // for (int j = 0; j < ds2.Tables[0].Rows.Count; j++) // { // int value = Util.GetValueOfInt(ds2.Tables[0].Rows[j]["Frpt_Acctdefault_Id"]); // if (value == 0) // { //bnkact = new X_FRPT_BankAccount_Acct(GetCtx(), 0, null); if (recordFound == 0) { bnkact = MTable.GetPO(GetCtx(), "FRPT_BankAccount_Acct", 0, null); bnkact.Set_ValueNoCheck("C_BankAccount_ID", Util.GetValueOfInt(GetC_BankAccount_ID())); bnkact.Set_ValueNoCheck("AD_Org_ID", 0); bnkact.Set_Value("FRPT_AcctDefault_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["FRPT_AcctDefault_ID"])); bnkact.Set_Value("C_ValidCombination_ID", Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_Validcombination_Id"])); bnkact.Set_Value("C_AcctSchema_ID", _AcctSchema_ID); if (!bnkact.Save()) { } } // } // } //} } } } } } } } else if (String.IsNullOrEmpty(GetCtx().GetContext("#DEFAULT_ACCOUNTING_APPLICABLE")) || Util.GetValueOfString(GetCtx().GetContext("#DEFAULT_ACCOUNTING_APPLICABLE")) == "Y") { bool sucs = Insert_Accounting("C_BankAccount_Acct", "C_AcctSchema_Default", null); //Karan. work done to show message if data not saved in accounting tab. but will save data in current tab. // Before this, data was being saved but giving message "record not saved". if (!sucs) { log.SaveWarning("AcctNotSaved", ""); } } } return(success); }