End of Day Troubleshooting

End of Day Setup

The End of Day Sequence is run in all properties at the end of their business day. This runs in different steps that are separated into the categories of postings, procedures and reports.

Arrivals for Today. Allows you to check-in the Due In guests. If you choose not to check in the listed guests, they would become No Shows during the Roll the Business Date part of the End of Day Sequence.

Departures for Today. All the Due Out guests for that Business date are listed. You can either extend the stay of the guest or check out the guest. You cannot proceed without completing this operation.

Automatic Closure of Cashiers. When the Application Settings>End of Day>Auto Close Cashiers parameter is set to Y, this procedure automatically closes all cashiers that have not been closed without a user having to go through balancing each of the cashiers individually.

Note: If the Cashiering>Positive Cashier Drop application parameter is set to Y, OPERA will not allow cashiers to be closed with a negative shift drop.

Weather. When the Application Settings>End of Day>Weather and Notes parameter is set to Y, you can activate the Weather and Notes routine.

Roll the Business Date. This operation rolls the system to the next business date. When you start this step, it accepts the number of minutes to wait before making users log out of their opera sessions (as defined in application settings). OPERA broadcasts the message to all other OPERA users that the business date is going to be rolled. After waiting for the specified number of minutes, the End of Day Sequence will kill all the existing opera sessions and roll the business date. The users can then go back into the system and pick up the new date. The first process that End of Day Sequence runs after rolling the business date is the No Show procedure. This process should not take long to complete. During this process, users are not allowed to alter the reservations that are in the process of being No Showed.

Posting Room and Tax. All the in-house guests for the selected Business Date will have room, tax, packages, and fixed charges posted based on their rate code.

Running Procedures. All the procedures would be run during this step. These populate the summary tables.

Printing Reports.  Prints all End of Day Sequence reports. Reports are stored on the application server under the Export folder.

All End of Day Sequence Procedures

The following is the list of the mandatory End of Day Sequence procedures, followed by a list of the optional procedures.

• Reservation No Shows - reservation.no_show
• Synchronizing Front Office Statuses -Synchronize_fo_status
• Updating Room Statuses -hkpkg.update_room_status_proc
• Creating Guest ledger - Refresh_fin_summary.refresh_resv_summary
• Populating Trial Balance - Refresh_fin_summary.populate_trial_balance
• Generating Reservation Statistics - Update_statistics.update_statistics_main
• Populate Folio Tax Summary - Refresh_fin_summary.populate_folio_tax

  Note: The procedures above are mandatory for the income audit as well. These procedures will be run twice if you use Income audit, once in the End of Day Sequence and once before the income audit.

Procedures below are not mandatory but can be configured to run in the End of Day Sequence. The mandatory procedures below are only run once in the End of Day Sequence even if you use Income Audit functionality.

• Calculating Historical Information - pms_prform00.update_profile_resv_statistics
• Membership Trans & Points - Membership_p.create_daily_membership_tran

Posting Room and Tax, Running Procedures and Printing Reports are submitted as jobs in the database. In order to know about the jobs, Support needs to be aware of two Database views, DBA_JOBS and DBA_JOBS_RUNNING.

The following two-step query gives information about what jobs are running.

SELECT J.JOB, J.WHAT, J.SCHEMA_USER, J.FAILURES, J.INSTANCE, r.sid

FROM DBA_JOBS j, dba_jobs_running r

where j.job = r.job

and j.instance in (select instance_number from v$instance where rownum < 2);

Next, select information about the session. You should issue the following statement.

Note: Replace the X below with proper sid value given in the statement above.

Select * from v$session where sid = <X>;

Status Change Information in the Business Date Table

The following statuses are for information purposes only.

• OPEN-- Starting of the business date.
• NIGHT AUDIT_PROGRESS-- After the End of Day Sequence starts and immediately after rolling the business date.
• NIGHT AUDIT_ROOM_COMPLETE-- Posting room and tax portion of the End of Day Sequence has been successfully completed.
• NIGHT AUDIT_UDJ_COMPLETE-- Procedures portion of the End of Day Sequence has been successfully completed.
• NIGHT AUDIT_REPS_COMPLETE-- After successful completion of the reports but before Income Audit.
• NIGHT AUDIT_UDJA_COMPLETE-- After successfully completing the Procedures after the Income Audit. Only valid if the Application Settings>End of Day>Income Audit function is set to Y.
• NIGHT AUDIT_AUDIT_COMPLETE-- After successfully completing the Income Audit.
• NIGHT AUDIT_REPA_COMPLETE-- After successfully submitting the reports that are setup to be submitted after income audit. Only valid if the Application Settings>End of Day>Income Audit function is set to Y.
• CLOSED-- After closing the business date.

Tables Involved in the End of Day Sequence

BUSINESSDATE Table

When the Posting Room and Tax procedure starts, the MANDATORY_PROCS_PROGRESS column in this table will be updated to PROGRESS for the business date for which the End of Day Sequence is running. This prevents the user from starting any other post room and tax process. If the user gets blocked,, Support needs to kill the job and update the value PROGRESS to ERROR for that business date. Caution is suggested while doing this, as updating of this value to anything other than ERROR could result in serious problems. All the updates should be followed with a Commit.

NIGHT_AUDIT_CHECK Table

This table stores the information about each reservation, whether the Post Room and Tax, Posting Fixed Charges, Posting Packages, Posting Package Profit/Loss is complete.

NIGHT_AUDIT_JURNAL Table

This table stores the times of all the procedures and their running statuses. If a procedure fails, this would be again picked up by the End of Day Sequence until the status of those procedures turns to COMPLETE. The ITERATION column tells how many times the procedure has run to become COMPLETE. SYS_JOB_ID column is a unique value generated for each insert. The different statuses that a procedure can have are RUNNING (Currently running), FAILED (Failed), and COMPLETED (Successfully completed).

NIGHT_AUDIT_JURNAL_DETAILS Table

This would insert records, if the Post Room and Tax procedure encounters errors. If you want to see the errors generated by one procedure, you can issue the following statement after replacing the proper sys_job_id from the night_audit_jurnal table.

Select * from night_audit_jurnal_details where sys_job_id = 15246;

NIGHT_AUDIT_FOR_TODAY (view)

The purpose of this table is to determine what reports/ procedures exist for a particular day. Initialization for a day is required to access proper information from this view.

Troubleshooting Tips and Tricks for End of Day Sequence

• If you are using SQL Navigator, it is not required to end the command with a semicolon (;).
• If you are using SQL Plus, it is required to terminate the command with a semi-colon (;) to get the result.

How to Initialize in a SQL Navigator /SQLPLUS Session

The following statement is used if you want to initialize to a particular date. This would be useful if you want to run a procedure from SQL Navigator for a date in the past. FDH in this following statement is the property code. Please substitute your resort code for FDH and the date in question.

begin
pms_p.initialize('NA_REPORTS','NA_REPORTS','FDH', 10-jan- 2001);

end;

If the User Thinks the Session is Hanging

Logon to SYSSTAT and check the session for blocking locks that can be resolved. This may take a few minutes. If it does not get resolved, escalate the issue.

How to Kill a DBA_JOB

Identify the session in which the job is running. Issue the two-step statements above to get the SID and serial number. Issue the following statement to kill the session. When you perform the following steps, allow the system some time (510 minutes) to take care of the sessions clean up.

Begin
Dbms_job.remove(<job_no>);

END;

If the above statement does not resolve the problem, proceed to kill the session using the following statement (where 12 = SID and 12312 = serial#).

Alter system kill session 12,12312;

If you kill a job while the End of Day Sequence is posting room and tax and the user gets stopped, Support will need to kill the job and update the value ‘Progress’ to ‘Error’ for that business date. Caution is advised while doing this as the wrong updates of this value could result in serious problems and jeopardize the integrity of the property’s financial data. All the updates should be followed with a Commit.

If you kill a job while procedures are running, it would make the current procedure fail and the application would proceed with other procedures.

In OPERA V2.0 and higher, Support does not need to update the business date as the program recognizes if the process is already running, and it would go to the respective Action Status screens if the Post Room and Tax procedure is in progress, if Procedures are in progress, or if Reports are in progress.

Error Message is Displayed when Starting the Night Audit

When initiating a Night Audit, if certain java components, IP Addresses, Virtual IP Address, etc. are not configured properly and user sessions cannot be disconnected, this will prevent the Night Audit from completing successfully. When this occurs, OPERA will display a message "Opera application is not configured properly for the Night Audit. Do you want to see the error?" Select Yes to display the error and details related to it and have the option to create and export file of these errors. Select No to continue with the Night Audit process.

How to Determine the Duration of the Procedures

In the following statement, replace the resort and business_date with proper values. The result from the SQL statement in the TIMETAKEN column will be shown in seconds.

Select job_name, (end_time-start_time)*24*60*60 TIMETAKEN, to_char(START_time,HH24:MI:SS) st,
to_char(end_time,HH24:MI:SS) en, status

From night_Audit_jurnal

Where resort = PHGBP (replace the PHGBP with the property code of your resort).

And business_date = 25-apr-01 (replace 25-apr-01 with the business date for the End of Day Sequence being run).

Order by sys_job_id

A Procedure is Failing Multiple Times, You Know you can Run it Later, and You Need to Proceed with the End of Day Sequence

• Contact the property for their consent to skip the procedure and run it later. If they agree, proceed with the following steps.
• Update the Status column in Night_Audit_Jurnal to COMPLETED for the sys_job_id and for the latest iteration number.
• Run the End of Day Sequence again.

When the End Of Day Sequence Hangs before Starting Post Room and Tax or Before Starting to run the Procedures

• Select * from v$parameter where name like '%job_queue_process%';
• Note the value column.
• Select * from dba_jobs_running;
• If the value from Step2 is equal to the total rows in Step 3, then please ask your Oracle DBA to increase the number of jobs queue processes. If you are authorized to issue the command yourself, issue the following command.
• Alter system set job_queue_processes = <increase the current value by 3>. This problem has been addressed by not allowing the End of Day Sequence to proceed if the number of job processes is not sufficient.

If You Receive an ORA-302000 Error during Reprint

This error indicates that you do not have write permissions to the directory. While reprinting the report OPERA creates one file in the specified directory. Make sure that the directory that you indicated allows you to create a file from your workstation.

How to Flush the Shared Pool from SQL Navigator / SQL Plus?

Go to the DOS Command prompt and enter the following SQL statement:

ALTER SYSTEM FLUSH SHARED POOL

Printing Problems with the End of Day Sequence

Following is a checklist to investigate the Setup and Configuration of the Application Server and Print Server:

• Check the printer setup is correct in the printers form.
• Point to System Configuration>Setup>Printers. There should be an entry for the printer you are troubleshooting. Select to Edit this record.
• Ensure the Physical Device field has a proper printer name.
• Ensure the Print Server name is correct.
• Ensure the print server procedure is started. In Windows Explorer, go to the PrintServer folder.

These are the files that are required to be in the PrintServer directory for the printserver to start:

• HTMLDBServer.class
• PrintClient.class
• Classes12.zip
• HTMLServer.class
• HTMLDBService.class

If the files are not there, perform the following steps:

• Create a Folder called PrintServer.
• Copy all the Class files from the \runtimes directory to the PrintServer folder as well as the classes12.zip file.
• Copy the OPERAPrint.conf.sample file to the PrintServer folder but rename it to OPERAPrint.conf.

Also make sure the following steps are completed and correct:

• Edit the OPERAprint.conf file and make necessary changes. You will need to change the Server name for your site.
• Go to the DOS Command prompt.
• Go to the PrintServer directory.
• Run the following commands (this will start the Print Server Receiving end).

set CLASSPATH=d:\printserver

java HTMLServer operaprint.conf start (This is the command to start the printserver)

Are the Reports Being Printed after Performing the Above Commands

If not then a small test command can be tried from the command prompt. Type the following test command:

jre -cp "d:\printserver;d:\printserver\classes12.zip" PrintClient APARNAC http://pmsweb/webtemp/abc.txt

"\\NPLDOMNW2S003\LANIER211" 23 pms_dev pms_dev purdue:1521:ohopera OPERAM SUPERVISOR

Parameters Explanation for above test command:

Parameter	Explanation
-cp	Sets the CLASSPATH on the fly. We can get to the runtimes directory using the registry key OPERARUNTIMES which we set as part of the installation.
PrintClient	The class file that contains the Main Method and opens the Socket communication.
APARNAC	Machine name where the Print Receiver END was started. For testing we can use the same machine or different ones.
http://pmsweb/webtemp/abc.txt	File name in URL form. This can be a txt file or a .pdf file.
\\NPLDOMNW2S003\LANIER211	Printer device where the output will be sent.
23	SID of the session of the user submitting the print job.
pms_dev pms_dev	Schema name and schema password from where user is printing.
purdue:1521:ohopera	Connect string to establish a connection with the Java program. Purdue is HostName, 1521 is Port No., and ohopera is the oracle database name.
OPERAM	The application name of the hotel from which the user is printing.
SUPERVISOR	User ID of the person logged into the application.

The last five parameters (23, pms_dev pms_dev, purdue:1521:ohopera, OPERAM, OPERA) are required to process the messages so Broadcast Messaging displays messages to the correct user.

This will insert messages into the message_bin table in pms_dev schema on ohopera.

The SQL statement below will show the messages that have been inserted with possible problems:

Select * from message_bin

All the logging messages into the database is done by the PrintClient.class file.

Is the Reports Queue Table Set Up?

There are a few objects required for the Reports Queue Manager to accept a report request.

The RW_QUEUE_SERVER table needs to exist.

In SQL, check the contents of this table. At the SQL prompt, type desc rw_server_queue, and your screen should return the following information.

SQL> desc rw_server_queue

Name	Null?	Type
JOB_TYPE		VARCHAR2(2000)
JOB_ID		NUMBER
JOB_NAME		VARCHAR2(2000)
STATUS_CODE		NUMBER
STATUS_MESSAGE		VARCHAR2(2000)
COMMAND_LINE		VARCHAR2(2000)
OWNER		VARCHAR2(2000)
DESTYPE		VARCHAR2(2000)
DESNAME		VARCHAR2(2000)
SERVER		VARCHAR2(2000)
QUEUED		DATE
STARTED		DATE
FINISHED		DATE
LAST_RUN		DATE
NEXT_RUN		DATE
REPEAT_INTERVAL		NUMBER
REPEAT_PATTERN		NUMBER
CACHE_KEY		VARCHAR2(2000)
CACHE_HIT		NUMBER(1)

The Status_Code field has values from 1-8, which correspond to the following:

• FINISHED -4
• RUNNING - 2
• CANCELLED
• OPENING_REPORT - 1
• ENQUEUED
• INVALID_JOB
• TERMINATED_WITH_ERROR -5
• CRASHED

For every report you run there will be one record in this table.

Setup Using Servlets on Your Application Server

Locate the default.env file in the //applicationserver/forms90/server directory. This has to be set with the path for the JRE l exactly like the following example:

ORACLE_HOME=d:\opera\10gdev

PATH=d:\opera\10gdev;d:\opera\10gdev\jre11\bin

FORM90_PATH=d:\runtimes;d:\runtimes\gifs

CLASSPATH=d:\runtimes

OS=WINDOWS

If this is missing, your reports will not be sent to the printer. The report will run but since the path to the JRE is not set up, the output will not be sent to the printer.

Questions to Ask when Troubleshooting Printing in End of Day Sequence

• Is the printer setup done correctly? (As explained above- check the setup).
• Does the printer_task NA_REPORTS have a printer attached?
• Is the print server started?
• Is a printer attached to the print server machine?
• Did the .ctl, and .bat files being generated?
• Check if there are any duplicate opera_print.bat files in the path?
• Check if the print server is accepting any requests?

Differences between End of Day Sequence Reports, Reprint, Regenerate

End of Day Sequence Reports. These run during End of Day Sequence and get printed automatically based upon configuration of your End of Day Sequence.

End of Day Sequence Reprint. This happens when you go to NAREPRINT form and click on the Print button. The request that was completed during the End of Day Sequence will be changed to PENDING again. CCMAN will then pick them up, print the same output that was generated during End of Day Sequence, and run the same request command. The End of Day Sequence hard copy report and End of Day Sequence reprint of the same report will never be different.

End of Day Sequence Regenerate. This will regenerate the batch file, regenerate the parameters file .ctl and will rerun the report with those parameters. Any changes to data made after the original audit that affect this date will show up on a regenerated report.

Note: The End of Day Sequence reports will be stored on the application server under the Export folder. This directory should be on the application server in the path d:\micros\opera\export\schema name\opera1\audit.