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

Three exports are provided: Fixed Price Schedules, Accounts Receivable, and Journal Entries. Fixed Price Schedules can also be imported.

Fixed Price Schedule Export

The Fixed Price Schedule export screen with options and available fields are shown below.



Accounts Receivable Export

The Accounts Receivable export screen exposes a very large number of data fields for export to downstream systems, ranging from invoice level data, to all the detailed transactions and associated posted journal values that comprise an invoice. This export will typically be used to feed invoiced data from Unanet to a down-stream system for Accounts Receivable. Some accounting systems, such as QuickBooks require detailed invoices to be recreated in order to drive their revenue reporting. The Accounts Receivable Export allows all the detailed transactions and fields to be exported, which can then be imported into QuickBooks to generate an invoice.

 

Journal Entry Export

The Journal Entry export screen with options and a sample format is shown below.

Note: That the export should normally be run with the Journal Entries Generated By option set to Billing & Revenue Post as the journal entries created by Invoice Completion will be reflected in the Account Receivable Export



 

Detailed examples of exports are discussed in the following sections.

Revenue and Billing Export Overview

If Unanet is used as the primary system to invoice and calculate revenues for projects the user will normally need to export the revenue and invoice information to their external accounts receivable and general ledger systems.  The appropriate export(s) to use to transfer this information is determined based on the following logic:

 

  1. Billings will equal revenues only in companies with very simple business practices or where the exceptions to this rule are immaterial or the company is willing to make manual adjustments for any exceptions. Common situations that will cause this to not be true include:
    1. Time and/or expenses are not always billed in the same fiscal period in which the work is performed or the cost incurred.
    2. Fixed Price project revenues are earned over several months based on percent complete or revenue schedules but are billed as a lump sum at the beginning or end of the project.
    3. Pre-billed labor is invoiced on the project in a period other than that in which the work is performed.
    4. Cost Plus Revenue and Billable Value calculations use different indirect rate types.

Attribute Values

The following attributes and the associated list of possible values are useful in some cases to differentiate various transactions within the exports. Note: Items tagged as (NEW in 10.0) may require changes to existing exports if the customer elects to use the new features. 

Field

Possible Values

Comments

JOURNAL_CATEGORY

1 - Billed (Accounts Receivable)

2 - Deferred Revenue

3 - Recognized Revenue

4 - Tax

5 - Unbilled (Receivables)

9 - Revenue Write-Off (NEW in 10.0)

10 - Funding Cap Adjustment - Revenue (NEW in 10.0)

25 - Fixed Fee Adjustment - Revenue (NEW in 10.0)

29 - Invalid Legal Entity Error - Billing Post (NEW in 10.0)

30 - Invalid Legal Entity Error - Invoice Complete (NEW in 10.0)

Account category posted by Unanet

NOTE: The Invalid Legal Entity Error account categories are not actually stored in the journal records, but are only used to provide an account to use if the legal entity associated with a transaction is not the same as the legal entity of the Billing & Revenue Post or of the Invoice (this might possibly happen when trying to post legacy data). In those cases, the transaction retains the original account category of the posting and uses the account specified for the Invalid Legal Entity Error category. These categories are mentioned here for information only and do not need to be included in any export logic.

JOURNAL_TYPE

L - Labor

E - Expense

F - Fixed Price

C - Cost Plus

O - One time item

P - Prebilled labor

D - Financial Document Expense (NEW in 10.0)

FC - Funding Cap Adjustment (NEW in 10.0)

FE - Fee Cap Adjustment (NEW in 10.0)

Source of the transaction in Unanet

JOURNAL_DETL_ACCOUNT_TYPE

A – Asset

L – Liability

I – Income

E - Expense (NEW in 10.0)

R - Revenue (NEW in 10.0)

Consult with Unanet Support with the removal of the 'I' - Income value in the unlikely event that this value was used in export template logic.

Fixed Price Items Where Revenue is Recognized at Billing

When Unanet posts invoices on invoice completion containing fixed price items that were set up to recognize revenue when billed, two extra transactions are created for each fixed price item billed to reverse out the Unbilled Receivable and Deferred Revenue balances related to the original creation of the billable item through the billing post. The entries made are as follows (assuming a $100 fixed price item):

Account

Debit

Credit

Billing Post Entries:

Unbilled Receivables

100.00


Deferred Revenues


100.00

Invoice Complete Entries:

Accounts Receivable

100.00


Unbilled Receivables
100.00

Recognized Revenues


100.00

Deferred Revenues

100.00



These entries are correct on an accrual basis and when used in conjunction with the Journals Export.

If only using the AR Export, the extra entries should be ignored. This can be done by ignoring all FP items with a JOURNAL_CATEGORY of 2 or 3.

The following can be used to restrict these records out of the export:


IF( JOURNAL_TYPE =  "F")
     {
          CASE (JOURNAL_CATEGORY,
           "2",  !IGNORERECORD!,
           "3",  !IGNORERECORD!,
          !DEFAULT!, 0)
     }
ELSE
     {
     "0"
     }

Debits and Credits

This section provides a brief guide to how credits and debits are handled in Unanet for various JOURNAL_CATEGORIES.

Amounts are stored in the journal tables based on whether the balance of the account category is being increased or decreased. This can be translated into debit and credit entries for export purposes based on the "normal" debit or credit orientation of the JOURNAL_CATEGORY. The debit/credit orientation of the JOURNAL_CATEGORIES are as follows:

  • Debit-oriented categories
    • 1 - Billed (Accounts Receivable)
    • 5 - Unbilled (Receivables)
    • 9 - Revenue Write-Off
  • Credit-oriented categories
    • 2 - Deferred Revenue
    • 3 - Recognized Revenue
    • 4 - Tax
    • 10 - Funding Cap Adjustment - Revenue
    • 25 - Fixed Fee Adjustment - Revenue

When a charge is posted to a journal, two journal transactions are generated in order to “hit” both sides, the debit and the credit.

For example: Let’s say we wanted to bill a $200 labor charge with a $10 tax, and $5 discount for a net invoice amount of $205, where discount is expected to be billed and recognized as negative recognized revenue. Following are the entries generated in the Unanet General Journal (the Amount is the value actually stored in the Journals tables):

Date

Account

Debit

Credit

[Amount]

1/1/2010

(Post dt)

Post(billable value) - labor charge




   Unbilled (debit)

$200


200

   Deferred Revenue (credit)


$200

200






1/1/2010

(Post dt)

Post(revenue) - labor charge




   Deferred Revenue (credit)

$200


-200

   Recognized Revenue (credit)


$200

200






1/2/2010

(Invoice dt)

Invoice - labor charge




   Unbilled (debit)


$200

-200

   Billed (debit)

$200


200






1/2/2010

(Invoice dt)

Invoice - tax




   Billed (debit)

$10


10

   Tax (credit)


$10

10






1/2/2010

(Invoice dt)

Invoice - discount




   Billed (debit)


$5

-5

   Discount (credit)

$5


-5

Journals and AR Export Combination Used

Using a combination of the Journals and AR Exports to recognize Revenue and AR is the only way to reliably sync balances between Unanet and an external accounting system. This approach is a Unanet best practice.

A summary of the postings included in the two exports is summarized in the following table. This matrix assumes the amounts posted are positive. If negative, the Debit/Credit values would be switched (see Debits and Credits above for further information).

Transaction Type

Billed

Deferred Revenue

Revenue

Tax

Unbilled

 Revenue Write-Off  Funding Cap Adjustment   Fixed Fee Adjustment  

Journals Export (transactions created by Billing Post)

TM Revenue









  • Labor


Debit

Credit






  • Expense


Debit

Credit






TM Billable Values









  • Labor


Credit



Debit




  • Expense


Credit



Debit




  • Prebilled Labor


Credit



Debit




  • Prebilled Labor Offset


Debit



Credit




FP Revenue









  • % Complete


Debit

Credit






  • Revenue Schedule


Debit

Credit






FP Billable Value









  • Fixed Price


Credit



Debit




CP Revenue









  • Cost Plus


Debit

Credit






  • Fixed Fee Cap Adjustment

Credit




 Debit

CP Billable Value (1)









  • Cost Plus


Credit



Debit




  • Fixed Fee Cap Adjustment

Debit

Credit 


 Funding Cap Adjustment







  • Revenue

Credit



 Debit
  • Billable Value

Debit

Credit 


AR Export (transactions created by Invoice Complete)

TM Invoicing









  • All Other

Debit




Credit




  • One-time

Debit (2)

Credit (2)

Credit (2)

Credit (2)

Credit (2)




  • Write-Off
Credit



 Debit (4)

FP Invoicing









  • All Other

Debit




Credit




  • When Billed

Debit (3)

Debit (3)

Credit (3)


Credit (3)




  • One-time

Debit (2)

Credit (2)

Credit (2)

Credit (2)

Credit (2)




  • Write-Off
Credit



 Debit (4)

CP Invoicing









  • All Other

Debit




Credit




  • One-time

Debit (2)

Credit (2)

Credit (2)

Credit (2)

Credit (2)




  • Write-Off
Credit



 Debit (4)

Funded Value Adjustment 







  • All Other
Credit


Debit 


  • Write-Off 




Credit  Debit (4)

(1) CP Revenue and Billable Values may differ if a different rate type (e.g., Actual, Provisional or Target) is picked for the Cost Plus Bill Rate and the Cost Plus Recognition Rate in Admin > Setup > Accounting > Posting Options and the rates entered for the rate type differ.

(2) One-time charges can be set up to post to different account categories in Admin > Setup > Accounting > Additional Item Types. Note that the Debit Account should always be set to “Billed” since the total invoice amount, which includes the one-time items, should always post to the “Billed” (Accounts Receivable) account. The Credit Account will typically be set to post to either “Recognized Revenue” or “Tax” but could technically be set to post to “Deferred Revenue” or “Unbilled” for special situations where you might want to change the billing but not reflect it in revenue for some reason (e.g., retainage or something similar).

(3) Fixed price items where revenue is recognized when billed create an extra set of entries on invoice completion to reverse out the deferred revenue and unbilled receivables balances created by the billing post. See Fixed Price Items Where Revenue is Recognized at Billing above for further details.

(4) Note that the write-off amounts are sent over as separate transactions in the exports. In the event that the customer cannot show the write-offs as separate lines in the invoice in the external system, the exports would need to do the following:

  • Combine the "normal" entry (e.g., typically, relief of unbilled) with the write-off entry and post the net value to the "normal" entry account in the accounts receivable export.
  • Create a special journal export that can be used to export only the write-off amounts posted via invoice complete.

This is necessary to relieve the remaining unbilled values and to recognize the write-offs effect on revenues, which would normally be reflected in the AR Export.

Journal Export

The Journal Export is used to export the transactions generated by the Billing > Post process.  It is critical that this be run with the JOURNAL ENTRIES GENERATED BY value set to Billing & Revenue Post on the Admin Export > Journal Entry Criteria page to avoid double exporting the transactions generated by the Invoice Complete process which are to be exported via the AR Export process.

Note: This section does not specifically address the transactions created via the Labor Cost Post and Expense Report Cost Post processes.

An example of how the Journals Export would be set up is as follows:

Name

Value

Comment

Header Record – A Header record, which typically contains batch/document level information such as posting date or control totals, may be required depending on the particular integration.

Detail Record

GL Account

IF (JOURNAL_CATEGORY != "3")

     {

         JOURNAL_ACCOUNT_CODE

     }

ELSE

     {

          SWITCH (JOURNAL_TYPE)

          {

               CASE("E", EXP_LINE_ITEM_EXP_TYPE_ACCT_NO)

               CASE("F", TASK_USER01)

               DEFAULT(ACCOUNT_CODE)

          }

     }

This example sets the account used for expense revenue based on the account specified for the expense type and fixed price revenue to a value stored in the Task User01 UDF.

All other revenue and all non-revenue transactions use the account passed in from Unanet.

Logic for determining the accounts must be created on a case-by-case basis.

Note: If the customer is using the Posting Group functionality in Unanet, this field could typically be set to use only the JOURNAL_ACCOUNT_CODE.

Department

PROJ_OWNING_ORG

Assumes that the revenue is always posted to the Project Owning Org - this logic may change based on customer requirements

Note: If the customer is using the Posting Group functionality available in Unanet, this field could typically be set to use only the JOURNAL_ORGANIZATION_CODE.

Project

PROJ_ CODE


Task

TASK_NAME


Trans Date

JOURNAL_TRANSACTION_DATE


Debit Amount

IF ( JOURNAL_TRANSACTION_AMOUNT > 0)

     {

       CASE (JOURNAL_CATEGORY,

       "1", JOURNAL_TRANSACTION_AMOUNT,

       "5", JOURNAL_TRANSACTION_AMOUNT,

       "9", JOURNAL_TRANSACTION_AMOUNT,

        !DEFAULT!, 0)

     }

ELSE

     {

       CASE (JOURNAL_CATEGORY,

       "2", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "3", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "4", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "10", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "25", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),           !DEFAULT!, 0)

     }


Note: Existing customers will need to upgrade exports if using the NEW_JOURNAL_CATEGORY values.


Credit Amount

IF ( JOURNAL_TRANSACTION_AMOUNT < 0)

     {

       CASE (JOURNAL_CATEGORY,

       "1", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "5", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "9", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

        !DEFAULT!, 0)

     }

ELSE

     {

       CASE (JOURNAL_CATEGORY,

       "2", JOURNAL_TRANSACTION_AMOUNT,

       "3", JOURNAL_TRANSACTION_AMOUNT,

       "4", JOURNAL_TRANSACTION_AMOUNT,

       "10", JOURNAL_TRANSACTION_AMOUNT,

       "25", JOURNAL_TRANSACTION_AMOUNT,        !DEFAULT!, 0)

     }

Note: Existing customers will need to upgrade exports if using the new JOURNAL_CATEGORY values.

Description

SWITCH (JOURNAL_CATEGORY) {

    CASE("2", CONCAT("Deferred Revenue - Project " + PROJ_CODE))

    CASE("3", CONCAT("Revenue - Project " + PROJ_CODE))

    CASE("4", CONCAT("Tax - Project " + PROJ_CODE))

    CASE("5", CONCAT("Unbilled - Project " + PROJ_CODE))

    DEFAULT("ERROR")

}



Accounts Receivable Export

The Accounts Receivable Export is used to export the transactions generated by the Billing > Invoice Completion process.

One of the situations that the Accounts Receivable Export must address is the same addressed above for Fixed Price Items Where Revenue is Recognized at Billing.

When Unanet posts invoices on invoice completion containing fixed price items that were set up to recognize revenue when billed, two extra transactions are created for each fixed price item billed to reverse out the Unbilled Receivable and Deferred Revenue balances related to the original creation of the billable item through the billing post. The entries made are as follows (assuming a $100 fixed price item):

Account

Debit

Credit

Billing Post Entries:

Unbilled Receivables

100.00


Deferred Revenues


100.00

Invoice Complete Entries:

Accounts Receivable

100.00


Recognized Revenues


100.00

Deferred Revenues

100.00


Unbilled Receivables


100.00


These entries are correct on an accrual basis and when used in conjunction with the Journals Export.

If only using the AR Export, the extra entries should be ignored. This can be done by ignoring all FP items with a JOURNAL_CATEGORY of 2 or 3.

The following can be used to restrict these records out of the export:


IF( JOURNAL_TYPE =  "F")
     {
          CASE (JOURNAL_CATEGORY,
           "2",  !IGNORERECORD!,
           "3",  !IGNORERECORD!,
          !DEFAULT!, 0)
     }
ELSE
     {
     "0"
     }


Except as noted in the following:

Name

Value

Comment

Group Header Record (Invoice)

Customer ID

PROJECT_ORG_CODE

This value is saved with the invoice in UNANET and is Project Org Code for the project when the invoice is saved. If the Project Org is changed in the Project after the invoice is created, the value stored in the invoice is not updated since the invoice was not sent to that organization.

This assumes the external system customer ID equals the Unanet project organization. This might be stored elsewhere, such as a Project UDF.

Doc Type

IF (INVOICE_AMOUNT < 0) {

    "Credit Memo"

}

ELSE {

    "Invoice"

}

Financial systems often differentiate between positive (Invoice) and negative (Credit Memo) invoices by a Doc Type attribute. Note that it is possible that they may also store the Credit Memo invoice amount as a positive amount which may necessitate reversing the sign of the Invoice Amounts for Credit Memos, depending on the external system.

Invoice #

INVOICE_NUMBER


Invoice Date

INVOICE_DATE


Invoice Amount

INVOICE_AMOUNT

Note that it is possible that Credit Memos (negative invoices) may be stored with the invoice amount set as a positive amount which may necessitate reversing the sign of the Invoice Amount for Credit Memos, depending on the external system.

Terms

INVOICE_PAYMENT_TYPE

Terms Code

Due Date

INVOICE_DUE_DATE


AR GL Account

INVOICE_ACCOUNT_CODE

“Billed” Category Account posted in Unanet - this is stored in the invoice record in Unanet.

AR GL Department

INVOICE_ORGANIZATION_CODE


Invoice Line Items

GL Account

IF (JOURNAL_TYPE != "O") {

     SWITCH(JOURNAL_CATEGORY)  {

         CASE("2", JOURNAL_DETL_ACCOUNT_CODE)

         CASE("3", DETL_PROJ_USER01)

         CASE("4", JOURNAL_DETL_ACCOUNT_CODE)

         CASE("5", JOURNAL_DETL_ACCOUNT_CODE)

         DEFAULT("ERROR")

    }

}

ELSE {

    SWITCH (ONETIME_CHARGE_TYPE) {

        CASE("Courtesy Discount", "4300")

        CASE("Placement Fee", "4420")

        CASE("Software Sales", "4140")

        DEFAULT("ERROR")

    }

}

This example bases the account used for the Revenue (3) journal category for all transactions other than one-time charges on an account stored in the Project User01 UDF and bases the account for one-time items on hard-coded logic based on the one-time charge type.

Other export accounts categories (Deferred Revenue (2), Tax (4) and Unbilled (5)) are based on account values passed in from Unanet.

Logic for determining the accounts must be created on a case-by-case basis.

Note: If the user is using Posting Groups, this value could be set to always use the JOURNAL_DETL_ACCOUNT_CODE for all records

GL Department

 IF (JOURNAL_TYPE != "O") {

    SWITCH(JOURNAL_CATEGORY)  {

       CASE("2", “0000”)

       CASE("3", DETL_PROJ_OWNING_ORG)

       CASE("4", “0000”)

       CASE("5", “0000”)

       DEFAULT("ERROR")

    }

}

ELSE {

DETL_PROJ_OWNING_ORG

    }

Assumes that postings to balance sheet accounts are posted to a fixed organization and revenue is posted to the Project Owning Org.

Note: If the user is using Posting Groups, this value could be set to always use the JOURNAL_DETL_ORGANIZATION_CODE for all records

Line item amount

SUM(SWITCH (JOURNAL_CATEGORY) {

    CASE("5", MATH(JOURNAL_TRANSACTION_AMOUNT * -1))

    CASE("9", MATH(JOURNAL_TRANSACTION_AMOUNT * -1))

    CASE("2", JOURNAL_TRANSACTION_AMOUNT)

    CASE("3", JOURNAL_TRANSACTION_AMOUNT)

    CASE("4", JOURNAL_TRANSACTION_AMOUNT)DEFAULT("no journal category")

}

)

The line item amount can be used in integrations that allow the entry of the actual billed amount for an invoice line (e.g., not QuickBooks).

The sum of the line item amounts should always add up to the invoice amount.

Note that this logic might need to be reversed for Credit Memos (negative invoices) if the receiving system stores the credit memo amounts as positive numbers.

NOTE THAT EXISTING CUSTOMERS WILL NEED TO UPGRADE EXPORTS IF USING THE NEW JOURNAL_CATEGORY VALUES.

Note: The funding cap adjustment and fixed fee adjustment categories are not present in the AR Export, so don't need to be considered. 

Note: The following example (from the 9.6 Billing Guide) is incorrect as the sign is not based on the account type, but on the Journal Type.

SUM(SWITCH (JOURNAL_DETL_ACCOUNT_TYPE) {

    CASE("A", MATH(JOURNAL_TRANSACTION_AMOUNT * -1))

    CASE("L", JOURNAL_TRANSACTION_AMOUNT)

    CASE("I", JOURNAL_TRANSACTION_AMOUNT)

DEFAULT("no account type")

}

)

Quantity

SUM(SWITCH (JOURNAL_TYPE) {

    CASE("L", TSCELL_QUANTITY)

    CASE("E", "1")

    CASE("F", "1")

    CASE("O", "1")

    CASE("C", "1")

    CASE("P", "1")

    CASE("D", "1")

    CASE("FC", "1")

    CASE("FE", "1")DEFAULT("no journal type")

}

)

This is required for QuickBooks integrations as the Line Item Amount cannot be entered directly as it is always computed as Quantity * Price.

This will provide a count of the duplicate non-labor items contained on the invoice, which will appear as a single line in the export.

Price

SWITCH (JOURNAL_TYPE) {

    CASE("L", TSCELL_RATE_BILL)

    CASE("E", JOURNAL_TRANSACTION_AMOUNT)

    CASE("F", FIXED_PRICE_AMOUNT)

    CASE("O", JOURNAL_TRANSACTION_AMOUNT)

    CASE("C", JOURNAL_TRANSACTION_AMOUNT)

    CASE("P", JOURNAL_TRANSACTION_AMOUNT)

    CASE("D", JOURNAL_TRANSACTION_AMOUNT)

    CASE("FC", JOURNAL_TRANSACTION_AMOUNT)

    CASE("FE", JOURNAL_TRANSACTION_AMOUNT)

    DEFAULT("no journal type")

}

For labor, use the transaction bill rate, use the billable amount for everything else for QuickBooks exports as they require a price (line items are always calculated as quantity * price, you cannot enter the billable amount directly.

Item

CONCAT(

    IF (PROJ_USER03 != "") {

    PROJ_USER03

    }

    ELSE {

        IF (PROJ_ORG_USER01 != "") {

            PROJ_ORG_USER01

        }

        ELSE {

        "No Item in org udf or project udf!"

        }

    }

,

SWITCH (JOURNAL_TYPE) {

    CASE("L","-Consulting Revenue")

    CASE("E","-Recoverable Project Expenses")

    CASE("F",CONCAT("-",DESCRIPTION))

    CASE("O", CONCAT("-Retentions - ",SUBSTR(ONETIME_CHARGE_TYPE,1,3)))

    CASE("C", CONCAT("-",DESCRIPTION))

    CASE("P", CONCAT("-",DESCRIPTION))

}

)

Item is required for QuickBooks integrations.

This example logic retrieves part of the item value from Project or Org user fields and concatenates it with a description based on Journal Type.

Actual logic for determining this value will vary from integration to integration.

Debit Amount

IF ( JOURNAL_TRANSACTION_AMOUNT > 0)

     {

       CASE (JOURNAL_CATEGORY,

       "1", JOURNAL_TRANSACTION_AMOUNT,

       "5", JOURNAL_TRANSACTION_AMOUNT,

       "9", JOURNAL_TRANSACTION_AMOUNT,

        !DEFAULT!, 0)

     }

ELSE

     {

       CASE (JOURNAL_CATEGORY,

       "2", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "3", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "4", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       !DEFAULT!, 0)

     }

NOTE THAT EXISTING CUSTOMERS WILL NEED TO UPGRADE EXPORTS IF USING THE NEW JOURNAL_CATEGORY VALUES.

Note: The funding cap adjustment and fixed fee adjustment categories are not present in the AR Export, so don't need to be considered. 

Note: The example (from the 9.6 Billing Guide) is incorrect as the sign is not based on the account type, but on the Journal Type.

Credit Amount

IF ( JOURNAL_TRANSACTION_AMOUNT < 0)

     {

       CASE (JOURNAL_CATEGORY,

       "1", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "5", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "9", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

        !DEFAULT!, 0)

     }

ELSE

     {

       CASE (JOURNAL_CATEGORY,

       "2", JOURNAL_TRANSACTION_AMOUNT,

       "3", JOURNAL_TRANSACTION_AMOUNT,

       "4", JOURNAL_TRANSACTION_AMOUNT,

       !DEFAULT!, 0)

     }

Note: Existing customers will need to upgrade exports if using the JOURNAL_CATEGORY values.

Note: The funding cap adjustment and fixed fee adjustment categories are not present in the AR Export, so don't need to be considered. 

Note: The example (from the 9.6 Billing Guide) is incorrect as the sign is not based on the account type, but on the Journal Type.

AR Export Used to Recognize AR and Revenue

The AR Export is always used to set up Invoices created in Unanet in the external AR system. It can also be used to recognize revenue by creating export transactions that post the detail invoice lines to Revenue accounts based on logic consistent with the way the user wants to track revenue in their external financial system. Note: The revenue in the external financial system is unlikely to match the revenue in Unanet as Unanet revenues are always recognized on an accrual basis, not based on billings. The only way the two will match is if the user consistently bills all transactions every month and does not have any other situations where revenue and billings are out of sync as discussed above. For this reason, using the AR export to recognize AR and Revenue is not a Unanet best practice.

An example of how the AR Export would be set up to recognize Revenue is as follows (Note: Not all of the fields specified below would likely ever be included in a single export - they are included for illustrative purposes). Note: The values are the same as those used for the regular export except that the Org/Account code specified must normally be overridden from the values determined by Unanet and the extra revenue and deferred revenue entries associated with FP items where revenue is recognized on billing are always ignored:

Name

Value

Comment

Group Header Record (Invoice)

Customer ID

PROJ_ORG_CODE

Assuming the external system customer ID equals the Unanet project organization. This might be stored elsewhere, such as a Project UDF.

Doc Type

IF (INVOICE_AMOUNT < 0) {

    "Credit Memo"

}

ELSE {

    "Invoice"

}

Financial systems often differentiate between positive (Invoice) and negative (Credit Memo) invoices by a Doc Type attribute. Note that it is possible that they may also store the Credit Memo invoice amount as a positive amount which may necessitate reversing the sign of the Invoice Amounts for Credit Memos, depending on the external system.

Invoice #

INVOICE_NUMBER


Invoice Date

INVOICE_DATE


Invoice Amount

INVOICE_AMOUNT

Note that it is possible that Credit Memos (negative invoices) may be stored with the invoice amount set as a positive amount which may necessitate reversing the sign of the Invoice Amount for Credit Memos, depending on the external system.

Terms

INVOICE_PAYMENT_TYPE

Terms Code

Due Date

INVOICE_DUE_DATE


AR GL Account

INVOICE_ACCOUNT_CODE

“Billed” Category Account posted in Unanet.  This is stored in the invoice record in Unanet.

AR GL Department

INVOICE_ORGANIZATION_CODE


Invoice Line Items

GL Account

IF (JOURNAL_TYPE != "O") {

     SWITCH(JOURNAL_CATEGORY)  {

         CASE("2", !IGNORERECORD!)

         CASE("3", !IGNORERECORD!)

         CASE("5", DETL_PROJ_USER01)

         DEFAULT("ERROR")

    }

}

ELSE {

    SWITCH (ONETIME_CHARGE_TYPE) {

        CASE("Courtesy Discount", "4300")

        CASE("Placement Fee", "4420")

        CASE("Software Sales", "4140")

        DEFAULT("ERROR")

    }

}

This example bases the revenue account for all transactions other than one-time charges on a revenue account stored in the Project User01 UDF and posts the revenue for one-time items based on hard-coded logic based on the one-time charge type.

The extra revenue and deferred revenue entries associated with FP items where revenue is recognized on billing are ignored since the original entries are not exported via the Journal Export.

Logic for determining the accounts must be created on a case-by-case basis.

Note: This type of logic would be necessary even if the user is using Posting Groups as the accounts derived by Unanet will normally not be revenue accounts, so the JOURNAL_DETL_ACCOUNT_CODE can not be used.

GL Department

DETL_PROJ_OWNING_ORG

Assumes that the revenue is always posted to the Project Owning Org - this logic may change based on customer requirements.

Note: This type of logic would be necessary even if the user is using Posting Groups as the accounts derived by Unanet will likely not be the organization that should be credited with the revenue, so the JOURNAL_DETL_ORGANIZATION_CODE can not be used.

Line item amount

SUM(SWITCH (JOURNAL_CATEGORY) {

    CASE("5", MATH(JOURNAL_TRANSACTION_AMOUNT * -1))

    CASE("9", MATH(JOURNAL_TRANSACTION_AMOUNT * -1))

    CASE("2", JOURNAL_TRANSACTION_AMOUNT)

    CASE("3", JOURNAL_TRANSACTION_AMOUNT)

    CASE("4", JOURNAL_TRANSACTION_AMOUNT)DEFAULT("no journal category")

}

)

The line item amount can be used in integrations that allow the entry of the actual billed amount for an invoice line (e.g., not QuickBooks).

The sum of the line item amounts should always add up to the invoice amount.

This logic might need to be reversed for Credit Memos (negative invoices) if the receiving system stores the credit memo amounts as positive numbers.

Existing customers will need to upgrade exports if using the new JOURNAL_CATEGORY values.

The funding cap adjustment and fixed fee adjustment categories are not present in the AR Export, so don't need to be considered. 

The following example (from the 9.6 Billing Guide) is incorrect as the sign is not based on the account type, but on the Journal Type.

SUM(SWITCH (JOURNAL_DETL_ACCOUNT_TYPE) {

    CASE("A", MATH(JOURNAL_TRANSACTION_AMOUNT * -1))

    CASE("L", JOURNAL_TRANSACTION_AMOUNT)

    CASE("I", JOURNAL_TRANSACTION_AMOUNT)

DEFAULT("no account type")

}

)

Quantity

SUM(SWITCH (JOURNAL_TYPE) {

    CASE("L", TSCELL_QUANTITY)

    CASE("E", "1")

    CASE("F", "1")

    CASE("O", "1")

    CASE("C", "1")

    CASE("P", "1")

    CASE("D", "1")

    CASE("FC", "1")

    CASE("FE", "1")

    DEFAULT("no journal type")

}

)

This is required for QuickBooks integrations as the Line Item Amount cannot be entered directly as it is always computed as Quantity * Price.

This will provide a count of the duplicate non-labor items contained on the invoice, which will appear as a single line in the export.

Price

SWITCH (JOURNAL_TYPE) {

    CASE("L", TSCELL_RATE_BILL)

    CASE("E", JOURNAL_TRANSACTION_AMOUNT)

    CASE("F", FIXED_PRICE_AMOUNT)

    CASE("O", JOURNAL_TRANSACTION_AMOUNT)

    CASE("C", JOURNAL_TRANSACTION_AMOUNT)

    CASE("P", JOURNAL_TRANSACTION_AMOUNT)

    CASE("D", JOURNAL_TRANSACTION_AMOUNT)

    CASE("FC", JOURNAL_TRANSACTION_AMOUNT)

    CASE("FE", JOURNAL_TRANSACTION_AMOUNT)

    DEFAULT("no journal type")

}

This is required for QuickBooks integrations as the Line Item Amount cannot be entered directly as it is always computed as Quantity * Price.

Item

CONCAT(

    IF (PROJ_USER03 != "") {

    PROJ_USER03

    }

    ELSE {

        IF (PROJ_ORG_USER01 != "") {

            PROJ_ORG_USER01

        }

        ELSE {

        "No Item in org udf or project udf!"

        }

    }

,

SWITCH (JOURNAL_TYPE) {

    CASE("L","-Consulting Revenue")

    CASE("E","-Recoverable Project Expenses")

    CASE("F",CONCAT("-",DESCRIPTION))

    CASE("O", CONCAT("-Retentions - ",SUBSTR(ONETIME_CHARGE_TYPE,1,3)))

    CASE("C", CONCAT("-",DESCRIPTION))

    CASE("P", CONCAT("-",DESCRIPTION))

}

)

Item is required for QuickBooks integrations.

This example logic retrieves part of the item value from Project or Org user fields and concatenates it with a description based on Journal Type.

Actual logic for determining this value will vary from integration to integration.

Debit Amount

IF ( JOURNAL_TRANSACTION_AMOUNT > 0)

     {

       CASE (JOURNAL_CATEGORY,

       "1", JOURNAL_TRANSACTION_AMOUNT,

       "5", JOURNAL_TRANSACTION_AMOUNT,

       "9", JOURNAL_TRANSACTION_AMOUNT,

        !DEFAULT!, 0)

     }

ELSE

     {

       CASE (JOURNAL_CATEGORY,

       "2", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "3", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "4", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       !DEFAULT!, 0)

     }

Some external systems require that you specify a debit/credit amount when integrating to their Accounts Receivable system.

Existing customers will need to upgrade exports if using the new JOURNAL_CATEGORY values.

The funding cap adjustment and fixed fee adjustment categories are not present in the AR Export, so don't need to be considered. 

The example (from the 9.6 Billing Guide) is incorrect as the sign is not based on the account type, but on the Journal Type.

Credit Amount

IF ( JOURNAL_TRANSACTION_AMOUNT < 0)

     {

       CASE (JOURNAL_CATEGORY,

       "1", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "5", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

       "9", MATH(JOURNAL_TRANSACTION_AMOUNT * -1),

        !DEFAULT!, 0)

     }

ELSE

     {

       CASE (JOURNAL_CATEGORY,

       "2", JOURNAL_TRANSACTION_AMOUNT,

       "3", JOURNAL_TRANSACTION_AMOUNT,

       "4", JOURNAL_TRANSACTION_AMOUNT,

       !DEFAULT!, 0)

     }

Some external systems require that you specify a debit/credit amount when integrating to their Accounts Receivable system.

Existing customers will need to upgrade exports if using the new JOURNAL_CATEGORY values.

The funding cap adjustment and fixed fee adjustment categories are not present in the AR Export, so don't need to be considered. 

The example (from the 9.6 Billing Guide) is incorrect as the sign is not based on the account type, but on the Journal Type.


 





  • No labels