previous

Net Room Rate Tax Calculation

A function (jap_tax_udf.fun) handles the special calculation rules for Japan's taxes.

This function calculates the Net Rate Amount (less tax, service, package elements) to decide the amount of Accommodation Tax to charge:

The UDF function called jap_tax_udf (RESV_NAME_ID) is used as Add to Rate Separate Line Package attached to a rate code, is calculated and posted to a guest's folio for Night Audit or manual rate code postings.

Below is a copy of the function:

CREATE OR REPLACE

FUNCTION jap_tax_udf(RESV_NAME_ID IN NUMBER, V_SVC IN NUMBER DEFAULT 10) RETURN NUMBER IS

v_resort resort.resort%type;

v_date DATE;

cursor get_resv(in_resv_name_id number) is

select den.adults, den.children, den.rate_code, den.share_amount, de.booked_room_category room_category,

(TRUNC(rn.end_date,'DD')-TRUNC(rn.begin_date,'DD')) nights, de.allotment_header_id

from reservation_daily_elements de, reservation_daily_element_name den, reservation_name rn

where de.resv_daily_el_seq = den.resv_daily_el_seq

and de.resort = den.resort

and de.reservation_date = den.reservation_date

AND rn.resv_name_id = in_resv_name_id

and den.resort = v_resort

and den.reservation_date = v_date

and den.resv_name_id = in_resv_name_id

and den.resort = rn.resort

and den.resv_name_id = rn.resv_name_id;

CURSOR c_trxCodes(in_trx_code IN VARCHAR2) IS

SELECT NVL(tax_inclusive_yn,'N') tax_inclusive_yn

FROM trx_codes

WHERE trx_code=in_trx_code;

in_resv_name_id reservation_name.resv_name_id%TYPE := resv_name_id;

ret_val number:= 0;

v_resv_rec get_resv%ROWTYPE;

v_trx_code VARCHAR2(20);

v_tax_trx_code VARCHAR2(20);

v_pkg_tax_inc_yn VARCHAR2(1);

v_room_tax_inc_yn VARCHAR2(1);

v_pax NUMBER:=0;

v_inc_tax_amount NUMBER:=0;

v_net_amount NUMBER:=0;

v_price NUMBER:=0;

v_out_net_room NUMBER:=0;

base_amount NUMBER:=0;

v_temp_amt NUMBER:=0;

v_called_from_mrt boolean := (nvl(fintrx_procedures.get_action_event(fin_trx_i.Current_tran_action_id), '@@@') = 'MRT' );

BEGIN

gem.trace_on('jap_tax_udf');

base_amount := nvl(fin_trx_i.get_base_for_udf, 0);

v_resort := pms_p.resort();

v_date := nvl(fin_trx_i.get_date_for_udf, nvl(get_rates.get_current_date, pms_p.business_date));

OPEN get_resv(in_resv_name_id);

FETCH get_resv INTO v_resv_rec.adults, v_resv_rec.children, v_resv_rec.rate_code, v_resv_rec.share_amount,

v_resv_rec.room_category, v_resv_rec.nights, v_resv_rec.allotment_header_id;

IF get_resv%notfound then

v_resv_rec.adults:=nvl(fin_trx_i.g_adults_for_udf,1);

END IF;

CLOSE get_resv;

IF in_resv_name_id is NOT NULL AND v_called_from_mrt THEN

v_resv_rec.share_amount := base_amount;

v_resv_rec.adults := nvl(fin_trx_i.g_adults_for_udf,1);

v_resv_rec.children := 0;

END IF;

v_pax:=NVL(v_resv_rec.adults,0)+NVL(v_resv_rec.children,0);

fintrx_procedures.GetPkgTrxCodes( in_resort => pms_p.resort(),

in_rate_code => v_resv_rec.rate_code,

out_pkg_trx => v_trx_code,

out_tax_trx => v_tax_trx_code,

out_pkg_tax_incl_yn => v_pkg_tax_inc_yn,

out_room_tax_incl_yn => v_room_tax_inc_yn );

IF NVL(v_room_tax_inc_yn,'N')='Y' THEN

fin_trx_i.set_called_to_get_net_room_amt('Y');

IF in_resv_name_id IS NOT NULL THEN

fin_tran_view_ref.get_eff_pkg_amts_latest( in_resv_name_id => in_resv_name_id,

in_amt => v_resv_rec.share_amount,

in_rate_code => v_resv_rec.rate_code,

new_date => v_date,

out_pkg_debit => v_out_net_room,

out_pkg_credit => v_temp_amt,

in_room_category => v_resv_rec.room_category,

in_no_of_days => v_resv_rec.nights,

in_adults => v_resv_rec.adults,

in_children => v_resv_rec.children,

in_resort => pms_p.resort(),

in_allotment_header_id => v_resv_rec.allotment_header_id);

ELSE

v_out_net_room := base_amount;

END IF;

fin_trx_i.set_called_to_get_net_room_amt('N');

v_inc_tax_amount:= pkg_rate_price.Calculate_tax_for( in_amt => v_out_net_room,

in_trx_code => v_tax_trx_code,

in_resv_name_id => in_resv_name_id,

in_tax_incl => v_room_tax_inc_yn,

in_resort => pms_p.resort(),

in_exclude_udf_functions => lower($$PLSQL_UNIT) );

v_net_amount:=NVL(v_out_net_room,0)-NVL(v_inc_tax_amount,0);

ELSE

fin_trx_i.set_called_to_get_net_room_amt('Y');

IF in_resv_name_id IS NOT NULL THEN

fin_tran_view_ref.get_eff_pkg_amts_latest( in_resv_name_id => in_resv_name_id,

in_amt => v_resv_rec.share_amount,

in_rate_code => v_resv_rec.rate_code,

new_date => v_date,

out_pkg_debit => v_out_net_room,

out_pkg_credit => v_temp_amt,

in_room_category => v_resv_rec.room_category,

in_no_of_days => v_resv_rec.nights,

in_adults => v_resv_rec.adults,

in_children => v_resv_rec.children,

in_resort => pms_p.resort(),

in_allotment_header_id => v_resv_rec.allotment_header_id);

ELSE

v_out_net_room := base_amount;

END IF;

fin_trx_i.set_called_to_get_net_room_amt('N');

-- No package is attached, look at the configuration for the transaction code to calculate inclusive/exclusive taxes.

/* IF v_tax_trx_code IS NULL THEN

v_tax_trx_code :=fin_trx_i.get_parameter('DFLT_TRAN_CODE');

OPEN c_trxCodes(in_trx_code=>v_tax_trx_code);

FETCH c_trxCodes INTO v_room_tax_inc_yn;

CLOSE c_trxCodes;

END IF;

v_inc_tax_amount:= pkg_rate_price.Calculate_tax_for( in_amt => v_out_net_room,

in_trx_code => v_tax_trx_code,

in_resv_name_id => in_resv_name_id,

in_tax_incl => v_room_tax_inc_yn,

in_resort => pms_p.resort(),

in_exclude_udf_functions => 'jap_tax_udf' );

v_net_amount:=NVL(v_out_net_room,0)-NVL(v_inc_tax_amount,0); */

v_net_amount := NVL(v_out_net_room,0);

END IF;

-- add back the V_SVC service charge

v_net_amount:=floor(v_net_amount*(1+V_SVC/100)); -- add rounded down service charge

-- calculate the tax based on rate amount

IF NVL(v_pax,0)<>0 THEN

IF ( v_net_amount/v_pax )>=15000 THEN

v_price:=200 * v_pax;

ELSIF ( v_net_amount/v_pax )>=10000 THEN

v_price:=100 * v_pax;

ELSE

v_price:=0;

END IF;

END IF;

fintrx_generate.SET_UDF_RETURN_TYPE('F');

gem.trace_off();

RETURN nvl(v_price,0);

EXCEPTION

WHEN OTHERS THEN

gem.trace_off();

fin_trx_i.set_called_to_get_net_room_amt('N');

RETURN 0;

END jap_tax_udf;