Knowledgebase: EC Enterprise
EC Enterprise: Script to Populate Meter Addresses from Place Addresses
Posted by Barry Kroeker, Last modified by Joel Brickell on 28 May 2015 03:06 PM

The script below populates the meter address field by copying the address of its parent place (building). It creates a new address record, so that if the parent address is changed, the meter address will NOT be changed. The script ignores any meter that already has an address record.

You can run this script from SQL Enterprise Manager or use the EnergyCAP Administrator program to upload it to the database, then execute it. Consult EnergyCAP for assistance. Always back up your EnergyCAP database prior to running scripts against it.

______________________________________

CREATE PROCEDURE PopulateMeterAddressFromPlace
AS
BEGIN
DECLARE @logicalDeviceID INT,
@addressID INT,
@contactID INT,
@corporationID INT,
@addressTypeID INT,
@line1 VARCHAR(32),
@line2 VARCHAR(32),
@city VARCHAR(32),
@state VARCHAR(3),
@postalCode VARCHAR(10),
@country VARCHAR(32)
DECLARE logicalDeviceCursor CURSOR FOR
SELECT
LD.logicalDeviceID,
A.addressID, A.contactID, A.corporationID, A.addressTypeID,
A.line1, A.line2, A.city, A.state, A.postalCode, A.country
FROM
LogicalDevice LD
INNER JOIN Place P ON LD.placeID = P.placeID
INNER JOIN Address A ON P.addressID = A.addressID
WHERE
LD.logicalDeviceTypeID = 1 AND
LD.addressID IS NULL AND
P.addressID IS NOT NULL

OPEN logicalDeviceCursor
FETCH NEXT FROM logicalDeviceCursor INTO @logicalDeviceID, @addressID, @contactID,
@corporationID, @addressTypeID, @line1, @line2, @city, @state, @postalCode, @country

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO
Address(contactID, corporationID, addressTypeID, line1, line2, city, state,
postalCode, country)
SELECT
@contactID, @corporationID, @addressTypeID, @line1, @line2, @city, @state,
@postalCode, @country

UPDATE
LogicalDevice
SET
addressID = IDENT_CURRENT('Address')
WHERE
logicalDeviceID = @logicalDeviceID

-- corporationID should be NULL for all parent places; if it is not,
-- we need to insert new corporation records as well
IF (@corporationID IS NOT NULL)
BEGIN
INSERT INTO
Corporation(dnbNumber, industryCode, taxIDNumber)
VALUES
(NULL, NULL, NULL)

UPDATE
Address
SET
corporationID = IDENT_CURRENT('Corporation')
WHERE
addressID = IDENT_CURRENT('Address')
END

FETCH NEXT FROM logicalDeviceCursor INTO @logicalDeviceID, @addressID, @contactID,
@corporationID, @addressTypeID, @line1, @line2, @city, @state, @postalCode, @country
END

CLOSE logicalDeviceCursor
DEALLOCATE logicalDeviceCursor
END

_________________________________

by Administrator  last modified 06/25/2007  EnergyCAP Enterprise Rel. 4.x and later

(273 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments: