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;