factline.java

Java写的ERP系统

	{
		log.debug("save " + toString());

		/**
		 *  Fill variables
		 */
		int AD_Client_ID = getAD_Client_ID();
		int AD_Org_ID = getAD_Org_ID();

		//  Set Account
		int Account_ID = m_acct.getAccount_ID();

		//	Doc Dates
		Timestamp 	DateDoc = null;
		if (m_docLine != null)
			DateDoc = m_docLine.getDateDoc();
		if (DateDoc == null)
			DateDoc = m_docVO.DateDoc;
		Timestamp 	DateAcct = null;
		if (m_docLine != null)
			DateAcct = m_docLine.getDateDoc();
		if (DateAcct == null)
			DateAcct = m_docVO.DateAcct;
		int C_Period_ID = m_docVO.C_Period_ID;

		//  Set Line Optional Info
		int C_UOM_ID = 0;
		BigDecimal Qty = m_docVO.Qty;
		int C_Tax_ID = 0;
		if (m_docLine != null)
		{
			C_UOM_ID = m_docLine.getC_UOM_ID();
			Qty = m_docLine.getQty();
			C_Tax_ID = m_docLine.getC_Tax_ID();
		}

		//  Set Account Info
		int M_Product_ID = 0;
		if (m_docLine != null)
			M_Product_ID = m_docLine.getM_Product_ID();
		if (M_Product_ID == 0)
			M_Product_ID = m_docVO.M_Product_ID;
		if (M_Product_ID == 0)
			M_Product_ID = m_acct.getM_Product_ID();

		int C_LocFrom_ID = m_C_LocFrom_ID;
		if (C_LocFrom_ID == 0 && m_docLine != null)
			C_LocFrom_ID = m_docLine.getC_LocFrom_ID();
		if (C_LocFrom_ID == 0)
			C_LocFrom_ID = m_docVO.C_LocFrom_ID;
		if (C_LocFrom_ID == 0)
			C_LocFrom_ID = m_acct.getC_LocFrom_ID();

		int C_LocTo_ID = m_C_LocFrom_ID;
		if (C_LocTo_ID == 0 && m_docLine != null)
			C_LocTo_ID = m_docLine.getC_LocTo_ID();
		if (C_LocTo_ID == 0)
			C_LocTo_ID = m_docVO.C_LocTo_ID;
		if (C_LocTo_ID == 0)
			C_LocTo_ID = m_acct.getC_LocTo_ID();

		int C_BPartner_ID = 0;
		if (m_docLine != null)
			C_BPartner_ID = m_docLine.getC_BPartner_ID();
		if (C_BPartner_ID == 0)
			C_BPartner_ID = m_docVO.C_BPartner_ID;
		if (C_BPartner_ID == 0)
			C_BPartner_ID = m_acct.getC_BPartner_ID();

		int AD_OrgTrx_ID = 0;
		if (m_docLine != null)
			AD_OrgTrx_ID = m_docLine.getAD_OrgTrx_ID();
		if (AD_OrgTrx_ID == 0)
			AD_OrgTrx_ID = m_docVO.AD_OrgTrx_ID;
		if (AD_OrgTrx_ID == 0)
			AD_OrgTrx_ID = m_acct.getAD_OrgTrx_ID();

		int C_SalesRegion_ID = 0;
		if (m_docLine != null)
			C_SalesRegion_ID = m_docLine.getC_SalesRegion_ID();
		if (C_SalesRegion_ID == 0)
			C_SalesRegion_ID = m_docVO.C_SalesRegion_ID;
		if (C_SalesRegion_ID == 0)
			C_SalesRegion_ID = m_acct.getC_SalesRegion_ID();

		int C_Project_ID = 0;
		if (m_docLine != null)
			C_Project_ID = m_docLine.getC_Project_ID();
		if (C_Project_ID == 0)
			C_Project_ID = m_docVO.C_Project_ID;
		if (C_Project_ID == 0)
			C_Project_ID = m_acct.getC_Project_ID();

		int C_Campaign_ID = 0;
		if (m_docLine != null)
			C_Campaign_ID = m_docLine.getC_Campaign_ID();
		if (C_Campaign_ID == 0)
			C_Campaign_ID = m_docVO.C_Campaign_ID;
		if (C_Campaign_ID == 0)
			C_Campaign_ID = m_acct.getC_Campaign_ID();

		int C_Activity_ID = 0;
		if (m_docLine != null)
			C_Activity_ID = m_docLine.getC_Activity_ID();
		if (C_Activity_ID == 0)
			C_Activity_ID = m_docVO.C_Activity_ID;
		if (C_Activity_ID == 0)
			C_Activity_ID = m_acct.getC_Activity_ID();

		int User1_ID = 0;
		if (m_docLine != null)
			User1_ID = m_docLine.getUser1_ID();
		if (User1_ID == 0)
			User1_ID = m_docVO.User1_ID;
		if (User1_ID == 0)
			User1_ID = m_acct.getUser1_ID();

		int User2_ID = 0;
		if (m_docLine != null)
			User2_ID = m_docLine.getUser2_ID();
		if (User2_ID == 0)
			User2_ID = m_docVO.User2_ID;
		if (User2_ID == 0)
			User2_ID = m_acct.getUser2_ID();

		//  Revenue Recognition for AR Invoices
		if (m_docVO.DocumentType.equals(DocVO.DOCTYPE_ARInvoice) &&
			m_docLine != null && m_docLine.getC_RevenueRecognition_ID() != 0)
		{
			Account_ID = createRevenueRecognition (con,
				m_docLine.getC_RevenueRecognition_ID(), m_docLine.getTrxLine_ID(),
				AD_Client_ID, AD_Org_ID, 0, Account_ID,
				M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
				C_LocFrom_ID, C_LocTo_ID, C_SalesRegion_ID, C_Project_ID,
				C_Campaign_ID, C_Activity_ID, User1_ID, User2_ID);
		}

		//  Description
		StringBuffer description = new StringBuffer();
		description.append(m_docVO.DocumentNo);
		//  ... line
		if (m_docLine != null)
		{
			description.append(" # ").append(m_docLine.getLine());
			if (m_docLine.getDescription() != null)
				description.append(" (").append(m_docLine.getDescription()).append(")");
		}
		//  ... cannot distinguish between header and tax

		if (description.length() > 255)
			description = new StringBuffer(description.substring(0,254));

		/**
		 *  Create SQL Statement
		 */
		StringBuffer sql = new StringBuffer ("INSERT INTO Fact_Acct "
			+ "(Fact_Acct_ID,AD_Client_ID,AD_Org_ID,"
			+ " IsActive,Created,CreatedBy,Updated,UpdatedBy,"
			+ " C_AcctSchema_ID,Account_ID,DateTrx,DateAcct,"
			+ " C_Period_ID,AD_Table_ID,Record_ID,Line_ID,"
			+ " GL_Category_ID,GL_Budget_ID,"
			+ " C_Tax_ID,PostingType,"
			+ " C_Currency_ID,AmtSourceDR,AmtSourceCR,AmtAcctDR,AmtAcctCR,"
			+ " C_UOM_ID,Qty,M_Locator_ID,"
			+ " M_Product_ID,C_BPartner_ID,AD_OrgTrx_ID,"
			+ " C_LocFrom_ID,C_LocTo_ID,C_SalesRegion_ID,"
			+ " C_Project_ID,C_Campaign_ID,C_Activity_ID,"
			+ " User1_ID,User2_ID,Description) VALUES (");

		//  Fact_Acct_ID, AD_Client_ID, AD_Org_ID,
		m_Fact_Acct_ID = DB.getKeyNextNo(AD_Client_ID, "N", "Fact_Acct");
		sql.append(m_Fact_Acct_ID).append(",")
			.append(AD_Client_ID).append(",")
			.append(AD_Org_ID).append(", ");

		//  IsActive, Created, CreatedBy, Updated, UpdatedBy,
		sql.append("'Y',SysDate,0,SysDate,0, ");

		//	C_AcctSchema_ID, Account_ID, DateTrx, DateAcct,
		sql.append(m_C_AcctSchema_ID).append(",")
			.append(Account_ID).append(",")
			.append(DB.TO_DATE(DateDoc)).append(",")
			.append(DB.TO_DATE(DateAcct)).append(", ");

		//	C_Period_ID, AD_Table_ID, Record_ID, Line_ID,
		if (C_Period_ID == 0)
			sql.append("NULL,");
		else
			sql.append(C_Period_ID).append(",");
		sql.append(m_AD_Table_ID).append(",")
			.append(m_Record_ID).append(",");
		if (m_Line_ID == 0)
			sql.append("NULL, ");
		else
			sql.append(m_Line_ID).append(", ");

		//  GL_Category_ID, GL_Budget_ID, (optional)
		if (m_GL_Category_ID == 0)
			sql.append("NULL,");
		else
			sql.append(m_GL_Category_ID).append(",");
		if (m_GL_Budget_ID == 0)
			sql.append("NULL, ");
		else
			sql.append(m_GL_Budget_ID).append(", ");

		//  C_Tax_ID, PostingType,
		if (C_Tax_ID == 0)
			sql.append("NULL,");
		else
			sql.append(C_Tax_ID).append(",");
		sql.append("'").append(m_PostingType).append("', ");

		//  C_Currency_ID, AmtSourceDR, AmtSourceCR, AmtAcctDR, AmtAcctCR,
		sql.append(m_C_Currency_ID).append(",")
			.append(m_AmtSourceDr).append(",")
			.append(m_AmtSourceCr).append(",")
			.append(m_AmtAcctDr).append(",")
			.append(m_AmtAcctCr).append(", ");

		//  C_UOM_ID, Qty, M_Locator_ID,
		if (C_UOM_ID == 0)
			sql.append("NULL,");
		else
			sql.append(C_UOM_ID).append(",");
		if (Qty == null)
			sql.append("NULL, ");
		else
			sql.append(Qty).append(", ");
		if (m_M_Locator_ID == 0)
			sql.append("NULL,");
		else
			sql.append(m_M_Locator_ID).append(",");

		//  M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
		if (M_Product_ID == 0)
			sql.append("NULL,");
		else
			sql.append(M_Product_ID).append(",");
		if (C_BPartner_ID == 0)
			sql.append("NULL,");
		else
			sql.append(C_BPartner_ID).append(",");
		if (AD_OrgTrx_ID == 0)
			sql.append("NULL, ");
		else
			sql.append(AD_OrgTrx_ID).append(", ");

		//	C_LocFrom_ID, C_LocTo_ID, C_SalesRegion_ID,
		if (C_LocFrom_ID == 0)
			sql.append("NULL,");
		else
			sql.append(C_LocFrom_ID).append(",");
		if (C_LocTo_ID == 0)
			sql.append("NULL,");
		else
			sql.append(C_LocTo_ID).append(",");
		if (C_SalesRegion_ID == 0)
			sql.append("NULL, ");
		else
			sql.append(C_SalesRegion_ID).append(", ");

		//  C_Project_ID, C_Campaign_ID, C_Activity_ID,
		if (C_Project_ID == 0)
			sql.append("NULL,");
		else
			sql.append(C_Project_ID).append(",");
		if (C_Campaign_ID == 0)
			sql.append("NULL,");
		else
			sql.append(C_Campaign_ID).append(",");
		if (C_Activity_ID == 0)
			sql.append("NULL, ");
		else
			sql.append(C_Activity_ID).append(", ");

		//  User1_ID, User2_ID, Description
		if (User1_ID == 0)
			sql.append("NULL,");
		else
			sql.append(User1_ID).append(",");
		if (User2_ID == 0)
			sql.append("NULL,");
		else
			sql.append(User2_ID).append(",");
		sql.append("'").append(description.toString()).append("')");

		/**
		 *  Save to DB
		 */
	//	Log.trace(Log.l6_Database, "FactLine.save SQL=" + sql.toString());
		int no = 0;
		try
		{
			Statement stmt = con.createStatement();
			no = stmt.executeUpdate(sql.toString());
			stmt.close();
		}
		catch (SQLException e)
		{
			log.error ("save", e);
			no = 0;
		}
		return no == 1;
	}   //  save

	/*************************************************************************/

	/**
	 *  Revenue Recognition.
	 *  Called from FactLine.save
	 *  <p>
	 *  Create Revenue recognition plan and return Unearned Revenue account
	 *  to be used instead of Revenue Account. If not found, it returns
	 *  the revenue account.
	 *
	 *  @param con connection
	 *  @param C_RevenueRecognition_ID revenue recognition
	 *  @param C_InvoiceLine_ID invoice line
	 *  @param AD_Client_ID client
	 *  @param AD_Org_ID org
	 *  @param AD_User_ID user
	 *  @param Account_ID of Revenue Account
	 *  @param M_Product_ID product
	 *  @param C_BPartner_ID bpartner
	 *  @param AD_OrgTrx_ID trx org
	 *  @param C_LocFrom_ID loc from
	 *  @param C_LocTo_ID loc to
	 *  @param C_SRegion_ID sales region
	 *  @param C_Project_ID project
	 *  @param C_Campaign_ID campaign
	 *  @param C_Activity_ID activity
	 *  @param User1_ID user1
	 *  @param User2_ID user2
	 *  @return Account_ID for Unearned Revenue or Revenue Account if not found
	 */
	private int createRevenueRecognition (Connection con,
		int C_RevenueRecognition_ID, int C_InvoiceLine_ID,
		int AD_Client_ID, int AD_Org_ID, int AD_User_ID, int Account_ID,
		int M_Product_ID, int C_BPartner_ID, int AD_OrgTrx_ID,
		int C_LocFrom_ID, int C_LocTo_ID, int C_SRegion_ID, int C_Project_ID,
		int	C_Campaign_ID, int C_Activity_ID, int User1_ID, int User2_ID)
	{
	//	Log.trace(Log.l6_Database, "FactLine.createRevenueRecognition from Accout_ID=" + Account_ID);
		//  get VC for P_Revenue (from Product)
		int P_Revenue_Acct = DB.getValidCombination(AD_Client_ID, AD_Org_ID,
			m_C_AcctSchema_ID, Account_ID, 0, null, AD_User_ID,
			M_Product_ID, C_BPartner_ID, AD_OrgTrx_ID,
			C_LocFrom_ID, C_LocTo_ID, C_SRegion_ID, C_Project_ID,
			C_Campaign_ID, C_Activity_ID, User1_ID, User2_ID);
		if (P_Revenue_Acct == 0)
		{
			log.error ("createRevenueRecognition - Revenue_Acct not found");
			return Account_ID;
		}

		//  get Unearned Revenue Acct from BPartner Group
		int UnearnedRevenue_Acct = 0;
		int new_Account_ID = 0;
		String sql = "SELECT ga.UnearnedRevenue_Acct, vc.Account_ID "
			+ "FROM C_BP_Group_Acct ga, C_BPartner p, C_ValidCombination vc "
			+ "WHERE ga.C_BP_Group_ID=p.C_BP_Group_ID"
			+ " AND ga.UnearnedRevenue_Acct=vc.C_ValidCombination_ID"
			+ " AND ga.C_AcctSchema_ID=? AND p.C_BPartner_ID=?";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, m_C_AcctSchema_ID);
			pstmt.setInt(2, C_BPartner_ID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				UnearnedRevenue_Acct = rs.getInt(1);
				new_Account_ID = rs.getInt(2);
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.error ("createRevenueRecognition_1", e);
		}
		if (new_Account_ID == 0)
		{
			log.error ("createRevenueRecognition - UnearnedRevenue_Acct not found");
			return Account_ID;
		}

		//  Insert record in C_RevenueRecognition_Plan to start batch process generation
		StringBuffer isql = new StringBuffer();
		isql.append("INSERT INTO C_RevenueRecognition_Plan "
			+ "(C_RevenueRecognition_Plan_ID,C_RevenueRecognition_ID,C_AcctSchema_ID, "
			+ "AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy, "
			+ "C_InvoiceLine_ID,UnearnedRevenue_Acct,P_Revenue_Acct, "
			+ "C_Currency_ID,TotalAmt,RecognizedAmt) VALUES (");

		//  C_RevenueRecognition_Plan_ID, C_RevenueRecognition_ID, C_AcctSchema_ID,
		String CompiereSys = "N";
		int C_RevenueRecognition_Plan_ID = DB.getKeyNextNo(AD_Client_ID, CompiereSys, "C_RevenueRecognition_Plan");
		isql.append(C_RevenueRecognition_Plan_ID).append(",")
			.append(C_RevenueRecognition_ID).append(",")
			.append(m_C_AcctSchema_ID).append(", ");
		//  AD_Client_ID,AD_Org_ID, IsActive, Created,CreatedBy, Updated,UpdatedBy,
		isql.append(AD_Client_ID).append(",")
			.append(AD_Org_ID).append(",'Y',SysDate,")
			.append(AD_User_ID).append(",SysDate,").append(AD_User_ID).append(", ");
		//  C_InvoiceLine_ID, UnearnedRevenue_Acct, P_Revenue_Acct,
		isql.append(C_InvoiceLine_ID).append(",")
			.append(UnearnedRevenue_Acct).append(",")
			.append(P_Revenue_Acct).append(", ");
		//  C_Currency_ID, TotalAmt, RecognizedAmt)
		isql.append(m_C_Currency_ID).append(",")
			.append(getAcctBalance()).append(",0)");

		int no = 0;
		try
		{
			Statement stmt = con.createStatement();
			no = stmt.executeUpdate(isql.toString());
			stmt.close();
		}
		catch (SQLException e)
		{
			log.error ("createRevenueRecognition_2 SQL=" + isql.toString(), e);
			no = 0;
		}
		if (no != 1)
		{
			log.error ("createRevenueRecognition - Plan NOT created");
			return Account_ID;
		}
		log.debug ("createRevenueRecognition From Acctount_ID=" + Account_ID + " to " + new_Account_ID
			+ " - Plan from UnearnedRevenue_Acct=" + UnearnedRevenue_Acct + " to Revenue_Acct=" + P_Revenue_Acct);
		return new_Account_ID;
	}   //  createRevenueRecognition


	/*************************************************************************/

	/**
	 * Update Line with reversed Original in Accounting Currency
	 * @param AD_Table_ID table
	 * @param Line_ID line
	 * @param multiplier targetQty/documentQty
	 * @return true if success
	 */
	public boolean updateReverseLine (int AD_Table_ID, int Line_ID, BigDecimal multiplier)
	{
		boolean success = false;

		String sql = "SELECT AmtAcctDr,AmtAcctCr FROM Fact_Acct "
			+ "WHERE C_AcctSchema_ID=? AND AD_Table_ID=?"
			+ " AND Line_ID=? AND Account_ID=?";
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql);
			pstmt.setInt(1, m_C_AcctSchema_ID);
			pstmt.setInt(2, AD_Table_ID);
			pstmt.setInt(3, Line_ID);
			pstmt.setInt(4, m_acct.getAccount_ID());
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
			{
				//  Accounted Amounts - reverse
				BigDecimal cr = rs.getBigDecimal("AmtAcctCr");
				BigDecimal dr = rs.getBigDecimal("AmtAcctDr");
				m_AmtAcctDr = cr.multiply(multiplier);
				m_AmtAcctCr = dr.multiply(multiplier);
				//  Source Amounts
				m_AmtSourceDr = m_AmtAcctDr;
				m_AmtSourceCr = m_AmtAcctCr;
				//
				success = true;
				log.debug (new StringBuffer("updateReverseLine (Table=").append(AD_Table_ID)
					.append(",Line=").append(Line_ID)
					.append(",Account=").append(m_acct)
					.append(",dr=").append(dr).append(",cr=").append(cr)
					.append(") - DR=").append(m_AmtSourceDr).append("|").append(m_AmtAcctDr)
					.append(", CR=").append(m_AmtSourceCr).append("|").append(m_AmtAcctCr)
					.toString());
			}
			else
				log.error (new StringBuffer("updateReverseLine - Not Found ")
					.append(", C_AcctSchema_ID=").append(m_C_AcctSchema_ID)
					.append(", AD_Table_ID=").append(AD_Table_ID)
					.append(", Line_ID=").append(Line_ID)
					.append(", Account_ID=").append(m_acct.getAccount_ID()).toString());
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.error ("updateReverseLine", e);
		}
		return success;
	}   //  updateReverseLine

}	//	FactLine