<%@ page language="PL/SQL" %> <%@ page errorPage="Hrs_Error_Page.psp" %> <%@ plsql procedure="room_availability_details" %> <%@ plsql parameter="p_hotel_id" type="NUMBER" %> <%! /** Overview : This PSP is part of the HRS application sample. The PSP displays the details Room availability details for the Hotel id passed in. Modification History: Person Date Comments ------------------------------------------------------------- Srinivas Ramarao 10-AUG-2000 Initial Sample **/ /* Cursor to select the Hotel Name */ CURSOR c_hotels(cp_hotel_id IN NUMBER) IS SELECT name FROM hotels WHERE id = cp_hotel_id; /* Cursor to get the room types for the hotel id passed in */ CURSOR c_room_types(cp_hotel_id IN NUMBER) IS SELECT room_type,standard_rate,currency FROM available_room_types WHERE hot_id = cp_hotel_id; /* Cursor to fetch the room totals for the hotel id passed in */ CURSOR c_room_totals(cp_hotel_id IN NUMBER) IS SELECT total_sglb,total_dble,total_qeen,total_king,total_suit,total_othr FROM room_availability WHERE hot_id = cp_hotel_id; /* Variable Declarations */ l_ra_rec c_room_totals%ROWTYPE; l_h_rec c_hotels%ROWTYPE; %> PSP Sample - Room Availability Details
<% /* Fetch Hotel Name for display in Header */ OPEN c_hotels(cp_hotel_id => p_hotel_id); FETCH c_hotels INTO l_h_rec; CLOSE c_hotels; /* Display Header part */ %>
The List Of Room Types in Hotel <%= l_h_rec.name %>
On <%= TO_CHAR(SYSDATE,'Day Mon dd yyyy') %> <%= ' At ' %> <%= TO_CHAR(SYSDATE,'hh24:mi:ss') %>


<% /* Fetch required details into local variables for display */ OPEN c_room_totals(cp_hotel_id => p_hotel_id); FETCH c_room_totals INTO l_ra_rec; IF c_room_totals%NOTFOUND THEN /* Room Availability info not available. So, Display Message. */ %>
Room details Not Available. Please create the Room Types First!

<% /* Close the Cursor */ CLOSE c_room_totals; /* Exit the procedure so that rest of the code is not executed */ RETURN; END IF; CLOSE c_room_totals; %> <% /* Loop through all the room types and display the respective details */ FOR l_rt_rec IN c_room_types(cp_hotel_id => p_hotel_id) LOOP /* Display the room type information */ %> <% END LOOP; %>
Room Type Standard RateCurrencyAvailable Rooms
<%= l_rt_rec.room_type %> <%= l_rt_rec.standard_rate %> <%= l_rt_rec.currency %> <% IF l_rt_rec.room_type = 'SGLB' THEN %> <%= l_ra_rec.total_sglb %> <% ELSIF l_rt_rec.room_type = 'DBLE' THEN %> <%= l_ra_rec.total_dble %> <% ELSIF l_rt_rec.room_type = 'QEEN' THEN %> <%= l_ra_rec.total_qeen %> <% ELSIF l_rt_rec.room_type = 'KING' THEN %> <%= l_ra_rec.total_king %> <% ELSIF l_rt_rec.room_type = 'SUIT' THEN %> <%= l_ra_rec.total_suit %> <% ELSIF l_rt_rec.room_type = 'OTHR' THEN %> <%= l_ra_rec.total_othr %> <% END IF; %>