U04 End of Period

U0467 Dealer Reserve Daily Extract

EOP Only	Yes
Frequency	Daily
Sequential Updates	No
Skip Notes

For more information about End of Period,
refer to U04 End of Period Overview.

 

Enhanced IDC/IDR: This EOP process requires the Enhanced IDC/IDR optional license module in order to use Dealer Reserve. For more information on Dealer Reserve, refer to the Dealer Reserve overview.

 

U0467 Dealer Reserve Daily Extracts update can be set to run as part of the Day End. This module creates an XML data file of any outstanding dealer reserve payables that exist after the batch booking process completes during end of process. Users must select any outstanding dealer reserve payables to include in the extract. Outstanding dealer reserve payables are the dealer reserve payables payable amount IDC7, when it is greater than zero on assets. This extract provides two XSL transformation files to transform the XML output into two comma-delimited text files, one with detailed information report, and one summary information report.

The below mentioned XSL files are sample files. NetSol will not maintain the XSL files to meet future design changes. These files are written to be processed using xsltproc. Other processors may or may not support function names used.

Extracts Format:

U0467 update produces an XML data file with data from the following fields. Each filed will be an element in the XML data file using the field or other identified name as the element name.

Fields are as follows with their descriptions:

Desription	Table	Field
Asset	REQ	unit_s
Lease	REQ	lse_s
Original acquisition cost	REQ	org_d
Residual amount	REQ	res_d
New/used (N/U)	REQ	new_c
Payable to IDC7	REQA	idc7_pay_to_s
Payable amount IDC7	REQA	idc7_ap_d
Dealer reserve buy rate	REQA	p_dlr_res_buy_rate_d
Dealer reserve program rate	REQA	p_dlr_res_prog_rate_d
Dealer reserve promotion number IDc7	REQA	dlr_res_promo_s
Dealer reserve plan code IDC7	REQA	idc7_dlr_plan_s
Accrual method lessor	RLS	acc_l_s
System booking date	RLS	d_boo_s
Maturity date	RLS	d_mat_s
IRR (yield)	RLS	p_irr_d
Lease term	RLS	trm_l
Credit quality	RLSA	cred_quality_s
Client short name	RCX	client_s_name_s
Current date		Today’s date as current_date
RHA		Count of RHA records as paid_invoices

The selection criterion is different and depends upon the requirements. The selection criteria of the data apply the following conditions to determine which REQ records to include in the selection:

• Current running portfolio must equal to the asset’s portfolio number.
• Select all assets that have a payable amount IDC7 greater than zero.

The selection process will use the selection of asset records from the (REQ) Asset table as its base set of records and then join to other tables to collect the additional information using the following criteria:

• An REQ record matches only on RLS record where (req) Lease = (rls) Lease.
• An REQ record matches only one RCX record where (reqa) Payable to IDC7 = (rcx) client Search Client.

LeasePak names this extract as p#_lpu0467.xml

Where p represents the portfolio and # represent the portfolio number. LeasePak places this XML file in the EOP directory. This update runs during the process of end of the day once batch processing completes. This update runs in any case at the end of the month, either the Dealer Reserve Batch check Disbursal switch is on or off or the Dealer Reserve Batch Check Disbursal runs or not.

XSL Templates

This update creates two XSL templates to transform the resulting data file into two comma delimited data files. LeasePak reports some of the information that will depend on whether the contract is ‘*AF*’, ‘*AX*’, ‘*IF*’, or ‘*IX*'. All other contracts are considered leases.

Following is the sample XSL templates detail file will contain the following fields:

Field	Format	Max Width	Value
1	Text	3	“NIS”
2	Text	7	If loan – “LPLOAN” else lease – “LPLEASE”
3	Text	5	Payable to IDC7 (req.idc7_pay_to_s). Truncate blanks and pad with leading zeros up to 5 characters. If longer than 5 characters drop extra characters from the left side. Ex: idc7_pay_to_s=52 Report as “00052”
4	Blank	0	“”
5	YYYYMM	6	Current year and month only
6	Text	15	Incremental number starting with 000000000000001
7	Text	20	If rls.d_boo_s is in the current month and year, then “NEW LEASE” or “NEW LOAN” depending on accrual type, otherwise “REBOOK”
8	YYYYMMDD	8	System booking date (rls.d_boo_s) if it is in the current month and year otherwise use the last day of the current month.
9	Text	25	Rcx.client_s_name_s
10	Text	20	Lease (req.lse_s) _ Asset (req.unit_s) padded with blanks
11	Numeric	8,2	Original acquisition cost (req.org_d)*
12	Numeric	8,2	Payable amount IDC7 (reqa.idc7_ap_d)*
13	Numeric	3	Lease term (rls.trm_l)***
14	Numeric	3,5	Dealer reserve buy rate (reqa.p_dlr_res_buy_rate_d)**
15	Numeric	3,5	Dealer reserve program rate (reqa.p_dlr_res_prog_rate_d)**
16	Numeric	3,5	IRR (rls.p_irr_d)**
17	Numeric	3,5	If loan – when field 15 > 0 then (field 16 - field 15) else (field 16 - field 14)** else lease – (field 16 - field 15)**
18	Numeric	8,2	If loan – “” else lease – re.res_d*
19	Numeric	3	If loan – “” else lease – Months to maturity (months (rls.d_mat_s – today))***
20	Text	4	If loan – credit quality (rlsa.cred_quality_s)
21	Text	1	If loan – New/Used (req.new_c) else lease – “”
22	Text	15	If loan – Dealer reserve plan promotion number (reqa.dlr_res_promo_s) else lease – “”
23	Text	4	If loan – Dealer reserve plan code (reqa.idc7_plan_s) else lease – “”
24	Numeric	3	If loan – Number of payments received in full (count rha)*** else lease – “”
25	YYYYMMDD	8	System booking date (rls.d_boo_s)

The summary file will take the same data as sued for detail file and group by the first five detail fields while totaling detail field 12. The summary file will contain the following fields:

Field	Format	Max Width	Value
1	Text	3	“NIS”
2	Text	7	If loan – “LPLOAN” else lease – “LPLEASE”
3	Text	5	Payable to IDC7 (req.idc7_pay_to_s). Truncate blanks and pad with leading zeros up to 5 characters. If longer than 5 characters drop extra characters from the left side. Ex: idc7_pay_to_s=52 Report as “00052”
4	Blank	0	“”
5	YYYYMM	6	Current year and month only
6	Text	8,2	Total payable amount IDC7 (sum(req.idc7_ap_d))*

LeasePak names this extract as p#_lpu0467.xml

Where p represents the portfolio and # present the portfolio number. The end of the detail file output will be an a.dat and the end of the summary output file will be a b.dat to indicate version a and b version respectively. These resultant output files are the data files. LeasePak places these .dat files in the EOP directory.

Example:

Following is an example of one line from a detail file.

NIS,LPLOAN,00120,,201104,000000000000156,NEW LOAN ,20110401,HAYNES ALISA J ,00102442333410001,+00022390.87,+00001185.30,072,.00046,.00046,.00046,.00000,,,TR0,U,11688,BY,000