IC/Host Functionality for ClientBase Windows and Trams Back Office

Some agencies today are choosing to change the way they make their bookings.  Many are dropping out of ARC/BSP and booking air tickets through a Host Agency.  They may also book some or their entire cruise and tour business with a Host Agency, or continue doing those bookings as they always have.  These Independent Contractors (IC) agents working through a Host Agency, may still operate a store front location, or move into a home-based environment.

The Host and IC need a method to transfer and process invoice data, and handle payments and commissions.  The following sections outline what steps both the Host and IC need to follow in Trams Back Office and ClientBase Windows.

Host Agency Setup & Procedures

Below are procedures the Host follows:

• Set up IC/Host code
• Transfer/Process data between the IC and Host
• Query IC items in Trams Back Office
• Handle payments between Host and IC
• Create a commission file for an IC who has Trams Back Office

Setup IC/Host Codes

The Host/IC functions in Trams Back Office and ClientBase Windows need to be activated.  If you are a Host Agency, IC Codes are setup, and a Host Code is setup by the IC.  With one or both of these codes setup, you are now ready to transmit or receive IC invoices.

Trams Back Office Setup of IC/Host Codes:  

In Trams Back Office, go to Utilities|Setup|IC/Host Codes.  As a Host Agency, create IC Codes for one or more IC's.

• Type: From the drop-down select IC.
• Code: Enter a free-flow IC code. 
• Description: Enter a free-flow description.
• Export Branch: The Export Branch represents the Branch Number in the IC’s or Host’s database.  When using the IC/Host Export Utility, the records to be exported and imported by either the IC or the Host can land in the appropriate branch for each database.
• Click Add to add the IC code to the data entry screen below, then OK to save.
• Note: The IC can set up the Host Code in Trams Back Office or ClientBase Windows if the IC has both products.  If the IC only has ClientBase Windows, then the Host Code must be entered there (see section below, IC Setup & Procedures). 

Transfer/Process Data between Host and IC 

Travel type names should be aligned between the Host and IC databases so they link to the correct travel type during the import/export routines.  The order within the Travel Type list has no consequences, just the travel type text.

Example:

Host has ship-deposit

IC has cruise-deposit

Suggestion:  The IC changes their travel type name to ship-deposit to align with the host.  The text needs to match exactly.

The HOST receives IC data in the Trams Back Office database by two methods.  

Method 1:  The Host receives IC data in Trams Back Office through GDS Interface of IC PNR’s created in Host GDS.  The IC identifies the PNR it creates with the IC Code (e.g., *ICPLANIT).  The *ICXXXX can be added to the AC line of the PNR, or as a remark line.  

The Host Agency does an interface download of the IC records.  Invoices can either be captured under a single profile (similar to a "house account") when IC alone is maintaining customer relationships and marketing; or invoices can be captured under multiple profiles if Host supports IC marketing efforts.  

The IC Code is captured for each invoice at the invoice level, and Host Code is captured at the booking level. 

Decide how the host will track the IC’s bookings.  

• Will they be under a single house account? If so, create that house account and assign an interface ID for it.  
• If not, ensure that both the host and IC understand the host’s policy for creating interface IDs (phone numbers or some other system).
• If the numbering system for interface IDs differs, note that as well.

Ensure that both the host and the IC are using the same vendor ID interface IDs and agent interface IDs.

Ticket and issue the invoice as usual.  When the host interfaces with the GDS, the PNR will come through normally with the addition of the IC’s code and the client’s marketing ID.

On a regular basis (daily, weekly, etc.), the host will use the IC/Host utility to create an export file for all of the IC’s invoices for that period.  The host would then email (or deliver some other way) the file to the IC.

The IC would import the file sent by the host.  The process is almost identical to interfacing from the GDS directly, except the user goes to Interface -> Import from File and selects the file the host sent.   The IC would then go to Interface -> Process as normal.

The interfaced records will include all of the bookings in the original record.  Supplier and Comm Track bookings come through as normal.  ARC/BSP bookings are converted to Supplier bookings since the IC will not be doing the ARC/BSP report.  All of the bookings will also be tagged with the host code.

Method 2:  The Host receives IC data in Trams Back Office through a file of ClientBase Windows  invoices created by the

IC using the Trams Export Utility (see section below on IC set up).  The IC identifies ClientBase Windows’ invoices with a Host Code when creating new reservations.  This Host Code is setup with the name of the Host, i.e. MAG.  The IC can export only Tour/Cruise bookings created in ClientBase Windows (OR) the IC can do a PNR Import of PNR’s created in the Host GDS and export Air/Car/Hotel bookings in addition to ClientBase Windows booked Tours and Cruises.  The IC then produces an export file by using the Trams Export Utility (see IC section below for creation of this file), and sends it to the Host.  Note:  The presence or absence of the Host Code in the reservation determines whether an invoice is included or excluded in the Trams Export Utility file sent to the Host.

Note:  To limit the possibility of duplicate invoices being created in the Host database, many Hosts desire to import Invoices/Bookings from each IC into a separate branch in the Host's Trams Back Office database.   

The Host interfaces the IC exported file into Trams Back Office by going to Interface|Import From File and processes the file like any other GDS interfaced records, by going to Interface|Process.  The Host receives a message that the files have been imported.  Invoices can either be captured under a single profile (similar to a "house account") when IC alone is maintaining customer relationships and marketing; or invoices can be captured under multiple profiles if Host supports IC marketing efforts.  

Note:  When new profiles are created as a .txt file using the Trams Export Utility, they are created the same as if interfaced from a GDS.  Fields captured in a new profile are:

*Name    *Address    *Interface ID   *Primary Phone Number *Primary Email Address

Host Sends Invoices/Bookings Created by the IC through the Host GDS

IC Has Trams Back Office:   The Host sends a file to the IC that DOES HAVE Trams Back Office, using the Trams Export Utility.  The Trams Export Utility and more information can be found here.  

Make sure to save this file to Program Files(x86)|Trams|Common Files.  

Database:  Select the database from the pull-down menu (default is Trams).

User Name/Password:  Enter User Name and Password for database, click Connect.

From/To Date:  Enter dates you want to create export file for.

Export Filename:  Enter the path to which to save your export file.  The example shows the file being saved in a "temp" folder, but you can create any folder to save the file to, or save it to the "desktop."  If you save it to the "temp" folder, make sure that you have a "temp" folder created on your computer or the export will error out when it can’t find the non-existent folder.  If you are exporting more than one file (to two different IC Agencies, for example), be careful about naming files, as the default gets overwritten each time a new file is generated.

Host to IC:  By default an IC code is defaulted, and make sure Trams Back Office Interface is the Export Format selected.  After the export file has been produced, email it to the IC.

Note:  

• All ARC/BSP bookings are converted to Supplier bookings.
• Only client credit card payment information is extracted. No partial payments or vendor payments are extracted.
• All exported invoice bookings are stamped with the Host code.

IC DOES NOT Have Trams Back Office, but Has ClientBase Windows Only:  The IC wants to capture Travel History from GDS bookings made with the Host. The Host sends a file to the IC that imports Travel History into ClientBase Windows.  The Trams Export Utility and more information can be found here.  

Make sure to save this file to Program Files(x86)|Trams|Common Files.  

Database:  Select the database from the pull-down menu (default is Trams).

User Name/Password:  Enter User Name and Password for database, click Connect.

From/To Date:  Enter dates you want to create export file for.

Export Filename:  Enter the path to which to save your export file.  The example shows the file being saved in a "temp" folder, but you can create any folder to save the file to, or save it to the "desktop."  If you save it to the "temp" folder, make sure that you have a "temp" folder created on your computer or the export will error out when it can’t find the non-existent folder.  If you are exporting more than one file (to two different IC Agencies, for example), be careful about naming files, as the default gets overwritten each time a new file is generated.

Host to IC:  By default an IC code is defaulted.  If you have multiple IC's that you are exporting records to, select the appropriate IC code from the drop-down window.  Make sure XML is the Export Format selected. 

After the export file has been produced, email it to the IC.

Automate the Host Export Utility using Windows Task Scheduler 

The Host export routine can be automated using the Windows Task Scheduler to create files for the IC import.  When setting up the scheduled task, add the command line parameters:

Run: Enter the TRAMS Interface Download command-line (here's an example): 

"C:\Program Files\Trams\Common Files\export.exe" -username SYSDBA -password masterkey -alias TRAMS -fromdate =1,-1 -todate =31,-1 -branchno 2 -filename c:\temp\abctravelexport.txt -direct –auto SAVEINVOICENO

The quotation marks around the export utility path and destination of the import.txt file are mandatory. Pay careful attention to the dashes and spaces for each of the command line parameters.

Command-line parameters for example above: 

"C:\Program Files\Trams\Common Files\export.exe" - Path to where the export.exe resides 

 -username - SYSDBA - User log in name 

-password  - User password 

-alias trams - Database Alias Name 

-encpwd – Encrypted password

-branchno  -  The appropriate ‘Target Branch’ that agency wants the invoices to be processed into

-filename – Path and filename for the export file, i.e. C:\Users\Username\Desktop\icexport.txt

-fromdate – The ‘From Date’ expressed as a date formula

-todate -  The ‘To Date’ expressed as a date formula

-hosttoic – Use Host to IC mode

-ictohost – Use IC to Host mode

-startinvoiceno – Specify a starting invoice number to export from

-saveinvoiceno - Manually run the Export utility the first time to specify the starting invoice number.

-direct  - the ‘Direct Export (No Data Mapping is Done)’ Option

-auto  -  The scheduled task will auto run based on the date and time entered during setup.

Important: All command line parameters after C:\Program Files\Trams\Common Files\export.exe must have a space- (dash) before the option and "C:\Program Files\Trams\Tramswin\download.exe" must have the quotes around it. 

Note: The Export Utility will correctly pass all the individual profiles with Name, Address, Interface ID and the Primary Phone and E-Mail.

To schedule for yesterday’s bookings:  fromdate    =-1,0,0                                                      

                                                              todate        =-1,0,0

To schedule for last week’s bookings:   fromdate    =MON-1

                                                              todate        =SUN

Other Examples of Date Formulas

=1,0,0	1st day of this month
=31,0,0	Last day of this month
=1,1,0	1st day of this year
=31,12,0	Last day of this year
=+7,0,0	Seven days from now
=Mon	Last Monday (will show today if today is Monday)
=Mon+1	Next Monday
=Mon-1	A week from last Monday
=0,+3,0	Three months from now
=0,-6,0	Six months ago
=0,0,+1	One year from now
=0,0,0	Today

**Make sure your computer is on when the process is scheduled and the date and time are correct.

Additional information on setting up scheduled tasks using the Windows Task Scheduler can be found here at www.microsoft.com

Ensure the import.txt file is created in a location where the recipient will be able to download it.

Query IC Items in Trams Back Office 

To query for invoices coded for the IC, go to the Invoice Query screen and use the IC Code field as selection criteria to find invoices.  To find Agent Adjustment items, go to the Agent Adjustment query in Trams Back Office and use the IC Code field as selection criteria to find adjustments. 

Note:  Report Generator reports can also be run based on an IC Code selection.

Handling Payments between Host and I/C

Host Receipts from the IC:  Depending on the payment agreement, the IC sends either a net check or a gross check to the Host for open cash/check invoices created by the IC.  The Host reconciles Trams Back Office with payments made/received to clear out client and vendor balances, and settle generated commissions, and/or service fees.   

Host Receives NET CHECK for Open Cash/Check Invoices from IC - Payment #1:

• After the Host receives check from the IC, the Host goes to Payment|Received.  
• Query for Cash/Check invoices with the Client Open (IC collect payments from customer).
• Select the Profile Type of Client and enter the name of the IC Agency as the Profile Name.

• Select the IC Code, and check All Clients on the Client Settings tab.
• Enter the amount of the check which is for the Net Amount on Cash/Check sales. Since the check due the Host is for the Net Amount excluding any commission, the check is probably less than the amount of the Net Due items being closed with the payment.  If the check is less than the amount open for cash/check invoices, create a voucher for this payment and do not close any invoices (see Payment #2 below).

Host Receives NET CHECK from IC -  Payment #2:

The Host next creates a second payment for the Cash/Check Commission amounts which are the balance due from Payment #1 above).

• Go to Payment|Received.
• Query for Cash/Check invoices with the Client Open (IC collect payments from customer).
• Select the Payment Method of Other; the Profile Type of Client, and the Profile Name is the name of the IC Agency. Add the IC Code and check All Clients on the Client Settings tab.  
• Close all open Cash/Check Invoices the IC is paying for, plus Payment #1 Voucher and create a journal entry to credit Client Balances and debit IC Commission for the amount of the IC commission retained by the IC when it issued the check.

All IC invoices are now closed on both the client side (IC check for open client Bookings + adjustment voucher) and on the Vendor Side with the Host checks sent to and received from Vendors or ARC/BSP.

Host Receives GROSS CHECK for Open Cash/Check Invoices from IC:

• Go to Payment|Received.  
• Query for any Cash/Check invoices that are open on the client side (IC collects payments from customer), by first setting the Profile Type to Client and entering the Profile Name of the IC agency.
• Add the IC Code and check All Clients on the Client Settings tab. The IC check was for the gross amount on Cash/Check Sales, so close all open Cash/Check Client Bookings paid by the check received.  All IC invoices are now closed on both the client side (IC Check For Open Client Bookings), and on the Vendor side with the Host checks sent to and received from Vendors or ARC/BSP.

Host FEE CHARGES to IC:  

If 100% commission is returned to the IC, then there should be a transaction fee invoice created by the Host for the fee charges.

• Go to Invoice|Sale and create a fee invoice with Total Fare = 40.00, and Commission/Rate = 40.00.
• Close the fee invoice when saving it with a Payment Received.
• Select the Payment Method of Other, and the Profile Type of Client, entering the Profile Name of the IC Agency.
• Create a Journal Entry to Credit Client Balances, and Debit IC Commissions for the amount of the Transaction Fees. The fees reduced the amount of the Comm Check to the IC - this puts it back in.

Host Determines and Pays IC Commission Due

If 100% of commission is to be returned to the IC by the HOST, and only transaction fees are charged by the Host, the IC adds Agents to bookings as normal.  Multiple agents or agent rates are not needed.  But if there is a commission split between the Host and IC, then the IC Agent Profiles need to reflect the commission percentage that the IC receives from the Host in the Inside Agent field of the profile.

For an IC/Host commission split of 80%, enter this into the Inside Agent Rate field of all the IC Agent Profiles.  These agents should always be indicated as the first agent in the booking.  Then the Trams Crystal Host Report – Comm Due IC - Commission Split Report (see below) used for commission reconciliation will be correct.

Determine Commission Due to IC - 100% Commission Return with Transaction Fees:  

• The Host determines how much commission is due the IC by running Host Report - Comm Due IC - 100% Commission Return With Transaction Fee (HostReport100PctComm.rpt), a Trams Crystal Report found at http://static.trams.com/tramslibrary/reports/tbotcr.html under TBO: Financial Reports. The report should be run for either Gross (G) or Net (N) depending on how the IC paid the Host for Cash/Check Sales.

• The Host then enters the Comm Due IC total from the report as a Payments|Made to the IC with a Payment Method of Check, a Profile Type of Other, the Profile Name of the IC Agency.
• Create a Journal Entry to Credit Checking and Debit IC Commission for the amount of the IC Commissions being paid.

Determine IC Commission if Split Without Transaction Fees:

If there is a commission split between the Host and IC, then that should be reflected in the IC Agent Profile in the Host database as outlined above.   

• To pay the IC, the Host runs the Trams Crystal Report, Host Report - Comm Due IC - Commission Split Without

Transaction Fee (HostReportCommSplit.rpt).  Report can be found http://static.trams.com/tramslibrary/reports/tbotcr.html under TBO: Financial Reports. The report should be run for either Gross (G) or Net (N) depending on how the IC paid the Host for Cash/Check Sales.

• The Host then enters the Due IC amount from the report by going to Payments|Made to the IC with a Payment Method of Check, a Profile Type of Other, the Profile Name of the IC Agency.

• Creates a Journal Entry to Credit Checking and Debit IC Commission for the amount of the IC Commissions being paid.

Host Creates IC Export File for Commissions Due an IC with Trams Back Office

A Host Agency can create a file to send an IC that has Trams Back Office.  It contains Credit Card and any possible

Cash/Check Commissions due, along with any fee invoice charged the IC.  This Mail Merge file can be used by an IC with Trams Back Office to reconcile its Host commissions as they would do for PayMode commissions.  The Host should include this file with a copy of the Trams Crystal Report for Host Commissions Paid Less Any Fees Charged, along with the IC’s Payment Check.  The report can be found here Report found at

http://static.trams.com/tramslibrary/reports/tbotcr.html under TBO: Financial Reports

• To create the file, the Host goes to Reports|Agent|IC Export:

IC Code:  Select the IC Code from the drop-down menu for the IC for which you are producing this file.

ARC/Supplier Non-CC:  From drop-down menu, select Issue Date, Client Payment Date, Vendor Payment Date, or Exclude.  Note:  If you are not returning Cash/Check Commissions to the IC, then this field can be set to Exclude.

ARC/Supplier CC:  From drop-down menu, select Issue Date, Client Payment Date,  Vendor Payment Date, or Exclude.  Note:  Normally set to Vendor Payment Date.

Comm Track:  From drop-down menu, select Issue Date, Vendor Payment Date, or Exclude.  Note:  Normally set to Vendor Payment Date.

From/To Dates:  Enter a date range for which to run this report/file.

Output Format:  Select Mail Merge from the drop-down menu.  Save to your desktop or other appropriate location.

• Send or email this file to the ICs.

IC Setup & Procedures

The following sections highlight the procedures Independent Contractors follow to effectively use the IC/Host functionality in Trams Back Office and ClientBase Windows. 

• Set Up the IC/Host Codes
• Create and Send IC Invoices to Host
• Handle Payments/Commissions Between Host and IC with Trams Back Office

Setup of IC/Host Codes

The Host/IC functions in Trams Back Office and ClientBase Windows need to be "turned on".  If you are an IC Host Codes are setup, and an IC Code is setup by your Host.  With one or both of these codes setup, you are now ready to transmit or receive IC invoices.

ClientBase Windows Setup of Host Codes by IC:

Set up a Host code in ClientBase Windows by going to Global Defaults|Res Card Defaults|Host Codes.  The IC creates Host codes for one or more Hosts:

Trams Back Office Setup of Host Codes:

If you are an IC with Trams Back Office, go to Utilities|Setup|IC/Host Codes.  As an IC, create a Host Code for one or more IC's.   

Type:  From the drop-down select Host.

Code:  Enter a free-flow Host code.

Description:  Enter a free-flow description.

Export Branch:  The Export Branch represents the Branch Number in the Host’s database. When using the IC/Host Export Utility, the records to be exported and imported by either the IC or the Host can be imported into the appropriate branch for each database.

Click Add to add the Host code to the data entry screen below, then OK to save.  

Note:  The Host sets up its own IC Code/s (see section above, Host Setup & Procedures).  

Creating IC Invoices

Invoices are created in one of two ways- either through the Host GDS or in ClientBase Windows.  

For agencies with only Trams Back Office, only Method 1 would be used.  

For agencies with ClientBase Windows, Method 2 would be used, along with Method 1.  

Air/Car/Hotel bookings would be created using the Host GDS and the Merge To PNR feature of ClientBase Windows. Tour/Cruise and other bookings would be created manually or using ClientBase Windows Live Connect. 

Method 1:  Creating IC Invoices Through Host GDS

• The IC enters its IC Code in the PNR when creating - e.g., *ICplanit. It can be added at the end of an "AC" Line or as a Remark Line.   
• The IC then enters a Client Account Number (*AN, *CA):

 Specific Number for each IC Client Profile OR Single number for Client Profiles sent to Host

If 100% of the commission is to be returned to the IC by the HOST and only transaction fees are charged by the Host, then enter Agents to the bookings as normal.  Multiple Agents or Agent Rates are not needed.  If there is a Commission Split between the Host & IC, then that should be reflected in the IC Profiles by the Host Agency.  

• The Host Agency does an interface download of the IC records.

Method 2:  Creating IC Invoices Using an IC Copy of ClientBase Windows

In ClientBase Windows the IC creates reservations and adds the Host Code from the drop-down menu.  This Host Code is setup with the name of the Host as outlined above, i.e. MAG.  The invoice is generated as normal.  The Host Code is added to and saved in the invoice at the reservation level in ClientBase Windows.

• IC Has Agreed To Share Client Profile Information With The Host
  • IC uses Merge To PNR as normal, transferring the Client Interface ID from the profile to the PNR created in the Host GDS.  All bookings created by the IC in the Host GDS are interfaced by the Host under individual profiles in the Host database.
• IC Has Agreed NOT To Share Client Profile Information With The Host
  • IC globally creates an additional “House Account” Interface ID under Merge To PNR Rules that always moves.  This single House Account ID would then be selected from each profile’s Merge To PNR screen, while deselecting the profiles primary Interface ID.   All bookings created by the IC in the Host GDS are interfaced by the Host under one single House Account profile in the Host database.
  • Even if the IC has agreed not to share individual client information with the host, the IC may include each individual client’s interface ID as a marketing identifier.  The marketing identifier is entered using the format
  • *MI and the individual client’s interface ID.  The house account ID will be used in the host’s database and the marketing identifier will be used in the ICs database.

IC Sends Invoices Created in IC’s ClientBase Windows to Host:

The IC would do a PNR Import of the PNR’s created in the Host GDS for its Air/Car/Hotel reservations.  These reservations would import into each of the IC’s client profiles.   To exclude reservations that were created via PNR Import, check the box in the Trams Export Utility “Exclude bookings generated via PNR Import.”  The Host will capture these bookings when doing his normal Interface Download and Processing.

The IC needs to export only Tour/Cruise and other booking reservations created in ClientBase Windows by setting up the Trams Export Utility.  

• The IC produces an export file using the Trams Export Utility. The Trams Export Utility and more information can be found here.
• Make sure to save this file to Program Files (x86)|Trams|Common Files.

Database:  Select the database from the pull-down menu (default is Trams).

User Name/Password:  Enter User Name and Password for database, click Connect.

From/To Date:  Enter dates you want to create export file for.

Export Filename:  Enter the path to which to save your export file.  The example shows the file being saved in a "temp" folder, but you can create any folder to save the file to, or save it to the "desktop."  If you save it to the "temp" folder, make sure that you have a "temp" folder created on your computer or the export will error out when it can’t find the non-existent folder.  If you are exporting more than one file (to two different Host Agencies, for example), be careful about naming files, as the default gets overwritten each time a new file is generated.

IC to Host:  By default a Target HOST Code is defaulted.  If you are working with more than one Host Agency, select the appropriate HOST code from the drop-down window.  Enter the appropriate branch the HOST wants invoices to be processed to (this is important).  As an IC make sure your IC Code is entered so that the Host knows that the records you send are from you as an IC.  That identifies your IC records from other IC records the Host Agency may have in their database.  (In this example, PLANIT is the sender's IC code.)  

Note 1:  If the IC has agreed to share Client Profile information with the Host, then currently the Export Utility will correctly pass all the individual profiles with Name, Address, Interface ID and the Primary Phone and Email.

Note 2:  If the IC has agreed NOT to share Client Profile information with the Host, then a single "House Account" profile needs to be specified in the Target Interface ID field of the Export Utility.  This "single" profile ID when specified captures all the IC bookings under one "House Account" profile in the Host database.    

Send or email file to the Host Agency to interface into TBO.

IC Receives Data from HOST 

IC with Trams Back Office:  If you are an IC who has Trams Back Office, there are two ways to get data from the Host into your database.   

• Do a regular GDS download interface of IC PNR’s created in Host GDS to bring these invoices into Trams Back Office. Your host would need to set up a second or "double download" file for you to access, so you can download your IC invoices into your copy of Trams Back Office.   

Your Host needs to set up a separate Pseudo City Code for you to work in so you only have access to the PNRs you create as an IC.  Only records you create are available in your interface download file.

Note:  If you choose this method of receiving invoices, they will contain ARC/BSP bookings that would need to be closed on the Vendor side, even though the Host would be processing the ARC/BSP report.

OR

• The Host sends you a file of interfaced GDS invoices using the Trams Export Utility which you, the IC, interface into TBO by going to Interface|Import by File. Then process the imported file by going to Interface|Process like any other GDS interfaced records.

Note 1:  ARC/BSP Bookings are converted to Supplier Bookings, only Client C/C Payment information is extracted; there are no Partial Payments or Vendor Payments contained in file; and all imported invoices are “stamped” with Host Code (e.g., “MAG”).

Note 2:  If the IC has both Trams Back Office and ClientBase Windows, they could choose to do a PNR Import from the Host GDS using ClientBase Windows for their Air/Car/Hotel bookings, and not have to receive an Export Utility file from the Host.   An IC may still desire to receive the Export Utility file as the ARC/BSP segment data details are more complete then what is captured using ClientBase Windows PNR Import.   

Note 3:  An IC using the PNR Import in ClientBase Windows for all Air/Car/Hotel bookings and creating all their Tour/Cruise and other bookings within ClientBase Windows, would NOT have a need to receive an export file from its Host agency.  The transfer of data would be one way.  Only the IC would need to use the Export Utility to pass Tour/Cruise and other bookings back to the Host, if being booked through the Host.

IC with ClientBase Only - Bringing in Travel History from Host 

As an IC that does NOT have Trams Back Office, but has ClientBase Windows, you want to bring in travel history of invoices that have been created in the Host GDS, but with no segment or payment details.   

Note 1:  This would most likely be used when the IC did not have access to a Host GDS or the Host is willing to keep track of the accounting details of bookings, so that the IC can focus on their selling efforts.   

With access to a Host GDS, you could choose to do a PNR Import of the GDS bookings created by you as IC.  If the Host is willing to keep track of the accounting details of bookings, so that the IC can focus on selling, then use the Import of Travel History option.   

Note 2:  New Profiles are created in ClientBase Windows when processing the XML Travel History Import File.  The XML Import currently does not import the Primary Phone or Email address into the new profile, as is done when using the Import From File option in Trams Back Office.

• Obtain an XML file created and sent to you by the Host and save somewhere on your computer.
• Go to File|XML Import|Travel History Import.

XML Filename:  Enter the XML filename full path and filename of the XML file to be processed, or browse for file from the drop-down menu.

Log filename (optional):  Enter the full path and filename of the log file, or choose file to append from drop-down menu.  It is suggested that you create a log file.    

Import rule/Match and update:  Select this option to match selected profile-related information from the export file and attach data created at time of export to a profile if a match is found.  If a match is not found, the profile data is added to the database as a new record and all data selected at time of export is attached to it.   For Match and update, choose to match profiles using the Interface ID.  Also select the Update rule by choosing Add Profile.

Branch (Required for Travel History)..Pick One:

Constant only when XML value is not found:  From the drop-down menu, select the default branch if value in the XML file is not found.  

Vendor (Required for Travel History)  It is highly unlikely that there will not be a vendor in the XML file, but pick one:

Constant only when XML value is not found:  From the drop-down menu, select the default vendor if value in the XML file is not found.  (Suggested dummy vendor profile or use of agency profile.) 

• You may be prompted with a listing of Travel Categories within the file that need to be mapped to the Travel Categories included within ClientBase Windows.  Click the arrow for a drop- down list of Travel Category choices to select from. 

• You are alerted when the import is completed, and you have the ability to view the import log and print out the import summary if desired:
• Go to the Travel History tab in one of the profiles to see if travel history was imported correctly.

Handling Payments between IC with Trams Back Office and Host 

IC Must Have Trams Back Office:  Depending on the payment agreement, the IC sends either a net check or a gross check to the Host.  IC then reconciles Trams Back Office with payments made/received to clear out client and vendor balances, and settle generated commission and/or service fees. 

Payment Made by IC with Trams Back Office -  Net Check

The Export Utility file MUST be received from the Host and processed, or a PNR Import of GDS booking into ClientBase must be done, before running the following Crystal Independent Contractor Cash/Check Invoice Report.

• If the IC is required to make NET Payments to the Host for open Cash/Check invoices in the Host database, the IC runs the Trams Crystal Independent Contractor Cash/Check Invoice Report (rpt) to determine the amount of Net Cash/Check invoices that are open in the Host Database. Select the "N" NET option on the report’s selection criteria screen.  This report can be found at http://static.trams.com/tramslibrary/reports/tbotcr.html under TBO: Financial Reports.

• Next, create an IC Payment Made for the Amount Due Host from the Crystal Report by going to Payments|Made.
• Query for Cash/Check Invoices with the Vendor Open (Host pays Vendor).
• Set the Profile Type to Vendor; and the Profile Name to Name of Host. Add the Host Code from Vendor Settings tab, and check All Vendors on Vendor Settings.

The check you produce closes all the invoice bookings where the Vendor amount in the "Net Due" column is a (-) or negative amount.  The client balances are closed with customer checks to IC.  All IC invoices are now closed on both the Client side (with Customer Payments), and on the Vendor Side (less any CC Commissions Due) with this check to the Host.

Payment Made by IC with TBO - Gross Check:

If the IC is required to make Gross Payments to the Host for open Cash/Check invoices in the Host database, the IC ensures that a second (Comm Track) booking is created for each Client Cash/Check booking that is Open in the Host database.  

1) After processing the Host/IC Trams Export Utility file, make sure in each invoice there is first a supplier booking for total fare and zero commission, and a second booking that is a Comm Track Booking for zero total fare and commission for the amount to be returned by the Host (you have to create this booking).

2) Run the Trams Crystal Independent Contractor Cash/Check Invoice Report (ICCashCheckPay.rpt) to determine the amount of Cash/Check invoices that are open in the Host database. Select the "G" Gross option on the report's selection criteria screen.  This report can be found at

http://static.trams.com/tramslibrary/reports/tbotcr.htm under TBO: Financial Reports.

3) Next, create an IC Payment Made for the Amount Due Host from the Crystal Report by going to Payments|Made. Query for Cash/Check Invoices with the Vendor Open (Host pays Vendor).

4) Set the Profile Type to Vendor; and the Profile Name to Name of Host. The Submit to is set to Supplier in the Vendor Settings   Add the Host Code from Vendor Settings tab, and check All Vendors on Vendor Settings tab.  

The check you produce closes all the invoice bookings where the Vendor amount in the "Net Due" column is a (-) or negative amount.  The Client Balances are closed with customer checks to IC.  All IC invoices are now closed on both the Client side (with Customer Payments), and on the Vendor Side (less any CC Commissions Due from Host) with this Check To Host.

How to Query for Host Items in Trams Back Office:

To query for invoices coded for the Host, go to the Invoice Query screen and use Host Code fields as selection criteria to find invoices.  To find Agent Adjustment items, go to the Agent Adjustment query in TBO, and use the Host Code fields as selection criteria to find adjustments.  Report Generator reports can also be run based on a Host Code selection.

IC Commission Reconciliation Option for 100% Commission with Fees

If Host charges transaction fees, the Host may have created a Supplier invoice in its database.  When the IC imports the file sent by the Host through the IC/Host Utility, that transaction fee invoice will be created in the IC’s database.  If not, the IC needs to create an invoice for transaction fees with Total Fare and Comm being the amount of the fees.   

• Close the fee invoice with a Payment Received when saving the invoice, or go to Payments|Received.  
• Use the Pay Method or Other, Profile Type of Client, Profile Name of the IC Agency, and create a Journal Entry to Credit Client Balances, and Debit Host Fees.  
• To reconcile Cash/Check/CC Commissions due from Host, run the Trams Host file sent to you by your Host by going to Payments|Import.

• Under Payment Source, select Trams Host, add name of Host profile in the Payee Name field, select the location of the commission reconciliation file received from your Host, and click Import to load the Host file.

All Paid CC and Cash/Check Commission records in the IC’s database should show a Status of “R”.

Note:  The IC has to create a Comm Track second booking for any Cash/Check invoices where the Host was returning the cash/check commissions to the IC using Trams Host File.  The Commission Amount reconciled is for the Total Comm from the Host booking.  Manually reconcile any unreconciled records. 

IC Commission Reconciliation Option for Commission Split and No Fees:

• If the Host and IC are splitting the commission with no transaction fees, the IC runs the Trams Host file sent by the Host by going to Payments| Import.  
• Under Payment Source, select Trams Host, add name of Host profile in the Payee Name field, select the location of the commission reconciliation file received from your Host, and click Import to load the Host file.
• All Paid CC and Cash/Check Commission records in the IC’s database should show a Status of “R”.

Commission Split Fee Adjustment:  Since the reconciliation is for the Total Fare and Total Commission from the Host booking, the amount of the Host check will be less than the reconciled commission.  

• An Adjustment needs to be done to put the difference into a Host Fee account. Refer to the Host Split Commission Trams Crystal Report sent by the Host.  Note the Host Portion of Commission Split line for the amount of the fee adjustment needed.  Since reconciled IC Commissions are for the full amount of commission, when using the Trams Host Import, the Host portion kept would be a Host fee to the IC.
• Create Sales Invoice for the amount of commission split.

• The Vendor is IC Fees; the Total Fare = 0; the Comm = Commission Split Expense to IC. Close the invoice upon saving with a zero Payment Received, Pay Method of Other, and don’t journalize.  
• Go to Payment|Received to close the invoice for the amount of the Commission Split
• Select a Payment Method of Other, the Vendor IC Fees. Journalize to Host Fees.   

The IC system commissions now reflect total commission yield for the products sold, with an adjustment invoice for the Commission Split amount withheld from the Host’s Commission Payment to the IC.  That Split has now been expensed to the IC’s Host Fees account.