@Override
public List<I_M_HU_Assignment> retrieveIncludedHUAssignments(final I_M_HU_Assignment assignment) {
final IQueryBL queryBL = Services.get(IQueryBL.class);
final IQueryBuilder<I_M_HU_Assignment> queryBuilder =
queryBL.createQueryBuilder(I_M_HU_Assignment.class, assignment);
queryBuilder
// references same record..
.addEqualsFilter(I_M_HU_Assignment.COLUMN_AD_Table_ID, assignment.getAD_Table_ID())
.addEqualsFilter(I_M_HU_Assignment.COLUMN_Record_ID, assignment.getRecord_ID())
// ..and same toplevel-HU..
.addEqualsFilter(I_M_HU_Assignment.COLUMN_M_HU_ID, assignment.getM_HU_ID());
// ..but additionally references one of the HU's components (TU or LU)
final ICompositeQueryFilter<I_M_HU_Assignment> subFilter =
queryBL.createCompositeQueryFilter(I_M_HU_Assignment.class);
subFilter
.setJoinOr()
.addNotEqualsFilter(I_M_HU_Assignment.COLUMN_M_LU_HU_ID, null)
.addNotEqualsFilter(I_M_HU_Assignment.COLUMN_M_TU_HU_ID, null);
queryBuilder.filter(subFilter);
//
return queryBuilder.create().list(I_M_HU_Assignment.class);
}
@Override
public void cleanup(final IMRPContext mrpContext, final IMRPExecutor executor) {
// If DRP module is not activated, then skip the cleanup
if (!mrpContext.isRequireDRP()) {
return;
}
final IQueryBL queryBL = Services.get(IQueryBL.class);
//
// Delete generated distribution orders
// (i.e. Distribution Order with Draft Status)
final ICompositeQueryFilter<I_DD_Order> filters =
queryBL.createCompositeQueryFilter(I_DD_Order.class);
filters.addEqualsFilter(I_DD_Order.COLUMNNAME_DocStatus, X_DD_Order.DOCSTATUS_Drafted);
//
// Only those which were generated by MRP
filters.addEqualsFilter(I_DD_Order.COLUMN_MRP_Generated, true);
// Only those which are allowed to be deleted by MRP cleanup
filters.addEqualsFilter(I_DD_Order.COLUMN_MRP_AllowCleanup, true);
//
// Only for our AD_Client_ID
filters.addEqualsFilter(I_DD_Order.COLUMNNAME_AD_Client_ID, mrpContext.getAD_Client_ID());
//
// Only for our AD_Org_ID
filters.addEqualsFilter(I_DD_Order.COLUMNNAME_AD_Org_ID, mrpContext.getAD_Org().getAD_Org_ID());
//
// Only those DD Orders which are from our Plant or does not have a plant at all
filters.addInArrayFilter(
I_DD_Order.COLUMNNAME_PP_Plant_ID, null, mrpContext.getPlant().getS_Resource_ID());
//
// Only those which have a line with Destination Warehouse same as our warehouse
final int targetWarehouseId = mrpContext.getM_Warehouse().getM_Warehouse_ID();
filters.addFilter(
Services.get(IDDOrderDAO.class)
.getDDOrdersForTargetWarehouseQueryFilter(targetWarehouseId));
//
// If we are running in an constrained MRP Context, filter only those documents
if (mrpContext.getEnforced_PP_MRP_Demand_ID() > 0) {
final IQuery<I_PP_MRP> mrpQuery =
createMRPQueryBuilderForCleanup(mrpContext, executor)
.createQueryBuilder()
.addEqualsFilter(I_PP_MRP.COLUMN_TypeMRP, X_PP_MRP.TYPEMRP_Supply)
.create();
filters.addInSubQueryFilter(
I_DD_Order.COLUMN_DD_Order_ID, I_PP_MRP.COLUMN_DD_Order_ID, mrpQuery);
}
deletePO(mrpContext, executor, I_DD_Order.class, filters);
}
@Override
@Cached(cacheName = I_AD_Message.Table_Name + "#by#" + I_AD_Message.COLUMNNAME_Value)
public I_AD_Message retrieveByValue(@CacheCtx final Properties ctx, final String value) {
final IQueryBuilder<I_AD_Message> queryBuilder =
Services.get(IQueryBL.class)
.createQueryBuilder(I_AD_Message.class)
.setContext(ctx, ITrx.TRXNAME_None);
final ICompositeQueryFilter<I_AD_Message> filters = queryBuilder.getFilters();
filters.addEqualsFilter(I_AD_Message.COLUMNNAME_Value, value);
filters.addOnlyActiveRecordsFilter();
return queryBuilder.create().firstOnly(I_AD_Message.class);
}
public final IQueryBuilder<I_M_Material_Tracking_Ref>
createMaterialTrackingRefQueryBuilderForModels(final List<?> models) {
if (models == null || models.isEmpty()) {
return null;
}
final IQueryBL queryBL = Services.get(IQueryBL.class);
//
// Iterate models and build model filters
final ICompositeQueryFilter<I_M_Material_Tracking_Ref> modelFilters =
queryBL.createCompositeQueryFilter(I_M_Material_Tracking_Ref.class).setJoinOr();
for (final Object model : models) {
if (model == null) {
continue;
}
final int adTableId = InterfaceWrapperHelper.getModelTableId(model);
final int recordId = InterfaceWrapperHelper.getId(model);
final ICompositeQueryFilter<I_M_Material_Tracking_Ref> filter =
queryBL
.createCompositeQueryFilter(I_M_Material_Tracking_Ref.class)
.addEqualsFilter(I_M_Material_Tracking_Ref.COLUMN_AD_Table_ID, adTableId)
.addEqualsFilter(I_M_Material_Tracking_Ref.COLUMN_Record_ID, recordId);
modelFilters.addFilter(filter);
}
// No models provided
if (modelFilters.isEmpty()) {
return null;
}
//
// Create M_Material_Tracking_Ref query
final IQueryBuilder<I_M_Material_Tracking_Ref> materialTrackingRefQueryBuilder =
queryBL
.createQueryBuilder(I_M_Material_Tracking_Ref.class)
.setContext(getCtx(), getTrxName())
.filter(modelFilters);
return materialTrackingRefQueryBuilder;
}
/**
* Called by {@link #createQueryFilter()} to create the MRP_Exclude filter.
*
* @return MRP_Exclude filter
*/
private IQueryFilter<I_PP_MRP> createQueryFilter_MRP_Exclude() {
final IContextAware contextProvider = getContextProviderToUse();
final ICompositeQueryFilter<I_PP_MRP> filters =
queryBL.createCompositeQueryFilter(I_PP_MRP.class);
// Exclude BPartners
{
final IQuery<I_C_BP_Group> excludedBPGroupsQuery =
queryBL
.createQueryBuilder(I_C_BP_Group.class)
.setContext(contextProvider)
.addEqualsFilter(I_C_BP_Group.COLUMN_MRP_Exclude, X_C_BP_Group.MRP_EXCLUDE_Yes)
.create();
final IQuery<I_C_BPartner> excludedBPartnersQuery =
queryBL
.createQueryBuilder(I_C_BPartner.class)
.setContext(contextProvider)
.setJoinOr()
.addEqualsFilter(I_C_BPartner.COLUMN_MRP_Exclude, X_C_BPartner.MRP_EXCLUDE_Yes)
.addInSubQueryFilter(
I_C_BPartner.COLUMNNAME_C_BP_Group_ID,
I_C_BP_Group.COLUMNNAME_C_BP_Group_ID,
excludedBPGroupsQuery)
.create();
filters.addNotInSubQueryFilter(
I_PP_MRP.COLUMNNAME_C_BPartner_ID,
I_C_BPartner.COLUMNNAME_C_BPartner_ID,
excludedBPartnersQuery);
}
// Exclude Products
{
final IQuery<I_M_Product_Category> excludedProductCategoriesQuery =
queryBL
.createQueryBuilder(I_M_Product_Category.class)
.setContext(contextProvider)
.addEqualsFilter(
I_M_Product_Category.COLUMN_MRP_Exclude, X_C_BP_Group.MRP_EXCLUDE_Yes)
.create();
final IQuery<I_M_Product> excludedProductsQuery =
queryBL
.createQueryBuilder(I_M_Product.class)
.setContext(contextProvider)
.setJoinOr()
.addEqualsFilter(I_M_Product.COLUMN_MRP_Exclude, X_M_Product.MRP_EXCLUDE_Yes)
.addInSubQueryFilter(
I_M_Product.COLUMNNAME_M_Product_Category_ID,
I_M_Product_Category.COLUMNNAME_M_Product_Category_ID,
excludedProductCategoriesQuery)
.create();
filters.addNotInSubQueryFilter(
I_PP_MRP.COLUMNNAME_M_Product_ID,
I_M_Product.COLUMNNAME_M_Product_ID,
excludedProductsQuery);
}
// Exclude Warehouses
{
final IQuery<I_M_Warehouse> excludedWarehousesQuery =
queryBL
.createQueryBuilder(I_M_Warehouse.class)
.setContext(contextProvider)
.addEqualsFilter(I_M_Warehouse.COLUMN_MRP_Exclude, X_M_Warehouse.MRP_EXCLUDE_Yes)
.create();
filters.addNotInSubQueryFilter(
I_PP_MRP.COLUMNNAME_M_Warehouse_ID,
I_M_Warehouse.COLUMNNAME_M_Warehouse_ID,
excludedWarehousesQuery);
}
// Exclude Plants
{
final IQuery<I_S_Resource> excludedPlantsQuery =
queryBL
.createQueryBuilder(I_S_Resource.class)
.setContext(contextProvider)
.addEqualsFilter(I_S_Resource.COLUMN_MRP_Exclude, X_S_Resource.MRP_EXCLUDE_Yes)
.create();
filters.addNotInSubQueryFilter(
I_PP_MRP.COLUMNNAME_S_Resource_ID,
I_S_Resource.COLUMNNAME_S_Resource_ID,
excludedPlantsQuery);
}
return filters;
}
private IQueryFilter<I_PP_MRP> createQueryFilter() {
final IContextAware contextProvider = getContextProviderToUse();
final ICompositeQueryFilter<I_PP_MRP> filters =
queryBL.createCompositeQueryFilter(I_PP_MRP.class);
//
// Filter only Active records
final boolean onlyActiveRecords = isOnlyActiveRecords();
if (onlyActiveRecords) {
filters.addOnlyActiveRecordsFilter();
}
//
// Filter by AD_Client_ID
final int adClientId = getAD_Client_ID_ToUse();
if (adClientId >= 0) {
filters.addEqualsFilter(I_PP_MRP.COLUMNNAME_AD_Client_ID, adClientId);
}
//
// Filter by AD_Org_ID
final int adOrgId = getAD_Org_ID_ToUse();
if (adOrgId >= 0) {
filters.addEqualsFilter(I_PP_MRP.COLUMNNAME_AD_Org_ID, adOrgId);
}
//
// Filter by M_Warehouse_ID
final int warehouseId = getM_Warehouse_ID_ToUse();
if (warehouseId > 0) {
filters.addEqualsFilter(I_PP_MRP.COLUMNNAME_M_Warehouse_ID, warehouseId);
}
//
// Filter by Plant (only those lines which have our plant ID or don't have the plant ID set at
// all)
// AIM: Clear separation on plant level (06594)
final Set<Integer> plantIds = getPP_Plant_IDs_ToUse();
if (plantIds != null && !plantIds.isEmpty()) {
filters.addInArrayFilter(I_PP_MRP.COLUMNNAME_S_Resource_ID, plantIds);
}
//
// Filter by M_Product_ID
final int productId = getM_Product_ID_ToUse();
if (productId > 0) {
filters.addEqualsFilter(I_PP_MRP.COLUMNNAME_M_Product_ID, productId);
}
//
// Filter by Product's Low Level Code (LLC)
final int productLLC = getLowLevelCode();
if (productLLC >= 0) {
final IQuery<I_M_Product> productQuery =
queryBL
.createQueryBuilder(I_M_Product.class)
.setContext(contextProvider)
.filter(
new EqualsQueryFilter<I_M_Product>(I_M_Product.COLUMNNAME_LowLevel, productLLC))
.create();
filters.addInSubQueryFilter(
I_PP_MRP.COLUMNNAME_M_Product_ID, I_M_Product.COLUMNNAME_M_Product_ID, productQuery);
}
//
// Filter by TypeMRP
final String typeMRP = getTypeMRP();
if (!Check.isEmpty(typeMRP, true)) {
filters.addEqualsFilter(I_PP_MRP.COLUMNNAME_TypeMRP, typeMRP);
}
//
// Filter by MRPFirmType (i.e. DocStatus)
final MRPFirmType mrpFirmType = getMRPFirmType();
if (mrpFirmType != null) {
final List<String> docStatuses = mrpFirmType.getDocStatuses();
filters.addInArrayFilter(I_PP_MRP.COLUMNNAME_DocStatus, docStatuses);
}
//
// Filter by OrderType (i.e. similar with DocBaseType)
final Set<String> orderTypes = getOrderTypes();
if (!Check.isEmpty(orderTypes)) {
filters.addInArrayFilter(I_PP_MRP.COLUMNNAME_OrderType, orderTypes);
}
//
// Filter by DatePromised
final Date datePromisedMax = getDatePromisedMax();
if (datePromisedMax != null) {
filters.addCompareFilter(
I_PP_MRP.COLUMNNAME_DatePromised, Operator.LessOrEqual, datePromisedMax);
}
//
// Filter Non Zero Qty
final boolean qtyNotZero = isQtyNotZero();
if (qtyNotZero) {
filters.addNotEqualsFilter(I_PP_MRP.COLUMNNAME_Qty, BigDecimal.ZERO);
}
//
// Filter by IsAvailable flag
final Boolean mrpAvailable = getMRPAvailable();
if (mrpAvailable != null) {
filters.addEqualsFilter(I_PP_MRP.COLUMNNAME_IsAvailable, mrpAvailable);
}
//
// Filter by Referenced Model
// i.e. Only those MRP records which are referencing our model
final Object referencedModel = getReferencedModel();
if (referencedModel != null) {
final String modelTableName = InterfaceWrapperHelper.getModelTableName(referencedModel);
final String modelKeyColumnName = modelTableName + "_ID";
final int modelId = InterfaceWrapperHelper.getId(referencedModel);
filters.addEqualsFilter(modelKeyColumnName, modelId);
}
//
// Filter only those MRP lines where PP_Product_BOMLine_ID is not set
if (_ppOrderBOMLine_Null) {
filters.addEqualsFilter(I_PP_MRP.COLUMN_PP_Order_BOMLine_ID, null);
}
//
// Filter by specific PP_MRP_IDs
final Set<Integer> mrpIds = getOnlyPP_MRP_IDs();
if (mrpIds != null && !mrpIds.isEmpty()) {
filters.addInArrayFilter(I_PP_MRP.COLUMNNAME_PP_MRP_ID, mrpIds);
}
//
// Filter by enforced MRP demand, i.e.
// * accept only MRP demand records which have the enforced MRP Demand ID
// * or accept only MRP supply records which are allocated to the enforced MRP Demand ID
if (_enforced_PP_MRP_Demand_ID > 0) {
final ICompositeQueryFilter<I_PP_MRP> filterMRPDemands =
queryBL
.createCompositeQueryFilter(I_PP_MRP.class)
.setJoinAnd()
.addEqualsFilter(I_PP_MRP.COLUMNNAME_TypeMRP, X_PP_MRP.TYPEMRP_Demand)
.addEqualsFilter(I_PP_MRP.COLUMNNAME_PP_MRP_ID, _enforced_PP_MRP_Demand_ID);
final IQuery<I_PP_MRP_Alloc> mrpSuppliesSubQuery =
queryBL
.createQueryBuilder(I_PP_MRP.class)
.setContext(contextProvider)
.filter(filterMRPDemands)
.andCollectChildren(I_PP_MRP_Alloc.COLUMN_PP_MRP_Demand_ID, I_PP_MRP_Alloc.class)
.create();
final ICompositeQueryFilter<I_PP_MRP> filterMRPSupplies =
queryBL
.createCompositeQueryFilter(I_PP_MRP.class)
.setJoinAnd()
.addEqualsFilter(I_PP_MRP.COLUMNNAME_TypeMRP, X_PP_MRP.TYPEMRP_Supply)
.addInSubQueryFilter(
I_PP_MRP.COLUMN_PP_MRP_ID,
I_PP_MRP_Alloc.COLUMN_PP_MRP_Supply_ID,
mrpSuppliesSubQuery);
final ICompositeQueryFilter<I_PP_MRP> filterEnforcedMRPDemand =
queryBL
.createCompositeQueryFilter(I_PP_MRP.class)
.setJoinOr()
.addFilter(filterMRPDemands)
.addFilter(filterMRPSupplies);
filters.addFilter(filterEnforcedMRPDemand);
}
//
// Apply MRP_Exclude filters
if (_skipIfMRPExcluded) {
final IQueryFilter<I_PP_MRP> skipIfMRPExcludedFilters = createQueryFilter_MRP_Exclude();
filters.addFilter(skipIfMRPExcludedFilters);
}
//
// Return built filters
return filters;
}
private final void compileIfNeeded() {
//
// Check: if is alread compiled, there is no need to compile it again
if (_compiled) {
return;
}
//
// Check: if we have no filters, we can set the status right away
if (filters.isEmpty()) {
_sqlWhereClause = isDefaultAccept() ? DEFAULT_SQL_TRUE : DEFAULT_SQL_FALSE;
_sqlFilters = Collections.emptyList();
_nonSqlFilters = null;
_compiled = true;
return;
}
final List<ISqlQueryFilter> resultSqlFilters = new ArrayList<ISqlQueryFilter>();
final StringBuilder resultSqlWhereClause = new StringBuilder();
final List<IQueryFilter<T>> resultNonSqlFilters = new ArrayList<IQueryFilter<T>>();
final List<IQueryFilter<T>> resultAllFiltersSoFar =
new ArrayList<IQueryFilter<T>>(filters.size());
boolean allowSqlFilters = true; // do we allow SQL filter?
for (final IQueryFilter<T> filter : filters) {
final List<ISqlQueryFilter> sqlFilters;
final List<IQueryFilter<T>> nonSqlFilters;
//
// Case: we are not accepting SQL filters
// => no point to check forward but treat this filter as a nonSQL filter
if (!allowSqlFilters) {
sqlFilters = null;
nonSqlFilters = Collections.singletonList(filter);
}
//
// Case: Composite Filter (SQL and non-SQL)
else if (filter instanceof ICompositeQueryFilter) {
final ICompositeQueryFilter<T> compositeFilter = (ICompositeQueryFilter<T>) filter;
// Case: our composite is a pure SQL filter
if (compositeFilter.isPureSql()) {
final ISqlQueryFilter sqlFilter = compositeFilter.asSqlQueryFilter();
sqlFilters = Collections.singletonList(sqlFilter);
nonSqlFilters = null;
}
// Case: our composite is not a pure SQL filter but it's join method is AND
// => we can mix this kind of filters
else if (compositeFilter.isJoinAnd() == this.isJoinAnd() == true) {
sqlFilters = compositeFilter.getSqlFilters();
nonSqlFilters = compositeFilter.getNonSqlFilters();
}
// Case: our composite is not a pure SQL filter and it's join method is not AND
// => we cannot mix SQL and nonSQL in this way so we consider the whole compositeFilter as
// non-sql
else {
sqlFilters = null;
nonSqlFilters = Collections.<IQueryFilter<T>>singletonList(compositeFilter);
}
}
//
// Case: Pure SQL Filter
else if (filter instanceof ISqlQueryFilter) {
final ISqlQueryFilter sqlFilter = (ISqlQueryFilter) filter;
sqlFilters = Collections.singletonList(sqlFilter);
nonSqlFilters = null;
}
//
// Case: non-SQL filter:
else {
sqlFilters = null;
nonSqlFilters = Collections.singletonList(filter);
}
//
// Append the SQL Part (if any)
appendSqlWhereClause(resultSqlWhereClause, resultSqlFilters, sqlFilters);
//
// Append Non-SQL part (if any)
if (nonSqlFilters != null && !nonSqlFilters.isEmpty()) {
resultNonSqlFilters.addAll(nonSqlFilters);
}
//
// Update our all filters so far list
resultAllFiltersSoFar.add(filter);
//
// Case: Until now we allowed SQL filters but our join method is not AND
// ... and we already have SQL Filters and nonSQL filters accumulated
//
// => make all filters as nonSQL
// => clear "resultNonSqlFilters" and "resultSqlWhereClause" because we will not use them from
// now on
// => don't allow SQL filters from now on
if (allowSqlFilters
&& !this.isJoinAnd()
&& !resultSqlFilters.isEmpty()
&& !resultNonSqlFilters.isEmpty()) {
resultNonSqlFilters.clear();
resultNonSqlFilters.addAll(resultAllFiltersSoFar);
resultSqlFilters.clear();
resultSqlWhereClause.setLength(0);
// we are not allowing mixing filters anymore
// => we allow only nonSQL filters; all SQL filters will be considered nonSQL from now on
allowSqlFilters = false;
}
}
this._sqlFilters = Collections.unmodifiableList(resultSqlFilters);
this._nonSqlFilters = Collections.unmodifiableList(resultNonSqlFilters);
this._sqlWhereClause = resultSqlWhereClause.toString();
this._compiled = true;
}
