/// <summary>
/// Update/Create initial Cost Record.
/// Check first for Purchase Price List,
/// then Product Purchase Costs
/// and then Price List
/// </summary>
/// <param name="as1">accounting schema</param>
/// <param name="create">create record</param>
/// <returns>costs</returns>
private Decimal?UpdateCostsOld(MAcctSchema as1, bool create)
{
// Create Zero Record
if (create)
{
StringBuilder sql = new StringBuilder("INSERT INTO M_Product_Costing "
+ "(M_Product_ID,C_AcctSchema_ID,"
+ " AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,"
+ " CurrentCostPrice,CostStandard,FutureCostPrice,"
+ " CostStandardPOQty,CostStandardPOAmt,CostStandardCumQty,CostStandardCumAmt,"
+ " CostAverage,CostAverageCumQty,CostAverageCumAmt,"
+ " PriceLastPO,PriceLastInv, TotalInvQty,TotalInvAmt) "
+ "VALUES (");
sql.Append(_M_Product_ID).Append(",").Append(as1.GetC_AcctSchema_ID()).Append(",")
.Append(as1.GetAD_Client_ID()).Append(",").Append(as1.GetAD_Org_ID()).Append(",")
.Append("'Y',SysDate,0,SysDate,0, 0,0,0, 0,0,0,0, 0,0,0, 0,0, 0,0)");
int no = DataBase.DB.ExecuteQuery(sql.ToString(), null, _trx);
if (no == 1)
{
log.Fine("CostingCreated");
}
}
// Try to find non ZERO Price
String costSource = "PriceList-PO";
Decimal?costs = GetPriceList(as1, true);
if (costs == null || costs.Equals(Env.ZERO))
{
costSource = "PO Cost";
costs = GetPOCost(as1);
}
if (costs == null || costs.Equals(Env.ZERO))
{
costSource = "PriceList";
costs = GetPriceList(as1, false);
}
// if not found use $1 (to be able to do material transactions)
if (costs == null || costs.Equals(Env.ZERO))
{
costSource = "Not Found";
costs = 1;//new Decimal(1);
}
// update current costs
StringBuilder sql1 = new StringBuilder("UPDATE M_Product_Costing ");
sql1.Append("SET CurrentCostPrice=").Append(costs)
.Append(" WHERE M_Product_ID=").Append(_M_Product_ID)
.Append(" AND C_AcctSchema_ID=").Append(as1.GetC_AcctSchema_ID());
int no1 = DataBase.DB.ExecuteQuery(sql1.ToString(), null, _trx);
if (no1 == 1)
{
log.Fine(costSource + " - " + costs);
}
return(costs);
}
public static bool CreateCostElementDetail(Ctx ctx, int AD_Client_ID, int AD_Org_ID, MProduct Product, int M_ASI_ID,
MAcctSchema mas, int M_costElement_ID, string windowName, MCostDetail cd, decimal amt, decimal qty)
{
try
{
MCostElementDetail ced = new MCostElementDetail(ctx, 0, cd.Get_Trx());
ced.SetAD_Client_ID(AD_Client_ID);
ced.SetAD_Org_ID(AD_Org_ID);
ced.SetC_AcctSchema_ID(mas.GetC_AcctSchema_ID());
ced.SetM_CostElement_ID(M_costElement_ID);
ced.SetM_Product_ID(Product.GetM_Product_ID());
ced.SetM_AttributeSetInstance_ID(M_ASI_ID);
ced.SetQty(qty);
ced.SetAmt(amt);
//Refrences
ced.SetC_OrderLine_ID(cd.GetC_OrderLine_ID());
ced.SetM_InOutLine_ID(cd.GetM_InOutLine_ID());
if (windowName == "Material Receipt" || windowName == "Customer Return" || windowName == "Shipment" || windowName == "Return To Vendor")
{
// not to bind Invoiceline refernece on cost element detail
}
else
{
ced.SetC_InvoiceLine_ID(cd.GetC_InvoiceLine_ID());
}
ced.Set_Value("VAFAM_AssetDisposal_ID", cd.Get_Value("VAFAM_AssetDisposal_ID"));
ced.SetM_InventoryLine_ID(cd.GetM_InventoryLine_ID());
ced.SetM_MovementLine_ID(cd.GetM_MovementLine_ID());
ced.SetC_ProjectIssue_ID(cd.GetC_ProjectIssue_ID());
ced.SetIsSOTrx(cd.IsSOTrx());
ced.SetA_Asset_ID(cd.GetA_Asset_ID());
ced.SetM_ProductionLine_ID(cd.GetM_ProductionLine_ID());
ced.SetM_WorkOrderResourceTxnLine_ID(cd.GetM_WorkOrderResourceTxnLine_ID());
ced.SetM_WorkOrderTransactionLine_ID(cd.GetM_WorkOrderTransactionLine_ID());
if (Env.IsModuleInstalled("VAMFG_"))
{
if (ced.Get_ColumnIndex("VAMFG_M_WrkOdrRscTxnLine_ID") > -1)
{
ced.Set_Value("VAMFG_M_WrkOdrRscTxnLine_ID", cd.GetVAMFG_M_WrkOdrRscTxnLine_ID());
}
if (ced.Get_ColumnIndex("VAMFG_M_WrkOdrTrnsctionLine_ID") > -1)
{
ced.Set_Value("VAMFG_M_WrkOdrTrnsctionLine_ID", cd.GetVAMFG_M_WrkOdrTrnsctionLine_ID());
}
}
ced.SetM_Warehouse_ID(cd.GetM_Warehouse_ID());
if (!ced.Save())
{
ValueNamePair pp = VLogger.RetrieveError();
_log.Info("Error Occured during costing " + pp.ToString());
return(false);
}
}
catch (Exception ex)
{
_log.Info("Error Occured during costing " + ex.ToString());
return(false);
}
return(true);
}
/// <summary>
/// Get Cost Queue Records in Lifo/Fifo order
/// </summary>
/// <param name="product">product</param>
/// <param name="M_ASI_ID">costing level ASI</param>
/// <param name="mas">accounting schema</param>
/// <param name="Org_ID">costing level org</param>
/// <param name="ce">Cost Element</param>
/// <param name="trxName">transaction</param>
/// <returns>cost queue or null</returns>
public static MCostQueue[] GetQueue(MProduct product, int M_ASI_ID, MAcctSchema mas,
int Org_ID, MCostElement ce, Trx trxName)
{
List <MCostQueue> list = new List <MCostQueue>();
String sql = "SELECT * FROM M_CostQueue "
+ "WHERE AD_Client_ID=@client AND AD_Org_ID=@org"
+ " AND M_Product_ID=@prod"
+ " AND M_CostType_ID=@ct AND C_AcctSchema_ID=@accs"
+ " AND M_CostElement_ID=@ce";
if (M_ASI_ID != 0)
{
sql += " AND M_AttributeSetInstance_ID=@asi";
}
sql += " AND CurrentQty<>0 "
+ "ORDER BY M_AttributeSetInstance_ID ";
if (!ce.IsFifo())
{
sql += "DESC";
}
try
{
SqlParameter[] param = null;
if (M_ASI_ID != 0)
{
param = new SqlParameter[7];
}
else
{
param = new SqlParameter[6];
}
param[0] = new SqlParameter("@client", product.GetAD_Client_ID());
param[1] = new SqlParameter("@org", Org_ID);
param[2] = new SqlParameter("@prod", product.GetM_Product_ID());
param[3] = new SqlParameter("@ct", mas.GetM_CostType_ID());
param[4] = new SqlParameter("@accs", mas.GetC_AcctSchema_ID());
param[5] = new SqlParameter("@ce", ce.GetM_CostElement_ID());
if (M_ASI_ID != 0)
{
param[6] = new SqlParameter("@asi", M_ASI_ID);
}
DataSet ds = DataBase.DB.ExecuteDataset(sql, param, trxName);
if (ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
list.Add(new MCostQueue(product.GetCtx(), dr, trxName));
}
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
MCostQueue[] costQ = new MCostQueue[list.Count];
costQ = list.ToArray();
return(costQ);
}
/// <summary>
/// Parent Constructor
/// </summary>
/// <param name="as1">accounting schema</param>
public MAcctSchemaElement(MAcctSchema as1)
: this(as1.GetCtx(), 0, as1.Get_TrxName())
{
SetClientOrg(as1);
SetC_AcctSchema_ID(as1.GetC_AcctSchema_ID());
// setC_Element_ID (0);
// setElementType (null);
// setName (null);
// setSeqNo (0);
}
/// <summary>
/// Parent Constructor
/// </summary>
/// <param name="product">product</param>
/// <param name="M_AttributeSetInstance_ID">Attribute Set Instance</param>
/// <param name="mas">Acct Schema</param>
/// <param name="AD_Org_ID">org</param>
/// <param name="M_CostElement_ID">cost element</param>
/// <param name="trxName">transaction</param>
public MCostQueue(MProduct product, int M_AttributeSetInstance_ID,
MAcctSchema mas, int AD_Org_ID, int M_CostElement_ID, Trx trxName)
: this(product.GetCtx(), 0, trxName)
{
SetClientOrg(product.GetAD_Client_ID(), AD_Org_ID);
SetC_AcctSchema_ID(mas.GetC_AcctSchema_ID());
SetM_CostType_ID(mas.GetM_CostType_ID());
SetM_Product_ID(product.GetM_Product_ID());
SetM_AttributeSetInstance_ID(M_AttributeSetInstance_ID);
SetM_CostElement_ID(M_CostElement_ID);
}
/// <summary>
/// Factory: Return ArrayList of Account Schema Elements
/// </summary>
/// <param name="as1">Accounting Schema</param>
/// <returns>ArrayList with Elements</returns>
public static MAcctSchemaElement[] GetAcctSchemaElements(MAcctSchema as1)
{
int key = as1.GetC_AcctSchema_ID();
MAcctSchemaElement[] retValue = (MAcctSchemaElement[])s_cache[key];
if (retValue != null)
{
return(retValue);
}
_log.Fine("C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID());
List <MAcctSchemaElement> list = new List <MAcctSchemaElement>();
//
String sql = "SELECT * FROM C_AcctSchema_Element "
+ "WHERE C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID() + " AND IsActive='Y' ORDER BY SeqNo";
try
{
DataSet ds = DataBase.DB.ExecuteDataset(sql, null, as1.Get_TrxName());
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
MAcctSchemaElement ase = new MAcctSchemaElement(as1.GetCtx(), dr, as1.Get_TrxName());
_log.Fine(" - " + ase);
if (ase.IsMandatory() && ase.GetDefaultValue() == 0)
{
_log.Log(Level.SEVERE, "No default value for " + ase.GetName());
}
list.Add(ase);
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
retValue = new MAcctSchemaElement[list.Count];
retValue = list.ToArray();
s_cache.Add(key, retValue);
return(retValue);
}
/// <summary>
/// This function is used to get Accounting Schema either binded on Organization or Primary Accounting SChema
/// </summary>
/// <param name="ctx">ctx</param>
/// <param name="Ad_Client_ID">AD_Client_ID</param>
/// <param name="AD_Org_ID">Org ID</param>
/// <returns>C_AcctSchema ID</returns>
public static int GetDefaultActSchema(Ctx ctx, int Ad_Client_ID, int AD_Org_ID)
{
MAcctSchema acctSchema = null;
if (AD_Org_ID > 0)
{
acctSchema = MOrg.Get(ctx, AD_Org_ID).GetAcctSchema();
}
if (acctSchema == null)
{
acctSchema = MClient.Get(ctx, Ad_Client_ID).GetAcctSchema();
}
return(acctSchema.GetC_AcctSchema_ID());
}
/// <summary>
/// Get/Create Cost Queue Record.
/// CostingLevel is not validated
/// </summary>
/// <param name="product">product</param>
/// <param name="M_AttributeSetInstance_ID">real asi</param>
/// <param name="mas">accounting schema</param>
/// <param name="AD_Org_ID">real org</param>
/// <param name="M_CostElement_ID">element</param>
/// <param name="trxName">transaction</param>
/// <returns>cost queue or null</returns>
public static MCostQueue Get(MProduct product, int M_AttributeSetInstance_ID,
MAcctSchema mas, int AD_Org_ID, int M_CostElement_ID, Trx trxName)
{
MCostQueue costQ = null;
String sql = "SELECT * FROM M_CostQueue "
+ "WHERE AD_Client_ID=@client AND AD_Org_ID=@org"
+ " AND M_Product_ID=@pro"
+ " AND M_AttributeSetInstance_ID=@asi"
+ " AND M_CostType_ID=@ct AND C_AcctSchema_ID=@accs"
+ " AND M_CostElement_ID=@ce";
try
{
SqlParameter[] param = new SqlParameter[7];
param[0] = new SqlParameter("@client", product.GetAD_Client_ID());
param[1] = new SqlParameter("@org", AD_Org_ID);
param[2] = new SqlParameter("@pro", product.GetM_Product_ID());
param[3] = new SqlParameter("@asi", M_AttributeSetInstance_ID);
param[4] = new SqlParameter("@ct", mas.GetM_CostType_ID());
param[5] = new SqlParameter("@accs", mas.GetC_AcctSchema_ID());
param[6] = new SqlParameter("@ce", M_CostElement_ID);
DataSet ds = DataBase.DB.ExecuteDataset(sql, param);
if (ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
costQ = new MCostQueue(product.GetCtx(), dr, trxName);
}
}
}
catch (Exception e)
{
_log.Log(Level.SEVERE, sql, e);
}
// New
if (costQ == null)
{
costQ = new MCostQueue(product, M_AttributeSetInstance_ID,
mas, AD_Org_ID, M_CostElement_ID, trxName);
}
return(costQ);
}
/**
* Get Charge Account
* @param C_Charge_ID charge
* @param as account schema
* @param amount amount for expense(+)/revenue(-)
* @return Charge Account or null
*/
public static MAccount GetAccount(int C_Charge_ID, MAcctSchema aSchema, Decimal amount)
{
if (C_Charge_ID == 0 || aSchema == null)
{
return(null);
}
int acct_index = 1; // Expense (positive amt)
if (amount < 0)
{
acct_index = 2; // Revenue (negative amt)
}
String sql = "SELECT CH_Expense_Acct, CH_Revenue_Acct FROM C_Charge_Acct WHERE C_Charge_ID=" + C_Charge_ID + " AND C_AcctSchema_ID=" + aSchema.GetC_AcctSchema_ID();
int Account_ID = 0;
IDataReader dr = null;
try
{
// PreparedStatement pstmt = DataBase.prepareStatement(sql, null);
// pstmt.setInt (1, C_Charge_ID);
// pstmt.setInt (2, aSchema.getC_AcctSchema_ID());
// ResultSet dr = pstmt.executeQuery();
dr = DataBase.DB.ExecuteReader(sql, null, null);
if (dr.Read())
{
Account_ID = Utility.Util.GetValueOfInt(dr[acct_index - 1].ToString());
}
dr.Close();
//pstmt.close();
}
catch (SqlException e)
{
if (dr != null)
{
dr.Close();
}
_log.Log(Level.SEVERE, sql, e);
return(null);
}
finally
{
if (dr != null)
{
dr.Close();
}
}
// No account
if (Account_ID == 0)
{
_log.Severe("NO account for C_Charge_ID=" + C_Charge_ID);
return(null);
}
// Return Account
MAccount acct = MAccount.Get(aSchema.GetCtx(), Account_ID);
return(acct);
} // getAccount
/// <summary>
/// Get Product Costs per UOM for Accounting Schema in Accounting Schema Currency.
/// - if costType defined - cost
/// - else CurrentCosts
/// </summary>
/// <param name="as1"></param>
/// <param name="costType">if null uses Accounting Schema Costs - see AcctSchema.COSTING_*</param>
/// <returns>product costs</returns>
private Decimal?GetProductItemCostOld(MAcctSchema as1, String costType)
{
Decimal? current = null;
Decimal? cost = null;
String cm = as1.GetCostingMethod();
StringBuilder sql = new StringBuilder("SELECT CurrentCostPrice,"); // 1
//
if ((costType == null && MAcctSchema.COSTINGMETHOD_AveragePO.Equals(cm)) ||
MAcctSchema.COSTINGMETHOD_AveragePO.Equals(costType))
{
sql.Append("COSTAVERAGE"); // 2
}
else if ((costType == null && MAcctSchema.COSTINGMETHOD_LastPOPrice.Equals(cm)) ||
MAcctSchema.COSTINGMETHOD_LastPOPrice.Equals(costType))
{
sql.Append("PRICELASTPO");
}
else // AcctSchema.COSTING_STANDARD
{
sql.Append("COSTSTANDARD");
}
sql.Append(" FROM M_Product_Costing WHERE M_Product_ID=" + _M_Product_ID + " AND C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID());
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql.ToString(), null, null);
if (idr.Read())
{
current = Utility.Util.GetValueOfDecimal(idr[0]); //.getBigDecimal(1);
cost = Utility.Util.GetValueOfDecimal(idr[1]); //.getBigDecimal(2);
}
idr.Close();
}
catch (Exception e)
{
log.Log(Level.SEVERE, sql.ToString(), e);
}
// Return Costs
if (costType != null && cost != null && !cost.Equals(Env.ZERO))
{
log.Fine("Costs=" + cost);
return(cost);
}
else if (current != null && !current.Equals(Env.ZERO))
{
log.Fine("Current=" + current);
return(current);
}
// Create/Update Cost Record
bool create = (cost == null && current == null);
return(UpdateCostsOld(as1, create));
}
/// <summary>
/// Account from Default Product Category
/// </summary>
/// <param name="AcctType"></param>
/// <param name="as1">accounting schema</param>
/// <returns> Requested Product Account</returns>
public MAccount GetAccountDefault(int AcctType, MAcctSchema as1)
{
// if (AcctType < 1 || AcctType > 10)
//Updated By raghu 7,jun,2011
if (AcctType < 1 || AcctType > 12)
{
return(null);
}
//String sql = "SELECT P_Revenue_Acct, P_Expense_Acct, P_Asset_Acct, P_Cogs_Acct, "
// + "P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct, "
// + "P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct, "
// + "P_CostAdjustment_Acct, P_InventoryClearing_Acct "
// + "FROM M_Product_Category pc, M_Product_Category_Acct pca "
// + "WHERE pc.M_Product_Category_ID=pca.M_Product_Category_ID"
// + " AND pca.C_AcctSchema_ID=" + as1.GetC_AcctSchema_ID()
// + "ORDER BY pc.IsDefault DESC, pc.Created";
//Updated By raghu 7,jun,2011
/*****************Manfacturing*********************/
String sql = "SELECT COALESCE(a.P_Revenue_Acct, b.P_Revenue_Acct), " //1
+ " COALESCE(a.P_Expense_Acct, b.P_Expense_Acct), " //2
+ " COALESCE(a.P_Asset_Acct, b.P_Asset_Acct), " //3
+ " COALESCE(a.P_Cogs_Acct, b.P_Cogs_Acct), " //4
+ " COALESCE(a.P_PurchasePriceVariance_Acct, b.P_PurchasePriceVariance_Acct), " //5
+ " COALESCE(a.P_InvoicePriceVariance_Acct, b.P_InvoicePriceVariance_Acct), " //6
+ " COALESCE(a.P_TradeDiscountRec_Acct, b.P_TradeDiscountRec_Acct), " //7
+ " COALESCE(a.P_TradeDiscountGrant_Acct, b.P_TradeDiscountGrant_Acct), " //8
+ " COALESCE(a.P_CostAdjustment_Acct, b.P_CostAdjustment_Acct), " //9
+ " COALESCE(a.P_InventoryClearing_Acct, b.P_InventoryClearing_Acct), " //10
+ " COALESCE(a.P_Resource_Absorption_Acct, b.P_Resource_Absorption_Acct), " //11
+ " COALESCE(a.P_MaterialOverhd_Acct, b.P_MaterialOverhd_Acct) " //12
+ " FROM C_AcctSchema_Default b "
+ " LEFT OUTER JOIN M_Product_Category_Acct a ON (a.C_AcctSchema_ID = b.C_AcctSchema_ID "
+ " AND a.IsActive = 'Y' ),"
+ " M_Product_Category pc "
+ " WHERE pc.M_Product_Category_ID=a.M_Product_Category_ID"
+ " AND b.C_AcctSchema_ID =" + as1.GetC_AcctSchema_ID()
+ " ORDER BY pc.IsDefault DESC, pc.Created";
/*****************Manfacturing*********************/
int validCombination_ID = 0;
IDataReader idr = null;
try
{
idr = DataBase.DB.ExecuteReader(sql, null, null);
if (idr.Read())
{
validCombination_ID = Utility.Util.GetValueOfInt(idr[AcctType - 1]);
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
idr = null;
}
log.Log(Level.SEVERE, sql, e);
}
if (validCombination_ID == 0)
{
return(null);
}
return(MAccount.Get(as1.GetCtx(), validCombination_ID));
}
/// <summary>
/// Parent Constructor
/// </summary>
/// <param name="as1">as account schema</param>
public MAccount(MAcctSchema as1)
: this(as1.GetCtx(), 0, as1.Get_TrxName())
{
SetClientOrg(as1);
SetC_AcctSchema_ID(as1.GetC_AcctSchema_ID());
}
