importgljournal.java

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("No CourrencyType=" + no);

		//	Set/Overwrite Home Currency Rate
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET CurrencyRate=1"
			+ "WHERE EXISTS (SELECT * FROM C_AcctSchema a"
			+ " WHERE a.C_AcctSchema_ID=i.C_AcctSchema_ID AND a.C_Currency_ID=i.C_Currency_ID)"
			+ " AND C_Currency_ID IS NOT NULL AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set Home CurrencyRate=" + no);
		//	Set Currency Rate
		sql = new StringBuffer ("UPDATE I_GLJournal i "
		   + "SET CurrencyRate=(SELECT r.MultiplyRate FROM C_Conversion_Rate r, C_AcctSchema s"
		   + " WHERE s.C_AcctSchema_ID=i.C_AcctSchema_ID AND s.AD_Client_ID=i.AD_Client_ID"
		   + " AND r.C_Currency_ID=i.C_Currency_ID AND r.C_Currency_ID_TO=s.C_Currency_ID"
		   + " AND r.AD_Client_ID=i.AD_Client_ID AND r.AD_Org_ID=i.AD_OrgDoc_ID"
		   + " AND r.C_ConversionType_ID=i.C_ConversionType_ID"
		   + " AND i.DateAcct BETWEEN r.ValidFrom AND r.ValidTo AND ROWNUM=1"
	   //	ORDER BY ValidFrom DESC
		   + ") WHERE CurrencyRate IS NULL OR CurrencyRate=0 AND C_Currency_ID>0"
		   + " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set Org Rate=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET CurrencyRate=(SELECT r.MultiplyRate FROM C_Conversion_Rate r, C_AcctSchema s"
			+ " WHERE s.C_AcctSchema_ID=i.C_AcctSchema_ID AND s.AD_Client_ID=i.AD_Client_ID"
			+ " AND r.C_Currency_ID=i.C_Currency_ID AND r.C_Currency_ID_TO=s.C_Currency_ID"
			+ " AND r.AD_Client_ID=i.AD_Client_ID"
			+ " AND r.C_ConversionType_ID=i.C_ConversionType_ID"
			+ " AND i.DateAcct BETWEEN r.ValidFrom AND r.ValidTo AND ROWNUM=1"
		//	ORDER BY ValidFrom DESC
			+ ") WHERE CurrencyRate IS NULL OR CurrencyRate=0 AND C_Currency_ID>0"
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set Client Rate=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=No Rate, '"
			+ "WHERE CurrencyRate IS NULL OR CurrencyRate=0"
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("No Rate=" + no);

		//	Set Period
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET C_Period_ID=(SELECT p.C_Period_ID FROM C_Period p"
			+ " INNER JOIN C_Year y ON (y.C_Year_ID=p.C_Year_ID)"
			+ " INNER JOIN AD_ClientInfo c ON (c.C_Calendar_ID=y.C_Calendar_ID)"
			+ " WHERE c.AD_Client_ID=i.AD_Client_ID"
			+ " AND i.DateAcct BETWEEN p.StartDate AND p.EndDate AND p.PeriodType='S' AND ROWNUM=1) "
			+ "WHERE C_Period_ID IS NULL"
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set Period=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Period, '"
			+ "WHERE C_Period_ID IS NULL OR C_Period_ID<>"
			+ "(SELECT C_Period_ID FROM C_Period p"
			+ " INNER JOIN C_Year y ON (y.C_Year_ID=p.C_Year_ID)"
			+ " INNER JOIN AD_ClientInfo c ON (c.C_Calendar_ID=y.C_Calendar_ID) "
			+ " WHERE c.AD_Client_ID=i.AD_Client_ID"
			+ " AND i.DateAcct BETWEEN p.StartDate AND p.EndDate AND p.PeriodType='S' AND ROWNUM=1)"
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Invalid Period=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_ErrorMsg=I_ErrorMsg||'WARN=Period Closed, ' "
			+ "WHERE C_Period_ID IS NOT NULL AND NOT EXISTS"
			+ " (SELECT * FROM C_PeriodControl pc WHERE pc.C_Period_ID=i.C_Period_ID AND DocBaseType='GLJ' AND PeriodStatus='O') "
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Period Closed=" + no);

		//	Posting Type
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET PostingType='A' "
			+ "WHERE PostingType IS NULL AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set Actual PostingType=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid PostingType, ' "
			+ "WHERE PostingType IS NULL OR NOT EXISTS"
			+ " (SELECT * FROM AD_Ref_List r WHERE r.AD_Reference_ID=125 AND i.PostingType=r.Value)"
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Invalid PostingTypee=" + no);


		//	** Account Elements (optional) **
		//	(C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0)

		//	Set Org from Name (* is overwritten and default)
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET AD_Org_ID=(SELECT o.AD_Org_ID FROM AD_Org o"
			+ " WHERE o.Value=i.OrgValue AND o.IsSummary='N' AND i.AD_Client_ID=o.AD_Client_ID) "
			+ "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0) AND OrgValue IS NOT NULL"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'");
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set Org from Value=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET AD_Org_ID=AD_OrgDoc_ID "
			+ "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0) AND OrgValue IS NULL AND AD_OrgDoc_ID IS NOT NULL AND AD_OrgDoc_ID<>0"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set Org from Doc Org=" + no);
		//	Error Org
		sql = new StringBuffer ("UPDATE I_GLJournal o "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Org, '"
			+ "WHERE (AD_Org_ID IS NULL OR AD_Org_ID=0"
			+ " OR EXISTS (SELECT * FROM AD_Org oo WHERE o.AD_Org_ID=oo.AD_Org_ID AND (oo.IsSummary='Y' OR oo.IsActive='N')))"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Invalid Org=" + no);

		//	Set Account
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET Account_ID=(SELECT ev.C_ElementValue_ID FROM C_ElementValue ev"
			+ " INNER JOIN C_Element e ON (e.C_Element_ID=ev.C_Element_ID)"
			+ " INNER JOIN C_AcctSchema_Element ase ON (e.C_Element_ID=ase.C_Element_ID AND ase.ElementType='AC')"
			+ " WHERE ev.Value=i.AccountValue AND ev.IsSummary='N'"
			+ " AND i.C_AcctSchema_ID=ase.C_AcctSchema_ID AND i.AD_Client_ID=ev.AD_Client_ID AND ROWNUM=1) "
			+ "WHERE Account_ID IS NULL AND AccountValue IS NOT NULL"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set Account from Value=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Account, '"
			+ "WHERE (Account_ID IS NULL OR Account_ID=0)"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Invalid Account=" + no);

		//	Set BPartner
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET C_BPartner_ID=(SELECT bp.C_BPartner_ID FROM C_BPartner bp"
			+ " WHERE bp.Value=i.BPartnerValue AND bp.IsSummary='N' AND i.AD_Client_ID=bp.AD_Client_ID) "
			+ "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set BPartner from Value=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid BPartner, '"
			+ "WHERE C_BPartner_ID IS NULL AND BPartnerValue IS NOT NULL"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Invalid BPartner=" + no);

		//	Set Product
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET M_Product_ID=(SELECT p.M_Product_ID FROM M_Product p"
			+ " WHERE (p.Value=i.ProductValue OR p.UPC=i.UPC OR p.SKU=i.SKU)"
			+ " AND p.IsSummary='N' AND i.AD_Client_ID=p.AD_Client_ID AND ROWNUM=1) "
			+ "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set Product from Value=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Product, '"
			+ "WHERE M_Product_ID IS NULL AND (ProductValue IS NOT NULL OR UPC IS NOT NULL OR SKU IS NOT NULL)"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Invalid Product=" + no);

		//	Set Project
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET C_Project_ID=(SELECT p.C_Project_ID FROM C_Project p"
			+ " WHERE p.Value=i.ProjectValue AND p.IsSummary='N' AND i.AD_Client_ID=p.AD_Client_ID) "
			+ "WHERE C_Project_ID IS NULL AND ProjectValue IS NOT NULL"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set Project from Value=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid Project, '"
			+ "WHERE C_Project_ID IS NULL AND ProjectValue IS NOT NULL"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Invalid Project=" + no);

		//	Set TrxOrg
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET AD_OrgTrx_ID=(SELECT o.AD_Org_ID FROM AD_Org o"
			+ " WHERE o.Value=i.OrgValue AND o.IsSummary='N' AND i.AD_Client_ID=o.AD_Client_ID) "
			+ "WHERE AD_OrgTrx_ID IS NULL AND OrgTrxValue IS NOT NULL"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set OrgTrx from Value=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Invalid OrgTrx, '"
			+ "WHERE AD_OrgTrx_ID IS NULL AND OrgTrxValue IS NOT NULL"
			+ " AND (C_ValidCombination_ID IS NULL OR C_ValidCombination_ID=0) AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Invalid OrgTrx=" + no);


		//	Source Amounts
		sql = new StringBuffer ("UPDATE I_GLJournal "
			+ "SET AmtSourceDr = 0 "
			+ "WHERE AmtSourceDr IS NULL"
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set 0 Source Dr=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal "
			+ "SET AmtSourceCr = 0 "
			+ "WHERE AmtSourceCr IS NULL"
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Set 0 Source Cr=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_ErrorMsg=I_ErrorMsg||'WARN=Zero Source Balance, ' "
			+ "WHERE (AmtSourceDr-AmtSourceCr)=0"
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Zero Source Balance=" + no);

		//	Accounted Amounts (Only if No Error)
		sql = new StringBuffer ("UPDATE I_GLJournal "
			+ "SET AmtAcctDr = ROUND(AmtSourceDr * CurrencyRate, 2) "	//	HARDCODED rounding
			+ "WHERE AmtAcctDr IS NULL OR AmtAcctDr=0"
			+ " AND I_IsImported='N'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Calculate Acct Dr=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal "
			+ "SET AmtAcctCr = ROUND(AmtSourceCr * CurrencyRate, 2) "
			+ "WHERE AmtAcctCr IS NULL OR AmtAcctCr=0"
			+ " AND I_IsImported='N'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		log.fine("Calculate Acct Cr=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_ErrorMsg=I_ErrorMsg||'WARN=Zero Acct Balance, ' "
			+ "WHERE (AmtSourceDr-AmtSourceCr)<>0 AND (AmtAcctDr-AmtAcctCr)=0"
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Zero Acct Balance=" + no);
		sql = new StringBuffer ("UPDATE I_GLJournal i "
			+ "SET I_ErrorMsg=I_ErrorMsg||'WARN=Check Acct Balance, ' "
			+ "WHERE ABS(AmtAcctDr-AmtAcctCr)>100000000"	//	100 mio
			+ " AND I_IsImported<>'Y'").append (clientCheck);
		no = DB.executeUpdate(sql.toString(), get_TrxName());
		if (no != 0)
			log.warning ("Chack Acct Balance=" + no);


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

		//	Get Balance