LeasePak Utilities

236 Batch IDC Adjustment Utility

Provides a means to adjust (write up or down) Book IDC and Tax IDC and write up IDC2 Notary Fee (a broker payable) by way of a batch input file.
This utility does not:

• Adjust IDC amounts other than Book IDC, Tax IDC, and IDC2 Notary Fee
• Make asset-level IDC adjustments
• Work with Asset Focus leases
• Write down IDC2 Notary Fee

Processing time is directly proportional to the size of the batch input file.

Since IDC2 is a broker payable, users must first have the appropriate broker set up in LeasePak prior to using this utility. Users can set up brokers through U0219 Broker. Brokers must be in an Active status.

For automatic accounts payable disbursal of IDC2, the LeasePak administrator must set the U0712 Miscellaneous Customizations switch Alternate A/P Check Screen to 'B' and assign a general ledger cash account number to Batch Application Disbursal Bank Code in U0212 Miscellaneous Customizations.

Creating the Batch Input File

The following specifies the format for the batch input file that the user will need to create before running the utility.

File Name
pnn_btchidc.dat, where nn is the one or two-digit portfolio number without space padding. The user will need a separate file for each portfolio that has leases to process.

Examples

	  p2_btchidc.dat

	  p99_btchidc.dat

After the utility finishes processing the items in an input file, it renames the file, adding a date/time block (YYYYMMDDHHMMSS) indicating the time processed — for example, p99_btchidc_20071205231155.dat.

File Location
$top/env/env_name/data directory, where $top is the location of the installed LeasePak server software and env_name is the individual environment name.
Users should also keep a copy of batch input files in a separate backup location.

File Format
The batch input file is a comma-delimited text file, with each line in the file representing an individual adjustment.

Each line of the input file requires the following elements in order:

• Lease number
• IDC type
• New beginning IDC amount

For IDC2 Notary Fee, the input line also requires:

• Broker number
• Disburse Y/N flag

Order	Element	Format	Example	Notes
1	Lease number	Lnnnnnnnnnn	L498573 (representing lease 498573)	Always required
2	IDC type	IDCB for Book IDC IDCT for Tax IDC IDC2 for Notary Fee	IDCB	Always required
3	New beginning IDC amount	9999999999, where the last two digits represent cents (do not use a decimal point)	43298 (representing $432.98)	Always required
4	Broker number	Bnnnnnnnnn	B756 (representing broker 756)	Required for IDC2
5	Disbursal Y/N flag	Y to disburse payable, N to leave payable undisbursed	Y	Required for IDC2

Line examples:

	  L498573,IDCT,43298

	    (adjust the Tax IDC   of Lease 498573 to the new beginning amount of $432.98)

	  L337088,IDC2,7500,B23,Y

	    (write up the   IDC2 Notary Fee of Lease 337088 to the new beginning amount of $75.00 and   disburse the payable to Broker 23)

Example of multiple updates to the same lease:

	  L498573,IDCB,65714

	  L498573,IDCT,43298

	  L498573,IDC2,15000,B756,Y
	  

Three separate lines:

• The first adjusts the Book IDC of Lease 498573 to the new beginning amount of $657.14
• The second adjusts the Tax IDC of Lease 498573 to the new beginning amount of $432.98
• The third writes up the IDC2 Notary Fee of Lease 498573 to the new beginning amount of $150.00 and disburses the payable to Broker 756

Note that the utility processes adjustments one line at a time, each line representing a separate transaction, so that multiple adjustments to the same lease do not need to be on consecutive lines.

Running the Utility
The following specifies how to run Utility 236 Batch IDC Adjustments Utility.

User Interface
Users must first create the batch input file and place it in their environment's $top/env/env_name/data directory before running the utility.
After users log on the LeasePak server, start the LeasePak Utilities, and select the Batch IDC Adjustment utility, the program displays:

This utility processes a   batch input file to make adjustments to Book IDC and Tax IDC and perform   write-ups to IDC2 Notary Fee.
Do you wish to continue   (Y/N)?

Users need to enter Y to continue. The program displays:

Client Password?

Users need to enter their LeasePak client string password. The program displays:

Enter the portfolio number   or <RETURN> to exit>

Users need to enter a portfolio number that matches one of the batch input files they have previously set up.
The program then proceeds to process the specified portfolio's input file. When complete, the program displays:

Processing complete. The   audit (pnn_btchidc_a.rpt) and   exceptions (pnn_btchidc_b.rpt)   reports are in your LeasePak server home directory.

(where pnn is 'p' followed by the portfolio number) and return users to the LeasePak Utilities menu.
The user who ran the utility can view and print these reports interactively in LeasePak through R0929 Print/View User Reports.

Edit Checks
As the utility reads the input file, it performs the following edit checks on each line before processing:

Element	Edit Check	Exception Message on Failure
Lease number	Must be first element on the line and begin with 'L'	Lease number missing or invalid
Lease number	Must exist in database as a lease	Lease does not exist
Lease number	Must belong to specified portfolio	Lease is not in specified portfolio
Lease number	Must not be an Asset Focus lease	Lease is an Asset Focus lease
Lease number	Must not be paid off or terminated	Lease is paid off/terminated
IDC type	Must be second on the line and be 'IDCB', 'IDCT', or 'IDC2'	IDC type missing or invalid
New beginning IDC amount	Must be third element on the line, must be positive numeric amount without a decimal point	Beginning IDC amount missing or invalid
New beginning IDC amount	For write-downs, difference between new and original beginning IDC must not exceed existing unamortized amount: New Beginning IDC – Original Beginning IDC + Existing Unamortized >= 0	Amount of decrease cannot exceed unamortized amount
New beginning IDC amount	For IDC2, new beginning IDC must be greater than original beginning IDC	New beginning IDC2 must be greater than previous IDC2
Broker number	For IDC2 only*; must be fourth element on the line and begin with 'B'	Broker number missing or invalid
Broker number	For IDC2 only*; must exist in database as a broker	Broker does not exist
Broker number	For IDC2 only*; must be a broker in Active status	Broker is not active
Disbursal Y/N flag	For IDC2 only*; must be fifth element on the line and be 'Y' or 'N'	Disbursal Y/N flag missing or invalid

* Otherwise, the utility ignores the element

The utility does not process input lines that fail any of the above checks. Instead, the utility displays these on the exceptions report along with the appropriate message.

Detail Record Adjustments
For each successful adjustment, the utility will alter the following LeasePak database columns:

Book IDC, Amortization Methods (AMOR, AMR2)
Table: rls Lease

Column	Description	New Amount
idc_d	Total Initial Direct Costs	Beginning IDC amount from input line
idc_rem_d	Unamortized IDC – Book	Recalculated amount based on new beginning IDC amount (same calculation as though using U0104 Payment Reschedule)

Book IDC, Income Method (INCM)
Table: rls Lease

Column	Description	New Amount
idc_d	Total Initial Direct Costs	Beginning IDC amount from input line

Tax IDC
Table: rls Lease

Column	Description	New Amount
idc_tax_d	Tax IDC	Beginning IDC amount from input line
idc_rem_tax_d	Unamortized IDC – Tax	Recalculated amount based on new beginning IDC amount (same calculation as though using U0104 Payment Reschedule)

IDC2 Notary Fee Write-up
Table: rlsa Lease 2

Column	Description	New Value/Amount
idc2_notary_fee_pay_to_s	Notary fee payable to IDC2	Broker number from input line
idc2_notary_fee_d	Notary fee IDC2	Beginning IDC2 amount from input line
idc2_notary_fee_ue_d	Notary fee unearned IDC2	Recalculated amount based on new beginning IDC2 amount (same calculation as though using U0104 Payment Reschedule)
idc2_notary_fee_ap_d*	Notary fee accounts payable IDC2	Increase in unamortized IDC2 amount payable to the broker

* If the input line indicates that the utility should disburse the payable (flag 'Y'), the utility will clear this amount.


General Ledger Account Adjustments
For each successful adjustment, the utility will make the following general ledger entries:


Book IDC Write-up, Amortization Methods (AMOR, AMR2)

Debit	Credit	Amount
34 Unamortized IDC Cost	102 IDC Offset	After recalculation, the difference between the new and old beginning IDC amounts

Book IDC Write-up, Income Method (INCM)

Debit	Credit	Amount
7 Unearned Income	33 Revenue	After recalculation, the difference between the new and old beginning IDC amounts

Book IDC Write-down, Amortization Methods (AMOR, AMR2)

Debit	Credit	Amount
102 IDC Offset	34 Unamortized IDC Cost	After recalculation, the difference between the old and new beginning IDC amounts

Book IDC Write-down, Income Method (INCM)

Debit	Credit	Amount
33 Revenue	7 Unearned Income	After recalculation, the difference between the old and new beginning IDC amounts

Tax IDC Write-up, Write-down
LeasePak does not make general ledger entries for Tax IDC.


IDC2 Notary Fee Write-up

Debit	Credit	Amount
173 Unamortized Notary Fee	73 Notary Fee Payable	After recalculation, the difference between the old and new beginning IDC2 amounts

IDC2 Notary Fee Disbursal
(Only applicable if disbursal Y/N flag is 'Y')

Debit	Credit	Amount
73 Notary Fee Payable	126 Cash—Default Disbursal (or other cash account as selected in U0212 Miscellaneous Customizations)	After recalculation, the difference between the new and old beginning IDC2 amounts

Batch IDC Adjustment Audit Report
For each successful adjustment from the batch input file, the utility will include a line on the Batch IDC Adjustment Audit report.

Report Title
Batch IDC Adjustment Audit


Report File Name
pnn_btchidc_a.rpt, where pnn is 'p' followed by the portfolio number.


Report File Location
The LeasePak server home directory of the user running the utility. The user can view and print this report interactively in LeasePak through R0929 Print/View User Reports.


Report Columns
The report includes the following columns:

Column Name	Description/Value
Lease	Lease number from input line
IDC Type	'Book', 'Tax', or 'IDC2'
Previous Beginning IDC	Beginning IDC amount before adjustment
New Beginning IDC	Beginning IDC amount after adjustment
Previous Unamortized/UE	Remaining unamortized/unearned amount before adjustment
New Unamortized/UE	Remaining unamortized/unearned amount after adjustment
Debit	Number and name of G/L account debited
Credit	Number and name of G/L account credited
G/L Change	Amount of debit/credit

Batch IDC Adjustment Exceptions Report
For each unsuccessful adjustment from the batch input file, the utility will include a line on the Batch IDC Adjustment Exceptions report.

Report Title
Batch IDC Adjustment Exceptions


Report File Name
pnn_btchidc_b.rpt, where pnn is 'p' followed by the portfolio number.


Report File Location
The LeasePak server home directory of the user running the utility. The user can view and print this report interactively in LeasePak through R0929 Print/View User Reports.


Report Columns
The report includes the following columns:

Column Name	Description/Value
Input Line	The full line from the input file
Exception Message	The exception message corresponding to the failed edit check