Connectivity to Nor1 using the Extract

5 Connectivity to Nor1 using the Extract

Introduction

This chapter addresses extracting reservation information from the property management systems (PMS). It describes how the extract works, Nor1 database parameters, and customer use of SFTP to transfer information to Nor1.

How it Works

The Nor1 extract for PMS exposes additional hotel guests to Nor1 eStandby Upgrade offers from the Nor1 pre-arrival email. Extracts should contain all the reservation activities that took place during the day (preceding 24 hrs. + x). You can send them to the Nor1 data center by a secure SSL encrypted connection. It contains absolutely no payment information of any kind.

Depending on the typical variance of the time your night audits run, you might want to add the corresponding hours to the update window. If your extract runs consistently on a scheduler basis, you may leave X as the number of minutes the process needs to complete. Nor1’s framework excludes bookings that have been previously exposed from being re-submitted for email delivery. If such a time cannot be handled, you can select the option to provide all active reservations on a nightly basis.

Upon completion of the night audit, or on a fixed schedule, you can deliver an extract using an https post (the file content is the payload of the post) or by Secure File Transfer Protocol (SFTP).

The payload of the post contains the content of the file and is described in the next section.

The following data is contained in the extract:

PROPERTY (code) (mandatory field)
CONFIRMATION NUMBER (mandatory field) 
BOOKED ROOM CODE (mandatory field) 
ARRIVAL DATE (mandatory field) 
NUMBER OF NIGHTS (mandatory field)
TOTAL BOOKING PRICE (without taxes) (mandatory field) 
GUEST EMAIL ADDRESS (mandatory field) 
GUEST LAST NAME (mandatory field) 
GUEST FIRST NAME (mandatory field) 
RATE CODE (mandatory field) 
NUMBER OF ADULTS (mandatory field) 
NUMBER OF CHILDREN (mandatory field) 
GUEST LANGUAGE (mandatory field) 
GUEST TITLE 
GUEST COUNTRY OF RESIDENCE 
GUEST ZIP CODE
GUEST STATE
DEPARTURE DATE 
NUMBER OF ROOMS 
INSERT DATE 
MEMBERSHIP ID 
ROOM CATEGORY 
MEMBERSHIP LEVEL 
RESERVATION STATUS (mandatory field) 
BLOCK CODE 
GUEST PHONE NUMBER 
EXTERNAL REFERENCE TYPE 
EXTERNAL REFERENCE 
MARKET CODE 
SOURCE CODE (ORIGIN OF BOOKING)
GUARANTEE CODE

Nor1 merges this data with previously exposed requests and uses this data to determine who should receive an offer email, and what the offer and pricing should be. Nor1 generates a hotel-branded email that is sent to the guest.

Note:

No credit card or payment information is included.

Sample Data/Payload

Nor1 Database Parameters

This table describes the data fields and values used by Nor1. Contact Nor1 to discuss the exact values that will be used.

Number	Priority	Field	Description	Example	Type (Limit)
1	Mandatory	PCD	Hotel / Property Code	SJCHOTEL	char (20)
2	Mandatory	EBC	Booking Code / Reservation number	MYP12346A	char (40)
3	Mandatory	RCC	Room Category Code	KIRRC	char (20)
4	Mandatory	GAD	Arrival Date	YYYYMMDD	date
5	Mandatory	NMN	Number of Nights	2	int (2)
6	Mandatory	TBP	Total booking price	256.43	float (10,2)
7	Mandatory	GEM	Guest email	guestuser@yahoo.com	char(100)
8	Mandatory	GLN	Guest last name	Doe	char(100)
9	Mandatory	GFN	Guest first name	John	char(100)
10	Mandatory	PID	Provider ID (Nor1 to Provide)	NOR1	char (5)
11	Mandatory	PET	Provider Entry Type	WEB	char(5)
12	Mandatory	PRL	Rate Level (if applicable)	LV1	char(12)
13	Mandatory	PRC	Rate Code	BAR	char(12)
14	Mandatory	SRP	Group Code	G1234	char(12)
15	Mandatory	NOA	Number of adults	1	int(2)
16	Mandatory	NOC	Number of children	0	int(2)
17	Mandatory	GNR	Number of Rooms	1	int(3)
18	Mandatory	LNG	Language (ISO 639-1 compliant)	EN (default)	char (2)
19	Mandatory	PCH	Property Chain Code	(Nor1 to Provide)	char(7)
20	Mandatory	PLC	Loyalty Code	D	char(3)
21	Mandatory	GPC	Guest Postal Code	95054	char(50)
22	Mandatory	GST	Guest State	California	char(100)
23	Recommended	PTS	Timestamp of Booking	YYYY-MM-DD H:MM:SS	date and time
24	Recommended	PRT	Rate Tier *	TIER	char(12)
25	Recommended	PSC	Market Segment Code *	AAA	char(12)
26	Recommended	PCC	Channel Code *	VOICE	char(12)
27	Recommended	PCN	Room Name	King Std with View	char(255)
28	Optional	CUR	Currency Code	USD, EUR, GBP	char(3)
29	Optional	GEN	Guest gender (“F”, “M”)	F	enum(‘M’,’F’)
30	Optional	GSL	Guest salutation	Mr.	char(50)
31	Optional	GCC	Guest country code (ISO 3166-1-alpha-2 code)	US	char (2)
32	Optional	RTP	Record Type (N = New, M=Mod, or =Cancel)	N	enum(‘N’,’M’,’C’)
33	Optional	GDD	Departure Date	YYYYMMDD	date
34	Optional	PLN	Loyalty Program Number (member id)	NH0004	char(16)

Note:

Use only two of the three fields marked with asterisks. Contact Nor1 and advise which fields you are using.

Exchanges using SFTP

To use the Secure File Transfer Protocol (SFTP), customers must provide the following information to Nor1 by email:

The public IP address of the customer computer that will perform the file transfer. This allows a connection only to the allowed IP address.
To find public IP addresses, refer to: http://www.whatismyip.com/
Nor1 uses Public key infrastructure (PKI) to further secure the login, so the Secure Shell (SSH) Public Key of the account that will perform the file transfer is required.
For help with key file generation, refer to Appendix B.
Send the above information to Nor1 by email.

Once Nor1 receives the above information, Nor1 sets up an account for the customer, for example: customer1.

To transfer a file from the computer where the key was generated, do the following:

sftp <customer1>@extracts2.nor1.com.

You should get the following prompt: sftp>.

If not, refer to the troubleshooting steps in Appendix A.
Change to the extracts directory using the following command

sftp> cd extracts
Enter the file name (for example, file1) with the following command:

sftp> put <file1>
List the file, with the following command:

sftp> ls
Close the connection:

sftp> quit

Note:

Customers can automate the above steps using scripts or tools to transfer files automatically.

On Premise SFTP Setup for OPERA Simple Reports (OSRs)

Use the following instructions to set up on premise SFTP for your property.

Allowlist the following IP address from the property:
- 147.154.12.24
Be sure to enable outbound traffic to that IP addresses over port 22.
Provide IP address from the App server.
Generate keys from Puttygen.com and provide the public key for allowlisting on the NOR1 side.
Once allowlisting is completed on both ends, set up configuration in OPERA as follows:
- Access OPERA Configuration > Property > Delivery Method > General and click the SFTP tab.
Import and validate the public key previously generated.
The Implementations team will upload the OPERA Simple Reports (adding unique transactions codes provided by the property): 5am, 10pm, and Recovery Report.
In the Reports Scheduler section Distribution dialog, select Type: SFTP and Value: NOR1 SFTP TRANS.
Run reports to validate communications.