/**
* Process
*
* @return message
* @throws Exception
*/
@Override
protected String doIt() throws Exception {
log.info("C_AcctSchema_ID=" + p_C_AcctSchema_ID);
if (p_C_AcctSchema_ID == 0) throw new CompiereSystemException("C_AcctSchema_ID=0");
MAcctSchema as = MAcctSchema.get(getCtx(), p_C_AcctSchema_ID);
if (as.get_ID() == 0)
throw new CompiereSystemException("Not Found - C_AcctSchema_ID=" + p_C_AcctSchema_ID);
// Update
String sql =
"UPDATE M_Product_Acct pa "
+ "SET (P_Revenue_Acct,P_Expense_Acct,P_CostAdjustment_Acct,P_InventoryClearing_Acct,P_Asset_Acct,P_COGS_Acct,"
+ " P_PurchasePriceVariance_Acct,P_InvoicePriceVariance_Acct,"
+ " P_TradeDiscountRec_Acct,P_TradeDiscountGrant_Acct,"
+ " P_Resource_Absorption_Acct, P_MaterialOverhd_Acct)="
+ " (SELECT P_Revenue_Acct,P_Expense_Acct,P_CostAdjustment_Acct,P_InventoryClearing_Acct,P_Asset_Acct,P_COGS_Acct,"
+ " P_PurchasePriceVariance_Acct,P_InvoicePriceVariance_Acct,"
+ " P_TradeDiscountRec_Acct,P_TradeDiscountGrant_Acct,"
+ " P_Resource_Absorption_Acct, P_MaterialOverhd_Acct"
+ " FROM M_Product_Category_Acct pca"
+ " WHERE pca.M_Product_Category_ID="
+ p_M_Product_Category_ID
+ " AND pca.C_AcctSchema_ID= pa.C_AcctSchema_ID "
+ "), Updated=SysDate, UpdatedBy=0 "
+ "WHERE pa.C_AcctSchema_ID= ? "
+ " AND EXISTS (SELECT * FROM M_Product p "
+ "WHERE p.M_Product_ID=pa.M_Product_ID"
+ " AND p.M_Product_Category_ID= ? )";
int updated = DB.executeUpdate(get_TrxName(), sql, p_C_AcctSchema_ID, p_M_Product_Category_ID);
addLog(0, null, new BigDecimal(updated), "@Updated@");
// Insert new Products
sql =
"INSERT INTO M_Product_Acct "
+ "(M_Product_ID, C_AcctSchema_ID,"
+ " AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,"
+ " P_Revenue_Acct, P_Expense_Acct, P_CostAdjustment_Acct, P_InventoryClearing_Acct, P_Asset_Acct, P_CoGs_Acct,"
+ " P_PurchasePriceVariance_Acct, P_InvoicePriceVariance_Acct,"
+ " P_TradeDiscountRec_Acct, P_TradeDiscountGrant_Acct,"
+ " P_Resource_Absorption_Acct, P_MaterialOverhd_Acct) "
+ "SELECT p.M_Product_ID, acct.C_AcctSchema_ID,"
+ " p.AD_Client_ID, p.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,"
+ " acct.P_Revenue_Acct, acct.P_Expense_Acct, acct.P_CostAdjustment_Acct, acct.P_InventoryClearing_Acct, acct.P_Asset_Acct, acct.P_CoGs_Acct,"
+ " acct.P_PurchasePriceVariance_Acct, acct.P_InvoicePriceVariance_Acct,"
+ " acct.P_TradeDiscountRec_Acct, acct.P_TradeDiscountGrant_Acct,"
+ " acct.P_Resource_Absorption_Acct, acct.P_MaterialOverhd_Acct "
+ "FROM M_Product p"
+ " INNER JOIN M_Product_Category_Acct acct ON (acct.M_Product_Category_ID=p.M_Product_Category_ID)"
+ "WHERE acct.C_AcctSchema_ID= ? " // #
+ " AND p.M_Product_Category_ID= ? " // #
+ " AND NOT EXISTS (SELECT * FROM M_Product_Acct pa "
+ "WHERE pa.M_Product_ID=p.M_Product_ID"
+ " AND pa.C_AcctSchema_ID=acct.C_AcctSchema_ID)";
int created = DB.executeUpdate(get_TrxName(), sql, p_C_AcctSchema_ID, p_M_Product_Category_ID);
addLog(0, null, new BigDecimal(created), "@Created@");
return "@Created@=" + created + ", @Updated@=" + updated;
} // doIt
/** Update Header. Set Approved Amount */
private void updateHeader() {
String sql =
"UPDATE S_TimeExpense te"
+ " SET ApprovalAmt = "
+ "(SELECT SUM(Qty*ConvertedAmt) FROM S_TimeExpenseLine tel "
+ "WHERE te.S_TimeExpense_ID=tel.S_TimeExpense_ID) "
+ "WHERE S_TimeExpense_ID=? ";
DB.executeUpdate(get_Trx(), sql, getS_TimeExpense_ID());
if (get_Trx() != null) get_Trx().commit();
} // updateHeader
/**
* Create Period Controls
*
* @param ctx context
* @param AD_Client_ID client
* @param sp server process
* @param trx transaction
*/
public static void createPeriodControls(Ctx ctx, int AD_Client_ID, SvrProcess sp, Trx trx) {
s_log.info("AD_Client_ID=" + AD_Client_ID);
// Delete Duplicates
String sql =
"DELETE FROM C_PeriodControl "
+ "WHERE (C_Period_ID, DocBaseType) IN "
+ "(SELECT C_Period_ID, DocBaseType "
+ "FROM C_PeriodControl pc2 "
+ "GROUP BY C_Period_ID, DocBaseType "
+ "HAVING COUNT(*) > 1)"
+ " AND C_PeriodControl_ID NOT IN "
+ "(SELECT MIN(C_PeriodControl_ID) "
+ "FROM C_PeriodControl pc3 "
+ "GROUP BY C_Period_ID, DocBaseType)";
int no = DB.executeUpdate(trx, sql);
s_log.info("Duplicates deleted #" + no);
// Insert Missing
sql =
"SELECT DISTINCT p.AD_Client_ID, p.C_Period_ID, dbt.DocBaseType "
+ "FROM C_Period p, "
+ "C_DocBaseType dbt "
+ "WHERE p.AD_Client_ID=? "
+ " AND NOT EXISTS"
+ " (SELECT * FROM C_PeriodControl pc "
+ "WHERE pc.C_Period_ID=p.C_Period_ID AND pc.DocBaseType=dbt.DocBaseType)"
+ " AND (dbt.AD_Client_ID = 0 OR p.AD_Client_ID = dbt.AD_Client_ID)";
PreparedStatement pstmt = null;
ResultSet rs = null;
int counter = 0;
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, AD_Client_ID);
rs = pstmt.executeQuery();
while (rs.next()) {
int Client_ID = rs.getInt(1);
int C_Period_ID = rs.getInt(2);
String DocBaseType = rs.getString(3);
s_log.config(
"AD_Client_ID="
+ Client_ID
+ ", C_Period_ID="
+ C_Period_ID
+ ", DocBaseType="
+ DocBaseType);
//
MPeriodControl pc = new MPeriodControl(ctx, Client_ID, C_Period_ID, DocBaseType, trx);
if (pc.save()) {
counter++;
s_log.fine(pc.toString());
} else s_log.warning("Not saved: " + pc);
}
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
if (sp != null) sp.addLog(0, null, new BigDecimal(counter), "@C_PeriodControl_ID@ @Created@");
s_log.info("Inserted #" + counter);
} // createPeriodControls