Reporte Trial Balance Details By Transactions en Opera PMS

En este reporte se muestra el detalle de todas las transacciones posteadas al huesped y la clasificación de estas transacciones.

Debido a que los términos del Balance no se pueden traducir de manera directa al español sin desnaturalizar su significado mantendré en este blog las definiciones en inglés.

The Trial Balance reprt includes all transactions posted, and then a breakdown of business within each of the five Ledgers (Guest Ledger, AR Ledger, Deposit Ledger, Package Ledger, and Inter Hotel Ledger). The Trial Balance may be used to assist in ensuring your property’s accounts are balanced and in good standing.

Summary of others Reports That May Be Used For Balancing

Report Title.REP FilenameAdditional Information
Aging Summary by TypesaragingMay be run for a specific date.
Detailed Aging ReportaragingdetMay be run for a specific date.
AR LedgerarledgerContinually updating
Deposit Activity Reportdeposit_activityMay be run for a specific date but should be run by Activity Date if used for balancing.
NA Deposit Ledgerdeposit_ledgerContinually updating.
Financial Payments and Revenue PostingsfindeptcodesMay be run for a specific date but should always be run for NET revenue if used for balancing.
Journal by Cashier and Transaction CodefinjrnlbytransMay be run for a specific date but should always be run for NET revenue if used for balancing.
Financial Transactions with Generatesfinjrnlbytrans2May be run for a specific date but should always be run for NET revenue if used for balancing.
Package PostingsfinpkgpostingCaptures data for the current business date, must be attached to Night Audit Report Group if used for balancing.
Guest Trial Balancegl_trial_balanceMay be run for a specific date.
Manager Reportmanager_reportMay be run for a specific date.
Deposits Transferred at C/InadeptransciCaptures data for the current business date, must be run with the Night Audit if used for balancing to trial_balance.
Package Ledger DetailnapkgledgerCaptures data for the current business date, must be run with the Night Audit if used for balancing to trial_balance.
Trial Balancetrial_balanceMay be run for a specific date.

El Trial Balance Detail posee la siguiente estructura:

Room. Room number of the reservation.

Guest Name. Name of the guest.

Arragement Desc. Name of transaction clasification.

Trx Code. Code Transaction

Trx No. Transaction secuencial number.

Description. Transaction description.

Old Balance (Opening Balance). Total balance of the guest account for the date in which the report was ran.

Credits. Total Amount that has been paid against the account by the guest.

Debits. Total Amount that has been charged to the guests account.

Transferred Credits. Any amounts that have been transferred to the guests account and that is considered a credit.

Transferred Debits. Any amounts charged to the guest account as a result of a transfer.New Balance (Closing Balance). Closing balance for the account. This column is calculated by the following formula: Opening Balance + Debits + Transfer Debit – Credits – Transfer Credits.

La consulta SQL para Oracle:

SELECT t.room,t.guest_name,t.arrangement_desc,
t.trx_code,t.trx_no,
t.description,
t.old_balance, t.credit,t.debit,t.transfer_credit,t.transfer_debit,t.new_balance
FROM(
SELECT
fs.trx_no,
ar.arrangement_desc,
fs.resv_name_id,
fs.name_id,
fs.resort,
rv.room,
rv.guest_name,
co.trx_code,
co.description,
-- all GL postings from days prior to p_from_date
DECODE(abs(trunc(to_date(to_char(to_date(to_char(pms_p.business_date))))) - trunc(fs.trx_date)),
0,0,
-1,0,
(NVL(fs.guest_account_debit,0))- (NVL(fs.guest_account_credit,0))) old_balance,
-- all GL credits posted on p_from_date
DECODE(fs.from_resv_id,NULL,decode(pms_p.business_date - trunc(fs.trx_date),
0,nvl(fs.guest_account_credit,0),
0),
0) credit,
-- all GL debits posted on p_from_date
DECODE(fs.from_resv_id,NULL,decode(pms_p.business_date - trunc(fs.trx_date),
0,nvl(fs.guest_account_debit,0),
0),
0) debit,
-- all GL transfer credits from other reservations posted on p_from_date
DECODE(fs.from_resv_id,NULL,0,
decode(pms_p.business_date - trunc(fs.trx_date),
0,nvl(fs.guest_account_credit,0),
0)) transfer_credit,
-- all GL transfer debits from other reservations posted on p_from_date
DECODE(fs.from_resv_id,NULL,0,
decode(pms_p.business_date - trunc(fs.trx_date),
0,nvl(fs.guest_account_debit,0),
0)) transfer_debit,
-- all GL postings posted on p_from_date or before
DECODE(abs(pms_p.business_date - trunc(fs.trx_date)),
-1,0,
NVL(fs.guest_account_debit,0) - NVL(fs.guest_account_credit,0)) new_balance
FROM financial_transactions_view fs
inner join rep_reservation_all_view rv on (fs.resv_name_id = rv.resv_name_id)
inner join trx$_codes co on (co.trx_code=fs.trx_code)
inner join trx$_code_arrangement ar on (ar.arrangement_id=co.arrange_code)
where fs.resv_name_id IN
(-- all resv_name_ids that had a posting today
SELECT resv_name_id
FROM financial_transactions ftx
WHERE ftx.resort = 'RESORT'
AND ftx.trx_date = pms_p.business_date
AND ftx.resv_name_id IS NOT NULL
UNION ALL
-- all reservations that were (supposed to be) in-house today
SELECT resv_name_id
FROM reservation_daily_elements rde,
reservation_daily_element_name rdn
WHERE rde.resort = 'RESORT'
AND rde.reservation_date = pms_p.business_date
AND rdn.resort = rde.resort
AND rdn.reservation_date = rde.reservation_date
AND rdn.resv_daily_el_seq = rde.resv_daily_el_seq
UNION ALL
SELECT resv_name_id
FROM reservation_name rn
WHERE rn.resort = 'RESORT'
AND rn.post_co_flag = 'O'
)
AND fs.trx_date <= pms_p.business_date
AND (fs.guest_account_credit IS NOT NULL OR fs.guest_account_debit IS NOT NULL)
and fs.resv_name_id=3142559 and rv.room='0206'
)t
ORDER BY t.room,t.arrangement_desc,t.trx_code,t.trx_no


Es importante comentarles que al igual como hicimos en HouseKeeping para mostrar el estado de las habitaciones desde el smartphone se puede desarrollar reportes analiticos en base al Trial Balance para tener en línea el estado de los ingresos.