String sql = "SELECT map.material_alloc_period_id," + "map.sap_material_id," + "map.period_start_date," + "map.period_end_date," + "ma.alloc_quantity AS allocation_amount," + "SUM(oi.sap_quantity) AS ordered_amount" + "FROM v_material_alloc_period map, v_material_alloc, v_customer_partner cp, v_customer csoldto, " + "v_order_header oh, v_order_item oi " + // Find the applicable material alloc periods "WHERE cp.sap_partner_function = '" + SAPPartnerFunctionConstants.SALES_REP + "' " + "AND cp.customer_id = csoldto.customer_id " + "AND csoldto.sap_customer_id = ? " + "AND csoldto.sap_sales_org_id = ? " + "AND ma.pinpoint_accountno = cp.pinpoint_accountno " + "AND map.material_alloc_period_id = ma.material_alloc_period_id " + "AND map.sap_sales_org_id = ? " + "AND TRUNC(?) >= TRUNC(map.period_start_date) " + "AND TRUNC(?) <= TRUNC(map.period_end_date) " + // Find the applicable orders "AND TRUNC(oh.sap_date_created) >= TRUNC(map.period_start_date) " + "AND TRUNC(oh.sap_date_created) <= TRUNC(map.period_end_date) " + "AND oh.sap_sold_to_customer_id = ? " + "AND oh.sap_sales_org_id = ? " + "AND oi.order_header_id = oh.order_header_id " + "AND oi.sap_material_id IN (" + SQLUtil.getPlaceholders(sapMaterialIds.size()) + ") " + "GROUP BY material_alloc_period_id, sap_material_id, period_start_date, period_end_date, allocation_amount";