Print this Page
the big picture
5.2.08 B8 Upload Proposal Procedure
Close this Page

Broker Procedures:
Broker state "B8" is assigned for each energy provider returned rate request spread sheet.   An upload facility is used to directly import the rates as new proposed contracts.

B8 Proposal:
When the Rate Request is returned by the energy provider with rates entered for each service, the returned List is uploaded as a CSV.   Each row becomes a new proposed contract with applicable rates and total cost.
This task is fully automated after the broker ensures that the new rate data is easy to identify in the energy providers List and the document is saved as a CSV.   The use of a CSV ensures that only text can exist in the document so any virus infection is eliminated by saving the document as a CSV.
Productivity is maximised as no data entry is needed to create all proposed contracts.   When at least three proposals have been uploaded, then the broker state can switch to B9 where the client quotation is created.   The broker may mark the recommended proposed contract.

Selection:
The responsible broker may choose to only upload those proposals that are worth uploading and showing to the client for approval.   A proposed contract may be created for each contract period for the same service.

Executive KPI:
Any number of proposals may be returned by energy providers and so this task may take some time to be completed.   Proposed contracts are continually monitored and measured by the day and by the week with annual trend reports.
Data integrity is paramount and so data integrity checks are undertaken every evening to minimise the danger of missing data.   Executive Exception Reports are triggered where proposed contracts are too close to their start date or have expired.

Climate Change Levy (CCL):
HMRC impose a CCL tax on the supply of electricity, gas and associated fuels.   Annual or quartery accounts are needed. VAT is chared on top of the CCT tax, but not for self-supply.   From Apr 2014, the electric rate is £0.00541 and gas rate is £0.00118.

Proposal Placeholders:
ESE26 is the mandatory first column placeholder containing the MPR or MPAN.   This meter number must match a service meter number for the selected client or the row will be rejected.
ESE37 is the mandatory postcode column.   Any postal address field (ESE44ADD) is ignored.   Any company name field (ECU45) is ignored.   Any company registration field (ECU47) is ignored.   Any supplier name field (ECO26SUP) is ignored.
ESE52 is the mandatory Estimated Annual Consumption column.
ESE53 is the mandatory Estimated Annual Spend column.
ECO28 is the mandatory proposed contract start date column.
ECO29 is the mandatory proposed contract end date column.
ECO60 is the mandatory proposed contract SC Rate column.
ECO62 is the mandatory proposed contract SC Units column.
ECO61 is the mandatory proposed contract KVA Rate column.
ECO63 is the mandatory proposed contract KVA Units column.
ECO41 is the mandatory proposed contract primary or day rate column.
ECO42 is the optional proposed contract secondary or night rate column.
ECO43 is the optional proposed contract week day rate column.
ECO44 is the optional proposed contract evening and week end rate column.
ECO45 is the optional proposed contract Dec-Jan Peak rate column.
ECO46 is the optional proposed contract Dec-Jan Off Peak or day rate column.
ECO47 is the optional proposed contract Nov-Feb Peak rate column.
ECO48 is the optional proposed contract Nov-Feb Off Peak rate column.
ECO49 is the optional proposed contract Other rate column.

SC Unit Rules:
The only permitted values (upper or lower case may be used) for column ECO62 are:.
  None as the default codified as NON.
  Pounds/Quarter or codified as PPQ.
  Pounds/Month or codified as PPM.
  Pounds/Day or codified as PPD.
  Pence/Day or codified as DPD.

KVA Unit Rules:
The only permitted values (upper or lower case may be used) for column ECO63 are:.
  None as the default codified as NON.
  Pounds/Quarter/KVA or codified as PQK.
  Pounds/Month/KVA or codified as PMK.
  Pounds/Day/KVA or codified as PDK.

Tuition:
1. The rate proposal List from an energy provider needs to be uploaded as follows.
2. Make sure that each column has a placeholder column heading as identified in the download rate request List.
3. Save the List as a CSV text file in a folder on your computer.
4. Select the applicable client and press the "B8" upload proposal button.
5. Select the energy provider (or supplier) name from the drop down list.
5. Select the CSV file on your local computer for upload - then press the upload button.

Manual alternative:
A proposal contract can be manually added with rates copied from an energy providers proposal document.   The current contract may be cloned to provide a valid framework for the new rates to replace the current rates.

Automated Action:
The uploaded file will be verified, if it is not a CSV or TXT file or it exceeds one million characters, then it will be rejected.
The CSV file will be analysed to discover column heading placeholders.   If column heading placeholders begining with "ESE26" cannot be found, the CSV file will be rejected.   Placeholders must be 5 to 8 characters in length - the last 3 characters are not used and are optional.
Each row that begins with a MPR or MPAN meter number will have its columns decoded in accordance with the placeholder field names.   This means that after the meter number, all other columns may be in any order and extra columns will be ignored.
Every valid row is inserted as a new proposal - every invalid row is rejected.   The layout design used by the client rate request download task B7 may be used as an example.
Every proposal is a copy of the current contract field values with the new rates, supplier and other field values as provided.   It is not practical to create a proposed contract where an existing current contract does not exist.

Upload Rules:
File uploaded must pass stringent security checks to ensure that an attack cannot be made to the application service.   Security begins by rejecting any Office file formats that may have been corrupted with viruses and malware.   Only a plain text file using CSV or TXT file format may be uploaded - these are safe as they cannot contain any vulnerability or malware.
A Comma Separated Values (CSV) file must only use commas to separate field values - commas must NOT be used as thousands separators in number fields or address fields.   A CSV must not contain any symbols such as quotation marks - any row containing quotation marks must be rejected.
Every date field must be in the format of 11 characters as "dd mmm yyyy" with two digits of days, three characters fo month and 4 digits of year - this data format ensures that UK, European and US date formats are not confused.   As an option, the European standard of 8 digits as "YYYYMMDD" may be used.
Rates should be entered as a decimal number such as "12.3456" as pence per unit with up to 4 decimal places.   Any rate not provided is assumed to have a value as 0.00.
* Life cycle is set to "Proposal" (PRO). Contract Step is set to "Quote Requested" (113). Renewal Step is set to "To be scheduled" (NON).
* Term days is set to "150 Days" (150) - this should be selected from the supplier default terms.
* LOT deadline is set to "1 Jan 2023" - no letter of termination can be due for a proposed contract.
* CCL (HMRC Climate Change Levy) Payable is set to "No" as this data is not used by any documented task - this is only applicable to an energy provider or supplier of energy to others.
* VAT Level is set to "Not Applicable" (NON) as this data is not used by any documented task - it is unreasonable to expect different contracts to have different VAT levels for the same supply.
* Charges Loaded is set to "Not Applicable" (NON) as this data is not used by any documented task - every proposed and current contract will be stored with the rates including any commission that is due to be paid by the energy provider.
* FIT is set to "0.00" as this data c=annot be related to any business contract - feed-in-tariff is a separate 20 year contract that cannot change energy providers.
* Green environment is set to "Not Applicable" (NON) as this data is not used by any documented task.
* Renewable Obligations is set to "0.00" as this data is not used by any documented task.
* Smart Service is set to "No" as this data is not used by any documented task.
* Smart Service Data is set to "0.00" as this data is not used by any documented task.

Document Control:
1. Document Title: B8 Upload Proposal Procedure.
3. Keywords: B8 Upload Proposal Procedure.
4. Description: B8 Upload Proposal Procedure.
5. Privacy: Public education service as a benefit to humanity.
6. Issued: 11 Jun 2018.
7. Edition: 1.3.