EOD

1. Night Audit Procedures Scripts

1.1 Script to run night audit procedures manually

BEGIN
PMS_P.INITIALIZE(IN_USER=> 'NA_REPORTS',
IN_PASSWORD=> 'NA_REPORTS',
IN_RESORT=> 'RESORTCODE', --Replace the resort Code
IN_BUSINESS_DATE=> 'DD-MON-YY'); --Replace 'DD-MON-YY' with the the date of the EOD/NA causing issue like '01-JAN-12'
Begin ProcedureName ; --Replace ProcedureName with one of the following procedures
End;
END;

This script may be used for the following procedures:
- SC_ACTIVITY.GENERATE_ACTIVITIES
- BOF_PACKAGE.EXPORT_BOF_DATA
- GENERATE_EXPORT_NA_DATA
- RATE_STRATEGY_PKG.SET_RATE_STRATEGY
- MEMBERSHIP_P.CREATE_DAILY_MEMBERSHIP_TRAN
- PMS_PRFORM00.UPDATE_PROFILE_RESV_STATISTICS
- UPDATE_STATISTICS.UPDATE_STATISTICS_MAIN
- REFRESH_FIN_SUMMARY.POPULATE_TRIAL_BALANCE
- REFRESH_FIN_SUMMARY.REFRESH_RESV_SUMMARY
- PMS_GRGRID.NIGHT_ALLOTMENT_CUTOFF

The following NA procedures should be checked differently however:
- CREATE FILE FOR GENERAL EXPORT
- CREATE FILE FOR BACK OFFICE EXPORT

For issues with these EXPORT Procedures, instead check the files they are creating, i.e. BOF Export file and General Export file.

Running the NA procedure manually would, for these procedures, trigger the CREATE FILE FOR GENERAL EXPORT or the CREATE FILE FOR BACK OFFICE EXPORT:

BOF_PACKAGE.EXPORT_BOF_DATA will trigger CREATE FILE FOR BACK OFFICE EXPORT
GENERATE_EXPORT_NA_DATA will trigger CREATE FILE FOR GENERAL EXPORT

If a mandatory procedure did not complete gracefully, the SR must not be closed.

Support should contact the property for their consent to skip the procedure and run it later.

Support should keep the SR open to troubleshoot why the procedure failed and ensure that the procedure is run manually before it is closed.

1.2 Set Trace File Size to Unlimited

This is done before tracing the Night Audit.

alter session set max_dump_file_size=unlimited;

1.3 Run a Trace While Running an EOD Procedure Manually

The below script is used to run a Night Audit Procedure manually - run a trace while doing so:

BEGIN
PMS_P.INITIALIZE(IN_USER=> 'NA_REPORTS',
IN_PASSWORD=> 'NA_REPORTS',
IN_RESORT=> 'RESORTCODE', --Replace the resort Code
IN_BUSINESS_DATE=> 'DD-MON-YY'); --Replace date with the the date of the EOD/NA cauing issue like '01-JAN-12'
dbms_monitor.session_trace_enable(null,null,true,true);
begin <ProcedureName>; --Replace the <ProcedureName> wby one of the following procedures
end;
dbms_monitor.session_trace_disable;
END;
/

1.4 Restore Trace File Size to Default

This script will set the trace file size back to the default value after tracing Night Audit is done:

alter session set max_dump_file_size=4096;

Always restore the max_dump_file_size to its original size after your test!

2. Business Date Scripts

2.1 Locate Dates In Businessdate Table Which Are Not Closed

Select the dates in the businessdate table which are not CLOSED, for one resort:

Select * from businessdate where state!='CLOSED' and resort='XXXXX'

TIP: If a date has a different status than closed, it means that the date is opened or the EOD procedure is running for this date. Please see Night Audit Troubleshooting Guide [Document ID 2360525.1] for more information.

2.2 Check How Many Properties in Multiproperty Environment Have Business Dates Not Closed

Multiproperty script to see the number of resort with business date not closed:

Select count(*),business_Date from businessdate where state!='CLOSED' and resort in(select resort from installed_app_licenses where product_code='OPE_500'and active_yn='Y') group by business_Date order by business_Date

2.3 Check How Many Properties in Multiproperty Environment Have Business Dates Not Closed / Not Open in Last Two Days

Multiproperty script to see the number of resort with business date not closed and not open for the last two days:

Select count(*),business_Date from businessdate where usiness_Date>=TRUNC (SYSDATE-2) and state!='OPEN' and state!='CLOSED' and resort in(select resort from installed_app_licenses where product_code='XXX'

Change the product code to match the licence installed

2.4 Use Business_Date to Identify SYS_JOB_ID from NIGHT_AUDIT_JURNAL_DETAILS Table

Use the business_date to identify the resv_name_id by checking the NIGHT_AUDIT_JURNAL_DETAILS table the procedures which have been run for the date:

select A.resort,B.sys_job_id,A .Job_name,A.Business_date,B.remarks from night_audit_jurnal A, night_audit_jurnal_details B
where A.sys_job_id=B.sys_job_id
and A.business_date='DD-MOM-YYYY'

Change the date with the date of the EOD/NA issue

3. Update Reservation Status

3.1 Script to Update Reservation to 'CHECKED OUT' Status in RESERVATION_NAME table

Update the resv_status of the reservation in RESERVATION_NAME to 'CHECKED OUT':

Update RESERVATION_NAME set resv_status='CHECKED OUT' where resv_name_id='YYYYYYYYY' and resort='ZZZZZZZZ'

Update 'YYYYYYYYY' with your resv_name_id and 'ZZZZZZZZ' with your resort code

4. NIGHT_AUDIT_CHECK Scripts

4.1 Identify Rooms Where 'Post Room and Tax' is Not Completed

The following script will identify the rooms for which the "Post Room & Tax" step is not completed, i.e. the row(s) where the value is 'N'

Select * from NIGHT_AUDIT_CHECK where resort='XXXXX' --Change the XXX for the resort code
and business_date='DD-MON-YYYY' --Change the business_Date like '01-JAN-2013'

Understanding the Output:

A completed procedure shows the value 'Y' in its column
A procedure which is not completed show the value 'N' in its column
Posting Room and Tax: 'Room_P_YN' column
Posting Fixed Charges: 'Fix_Charges_P_YN' column
Posting Packages: 'Package_P_YN' column
Posting Profit and Loss: 'Profit_Loss_P_YN' column

4.2 Find Reservations Where Post Room and Tax / Fixed Charges / Packages / Profit & Loss

Find Any reservation where one of the above colunms has not been completed:

Select * from NIGHT_AUDIT_CHECK where resort='XXXXX' --Change the XXX for the resort code
and business_date='DD-MON-YYYY' --Change the business_Date like '01-JAN-2013'
and (Room_P_YN='N' or Fix_Charges_P_YN='N' or Package_P_YN='N' or Profit_Loss_P_YN='N')

4.3 Update NIGHT_AUDIT_CHECK Table

Update night_audit_check

WARNING: This script should be applied with caution and only for specific issues, e.g. posting package failing on a specific room type.

update night_audit_check set room_p_yn='Y', fix_charges_p_yn='Y', package_p_yn='Y', profit_loss_p_yn='Y' where business_date='DD-MON-YYYY' and room_p_yn='N' and resort='XXXXX' --Change the XXX for the resort code

5. Night Audit Jurnal and Night Audit Jurnal Details

5.1 Scripts to Check Night Audit Procedures / STATUS / SYS_JOB_ID / PROCEDURE TIME / ITERATION

Scripts to see NA procedures, the status, the sys_job_id, the time procedure, the iteration:

SELECT Resort,module_id,business_date,job_name,iteration,sys_job_id,run_by_user_id,status,TO_CHAR(start_time, 'HH24:MI'),TO_CHAR(end_time, 'HH24:MI'),(end_time-start_time)*24*60 proc_time
FROM night_audit_jurnal
WHERE business_date >= TRUNC (SYSDATE - 5) --Change the date like sysdate-10= 10days before the current date
AND resort='XXXX' --Change the resort Code
order by sys_job_id

The below scripts puts the procedures in order, slowest first:

Data from the NIGHT_AUDIT_JURNAL table is purged after 12 days

5.2 Script to Check NIGHT_AUDIT_JURNAL_DETAILS TABLE for Errors

The script will show if there is any error message linked to a NA procedure which is not completed:

select * from night_audit_jurnal_details
where sys_job_id in (select sys_job_id from night_audit_jurnal
where business_date='DD-MMM-YYYY' --Change the date with the date of the EOD/NA issue like '01-JAN-2012'
and status!='COMPLETED')

5.3 Check Last Error Logged During End of Day

Run the below script to see the last error message logged during the EOD. The table is for the whole schema, there might be some error message from another site in the table.

select * from night_audit_jurnal_details order by sys_job_id desc

This query will return results for the entire schema, not just one property.

5.4 Identify Errors in NIGHT_AUDIT_JURNAL_DETAILS By BUSINESS_DATE

Use the business_date to identify if there was an error message in the NIGHT_AUDIT_JURNAL_DETAILS table against the procedures which have been run for the date:

select A.resort,B.sys_job_id,A .Job_name,A.Business_date,B.remarks from night_audit_jurnal A, night_audit_jurnal_details B
where A.sys_job_id=B.sys_job_id
and A.business_date='DD-MOM-YYYY' --Change the date with the date of the EOD/NA issue

5.5 Script to Update the Night_AUDIT_JURNAL TABLE

WARNING: Before running the script make sure you understand the consequence. There is no way back. NEVER RUN THIS SCRIPT IN Post Room & Tax!

If you are updating any mandatory procedures because it has failed multiple times, you can run it later and you need to Proceed with the End of Day Sequence if it is OK to set the job to 'COMPLETED'.

If a mandatory procedure did not complete gracefully, the SR must not be closed.

Support should contact the property for their consent to skip the procedure and run it later.

Support should keep the SR open to troubleshoot why the procedure failed and ensure that the procedure is run manually before it is closed.

STEP ONE: Find the NA job procedure that needs to be completed

select resort,business_date,job_name,sys_job_id,status from NIGHT_AUDIT_JURNAL where resort='XXXX' and business_date= 'DD-MON-YYYY' and status!='COMPLETED'
--Change the resort Code 'XXXX', the business_date like '01-JAN-2013'

STEP TWO: Update the NA job procedure to completed

update NIGHT_AUDIT_JURNAL set status='COMPLETED' where resort='XXXX' and business_date= 'DD-MON-YYYY' and status!='COMPLETED' and sys_job_id='XXXXX' --Change the resort Code 'XXXX', the business_date like '01-JAN-2013'and add the sys_job_id='XXXXX' found above