BILLING REQUIREMENT INCLUDES MAPPING — PROJ_MAP_FL = N — PRORATION

ACRN Only with Mapping and PROJ_MAP_FL = N.

ACRN/Line Item with Mapping and PROJ_MAP_FL = N.

• 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:

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.

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:

Set up the Account data as follows on the Accounts subtask of the Manage ACRN Bills screen.

Set up the PLC (Project Labor Category) data as follows on the PLC Mapping subtask of the Manage ACRN Bills screen.

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

(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

 (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

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

(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 Funded Value.

The mathematical equation to determine the Proration amounts is:

      Individual Remaining ACRN Funded Value (each ACRN)

              Total of ALL Remaining ACRN Funded 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

Calculate the Remaining ACRN Funded Value for each individual ACRN along with the Total Remaining ACRN Funded Value (grouped by account). If the individual ACRN Remaining ACRN Funded 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 Funded 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 Funded Value in the calculation.

Table B — Account/PLC/ACRN Mapping with Remaining ACRN Funded Values

Continue with ACRN Proration calculation –

The ACRN Proration is based upon the Individual Remaining ACRN Funded Value to the Total Remaining ACRN Funded Value Grouped by Account/PLC for active ACRNs.

Table C — Proration Basis for Active ACRNs

Summarize the Adjusted Billable Amounts (Table 4) based upon the associated ACRNs.

Table D — Adjusted Billable Amounts based upon Account/PLC/ACRN Mappings

(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).

(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 Funded Value > 0.00.

Summary of ACRN Allocation Amounts:

Upon completion of the calculation, the following amounts are updated in the PROJ_BILL_ACRN and PROJ_ACRN_DETL tables: