BILLING REQUIREMENT INCLUDES MAPPING
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 Billings screen (Projects » Billing » Calculate Billings).
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 Billings screen (Projects
» Billing » Calculate Billings) and has been added to the Calculate
Billings screen (Projects » Billing »
Calculate Billings). 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 in the Maintain ACRN Bills screen (Projects
» Billing » Billing Master), 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
The ACRN data would be set up as follows in the Maintain ACRN Bills
screen (Projects » Billing » Billing
Master). Line and Subline data would not be entered based upon
the Billing Requirement selection of ACRN
Only w/Account Mapping. Accounts and PLCs are mapped.
|
Sequence
Number |
Active |
ACRN |
ACRN
Funded Value |
Previous
ACRN Allocation Amt |
Current
ACRN Allocation Amount |
Remaining
ACRN Funded Value |
|
1 |
Y |
AA |
38,000.00 |
0.00 |
0.00 |
38,000.00 |
|
2 |
Y |
AB |
41,000.00 |
0.00 |
0.00 |
41,000.00 |
|
3 |
Y |
AC |
80,000.00 |
0.00 |
0.00 |
80,000.00 |
|
4 |
Y |
AD |
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 |
The Account data could be set up as follows in the Accounts subtask
of the Maintain ACRN Bills screen (Projects
» Billing » Billing Master).
|
ACRN |
Starting Account |
Ending Account |
|
AB |
05020 |
05090 |
|
AC |
01200-010 |
01200-010 |
You could set up the PLC (Project
Labor Category) data as follows in the PLC Mapping subtask of the Maintain
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 Billings screen (Projects » Billing » Calculate Billings))
-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, 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 in the
Maintain ACRN Bills screen (Projects
» Billing » Billing Master). 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 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, 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 |
|
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 funded value balance.
This amount is calculated by the following formula:
PROJ_ACRN_DETL.ACRN_FUNDED_AMT - PROJ_ACRN_DETL.ACRN_BILLED_AMT
Remaining ACRN Funded Value amount for ACRN AA: 38,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount
Remaining to be Allocated.
The Project USN0418 Amount
Remaining to be Allocated, 36,382.50, is less than the Remaining
ACRN Funded Value of 38,000.00 for ACRN AA; so the system 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 |
|
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 funded 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 Funded Value amount for ACRN AB: 41,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount
Remaining to be Allocated.
The Project USN0418 Amount Remaining to be Allocated, 34,945.00,
is less than the Remaining ACRN Funded 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 will be 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 |
|
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 funded 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 Funded
Value amount for ACRN AC: 80,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount
Remaining to be Allocated.
The Project USN0418 Amount
Remaining to be Allocated, 10,750.00, is less than the Remaining
ACRN Funded Value of 80,000.00 for ACRN AC; so the system 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 will be 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 |
|
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 funded 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 Funded Value amount for ACRN AD: 25,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418 Amount Remaining
to be Allocated, 0.00, is less than the Remaining ACRN Funded 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 |
Using LIFO Methodology
Requirement: The
payment office make payments in sequential ACRN order within the contract,
exhausting all funds in the previous ACRN before paying from the next
ACRN.
Using the LIFO method, the funds
from ACRN AB are exhausted before using funds from ACRN AA. 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). The
sequencing of ACRNs is the same with the FIFO and LIFO methodologies.
When calculating
using FIFO, the lowest Sequence Number is used first. When
calculating using LIFO, the highest Sequence Number is used first.
Determine the ACRN Linked to
greatest ACRN Sequence Number value.
Sequence
Number 1 exists (SEQ_NO = 4) for ACRN AD.
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
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 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 will be 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.
Use the Adjusted Billable Amount
from Table 4, Adjusted Billable Amount Calculation, for Project USN0418
for ACRN AD:
|
BILL_LAB_CAT_CD |
Adjusted Billable Amount |
|
EN |
17,325.00 |
|
AD |
19,057.50 |
|
Billable Amount for ACRN AD: |
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 AD: |
36,382.50 |
Determine
the available ACRN AD remaining funded 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 Funded Value amount for ACRN AD: 25,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418 Amount Remaining
to be Allocated, 36,382.50, is greater than the Remaining ACRN Funded
Value of 25,000.00 for ACRN AD; allocate 25,000.00 to ACRN AD for Project
USN0418.
Check
for the existence of an ACRN Sequence Number < 4. Sequence Numbers
1, 2, and 3 exist. Use
the largest ACRN Sequence Number remaining for the next step of the calculation.
Sequence Number 3 exists (SEQ_NO
= 3) for ACRN AC.
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 = 3. 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 will be 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 |
|
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 funded 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 Funded Value amount
for ACRN AC: 80,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418 Amount
Remaining to be Allocated, 10,750.00, is less than the Remaining
ACRN Funded Value of 80,000.00 for ACRN AC; allocate 10,750.00 to ACRN
AC for Project USN0418.
Check for the existence of an
ACRN Sequence Number < 3. Sequence Numbers 1 and 2 exist. Use
the largest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AB is the 2nd 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
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 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 will be 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 rows 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 |
|
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 funded 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 Funded Value amount
for ACRN AB: 41,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418 Amount Remaining
to be Allocated, 34,945.00, is less than the Remaining ACRN Funded 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 1 exists. Use
the largest ACRN Sequence Number remaining for the next step of the calculation.
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 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 will be 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 |
|
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. |
-25,000.00 |
|
Amount
Remaining to be Allocated for ACRN AA: |
11,382.50 |
Determine
the available ACRN AA remaining funded 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 Funded Value amount
for ACRN AA: 38,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418 Amount Remaining
to be Allocated, 11,382.50, is less than the Remaining ACRN Funded Value
of 38,000.00 for ACRN AA; allocate 11,382.50 to ACRN AA for Project USN0418.
Check for the existence of an
ACRN Sequence Number < 1. None exists. Calculation
ends.
Summary of ACRN Allocation Amounts:
|
ACRN |
Allocation Amount |
|
AA |
11,382.50 |
|
AB |
34,945.00 |
|
AC |
10,750.00 |
|
AD |
25,000.00 |
|
Total Allocation: |
82,077.50 |
Upon completion of the calculation,
the following amounts would be 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 |
11,382.50 |
|
|
AB |
34,945.00 |
|
|
AC |
10,750.00 |
|
|
AD |
25,000.00 |
|
PROJ_ACRN_DETL |
ACRN_TOT_ALLOC_AMT |
|
AA |
11,382.50 |
|
|
AB |
34,945.00 |
|
|
AC |
10,750.00 |
|
|
AD |
25,000.00 |
|
PROJ_ACRN_DETL |
AVAIL_ACRN_AMT |
|
AA |
26,617.50 |
|
|
AB |
6,055.00 |
|
|
AC |
69,250.00 |
|
|
AD |
0.00 |
Using Proration Methodology
Requirement:
the payment
office will make 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 will be:
Individual
Remaining ACRN Funded Value (each ACRN)
Total
of ALL Remaining ACRN Funded Values
Determine which accounts or PLC’s,
if any, share multiple ACRNs. For
example, Project Labor Categories EN and AD are linked to ACRN AA and
ACRN AD. A
table must be 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 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
|
ACCT_ID |
BILL_LAB_CAT_CD |
Shared ACRN |
Active? |
Individual Remaining ACRN Funded Value |
Total Remaining ACRN Funded 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 Funded
Value to the Total Remaining ACRN
Funded Value Grouped by Account/PLC
for active ACRNs.
Table C -
Proration Basis for Active ACRNs
|
ACRN |
Related ACRN |
Individual Remaining ACRN Funded Value |
Total Remaining ACRN Funded 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 Funded 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 |
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 in the Maintain ACRN Bills screen (Projects
» Billing » Billing Master). 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
would be 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 in the Maintain ACRN Bills screen (Projects
» Billing » Billing Master) 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 Billings screen (Projects
» Billing » Calculate Billings). 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
has been removed from the Calculate ACRN Billings process (Projects
» Billing » Calculate Billings) and will be added to the Calculate
Billings screen (Projects » Billing »
Calculate Billings). The
retainage amounts will be 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:
|
Line Item |
Subline Item |
ACRN |
Accounting Appropriation
Number |
Funding Amt |
|
0001 |
0001AA |
AA |
5793400309475A110704040004081572806F503000
F03000 |
7,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 |
20,000.00 |
|
0003 |
0003AB |
AB |
8893400309475A110704040004081572806F336000
F07700 |
15,000.00 |
|
0004 |
0004AA |
AC |
7765658810475A110704040004081572806F336000
C15500 |
80,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 in the Maintain ACRN Bills screen (Projects » Billing » Billing Master).
Some accounts and PLCs are mapped.
|
ACRN
Sequence Number |
Active? |
ACRN |
Line Item |
Project Level At
and Below |
Include Schedule Bill |
ACRN
Funded Value |
Previous
ACRN Allocation Amounts |
Current
ACRN Allocation Amount |
Remaining
ACRN Funded Value |
|
1 |
Y |
AA |
0001AA |
USN0418.01.01 |
|
7,000.00 |
0.00 |
0.00 |
7,000.00 |
|
2 |
Y |
AA |
0001AB |
USN0418.01.01 |
|
11,000.00 |
0.00 |
0.00 |
11,000.00 |
|
3 |
Y |
AA |
0003AA |
USN0418.01.02 |
|
12,000.00 |
0.00 |
0.00 |
12,000.00 |
|
4 |
Y |
AB |
0002AA |
USN0418.02.01 |
|
20,000.00 |
0.00 |
0.00 |
20,000.00 |
|
5 |
Y |
AB |
0002AB |
USN0418.02.01 |
|
14,000.00 |
0.00 |
0.00 |
14,000.00 |
|
6 |
Y |
AB |
0003AB |
USN0418.02.02 |
|
15,000.00 |
0.00 |
0.00 |
15,000.00 |
|
7 |
Y |
AC |
0004AA |
USN0418.03 |
Y |
80,000.00 |
0.00 |
0.00 |
80,000.00 |
|
8 |
Y |
AD |
0005AA |
USN0418.04 |
|
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 |
Line Item |
Resource |
Accounts |
PLC Mapping |
|
AA |
0001AA |
Labor |
|
EN |
|
AA |
0001AB |
Labor |
|
EN |
|
AA |
0003AA |
Labor |
|
AD |
|
AB |
0002AA |
ODCs |
05030 |
|
|
AB |
0002AB |
ODCs |
05030 |
|
|
AB |
0003AB |
ODCs |
05030 |
|
|
AC |
0004AA |
Maintenance Contract |
01200-010 |
|
|
AD |
0005AA |
All Costs |
(no mapping) |
(no mapping) |
The Account data could be set up as follows in the Accounts subtask
of the Maintain ACRN Bills screen (Projects
» Billing » Billing Master).
PROJ_ACRN_ACCT
|
ACRN |
Line Item |
Project Level At and Below |
Starting Account |
Ending Account |
|
AB |
0002AA |
USN0418.02.01 |
05020 |
05030 |
|
AB |
0002AB |
USN0418.02.01 |
05020 |
05030 |
|
AB |
0003AB |
USN0418.02.02 |
05040 |
05090 |
|
AC |
0004AA |
USN0418.03 |
01200-010 |
01200-010 |
You could set up the PLC (Project
Labor Category) data as follows in the PLC Mapping subtask of the Maintain
ACRN Bills screen (Projects » Billing
» Billing Master).
PROJ_ACRN_PLC
|
ACRN |
Line Item |
Project Level At
and Below |
PLC |
Description |
|
AA |
0001AA |
USN0418.01.01 |
EN |
Engineer |
|
AA |
0001AB |
USN0418.01.01 |
EN |
Engineer |
|
AA |
0003AA |
USN0418.01.02 |
AD |
Admin Support |
A Standard Bill (calculated by the Calculate Billings screen (Projects » Billing » Calculate Billings))
-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/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 should occur.
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 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 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
in the Maintain ACRN Bills screen (Projects
» Billing » Billing Master). 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 result of the script produced
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 |
140.00 |
|
USN0418 |
USN0418.01.02 |
05000-020 |
|
AD |
0.00 |
180.00 |
|
USN0418 |
USN0418.02.01 |
05030 |
|
|
45.83 |
0.00 |
|
USN0418 |
USN0418.02.02 |
05040 |
|
|
9.17 |
0.00 |
|
USN0418 |
USN0418 |
01200-010 |
|
|
0.00 |
0.00 |
|
USN0418 |
USN0418.04.01 |
05000-010 |
|
AD |
0.00 |
35.00 |
|
USN0418 |
USN0418.04.02 |
05000-010 |
|
EN |
0.00 |
12.50 |
|
USN0418 |
USN0418.04.03 |
05030 |
|
|
0.00 |
0.00 |
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
|
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 |
14,000.00 |
0.00 |
140.00 |
13,860.00 |
|
USN0418 |
USN0418.01.02 |
05000-020 |
|
AD |
18,000.00 |
0.00 |
180.00 |
17,820.00 |
|
USN0418 |
USN0418.02.01 |
05030 |
|
|
25,000.00 |
45.83 |
0.00 |
24,954.17 |
|
USN0418 |
USN0418.02.02 |
05040 |
|
|
5,000.00 |
9.17 |
0.00 |
4,990.83 |
|
USN0418 |
USN0418 |
01200-010 |
|
|
10,750.00 |
0.00 |
0.00 |
10,750.00 |
|
USN0418 |
USN0418.04.01 |
05000-010 |
|
AD |
3,500.00 |
0.00 |
35.00 |
3,465.00 |
|
USN0418 |
USN0418.04.02 |
05000-010 |
|
EN |
1,250.00 |
0.00 |
12.50 |
1,237.50 |
|
USN0418 |
USN0418.04.03 |
05030 |
|
|
5,000.00 |
0.00 |
|
5,000.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, project labor category combination.
Next, verify the Calculation
Method.
Using FIFO Methodology
Requirement:
If there
is more than one ACRN within a contract line item, the payment office
makes payment in sequential ACRN order within the line item, exhausting
all funds in the previous ACRN before paying from the next ACRN.
Using the
FIFO method, the ACRN value is compared to each Line Item/Subline Item
to determine which ACRN funds to exhaust first. Line
Item 0001/ACRN AA is exhausted before using funds from Line Item 0001/ACRN
AB. You
are responsible for assigning the correct Sequence Number to the ACRN
value (that is, you must link Line Item 0001/ACRN AA to sequence number
1 and Line Item 0001/ACRN AB to sequence number 2).
Use the
account mapping table (PROJ_ACRN_ACCT), project labor category mapping
table (PROJ_ACRN_PLC), and BILL_EDIT_DETL to summarize the like accounts/PLCs
to the associated ACRN/Line Item combinations by transaction project number:
|
Project Level At and Below |
Accounts |
PLC |
ACRN |
Line Item |
|
USN0418.01.01 |
|
EN |
AA
AA |
0001AA
0001AB |
|
USN0418.01.02 |
|
AD |
AA |
0003AA |
|
USN0418.02.01 |
05030 |
|
AB
AB |
0002AA
0002AB |
|
USN0418.02.02 |
05040 |
|
AB |
0003AB |
|
USN0418.03 |
01200-010 |
|
AC |
0004AA |
|
USN0418.04 |
% |
|
AD |
0005AA |
The
USN0418.04 line doesn’t have any accounts or project labor categories
mapped in PROJ_ACRN_ACCT or PROJ_ACRN_PLC. When
no accounts or project labor categories are mapped for a sequence number,
ALL amounts from BILL_EDIT_DETL , for that transaction level and below
are used to compute the allocation amount(s).
Determine
the ACRN Linked to smallest ACRN/Line Item Sequence Number value.
Sequence
Number 1 exists (SEQ_NO = 1) for
ACRN/Line Item AA/0001AA.
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.
Determine
the transaction project level(s) to be used in the calculation. Wildcard
the TRN_PROJ_ID value.
The TRN_PROJ_ID
value for SEQ_NO = USN0418.01.01.
Look for any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.01.01 (LIKE ‘USN0418.01.01%’).
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, ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The PROJ_ACRN_PLC
table has a row for SEQ_NO = 1.
Match any
BILL_LAB_CAT_CD values from Table 4 to the PROJ_ACRN_PLC table.
The EN BILL_LAB_CAT_CD value is a match.
Use the Adjusted Billable Amount
from Table 4, Adjusted Billable Amount Calculation, for Project USN0418.01.01
for ACRN AA/0001AA:
|
TRN_PROJ_ID |
BILL_LAB_CAT_CD |
Adjusted Billable Amount |
|
USN0418.01.01 |
EN |
13,860.00 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
N. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for ACRN AA/00001AA: |
13,860.00 |
|
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/00001AA: |
13,860.00 |
Determine the available ACRN AA/00001AA remaining funded value balance.
This amount is calculated by the following formula:
PROJ_ACRN_DETL.ACRN_FUNDED_AMT - PROJ_ACRN_DETL.ACRN_BILLED_AMT
Remaining ACRN Funded Value amount for ACRN AA/00001AA: 7,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount
Remaining to be Allocated.
The Project USN0418 Amount
Remaining to be Allocated, 13,860.00, is greater than the Remaining
ACRN Funded Value of 7,000.00 for
ACRN AA; so the system allocates 7,000.00 to
ACRN AA/00001AA for Project USN0418.
Check for the existence of an ACRN Sequence Number > 1. Sequence
Numbers 2 through 8 exist. Use the smallest ACRN Sequence Number remaining
for the next step of the calculation.
AA/0001AB is the 2nd 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.
Determine the transaction project level(s) to be used in the calculation.
Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO = USN0418.01.01.
Look for any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.01.01 (LIKE
‘USN0418.01.01%’).
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 = 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.
The EN
BILL_LAB_CAT_CD value is a match.
Use the Adjusted Billable Amount from Table 4, Adjusted Billable Amount
Calculation, for Project USN0418.01.01 for ACRN AA/0001AB:
|
TRN_PROJ_ID |
BILL_LAB_CAT_CD |
Adjusted Billable Amount |
|
USN0418.01.01 |
EN |
13,860.00 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount forAA/0001AB : |
13,860.00 |
|
Subtract any allocation amounts with an ACRN Sequence
Number < 2 that contains the same mapping values: ACRN AA/0001AA exists.
|
-
7,000.000 |
|
Amount
Remaining to be Allocated for AA/0001AB: |
6,860.00 |
Determine the available ACRN AA/0001AB remaining
funded 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 Funded Value amount for ACRN AA/0001AB: 11,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount
Remaining to be Allocated.
The Project USN0418.01.01 Amount Remaining
to be Allocated, 6,860.00, is less than the Remaining
ACRN Funded Value of 11,000.00 for ACRN AA/0001AB; allocate 6,860.00
to AA/0001AB for Project USN0418.01.01.
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 AA/0003AA is the 3rd 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.
Determine the transaction project level(s) to be used in the calculation.
Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO = USN0418.01.02.
Look for
any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.01.02 (LIKE
‘USN0418.01.02%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
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.
The AD
BILL_LAB_CAT_CD value is a match.
Use the Adjusted Billable Amount
from Table 4, Adjusted Billable Amount Calculation, for Project USN0418.01.02
for ACRN AA/0003AA:
|
TRN_PROJ_ID |
BILL_LAB_CAT_CD |
Adjusted Billable Amount |
|
USN0418.01.02 |
AD |
17,820.00 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for ACRN AA/0003AA: |
17,820.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 AA/0003AA: |
17,820.00 |
Determine the available ACRN AA/0003AA remaining funded 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 Funded
Value amount for ACRN AA/0003AA:
20,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount
Remaining to be Allocated.
The Project USN0418.01.02 Amount Remaining
to be Allocated, 17,820.00, is less than the Remaining
ACRN Funded Value of 20,000.00 for ACRN AA/0003AA; allocate 17,820.00
to ACRN AA/0003AA for Project USN0418.01.02.
CCheck for the existence of an ACRN Sequence Number >3. Sequence
Numbers 4 through 8 exists. Use
the smallest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AB/0002AA is the 4th ACRN
sequence number. SEQ_NO
= 4.
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.
Determine
the transaction project level(s) to be used in the calculation. Wildcard
the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO
= USN0418.02.01. Look
for any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.02.01 (LIKE
‘USN0418.02.01%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
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.
Account 05030 matches.
Use the Adjusted
Billable Amount from Table 4, Adjusted Billable Amount Calculation,
for Project USN0418.02.01 for ACRN AB/0002AA:
|
TRN_PROJ_ID |
ACCT_ID |
Adjusted Billable Amount |
|
USN0418.02.01 |
05030 |
24,954.17 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for ACRN AB/0002AA: |
24,954.17 |
|
Subtract any allocation amounts with an ACRN
Sequence Number < 4 that contains the same mapping values: None exists. |
- 0.00 |
|
Amount
Remaining to be Allocated for ACRN AB/0002AA: |
24,954.17 |
Determine the available ACRN
AB/0002AA remaining
funded 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 Funded Value amount for ACRN AB/0002AA: 12,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418.02.01 Amount Remaining to be Allocated, 24,954.17,
is greater than the Remaining ACRN Funded
Value of 12,000.00 for ACRN AB/0002AA; allocate 12,000.00 to ACRN
AB/0002AA for Project USN0418.02.01.
Check for the existence of an
ACRN Sequence Number > 4. Sequence
Numbers 5 through 8 exists. Use
the smallest ACRN Sequence Number remaining for the next step of the calculation.
ACRN
AB/0002AB is the 5th ACRN sequence number. SEQ_NO
= 5.
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 = 5. Continue
calculation.
Determine the transaction project
level(s) to be used in the calculation. Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO
= USN0418.02.01. Look
for any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.02.01 (LIKE
‘USN0418.02.01%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The
PROJ_ACRN_PLC table does not have any rows for SEQ_NO = 5.
Check PROJ_ACRN_ACCT. PROJ_ACRN_ACCT
does have a row for SEQ_NO = 5.
Match any ACCT_ID values from
Table 4 to the PROJ_ACRN_ACCT table.
Account 05030 matches.
Use the Adjusted
Billable Amount from Table 4, Adjusted Billable Amount Calculation,
for Project USN0418.02.01 for ACRN AB/0002AB:
|
TRN_PROJ_ID |
ACCT_ID |
Adjusted Billable Amount |
|
USN0418.02.01 |
05030 |
24,954.17 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for ACRN AB/0002AB: |
24,954.17 |
|
Subtract any allocation amounts with an ACRN
Sequence Number < 5 that contains the same mapping values: ACRN AB/0002AA
exists |
-
12,000.00 |
|
Amount
Remaining to be Allocated for ACRN AB/0002AB: |
12,954.17 |
Determine
the available ACRN AB/0002AB remaining funded 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 Funded Value amount for ACRN AB/0002AB: 14,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418.02.01 Amount Remaining to be Allocated, 12,954.17,
is less than the Remaining ACRN Funded
Value of 14,000.00 for ACRN AB/0002AB; allocate 12,954.17 to ACRN
AB/0002AB for Project USN0418.02.01.
Check
for the existence of an ACRN Sequence Number > 5. Sequence Numbers
6 through 8 exists. Use
the smallest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AB/0003AB is the 6th ACRN
sequence number. SEQ_NO
= 6.
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 = 6. Continue
calculation.
Determine the transaction project
level(s) to be used in the calculation. Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO
= USN0418.02.02. Look for any
TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.02.02 (LIKE
‘USN0418.02.02%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The
PROJ_ACRN_PLC table does not have any rows for SEQ_NO = 6.
Check PROJ_ACRN_ACCT. PROJ_ACRN_ACCT
does have a row for SEQ_NO = 6.
Match any ACCT_ID values from
Table 4 to the PROJ_ACRN_ACCT table.
Account 05040 matches.
Use the Adjusted
Billable Amount from Table 4, Adjusted Billable Amount Calculation,
for Project USN0418.02.02 for ACRN AB/0002AB:
|
TRN_PROJ_ID |
ACCT_ID |
Adjusted Billable Amount |
|
USN0418.02.02 |
05040 |
4,990.83 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount forACRN AB/0003AB: |
4,990.83 |
|
Subtract any allocation amounts with an ACRN
Sequence Number < 6 that contains the same mapping values: None exists |
-
0.00 |
|
Amount
Remaining to be Allocated for ACRN AB/0003AB: |
4,990.83 |
Determine
the available ACRN AB/0003AB remaining funded 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 Funded Value amount
for ACRN AB/0003AB: 15,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418.02.02 Amount Remaining to be Allocated, 4,990.83,
is less than the Remaining ACRN Funded
Value of 15,000.00 for ACRN AB/0003AB; allocate 4,990.83 to ACRN
AB/0003AB for Project USN0418.02.02.
Check for the existence of an
ACRN Sequence Number > 6. Sequence Numbers 7 and 8 exist. Use
the smallest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AC/0004AA is the 7th ACRN
sequence number. SEQ_NO
= 7.
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 = 7. Continue
calculation.
Determine the transaction project
level(s) to be used in the calculation. Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO
= USN0418.03. Look
for any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.03 (LIKE ‘USN0418.03%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The
PROJ_ACRN_PLC table does not have any rows for SEQ_NO = 7.
Check
PROJ_ACRN_ACCT. PROJ_ACRN_ACCT
does not have any rows for SEQ_NO = 7.
If there are no rows in PROJ_ACRN_PLC
or PROJ_ACRN_ACCT, ALL rows from Table 4 for the TRN_PROJ_ID (USN0418.03%)
are used to calculate the allocation amount(s).
There are no Adjusted Billable
Amounts from Table 4, Adjusted Billable Amount Calculation, for Project
USN0418.03:
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = Y
for the sequence number. Continue allocation.
Combine the Adjusted
Billable Amount from Table 4, plus the BILL_AMT from BILL_EDIT_DETL
for the S_TRN_TYPE = SCH rows.
When looking for the S_TRN_TYPE = SCH
rows, ignore the TRN_PROJ_ID value. INVC_PROJ_ID
is used because the Schedule Bill records are computed at the invoice
level project.
|
ACCT_ID |
BILL_LAB_CAT_CD |
Adjusted Billable Amount |
Schedule Bill Amount |
Revised Adjusted Billable Amount |
|
|
|
0.00 |
10,750.00 |
10,750.00 |
|
Billable Amount for ACRN AC/0004AA: |
10,750.00 |
|
Subtract any allocation amounts with an ACRN
Sequence Number < 7 that contains the same mapping values: None exists |
-
0.00 |
|
Amount
Remaining to be Allocated for ACRN AC/0004AA: |
10,750.00 |
Determine
the available ACRN AC/0004AA remaining funded 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 Funded Value amount
for ACRN AC/0004AA: 80,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418.03 Amount Remaining to be Allocated, 10,750.00,
is less than the Remaining ACRN Funded
Value of 80,000.00 for ACRN AC/0004AA; allocate 10,750.00 to ACRN
AC/0004AA for Project USN0418.03.
Check for the existence of an
ACRN Sequence Number > 7. Sequence Number 8 exists. Use
the smallest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AD/0005AA is the 8th ACRN
sequence number. SEQ_NO
= 8.
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 = 8. Continue
calculation.
Determine the transaction project
level(s) to be used in the calculation. Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO
= USN0418.04. Look
for any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.04 (LIKE ‘USN0418.04%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The
PROJ_ACRN_PLC table does not have any rows for SEQ_NO = 8.
Check PROJ_ACRN_ACCT. PROJ_ACRN_ACCT
does not have any rows for SEQ_NO = 8.
If there are no rows in PROJ_ACRN_PLC
or PROJ_ACRN_ACCT, ALL rows from BILL_EDIT_DETL for
the TRN_PROJ_ID (USN0418.04%) will be used to calculate the allocation
amount(s).
Use the Adjusted Billable Amount
from Table 4, Adjusted Billable Amount Calculation, for Project USN0418.04
for ACRN AD/0005AA:
|
TRN_PROJ_ID |
ACCT_ID |
Adjusted Billable Amount |
|
USN0418.04.01 |
05000-010 |
3,465.00 |
|
USN0418.04.02 |
05000-010 |
1,237.50 |
|
USN0418.04.03 |
05030 |
5,000.00 |
|
TOTAL |
9,702.50 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for ACRN AD/0005AA : |
9,702.50 |
|
Subtract any allocation amounts with an ACRN
Sequence Number < 8 that contains the same mapping values: None exists |
-
0.00 |
|
Amount
Remaining to be Allocated for ACRN AD/0005AA: |
9,702.50 |
Determine
the available ACRN AD/0005AA remaining funded 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 Funded Value amount for ACRN AD/0005AA: 25,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418.04 Amount Remaining to be Allocated, 9,702.50,
is less than the Remaining ACRN Funded
Value of 25,000.00 for ACRN AD/0005AA; allocate 9,702.50 to ACRN
AD/0005AA for Project USN0418.04.
Check for the existence of an
ACRN Sequence Number > 8. None
exists. Calculation
ends.
Summary of ACRN Allocation Amounts:
|
ACRN |
Line Item |
Allocation Amount |
|
AA |
0001AA |
7,000.00 |
|
AA |
0001AB |
6,860.00 |
|
AA |
0003AA |
17,820.00 |
|
AB |
0002AA |
12,000.00 |
|
AB |
0002AB |
12,954.17 |
|
AB |
0003AB |
4,990.83 |
|
AC |
0004AA |
10,750.00 |
|
AD |
0005AA |
9,702.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 Line Item |
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 |
0001AA |
7,000.00 |
|
|
AA |
0001AB |
6,860.00 |
|
|
AA |
0003AA |
17,820.00 |
|
|
AB |
0002AA |
12,000.00 |
|
|
AB |
0002AB |
12,954.17 |
|
|
AB |
0003AB |
4,990.83 |
|
|
AC |
0004AA |
10,750.00 |
|
|
AD |
0005AA |
9,720.50 |
|
PROJ_ACRN_DETL |
ACRN_TOT_ALLOC_AMT |
|
AA |
0001AA |
7,000.00 |
|
|
AA |
0001AB |
6,860.00 |
|
|
AA |
0003AA |
17,820.00 |
|
|
AB |
0002AA |
12,000.00 |
|
|
AB |
0002AB |
12,954.17 |
|
|
AB |
0003AB |
4,990.83 |
|
|
AC |
0004AA |
10,750.00 |
|
|
AD |
0005AA |
9,720.50 |
|
PROJ_ACRN_DETL |
AVAIL_ACRN_AMT |
|
AA |
0001AA |
0.00 |
|
|
AA |
0001AB |
4,140.00 |
|
|
AA |
0003AA |
2,180.00 |
|
|
AB |
0002AA |
0.00 |
|
|
AB |
0002AB |
1,045.83 |
|
|
AB |
0003AB |
10,009.17 |
|
|
AC |
0004AA |
69,250.00 |
|
|
AD |
0005AA |
15,297.50 |
Using LIFO Methodology
Requirement:
If there
is more than one ACRN within a contract line item, the payment office
makes payment in sequential ACRN order within the line item, exhausting
all funds in the previous ACRN before paying from the next ACRN.
Using the
LIFO method, the ACRN value is compared to each Line Item/Subline Item
to determine which ACRN funds to exhaust first. AB/0001AB
is exhausted before using funds from AA/0001AA. You
ar responsible for assigning the correct Sequence Number to the ACRN value
(that is, you must link AA/0001AA to sequence number 1 and AB/0001AB to
sequence number 2).
Use the
account mapping table (PROJ_ACRN_ACCT), project labor category mapping
table (PROJ_ACRN_PLC), and BILL_EDIT_DETL to summarize the like accounts/PLCs
to the associated ACRN/Line Item combinations by transaction project number:
|
Project Level At and Below |
Accounts |
PLC |
ACRN |
Line Item |
|
USN0418.01.01 |
|
EN |
AA
AA |
0001AA
0001AB |
|
USN0418.01.02 |
|
AD |
AA |
0003AA |
|
USN0418.02.01 |
05030 |
|
AB
AB |
0002AA
0002AB |
|
USN0418.02.02 |
05040 |
|
AB |
0003AB |
|
USN0418.03 |
01200-010 |
|
AC |
0004AA |
|
USN0418.04 |
% |
|
AD |
0005AA |
The
USN0418.04 line doesn’t have any accounts or project labor categories
mapped in PROJ_ACRN_ACCT or PROJ_ACRN_PLC. When
no accounts or project labor categories are mapped for a sequence number,
ALL amounts from BILL_EDIT_DETL , for that transaction level and below
are used to compute the allocation amount(s).
Determine
the ACRN Linked to greatest ACRN/Line Item Sequence Number value.
ACRN AD/0005AA
is the 8th ACRN sequence number. SEQ_NO
= 8.
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 = 8. Continue
calculation.
Determine
the transaction project level(s) to be used in the calculation. Wildcard
the TRN_PROJ_ID value.
The TRN_PROJ_ID
value for SEQ_NO = USN0418.04.
Look for
any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.04 (LIKE ‘USN0418.04%’).
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, ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The PROJ_ACRN_PLC
table does not have any rows for SEQ_NO = 8.
Check PROJ_ACRN_ACCT.
PROJ_ACRN_ACCT
does not have any rows for SEQ_NO = 8.
If there
are no rows in PROJ_ACRN_PLC or PROJ_ACRN_ACCT, ALL rows from BILL_EDIT_DETL
for the
TRN_PROJ_ID (‘USN0418.04%’) will be used to calculate the allocation amount(s).
Use the
Adjusted Billable Amount from Table 4, Adjusted Billable Amount Calculation,
for Project USN0418.04 for ACRN AD/0005AA
|
TRN_PROJ_ID |
BILL_LAB_CAT_CD |
Adjusted Billable Amount |
|
USN0418.04.01 |
05000-010 |
3,465.00 |
|
USN0418.04.02 |
05000-010 |
1,237.50 |
|
USN0418.04.03 |
05030 |
5,000.00 |
|
TOTAL: |
9,702.50 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
N. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for AD/0005AA: |
9,702.50 |
|
Subtract any allocation amounts with an ACRN Sequence
Number > 8 that contains the same mapping values: None exists.
|
- 0.00 |
|
Amount
Remaining to be Allocated for ACRN AD/0005AA: |
9,702.50 |
Determine the available ACRN AD/0005AA remaining funded 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 Funded Value amount for ACRN AD/0005AA: 25,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount Remaining
to be Allocated.
The Project USN0418.04 Amount Remaining
to be Allocated, 9,702.50, is less than the Remaining
ACRN Funded Value of 25,000.00 for ACRN AD/0005AA; allocate 9,702.50
to ACRN AD/0005AA for Project USN0418.04.
Check for the existence of an ACRN Sequence Number < 8. Sequence
Numbers 1 through 7 exist. Use
the greatest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AC/0004AA is the 7th ACRN sequence number. SEQ_NO
= 7.
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 = 7. Continue
calculation.
Determine the transaction project level(s) to be used in the calculation.
Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO = USN0418.03.
Look for
any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.03 (LIKE ‘USN0418.03%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs
The PROJ_ACRN_PLC table does not have any rows for SEQ_NO = 7.
Check PROJ_ACRN_ACCT. PROJ_ACRN_ACCT
does not have any rows for SEQ_NO = 7.
If there are no rows in PROJ_ACRN_PLC or PROJ_ACRN_ACCT, ALL rows from
Table 4 for the TRN_PROJ_ID (USN0418.03%) are used to calculate the allocation
amount(s).
There are no Adjusted Billable Amounts from Table 4, Adjusted Billable
Amount Calculation, for Project USN0418.03:
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = Y
for the sequence number. Continue allocation.
Combine the Adjusted Billable Amount from Table 4, plus the BILL_AMT
from BILL_EDIT_DETL for the S_TRN_TYPE = SCH
rows. When looking for the S_TRN_TYPE = SCH
rows, ignore the TRN_PROJ_ID value. INVC_PROJ_ID
will be used because the Schedule Bill records are computed at the invoice
level project.
|
ACCT_ID |
BILL_LAB_CAT_CD |
Adjusted Billable Amount |
Schedule Bill Amount |
Revised Adjusted Billable Amount |
|
|
|
0.00 |
10,750.00 |
10,750.00 |
|
Billable Amount for ACRN AC/0004AA: |
10,750.00 |
|
Subtract any allocation amounts with an ACRN Sequence
Number > 7 that contains the same mapping values: None exists. |
-
0.00 |
|
Amount
Remaining to be Allocated for ACRN AC/0004AA: |
10,750.00 |
Determine the available ACRN AC/0004AA remaining funded 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 Funded Value amount for ACRN AC/0004AA: 80,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount Remaining
to be Allocated.
The Project USN0418.03 Amount Remaining
to be Allocated, 10,750.00, is less than the Remaining
ACRN Funded Value of 80,000.00 for ACRN AC/0004AA; allocate 10,750.00
to ACRN AC/0004AA for Project USN0418.03.
Check for the existence of an ACRN Sequence Number < 7. Sequence
Numbers 1 through 6 exist. Use
the greatest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AB/0003AB is the 6th ACRN sequence number. SEQ_NO
= 6.
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 = 6. Continue
calculation.
Determine the transaction project level(s) to be used in the calculation.
Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO = USN0418.02.02.
Look for
any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.02.02 (LIKE
‘USN0418.02.02%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The
PROJ_ACRN_PLC table does not have any rows for SEQ_NO = 6.
Check PROJ_ACRN_ACCT. PROJ_ACRN_ACCT
does have a row for SEQ_NO = 6.
Match any ACCT_ID values from
Table 4 to the PROJ_ACRN_ACCT table.
Account 05040 matches.
Use the Adjusted Billable Amount
from Table 4, Adjusted Billable Amount Calculation, for Project USN0418.02.02
for ACRN AB/0002AB:
|
TRN_PROJ_ID |
ACCT_ID |
Adjusted Billable Amount |
|
USN0418.02.02 |
05040 |
4,990.83 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for ACRN AB/0003AB: |
4,990.83 |
|
Subtract any allocation amounts with an ACRN
Sequence Number > 6 that contains the same mapping values: None exists.
|
- 0.00 |
|
Amount
Remaining to be Allocated for ACRN AB/0003AB: |
4,990.83 |
Determine the available ACRN AB/0003AB remaining funded 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 Funded Value amount for ACRN AB/0003AB: 15,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount Remaining
to be Allocated.
The Project USN0418.02.02 Amount Remaining
to be Allocated, 4,990.83, is less than the Remaining
ACRN Funded Value of 15,000.00 for ACRN AB/0003AB; allocate 4,990.83
to ACRN AB/0003AB for Project USN0418.02.02.
Check for the existence of an ACRN Sequence Number < 6. Sequence
Numbers 1 through 5 exist. Use
the greatest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AB/0002AB is the 5th ACRN sequence number. SEQ_NO
= 5.
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 = 5. Continue
calculation.
Determine the transaction project level(s) to be used in the calculation.
Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO = USN0418.02.01.
Look for
any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.02.01 (LIKE
‘USN0418.02.01%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The
PROJ_ACRN_PLC table does not have any rows for SEQ_NO = 5.
Check PROJ_ACRN_ACCT. PROJ_ACRN_ACCT
does have a row for SEQ_NO = 5.
Match any ACCT_ID values from
Table 4 to the PROJ_ACRN_ACCT table.
Account ‘05030’ matches.
Use the Adjusted
Billable Amount from Table 4, Adjusted Billable Amount Calculation,
for Project USN0418.02.01 for ACRN AB/0002AB:
|
TRN_PROJ_ID |
ACCT_ID |
Adjusted Billable Amount |
|
USN0418.02.01 |
05030 |
24,954.17 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for ACRN AB/0002AB: |
24,954.17 |
|
Subtract any allocation amounts with an ACRN
Sequence Number > 5 that contains the same mapping values: None exists. |
- 0.00 |
|
Amount
Remaining to be Allocated for ACRN AB/0002AB: |
24,954.17 |
Determine
the available ACRN AB/0002AB remaining funded 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 Funded Value amount
for ACRN AB/0002AB: 14,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418.02.01 Amount Remaining to be Allocated, 24,954.17,
is greater than the Remaining ACRN Funded
Value of 14,000.00 for ACRN AB/0002AB; allocate 14,000.00 to ACRN
AB/0002AB for Project USN0418.02.01.
Check for the existence of an
ACRN Sequence Number < 5. Sequence Numbers 1 through 4 exist. Use
the greatest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AB/0002AA is the 4th ACRN
sequence number. SEQ_NO
= 4.
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.
Determine the transaction project
level(s) to be used in the calculation. Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO
= USN0418.02.01. Look
for any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.02.01 (LIKE
‘USN0418.02.01%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The
PROJ_ACRN_PLC table does not have any rows for SEQ_NO = 4.
Check PROJ_ACRN_ACCT. PROJ_ACRN_ACCT
does have a row for SEQ_NO = 4.
Match any ACCT_ID values from
Table 4 to the PROJ_ACRN_ACCT table.
Account ‘05030’ matches.
Use the Adjusted
Billable Amount from Table 4, Adjusted Billable Amount Calculation,
for Project USN0418.02.01 for ACRN AB/0002AA:
|
TRN_PROJ_ID |
ACCT_ID |
Adjusted Billable Amount |
|
USN0418.02.01 |
05030 |
24,954.17 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for ACRN AB/0002AA: |
24,954.17 |
|
Subtract any allocation amounts with an ACRN
Sequence Number > 4 that contains the same mapping values: Sequence
# 5, AB/0002AB exists |
-
14,000.00 |
|
Amount
Remaining to be Allocated for ACRN AB/0002AA: |
10,954.17
|
Determine
the available ACRN AB/0002AA remaining funded 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 Funded Value amount
for ACRN AB/0002AA: 12,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418.02.01 Amount Remaining to be Allocated, 10,954.17,
is less than the Remaining ACRN Funded
Value of 12,000.00 for ACRN AB/0002AA; allocate 10,954.17 to ACRN
AB/0002AA for Project USN0418.02.01.
Check for the existence of an
ACRN Sequence Number < 4. Sequence Numbers 1 through 3 exist. Use the
greatest ACRN Sequence Number remaining for the next step of the calculation.
ACRN AA/0003AA is the 3rd 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.
Determine the transaction project
level(s) to be used in the calculation. Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO
= USN0418.01.02. Look
for any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.01.02 (LIKE
‘USN0418.01.02%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The
PROJ_ACRN_PLC table has a row for SEQ_NO = 3.
Match any BILL_LAB_CAT_CD values
from Table 4 to the PROJ_ACRN_PLC table.
The AD
BILL_LAB_CAT_CD value is a match.
Use the Adjusted
Billable Amount from Table 4, Adjusted Billable Amount Calculation,
for Project USN0418.01.02 for ACRN AA/0003AA:
|
TRN_PROJ_ID |
BILL_LAB_CAT_CD |
Adjusted Billable Amount |
|
USN0418.01.02 |
AD |
17,820.00 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount forACRN AA/0003AA: |
17,820.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 AA/0003AA: |
17,820.00 |
Determine
the available ACRN AA/0003AA remaining funded 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 Funded Value amount
for ACRN AA/0003AA: 20,000.00
Allocate the smaller of the Remaining
ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418.01.02 Amount Remaining to be Allocated, 17,820.00,
is less than the Remaining ACRN Funded
Value of 20,000.00 for ACRN AA/0003AA; allocate 17,820.00 to ACRN
AA/0003AA for Project USN0418.01.02.
Check for the existence of an
ACRN Sequence Number < 3. Sequence Numbers 1 and 2 exist. Use
the greatest ACRN Sequence Number remaining for the next step of the calculation.
ACRN/Line Item AA/0001AB is the
2nd 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.
Determine the transaction project
level(s) to be used in the calculation. Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO
= USN0418.01.01. Look
for any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.01.01 (LIKE
‘USN0418.01.01%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The
PROJ_ACRN_PLC table has a row for SEQ_NO = 2.
Match any BILL_LAB_CAT_CD values
from Table 4 to the PROJ_ACRN_PLC table.
The EN
BILL_LAB_CAT_CD value is a match.
Use the Adjusted
Billable Amount from Table 4, Adjusted Billable Amount Calculation,
for Project USN0418.01.01 for ACRN AA/0001AB:
|
TRN_PROJ_ID |
BILL_LAB_CAT_CD |
Adjusted Billable Amount |
|
USN0418.01.01 |
EN |
13,860.00 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for ACRN AA/0001AB: |
13,860.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 AA/0001AB: |
13,860.00 |
Determine
the available ACRN AA/0001AB remaining funded 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 Funded Value amount
for ACRN AA/0001AB: 11,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418.01.01 Amount
Remaining to be Allocated, 13,860.00, is greater than the Remaining ACRN
Funded Value of 11,000.00 for ACRN AA/0001AB; allocate 11,000.00 to AA/0001AB
for Project USN0418.01.01.
Check for the existence of an
ACRN Sequence Number < 2. Sequence Number 1 exists. Use the smallest
ACRN Sequence Number remaining for the next step of the calculation.
Sequence Number 1 exists (SEQ_NO
= 1) for ACRN/Line Item AA/0001AA.
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.
Determine the transaction project
level(s) to be used in the calculation. Wildcard the TRN_PROJ_ID value.
The TRN_PROJ_ID value for SEQ_NO
= USN0418.01.01. Look
for any TRN_PROJ_ID values in BILL_EDIT_DETL that
begins with USN0418.01.01 (LIKE
‘USN0418.01.01%’).
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. ALL
accounts from BILL_EDIT_DETL will
be used to allocate costs.
The
PROJ_ACRN_PLC table has a row for SEQ_NO = 1.
Match any BILL_LAB_CAT_CD values
from Table 4 to the PROJ_ACRN_PLC table.
The ‘EN’ BILL_LAB_CAT_CD value
is a match.
Use the Adjusted
Billable Amount from Table 4, Adjusted Billable Amount Calculation,
for Project USN0418.01.01 for ACRN AA/0001AA:
|
TRN_PROJ_ID |
BILL_LAB_CAT_CD |
Adjusted Billable Amount |
|
USN0418.01.01 |
EN |
13,860.00 |
Go to PROJ_ACRN_DETL.APPLY_RESIDUAL_FL (Include
Schedule Bill field) to check the value.
Yes. Add the BILL_AMT
for the Schedule Bill to the Adjusted Billable amount for the sequence
number.
No. Go to next step.
The PROJ_ACRN_DETL.APPLY_RESIDUAL_FL = N
for the sequence number. Continue allocation.
|
Billable Amount for ACRN AA/00001AA: |
13,860.00 |
|
Subtract any allocation amounts with an ACRN
Sequence Number < 1 that contains the same mapping values: Sequence
#2; AA/0001AB |
-
11,000.00 |
|
Amount
Remaining to be Allocated for ACRN AA/00001AA: |
2,860.00 |
Determine
the available ACRN AA/0001AA remaining funded 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 Funded Value amount
for ACRN AA/0001AA: 7,000.00
Allocate the smaller of the Remaining ACRN Funded Value or Amount Remaining to be Allocated.
The Project USN0418.01.01 Amount Remaining to be Allocated, 2,860.00,
is less than the Remaining ACRN Funded
Value of 7,000.00 for ACRN AA/0001AA; allocate 2,860.00 to AA/0001AA
for Project USN0418.01.01.
Check for the existence of an
ACRN Sequence Number < 1. None exists. Calculation
ends.
Summary of ACRN Allocation Amounts:
|
ACRN |
Line Item |
Allocation Amount |
|
AA |
0001AA |
7,000.00 |
|
AA |
0001AB |
6,860.00 |
|
AA |
0003AA |
17,820.00 |
|
AB |
0002AA |
12,000.00 |
|
AB |
0002AB |
12,954.17 |
|
AB |
0003AB |
4,990.83 |
|
AC |
0004AA |
10,750.00 |
|
AD |
0005AA |
9,702.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 Line Item |
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 |
0001AA |
2,860.00 |
|
|
AA |
0001AB |
11,000.00 |
|
|
AA |
0003AA |
17,820.00 |
|
|
AB |
0002AA |
10,954.17 |
|
|
AB |
0002AB |
14,000.00 |
|
|
AB |
0003AB |
4,990.83 |
|
|
AC |
0004AA |
10,750.00 |
|
|
AD |
0005AA |
9,702.50 |
|
PROJ_ACRN_DETL |
ACRN_TOT_ALLOC_AMT |
|
AA |
0001AA |
2,860.00 |
|
|
AA |
0001AB |
11,000.00 |
|
|
AA |
0003AA |
17,820.00 |
|
|
AB |
0002AA |
10,954.17 |
|
|
AB |
0002AB |
14,000.00 |
|
|
AB |
0003AB |
4,990.83 |
|
|
AC |
0004AA |
10,750.00 |
|
|
AD |
0005AA |
9,702.50 |
|
PROJ_ACRN_DETL |
AVAIL_ACRN_AMT |
|
AA |
0001AA |
4,140.00 |
|
|
AA |
0001AB |
0.00 |
|
|
AA |
0003AA |
2,180.00 |
|
|
AB |
0002AA |
1,045.83 |
|
|
AB |
0002AB |
0.00 |
|
|
AB |
0003AB |
10,009.17 |
|
|
AC |
0004AA |
69,250.00 |
|
|
AD |
0005AA |
15,297.50 |
Using the Proration Methodology
Requirement:
the payment
office will make 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
|
Project Level At
and Below TRN_PROJ_ID |
ACCT_ID |
BILL_LAB_CAT_CD |
Shared ACRN/Line Item |
|
USN0418.01.01 |
|
EN |
AA/0001AA
AA/0001AB |
|
USN0418.01.02 |
|
AD |
AA/0003AA |
|
USN0418.02.01 |
05030 |
|
AB/0002AA
AB/0002AB |
|
USN0418.02.02 |
05040 |
|
AB/0003AB |
|
USN0418.03 |
01200-010 |
|
AC/0004AA |
|
USN0418.04 |
* |
* |
AD/0005AA |
* All accounts for TRN_PROJ_ID
USN0418.04% will be 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
|
TRN_PROJ_ID |
ACCT_ID |
BILL_LAB_CAT_CD |
Shared ACRN/Line Item |
Active? |
Individual Remaining ACRN/Line Item Funded Value |
Total Remaining ACRN Funded Value Grouped by Transaction
Project/Account/PLC |
|
USN0418.01.01 |
|
EN |
AA/0001AA
AA/0001AB |
Y
Y |
7,000.00
11,000.00 |
18,000.00 |
|
USN0418.01.02 |
|
AD |
AA/0003AA |
Y |
20,000.00 |
20,000.00 |
|
USN0418.02.01 |
05030 |
|
AB/0002AA
AB/0002AB |
Y
Y |
12,000.00
14,000.00 |
26,000.00 |
|
USN0418.02.02 |
05030 |
|
AB/0003AB |
Y |
15,000.00 |
15,000.00 |
|
USN0418.03 |
01200-010 |
|
AC/0004AA |
Y |
80,000.00 |
80,000.00 |
|
USN0418.04 |
* |
* |
AD/0005AA |
Y |
25,000.00 |
25,000.00 |
* All accounts for TRN_PROJ_ID
USN0418.04% will be used.
Continue
with ACRN Proration calculation –
The ACRN Proration will be 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
|
ACRN/Line Item |
Related ACRN/Line Item |
Individual Remaining ACRN/Line Funded Value |
Total Remaining ACRN Funded Value Grouped by Account |
ACRN Proration |
|
AA/0001AA |
AA/0001AB |
7,000.00 |
18,000.00 |
(7,000.00 / 18,000.00) |
|
AA/0001AB |
AA/0001AA |
11,000.00 |
18,000.00 |
(11,000.00 / 18,000.00) |
|
AA/0003AA |
|
20,000.00 |
20,000.00 |
(20,000.00 / 20,000.00) |
|
AB/0002AA |
AB/0002AB |
12,000.00 |
26,000.00 |
(12,000.00 / 26,000.00) |
|
AB/0002AB |
AB/0002AA |
14,000.00 |
26,000.00 |
(14,000.00 / 26,000.00) |
|
AB/0003AB |
|
15,000.00 |
15,000.00 |
(15,000.00 / 15,000.00) |
|
AC/0004AA |
|
80,000.00 |
80,000.00 |
(80,000.00 / 80,000.00) |
|
AD/0005AA |
|
25,000.00 |
25,000.00 |
(25,000.00 / 25,000.00) |
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
|
TRN_PROJ_ID |
ACCT_ID |
BILL_LAB_CAT_CD |
Associated ACRN/ Line Items (1) |
Adjusted Billable Amount (2) |
Total Adjusted Billable Amount Grouped by Transaction
Project/Account/PLC (2) |
|
USN0418.01.01 |
|
EN |
AA/0001AA
AA/0001AB |
13,860.00 |
13,860.00 |
|
USN0418.01.02 |
|
AD |
AA/0003AA |
17,820.00 |
17,820.00 |
|
USN0418.02.01 |
05030 |
|
AB/0002AA
AB/0002AB |
24,954.17 |
24,954.17 |
|
USN0418.02.02 |
05040 |
|
AB/0003AB |
4,990.83 |
4,990.83 |
|
USN0418.03 |
|
|
AC/0004AA |
0.00 |
0.00 |
|
USN0418.04 |
05000-010
05000-020
05030 |
|
AD/0005AA |
3,465.00
1,237.50
5,000.00 |
9,702.50 |
(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.
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
|
TRN_PROJ_ID |
ACCT_ID |
BILL_LAB_CAT_CD |
Associated ACRN/ Line Items (1) |
Adjusted Billable Amount (2) |
Schedule Bill Amount (3) |
Total Readjusted Billable
Amount Grouped by Transaction Project/Account/PLC (4) |
|
USN0418.01.01 |
|
EN |
AA/0001AA
AA/0001AB |
13,860.00 |
|
13,860.00 |
|
USN0418.01.02 |
|
AD |
AA/0003AA |
17,820.00 |
|
17,820.00 |
|
USN0418.02.01 |
05030 |
|
AB/0002AA
AB/0002AB |
24,954.17 |
|
24,954.17 |
|
USN0418.02.02 |
05040 |
|
AB/0003AB |
4,990.83 |
|
4,990.83 |
|
USN0418.03 |
|
|
AC/0004AA |
0.00 |
10,750.00 |
10,750.00 |
|
USN0418.04 |
05000-010
05000-020
05030 |
|
AD/0005AA |
3,465.00
1,237.50
5,000.00 |
|
9,702.50 |
(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).
|
TRN_PROJ_ID |
ACRN/Line Item |
Readjusted Billable Amount (1) |
Proration (2) |
Radio Computation |
Allocation Amount |
|
USN0418.01.01 |
AA/0001AA |
13,860.00 |
(7,000.00 / 18,000.00) |
13,860.00 * (7,000.00 / 18,000.00) |
5,390.00 |
|
USN0418.01.01 |
AA/0001AB |
13,860.00 |
(11,000.00 / 18,000.00) |
13,860.00
* (11,000.00 / 18,000.00) |
8,470.00 |
|
USN0418.01.02 |
AA/0003AA |
17,820.00 |
(20,000.00 / 20,000.00) |
17,820.00
*(20,000.00 / 20,000.00) |
17,820.00 |
|
USN0418.02.01 |
AB/0002AA |
24,954.17 |
(12,000.00 / 26,000.00) |
24,954.17
* (12,000.00 / 26,000.00) |
11,517.31 |
|
USN0418.02.01 |
AB/0002AB |
24,954.17 |
(14,000.00 / 26,000.00) |
24,954.17
* (14,000.00 / 26,000.00) |
13,436.86 |
|
USN0418.02.02 |
AB/0003AB |
4,990.83 |
(15,000.00 / 15,000.00) |
4,990.83
* (15,000.00 / 15,000.00) |
4,990.83 |
|
USN0418.03 |
AC/0004AA |
10,750.00 |
(80,000.00 / 80,000.00) |
10,750.00
* (80,000.00 / 80,000.00) |
10,750.00 |
|
USN0418.04 |
AD/0005AA |
9,702.50 |
(25,000.00 / 25,000.00) |
9,702.50
* (25,000.00 / 25,000.00) |
9,702.50 |
|
Total ACRN Allocation Amount |
82,077.50 |
(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:
|
ACRN |
Line Item |
Allocation Amount |
|
AA |
0001AA |
5,390.00 |
|
AA |
0001AB |
8,470.00 |
|
AA |
0003AA |
17,820.00 |
|
AB |
0002AA |
11,517.31 |
|
AB |
0002AB |
13,436.86 |
|
AB |
0003AB |
4,990.83 |
|
AC |
0004AA |
10,750.00 |
|
AD |
0005AA |
9,702.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 Line Item |
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 |
0001AA |
5,390.00 |
|
|
AA |
0001AB |
8,470.00 |
|
|
AA |
0003AA |
17,820.00 |
|
|
AB |
0002AA |
11,517.31 |
|
|
AB |
0002AB |
13,436.86 |
|
|
AB |
0003AB |
4,990.83 |
|
|
AC |
0004AA |
10,750.00 |
|
|
AD |
0005AA |
9,702.50 |
|
PROJ_ACRN_DETL |
ACRN_TOT_ALLOC_AMT |
|
AA |
0001AA |
5,390.00 |
|
|
AA |
0001AB |
8,470.00 |
|
|
AA |
0003AA |
17,820.00 |
|
|
AB |
0002AA |
11,517.31 |
|
|
AB |
0002AB |
13,436.86 |
|
|
AB |
0003AB |
4,990.83 |
|
|
AC |
0004AA |
10,750.00 |
|
|
AD |
0005AA |
9,702.50 |
|
PROJ_ACRN_DETL |
AVAIL_ACRN_AMT |
|
AA |
0001AA |
1,610.00 |
|
|
AA |
0001AB |
2,530.00 |
|
|
AA |
0003AA |
2,180.00 |
|
|
AB |
0002AA |
482.69 |
|
|
AB |
0002AB |
563.14 |
|
|
AB |
0003AB |
10,009.17 |
|
|
AC |
0004AA |
69,250.00 |
|
|
AD |
0005AA |
15,297.50 |