Skip to main content

Adjustment Mapping for Aurea items or GL Account Code

Upasana Mukherjee
  • Updated

Overview

You need to have a set of Aurea items mapped where all of the AR-Adjusted Amount items are the offset of the other items. It is likely that for the items, the GL Account is dependent upon a specific Description and the GL Subaccount is dependent upon the Utility.  These items are erroring out because of either the utility or the description not being added to Aurea.

Solution

Please raise a support ticket with us with the following information:

  • The data for the items to be mapped should be provided in the below format:
GLAccountCode, GLAccountCodeMultiplier, Description, GLAccountCodeOffset, GLAccountCodeOffsetMultiplier, GLAccountCodeOffsetDescription

The value for GLAccountCodeOffsetMultiplier is -1 for all GL Account Codes.

You can request to know the current existing GL Account codes and we will share them for your reference.

  • The newly added GL Account Codes would also need to be mapped to be picked for reporting.
    Please specify if you want these GL Account Codes to be mapped to Adjustment.
    If these are to be mapped with ARAdjustmentType then, provide the ARAdjustmentType for the corresponding GLAccountCode.

You can request us for a sample Adjustment mapping and we can share one for, say, "Early Termination Fee Adjustment".

We will add the provided GL Account Codes, perform the necessary Adjustment mappings and inform you.

<supportagent>

Prerequisites:

  • Access to the Client database

 

Steps:

Sharing sample information with client

  • If the client requests to know the current existing GL Account codes, run the select query below and share the query result with the client.
select * from GLAccountCode
  • If the client requests a sample Adjustment mapping, run the select query below to get the Adjustment mapping for the AdjustmentType "Early Termination Fee Adjustment" and share the query result with the client.
SELECT ar.*,a.*, c.*
FROM GLAccountCodeARAdjustmentType a
join GLAccountCode c on c.GLAccountCodeID = a.GLAccountCodeID 
join ARAdjustmentType ar on ar.ArAdjTypeID =  a.ArAdjTypeID
where a.ARAdjTypeID= 14

 

Adding GL Account Codes and mapping

  1. Run an insert query to add the GL Account Codes using the following query.
    Replace the values for the columns as per the given set of data.

    insert into GLAccountCode 
    (GLAccountCode, GLAccountCodeMultiplier, Description, GLAccountCodeOffset,
    GLAccountCodeOffsetMultiplier, GLAccountCodeOffsetDescription)
    values 
    ('520076|SA-ER-TNMP', '1' , 'Other Fees ERCOT-TNMP' , 
    '150000|00-ER-TNMP', '-1' ,'A/R [Power:TNMP]')
  2. Next, run a select query to get the list of IDs for the inserted data in the previous step.
     Note the GLAccountCodeID per entered GLAccountCode.
    select GLAccountCodeID 
    from GLAccountCode 
    where GLAccountCode in ('code1', 'code2')
  3. Find the ARAdjTypeID value per ARAdjustmentType in the given list of ARAdjustmentType.
    Replace the parameter description with the value of the given ARAdjustmentType.
    select ARAdjTypeID 
    from ARAdjustmentType 
    where description like '%debit for refund%'
  4. Find the LDCID value for the utility to which the GL Account Code is linked.
    The value for GLAccountCodeOffsetDescription has the LDCShortName appended.
    The acronym appended to the terms Gas or Power in the description is the LDCShortName.
    It can be double-checked with the value for GLAccountCodeOffset. The last 3-4 characters would typically denote the LDCShortName.
    Example 1- if the value for GLAccountCodeOffsetDescription is A/R [Gas:PGE], then LDCShortName is PGE. 
    The associated GLAccountCodeOffset value, 150000|00-CA-PGE1, would indicate PGE as the LDCShortName.
    Example 2- if the value is A/R [Power:CPE], then LDCShortName is CPE. 
    The associated GLAccountCodeOffset value, 150000|00-ER-CPE1, would indicate CPE as the LDCShortName.

    Run the select query below to get the LDCID for the identified LDCShortName.
    select LDCID 
    from LDC 
    where LDCShortName='PGE'
  5. Next, map the added GL Account Codes to the adjustment items.
    The client should have specified if the GL Account Codes need to be mapped to Adjustment. If not, confirm with the client before mapping.
    Replace the values as follows:
    GLAccountCodeID - value fetched in step 2
    Ldcid - value fetched in step 4
    DivisionID - value is 1 or 2 for a GLAccountCode and is determined as follows:
    if the corresponding GLAccountCodeOffsetDescription has the term "power" embedded in it,
    the value for DivisionID is 1
    if the corresponding GLAccountCodeOffsetDescription has the term "gas" embedded in it,
     the value for DivisionID is 2
    ArAdjTypeID - value fetched in step 3
    insert into GLAccountCodeARAdjustmentType
    (GLAccountCodeID, ldcid, DivisionID, ArAdjTypeID)
    values (1234, 11, 1, 14)

</supportagent>