Can I use a SQL script to restamp expenses with a different ODC Cost Element?
If historical expenses simply need to be stamped for the first time with a new mapping, then the Support team is able to provide/run a script to do that stamping, generally called an "ODC Restamp" script. The Unanet Support team (firstname.lastname@example.org) handles this request.
However, 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. This is generally called an "Expense Type Remap" script.
There are prerequisites to running these scripts, as described below.
For any items in the system that have been stamped with an incorrect Cost Structure (from the Project) or incorrectly-mapped Cost Structure | Expense Type | Cost Element combination:
- If such items have been posted for Billing and Revenue, they need to be unposted before running the ODC restamp script.
- If such items have been posted for Billing and Revenue and subsequently invoiced, then 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, if such items have had Customer Payments related to the above invoices, then 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.
- For Unanet Financials, if such items exist as part of Financial Documents (Vendor Invoices, Vendor Payments, Journal Entries, Customer Payments, or Deposits), then please identify these, as another script is required to restamp the financial documents in addition to the script that restamps the expenses.
- If the expenses in question only occur on Fixed Price Projects, 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 predicate on the Cost Element.
- 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?
- 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?
Determining whether Restamp is Needed
- 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.
For each Expense Type to be remapped, you will need to provide the following three pieces of information:
Cost Structure Name
Expense Type Name
Cost Element Name (to be mapped)
For internal Unanet employees and partners, please request your script using the templates on the SQL Script Requests page.