U04 End of Period
U0468 Dealer Reserve Monthly Extracts
| EOP Only | Yes |
| Frequency | Monthly |
| 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.
Update U0468 Dealer Reserve Monthly Extracts create monthly extract of dealer reserve payables and chargeback that runs as part of end of month. Update selects all the records from (rdh) Dealer Transaction
History table with a check type of DRES that have been created (disbursement date and disbursement time) since the last end of month (date of EOM completion and time of EOM completion). Update U0468 creates
two comma delimited files, one detail and one summary file, through sample XSL transformation. Also creates Dealer Statement report with the same information through a sample XSL transformation. NetSol provides
only sample files. NetSol will not maintain the XSL files to meet the future design changes. These XSL files are written to be processed using xsltproc. Other processors may or may not support function names used.
This updates creates a one-month end XML data file. This updates runs during end of the month after the batch booking, dealer reserve batch check disbursal, and U0117 Cashier's check disbursal have run.
NetSol provides the two XSL transformation files to the sponsoring clients to transform the XML output into two comma delimited text files. One files with detail information and one whit the summary information.
NetSol also provides another XSL transformation file to the sponsoring client to transform the XML output into a month end report.
Extract
Update U0468 produces an XML data file with the data from the following fields. Each field will be an element in the XML data file using the name or other identified name as the element name.
| Description | Table | Field |
| Disbursement date | RDH | d_dis_s |
| Lease | RDH | lse_s |
| Asset | RDH | unit_s |
| Transaction amount | RDH | tx_d |
| Transaction reason code | RDH | trn_rsn_s |
| Transaction reversal reason code | RDH | rev_trn_rsn_s |
| Vendor | RDH | dlr_s |
| Original acquisition cost | REQ | org_d |
| Residual amount | REQ | res_d |
| New/used (N/U) | REQ | new_c |
| 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 |
| Transaction reason description | RCC | cd_long_s |
| Transaction reversal reason description | RCC | cd_long_s as rev_cd_long_s |
| Current date | today's date as current _date | |
| RHA | count of RHA records as paid_invoices |
The selection of data for extract will apply the following conditions to determine which RDH records to include in the selection:
- Current running portfolio must equal the dealer transaction history's portfolio number (rdh portfolio)
- Select only RDH records for dealer reserve checks (dealer transaction history-> transaction type = "DRES")
- Select only RDH records that have disbursed since the last end of month (disbursement date and disbursement time are greater than date of EOM completion and time of EOM completion).
The selection process will use the selection of dealer transaction history records from the RDH table as its base set of records and then joins the other tables to collect the additional information using the following criteria:
- An RDH record matches only one REQ record where (rdh.unit_s) Asset = (req.unit_s) Asset
- An RDH record matches only one RLS record where (rdh.lse_s) Lease = (rls.lse_s) Lease Number
- An RDH record matches only one RCX record where (rdh.dlr_s) vendor = (rcx.client_id_s and rcx.client_rel_s = 'VND') Client and Client Relation 'VND'
- If (rdh.trn_rsn_s) transaction reason code is not blank, it will match only one RCC record where (rcc.cd_type_s = 'TRN.RSN' and rcc.cd_shrt_s = rdh.trn_rsn_s) 'TRNS.RSN' & custom code flag = custom/LP code short symbol = transaction type to return (cd_long_s) custom LP code long name as the transaction reason description.
- If (rdh.rev_trn_rsn_s) transaction reversal reason code is not blank, it will match only one RCC record where (rcc.cd_type_s = 'REV.TR.RSN' and rcc.cd_shrt_s = rdh.rev_trn_rsn_s) custom/LP code type = 'REV.TR.RSN' & custom/LP code short symbol to return (cd_long_s, rev_cd_long_s) transaction reversal reason description as the transaction reversal reason description.
The extract will counts the number of RHA records where (rls.por_s=rha.por_s, rls.com_s=rha.com_s,rls.reg_s=rha.req_s, rls.off_s=rha.off_s, rls.lse_s=rha.lse_s, and rha.ar_typ_s='I') lse portfolio = rha portfolio, rls company = rha company, rls region = rha region, rls office = rha office, rls lease = rha lease, and rha A/R type flag = 'I'.
LeasePak names this XML extract file as p#_lpu0468.xml
Where p represents the portfolio and # present the portfolio number. The XML can be located at the EOP directory. The XML data file contains the dealer reserve checks created or reversed since the last EOM runs. Dealer reserve checks created or reversed in the past can be found in the RDH table. The disbursement date and time are set to the date and time that either the check was wad cut or voided and therefore indicates that the date and time record has been created in RDH table. The last EOM run completion date and time can be found in the RAS table as disbursement date and disbursement time. The RDH records that represent dealer reserve checks can be indicated by transaction type of "DRES".
Sample XSLT Template
User needs to create two XSL templates to transform the resulting data file into two comma delimited data files. Two sample XSL templates will be created to transform the resulting data into two
comma-delimited files. These comma delimited files will consist of a detail and summary reports of data for any dealer reserve checks created or reversed since the last end of month runs. Some of the
information on the report depends whether the considered contract a lease or a loan. A loan is any contract with an accrual method of '*AF*', '*AX*', '*IF*', or '*IX*'. All other considered contracts will
be leases.
The 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 | Vendor (rdh.dlr_s) Truncate blanks and pad with leading zerosup to 5 characters. If longer than 5 characters drop extra characters from the left side. Ex: dlr_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 | rev_cd_long_s if it's not blank, otherwise cd_long_s |
| 8 | YYYYMMDD | 8 | Disbursement date (edh.d_dis_s) |
| 9 | Text | 25 | rcx.client_s_name_s |
| 10 | Text | 20 | Lease (rdh.lse_s) + Asset (rdh.unit_s) padded with blanks |
| 11 | Numeric | 8, 2 | Original acquisition cost (req.org_d)* |
| 12 | Numeric | 8, 2 | Transaction amount (rdh.tx_d) |
| 13 | Numeric | 10 | 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 - req.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) else lease - "" |
| 21 | Text | 1 | If loan - New/Used (req.new_c) else lease - "" |
| 22 | Text | 15 | If loan - Dealer reserve promotion number (reqa.dlr_res_promo_s) else lease - "" |
| 23 | Text | 4 | If loan - Dealer reserve plan code (reqa.idc7_pan_s) else lease - "" |
| 24 | Numeric | 3 | If loan - Numberof 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 detail file uses 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 | Vendor (rdh.dlr_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: dlr_s= 52 Report as "00052" |
| 4 | Blank | 0 | "" |
| 5 | YYYYMM | 6 | Disbursement date (rdh.d_dis_s) year and month only |
| 6 | Numeric | 8,2 | Transaction amount (rdh.tx_d)* |
LeasePak names this XML extract file as p#_lpu0468.xml
Where p represents the portfolio and # present the portfolio number. The detail file will result in an a.dat and the end of the summary file is a b.dat to indicate a report and b
report respectively. These will be data files. LeasePak will place these files in the EOP directory. * Dollar amounts such, as $2,345.67 will be reported as + 00002345.67 where the first character indicates positive or negative ** Percentages such as 1.25% will be reported as 1.25000 when in a loan and as 0.01250 when on a lease.
Note: ***No + or - sign
Monthly Statement Sample XSL Template
Update U0468 provides a sample XSL template to transform the resulting data file into a monthly statement. The dealer reserve monthly statement will use the same monthly data extract.
This sample file includes all RDH records since the last end of month for dealer reserve check types and the given portfolio. The dealer reserve statement filed will consist of the following:
| Field | Header | Format | Max Width | Value |
| 1 | Dealer | Text | 9 | Vendor (rdh.dlr_s) |
| 2 | Account Number | Text | 20 | Lease (rdh.lse_s)+ Asset (rdh.unit_s) each right justified and padded with blanks |
| 3 | Book Date | Date(MMDDYY) | 6 | Booking date (rls_d_boo_s) |
| 4 | Trans Date | Date(MMDDYY) | 6 | Disbursement date (rdh.d_dis_s) |
| 5 | Customer Name | Text | 25 | Rcx.client_s_name_s |
| 6 | Amount Financed | Numeric(#,###,###.00) | 12 | Original acquisition cost (req.org_d) if prior record is for the same Account Number, then this should be blank so that the same asset's amount is not repeated. |
| 7 | Residual Amount | Numeric(#,###,###.00) | 12 | Req.res_d if prior record is for the same Account Number, then this should be blank so that the same asset's amount does not get repeated. |
| 8 | Term | Numeric(###) | 3 | Lease term (rls.trm_l) |
| 9 | Tier Code | Text | 4 | Credit quality (rlsa.cred_quality_s) |
| 10 | Promo number | Text | 15 | Dealer reserve promotion number (reqa.p_dlr_res_promo_s) |
| 11 | Buy Rate | Numeric(##.00000) | 8 | Dealer reserve buy rate (reqa.p_dlr_res_buy_rate_d) [divide by 100 when on a lease] |
| 12 | Pgm Rate | Numeric(##.00000) | 8 | Dealer reserve program rate (reqa.p_dlr_res_prog_rate_d) [divide by 100 when on a lease] |
| 13 | Cust Rate | Numeric(##.00000) | 8 | IRR (rls.p_irr_d) [divide by 100 when on a lease] |
| 14 | Mark Up | Numeric(##.00000) | 8 | If lease - (field 12- field 12)/100 if loan - when field 12 > 0 then (field 13 - field 12) else (field 12 - field 11) |
| 15 | Plan Code | Text | 4 | Dealer reserve plan code (reqa.idc7_pan_s) |
| 16 | Book Reason | Text | 20 | If rdh.rev_trn_rsn_s is not blank, then rcc.cd_type_s where rcc.cd_shrt_s = rdh.rev_trn_rsn_s and rcc.cd_type_s = "REV.TR.RSN" else rcc.cd_type_s where rcc.cd_shrt_s = rdh.trn_rsn_s and rcc.cd_type_s = "TRN.RSN" |
| 17 | # of Pmt | Numeric (###) | 3 | Number of payments received iin full (count rha) |
| 18 | Pmt Amt | Numeric (###,###.00) | 10 | Transaction amount (rdh.tx_d if > 0 else 0) |
| 19 | Reversal | Numeric (###,###.00) | 10 | Transaction amount (rdh.tx_d if < 0 else 0) |
| 20 | Due to Dealer | Numeric (###,###.00) | 10 | Transaction amount (rdh.tx_d) |
The above mentioned all text fields are left justified unless otherwise noted and numeric fields are right justified. The monthly statement will take the data and sort it by Dealer Number, Promo Number, Account Number, and Transaction Date. The process will subtotal the following when the change of Dealer Number and Promo Number combination occurs:
- Amount Financed
- Residual Amount
- Pmt Amt
- Reversal
- Due to Dealer
LeasePak place the output file in a report file in the EOP directory on the server. LeasePak names the file p#_lpu0468.rpt. Where p represents portfolio and # represents the portfolio number. Each record will take up to two lines on the statement report.
Reference Guide |
Table of Contents
© 2025 NETSOL Technologies. All rights reserved.
The information contained in this document is the sole property of NETSOL Technologies, Inc. Use of the information contained herein is restricted. Conditions of use are subject to change without notice. NETSOL Technologies, Inc. assumes no liability for any inaccuracy that may appear in this document nor does it have the obligation to update any of the information contained herein; the contents of this document do not constitute a promise or warranty. The software described in this document is furnished under license and may be used or copied only in accordance with the terms of said license. Unauthorized use, alteration, or reproduction of this document without the written consent of NETSOL Technologies, Inc. is prohibited.
The information contained in this document is the sole property of NETSOL Technologies, Inc. Use of the information contained herein is restricted. Conditions of use are subject to change without notice. NETSOL Technologies, Inc. assumes no liability for any inaccuracy that may appear in this document nor does it have the obligation to update any of the information contained herein; the contents of this document do not constitute a promise or warranty. The software described in this document is furnished under license and may be used or copied only in accordance with the terms of said license. Unauthorized use, alteration, or reproduction of this document without the written consent of NETSOL Technologies, Inc. is prohibited.