private static void commissionfact2salesRepFact() {
final Properties ctx = Env.getCtx();
final String tableName = "C_AdvComFact_SalesRepFact";
TableInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName)
.setName("Commissionfact to sales rep fact")
.setAccessLevel(X_AD_Table.ACCESSLEVEL_ClientPlusOrganization)
.save()
.standardColumns();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, C_ADV_COMMISSION_FACT + "_ID")
.setReference(TableDir)
.setMandatory(true)
.setUpdatable(false)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(
ENTITY_TYPE, tableName, I_C_AdvComSalesRepFact.COLUMNNAME_C_AdvComSalesRepFact_ID)
.setReference(TableDir)
.setMandatory(true)
.setUpdatable(false)
.save()
.syncColumn();
}
private static void commissionFact_SalesRepV() {
final Properties ctx = Env.getCtx();
final String tableName = "C_AdvComFact_SalesRepFact_V";
TableInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName)
.setName("Sponsor-Umsatzdatensatz")
.setAccessLevel(X_AD_Table.ACCESSLEVEL_ClientPlusOrganization)
.setView(true)
.save()
.standardColumns();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, I_C_AdvCommissionFact.COLUMNNAME_AD_Table_ID)
.setReference(TableDir)
.setMandatory(true)
.save();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, I_C_AdvCommissionFact.COLUMNNAME_Record_ID)
.setReference(TableDir)
.setMandatory(true)
.save();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, I_C_AdvCommissionFact.COLUMNNAME_DateDoc)
.setReference(TableDir)
.setMandatory(true)
.save();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, I_C_AdvCommissionFact.COLUMNNAME_Status)
.setReference(TableDir)
.setMandatory(true)
.save();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(
ENTITY_TYPE, tableName, I_C_AdvCommissionFact.COLUMNNAME_CommissionPointsBase)
.setReference(TableDir)
.setMandatory(true)
.save();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, I_C_AdvCommissionFact.COLUMNNAME_Qty)
.setReference(TableDir)
.setMandatory(true)
.save();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(
ENTITY_TYPE, tableName, I_C_AdvCommissionFact.COLUMNNAME_CommissionPointsSum)
.setReference(TableDir)
.setMandatory(true)
.save();
}
private static void commissionInstanceFact() {
final Properties ctx = Env.getCtx();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_ADV_COMMISSION_FACT, "DateDoc")
.setReference(DateTime)
.save()
.syncColumn()
.replacesOldCol("DateAcct");
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_ADV_COMMISSION_FACT, C_INCIDENT_LINE_FACT + "_ID")
.setReference(TableDir)
.setParent(true)
.setUpdatable(false)
.setMandatory(false)
.setDefaultValue("-1")
.save()
.syncColumn();
}
private static void commissionRelevantPO() {
final Properties ctx = Env.getCtx();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(
ENTITY_TYPE,
I_C_AdvCommissionRelevantPO.Table_Name,
I_C_AdvCommissionRelevantPO.COLUMNNAME_DateDocColumn_ID)
.setMandatory(true)
.save()
.syncColumn();
}
private static void specificSponsorParams() {
final Properties ctx = Env.getCtx();
final String tableName = "C_AdvComSponsorParam";
TableInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName)
.setName("Spezifische sponsorbezogene Parameter")
.setAccessLevel(X_AD_Table.ACCESSLEVEL_Organization)
.save()
.standardColumns();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, "C_AdvCommissionTerm_ID")
.setReference(TableDir)
.setUpdatable(false)
.setMandatory(true)
.setParent(true)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, "Description")
.setReference(String)
.setFieldLength(1024)
.setUpdatable(true)
.setMandatory(false)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, "Name")
.setReference(String)
.setFieldLength(255)
.setUpdatable(true)
.setMandatory(true)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, "ParamName")
.setReference(String)
.setFieldLength(255)
.setUpdatable(true)
.setMandatory(true)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, "ParamValue")
.setReference(String)
.setFieldLength(255)
.setUpdatable(true)
.setMandatory(true)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, "SeqNo")
.setReference(String)
.setFieldLength(255)
.setUpdatable(true)
.setMandatory(true)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, "AD_Reference_ID")
.setReference(TableDir)
.setUpdatable(true)
.setMandatory(true)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, "AD_Reference_Value_ID")
.setReference(Table)
.setReferenceValue(4)
.setUpdatable(true)
.setMandatory(true)
.save()
.syncColumn();
IndexTableInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, "Name_SponsorParam_Idx")
.setUnique(true)
.save()
.addIndexColumns("Name", "C_AdvCommissionTerm_ID")
.syncIndexTable();
IndexTableInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, tableName, "ParamName_SponsorParam_Idx")
.setUnique(true)
.save()
.addIndexColumns("ParamName", "C_AdvCommissionTerm_ID")
.syncIndexTable();
}
private static void commissionIncidentFact() {
final Properties ctx = Env.getCtx();
TableInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_INCIDENT_LINE_FACT)
.setName("Commission Incident Fact")
.setAccessLevel(X_AD_Table.ACCESSLEVEL_ClientPlusOrganization)
.save()
.standardColumns();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_INCIDENT_LINE_FACT, C_INCIDENT_LINE + "_ID")
.setReference(TableDir)
.setParent(true)
.setMandatory(true)
.setUpdatable(false)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_INCIDENT_LINE_FACT, "AD_Table_ID")
.setReference(TableDir)
.setMandatory(true)
.setUpdatable(false)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_INCIDENT_LINE_FACT, "Record_ID")
.setReference(Button)
.setFieldLength(20)
.setMandatory(true)
.setUpdatable(false)
.save()
.syncColumn();
IndexTableInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_INCIDENT_LINE_FACT, C_INCIDENT_LINE_FACT + "_T_R_IDX")
.setUnique(true)
.save()
.addIndexColumns("AD_Table_ID", "Record_ID")
.syncIndexTable();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_INCIDENT_LINE_FACT, "C_BPartner_ID")
.setReference(TableDir)
.setMandatory(true)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_INCIDENT_LINE_FACT, "DateDoc")
.setReference(DateTime)
.setMandatory(true)
.save()
.syncColumn();
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_INCIDENT_LINE_FACT, "C_AdvCommissionRelevantPO_ID")
.setReference(TableDir)
.setMandatory(true)
.save()
.syncColumn();
}
private static void commissionInstance() {
final Properties ctx = Env.getCtx();
logger.info("Column 'C_IncidentLine_ID'");
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_ADV_COMMISSION_INSTANCE, C_INCIDENT_LINE + "_ID")
.setReference(TableDir)
.setParent(true)
.setUpdatable(false)
.setMandatory(false)
.setDefaultValue("-1")
.save()
.syncColumn();
logger.info("Column 'LevelForecast'");
final MColumn levelForecastCol =
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_ADV_COMMISSION_INSTANCE, "LevelForecast")
.setReference(Integer)
.save()
.syncColumn()
.getColumn();
logger.info("Field for column 'LevelForecast'");
FieldInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, levelForecastCol)
.setWindowAndTab("Buchauszug", "Vorgang")
.setVisible(true)
.save();
logger.info("Column 'LevelCalculation'");
final MColumn levelCalculationCol =
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_ADV_COMMISSION_INSTANCE, "LevelCalculation")
.setReference(Integer)
.save()
.syncColumn()
.replacesOldCol("LevelCommission")
.getColumn();
logger.info("Field for column 'LevelCalculation'");
FieldInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, levelCalculationCol)
.setWindowAndTab("Buchauszug", "Vorgang")
.setVisible(true)
.save();
logger.info("Column 'LevelHierarchy'");
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_ADV_COMMISSION_INSTANCE, "LevelHierarchy")
.setReference(Integer)
.save()
.syncColumn();
// there is a view c_advcommission_info_v that refers to 'level'
// so in order to drop the level column, one needs to change that view
// TODO create an installer that executes generic SQL
// DROP VIEW c_advcommission_info_v;
//
// CREATE OR REPLACE VIEW c_advcommission_info_v AS
// SELECT max(i.c_advcommissioninstance_id) AS
// c_advcommission_info_v_id, max(i.ad_client_id) AS ad_client_id,
// max(i.ad_org_id) AS ad_org_id, max(i.created) AS created,
// max(i.createdby) AS createdby, max(i.updated) AS updated,
// max(i.updatedby) AS updatedby, ssr.c_bpartner_id,
// i.c_advcommissionterm_id, i."levelforecast", p.startdate, p.enddate,
// sum(pr.commissionpoints) AS points_predicted,
// sum(ca.commissionpoints) AS points_calculated,
// sum(pa.commissionpoints) AS points_paid
// FROM c_advcommissioninstance i
// LEFT JOIN c_sponsor_salesrep ssr ON i.c_sponsor_salesrep_id =
// ssr.c_sponsor_id AND ssr.isactive = 'Y'::bpchar AND ssr.c_bpartner_id
// > 0::numeric
// LEFT JOIN ( SELECT p.startdate, p.enddate, t.c_advcommissionterm_id
// FROM c_period p
// LEFT JOIN c_year y ON y.c_year_id = p.c_year_id
// LEFT JOIN c_advcommissioncondition c ON c.c_calendar_id =
// y.c_calendar_id
// LEFT JOIN c_advcommissionterm t ON t.c_advcommissioncondition_id =
// c.c_advcommissioncondition_id) p ON p.c_advcommissionterm_id =
// i.c_advcommissionterm_id
// LEFT JOIN ( SELECT f.commissionpoints, f.c_advcommissioninstance_id,
// f.datedoc
// FROM c_advcommissionfact f
// WHERE f.commissionpoints > 0::numeric AND f.status::text =
// 'PR'::text) pr ON pr.c_advcommissioninstance_id =
// i.c_advcommissioninstance_id AND ssr.validfrom <= pr.datedoc AND
// ssr.validto >= pr.datedoc AND p.startdate <= pr.datedoc AND p.enddate
// >= pr.datedoc
// LEFT JOIN ( SELECT f.commissionpoints, f.c_advcommissioninstance_id,
// f.datedoc
// FROM c_advcommissionfact f
// WHERE f.commissionpoints > 0::numeric AND f.status::text =
// 'CA'::text) ca ON ca.c_advcommissioninstance_id =
// i.c_advcommissioninstance_id AND ssr.validfrom <= ca.datedoc AND
// ssr.validto >= ca.datedoc AND p.startdate <= ca.datedoc AND p.enddate
// >= ca.datedoc
// LEFT JOIN ( SELECT f.commissionpoints, f.c_advcommissioninstance_id,
// f.datedoc
// FROM c_advcommissionfact f
// WHERE f.commissionpoints > 0::numeric AND f.status::text =
// 'PA'::text) pa ON pa.c_advcommissioninstance_id =
// i.c_advcommissioninstance_id AND ssr.validfrom <= pa.datedoc AND
// ssr.validto >= pa.datedoc AND p.startdate <= pa.datedoc AND p.enddate
// >= pa.datedoc
// WHERE i.isactive = 'Y'::bpchar AND (pr.commissionpoints > 0::numeric
// OR ca.commissionpoints > 0::numeric OR pa.commissionpoints >
// 0::numeric)
// GROUP BY ssr.c_bpartner_id, i.c_advcommissionterm_id,
// i."levelforecast", p.startdate, p.enddate
// ORDER BY ssr.c_bpartner_id, i.c_advcommissionterm_id,
// i."levelforecast", p.startdate;
//
// GRANT ALL ON TABLE c_advcommission_info_v TO adempiere;
logger.info("Replacing column 'Level' with 'LevelHierarchy'");
final MColumn levelHierarchyCol =
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_ADV_COMMISSION_INSTANCE, "LevelHierarchy")
.save()
.replacesOldCol("Level")
.getColumn();
logger.info("Field for column 'LevelHierarchy'");
FieldInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, levelHierarchyCol)
.setWindowAndTab("Buchauszug", "Vorgang")
.setVisible(true)
.save();
logger.info("Index for table " + C_ADV_COMMISSION_INSTANCE);
IndexTableInstaller.mkInst(ctx, trxName)
.createOrUpdate(
ENTITY_TYPE, C_ADV_COMMISSION_INSTANCE, levelHierarchyCol.getColumnName() + "_IDX")
.setUnique(true)
.save()
.addIndexColumns(
COLUMNNAME_Record_ID,
COLUMNNAME_AD_Table_ID,
COLUMNNAME_C_AdvCommissionTerm_ID,
COLUMNNAME_LevelHierarchy)
.syncIndexTable();
logger.info("Column 'Points_ToCalculate'");
final MColumn PointsToCalcCol =
ColumnInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, C_ADV_COMMISSION_INSTANCE, "Points_ToCalculate")
.setReference(Amount)
.setColumnSQL(
"(select sum(CommissionPoints) from C_AdvCommissionFact f where f.C_AdvCommissionInstance_ID=C_AdvCommissionInstance.C_AdvCommissionInstance_ID AND f.Status='CP')")
.save()
.getColumn();
logger.info("Field for column 'Points_ToCalculate'");
FieldInstaller.mkInst(ctx, trxName)
.createOrUpdate(ENTITY_TYPE, PointsToCalcCol)
.setWindowAndTab("Buchauszug", "Vorgang")
.setVisible(true)
.save();
}
