/// <summary> /// Process /// </summary> /// <returns></returns> protected override string DoIt() { try { MRevenueRecognitionPlan revenueRecognitionPlan = null; MInvoiceLine invoiceLine = null; MInvoice invoice = null; MRevenueRecognition mRevenueRecognition = new MRevenueRecognition(GetCtx(), _RevenueRecognition_ID, Get_Trx()); // Count of previous date's RevenueRecognition_Run whose journal is not created string sql = "SELECT COUNT(C_RevenueRecognition_Run_ID) FROM C_RevenueRecognition_Run run " + "INNER JOIN C_RevenueRecognition_Plan pl ON pl.C_RevenueRecognition_Plan_ID = run.C_RevenueRecognition_Plan_ID WHERE "; if (C_InvoiceLine_ID > 0) { sql += "pl.C_invoiceLine_ID = " + C_InvoiceLine_ID + " AND "; } sql += "pl.C_RevenueRecognition_ID = " + _RevenueRecognition_ID + " AND NVL(GL_Journal_ID, 0)<=0 AND run.RECOGNITIONDATE < " + GlobalVariable.TO_DATE(DateTime.Now, true); if (Util.GetValueOfInt(DB.ExecuteScalar(sql)) == 0) { MRevenueRecognitionPlan[] revenueRecognitionPlans = MRevenueRecognitionPlan.GetRecognitionPlans(mRevenueRecognition, C_InvoiceLine_ID, _orgId); journal_ID = new int[revenueRecognitionPlans.Length]; if (ReversalType == "P") { for (int i = 0; i < revenueRecognitionPlans.Length; i++) { revenueRecognitionPlan = revenueRecognitionPlans[i]; invoiceLine = new MInvoiceLine(GetCtx(), revenueRecognitionPlan.GetC_InvoiceLine_ID(), Get_Trx()); invoice = new MInvoice(GetCtx(), invoiceLine.GetC_Invoice_ID(), Get_Trx()); //get Sum of Amount Whose journal is not yet created sql = "SELECT SUM(run.Recognizedamt) AS TotalRecognizedAmt FROM C_RevenueRecognition_Run run WHERE " + "C_RevenueRecognition_Plan_ID = " + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID() + " AND NVL(GL_Journal_ID,0) <= 0"; totalAmt = Util.GetValueOfInt(DB.ExecuteScalar(sql)); if (totalAmt != 0) { //if totalAmount is not 0 then only create Journal if (revenueRecognitionPlan.GetC_AcctSchema_ID() != _AcctSchema_ID || revenueRecognitionPlan.GetC_Currency_ID() != _Currency_ID) { if (journal != null) { if (DocNo == null) { DocNo = journal.GetDocumentNo(); } else { DocNo += "," + journal.GetDocumentNo(); } journal_ID[i - 1] = journal.GetGL_Journal_ID(); } journal = new MJournal(GetCtx(), 0, Get_Trx()); journal.SetC_DocType_ID(_DocType); journal = CreateJournalHDR(revenueRecognitionPlan); if (journal.Save(Get_TrxName())) { _AcctSchema_ID = journal.GetC_AcctSchema_ID(); _Currency_ID = journal.GetC_Currency_ID(); lineno = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(Line), 0)+10 AS DefaultValue FROM GL_JournalLine WHERE GL_Journal_ID=" + journal.GetGL_Journal_ID(), null, invoice.Get_Trx())); } else { pp = VLogger.RetrieveError(); if (pp != null) { errorMsg = pp.GetName(); if (errorMsg == "") { errorMsg = pp.GetValue(); } } if (errorMsg == "") { errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated"); } Get_TrxName().Rollback(); return(errorMsg); } } revenueRecognitionPlan.SetRecognizedAmt(totalAmt + revenueRecognitionPlan.GetRecognizedAmt()); if (!revenueRecognitionPlan.Save()) { pp = VLogger.RetrieveError(); if (pp != null) { errorMsg = pp.GetName(); if (errorMsg == "") { errorMsg = pp.GetValue(); } } if (errorMsg == "") { errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated"); } Get_TrxName().Rollback(); return(errorMsg); } for (int k = 0; k < 2; k++) { journalLine = new MJournalLine(journal); journalLine = GenerateJounalLine(journal, invoice, invoiceLine, revenueRecognitionPlan, totalAmt, mRevenueRecognition.GetRecognitionType(), k); if (journalLine.Save(Get_TrxName())) { lineno += 10; } else { pp = VLogger.RetrieveError(); if (pp != null) { errorMsg = pp.GetName(); if (errorMsg == "") { errorMsg = pp.GetValue(); } } if (errorMsg == "") { errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated"); } Get_TrxName().Rollback(); return(errorMsg); } } sql = "UPDATE C_RevenueRecognition_Run set Gl_Journal_ID= " + journal.GetGL_Journal_ID() + " WHERE C_RevenueRecognition_Plan_ID= " + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID() + "AND NVL(Gl_Journal_ID,0)=0"; int count = DB.ExecuteQuery(sql, null, Get_Trx()); log.Log(Level.INFO, (Msg.GetMsg(GetCtx(), "RevenueRecognitionRunUpdated") + count)); } } if (journal != null) { if (DocNo == null) { DocNo = journal.GetDocumentNo(); } else { DocNo += "," + journal.GetDocumentNo(); } journal_ID[journal_ID.Length - 1] = journal.GetGL_Journal_ID(); } } #region Existing Reversal Type //else if (ReversalType == "E") //{ // for (int i = 0; i < revenueRecognitionPlans.Length; i++) // { // MRevenueRecognitionPlan revenueRecognitionPlan = revenueRecognitionPlans[i]; // MInvoiceLine invoiceLine = new MInvoiceLine(GetCtx(), revenueRecognitionPlan.GetC_InvoiceLine_ID(), Get_Trx()); // MInvoice invoice = new MInvoice(GetCtx(), invoiceLine.GetC_Invoice_ID(), Get_Trx()); // sql = "Select Distinct round(SUM(recognizedamt),5) as RecognizedAmt from C_RevenueRecognition_Run Where C_RevenueRecognition_Plan_ID = " + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID() + " And NVL(GL_Journal_ID,0) > 0 "; // DataSet ds = new DataSet(); // ds = DB.ExecuteDataset(sql); // if (ds != null && ds.Tables[0].Rows.Count > 0) // { // decimal Amt = Util.GetValueOfDecimal(ds.Tables[0].Rows[i]["RecognizedAmt"]); // string sql1 = "Select GL_Journal_ID from C_RevenueRecognition_Run Where C_RevenueRecognition_Plan_ID = " + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID() + " And NVL(GL_Journal_ID,0) > 0 AND RowNum=1"; // int GL_Journal_ID = Util.GetValueOfInt(DB.ExecuteScalar(sql1)); // MJournal journal = new MJournal(GetCtx(), GL_Journal_ID, Get_TrxName()); // log.Info(ToString()); // // Journal // MJournal reverse = new MJournal(journal); // reverse.SetDateDoc(DateTime.Now); // reverse.SetC_Period_ID(journal.GetC_Period_ID()); // int Period_ID = MPeriod.GetC_Period_ID(GetCtx(), DateTime.Now); // reverse.SetDateAcct(DateTime.Now); // if (reverse.Save()) // { // MJournalLine[] journalLines = journal.GetLines(false); // if (journalLines.Length > 0) // { // for (int j = 0; j < journalLines.Length; j++) // { // MJournalLine journalLine = journalLines[j]; // MJournalLine newjournalLine = new MJournalLine(GetCtx(), 0, Get_TrxName()); // PO.CopyValues(journalLine, newjournalLine, GetAD_Client_ID(), GetAD_Org_ID()); // newjournalLine.SetGL_Journal_ID(reverse.GetGL_Journal_ID()); // newjournalLine.SetDateAcct(DateTime.Now); // // Amounts // if (newjournalLine.GetAmtAcctCr() > 0) // { // newjournalLine.SetAmtAcctDr(0); // newjournalLine.SetAmtSourceDr(0); // newjournalLine.SetAmtSourceCr(Decimal.Negate(Amt)); // newjournalLine.SetAmtAcctCr(Decimal.Negate(Amt)); // } // else // { // newjournalLine.SetAmtAcctDr(Decimal.Negate(Amt)); // newjournalLine.SetAmtSourceDr(Decimal.Negate(Amt)); // newjournalLine.SetAmtSourceCr(0); // newjournalLine.SetAmtAcctCr(0); // } // newjournalLine.SetIsGenerated(true); // newjournalLine.SetProcessed(false); // newjournalLine.Save(); // if (j == 1) // { // break; // } // } // } // } // if (reverse != null && reverse.GetDocStatus() != "CO") // { // reverse.CompleteIt(); // reverse.SetProcessed(true); // reverse.SetDocStatus("CO"); // reverse.SetDocAction("CL"); // reverse.Save(Get_TrxName()); // if (DocNo == null) // { // DocNo = reverse.GetDocumentNo(); // } // int count = Util.GetValueOfInt(DB.ExecuteQuery("Update C_RevenueRecognition_Run Set GL_Journal_ID=null WHere C_RevenueRecognition_Plan_ID=" + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID())); // int count1 = Util.GetValueOfInt(DB.ExecuteQuery("Update C_RevenueRecognition_Plan Set RecognizedAmt=RecognizedAmt - " + Amt + " WHere C_RevenueRecognition_Plan_ID=" + revenueRecognitionPlan.GetC_RevenueRecognition_Plan_ID())); // } // } // } //} #endregion Get_TrxName().Commit(); if (journal_ID != null) { for (int i = 0; i < journal_ID.Length; i++) { if (journal_ID[i] > 0) { string result = RevenueRun.CompleteOrReverse(GetCtx(), journal_ID[i], 169, "CO"); if (!String.IsNullOrEmpty(result)) { journalIDS += ", " + journal_ID[i] + " " + result; } } } } if (DocNo == null) { return(Msg.GetMsg(GetCtx(), "FoundNoRevenueRecognitionPlan")); } } else { return(Msg.GetMsg(GetCtx(), "NotRecoganized")); } } catch (Exception ex) { log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "GLJournalnotallocateddueto") + ex.Message); Get_TrxName().Rollback(); return(ex.Message); } if (!String.IsNullOrEmpty(journalIDS)) { return(Msg.GetMsg(GetCtx(), "GLJournalCreated") + DocNo + ", " + Msg.GetMsg(GetCtx(), "GLJournalNotCompleted") + journalIDS); } else { return(Msg.GetMsg(GetCtx(), "GLJournalCreated") + DocNo); } }
/// <summary> /// Create GL Journal /// </summary> /// <param name="mRevenueRecognition">Revenue Recognition</param> /// <returns>Message</returns> public string CreateJournals(MRevenueRecognition mRevenueRecognition) { try { MRevenueRecognitionRun[] mRevenueRecognitionRuns = null; MRevenueRecognitionPlan revenueRecognitionPlan = null; MInvoiceLine invoiceLine = null; MInvoice invoice = null; mRevenueRecognitionRuns = MRevenueRecognitionRun.GetRecognitionRuns(mRevenueRecognition, _RecognitionDate, _orgId, false); journal_ID = new int[mRevenueRecognitionRuns.Length]; if (mRevenueRecognitionRuns.Length > 0) { for (int j = 0; j < mRevenueRecognitionRuns.Length; j++) { MRevenueRecognitionRun revenueRecognitionRun = mRevenueRecognitionRuns[j]; revenueRecognitionPlan = new MRevenueRecognitionPlan(GetCtx(), revenueRecognitionRun.GetC_RevenueRecognition_Plan_ID(), Get_TrxName()); invoiceLine = new MInvoiceLine(GetCtx(), revenueRecognitionPlan.GetC_InvoiceLine_ID(), Get_TrxName()); invoice = new MInvoice(GetCtx(), invoiceLine.GetC_Invoice_ID(), Get_TrxName()); if (revenueRecognitionPlan.GetC_AcctSchema_ID() != _AcctSchema_ID || revenueRecognitionPlan.GetC_Currency_ID() != _Currency_ID || revenueRecognitionRun.GetRecognitionDate() != _RecognizeDate) { if (journal != null) { if (DocNo == null) { DocNo = journal.GetDocumentNo(); } else { DocNo += ", " + journal.GetDocumentNo(); } journal_ID[j - 1] = journal.GetGL_Journal_ID(); } journal = new MJournal(GetCtx(), 0, Get_TrxName()); journal.SetC_DocType_ID(_DocType); journal = CreateJournalHDR(revenueRecognitionPlan, revenueRecognitionRun, mRevenueRecognition.GetRecognitionFrequency()); if (journal.Save()) { _AcctSchema_ID = journal.GetC_AcctSchema_ID(); _Currency_ID = journal.GetC_Currency_ID(); _RecognizeDate = revenueRecognitionRun.GetRecognitionDate(); lineno = Util.GetValueOfInt(DB.ExecuteScalar("SELECT NVL(MAX(Line), 0)+10 AS DefaultValue FROM GL_JournalLine WHERE GL_Journal_ID=" + journal.GetGL_Journal_ID(), null, invoice.Get_Trx())); } else { pp = VLogger.RetrieveError(); if (pp != null) { errorMsg = pp.GetName(); if (errorMsg == "") { errorMsg = pp.GetValue(); } } if (errorMsg == "") { errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated"); } Get_TrxName().Rollback(); return(errorMsg); } } for (int k = 0; k < 2; k++) { journalLine = new MJournalLine(journal); journalLine = GenerateJounalLine(journal, invoice, invoiceLine, revenueRecognitionPlan, revenueRecognitionRun, mRevenueRecognition.GetRecognitionType(), k); if (journalLine.Save()) { revenueRecognitionRun.SetGL_Journal_ID(journal.GetGL_Journal_ID()); revenueRecognitionRun.Save(); lineno += 10; } else { pp = VLogger.RetrieveError(); if (pp != null) { errorMsg = pp.GetName(); if (errorMsg == "") { errorMsg = pp.GetValue(); } } if (errorMsg == "") { errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated"); } Get_TrxName().Rollback(); return(errorMsg); } } revenueRecognitionPlan.SetRecognizedAmt(revenueRecognitionRun.GetRecognizedAmt() + revenueRecognitionPlan.GetRecognizedAmt()); if (!revenueRecognitionPlan.Save()) { pp = VLogger.RetrieveError(); if (pp != null) { errorMsg = pp.GetName(); if (errorMsg == "") { errorMsg = pp.GetValue(); } } if (errorMsg == "") { errorMsg = Msg.GetMsg(GetCtx(), "GLJournalNotCreated"); } Get_TrxName().Rollback(); return(errorMsg); } } if (journal != null) { if (DocNo == null) { DocNo = journal.GetDocumentNo(); } else { DocNo += ", " + journal.GetDocumentNo(); } journal_ID[journal_ID.Length - 1] = journal.GetGL_Journal_ID(); } } } catch (Exception ex) { log.Log(Level.SEVERE, Msg.GetMsg(GetCtx(), "GLJournalnotallocateddueto") + ex.Message); Get_TrxName().Rollback(); return(ex.Message); } if (DocNo == null) { return(Msg.GetMsg(GetCtx(), "FoundNoRevenueRecognitionPlan")); } Get_TrxName().Commit(); if (journal_ID != null) { for (int i = 0; i < journal_ID.Length; i++) { if (journal_ID[i] > 0) { string result = CompleteOrReverse(GetCtx(), journal_ID[i], 169, "CO"); if (!String.IsNullOrEmpty(result)) { journalIDS += ", " + journal_ID[i] + " " + result; } } } } if (!String.IsNullOrEmpty(journalIDS)) { return(Msg.GetMsg(GetCtx(), "GLJournalCreated") + DocNo + " " + Msg.GetMsg(GetCtx(), "GLJournalNotCompleted") + journalIDS); } else { return(Msg.GetMsg(GetCtx(), "GLJournalCreated") + DocNo); } }