Page tree
Skip to end of metadata
Go to start of metadata

Question

Can I use a SQL script to restamp expenses with a different ODC Cost Element?

Solution

The Unanet Support team (support@unanet.com) can provide/run a script to stamp historical expenses with a new mapping (generally called an "ODC Restamp" script). If an Expense Type has been mapped to an ODC Cost Element for a period of time such that expenses have already been stamped with that combination and you now want to change that mapping, a different type of script can be written to change the mapping (generally called an "Expense Type Remap" script).

Before the script(s) can be run:

  1. ODC Restamp – The following prerequisites must be completed/answered for any expenses in the system that have been stamped with either an incorrect Cost Structure (from the Project) or an incorrectly-mapped Cost Structure / Expense Type / Cost Element combination.
    • Have these expenses been posted for Billing and Revenue?
      • If so, they need to be unposted before running the ODC Restamp script.
    • Have these expenses been posted for Billing and Revenue and subsequently invoiced?
      • If so, the invoice needs to be unposted and deleted, after which the related B&R posts need to be unposted, per the above.
    • For Unanet Financials:
      • Have there been Customer Payments related to the above invoices?
        • If so, the Customer Payments related to the invoice need to be unposted and deleted. After that, the invoice and B&R postings need to be unposted, per the above.
          • To do so, temporarily enable Admin > Properties > Financials > Accounts Receivable > "Allow users to unpost Customer Payments" property.
          • Once a Customer Payment is unposted, then it can be deleted.
          • Note that if there exists a voiding payment, then setting the above property will delete the voiding document and put the original Customer Payment document back into INUSE status, where it can be deleted or modified to not include the offending vendor invoice.
          • After successfully completing the above steps, go back and un-check the property, as this is not a property that should be set in production, since your audit trail will lack that information. If the site is already live in production, then it is best to make a note somewhere that this action has been taken (the deletion of the payment), again for audit trail purposes.
      • Do these expenses exist as part of Financial Documents (Vendor Invoices, Vendor Payments, Journal Entries, Customer Payments, or Deposits)?
        • If so, they must be identified, as another script is required to restamp the Financial Documents in addition to the script that restamps the expenses.
      • Have these expenses only occurred on Fixed Price projects?
        • If so, then the remap (and restamp, if needed) scripts can be run without doing the unposted work described below, as no postings associated with FP transactions are predicated on the Cost Element.
      • Have these expenses gone through Expense Report Cost Post (but not Billing and Revenue Post)?
        • If so, then it's still safe to run the OCD remap/restamp.
      • Are there any unmapped expense corrections done via journal entries that were created prior to this request, where the expense type used for the credit entry side of the journal entry still remains unmapped? This currently applies to all expense types, not just the ones in question.
        • If so, then please identify the unmapped expense type(s) and related cost structure used for the correcting journal entries, as another script is required to restamp.
      • For this script, what is the earliest date for the current unmapped expense(s) that you would like to use?

  2. Expense Type Remap – The following prerequisites must be completed/answered for any expense types in the system that need a different mapping.

    • If the script is meant to change the mapping of an expense type from one ODC cost element to another, then for that expense type: 
      • Are all corresponding expenses within the Cost Structure already stamped with the old mapping?
        • If so, then only the "remap" script is needed, but a "restamp" script is not needed, as the stamped value on the expenses will refer to the correctly mapped value after the remap script is run.
      • Might there be expenses of that Expense Type within the Cost Structure that are not stamped at all? That is, are there any expenses that are currently "unmapped"?
        • If so, then both the "remap" and the "restamp" scripts are needed.
      • If such items are known to exist for only certain projects, please provide the Project Codes and/or Project Keys so that we can limit the scope of the restamp to just those projects rather than applying to the entire system.
    • If the script is meant to change the Cost Structure on a Project, then an ODC Restamp script must be run after the script updates the project Cost Structure.

  3. If all the above prerequisites are met, you will need to provide the following information:

    CLIENT NAME:

    <Insert company name here>

    UNANET VERSION:

    <Unanet Version #>

    CLOUD or ON PREMISE:

    <Indicate whether Cloud or On Premise>

    DATABASE:

    <If On Premise, indicate whether Unanet database is Oracle or SQL Server>

    URL:

    <Provide the URL>

    SITE ACCESS:

    <Provide the Username/Password>

    SCRIPT REQUIREMENTS:

    <Describe the requirements of the script, including any needed parameters like date ranges, usernames, person keys, cost structure names, etc.>

    If the request is to update the mapping between Expense Type and ODC Cost Element, please fill out the following table:

    Cost Structure Name - Cost Structure Key *

    Expense Type Name - Expense Type Key **

    Cost Element Name (to be mapped)








    *  Find Cost Structure Key at end of URL when editing the Cost Structure:    /edit?costStructureSummaryKey=1

    ** Find Expense Type Key at end of URL when editing the Expense Type:      /edit?expenseTypeKey=8

    SCREEN SHOTS:

    <Include relevant screenshots that would show that incorrect data, either as-is, or marked up with the needed edits>

    WORKAROUNDS:

    <Describe any known workarounds, such as data imports or manual data changes>

    JUSTIFICATION:

    <Describe why this script is needed from a business and/or technical standpoint. For example, reports X and Y will be inconsistent or incorrect, or the existence of certain data is causing errors, etc.>

    IMPLEMENTATION CONSULTANT:

    <Provide the name of the Implementation Consultant, if applicable>


For internal Unanet employees and partners, please request your script using the templates on the SQL Script Requests page.


Additional Information

KC - FAQ - Restamp - Can I use a SQL script to restamp labor with a different Labor Cost Element?


  • No labels