Billing Requirement Includes Mapping and PROJ_MAP_FL is N - FIFO
Use this section to see examples of calculations for ACRN Only with Mapping and PROJ_MAP_FL = N and ACRN/Line Item with Mapping and PROJ_MAP_FL = N using the First In First Out (FIFO) methodology.
- PLCs can be mapped to an ACRN or ACRN/Line Item. If a PLC is mapped for a specific line, an Account cannot be mapped to the same line.
- Mapping (an Account or PLC record) must exist for each line in the child table window.
- Overlapping of Accounts is not permitted.
- Over Ceiling Amounts are calculated by the Calculate Standard Bills screen. The Over Ceiling amounts are stored in new fields in the BILL_EDIT_DETL table (WAWF_CST_CEIL_AMT, WAWF_FEE_CEIL_AMT, and WAWF_TOT_CEIL_AMT).
- Retainage calculation has been removed from the Calculate ACRN Bills screen and has been added to the Calculate Standard Bills screen. The retainage amounts are stored in a new field in BILL_EDIT_DETL (WAWF_RTNGE_AMT).
Since the PROJ_MAP_FL = N, all calculations are performed at the billing level (INVC_PROJ_ID).
Since the Billing Requirement is ACRN Only with Mapping on the Manage ACRN Bills screen, an Account or PLC must be linked for every line in the PROJ_ACRN_DETL table. You cannot map an Account and a PLC to the same row (if this occurs, the PLC is used and the Account is ignored).
Account mapping allows you to link specific accounts to each ACRN in case funding is based on different types of cost elements, such as Labor or ODCs.
PLC mapping allows you to link specific project labor categories to an ACRN in case funding is based up a specific labor function, such as Engineering or Admin Support.
Example:
A contract is awarded with the following Accounting Classification data:
| Line Item | Subline Item | ACRN | Accounting Appropriation Number | Funding Amt |
|---|---|---|---|---|
| 0001 | 0001AA | AA | 5793400309475A110704040004081572806F503000 F03000 | 15,000.00 |
| 0001 | 0001AB | AA | 5793400309475A110704040004081572806F503000 F03000 | 11,000.00 |
| 0002 | 0002AA | AB | 8893400309475A110704040004081572806F336000 F07700 | 12,000.00 |
| 0002 | 0002AB | AB | 8893400309475A110704040004081572806F336000 F07700 | 14,000.00 |
| 0003 | 0003AA | AA | 5793400309475A110704040004081572806F503000 F03000 | 12,000.00 |
| 0003 | 0003AB | AB | 8893400309475A110704040004081572806F336000 F07700 | 15,000.00 |
| 0004 | 0004AA | AC | 7765658810475A110704040004081572806F336000 C15500 | 30,000.00 |
| 0004 | 0004AB | AC | 7765658810475A110704040004081572806F336000 C15500 | 50,000.00 |
| 0005 | 0005AA | AD | 4582265450475A110704040004081572806F336000 K17700 | 25,000.00 |
Project Setup Data:
Project Number: USN0418
Billing Level: USN0418
Set up the ACRN data as follows on the Manage ACRN Bills screen. Line and Subline data are not entered based upon the Billing Requirement selection of ACRN Only w/Account Mapping. Accounts and PLCs are mapped.
| Seq No | ACRN | Active | Total ACRN Value | Previous ACRN Allocation Value | Current ACRN Allocation Value | Remaining ACRN Allocation Value |
|---|---|---|---|---|---|---|
| 1 | AA | Y | 38,000.00 | 0.00 | 0.00 | 38,000.00 |
| 2 | AB | Y | 41,000.00 | 0.00 | 0.00 | 41,000.00 |
| 3 | AC | Y | 80,000.00 | 0.00 | 0.00 | 80,000.00 |
| 4 | AD | Y | 25,000.00 | 0.00 | 0.00 | 25,000.00 |
Furthermore, the ACRN Funding is based upon specific resources. The specific resources can be associated with direct accounts or PLCs. The ACRN, Resource, Account, and PLC breakdown are as follows:
| ACRN | Resource | Accounts | PLC Mapping |
|---|---|---|---|
| AA | Labor | EN, AD | |
| AB | ODCs | 05030 and 05040 | |
| AC | Maintenance Contract | 01200-010 | |
| AD | Labor | EN, AD |
Set up the Account data as follows on the Accounts subtask of the Manage ACRN Bills screen.
| ACRN | Starting Account | Ending Account |
|---|---|---|
| AB | 05020 | 05090 |
| AC | 01200-010 | 01200-010 |
Set up the PLC (Project Labor Category) data as follows on the PLC Mapping subtask of the Manage ACRN Bills screen.
| ACRN | PLC | Description |
|---|---|---|
| AA | EN | Engineer |
| AB | AD | Admin Support |
| AD | EN | Engineer |
| AD | AD | Admin Support |
A Standard Bill (calculated by the Calculate Standard Bills screen) produces the following invoice amounts for Project USN0418 (SUM(MU_INVC_AMT) in BILL_EDIT_INVC_HDR):
USN0418: 82,077.50
Calculation Logic
For the ACRN Only with Mapping methodology with PROJ_MAP_FL = N, the billing level project number is used for the calculation. There is no breakout on the detail levels, if any. When mapping occurs, BILL_EDIT_DETL is used to calculate the allocation amounts in addition to the WAWF_XXX fields in BILL_EDIT_DETL for any over ceiling and retainage amounts. BILL_EDIT_INVC_HDR is still used to determine whether a calculation occurs.
Check PROJ_BILL_ACRN to see if data exists for the project(s).
Project data exists for Project USN0418. Continue calculation.
Verify the Billing Requirement: PROJ_BILL_ACRN.ACRN_BASIS_CD = AM; ACRN Only with Mapping.
Verify the PROJ_MAP_FL value: PROJ_BILL_ACRN.PROJ_MAP_FL = N.
Check the ACTIVE_FL in PROJ_BILL_ACRN for the project. If the PROJ_BILL_ACRN.ACTIVE_FL = Y, continue calculation. If the PROJ_BILL_ACRN.ACTIVE_FL = N, end calculation for this project and check for existence of the next project, if any.
The ACTIVE_FL = Y for Project USN0418. Continue calculation.
Check BILL_EDIT_INVC_HDR to verify the Status of the invoice. If the Status is Selected, Reverse, or Void (BILL_EDIT_INVC_HDR.S_BILL_STATUS_CD IN (S, R, V)), store SUM(MU_INVC_AMT) and continue calculation. If the Status is Unselected (BILL_EDIT_INVC_HDR.S_BILL_STATUS_CD = U), end calculation.
The Status of Project USN0418 is Selected. BILL_EDIT_INVC_HDR.S_BILL_STATUS_CD = S. Continue calculation.
Summarize the billable amounts from BILL_EDIT_DETL based upon the INVC_PROJ_ID, ACCT_ID, and Billable Amount.
Billable Amount = SUM(MU_BILL_AMT + MU_SALES_TAX_AMT + MU_RTNGE_AMT + MU_OVRFEE_CL_AMT + MU_OVRTOT_CL_AMT + MU_OVRCST_CL_AMT - MU_DISC_AMT)
If the Billable Amount is 0.00 for each account (where the ACCT_ID is NOT NULL), end calculation. If the Billable Amount does not equal 0.00 for any account, continue calculation. Even if the sum of all the accounts is 0.00, continue the calculation in case costs are being moved from one account to another, thus having a possible affect on the ACRN balances
Invoice #1 produces the following billable amounts:
Table 1: Billable Amount Calculation
| ACCT_ID | Billable Amount (1) BILL_EDIT_DETL |
|---|---|
| 05000-010 | 17,500.00 |
| 05000-020 | 19,250.00 |
| 05030 | 30,000.00 |
| 05040 | 5,000.00 |
| 01200-010 | 10,750.00 |
| NULL | 422.50 |
(1) Billable Amount = SUM (MU_BILL_AMT + MU_SALES_TAX_AMT + MU_RTNGE_AMT + MU_OVRFEE_CL_AMT + MU_OVRTOT_CL_AMT + MU_OVRCST_CL_AMT - MU_DISC_AMT)
Billable amount does not equal 0.00 for all accounts; calculation continues.
Check for Retainage and Over Ceiling amounts in BILL_EDIT_DETL (S_TRN_TYPE is OT, OF, OC, R). These transactions are stored in BILL_EDIT_DETL without an ACCT_ID, so you cannot map retainage or over ceiling amounts via the Accounts subtask of the Manage ACRN Bills screen. New fields have been added to BILL_EDIT_DETL to redistribute these amounts to a transaction project, account, CLIN, or PLC level.
Sum and group the Billable Amount (formula above) by S_TRN_TYPE (OT, OF, OC, R) by INVC_PROJ_ID. If any row produces an amount not equal to 0.00, the new BILL_EDIT_DETL fields (WAWF_XXXX) are used. If no rows are returned, or if the billable amount for all rows is 0.00, the source of the allocation amounts is the billable amount from BILL_EDIT_DETL.
The result produces the following amounts by INVC_PROJ_ID, S_TRN_TYPE:
Table 2: Retainage and Over Ceiling Amounts
| S_TRAN_TYPE | Billable Amount (1) BILL_EDIT_DETL |
|---|---|
| OT | 55.00 |
| R | 367.50 |
(1) Billable Amount = SUM(MU_BILL_AMT + MU_SALES_TAX_AMT + MU_RTNGE_AMT + MU_OVRFEE_CL_AMT
+ MU_OVRTOT_CL_AMT + MU_OVRCST_CL_AMT - MU_DISC_AMT)
This produces amounts not equal to 0.00, so the new BILL_EDIT_DETL fields are used to determine the allocation amounts by account. The BILL_EDIT_DETL table displays the following amounts:
Table 3: BILL_EDIT_DETL
| INVC_PROJ_ID | TRN_PROJ_ID | ACCT_ID | ITEM | BILL_LAB_CAT_CD | WAWF_TOT_CEIL_AMT | WAWF_RTNGE_AMT |
|---|---|---|---|---|---|---|
| USN0418 | USN0418.01.01 | 05000-010 | EN | 0.00 | 175.00 | |
| USN0418 | USN0418.01.02 | 05000-020 | AD | 0.00 | 192.50 | |
| USN0418 | USN0418.02.01 | 05030 | 47.14 | 0.00 | ||
| USN0418 | USN0418.02.02 | 05040 | 7.86 | 0.00 | ||
| USN0418 | USN0418 | 01200-010 | 0.00 | 0.00 |
The Retainage and Over Ceiling amounts from the BILL_EDIT_DETL table are deducted from the original Billing Amounts from BILL_EDIT_DETL grouped by ACCT_ID/ITEM_ID/BILL_LAB_CAT_CD.
Table 4: Adjusted Billable Amount Calculation
| INVC_PROJ_ID | TRN_PROJ_ID | ACCT_ID | ITEM_ID | BILL_LAB_CAT_CD | Billable Amount
BILL_EDIT_DETL (A) |
Over Ceiling Allocation Amount
(B) |
Retainage Allocation Amount
(C) |
Adjusted Billable Amount
(A, B, C) |
|---|---|---|---|---|---|---|---|---|
| USN0418 | USN0418.01.01 | 05000-010 | EN | 17,500.00 | 0.00 | 175.00 | 17,325.00 | |
| USN0418 | USN0418.01.02 | 05000-020 | AD | 19,250.00 | 0.00 | 192.50 | 19,057.50 | |
| USN0418 | USN0418.02.01 | 05030 | 30,000.00 | 47.14 | 0.00 | 29,952.86 | ||
| USN0418 | USN0418.02.02 | 05040 | 5,000.00 | 7.86 | 0.00 | 4,992.14 | ||
| USN0418 | USN0418 | 01200-010 | 10,750.00 | 0.00 | 0.00 | 10,750.00 |
(A) SELECT ACCT_ID, SUM(MU_BILL_AMT + MU_SALES_TAX_AMT - MU_DISC_AMT) FROM BILL_EDIT_DETL WHERE INVC_PROJ_ID = USN0418 AND ACCT_ID IS NOT NULL GROUP BY ACCT_ID;
(B) Redistributed amount from Table 3.
(C) Redistributed amount from Table 3.
The billable amounts have been calculated by account, item, and project labor category combination.
Using FIFO Methodology
Requirement: The payment office makes payment in sequential ACRN order within the contract, exhausting all funds in the previous ACRN before paying from the next ACRN.
Using the FIFO method, the funds from ACRN AA are exhausted before using funds from ACRN AB. You are responsible for assigning the correct Sequence Number to the ACRN value (that is, you must link ACRN AA to sequence number 1 and ACRN AB to sequence number 2).
Determine the ACRN Linked to smallest ACRN Sequence Number value.
Sequence Number 1 exists (SEQ_NO = 1) for ACRN AA.
Verify the PROJ_ACRN_DETL.ACTIVE_FL value for the Sequence Number. If the PROJ_ACRN_DETL.ACTIVE_FL = Y, continue calculation. If PROJ_ACRN_DETL.ACTIVE_FL = N, end calculation and look for next sequence number.
The PROJ_ACRN_DETL.ACTIVE_FL = Y for SEQ_NO = 1. Continue calculation.
Go to PROJ_ACRN_PLC to determine whether any rows exist.
- Yes. Match any BILL_LAB_CAT_CD values from BILL_EDIT_DETL to PROJ_ACRN_PLC. Continue calculation.
- No. Go to PROJ_ACRN_ACCT to determine whether any rows exist.
- Yes. Match any ACCT_ID values from BILL_EDIT_DETL to PROJ_ACRN_ACCT. Reminder: Summary account may be entered into the PROJ_ACRN_ACCT table. Continue calculation.
- No. No calculation is performed for this sequence number. Check for the next sequence number.
The PROJ_ACRN_PLC table has rows for SEQ_NO = 1.
Match any BILL_LAB_CAT_CD values from Table 4 to the PROJ_ACRN_PLC table.
The EN and AD BILL_LAB_CAT_CD values are matches.
Use the Adjusted Billable Amount from Table 4, Adjusted Billable Amount Calculation, for Project USN0418 for ACRN AA:
| BILL_LAB_CAT_CD | Adjusted Billable Amount |
|---|---|
| EN | 17,325.00 |
| AD | 19,057.50 |
| Amount | |
|---|---|
| Billable Amount for ACRN AA: | 36,382.50 |
| Subtract any allocation amounts with an ACRN Sequence Number < 1 that contains the same mapping values: None exists. | - 0.00 |
| Amount Remaining to be Allocated for ACRN AA : | 36,382.50 |
Determine the available ACRN AA remaining allocation value balance.
This amount is calculated by the following formula:
PROJ_ACRN_DETL.ACRN_FUNDED_AMT - PROJ_ACRN_DETL.ACRN_BILLED_AMT
Remaining ACRN Allocation Value amount for ACRN AA: 38,000.00
Allocate the smaller of the Remaining ACRN Allocation Value or Amount Remaining to be Allocated.
The Project USN0418 Amount Remaining to be Allocated, 36,382.50, is less than the Remaining ACRN Allocation Value of 38,000.00 for ACRN AA; so Costpoint allocates 36,382.50 to ACRN AA for Project USN0418.
Check for the existence of an ACRN Sequence Number > 1. Sequence Numbers 2 and 3 exist. Use the smallest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AB is the second ACRN Sequence Number. SEQ_NO = 2.
Verify the PROJ_ACRN_DETL.ACTIVE_FL value for the Sequence Number. If the PROJ_ACRN_DETL.ACTIVE_FL = Y, continue calculation. If PROJ_ACRN_DETL.ACTIVE_FL = N, end calculation.
The PROJ_ACRN_DETL.ACTIVE_FL = Y for SEQ_NO = 2. Continue calculation.
Go to PROJ_ACRN_PLC to determine whether any rows exist.
- Yes. Match any BILL_LAB_CAT_CD values from BILL_EDIT_DETL to PROJ_ACRN_PLC. Continue calculation.
- No. Go to PROJ_ACRN_ACCT to determine whether any rows exist.
- Yes. Match any ACCT_ID values from BILL_EDIT_DETL to PROJ_ACRN_ACCT. Reminder: Summary account can be entered into the PROJ_ACRN_ACCT table. Continue calculation.
- No. No calculation is performed for this sequence number. Check for the next sequence number.
The PROJ_ACRN_PLC table does not have any rows for SEQ_NO = 2.
Check PROJ_ACRN_ACCT. PROJ_ACRN_ACCT does have a row for SEQ_NO = 2.
Match any ACCT_ID values from Table 4 to the PROJ_ACRN_ACCT table.
Accounts 05030 and 05040 match.
Use the Adjusted Billable Amount from Table 4, Adjusted Billable Amount Calculation, for Project USN0418 for ACRN AB:
| ACCT_ID | Adjusted Billable Amount |
|---|---|
| 05030 | 29,952.86 |
| 05040 | 4,992.14 |
| Amount | |
|---|---|
| Billable Amount for ACRN AB: | 34,945.00 |
| Subtract any allocation amounts with an ACRN Sequence Number < 2 that contains the same mapping values: None exists. | - 0.00 |
| Amount Remaining to be Allocated for ACRN AB: | 34,945.00 |
Determine the available ACRN AB remaining allocation value balance. This amount is calculated by using the following formula:
PROJ_ACRN_DETL.ACRN_FUNDED_AMT - PROJ_ACRN_DETL.ACRN_BILLED_AMT
Remaining ACRN Allocation Value amount for ACRN AB: 41,000.00
Allocate the smaller of the Remaining ACRN Allocation Value or Amount Remaining to be Allocated.
The Project USN0418 Amount Remaining to be Allocated, 34,945.00, is less than the Remaining ACRN Allocation Value of 41,000.00 for ACRN AB; allocate 34,945.00 to ACRN AB for Project USN0418.
Check for the existence of an ACRN Sequence Number > 2. Sequence Number 3 exists. Use the smallest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AC is the third ACRN Sequence Number. SEQ_NO = 3
Verify the PROJ_ACRN_DETL.ACTIVE_FL value for the Sequence Number. If the PROJ_ACRN_DETL.ACTIVE_FL = Y, continue calculation. If PROJ_ACRN_DETL.ACTIVE_FL = N, end calculation.
The PROJ_ACRN_DETL.ACTIVE_FL = Y for SEQ_NO = 3. Continue calculation.
Go to PROJ_ACRN_PLC to determine whether any rows exist.
- Yes. Match any BILL_LAB_CAT_CD values from BILL_EDIT_DETL to PROJ_ACRN_PLC. Continue calculation.
- No. Go to PROJ_ACRN_ACCT to determine whether any rows exist.
- Yes, match any ACCT_ID values from BILL_EDIT_DETL to PROJ_ACRN_ACCT. Reminder: Summary account may be entered into the PROJ_ACRN_ACCT table. Continue calculation.
- No. No calculation is performed for this sequence number. Check for the next sequence number.
The PROJ_ACRN_PLC table does not have any rows for SEQ_NO = 3.
Check PROJ_ACRN_ACCT. PROJ_ACRN_ACCT does have a row for SEQ_NO = 3.
Match any ACCT_ID values from Table 4 to the PROJ_ACRN_ACCT table.
Account 01200-010 matches.
Use the Adjusted Billable Amount from Table 4, Adjusted Billable Amount Calculation, for Project USN0418 for ACRN AC:
| ACCT_ID | Adjusted Billable Amount |
|---|---|
| 01200-010 | 10,750.00 |
| Amount | |
|---|---|
| Billable Amount for ACRN AC: | 10,750.00 |
| Subtract any allocation amounts with an ACRN Sequence Number < 3 that contains the same mapping values: None exists. | - 0.00 |
| Amount Remaining to be Allocated for ACRN AC: | 10,750.00 |
Determine the available ACRN AC remaining allocation value balance.
This amount is calculated by using the following formula:
PROJ_ACRN_DETL.ACRN_FUNDED_AMT - PROJ_ACRN_DETL.ACRN_BILLED_AMT
Remaining ACRN Allocation Value amount for ACRN AC: 80,000.00
Allocate the smaller of the Remaining ACRN Allocation Value or Amount Remaining to be Allocated.
The Project USN0418 Amount Remaining to be Allocated, 10,750.00, is less than the Remaining ACRN Allocation Value of 80,000.00 for ACRN AC; so Costpoint allocates 10,750.00 to ACRN AC for Project USN0418.
Check for the existence of an ACRN Sequence Number > 3. Sequence Number 4 exists.
Verify the PROJ_ACRN_DETL.ACTIVE_FL value for the Sequence Number. If the PROJ_ACRN_DETL.ACTIVE_FL = Y, continue calculation. If PROJ_ACRN_DETL.ACTIVE_FL = N, end calculation and look for next sequence number.
The PROJ_ACRN_DETL.ACTIVE_FL = Y for SEQ_NO = 4. Continue calculation.
Go to PROJ_ACRN_PLC to determine if any rows exist.
- Yes. Match any BILL_LAB_CAT_CD values from BILL_EDIT_DETL to PROJ_ACRN_PLC. Continue calculation.
- No. Go to PROJ_ACRN_ACCT to determine if any rows exist.
- Yes. Match any ACCT_ID values from BILL_EDIT_DETL to PROJ_ACRN_ACCT. Reminder: Summary account may be entered into the PROJ_ACRN_ACCT table. Continue calculation.
- No. No calculation is performed for this sequence number. Check for the next sequence number.
The PROJ_ACRN_PLC table has rows for SEQ_NO = 4.
Match any BILL_LAB_CAT_CD values from Table 4 to the PROJ_ACRN_PLC table.
The EN and AD BILL_LAB_CAT_CD values are matches.
| BILL_LAB_CAT_CD | Allocation Amount |
|---|---|
| EN | 17,325.00 |
| AD | 19,057.50 |
| Amount | |
|---|---|
| Billable Amount for ACRN AD: | 36,382.50 |
| Subtract any allocation amounts with an ACRN Sequence Number < 1 that contains the same mapping values: ACRN AA exists. | - 0.00 |
| Amount Remaining to be Allocated for ACRN AD: | 36,382.50 |
Determine the available ACRN AA remaining allocation value balance.
This amount is calculated by using the following formula:
PROJ_ACRN_DETL.ACRN_FUNDED_AMT - PROJ_ACRN_DETL.ACRN_BILLED_AMT
Remaining ACRN Allocation Value amount for ACRN AD: 25,000.00
Allocate the smaller of the Remaining ACRN Allocation Value or Amount Remaining to be Allocated.
The Project USN0418 Amount Remaining to be Allocated, 0.00, is less than the Remaining ACRN Allocation Value of 38,000.00 for ACRN AA; allocate 0.00 to ACRN AD for Project USN0418.
Check for the existence of an ACRN Sequence Number > 4. None exists. Calculation ends.
Summary of ACRN Allocation Amounts:
| ACRN | Allocation Amount |
|---|---|
| AA | 36,382.50 |
| AB | 34,945.00 |
| AC | 10,750.00 |
| AD | 0.00 |
| Total Allocation: | 82,077.50 |
Upon completion of the calculation, the following amounts are updated in the PROJ_BILL_ACRN and PROJ_ACRN_DETL tables:
| Table | Field | PROJ_BILL_ACRN Amount | PROJ_ACRN_DETL ACRN | PROJ_ACRN_DETL Amount |
|---|---|---|---|---|
| PROJ_BILL_ACRN | ACRN_INVC_AMT | 82,077.50 | ||
| PROJ_BILL_ACRN | ACRN_ALLOC_AMT | 82,077.50 | ||
| PROJ_ACRN_DETL | ACRN_CUR_ALLOC_AMT | AA | 36,382.50 | |
| AB | 34,945.00 | |||
| AC | 10,750.00 | |||
| AD | 0.00 | |||
| PROJ_ACRN_DETL | ACRN_TOT_ALLOC_AMT | AA | 36,382.50 | |
| AB | 34,945.00 | |||
| AC | 10,750.00 | |||
| AD | 0.00 | |||
| PROJ_ACRN_DETL | AVAIL_ACRN_AMT | AA | 1,617.50 | |
| AB | 6,055.00 | |||
| AC | 69,250.00 | |||
| AD | 0.00 |