Map & Upload

Click here to see this page in full context

Loan Maintenance / Collateral Loan Upload / Map & Upload

Map & Upload

The Map & Upload page allows you to map and upload new collateral loan data. It also allows you to update the existing loan data.

In the Map and Upload page, the UDFs under Client level UDFs display the column number to which it is mapped to (this is based on the uploaded collateral loan excel file) .

Navigation: Loan Maintenance → Collateral Loan Upload → Map & Upload

Refer to the screenshot:
Map & Upload page

Note The UDF values as provided in the collateral loan excel file is updated in the UDFs in Basic Client Details page.

Fields and Descriptions

Fields	Descriptions
Mappings	Includes the list of mapped file names.
Parameter Name	Specifies collateral's loan transaction parameter name.
Delimiter Character	Specifies delimiter character automatically based on mappings.
Date Format	Specifies the date format. The default date format is M/D/Y. It also supports the date formats such as MM/DD/YYYY and MM-DD-YYYY.
No. Of Header Rows	Specifies the number of header rows to display.
No. Of Trailer Rows	Specifies the number of trailer rows to display.
Discard Rows Blank in Column	Specifies the position number of column having blank row to be discarded in the file you are mapping. Multiple column numbers can be inputted using Comma. For example, If you want to discard column 3 and column 4 which are having blank rows, you may input as 3, 4.The column 3 and 4 will be discarded in the file you are mapping.
Consider Rows Blank in Column	Specifies the position number of column having blank row to be considered in the file you are mapping irrespective of mapping the same row in Discard Value Text/Select Value Text fields. Multiple column numbers can be inputted using Comma. For example, If you want to consider column 3 and column 4 which are having blank rows, you may input as 3, 4.The column 3 and 4 will be considered in the file you are mapping.
Discard Value Column	Specifies the position number of column to be discarded in the file you are mapping. Multiple column numbers can be inputted using Comma. For example, If you want to discard column 1 and column 2, you may input as 1, 2.The column 1 and 2 will be discarded in the file you are mapping.
Discard Value Text	Specifies the text to be discarded in the file you are mapping, based on the value inputted in the Discard Value Column field. This field is enabled only after the value is inputted in Discard Value Column field. Multiple text can be inputted using Pipe “\|” symbol. For example, Discard Value Column is 4, Discard Value Text is LEGAL FEE and WIRE FEE. You may input the Discard Value Text as LEGAL FEE \| WIRE FEE. The rows having LEGAL FEE and WIRE FEE in the column number 4 will be discarded in the file you are mapping.
Select Value Column	Specifies the position number of column to be only uploaded in the file you are mapping. Multiple column numbers can be inputted using Comma. For example, If you want to upload only column 2 and column 3, you may input as 2, 3.The column 2 and 3 will only be uploaded in the file you are mapping.
Select Value Text	Specifies the text to be uploaded in the file you are mapping, based on the value inputted in the Select Value Column field. This field is enabled only after the value is inputted in Select Value Column field. Multiple text can be inputted using Pipe “\|” symbol. For example, Select Value Column is 2, Select Value Text is LEGAL FEE and WIRE FEE. You may input the Select Value Text as LEGAL FEE \| WIRE FEE. The rows having LEGAL FEE and WIRE FEE in the column number 4 will be only uploaded in the file you are mapping.
Client No.	Specifies the column position for the Client No. in the file you are mapping.
Loan ID	Specifies the column position for Loan ID in the file you are mapping. The default value is 0.
Activity Date	Specifies the column position for the Activity Date in the file you are mapping.
Charge Amount	Specifies the column position for the Charge Amount in the file you are mapping.
Charge Code Lookup?	Specifies the column position for the Charge Code Lookup? in the file you are mapping. Select any one of the following options: Select By Charge Name if you want to map the file by the by the charge name values in the file. Select By Transaction Code if you want to map the file by the by the transaction value mentioned in the file.
Charge Code	Specifies the column position for the charge code in the file you are mapping.
Charge Rate	Specifies the column position for the Charge Rate in the file you are mapping.
Credit Line	Specifies the column position for the Credit Line in the file you are mapping.
Expiry Date	Specifies the column position for the Expiry Date in the file you are mapping.
Risk Rating Code	Specifies the column position for the Risk Rating Code in the file you are mapping.
Cash Control Lookup?	Specifies the column position for the Cash Control Lookup? in the file you are mapping. Select any one of the following options: Select By Name if you want to map the file by the cash control name values in the file. Select By description if you want to map the file by the cash control description value mentioned in the file.
Cash Control	Specifies the column position for the cash control data in the file you are mapping.
Current Balance	Specifies the column position for the Current Balance in the file you are mapping.
Inactive Loan Column	Specifies the position number of column in the file you are mapping to make the loan status as Inactive. Upon uploading the file, the loan status needs to be updated as Inactive in Loan Set up page once the closing the balance becomes zero.
Inactive Loan Text	Specifies the text where the column contained text in the file you are mapping, to make the loan status as inactive. The Loan status should change from Active to Inactive in Loan Set up page only if the Loan Status column in the uploaded file or Inactive Loan Text values are matching with each other and if the closing balance becomes zero.
New UDF	It specifies the User Defined Field (UDFs) name. The Client Level UDFs section will appear only if any UDF is mapped to Collateral Loan Upload Program in the UDF Mapping page.

In the Map & Upload page, you can map the collateral loan file of a client by considering the following conditions:

When Charge Code Column is Selected or Discarded
Allows the user to to Select/Discard loan level transactions related to Charge Codes (transactions with other charge codes will not be included) and also client level transactions related to UDF, Cash Controls, LGD Grades etc. is updated in Basic Client Detail page. In both the cases, the entire row is selected or discarded on file upload.

Other Fields besides Charge Code is Selected or Discarded
Allows the user to Select/Discard other fields besides Charge Code. Transactions with other charge codes is Selected or Discarded.

Multiple Fields are Selected/Discarded
When multiple fields are Selected/Discarded, then it considers all the transaction from the Selected/Discarded fields and ignores the loan transactions only from unselected fields.
For example, when multiple fields are entered in Discard Value Column with comma separator, if entered field is loan transactions then only those transactions is eliminated and client level is uploaded, but if client level fields are entered then the entire row is eliminated.

Select and Discard are used simultaneously
If Select and Discard function is used together then the Select and Discard value works simultaneously on file upload.

Note Click on Info icon icon next to Discard Value Column. The following message displays.

If the column number is the same for Discard Value Column and Charge Code.

Then only Collateral Loan related columns will be discarded
All other columns will be used for updating parameters in Loan Setup and/or Basic Client Detail

If the column number is different for Select Value Column and Charge Code.

Then the entire row will be discarded

Refer to the screenshot:

Note Click on Info icon icon next to Select Value Column. The following message displays.

If the column number is the same for Select Value Column and Charge Code.

Then only Collateral Loan related columns will be used for ‘Select Value Column’
All other columns will be used for updating parameters in Loan Setup and/or Basic Client Details

If the column number is different for Select Value Column and Charge Code.

All rows matching to ‘Select Value Text’ will be used for Collateral Loans and updating parameters in Loan Setup and/or Basic Client Details
All rows mismatching to ‘Select Value Text’ will be discarded

Refer to the screenshot: Map and upload page

To map a file, perform these steps:

1. Go to Menu → File Uploads → Collateral Loan Upload → Map & Upload. Map & Upload page appears.

2. Click the New button button.

3. Enter field values under Details and Columns sections. A Collateral Loans to Upload section appears below.

Note In the Mapping field, the parameter name would be taken as mapping name. This field is auto-generated. You may also select the existing mapping in Mapping field from drop-down list.

4. In the File Format field, select the file type from drop-down list.

5. In Sheet No field, enter the sheet number. For .xls, .xlsx and .xlsm file formats, enter the number for the sheet being exported.

Note This field will be displayed only if the file format is selected as XLS/XLSX/XLSM in the File Format field.

6. In the Select file field, click Choose File and select the file. A message, "Selected file queued to upload collateral loan transactions." appears.

7. Click . The file is mapped and a message “Mapping was successfully created” appears.

Refer to the screenshot:

Once the file is uploaded successfully, the user can view the uploaded file data to the particular clients under Collateral Loans page.

When any specific number of column having blank row in the uploaded file is mapped in the Consider Rows Blank In Column field, the system will still consider the corresponding row from the uploaded file even if the same row is mapped in either Discard Value Column/Select Value Column fields so that the status of the specific loan gets changed into Inactive in Loan Set up page without any dependency of Closing/Principal Balance to be "0.00". But, the Credit Line, Expiry Date and Risk Rating Code will be updated based on the values provided in the uploaded file.

The Loan Paid Off date gets automatically updated with the processed date from Task & Upload History page of that uploaded file under Loan Set up page for the specific loan if the Activity date is blank in the uploaded file. The Loan Paid Off date gets automatically updated with the Activity date under Loan Set up page for the specific loan if the uploaded file has Activity date for the specific loan.

When the Inactivate Loan text provided in the Inactive Loan Text field does not match with Inactive text provided in the file, the corresponding loan cannot be inactivated under Loan Set up page, the exception task will be automatically generated in Task & Upload History page and there will not be any transactions for the respective loans in Collateral Loans page if there is no activity date in the uploaded file; but the credit line and expiry dates will be updated based on the values provided in the uploaded file.

If the Statement Balance Before Loan Balance flag is OFF under Basic Parameters page:

MCL Loans can be inactivated when the Inactivate Loan text provided in the Inactive Loan Text field matches with Inactive text provided in the file irrespective of the closing balance becomes zero or not, under Loan Set up page.
Loan Paid Off date will be updated with transaction date/uploaded date for MCL loans under Loan Set up page.

The Risk Rating Code value is shown is all management reports (BBC Executive Trend report, BBC Trend report, Collateral Analysis - Comparative report). Once you map the Risk Rating Code value, you need to update the same in the Basic Client Details page. If there is no value for Risk Rating code in the mapped file, the same is not displayed in the reports. If you undo the mapping in Task & Upload History page, all values are reverted in the Basic Client Details screen except the Risk Rating Code value.

If cash control data on database is matched with the uploaded cash control data, then the system will display the cash control data in Cash Controls field under Basic Client Details page in the following conditions and will not create any exceptional tasks in Task & Upload History page:

If there are 'n' number of transactions with same Client No. or same Loan No. and only one Cash Control cell value matches to that of Name/Description in Cash Controls of the client, then the cash control data will be displayed in Cash Controls field under Basic Client Details page.
If there are 'n' number of transactions with different Loan No. of the same client and only one Cash Control cell value matches to that of Name/Description in Cash Controls of the client, then the cash control data will be displayed in Cash Controls field under Basic Client Details page.
If there are 'n' number of transactions with uniquely different Client No. and the Cash Control cell value in each transaction are same or different but each of it matches with the Name/Description in Cash Controls of the client, then the cash control data will be displayed in Cash Controls field under Basic Client Details page.
If there are 'n' number of transactions with same Client No. or same Loan No. and all these transactions have the same Cash Control value that matches with Name/Description in Cash Controls of the client, then the system would display in Cash Controls field under Basic Client Details page.
If there are 'n' number of transactions with different Loan No. of the same client and all these transactions have the same Cash Control value that matches with Name/Description in Cash Controls of the client, then the system would display in Cash Controls field under Basic Client Details page.

Note When the collateral loan data uploaded mismatches with client's closing balance, the application sends an e-mail alert to the client along with the transaction date. Cync support receives a document which includes a mismatched client list. This e-mail is password protected and the same is used to view the details.

If user enters the duplicate mapping name in Parameter Name field, then the system shows an error notification.

Refer to the screenshot:
Validation - Mapping name already exists

If user enters the same column numbers in both Consider Rows Blank In Column field and Discard Rows Blank In Column field while creating new map file or editing the existing map file, then the system shows an error notification.

Refer to the screenshot:
If user inputs only Discard Value Column without inputting Discard Value text and clicks on the button , then the system shows an error notification.

Refer to the screenshot:If user inputs only Select Value Column without inputting Select Value text and clicks on the button, then the system shows an error notification.

Refer to the screenshot:
If user inputs only Inactive Loan Column without inputting Inactive Loan Text and clicks on the button, then the system shows an error notification.

Refer to the screenshot:
If user inputs only Inactive Loan Text without inputting Inactive Loan Column and clicks on the button, then the system shows an error notification.

Refer to the screenshot:

To edit the mapped file, perform these steps:

1. Go to Menu → File Uploads → Collateral Loan Upload → Map & Upload. Map & Upload page appears.

2. In the Mappings dropdown list, select the type of mapping you wish to edit. A Collateral Loans to upload section appears.

3. Click the Edit button button.

4. Edit the fields as required.

5. Click the button to cancel the map file editing process.

6. Click the button. The mapped file is updated and a message “Mapping was successfully updated” appears.

Note The system allows the user to save the UDF field with zero value (for the UDFs which are already mapped with Collateral Loan Upload).

Refer to the screenshot:

To delete the mapped file, perform these steps:

1. Go to Menu → File Uploads → Collateral Loan Upload → Map & Upload. Map & Upload page appears.

2. In the Mappings dropdown list, select the type of mapping you wish to delete. A Collateral Loans to upload section appears.

3. Click the button. A pop-up message “Are you sure you want to delete?” appears.

4. Click Ok. The file is deleted and a message “Mapping was successfully deleted” appears.

Refer to the screenshot: