FILE TYPE

CLICKSQUARED EXPORT

FILE TYPE DESC

Clicksquared Export

EXPORT SOURCE

GEN_STAYREC_REV_EXPORT_VW

SOURCE DESCRIPTION

OIS Stay Revenue Record Export

FILE NAME FORMULA

to_char(pms_p.business_date,'YYYYMMDD')||'.pms'

FILE NAME EXTENSION

TXT

COMPRESS DATA

Y

COL SEPARATOR

| (Pipe sign, no quotes)

COL_NAME

COL_TYPE

COL_LENGTH

NATIVE NAME

MAPPING

FORMULA

txn_folio_id

varchar

30

Transaction folio id

FORMULA

RESORT||'-'||PMS_RESV_NO||'-'||ROW_NUMBER() OVER (PARTITION BY PMS_RESV_NO ORDER BY STAY_RECORD_ID DESC)

status

varchar

20

"Assign status

(cancel, no-show, etc) to each record"

STATUS

tran_id

varchar

20

Transaction ID

FILLER <-> BLANK

tran_code

varchar

6

Transaction code

TRANSACTION_REVENUE_TYPE

post_dat

datetime

8

Date that transaction posted

FILLER <-> BLANK

amount

money

8

Transaction amount

TOTAL_REVENUE

currency

varchar

6

Transaction currency

CURRENCY_CODE

account

varchar

20

PMS account number

PMS_RESV_NO

name

varchar

30

Guest Name

DISPLAY_NAME

address

varchar

40

Guest Address

ADDRESS1

city

varchar

20

Guest City

CITY

state

varchar

2

Guest state

STATE

zip

varchar

10

Guest zip

ZIP_CODE

country

varchar

6

Guest country

COUNTRY

company

varchar

30

Guest company

COMPANY

phone

varchar

15

Guest phone

PHONE_NO

fax

varchar

15

Guest fax

FAX_NO

email

varchar

40

Guest email

EMAIL

t1_taacct

varchar

6

PMS travel agent account number

TA_NAMEID

t1_name

varchar

30

Travel agent #1 name

TA_NAME

t1_address1

varchar

40

Travel agent #1 address

TA_ADDRESS1

t1_address2

varchar

40

Travel agent #1 addre_1

TA_ADDRESS2

t1_city

varchar

20

Travel agent #1 city

TA_CITY

t1_state

varchar

2

Travel agent #1 state

"TA_STATE,"

t1_zip

varchar

10

Travel agent #1 zip

TA_ZIP_CODE

t1_phone

varchar

15

Travel agent #1 phone

TA_PHONE_NO

t2_taacct

varchar

6

PMS travel agent account number

FILLER <-> BLANK

t2_name

varchar

30

Travel agent #2 name

FILLER <-> BLANK

t2_address1

varchar

40

Travel agent #2 address1

FILLER <-> BLANK

t2_address2

varchar

40

Travel agent #2 address2

FILLER <-> BLANK

t2_city

varchar

20

Travel agent #2 city

FILLER <-> BLANK

t2_state

varchar

2

Travel agent #2 state

FILLER <-> BLANK

t2_zip

varchar

10

Travel agent #2 zip

FILLER <-> BLANK

t2_phone

varchar

15

Travel agent #2 phone

FILLER <-> BLANK

t1_country

varchar

6

Travel agent #1 country

TA_COUNTRY

t2_country

varchar

6

Travel agent #2 country

FILLER <-> BLANK

t1_iata

varchar

8

Travel agent #1 iata

TA_IATA_CORP_NO

t2_iata

varchar

8

Travel agent #2 iata

FILLER <-> BLANK

t1_type

varchar

20

PMS TA account type

TA_IATA_COMP_TYPE

t2_type

varchar

20

PMS TA account type

FILLER <-> BLANK

comments

varchar

2000

Guest comments

FILLER <-> BLANK

crs

varchar

12

Central Reservation number

CRS_BOOK_NO

res_date

datetime

8

Book date

FORMULA

"TO_CHAR(BOOK_DATE,'YYYYMMDD')"

cxl_date

datetime

8

Cancellation date

FORMULA

"TO_CHAR(CANCELLATION_DATE,'YYYYMMDD')"

arrival

datetime

8

Guest arrival

FORMULA

"TO_CHAR(ARRIVAL_DATE,'YYYYMMDD')"

departure

datetime

8

Guest departure

FORMULA

"TO_CHAR(DEPARTURE_DATE,'YYYYMMDD')"

room_type

varchar

12

Room type description

ROOM_LABEL

room

varchar

6

Guest room

ROOM_NUMBER

rate

varchar

11

Guest rate

ROOM_REVENUE

gtd

varchar

20

Guaranteed credit card type

PAYMENT_METHOD

ghacct

varchar

8

Guest History account number

FILLER <-> BLANK

last_stay

varchar

8

Guest last stayed

FORMULA

"DEPARTURE_DATE+1 OR LAST_INFO.GET_LAST_STAY(RESORT,TRUNC(DEPARTURE_DATE,'YYYY'),GUEST_NAME_ID)"

package

varchar

6

Guest package

FILLER <-> BLANK

rate_code

varchar

6

Guest rate_code

FORMULA

"STAY_API.GET_STAY_RATE_CODE(MEMBERSHIP_TRX_LINK_ID,ARRIVAL_DATE)"

marketseg

varchar

6

Guest market segment

MARKET_CODE

source

varchar

50

Guest source

SOURCE_CODE

track

varchar

6

Source of awareness

FILLER <-> BLANK

nights

varchar

2

Guest nights

NUMBER_NIGHTS

adult

varchar

2

Guest adult

ADULTS

child

varchar

2

Guest child

CHILDREN

sgacct

varchar

20

Select guest account number

MEMBERSHIP_NUMBER

cc

varchar

19

Credit card number

CREDIT_CARD_NUMBER

exp

varchar

5

Credit card exp date

CREDIT_CARD_

EXPIRATION_DATE

"TO_CHAR(CREDIT_CARD_EXPIRATION_DATE,'MM/YY')"

r_name

varchar

50

Group name

C_DISPLAY_NAME

saacct

varchar

10

Group account number

GROUP_NAME_ID

shareacct

varchar

10

share-with account

FORMULA

"DECODE(NVL(SHARE_NO,0),0,'N','Y')"

FILE FORMULA

"to_char(pms_p.business_date,'YYYYMMDD')||'.pms'"