BILLING REQUIREMENT INCLUDES MAPPING — PROJ_MAP_FL = Y — PRORATION

ACRN Only with Mapping and PROJ_MAP_FL = Y.

ACRN/Line Item with Mapping and PROJ_MAP_FL = Y.

• Transaction project level projects are used to calculate ACRN allocation amounts. Enter, or use to select, a summary or transaction level project in the Project Level At and Below field on the Manage ACRN Bills screen. The project number entered is wildcarded. The same project number can be repeated on multiple lines in the child window table, but you cannot enter multiple lines in the child table window that are part of the same project tree branch. For example, you cannot enter project 1000.01 on one line and 1000.01.01 on another. This creates a circular calculation.

• 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) is NOT required. Many users have their project structures set up to restrict which accounts are eligible to charge a project by using the PAG or the Limit Accounts functionality. It is redundant to require you to re-map the same accounts. If accounts or project labor categories are not mapped for a particular ACRN or ACRN/Line Item sequence number, ALL accounts found in the BILL_EDIT_DETL table are eligible for the allocation calculation.  If the same project number is used on multiple lines in the child table window in the Project Level At and Below field on the Manage ACRN Bills screen and you map an account or project labor category for any row, all rows in the table for that specific project require account or project labor mapping. This helps prevent circular calculations. If another row exists in the child table window with a different project number value, accounts or project labor categories do not need to be mapped unless that project number is repeated on multiple lines. Mapping may exist for some lines, but not all.

• If mapping is used, 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 added to BILL_EDIT_DETL (WAWF_CST_CEIL_AMT, WAWF_FEE_CEIL_AMT, WAWF_TOT_CEIL_AMT).  

• Retainage calculation were removed from the Calculate ACRN Bills process and added to the Calculate Standard Bills screen. The retainage amounts are stored a new field added to BILL_EDIT_DETL (WAWF_RTNGE_AMT).  

• Allocating Schedule Bill amounts require an extra step. When the Use Project Mapping check box is selected, you enter project levels below the invoice level. Schedule Bill amounts are stored at the billing level (INVC_PROJ_ID) in BILL_EDIT_DETL. To allocate the Schedule Bill amount to an ACRN or ACRN/Line Item, use the Include Schedule Bill field in the table window. You can select one row, and only one row, in the child table window to indicate where the Schedule Bill amount, if any, is allocated.  

Since the PROJ_MAP_FL = Y, all calculations are performed at the transaction level (TRN_PROJ_ID).

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. Some 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.

PROJ_ACRN_ACCT

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

PROJ_ACRN_PLC

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/Line Item with Mapping methodology with PROJ_MAP_FL = Y, the TRANSACTION LEVEL project number is used for the calculation. Account and PLC mapping may occur.  When mapping occurs, BILL_EDIT_DETL must be used to calculate the allocation amounts in addition to the new fields added to BILL_EDIT_DETL (WAWF_CST_CEIL_AMT, WAWF_FEE_CEIL_AMT, WAWF_TOT_CEIL_AMT, WAWF_RTNGE_AMT) for any over ceiling and retainage amounts. Schedule Bill amounts (S_TRN_TYPE = SCH), if any, must be allocated to the line where the PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = Y. BILL_EDIT_INVC_HDR is 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 = AL; ACRN/Line Item with Mapping.

Verify the PROJ_MAP_FL value: PROJ_BILL_ACRN.PROJ_MAP_FL = Y.

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 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 TRN_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 moved from one account to another, thus 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 in (OT, OF, OC, R)). These transactions are stored in BILL_EDIT_DETL without an ACCT_ID so the user 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, 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, then new BILL_EDIT_DETL fields (WAWF_XXXX) are used. If no rows are returned, or if billable amount for all rows is 0.00, the source of the allocation amounts is the billable amount from BILL_EDIT_DETL.

The calculation 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 must be deducted from the original Billing Amounts from BILL_EDIT_DETL grouped by TRN_PROJ_ID/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, project labor category combination.  

Next, verify the Calculation Method.

Using the 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/Line Item Funded Value (each ACRN/Line Item)

              Total of ALL Remaining ACRN/Line Item Funded Values

Determine which transaction projects, accounts or PLCs, if any, share multiple ACRN/Line Items.  For example, Project Level At And Below ‘USN0418.01.01 and Project Labor Category EN are linked to ACRN AA/0001AA and ACRN AA/0001AB.  A table must be created to hold the shared Transaction Projects/Account/PLC/ACRN/Line Item mappings.

Table A —  Transaction Project/Account/PLC/ACRN Line Item Mapping

* All accounts for TRN_PROJ_ID USN0418.04% are used.

Calculate the Remaining ACRN Funded Value for each individual ACRN/Line Item along with the Total Remaining ACRN Funded Value (grouped by Transaction Project/Account/PLC). If the individual ACRN/Line Item 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 — Transaction Project/Account/PLC/ACRN Line Item Mapping with Remaining ACRN Funded Values

* All accounts for TRN_PROJ_ID USN0418.04% are used.

Continue with ACRN Proration calculation.

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

Table C — Proration Basis for Active ACRN/Line Items

Summarize the Adjusted Billable Amounts (Table 4) based upon the associated ACRN/Line Items.

Table D — Adjusted Billable Amounts based upon Transaction Project/Account/PLC/ACRN Line Item Mappings

(1) Associated ACRNs/Line Items are derived from Table A.

(2) Adjusted Billable Amounts are derived from Table 4.

Next, go to PROJ_ACRN_DETL to locate where the Schedule Bill Amount, if any, should be allocated.  

Select ACRN_ID||ACRN_LN_ITEM from PROJ_ACRN_DETL where INVC_PROJ_ID = USN0418 and APPLY_RESIDUAL_FL = Y;

ACRN_ID||ACRN_LN_ITEM

AC||0004AA

Next, go to BILL_EDIT_DETL to see if any rows exist for the INVC_PROJ_ID for S_TRN_TYPE = SCH with a MU_BILL_AMT <> 0.

• Yes. Add the BILL_AMT for the Schedule Bill to the Total Adjusted Billable Amount from Table D.

• No. Go to next step.

The BILL_AMT from BILL_EDIT_DETL for S_TRN_TYPE = SCH produced an amount equal to $10.750.00

Table E — Readjust Total Billable Amount to include Schedule Bill Amount, if any

(1) Associated ACRNs are derived from Table A.

(2) Adjusted Billable Amounts are derived from Table 4 or Table D.

(3) Value from BILL_EDIT_DETL.

(4) Adjusted Billable Amount + Schedule Bill Amount.

Match the Total Readjusted Billable Amount by associated ACRN (Table E) to the ACRN Proration (Table C).

(1) Adjusted Billable Amount from Table E.

(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/Line Item linked to the smallest ACRN Sequence Number with a Remaining ACRN Funded Value > 0.00.

Summary of ACRN Allocation Amounts:

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