Billing Requirement Includes Mapping and PROJ_MAP_FL is N - Proration
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 proration 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 defined 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) -BLPGBILL) 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, possibly affecting 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 in BILL_EDIT_DETL 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 Proration Methodology
Requirement: the payment office makes payment from each ACRN within the contract in the same proportion as the amount of funding currently unliquidated for each ACRN.
Unliquidated is defined as the Remaining ACRN Allocation Value.
The mathematical equation to determine the Proration amounts is:
Individual Remaining ACRN Allocation Value (each ACRN)
Total of ALL Remaining ACRN Allocation Values
Determine which accounts or PLCs, if any, share multiple ACRNs. For example, Project Labor Categories EN and AD are linked to ACRN AA and ACRN AD. A table is created to hold the shared Account/PLC/ACRN mappings.
Table A: Account/PLC/ACRN Mapping
| ACCT_ID | BILL_LAB_CAT_CD | Shared ACRN |
|---|---|---|
| EN
AD |
AA
AD |
|
| 05030 | AB | |
| 05040 | AB | |
| 01200-010 | AC |
Calculate the Remaining ACRN Allocation Value for each individual ACRN along with the Total Remaining ACRN Allocation Value (grouped by account). If the individual ACRN Remaining ACRN Allocation Value (PROJ_ACRN_DETL.ACRN_FUNDED_AMT – PROJ_ACRN_DETL.ACRN_BILLED_AMT) is less than or equal to 0.00, do not include that specific ACRN Allocation Value in the calculation as part of the numerator or denominator. If the PROJ_ACRN_DETL.ACTIVE_FL = N, do not include that specific ACRN Allocation Value in the calculation.
Table B: Account/PLC/ACRN Mapping with Remaining ACRN Allocation Values
| ACCT_ID | BILL_LAB_CAT_CD | Shared ACRN | Active? | Individual Remaining ACRN Allocation Value | Total Remaining ACRN Allocation Value Grouped by Account/PLC |
|---|---|---|---|---|---|
| EN
AD |
AA
AD |
Y
Y |
38,000.00
25,000.00 |
63,000.00 | |
| 05030 & 05040 | AB | Y | 41,000.00 | 41,000.00 | |
| 01200-010 | AC | Y | 80,000.00 | 80,000.00 |
Continue with ACRN Proration calculation.
The ACRN Proration is based upon the Individual Remaining ACRN Allocation Value to the Total Remaining ACRN Allocation ValueGrouped by Account/PLC for active ACRNs.
Table C: Proration Basis for Active ACRNs
| ACRN | Related ACRN | Individual Remaining ACRN Allocation Value | Total Remaining ACRN Allocation Value Grouped by Account/PLC | ACRN Proration |
|---|---|---|---|---|
| AA | AD | 38,000.00 | 63,000.00 | (38,000.00 / 63,000.00) |
| AB | 41,000.00 | 41,000.00 | (41,000.00 / 41,000.00) | |
| AC | 80,000.00 | 80,000.00 | (80,000.00 / 80,000.00) | |
| AD | AA | 25,000.00 | 63,000.00 | (25,000.00 / 63,000.00) |
Summarize the Adjusted Billable Amounts (Table 4) based upon the associated ACRNs.
Table D: Adjusted Billable Amounts based upon Account/PLC/ACRN Mappings
| ACCT_ID | BILL_LAB_CAT_CD | Associated ACRNs (1) | Adjusted Billable Amount (2) | Adjusted Billable Amount (2) | Adjusted Billable Amount (2) |
|---|---|---|---|---|---|
| EN
AD |
AA
AD |
17,325.00
19,057.50 |
|||
| 05030
05040 |
AB | 34,945.00 | |||
| 01200-010 | AC | 10,750.00 | |||
| Total Adjusted Billable Amount | 36,382.50 | 34,945.00 | 10,750.00 | ||
(1) Associated ACRNs are derived from Table A.
(2) Adjusted Billable Amounts are derived from Table 4.
Match the Total Adjusted Billable Amount by associated ACRN (Table 8) to the ACRN Proration (Table 7).
| ACCT_ID | Adjusted Billable Amount | Proration (2) | Ratio Computation | Allocation Amount |
|---|---|---|---|---|
| AA | 36,382.50 | (38,000.00 / 63,000.00) | 36,382.50 * (38,000.00 / 63,000.00) | 21,945.00 |
| AB | 34,945.00 | (41,000.00 /41,000.00) | 34,945.00 * (41,000.00 / 41,000.00) | 34,945.00 |
| AC | 10,750.00 | (80,000.00 / 80,000.00) | 10,750.00 * (80,000.00 / 80,000.00) | 10,750.00 |
| AD | 36,382.50 | (25,000.00 / 63,000.00) | 36,382.50 * (25,000.00 / 63,000.00) | 14,437.50 |
| Total ACRN Allocation Amount | 82,077.50 | |||
(1) Adjusted Billable Amount from Table D.
(2) Proration formula from Table C.
If any residual amounts result due to rounding (Total ACRN Allocation Amount not equal to Invoice Amount), apply the residual to the ACRN linked to the smallest ACRN Sequence Number with a Remaining ACRN Allocation Value > 0.00.
Summary of ACRN Allocation Amounts:
| ACRN | Allocation Amount |
|---|---|
| AA | 21,945.00 |
| AB | 34,945.00 |
| AC | 10,750.00 |
| AD | 14,437.50 |
| 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 | 21,945.00 | |
| AB | 34,945.00 | |||
| AC | 10,750.00 | |||
| AD | 14,437.50 | |||
| PROJ_ACRN_DETL | ACRN_TOT_ALLOC_AMT | AA | 21,945.00 | |
| AB | 34,945.00 | |||
| AC | 10,750.00 | |||
| AD | 14,437.50 | |||
| PROJ_ACRN_DETL | AVAIL_ACRN_AMT | AA | 16,055.00 | |
| AB | 6,055.00 | |||
| AC | 69,250.00 | |||
| AD | 10,562.50 |