/** * Set Invoice Line. * Does not set Quantity! * @param iLine invoice line * @param M_Locator_ID locator * @param Qty qty only fo find suitable locator */ public void SetInvoiceLine(MInvoiceLine iLine, int M_Locator_ID, Decimal Qty) { SetC_OrderLine_ID(iLine.GetC_OrderLine_ID()); SetLine(iLine.GetLine()); SetC_UOM_ID(iLine.GetC_UOM_ID()); int M_Product_ID = iLine.GetM_Product_ID(); if (M_Product_ID == 0) { Set_ValueNoCheck("M_Product_ID", null); Set_ValueNoCheck("M_Locator_ID", null); Set_ValueNoCheck("M_AttributeSetInstance_ID", null); } else { SetM_Product_ID(M_Product_ID); SetM_AttributeSetInstance_ID(iLine.GetM_AttributeSetInstance_ID()); if (M_Locator_ID == 0) { SetM_Locator_ID(Qty); // requires warehouse, product, asi } else { SetM_Locator_ID(M_Locator_ID); } } SetC_Charge_ID(iLine.GetC_Charge_ID()); SetDescription(iLine.GetDescription()); SetIsDescription(iLine.IsDescription()); // SetC_Project_ID(iLine.GetC_Project_ID()); SetC_ProjectPhase_ID(iLine.GetC_ProjectPhase_ID()); SetC_ProjectTask_ID(iLine.GetC_ProjectTask_ID()); SetC_Activity_ID(iLine.GetC_Activity_ID()); SetC_Campaign_ID(iLine.GetC_Campaign_ID()); SetAD_OrgTrx_ID(iLine.GetAD_OrgTrx_ID()); SetUser1_ID(iLine.GetUser1_ID()); SetUser2_ID(iLine.GetUser2_ID()); }
public static int RecognitionCombination(MINT15AccountingSchemaOrg accountingSchemaOrg, string recognitionType, MInvoiceLine InvoiceLine) { string sql = ""; if (InvoiceLine.GetM_Product_ID() > 0) { sql = "Select pacct.c_validcombination_id From FRPT_Product_Acct pacct INNER JOIN FRPT_AcctDefault fad on fad.FRPT_AcctDefault_ID = pacct.FRPT_AcctDefault_ID " + " where fad.INT15_RecognizeType = '" + recognitionType + "' and pacct.c_acctschema_id = " + accountingSchemaOrg.GetC_AcctSchema_ID() + " And pacct.M_PRoduct_ID=" + InvoiceLine.GetM_Product_ID(); } else if (InvoiceLine.GetC_Charge_ID() > 0) { sql = "Select pacct.c_validcombination_id From FRPT_Charge_Acct pacct INNER JOIN FRPT_AcctDefault fad on fad.FRPT_AcctDefault_ID = pacct.FRPT_AcctDefault_ID " + " where fad.INT15_RecognizeType = '" + recognitionType + "' and pacct.c_acctschema_id = " + accountingSchemaOrg.GetC_AcctSchema_ID() + " And pacct.C_Charge_ID=" + InvoiceLine.GetC_Charge_ID(); } else { sql = "select ad.c_validcombination_id from INT15_AcctSchema_Default ad inner join FRPT_AcctDefault fad on fad.FRPT_AcctDefault_ID = ad.FRPT_AcctDefault_ID" + " where fad.INT15_RecognizeType = '" + recognitionType + "' and ad.c_acctschema_id = " + accountingSchemaOrg.GetC_AcctSchema_ID(); } int ValidCombination_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql)); return(ValidCombination_ID); }
/// <summary> /// Create Invoice. /// </summary> /// <returns>document no</returns> protected override String DoIt() { StringBuilder invDocumentNo = new StringBuilder(); int count = Util.GetValueOfInt(DB.ExecuteScalar(" SELECT Count(*) FROM M_Inout WHERE ISSOTRX='Y' AND M_Inout_ID=" + GetRecord_ID())); MInOut ship = null; bool isAllownonItem = Util.GetValueOfString(GetCtx().GetContext("$AllowNonItem")).Equals("Y"); if (count > 0) { if (_M_InOut_ID == 0) { throw new ArgumentException("No Shipment"); } // ship = new MInOut(GetCtx(), _M_InOut_ID, Get_Trx()); if (ship.Get_ID() == 0) { throw new ArgumentException("Shipment not found"); } if (!MInOut.DOCSTATUS_Completed.Equals(ship.GetDocStatus())) { // JID_0750: done by Bharat on 05 Feb 2019 if Customer Return document and status is not complete it should give message "Customer Return Not Completed". if (ship.IsReturnTrx()) { throw new ArgumentException("Customer Return Not Completed"); } else { throw new ArgumentException("Shipment Not Completed"); } } } else { if (_M_InOut_ID == 0) { throw new ArgumentException("No Material Receipt"); } // ship = new MInOut(GetCtx(), _M_InOut_ID, Get_Trx()); if (ship.Get_ID() == 0) { throw new ArgumentException("Material Receipt not found"); } if (!MInOut.DOCSTATUS_Completed.Equals(ship.GetDocStatus())) { // JID_0750: done by Bharat on 05 Feb 2019 if Return to vendor document and status is not complete it should give message "Return To Vendor Not Completed". if (ship.IsReturnTrx()) { throw new ArgumentException("Return To Vendor Not Completed"); } else { throw new ArgumentException("Material Receipt Not Completed"); } } } // When record contain more than single order and order having different Payment term or Price List then not to generate invoices // JID_0976 - For conversion Type if (ship.GetC_Order_ID() > 0 && Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT COUNT(DISTINCT c_order.m_pricelist_id) + count(distinct c_order.c_paymentterm_id) + count(distinct COALESCE( c_order.C_ConversionType_ID , " + MConversionType.GetDefault(GetAD_Client_ID()) + @")) as recordcount FROM m_inoutline INNER JOIN c_orderline ON m_inoutline.c_orderline_id = c_orderline.c_orderline_id INNER JOIN c_order ON c_order.c_order_id = c_orderline.c_order_id WHERE m_inoutline.m_inout_id = " + _M_InOut_ID + @" GROUP BY m_inoutline.m_inout_id ", null, Get_Trx())) > 3) { if (ship.IsSOTrx()) { //Different Payment Terms, Price list found against the selected orders, use "Generate Invoice" process to create multiple invoices. throw new ArgumentException(Msg.GetMsg(GetCtx(), "VIS_SoDifferentPayAndPrice")); } else { //Different Payment Terms, Price list found against the selected orders throw new ArgumentException(Msg.GetMsg(GetCtx(), "VIS_DifferentPayAndPrice")); } } // Create Invoice Header MInvoice invoice = new MInvoice(ship, null); //Payment Management int _CountVA009 = Env.IsModuleInstalled("VA009_") ? 1 : 0; // Amortization int _CountVA038 = Env.IsModuleInstalled("VA038_") ? 1 : 0; if (_CountVA009 > 0) { int _PaymentMethod_ID = Util.GetValueOfInt(DB.ExecuteScalar("Select VA009_PaymentMethod_ID From C_Order Where C_Order_ID=" + ship.GetC_Order_ID(), null, Get_Trx())); // during consolidation, payment method need to set that is defined on selected business partner. // If not defined on BP then it will set from order int bpPamentMethod_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT " + (ship.IsSOTrx() ? " VA009_PaymentMethod_ID " : " VA009_PO_PaymentMethod_ID ") + @" FROM C_BPartner WHERE C_BPartner_ID = " + ship.GetC_BPartner_ID(), null, Get_Trx())); if (bpPamentMethod_ID != 0) { _PaymentMethod_ID = bpPamentMethod_ID; } if (_PaymentMethod_ID > 0) { invoice.SetVA009_PaymentMethod_ID(_PaymentMethod_ID); } } // Letter Of Credit int _CountVA026 = Env.IsModuleInstalled("VA026_") ? 1 : 0; if (_CountVA026 > 0) { int VA026_LCDetail_ID = Util.GetValueOfInt(DB.ExecuteScalar("Select VA026_LCDetail_ID From C_Order Where C_Order_ID=" + ship.GetC_Order_ID(), null, Get_Trx())); if (VA026_LCDetail_ID > 0) { invoice.SetVA026_LCDetail_ID(VA026_LCDetail_ID); } } //end if (ship.IsReturnTrx()) { if (!ship.IsSOTrx()) { // Purchase Return // set target document from documnet type window -- based on documnet type available on material receipt / return to vendor // JID_0779: Create AP Credit memo if we run the Generate TO process from Returm to Vendor window. //if (invoice.GetC_DocTypeTarget_ID() == 0) //{ if (_C_DocType_ID > 0) { invoice.SetC_DocTypeTarget_ID(_C_DocType_ID); } else { int C_DocTypeTarget_ID = DB.GetSQLValue(null, "SELECT C_DocTypeInvoice_ID FROM C_DocType WHERE C_DocType_ID=@param1", ship.GetC_DocType_ID()); if (C_DocTypeTarget_ID > 0) { invoice.SetC_DocTypeTarget_ID(C_DocTypeTarget_ID); } else { invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO); } } invoice.SetIsReturnTrx(ship.IsReturnTrx()); invoice.SetIsSOTrx(ship.IsSOTrx()); } else { // Sales Return if (_C_DocType_ID > 0) { invoice.SetC_DocTypeTarget_ID(_C_DocType_ID); } else { if (ship.GetC_Order_ID() >= 0) { int C_DocType_ID = Util.GetValueOfInt(DB.ExecuteScalar("Select C_DocType_ID From C_Order Where C_Order_ID=" + ship.GetC_Order_ID(), null, Get_Trx())); MDocType dt = MDocType.Get(GetCtx(), C_DocType_ID); if (dt.GetC_DocTypeInvoice_ID() != 0) { invoice.SetC_DocTypeTarget_ID(dt.GetC_DocTypeInvoice_ID(), true); } else { invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO); } } else { invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO); } } } } if (_M_PriceList_ID != 0) { invoice.SetM_PriceList_ID(_M_PriceList_ID); } //Set InvoiceDocumentNo to InvoiceReference if (_InvoiceDocumentNo != null && _InvoiceDocumentNo.Length > 0) { invoice.Set_Value("InvoiceReference", _InvoiceDocumentNo); } //Set TargetDoctype if (_C_DocType_ID > 0 && !ship.IsReturnTrx()) { invoice.Set_Value("C_DocTypeTarget_ID", _C_DocType_ID); } // Added by Bharat on 30 Jan 2018 to set Inco Term from Order if (invoice.Get_ColumnIndex("C_IncoTerm_ID") > 0) { invoice.SetC_IncoTerm_ID(ship.GetC_IncoTerm_ID()); } //To get Payment Rule and set the Payment method if (invoice.GetPaymentRule() != "") { invoice.SetPaymentMethod(invoice.GetPaymentRule()); } if (!invoice.Save()) { //SI_0708 - message was not upto the mark ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Cannot save Invoice. " + pp.GetName()); } throw new ArgumentException("Cannot save Invoice"); } MInOutLine[] shipLines = ship.GetLines(false); DateTime? AmortStartDate = null; DateTime? AmortEndDate = null; count = 0; DataSet ds = null; DataSet dsDoc = null; for (int i = 0; i < shipLines.Length; i++) { MInOutLine sLine = shipLines[i]; // Changes done by Bharat on 06 July 2017 restrict to create invoice if Invoice already created against that for same quantity string sql = @"SELECT ml.QtyEntered - SUM(COALESCE(li.QtyEntered,0)) as QtyEntered, ml.MovementQty- SUM(COALESCE(li.QtyInvoiced, 0)) as QtyInvoiced" + " FROM M_InOutLine ml INNER JOIN C_InvoiceLine li ON li.M_InOutLine_ID = ml.M_InOutLine_ID INNER JOIN C_Invoice ci ON ci.C_Invoice_ID = li.C_Invoice_ID " + " WHERE ci.DocStatus NOT IN ('VO', 'RE') AND ml.M_InOutLine_ID =" + sLine.GetM_InOutLine_ID() + " GROUP BY ml.MovementQty, ml.QtyEntered"; ds = DB.ExecuteDataset(sql, null, Get_Trx()); if (ds != null && ds.Tables[0].Rows.Count > 0) { decimal qtyEntered = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["QtyEntered"]); decimal qtyInvoiced = Util.GetValueOfDecimal(ds.Tables[0].Rows[0]["QtyInvoiced"]); if (qtyEntered <= 0) { // Getting document number Count if Invoice already generated for Material Receipt string StrSql = "SELECT ci.DocumentNo,li.M_InOutLine_ID FROM C_InvoiceLine li INNER JOIN C_Invoice ci ON ci.C_Invoice_ID = li.C_Invoice_ID " + " WHERE ci.DocStatus NOT IN ('VO', 'RE') AND li.M_InOutLine_ID = " + sLine.GetM_InOutLine_ID(); dsDoc = DB.ExecuteDataset(StrSql, null, Get_Trx()); if (dsDoc != null && dsDoc.Tables[0].Rows.Count > 0) { for (int j = 0; j < dsDoc.Tables[0].Rows.Count; j++) { // JID_1358: Need to show document number in message if Invoice already generated for Material Receipt string no = invDocumentNo.ToString(); if (invDocumentNo.Length > 0 && no != Util.GetValueOfString(dsDoc.Tables[0].Rows[j]["DocumentNo"])) { invDocumentNo.Append(", " + Util.GetValueOfString(dsDoc.Tables[0].Rows[j]["DocumentNo"])); } else { invDocumentNo.Clear(); invDocumentNo.Append(Util.GetValueOfString(dsDoc.Tables[0].Rows[j]["DocumentNo"])); } ds.Dispose(); log.Info("Invoice Line already exist for Receipt Line ID - " + sLine.GetM_InOutLine_ID()); continue; } dsDoc.Dispose(); } } else { MInvoiceLine line = new MInvoiceLine(invoice); line.SetShipLine(sLine); line.SetQtyEntered(qtyEntered); line.SetQtyInvoiced(qtyInvoiced); // Change By Mohit Amortization process ------------- if (_CountVA038 > 0) { if (line.GetM_Product_ID() > 0) { //MProduct pro = new MProduct(GetCtx(), sLine.GetM_Product_ID(), Get_TrxName()); int VA038_AmortizationTemplate_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT VA038_AmortizationTemplate_ID FROM M_Product WHERE M_Product_ID = " + sLine.GetM_Product_ID(), null, Get_Trx())); if (VA038_AmortizationTemplate_ID > 0) { line.Set_Value("VA038_AmortizationTemplate_ID", VA038_AmortizationTemplate_ID); DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + VA038_AmortizationTemplate_ID); AmortStartDate = null; AmortEndDate = null; if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A") { AmortStartDate = invoice.GetDateAcct(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T") { AmortStartDate = invoice.GetDateInvoiced(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M") { AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y") { AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } line.Set_Value("FROMDATE", AmortStartDate); line.Set_Value("EndDate", AmortEndDate); if (amrtDS != null) { amrtDS.Dispose(); } } } if (line.GetC_Charge_ID() > 0) { //MCharge charge = new MCharge(GetCtx(), sLine.GetC_Charge_ID(), Get_TrxName()); int VA038_AmortizationTemplate_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT VA038_AmortizationTemplate_ID FROM C_Charge WHERE C_Charge_ID = " + sLine.GetC_Charge_ID(), null, Get_Trx())); if (VA038_AmortizationTemplate_ID > 0) { line.Set_Value("VA038_AmortizationTemplate_ID", VA038_AmortizationTemplate_ID); DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + VA038_AmortizationTemplate_ID); AmortStartDate = null; AmortEndDate = null; if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A") { AmortStartDate = invoice.GetDateAcct(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T") { AmortStartDate = invoice.GetDateInvoiced(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M") { AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y") { AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } line.Set_Value("FROMDATE", AmortStartDate); line.Set_Value("EndDate", AmortEndDate); if (amrtDS != null) { amrtDS.Dispose(); } } } } // End Change Amortization process-------------- if (!line.Save()) { //SI_0708 - message was not upto the mark ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName()); } throw new ArgumentException("Cannot save Invoice Line"); } count++; } ds.Dispose(); } else { MInvoiceLine line = new MInvoiceLine(invoice); // JID_1850 Avoid the duplicate charge line if (sLine.GetC_Charge_ID() > 0 && (!isAllownonItem || _GenerateCharges)) { continue; } line.SetShipLine(sLine); line.SetQtyEntered(sLine.GetQtyEntered()); line.SetQtyInvoiced(sLine.GetMovementQty()); line.Set_ValueNoCheck("IsDropShip", sLine.Get_Value("IsDropShip")); //Arpit Rai 20-Sept-2017 // Change By Mohit Amortization process ------------- if (_CountVA038 > 0) { if (line.GetM_Product_ID() > 0) { //MProduct pro = new MProduct(GetCtx(), sLine.GetM_Product_ID(), Get_TrxName()); int VA038_AmortizationTemplate_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT VA038_AmortizationTemplate_ID FROM M_Product WHERE M_Product_ID = " + sLine.GetM_Product_ID(), null, Get_Trx())); if (VA038_AmortizationTemplate_ID > 0) { line.Set_Value("VA038_AmortizationTemplate_ID", VA038_AmortizationTemplate_ID); DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + VA038_AmortizationTemplate_ID); AmortStartDate = null; AmortEndDate = null; if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A") { AmortStartDate = invoice.GetDateAcct(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T") { AmortStartDate = invoice.GetDateInvoiced(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M") { AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y") { AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } line.Set_Value("FROMDATE", AmortStartDate); line.Set_Value("EndDate", AmortEndDate); if (amrtDS != null) { amrtDS.Dispose(); } } } if (line.GetC_Charge_ID() > 0) { //MCharge charge = new MCharge(GetCtx(), sLine.GetC_Charge_ID(), Get_TrxName()); int VA038_AmortizationTemplate_ID = Util.GetValueOfInt(DB.ExecuteScalar(@"SELECT VA038_AmortizationTemplate_ID FROM C_Charge WHERE C_Charge_ID = " + sLine.GetC_Charge_ID(), null, Get_Trx())); if (VA038_AmortizationTemplate_ID > 0) { line.Set_Value("VA038_AmortizationTemplate_ID", VA038_AmortizationTemplate_ID); DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + VA038_AmortizationTemplate_ID); AmortStartDate = null; AmortEndDate = null; if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A") { AmortStartDate = invoice.GetDateAcct(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T") { AmortStartDate = invoice.GetDateInvoiced(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M") { AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y") { AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } line.Set_Value("FROMDATE", AmortStartDate); line.Set_Value("EndDate", AmortEndDate); if (amrtDS != null) { amrtDS.Dispose(); } } } } // End Change Amortization process-------------- if (!line.Save()) { //SI_0708 - message was not upto the mark ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName()); } throw new ArgumentException("Cannot save Invoice Line"); } count++; } } #region [Enhancement for Charges Distribution/Generation by Sukhwinder on 13 December 2017] if (_GenerateCharges && count > 0) { StringBuilder OrderSql = new StringBuilder(); OrderSql.Append(" SELECT CO.C_ORDER_ID, " + " SUM(ML.QTYENTERED) AS MRLINEQTY, " + " SUM(OL.QTYENTERED) AS ORDERQTY " + " FROM M_INOUTLINE ML " + " INNER JOIN C_ORDERLINE OL " + " ON OL.C_ORDERLINE_ID = ML.C_ORDERLINE_ID " + " INNER JOIN C_ORDER CO " + " ON CO.C_ORDER_ID = OL.C_ORDER_ID " + " WHERE ML.M_INOUT_ID = " + _M_InOut_ID + " AND (OL.C_CHARGE_ID IS NULL " + " OR OL.C_CHARGE_ID = 0) " + " GROUP BY CO.C_ORDER_ID "); DataSet OrderDS = DB.ExecuteDataset(OrderSql.ToString(), null, Get_Trx()); if (OrderDS != null && OrderDS.Tables[0].Rows.Count > 0) { StringBuilder ChargesSql = new StringBuilder(); for (int index = 0; index < OrderDS.Tables[0].Rows.Count; index++) { ds = null; ChargesSql.Clear(); ChargesSql.Append(" SELECT C_CHARGE_ID, " + " C_ORDERLINE_ID, " + " C_ORDER_ID, " + " C_CURRENCY_ID, " + " PRICEENTERED, " + " PRICEACTUAL, " + " LINENETAMT, " + " QTYENTERED, " + " C_UOM_ID, " + " C_Tax_ID, " + " IsDropShip " + " FROM C_ORDERLINE " + " WHERE C_ORDER_ID IN " + " ( " + Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["C_ORDER_ID"]) + " ) " + " AND C_CHARGE_ID IS NOT NULL " + " AND C_CHARGE_ID > 0 "); ds = DB.ExecuteDataset(ChargesSql.ToString(), null, Get_Trx()); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { MInvoiceLine line = new MInvoiceLine(invoice); line.SetQty(1); line.SetQtyEntered(1); line.SetQtyInvoiced(1); line.SetOrderLine(new MOrderLine(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_ORDERLINE_ID"]), Get_Trx())); line.SetC_Charge_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_CHARGE_ID"])); line.SetC_UOM_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_UOM_ID"])); line.SetC_Tax_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_TAX_ID"])); decimal SumOfQty = 0; if (Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["ORDERQTY"]) == 0) { SumOfQty = 1; } else { SumOfQty = Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["ORDERQTY"]); } decimal amt = (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["LINENETAMT"]) / SumOfQty) * Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["MRLINEQTY"]); line.SetPrice(Decimal.Round(amt, 3)); line.SetPriceActual(Decimal.Round(amt, 3)); line.SetPriceEntered(Decimal.Round(amt, 3)); line.SetLineNetAmt(Decimal.Round(amt, 3)); line.Set_ValueNoCheck("IsDropShip", Util.GetValueOfString(ds.Tables[0].Rows[i]["ISDROPSHIP"])); if (_CountVA038 > 0) { if (line.GetC_Charge_ID() > 0) { MCharge charge = new MCharge(GetCtx(), line.GetC_Charge_ID(), Get_TrxName()); if (Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")) > 0) { line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID"))); DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID"))); AmortStartDate = null; AmortEndDate = null; if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A") { AmortStartDate = invoice.GetDateAcct(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T") { AmortStartDate = invoice.GetDateInvoiced(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M") { AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y") { AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } line.Set_Value("FROMDATE", AmortStartDate); line.Set_Value("EndDate", AmortEndDate); if (amrtDS != null) { amrtDS.Dispose(); } } } } if (!line.Save()) { //SI_0708 - message was not upto the mark ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName()); } throw new ArgumentException("Cannot save Invoice Line"); } } } } } } #endregion if (count > 0) { return(invoice.GetDocumentNo()); } else { //Get_Trx().Rollback(); throw new ArgumentException(Msg.GetMsg(GetCtx(), "InvoiceExist") + ": " + invDocumentNo.ToString()); } }
/// <summary> /// Create Shipment /// </summary> /// <returns>info</returns> protected override String DoIt() { //log.info("C_Invoice_ID=" + _C_Invoice_ID // + ", M_Warehouse_ID=" + _M_Warehouse_ID // + ", C_DocType_ID=" + _C_DocType_ID); if (_C_Invoice_ID == 0) { throw new ArgumentException("@NotFound@ @C_Invoice_ID@"); } if (_M_Warehouse_ID == 0) { throw new ArgumentException("@NotFound@ @M_Warehouse_ID@"); } // MInvoice invoice = new MInvoice(GetCtx(), _C_Invoice_ID, Get_Trx()); if (invoice.Get_ID() == 0) { throw new ArgumentException("@NotFound@ @C_Invoice_ID@"); } if (!MInvoice.DOCSTATUS_Completed.Equals(invoice.GetDocStatus())) { throw new ArgumentException("@InvoiceCreateDocNotCompleted@"); } MDocType dt = MDocType.Get(GetCtx(), _C_DocType_ID); if (invoice.IsSOTrx() != dt.IsSOTrx() || invoice.IsReturnTrx() != dt.IsReturnTrx()) { throw new ArgumentException("@C_DocType_ID@ <> @C_Invoice_ID@"); } //*****************************Vikas 1 Dec 2015 ********************************* //Case Msg Not Showing Proper MInOut ship = null; MOrder ord = new MOrder(GetCtx(), invoice.GetC_Order_ID(), null); if (ord.GetC_BPartner_ID() > 0) { ship = new MInOut(invoice, _C_DocType_ID, null, _M_Warehouse_ID); // Change by Mohit Asked by Amardeep sir 02/03/2016 ship.SetPOReference(invoice.GetPOReference()); // End if (!ship.Save()) { return(GetRetrievedError(ship, "@SaveError@ Receipt")); // throw new ArgumentException("@SaveError@ Receipt"); } } else { return(GetRetrievedError(ship, "InvoiceNotLinkedWithPO")); //throw new ArgumentException("@InvoiceNotLinkedWithPO@"); } /* * MInOut ship = new MInOut(invoice, _C_DocType_ID, null, _M_Warehouse_ID); * if (!ship.Save()) * { * throw new ArgumentException("@SaveError@ Receipt"); * } */ //************************END***************************************** MInvoiceLine[] invoiceLines = invoice.GetLines(false); for (int i = 0; i < invoiceLines.Length; i++) { MInvoiceLine invoiceLine = invoiceLines[i]; MProduct product = invoiceLine.GetProduct(); // Nothing to Deliver // Get the lines of Invoice based on the setting taken on Tenant to allow non item Product if (Util.GetValueOfString(GetCtx().GetContext("$AllowNonItem")).Equals("N") && ((product != null && product.GetProductType() != MProduct.PRODUCTTYPE_Item) || invoiceLine.GetC_Charge_ID() != 0)) { continue; } MInOutLine sLine = new MInOutLine(ship); //JID_1679 Generate Receipt from Invoice(Vendor) for remaining quantity //decimal movementqty = 0; //if (invoiceLine.GetC_OrderLine_ID() != 0) //{ // decimal? res = 0; // movementqty = Util.GetValueOfDecimal(DB.ExecuteScalar(@" select (QtyOrdered-sum(MovementQty)) from C_OrderLine ol Inner join M_InOutLine il on il.C_orderline_ID= ol.C_Orderline_Id " // + " WHERE il.C_OrderLine_ID =" + invoiceLine.GetC_OrderLine_ID() + "group by QtyOrdered", null, Get_Trx())); // // in case of partial receipt // if (invoiceLine.GetQtyInvoiced() > movementqty && movementqty != 0) // { // if (product.GetC_UOM_ID() != invoiceLine.GetC_UOM_ID()) // { // res = MUOMConversion.ConvertProductTo(GetCtx(), product.GetM_Product_ID(), invoiceLine.GetC_UOM_ID(), movementqty); // } // sLine.SetInvoiceLine(invoiceLine, 0, // Locator // invoice.IsSOTrx() ? (movementqty) : Env.ZERO); // sLine.SetQtyEntered(res == 0 ? (movementqty) : res); // sLine.SetMovementQty(movementqty); // } // // if QtyInvoiced is less or No Material receipt is found against the order // else // { // sLine.SetInvoiceLine(invoiceLine, 0, // Locator // invoice.IsSOTrx() ? invoiceLine.GetQtyInvoiced() : Env.ZERO); // sLine.SetQtyEntered(invoiceLine.GetQtyEntered()); // sLine.SetMovementQty(invoiceLine.GetQtyInvoiced()); // } //} //else //{ sLine.SetInvoiceLine(invoiceLine, 0, // Locator invoice.IsSOTrx() ? invoiceLine.GetQtyInvoiced() : Env.ZERO); sLine.SetQtyEntered(invoiceLine.GetQtyEntered()); sLine.SetMovementQty(invoiceLine.GetQtyInvoiced()); //} if (invoice.IsCreditMemo()) { sLine.SetQtyEntered(Decimal.Negate(sLine.GetQtyEntered())); //.negate()); sLine.SetMovementQty(Decimal.Negate(sLine.GetMovementQty())); //.negate()); } if (!sLine.Save()) { ship.Get_Trx().Rollback(); //if (movementqty == 0) //{ // _processMsg += ", LineNo: " + invoiceLine.GetLine() + Msg.GetMsg(GetCtx(), "MRIsAlreadyCreated"); // return _processMsg; //} //else //{ return(GetRetrievedError(sLine, "@SaveError@ @M_InOutLine_ID@")); //} // throw new ArgumentException("@SaveError@ @M_InOutLine_ID@"); } invoiceLine.SetM_InOutLine_ID(sLine.GetM_InOutLine_ID()); // _processMsg+= ", LineNo: "+invoiceLine.GetLine()+Msg.GetMsg(GetCtx(), "MRCreatedWithDocNo" + ship.GetDocumentNo()); if (!invoiceLine.Save()) { return(GetRetrievedError(invoiceLine, "@SaveError@ @C_InvoiceLine_ID@")); //throw new ArgumentException("@SaveError@ @C_InvoiceLine_ID@"); } } return(ship.GetDocumentNo()); }
/// <summary> /// Create Shipment /// </summary> /// <returns>info</returns> protected override String DoIt() { //log.info("C_Invoice_ID=" + _C_Invoice_ID // + ", M_Warehouse_ID=" + _M_Warehouse_ID // + ", C_DocType_ID=" + _C_DocType_ID); if (_C_Invoice_ID == 0) { throw new ArgumentException("@NotFound@ @C_Invoice_ID@"); } if (_M_Warehouse_ID == 0) { throw new ArgumentException("@NotFound@ @M_Warehouse_ID@"); } // MInvoice invoice = new MInvoice(GetCtx(), _C_Invoice_ID, null); if (invoice.Get_ID() == 0) { throw new ArgumentException("@NotFound@ @C_Invoice_ID@"); } if (!MInvoice.DOCSTATUS_Completed.Equals(invoice.GetDocStatus())) { throw new ArgumentException("@InvoiceCreateDocNotCompleted@"); } MDocType dt = MDocType.Get(GetCtx(), _C_DocType_ID); if (invoice.IsSOTrx() != dt.IsSOTrx() || invoice.IsReturnTrx() != dt.IsReturnTrx()) { throw new ArgumentException("@C_DocType_ID@ <> @C_Invoice_ID@"); } //*****************************Vikas 1 Dec 2015 ********************************* //Case Msg Not Showing Proper MInOut ship = null; MOrder ord = new MOrder(GetCtx(), invoice.GetC_Order_ID(), null); if (ord.GetC_BPartner_ID() > 0) { ship = new MInOut(invoice, _C_DocType_ID, null, _M_Warehouse_ID); // Change by Mohit Asked by Amardeep sir 02/03/2016 ship.SetPOReference(invoice.GetPOReference()); // End if (!ship.Save()) { return(GetRetrievedError(ship, "@SaveError@ Receipt")); // throw new ArgumentException("@SaveError@ Receipt"); } } else { return(GetRetrievedError(ship, "InvoiceNotLinkedWithPO")); //throw new ArgumentException("@InvoiceNotLinkedWithPO@"); } /* * MInOut ship = new MInOut(invoice, _C_DocType_ID, null, _M_Warehouse_ID); * if (!ship.Save()) * { * throw new ArgumentException("@SaveError@ Receipt"); * } */ //************************END***************************************** MInvoiceLine[] invoiceLines = invoice.GetLines(false); for (int i = 0; i < invoiceLines.Length; i++) { MInvoiceLine invoiceLine = invoiceLines[i]; MProduct product = invoiceLine.GetProduct(); // Nothing to Deliver // Get the lines of Invoice based on the setting taken on Tenant to allow non item Product if (Util.GetValueOfString(GetCtx().GetContext("$AllowNonItem")).Equals("N") && ((product != null && product.GetProductType() != MProduct.PRODUCTTYPE_Item) || invoiceLine.GetC_Charge_ID() != 0)) { continue; } MInOutLine sLine = new MInOutLine(ship); sLine.SetInvoiceLine(invoiceLine, 0, // Locator invoice.IsSOTrx() ? invoiceLine.GetQtyInvoiced() : Env.ZERO); sLine.SetQtyEntered(invoiceLine.GetQtyEntered()); sLine.SetMovementQty(invoiceLine.GetQtyInvoiced()); if (invoice.IsCreditMemo()) { sLine.SetQtyEntered(Decimal.Negate(sLine.GetQtyEntered())); //.negate()); sLine.SetMovementQty(Decimal.Negate(sLine.GetMovementQty())); //.negate()); } if (!sLine.Save()) { return(GetRetrievedError(sLine, "@SaveError@ @M_InOutLine_ID@")); //throw new ArgumentException("@SaveError@ @M_InOutLine_ID@"); } // invoiceLine.SetM_InOutLine_ID(sLine.GetM_InOutLine_ID()); if (!invoiceLine.Save()) { return(GetRetrievedError(invoiceLine, "@SaveError@ @C_InvoiceLine_ID@")); //throw new ArgumentException("@SaveError@ @C_InvoiceLine_ID@"); } } return(ship.GetDocumentNo()); }
/// <summary> /// Create Gl Journal Line /// </summary> /// <param name="Journal">GL Journal</param> /// <param name="Invoice">Invoice</param> /// <param name="InvoiceLine">Invoice Line</param> /// <param name="RevenueRecognitionPlan">Revenue Recognition Plan</param> /// <param name="TotalAmt">Sum of RecognizeAmt</param> /// <param name="RecognitionType">Recognition Type</param> /// <param name="k">loop variable</param> /// <returns>>Journal line object</returns> public MJournalLine GenerateJounalLine(MJournal Journal, MInvoice Invoice, MInvoiceLine InvoiceLine, MRevenueRecognitionPlan RevenueRecognitionPlan, Decimal TotalAmt, string RecognitionType, int k) { int combination_ID = 0; if (k == 0) { combination_ID = RevenueRun.GetCombinationID(InvoiceLine.GetM_Product_ID(), InvoiceLine.GetC_Charge_ID(), journal.GetC_AcctSchema_ID(), Invoice.IsSOTrx(), Invoice.IsReturnTrx(), totalAmt, RevenueRecognitionPlan.GetC_RevenueRecognition_ID()); journalLine.SetLine(lineno); if (RecognitionType.Equals("E") && TotalAmt > 0) { journalLine.SetAmtAcctDr(TotalAmt); journalLine.SetAmtSourceDr(TotalAmt); journalLine.SetAmtSourceCr(0); journalLine.SetAmtAcctCr(0); } else if (RecognitionType.Equals("E") && TotalAmt < 0) { journalLine.SetAmtAcctCr(Decimal.Negate(TotalAmt)); journalLine.SetAmtSourceCr(Decimal.Negate(TotalAmt)); journalLine.SetAmtSourceDr(0); journalLine.SetAmtAcctDr(0); } else if (RecognitionType.Equals("R") && TotalAmt > 0) { journalLine.SetAmtAcctCr(TotalAmt); journalLine.SetAmtSourceCr(TotalAmt); journalLine.SetAmtSourceDr(0); journalLine.SetAmtAcctDr(0); } else if (RecognitionType.Equals("R") && TotalAmt < 0) { journalLine.SetAmtAcctDr(Decimal.Negate(TotalAmt)); journalLine.SetAmtSourceDr(Decimal.Negate(TotalAmt)); journalLine.SetAmtSourceCr(0); journalLine.SetAmtAcctCr(0); } int account_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT Account_ID From C_ValidCombination Where C_ValidCombination_ID=" + combination_ID)); journalLine.Set_ValueNoCheck("Account_ID", account_ID); journalLine.Set_ValueNoCheck("C_BPartner_ID", Invoice.GetC_BPartner_ID()); journalLine.SetAD_OrgTrx_ID(InvoiceLine.Get_ColumnIndex("AD_OrgTrx_ID") > 0 ? InvoiceLine.GetAD_OrgTrx_ID() : Invoice.GetAD_OrgTrx_ID()); journalLine.Set_ValueNoCheck("C_Project_ID", InvoiceLine.GetC_Project_ID() > 0 ? InvoiceLine.GetC_Project_ID() : Invoice.Get_Value("C_ProjectRef_ID")); journalLine.Set_ValueNoCheck("C_Campaign_ID", InvoiceLine.Get_ColumnIndex("C_Campaign_ID") > 0 ? InvoiceLine.GetC_Campaign_ID() : Invoice.GetC_Campaign_ID()); journalLine.Set_ValueNoCheck("C_Activity_ID", InvoiceLine.Get_ColumnIndex("C_Activity_ID") > 0 ? InvoiceLine.GetC_Activity_ID() : Invoice.GetC_Activity_ID()); journalLine.Set_ValueNoCheck("M_Product_ID", InvoiceLine.GetM_Product_ID()); } else { combination_ID = RevenueRun.GetCombinationID(0, 0, RevenueRecognitionPlan.GetC_AcctSchema_ID(), Invoice.IsSOTrx(), Invoice.IsReturnTrx(), totalAmt, RevenueRecognitionPlan.GetC_RevenueRecognition_ID()); int account_ID = Util.GetValueOfInt(DB.ExecuteScalar("SELECT Account_ID From C_ValidCombination Where C_ValidCombination_ID=" + combination_ID)); journalLine = RevenueRun.GetOrCreate(journal, journalLine, InvoiceLine.GetM_Product_ID(), InvoiceLine.GetC_Charge_ID(), InvoiceLine.Get_ColumnIndex("C_Campaign_ID") > 0 ? InvoiceLine.GetC_Campaign_ID() : Invoice.GetC_Campaign_ID(), account_ID, InvoiceLine.GetC_Project_ID() > 0 ? InvoiceLine.GetC_Project_ID() : Util.GetValueOfInt(Invoice.Get_Value("C_ProjectRef_ID")), InvoiceLine.Get_ColumnIndex("C_Activity_ID") > 0 ? InvoiceLine.GetC_Activity_ID() : Invoice.GetC_Activity_ID(), Invoice.GetC_BPartner_ID(), Invoice.GetAD_Org_ID(), InvoiceLine.Get_ColumnIndex("AD_OrgTrx_ID") > 0 ? InvoiceLine.GetAD_OrgTrx_ID() : Invoice.GetAD_OrgTrx_ID()); journalLine.SetLine(lineno); if (RecognitionType.Equals("E") && TotalAmt > 0) { journalLine.SetAmtAcctCr(journalLine.GetAmtAcctCr() + TotalAmt); journalLine.SetAmtSourceCr(journalLine.GetAmtSourceCr() + TotalAmt); journalLine.SetAmtSourceDr(0); journalLine.SetAmtAcctDr(0); } else if (RecognitionType.Equals("E") && TotalAmt < 0) { journalLine.SetAmtAcctDr(journalLine.GetAmtAcctDr() + Decimal.Negate(TotalAmt)); journalLine.SetAmtSourceDr(journalLine.GetAmtSourceDr() + Decimal.Negate(TotalAmt)); journalLine.SetAmtSourceCr(0); journalLine.SetAmtAcctCr(0); } else if (RecognitionType.Equals("R") && TotalAmt > 0) { journalLine.SetAmtAcctDr(journalLine.GetAmtAcctDr() + TotalAmt); journalLine.SetAmtSourceDr(journalLine.GetAmtSourceDr() + TotalAmt); journalLine.SetAmtSourceCr(0); journalLine.SetAmtAcctCr(0); } else if (RecognitionType.Equals("R") && TotalAmt < 0) { journalLine.SetAmtAcctCr(journalLine.GetAmtAcctCr() + Decimal.Negate(TotalAmt)); journalLine.SetAmtSourceCr(journalLine.GetAmtSourceCr() + Decimal.Negate(TotalAmt)); journalLine.SetAmtSourceDr(0); journalLine.SetAmtAcctDr(0); } } return(journalLine); }
/// <summary> /// Create Invoice. /// </summary> /// <returns>document no</returns> protected override String DoIt() { //log.info("M_InOut_ID=" + _M_InOut_ID // + ", M_PriceList_ID=" + _M_PriceList_ID // + ", InvoiceDocumentNo=" + _InvoiceDocumentNo); // Message Display Wrong Done Vikas and Assigned by Gurinder int count = Util.GetValueOfInt(DB.ExecuteScalar(" SELECT Count(*) FROM M_Inout WHERE ISSOTRX='Y' AND M_Inout_ID=" + GetRecord_ID())); MInOut ship = null; if (count > 0) { if (_M_InOut_ID == 0) { throw new ArgumentException("No Shipment"); } // ship = new MInOut(GetCtx(), _M_InOut_ID, Get_Trx()); if (ship.Get_ID() == 0) { throw new ArgumentException("Shipment not found"); } if (!MInOut.DOCSTATUS_Completed.Equals(ship.GetDocStatus())) { throw new ArgumentException("Shipment not completed"); } } else { if (_M_InOut_ID == 0) { throw new ArgumentException("No Material Receipt"); } // ship = new MInOut(GetCtx(), _M_InOut_ID, Get_Trx()); if (ship.Get_ID() == 0) { throw new ArgumentException("Material Receipt not found"); } if (!MInOut.DOCSTATUS_Completed.Equals(ship.GetDocStatus())) { throw new ArgumentException("Material Receipt not completed"); } } //***********************END*****************************// MInvoice invoice = new MInvoice(ship, null); //-------------Column Added by Anuj---------------------- //int _CountVA009 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA009_' AND IsActive = 'Y'")); int _CountVA009 = Env.IsModuleInstalled("VA009_") ? 1 : 0; // Code by Mohit Amortization process //int _CountVA038 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA038_' AND IsActive = 'Y'")); int _CountVA038 = Env.IsModuleInstalled("VA038_") ? 1 : 0; // End Amortization Code if (_CountVA009 > 0) { int _PaymentMethod_ID = Util.GetValueOfInt(DB.ExecuteScalar("Select VA009_PaymentMethod_ID From C_Order Where C_Order_ID=" + ship.GetC_Order_ID())); if (_PaymentMethod_ID > 0) { invoice.SetVA009_PaymentMethod_ID(_PaymentMethod_ID); } } //-------------Column Added by Anuj---------------------- // added by Amit 26-may-2016 //int _CountVA026 = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(AD_MODULEINFO_ID) FROM AD_MODULEINFO WHERE PREFIX='VA026_' AND IsActive = 'Y'")); int _CountVA026 = Env.IsModuleInstalled("VA026_") ? 1 : 0; if (_CountVA026 > 0) { MOrder order = new MOrder(GetCtx(), ship.GetC_Order_ID(), Get_Trx()); if (order != null && order.GetC_Order_ID() > 0) { invoice.SetVA026_LCDetail_ID(order.GetVA026_LCDetail_ID()); } } //end if (ship.IsReturnTrx()) { if (!ship.IsSOTrx()) { // Purchase Return // set target document from documnet type window -- based on documnet type available on material receipt / return to vendor if (invoice.GetC_DocTypeTarget_ID() == 0) { int C_DocTypeTarget_ID = DB.GetSQLValue(null, "SELECT C_DocTypeInvoice_ID FROM C_DocType WHERE C_DocType_ID=@param1", ship.GetC_DocType_ID()); if (C_DocTypeTarget_ID >= 0) { invoice.SetC_DocTypeTarget_ID(C_DocTypeTarget_ID); } else { invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO); } } invoice.SetIsReturnTrx(ship.IsReturnTrx()); invoice.SetIsSOTrx(ship.IsSOTrx()); } else { // Sales Return if (ship.GetC_Order_ID() >= 0) { MOrder order = new MOrder(GetCtx(), ship.GetC_Order_ID(), Get_Trx()); MDocType dt = MDocType.Get(GetCtx(), order.GetC_DocType_ID()); if (dt.GetC_DocTypeInvoice_ID() != 0) { invoice.SetC_DocTypeTarget_ID(dt.GetC_DocTypeInvoice_ID(), true); } else { invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO); } } else { invoice.SetC_DocTypeTarget_ID(ship.IsSOTrx() ? MDocBaseType.DOCBASETYPE_ARCREDITMEMO : MDocBaseType.DOCBASETYPE_APCREDITMEMO); } } } if (_M_PriceList_ID != 0) { invoice.SetM_PriceList_ID(_M_PriceList_ID); } if (_InvoiceDocumentNo != null && _InvoiceDocumentNo.Length > 0) { invoice.SetDocumentNo(_InvoiceDocumentNo); } // Added by Bharat on 30 Jan 2018 to set Inco Term from Order if (invoice.Get_ColumnIndex("C_IncoTerm_ID") > 0) { invoice.SetC_IncoTerm_ID(ship.GetC_IncoTerm_ID()); } if (!invoice.Save()) { //SI_0708 - message was not upto the mark ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Cannot save Invoice. " + pp.GetName()); } throw new ArgumentException("Cannot save Invoice"); //return GetReterivedError(invoice, "Cannot save Invoice"); } MInOutLine[] shipLines = ship.GetLines(false); DateTime? AmortStartDate = null; DateTime? AmortEndDate = null; count = 0; DataSet ds = null; for (int i = 0; i < shipLines.Length; i++) { MInOutLine sLine = shipLines[i]; // Changes done by Bharat on 06 July 2017 restrict to create invoice if Invoice already created against that for same quantity string sql = @"SELECT ml.QtyEntered - SUM(COALESCE(li.QtyEntered,0)) as QtyEntered, ml.MovementQty-SUM(COALESCE(li.QtyInvoiced,0)) as QtyInvoiced FROM M_InOutLine ml INNER JOIN C_InvoiceLine li ON li.M_InOutLine_ID = ml.M_InOutLine_ID INNER JOIN C_Invoice ci ON ci.C_Invoice_ID = li.C_Invoice_ID WHERE ci.DocStatus NOT IN ('VO', 'RE') AND ml.M_InOutLine_ID =" + sLine.GetM_InOutLine_ID() + " GROUP BY ml.MovementQty, ml.QtyEntered"; ds = DB.ExecuteDataset(sql, null, Get_Trx()); if (ds != null && ds.Tables[0].Rows.Count > 0) { decimal qtyEntered = Util.GetValueOfDecimal(ds.Tables[0].Rows[0][0]); decimal qtyInvoiced = Util.GetValueOfDecimal(ds.Tables[0].Rows[0][1]); if (qtyEntered <= 0) { ds.Dispose(); log.Info("Invoice Line already exist for Receipt Line ID - " + sLine.GetM_InOutLine_ID()); continue; } else { MInvoiceLine line = new MInvoiceLine(invoice); line.SetShipLine(sLine); //line.SetQtyEntered(sLine.GetQtyEntered()); //line.SetQtyInvoiced(sLine.GetMovementQty()); line.SetQtyEntered(qtyEntered); line.SetQtyInvoiced(qtyInvoiced); // Change By Mohit Amortization process ------------- if (_CountVA038 > 0) { if (line.GetM_Product_ID() > 0) { MProduct pro = new MProduct(GetCtx(), sLine.GetM_Product_ID(), Get_TrxName()); if (Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID")) > 0) { line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID"))); DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID"))); AmortStartDate = null; AmortEndDate = null; if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A") { AmortStartDate = invoice.GetDateAcct(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T") { AmortStartDate = invoice.GetDateInvoiced(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M") { AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y") { AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } line.Set_Value("FROMDATE", AmortStartDate); line.Set_Value("EndDate", AmortEndDate); if (amrtDS != null) { amrtDS.Dispose(); } } } if (line.GetC_Charge_ID() > 0) { MCharge charge = new MCharge(GetCtx(), sLine.GetC_Charge_ID(), Get_TrxName()); if (Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")) > 0) { line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID"))); DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID"))); AmortStartDate = null; AmortEndDate = null; if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A") { AmortStartDate = invoice.GetDateAcct(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T") { AmortStartDate = invoice.GetDateInvoiced(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M") { AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y") { AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } line.Set_Value("FROMDATE", AmortStartDate); line.Set_Value("EndDate", AmortEndDate); if (amrtDS != null) { amrtDS.Dispose(); } } } } // End Change Amortization process-------------- if (!line.Save()) { //return GetReterivedError(line, "Cannot save Invoice Line"); //SI_0708 - message was not upto the mark ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName()); } throw new ArgumentException("Cannot save Invoice Line"); } count++; } ds.Dispose(); } else { MInvoiceLine line = new MInvoiceLine(invoice); line.SetShipLine(sLine); line.SetQtyEntered(sLine.GetQtyEntered()); line.SetQtyInvoiced(sLine.GetMovementQty()); line.Set_ValueNoCheck("IsDropShip", sLine.Get_Value("IsDropShip")); //Arpit Rai 20-Sept-2017 // Change By Mohit Amortization process ------------- if (_CountVA038 > 0) { if (line.GetM_Product_ID() > 0) { MProduct pro = new MProduct(GetCtx(), sLine.GetM_Product_ID(), Get_TrxName()); if (Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID")) > 0) { line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID"))); DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(pro.Get_Value("VA038_AmortizationTemplate_ID"))); AmortStartDate = null; AmortEndDate = null; if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A") { AmortStartDate = invoice.GetDateAcct(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T") { AmortStartDate = invoice.GetDateInvoiced(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M") { AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y") { AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } line.Set_Value("FROMDATE", AmortStartDate); line.Set_Value("EndDate", AmortEndDate); if (amrtDS != null) { amrtDS.Dispose(); } } } if (line.GetC_Charge_ID() > 0) { MCharge charge = new MCharge(GetCtx(), sLine.GetC_Charge_ID(), Get_TrxName()); if (Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")) > 0) { line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID"))); DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID"))); AmortStartDate = null; AmortEndDate = null; if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A") { AmortStartDate = invoice.GetDateAcct(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T") { AmortStartDate = invoice.GetDateInvoiced(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M") { AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y") { AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } line.Set_Value("FROMDATE", AmortStartDate); line.Set_Value("EndDate", AmortEndDate); if (amrtDS != null) { amrtDS.Dispose(); } } } } // End Change Amortization process-------------- if (!line.Save()) { //return GetReterivedError(line, "Cannot save Invoice Line"); //SI_0708 - message was not upto the mark ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName()); } throw new ArgumentException("Cannot save Invoice Line"); } count++; } } #region [Enhancement for Charges Distribution/Generation by Sukhwinder on 13 December 2017] if (_GenerateCharges && count > 0) { StringBuilder OrderSql = new StringBuilder(); OrderSql.Append(" SELECT CO.C_ORDER_ID, " + " SUM(ML.QTYENTERED) AS MRLINEQTY, " + " SUM(OL.QTYENTERED) AS ORDERQTY, " + " SUM(OL.QTYENTERED *OL.PRICEENTERED) AS LINEAMT, " + " SUM(ML.QTYENTERED*OL.PRICEENTERED) AS MRLINEVALUE " + " FROM M_INOUTLINE ML " + " INNER JOIN C_ORDERLINE OL " + " ON OL.C_ORDERLINE_ID = ML.C_ORDERLINE_ID " + " INNER JOIN C_ORDER CO " + " ON CO.C_ORDER_ID = OL.C_ORDER_ID " + " WHERE ML.M_INOUT_ID = " + _M_InOut_ID + " AND (OL.C_CHARGE_ID IS NULL " + " OR OL.C_CHARGE_ID = 0) " + " GROUP BY CO.C_ORDER_ID "); DataSet OrderDS = DB.ExecuteDataset(OrderSql.ToString(), null, Get_Trx()); if (OrderDS != null && OrderDS.Tables[0].Rows.Count > 0) { StringBuilder ChargesSql = new StringBuilder(); for (int index = 0; index < OrderDS.Tables[0].Rows.Count; index++) { ds = null; ChargesSql.Clear(); ChargesSql.Append(" SELECT C_CHARGE_ID, " + " C_ORDERLINE_ID, " + " C_ORDER_ID, " + " C_CURRENCY_ID, " + " PRICEENTERED, " + " PRICEACTUAL, " + " LINENETAMT, " + " QTYENTERED, " + " C_UOM_ID, " + " C_Tax_ID, " + " IsDropShip " + " FROM C_ORDERLINE " + " WHERE C_ORDER_ID IN " + " ( " + Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["C_ORDER_ID"]) + " ) " + " AND C_CHARGE_ID IS NOT NULL " + " AND C_CHARGE_ID > 0 "); ds = DB.ExecuteDataset(ChargesSql.ToString(), null, Get_Trx()); if (ds != null && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { MInvoiceLine line = new MInvoiceLine(invoice); line.SetQty(1); line.SetQtyEntered(1); line.SetQtyInvoiced(1); line.SetOrderLine(new MOrderLine(GetCtx(), Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_ORDERLINE_ID"]), Get_Trx())); line.SetC_Charge_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_CHARGE_ID"])); line.SetC_UOM_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_UOM_ID"])); line.SetC_Tax_ID(Util.GetValueOfInt(ds.Tables[0].Rows[i]["C_TAX_ID"])); decimal SumOfQty = 0; decimal SumOfvalue = 0; decimal sototval = 0; string sql = "SELECT SUM(OL.QTYENTERED *OL.PRICEENTERED) AS LINEAMT from C_ORDER CO,C_ORDERLINE OL WHERE CO.C_ORDER_ID=OL.C_ORDER_ID AND (OL.C_CHARGE_ID IS NULL OR OL.C_CHARGE_ID = 0) and CO.C_ORDER_ID =" + ship.GetC_Order_ID(); sototval = VAdvantage.Utility.Util.GetValueOfDecimal(DB.ExecuteScalar(sql)); if (Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["ORDERQTY"]) == 0) { SumOfQty = 1; } else { SumOfQty = Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["ORDERQTY"]); SumOfvalue = Util.GetValueOfDecimal(OrderDS.Tables[0].Rows[index]["LINEAMT"]); } //decimal amt = (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["LINENETAMT"]) / SumOfQty) * Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["MRLINEQTY"]); decimal amt = (Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["LINENETAMT"]) / sototval) * Util.GetValueOfInt(OrderDS.Tables[0].Rows[index]["MRLINEVALUE"]); line.SetPrice(Decimal.Round(amt, 3)); line.SetPriceActual(Decimal.Round(amt, 3)); line.SetPriceEntered(Decimal.Round(amt, 3)); line.SetLineNetAmt(Decimal.Round(amt, 3)); line.Set_ValueNoCheck("IsDropShip", Util.GetValueOfString(ds.Tables[0].Rows[i]["ISDROPSHIP"])); if (_CountVA038 > 0) { if (line.GetC_Charge_ID() > 0) { MCharge charge = new MCharge(GetCtx(), line.GetC_Charge_ID(), Get_TrxName()); if (Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID")) > 0) { line.Set_Value("VA038_AmortizationTemplate_ID", Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID"))); DataSet amrtDS = DB.ExecuteDataset("SELECT VA038_AmortizationType,VA038_AmortizationPeriod,VA038_TermSource,VA038_PeriodType,Name FROM VA038_AmortizationTemplate WHERE IsActive='Y' AND VA038_AMORTIZATIONTEMPLATE_ID=" + Util.GetValueOfInt(charge.Get_Value("VA038_AmortizationTemplate_ID"))); AmortStartDate = null; AmortEndDate = null; if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "A") { AmortStartDate = invoice.GetDateAcct(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_TermSource"]) == "T") { AmortStartDate = invoice.GetDateInvoiced(); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "M") { AmortEndDate = AmortStartDate.Value.AddMonths(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } if (Util.GetValueOfString(amrtDS.Tables[0].Rows[0]["VA038_PeriodType"]) == "Y") { AmortEndDate = AmortStartDate.Value.AddYears(Util.GetValueOfInt(amrtDS.Tables[0].Rows[0]["VA038_AmortizationPeriod"])); } line.Set_Value("FROMDATE", AmortStartDate); line.Set_Value("EndDate", AmortEndDate); if (amrtDS != null) { amrtDS.Dispose(); } } } } if (!line.Save()) { //return GetReterivedError(line, "Cannot save Invoice Line"); //SI_0708 - message was not upto the mark ValueNamePair pp = VAdvantage.Logging.VLogger.RetrieveError(); if (pp != null) { throw new ArgumentException("Cannot save Invoice Line. " + pp.GetName()); } throw new ArgumentException("Cannot save Invoice Line"); } } } } } } #endregion if (count > 0) { return(invoice.GetDocumentNo()); } else { Get_Trx().Rollback(); return(Msg.GetMsg(GetCtx(), "InvoiceExist")); } }