113 Batch General Ledger Adjustment

LeasePak Utilities

113 Batch General Ledger Adjustment

Information note: Customer must own the Batch General Ledger Adjustment module to run this utility.

This utility allows users to submit a file to process U0121 General Ledger Adjustement transactions in batch. The program provides the option to preload a batch file, checks for existing records before preloading, and allows users to delete existing records as needed. It will preload the batch file into the LeasePak database rbg Batch General Ledger Adjustment and rbga Batch General Ledger Adjustment Detail tables. If records were not loaded properly or an error occurs, the utility will generate the Preload Exception Report detailing records not loaded. The utility processes the records in the RBG and RBGA tables and generates an audit and exception report of processed records.

Input File

Users must first create an input file named btchglaj.dat and place it in the $udata directory before running the utility. If the file already exists, the user running the utility must have read and write access to the file.

Records in the input file consist of comma-delimited data elments, each preceded by an identifying prefix:

L45,TAuction expense,CR5399,A122,DR5399,A125
P2,C1,R1,O2,TSplit cash accounts,CR10000,A125,DR7500,A127,DR2500,A128
L20,CR2750,A125,DR2750,A126

Prefix	Name	Description
P	Portfolio	Portfolio of general ledger office to receive adjustment
C	Company	Company of general ledger office to receive adjustment
R	Region	Region of general ledger office to receive adjustment
O	Office	General ledger office to receive adjustment
L	Lease	Number of lease to receive adjustment
T	Comment	Optional comment to place on transaction. Maximum length: 36 characters, no commas. Comment will appear on transaction if customer owns JULE Fund 1996 and has U0212 Portfolio – Miscellaneous Customizations switch Add Comment to Transaction set to 'Y'.
DR	Debit amount	Amount to debit. An element specifying the account (A) to debit must follow. Format the dollar amount with cents included, but without decimal points, thousands separators, or negative signs.
CR	Credit amount	Amount to credit. An element specifying the account (A) to credit must follow. Format the dollar amount with cents included, but without decimal points, thousands separators, or negative signs.
A	Account	Number of general ledger account to debit or credit. A corresponding account element must follow each debit (DR) and credit (CR) amount.

Each record in the file must begin with one of the following, either a series of Portfolio, Company, Region, Office elements or a Lease number element:

P2,C1,R1,O2,TSplit cash accounts,CR10000,A125,DR7500,A127,DR2500,A128
L45,TAuction expense,CR5399,A122,DR5399,A125

PCRO	or	Lease
Portfolio (P) Company (C) Region (R) Office (O)		Lease (L)

Next, each record can contain a Comment, which is an optional element:

L45,TAuction expense,CR5399,A122,DR5399,A125

Comment
Comment (T)

Finally, each record must contain at least one debit (DR,A) group and at least one credit (CR,A) group. The debit group consists of a debit amount (DR) and corresponding Account number. The credit group consists of a credit amount (CR) and corresponding Account number. Each record can contain multiple debit and credit groups. As long as each debit or credit amount is immediately followed by its corresponding account number, debit and credit groups can occur in any order:

L45,TAuction expense,CR5399,A122,DR5399,A125
P2,C1,R1,O2,TSplit cash accounts,CR10000,A125,DR7500,A127,DR2500,A128
L20,CR2750,A125,DR2750,A126

Debit Group	and	Credit Group
Debit Amount (DR) Account (A)		Credit Amount (CR) Account (A)

Information note: The number of debit groups in a given record does not have to equal the number of credit groups in that same record, but the total debit amount of the record must equal the total credit amount for that record, meaning that each record must balance within itself.

Format debit and credit dollar amounts with cents included, but without decimal points, thousands separators, or negative signs:

Dollar Amount	Format As
$53.99 $100.00 $5,000.00 $42.75 $1,260.50	5399 10000 500000 4275 126050

Here again are the 3 sample records:

L45,TAuction expense,CR5399,A122,DR5399,A125
P2,C1,R1,O2,TSplit cash accounts,CR10000,A125,DR7500,A127,DR2500,A128
L20,CR2750,A125,DR2750,A126

The first record applies the adjustment to Lease 45; it includes the comment "Auction expense", applies a credit of $53.99 to Account 122, and applies a debit of $53.99 to Account 125. As required, the total debits match the total credits.

The second record applies the adjustment to the accounting unit Portfolio 2, Company 1, Region 1, Office 2; it includes the comment "Split cash accounts", applies a credit of $100.00 to Account 125, applies a debit of $75.00 to Account 127, and applies another debit of $25.00 to Account 128. The total debit amount ($75.00 + $25.00) equals the total credit amount ($100.00).

The third record applies the adjustment to Lease 20; it does not include a comment, applies a credit of $27.50 to Account 125, and applies a debit of $27.50 to Account 126. Again, the debit and credit totals match.

User Interface

From the LeasePak Utilities main menu, enter '113':

This utility preloads a general ledger adjustment batch file from the $udata directory into the LeasePak RBG 
and RBGA tables and processes the records from the LeasePak RBG and RBGA tables.

Do you wish to continue (Y/N)?

Enter 'Y' to continue. The program displays:

Client Password?

Enter your LeasePak client string password to proceed. The program displays:

Do you wish to preload a batch file [Y/N],  <RETURN> to exit?

Enter 'Y' to proceed. The system will check if any header record exists in the rbg Batch General Ledger Adjustment table. If the header record still exists, it means that the preload ended abnormally. The programs displays:

E R R O R – read carefully:

The prior input file was in the middle of being preloaded when an error occurred. The input file must be manually 
modified to remove entries that have already been preloaded. Failure to do so will cause those records to be 
processed twice. Do not continue here until you have modified the input file. Do you want to continue [Y/N]?

Enter 'Y' to proceed. The program displays:

Records already exist. Do you want to delete these records before loading more [Y/N], <RETURN> to exit:

Enter 'Y' to proceed. The system will delete all the records from RBG & RBGA tables. The program willl display:

Loading batch general ledger adjustment file (path and name of file)
One moment please . . .

While running this preload process, the utility will first write a header record if header record does not exist. System will delete the header record once preload completes successfully. A header record will have blanks in all fields except the run date and run time. The preload process will continue running. The utility will read in the batch file, do the preliminary edit checks, and load data into the leasePak tables RBG and RBGA. If exception encountered, then it will write the exception to the preload exception report about the group of adjustments skipped and not written to the RBG and RBGA tables. The system will continue processing with next records found until all records have been processed. The utility will overwrite the exception report, if the exception report already exists. After processing batch file, the utility will delete the batch file; delete the header record, and displays:

Completed.
Do you wish to process loaded records [Y/N], <RETURN> to exit?

Enter Y to proceed. The program displays:

Processing batch general ledger adjustments, one moment please . . .

Once process is done system displays:

Completed.

When processing, the utility will check all the records found in LeasePak RBG and RBGA tables and also deletes the records once processing is done, whether successful or with exceptions. The utility reads records in order of the rbg_idx0 index. The utility will process each record through U0121. Any edit checks that would have been done through the U0121 when data is entered manually will be done by the utility and will write the exceptions to the General Ledger Adjustment Exception Report. If an exception occurs, the system will write the exception, will not perform adjustment for that record, and moves to the next record.

Whereas, processing each record in the batch file, utility will check the following data before writing data to RBG and RBGA tables:

The PCRO or lease number is present and exists in the LeasePak database.

The total amount of debits equals the total amount of credits.

The values of the columns in RBG table will be determined as follows:

Column	Value
por_s	The P element if present or portfolio lease resides in if not present.
com_s	The C element if present or company lease resides in if not present.
reg_s	The R element if6 present or region lease resides in if not present.
off_s	The O element if present or office lease resides in if not present.
lse_s	The L element if present or blank
d_run_s	Date preload is run
t_run_s	Time preload is run
unique_s	Sequential number starting with 1 and increasing by 1 for each additional record.
timestamp	Timestamp
comment_s	The T element if present or blank

The values of the columns in the RBGA table will be determined as follows:

Column	Value
por_s	Same as RBG
com_s	Same as RBG
reg_s	Same as RBG
off_s	Same as RBG
lse_s	Same as RBG
d_run_s	Same as RBG
t_run_s	Same as RBG
unique_s	Same as RBG
unique_s	Same as RBG
unique_l	Sequential number starting with 1 and increasing by 1 for each additional record up to a maximum of 20.
timestamp	timestamp
gl_type_c	‘C’ or ‘D’ based on the prefix sent with the dollar amount element
gl_vector_l	The A element
chg_d	The value of the CR or DR element

Here is an example of how the batch file will look in the RBG table:

por_s	com_s	reg_s	off_s	lse_s	d_run_s	t_run_s	unique_s	timestamp	comment_s
2	1	1	1	45	Feb 4 2010	102631	1	0x00000000000d7b1c	Auction expense
2	1	1	2		Feb 4 2010	102631	2	0x00000000000d7b18	Split cash accounts
2	1	1	1	20	Feb 4 2010	102631	3	0x00000000000d5489	-

And in the RBGA table:

por_s	com_s	reg_s	off_s	lse_s	d_run_s	t_run_s	unique_s	unique_l	timestamp	gl_type_c	gl_vector_l	chg_d
2	1	1	1	45	Feb 4 2010	102631	1	1	0x00000000000a6098	D	122	53.99
2	1	1	1	45	Feb 4 2010	102631	1	2	0x000000000007636a	C	125	53.99
2	1	1	1		Feb 4 2010	102631	2	1	0x00000000000a9e65	C	125	100.00
2	1	1	2		Feb 4 2010	102631	2	2	0x00000000000d3c94	D	127	75.00
2	1	1	2		Feb 4 2010	102631	2	3	0x00000000000a6098	D	128	25.00
2	1	1	1	20	Feb 4 2010	102631	3	1	0x00000000000c8077	C	125	27.50
2	1	1	1	20	Feb 4 2010	102631	3	2	0x00000000000c71ee	D	126	27.50

Output Reports:

LeasePak will generate the Preload Exception Report when the batch input file processing ends abnormally. On failure LeasePak will create the Exception Report else it will generate the Audit Report in case of successful completion.

Preload Exception Report:

The utility will create a preload exception report in the directory from which the utility is run when the preload option is selected. The utility will name the preload exception report plglajexcpt.rpt. If file already exist with same name then, LeasePak will overwrite the existing file and replace it with the new report when processing preload process. The report will contain records from the batch file that have exceptions, exactly the way they appeared in the batch file along with an error message.

The report will include the following information in the report header:

Run date: Current date when running preload process.
Run time: Will show the time when started loading batch file for processing.
User ID: LeasePak user Id.
LeasePak version: Version of LeasePak in which the utility is running.
Title: BATCH GENERAL LEDGER ADJUSTMENT PRELOAD EXCEPTIONS: Title of the report or report header.

The report detail will have the following column headings:

Batch Record: The batch record will contain the actual record text of the record rejected.
*Message: The message will contain an error message preceded by and (*) asterisk.

Below is the sample preload exception report:

Exception Report:

The utility will create the general ledger adjustment exceptions report in the directory from which the utility is run when the “process loaded records” option is selected. The utility will name the exceptions report btchglaj_excpt.rpt. If a file with the same name exists, the program will overwrite the file and replace it with new report. The exceptions report lists all the exceptions encountered while processing preloaded records.
The report will include the following information in the header:

Run date: Day of running utility.
Run time: Utility start time.
User ID: LeasePak user Id.
LeasePak version: LeasePak current version.
Title: BATCH GENERAL LEDGER ADJUSTMENT EXCEPTIONS: Hearer of the report presenting exceptions report.

The report detail will have the following column headings:

PCRO: Portfolio, company, region and office number.
Lease: lease number.
Message: Message displays the error message.
Comment:
Account: Default account number followed by either credit amount or debit. Lists multiple lines of information providing detail of the given adjustment that has an exception.
Debit: Lists multiple lines of information providing detail of the given adjustment that has an exception.
Credit: Lists multiple lines of information providing detail of the given adjustment that has an exception.

Following is the sample exception report:

Audit Report:

The utility generates a general ledger adjustment audit report in the directory from which the utility is run when the “process loaded records” option is selected. The utility will name the audit report btchglaj_audit.rpt. If a file with the same name already exists, the utility will overwrite the file and replace it with the new report content. The audit report will lists all successfully processed records when trying to process preloaded records.
The report will include the following in the header:

Run date: Day of running utility.
Run time: Utility start time.
User ID: LeasePak user Id
LeasePak version: LeasePak current version
Title: BATCH GENERAL LEDGER ADJUSTMENT AUDIT:

The report detail will have the following column headings:

PCRO: Portfolio, company, region and office number.
Lease: Lease number.
Comment:
Debit:
Credit:
Account:

Following is the sample audit report: