abcd: A Procedure with Some of passing parameters and Returning with the Condition Values

CREATE OR REPLACE Procedure Insert_Individual_departure(
p_date IN VARCHAR, --1
p_time IN VARCHAR, --2--------------------------
p_reg_no IN VARCHAR, --3
p_location_cd IN VARCHAR, --4
p_operator_cd IN VARCHAR, --5

p_flight_no IN VARCHAR, --6
p_nature IN VARCHAR, --7
p_pilot_in_command IN VARCHAR, --8
p_pob IN VARCHAR, --9
p_scheduled IN VARCHAR, --10

p_route_cd IN VARCHAR, --11
p_gcd IN VARCHAR, --12
p_position_type IN VARCHAR, --13
p_position_cd IN VARCHAR, --14
p_runway_no IN VARCHAR, --15

p_nop IN VARCHAR, --16
p_diverted_flight IN VARCHAR, --19
p_haj_flight IN VARCHAR, --20

p_chargeable_flight IN VARCHAR, --21
p_ficadc IN VARCHAR, --22
p_remarks IN VARCHAR, --23
p_transit_passengers IN VARCHAR, --24
p_touchgo_status IN VARCHAR, --25

p_uncontrolled_loc IN VARCHAR, --26
p_use_aaixray IN VARCHAR, --27
p_fullstop_landdate IN VARCHAR, --28
p_nooftouchdowns IN VARCHAR, --29
p_commoncounter IN VARCHAR, --30
p_watch_extension IN VARCHAR, --31
p_type IN VARCHAR, --32

p_start_time IN VARCHAR,
p_dtd_time IN VARCHAR,--------------------------

p_entered_by IN VARCHAR,-- This variable refers to the user id.
p_current_loc IN VARCHAR,-- This variable refers to the current airport code.
p_select_field OUT VARCHAR,-- To make the corresponding text box select automatically if any error occurs.
p_user_errors OUT VARCHAR -- To Propogate validation errors to the user.
)

AS

 v_utc_datetime VARCHAR(16);
 v_time_difference NUMBER;

 v_fmuid VARCHAR(20);
 v_fmuid1 VARCHAR(20);
 v_flight_no VARCHAR(20);
 v_position_type VARCHAR(2);
 v_last_parked_datetime date;

 v_credit_facility CHAR;
 v_free_facility CHAR;
 v_billable_flag CHAR Default 'Y';
 v_ca12_new CHAR Default 'T';
 v_ca12_no VARCHAR(12);
 v_bay_allocated VARCHAR(10);
 v_prioritized CHAR;
 v_srno Tc_Parking_Register.Srno%TYPE;
 v_airport_type CHAR;

 v_bay_parking NUMBER;
 v_hangar_parking NUMBER;
 v_open_parking NUMBER;

 v_temp VARCHAR(1000);
 v_condition VARCHAR(200);
 v_ncb_status CHAR;
 v_runway_no VARCHAR(10);
 v_arr_watch_extension Number;

p_mail_loaded Number:=0;
p_cargo_loaded Number:=0;
p_entered_date Date:= Sysdate;
v_Delay Number(5);
v_DelayPb Number(5);
v_arrival_date_time varchar2(20);

 cursor c_arrival_details is select flight_no,arr_date,ata,dep_location,nature,arr_pob,mail_unloaded,cargo_unloaded,arr_gcd,operator_cd,scheduled,chargeable,WatchExt_Arr from tc_arrival_log where status='A' and Upper(Trim(reg_no))=Upper(Trim(p_reg_no));

 c_arr_details c_arrival_details%rowtype;

 user_ex Exception; --Single user defined exception to jump from the code and to inform the error to user.
Begin

 --Setting the user errors to 0, means initially there are no errors.
 p_user_errors := '0'; --0 means No Errors;
 v_arrival_date_time :=to_date(p_date||' '||p_time,'DD/Mon/yyyy HH24MI');

 -- Step 1: To calculate the utc date and time and to find the differnce between the entered date and system date.
 p_user_errors := check_datetime(p_date,p_time);
 If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].actualTime.select();';
 raise user_ex;
 End If;
 --End of Step 1;

 --Step 2: To check for Registration No.
p_user_errors := check_reg_no(p_reg_no);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Reg_No.select();';
 raise user_ex;
End If;
--End of Step 2.

--Step 3: To check for Location.
p_user_errors := check_location(p_location_cd);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Dep_Location.select();';
 raise user_ex;
End If;
--End of Step 3.

--Step 4: To check for Operator.
p_user_errors := check_operator(p_operator_cd);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].OPERATOR_CD.select();';
 raise user_ex;
End If;
--End of Step 4.

--The below condition is to check whether the arrival exists for a given registration no.
p_user_errors := check_arrival(p_reg_no);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Reg_No.select();';
 raise user_ex;
End If;
--End of checking the arrival existence.

--Step 7: To check for last parking date and time.
v_last_parked_datetime := last_parking_datetime(p_reg_no);
If(v_last_parked_datetime IS NULL) THEN
 v_last_parked_datetime:=sysdate;
 v_time_difference:=datetime_difference(v_last_parked_datetime,v_arrival_date_time);
End If;
--v_time_difference:=datetime_difference(v_arrival_date_time,v_last_parked_datetime);
If(v_last_parked_datetime IS NOT NULL AND v_arrival_date_time IS NOT NULL) Then
 v_time_difference:=datetime_difference(v_last_parked_datetime,v_arrival_date_time);
End If;

-- If(v_last_parked_datetime IS NULL) Then
-- v_last_parked_datetime:=sysdate;
-- End If;
-- If(v_last_parked_datetime IS NOT NULL OR v_ardtime IS NOT NULL) Then
-- v_time_difference := datetime_difference(v_ardtime,v_last_parked_datetime);
-- End If;
-- If(v_time_difference IS NOT NULL)Then
-- v_time_difference:=v_time_difference;
-- End If;
-- If(v_time_difference IS NULL) Then
-- v_time_difference:=0;
-- End If;
If(v_time_difference <0) Then
 p_user_errors := 'Departing Date and Time cannot be less than or equal or equal to previous parking date and time:'||v_last_parked_datetime||' & Arrival Time:'||v_arrival_date_time;
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
End If;
--End of Step 7.

--Step 5: To check slot validation.
p_user_errors:= Slot_Checking(p_reg_no,p_date,p_time);
--p_user_errors := fn_check_slot(p_reg_no,p_date,p_time,'D',p_current_loc);
If(p_user_errors!='0') Then
 --p_user_errors := 'Please change the Departure Date / Time. The Departure Date / Time entered is conflicting with an existing movement, or The Flight is currently not on Bay.';
 p_select_field := 'document.forms[0].date.select();';
 raise user_ex;
Else
 p_user_errors := '0';
End If;
--End of Step 5.

--Step 6: To check for Last parked position.
v_position_type := last_parking_position(p_reg_no);
If(p_nature!='T' AND v_position_type='H') Then
 p_user_errors:= 'This Flight Is Not in Bay. Please Change The Parking Position To Bay Before Departing. Use Parking Register.';
 p_select_field:= 'document.forms[0].date.focus();';
 raise user_ex;
End If;
-- End of Step 6.

--Step 8: To check for credit facility and free facility and billable flag.
v_condition := 'Upper(Trim(Operator_Cd))=Upper(Trim('''||p_operator_cd||''')) and Upper(Trim(Location_Cd))=Upper(Trim('''||p_current_loc||'''))';
v_credit_facility := get_description('Nvl(Decode(CreditFacility,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);

v_free_facility := get_description('Nvl(Decode(FreeFacility_Flag,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);

If(v_free_facility = 'Y') Then
 v_billable_flag := 'N';
End If;
--End of Step 8.

--Step 9: To check whether ca12 no exists, if not generate a new ca12 no.
v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';

v_ca12_no := get_description('al.CA12_No','TC_Arrival_Log al,tc_ca12info ca',v_condition);

If(v_ca12_no != '0') Then
 v_ca12_new := 'F';
 v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';
 v_ncb_status := get_description('ca.ncb_status','TC_Arrival_Log al,tc_ca12info ca',v_condition);

 If(v_ncb_status='P') Then
 p_user_errors := 'The Operator of this Aircraft does not have Credit Facility And Complete Bill should be raised before its departure. Partial Bill has been Raised. Either Non Credit bill or Landing and Parking charges bill has been raised.';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
Else
 If(v_free_facility='N' AND v_credit_facility='N') Then
 p_user_errors := 'Bill has not raised for this Aircraft. The Operator of this Aircraft does not have credit facility and bill should be raised before its departure.';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
 v_ca12_no := get_nextcode_gby('Tc_ca12info','ca12_no',10,date_format('YYMMDD'));
End If;
--End of Step 9.

/*
v_condition := 'Upper(Trim(Flight_No))=Upper(Trim('''||p_flight_no||''')) and (Status in (''OS'',''DL'') OR Status is Null) And Flight_Dt='''||p_date||''' and Dep_Location='''||p_current_loc||'''';
v_bay_allocated := get_description('Bay_Allocated','Tc_EstimatedFlights',v_condition);
v_prioritized := get_description('Prioritized','Tc_EstimatedFlights',v_condition);
*/

/*
--Update estimated flights.
Update Tc_EstimatedFlights set Tow_Status='D', Tow_Time=p_time, Reg_No=Upper(Trim(p_reg_no)), Route_Cd=p_route_cd, Gcd=p_gcd Where Flight_no=p_flight_no and Flight_Dt=p_date and Dest_Location=Upper(Trim(p_location_cd)) and Dep_Location=p_current_loc;
*/

v_condition := 'Upper(Trim(Reg_No))=Upper(Trim('''||p_reg_no||''')) and Status=''A''';
v_fmuid := get_description('Fmuid','Tc_Arrival_Log',v_condition);

--Step 10: To Insert/Update the corresponding tables.

--Insert into parking register.
v_srno := get_nextcode_gby('Tc_Parking_Register','Srno',11,date_format('YYMMDD'));

Insert into Tc_Parking_Register(Fmuid,Srno,Reg_No,Parking_Date,Position_Type,Position_Cd,Actual_time,Status,Entered_Dt,Entered_by) values (v_fmuid,v_srno,Upper(Trim(p_reg_no)),p_date,'D',v_bay_allocated,p_time,'A',p_entered_date, p_entered_by);

--Calculate parking hours.
v_airport_type := get_description('Airport_Type','Tc_Location_Mt','Location_Cd='''||p_current_loc||'''');

v_bay_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'B');
v_bay_parking := roundoff_minutes('P',v_airport_type,p_nature,'R', p_date,v_bay_parking);

v_condition:= 'od.operator_cd=mt.operator_cd and od.location_cd='''||p_current_loc||''' and Upper(Trim(mt.reg_no))=Upper(Trim('''||p_reg_no||'''))';

If(get_description('od.Use_AAI_Housing','TC_OPERATOR_DT od,TC_AIRCRAFT_MT mt',v_condition)='Y') Then
 v_hangar_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'H');
 v_hangar_parking := roundoff_minutes('H',v_airport_type,p_nature,'R', p_date,v_hangar_parking);
Else
 v_hangar_parking := 0;
End If;

v_open_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'O');
v_open_parking := roundoff_minutes('E',v_airport_type,p_nature,'R', p_date,v_open_parking);

--End of Calculating parking hours.

--Update parking register.
Update Tc_parking_Register set Status='D' where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';

v_runway_no := p_runway_no;
if(Trim(p_runway_no)='' or p_runway_no is null) Then
 v_runway_no := get_description('Rwy_No','Tc_Runway_Mt','Rownum <= 1');
End If;

--Insert into departure log.
select TIMEDIFFINMINS(p_time,p_dtd_time) d into v_Delay from dual;
select TIMEDIFFINMINS(p_time,p_start_time) dl into v_DelayPb from dual;

v_fmuid1:=checkfmuid(v_fmuid);
If(v_fmuid1!='143') Then
 p_user_errors := 'Conflict between Arrival Movement Please check once for Previous Arrival Movement Exists or not';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
End If;


--DelayPbValue
/* Formatted on 2011/06/20 12:28 (Formatter Plus v4.8.8) */
INSERT INTO tc_departure_log
 (fmuid, flight_no, dep_date, dep_reg_no,
 dest_location, startuptime, atd, dtd,
 Delay, delaypb, rwy_no, pilot_in_cmd, hyt_no,
 pob, transit_pax, mail_loaded,
 cargo_loaded, bay_allocated, prioritized, scheduled,
 nature, touchgo_status, ca12_no, uncontrolled_location,
 route_cd, dep_gcd, remarks, entered_by, entered_dt,
 dep_nop, use_aai_xray, hajflight, operator_cd,
 service_units, fullstop_landingdate,
 chargeable, noof_touchdowns, common_counter, TYPE
 )
VALUES (v_fmuid, p_flight_no, p_date, UPPER (TRIM (p_reg_no)),
 UPPER (TRIM (p_location_cd)),p_start_time, p_time, p_dtd_time, v_Delay,
 v_DelayPb, v_runway_no, p_pilot_in_command, p_ficadc, p_pob,
 p_transit_passengers, p_mail_loaded, p_cargo_loaded,
 v_bay_allocated, v_prioritized, p_scheduled, p_nature,
 p_touchgo_status, v_ca12_no, p_uncontrolled_loc, p_route_cd,
 p_gcd, p_remarks, p_entered_by, p_entered_date, p_nop,
 NVL (p_use_aaixray, 'N'), p_haj_flight, p_operator_cd, '0',
 p_fullstop_landdate, p_chargeable_flight,
 NVL (p_nooftouchdowns, 0), NVL (p_commoncounter, 'N'), p_type
 );
--The below two statements are to fetch corresponding arrival details.
open c_arrival_details;
fetch c_arrival_details into c_arr_details;

--Insert/Update ca12info.
If(v_ca12_new ='T') Then
 Insert Into Tc_Ca12Info(Fmuid,Ca12_No,Reg_No,Arr_Flight_No,Arr_Date,Arr_Gmt,Dep_Location,Arr_Pob, Mail_Unloaded,Cargo_Unloaded,Arr_FlightNature,Dep_Flight_No,Dep_Date,Dep_GMT,Dest_Location,Dep_POB, Mail_Loaded,Cargo_Loaded,Dep_FlightNature,DepScheduled,Billable_Flag,Operator_Cd,Arr_Gcd,Dep_Gcd, Uncontrolled_Location,Manual_Flag,Use_AAI_Xray,Arr_Operator_Cd,Dep_Operator_Cd,ArrScheduled, Arr_Chargeable,Dep_Chargeable,Parking_Hrs,Housing_Hrs,Open_Parking_Hours,Noof_TouchDowns,Common_Counter,Arr_Watch_Hrs,Dep_Watch_Hrs,Type,Entered_By,Entered_dt) Values (v_fmuid,v_ca12_no,Upper(Trim(p_reg_no)),c_arr_details.flight_no,c_arr_details.arr_date,c_arr_details.ata, c_arr_details.dep_location,c_arr_details.Arr_Pob,c_arr_details.Mail_Unloaded, c_arr_details.Cargo_Unloaded,c_arr_details.Nature,p_flight_No,p_date,p_time,p_location_cd,p_pob, p_mail_loaded,p_cargo_loaded,p_nature,p_scheduled,v_billable_flag,p_operator_cd, c_arr_details.arr_gcd,p_gcd,p_uncontrolled_loc,'N',Nvl(p_use_aaixray,'N'),c_arr_details.operator_cd, p_operator_cd,c_arr_details.scheduled,c_arr_details.chargeable,p_chargeable_flight,v_bay_parking, v_hangar_parking,v_open_parking,Nvl(p_nooftouchdowns,0),Nvl(p_commoncounter,'N'),c_arr_details.WatchExt_Arr,p_watch_extension,p_type,p_entered_by,p_entered_date);

Else
 If(v_credit_facility='Y') Then
 Update TC_Ca12Info Set Dep_Flight_No=p_flight_no,Dep_Date=p_date,Dep_GMT=p_time,Dest_Location=Upper(Trim(p_location_cd)), Dep_POB=p_pob,Mail_Loaded=p_mail_loaded,Cargo_Loaded=p_cargo_loaded, Arr_Date=c_arr_details.arr_date,Arr_Gmt=c_arr_details.ata, Dep_FlightNature=p_nature,DepScheduled=p_scheduled,Parking_Hrs=v_bay_parking, Housing_Hrs=v_hangar_parking,Billable_Flag=v_billable_flag,Operator_Cd=p_operator_cd, DEP_GCD=p_gcd,Uncontrolled_Location=p_uncontrolled_loc,Manual_Flag='N',Entered_By=p_entered_by, Entered_Dt=p_entered_date,Open_Parking_Hours=v_open_parking,Use_AAI_Xray=Nvl(p_use_aaixray,'N'), Dep_Operator_Cd=p_operator_cd,Dep_Chargeable=p_chargeable_flight,Noof_TouchDowns=Nvl(p_nooftouchdowns,0),Common_Counter=Nvl(p_commoncounter,'N'),Arr_Watch_Hrs=c_arr_details.WatchExt_Arr,Dep_Watch_Hrs=p_watch_extension,Type=p_type where CA12_No=v_ca12_no;
 --v_temp := 'updating ca12 '||v_ca12_new||' '||v_credit_facility;
 End If;
End If;
--End of Step 10.
close c_arrival_details;

 --Update Arrival Log.
Update Tc_Arrival_Log set Status='D',Ca12_No=v_ca12_no where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';

/***************************************************************************************************
--This block is for testing purpose, where ever you want test the value, just assign that value to v_temp variable.
If( v_temp!='0') Then
 p_user_errors := v_temp;
 p_select_field:= 'document.forms[0].date.focus();';
 raise user_ex;
End If;
*****************************************************************************************************/
 Commit;
 Exception
 When user_ex Then
 rollback;
End;
/CREATE OR REPLACE Procedure VABB.Insert_Individual_departure(
p_date IN VARCHAR, --1
p_time IN VARCHAR, --2--------------------------
p_reg_no IN VARCHAR, --3
p_location_cd IN VARCHAR, --4
p_operator_cd IN VARCHAR, --5

p_flight_no IN VARCHAR, --6
p_nature IN VARCHAR, --7
p_pilot_in_command IN VARCHAR, --8
p_pob IN VARCHAR, --9
p_scheduled IN VARCHAR, --10

p_route_cd IN VARCHAR, --11
p_gcd IN VARCHAR, --12
p_position_type IN VARCHAR, --13
p_position_cd IN VARCHAR, --14
p_runway_no IN VARCHAR, --15

p_nop IN VARCHAR, --16
p_diverted_flight IN VARCHAR, --19
p_haj_flight IN VARCHAR, --20

p_chargeable_flight IN VARCHAR, --21
p_ficadc IN VARCHAR, --22
p_remarks IN VARCHAR, --23
p_transit_passengers IN VARCHAR, --24
p_touchgo_status IN VARCHAR, --25

p_uncontrolled_loc IN VARCHAR, --26
p_use_aaixray IN VARCHAR, --27
p_fullstop_landdate IN VARCHAR, --28
p_nooftouchdowns IN VARCHAR, --29
p_commoncounter IN VARCHAR, --30
p_watch_extension IN VARCHAR, --31
p_type IN VARCHAR, --32
p_start_time IN VARCHAR,
p_dtd_time IN VARCHAR,--------------------------

p_entered_by IN VARCHAR,-- This variable refers to the user id.
p_current_loc IN VARCHAR,-- This variable refers to the current airport code.
p_select_field OUT VARCHAR,-- To make the corresponding text box select automatically if any error occurs.
p_user_errors OUT VARCHAR -- To Propogate validation errors to the user.
)

AS

 v_utc_datetime VARCHAR(16);
 v_time_difference NUMBER;

 v_fmuid VARCHAR(20);
 v_flight_no VARCHAR(20);
 v_position_type VARCHAR(2);
 v_last_parked_datetime VARCHAR(16);

 v_credit_facility CHAR;
 v_free_facility CHAR;
 v_billable_flag CHAR Default 'Y';
 v_ca12_new CHAR Default 'T';
 v_ca12_no VARCHAR(12);
 v_bay_allocated VARCHAR(10);
 v_prioritized CHAR;
 v_srno Tc_Parking_Register.Srno%TYPE;
 v_airport_type CHAR;

 v_bay_parking NUMBER;
 v_hangar_parking NUMBER;
 v_open_parking NUMBER;

 v_temp VARCHAR(1000);
 v_condition VARCHAR(200);
 v_ncb_status CHAR;
 v_runway_no VARCHAR(10);
 v_arr_watch_extension Number;

p_mail_loaded Number:=0;
p_cargo_loaded Number:=0;
p_entered_date Date:= Sysdate;
v_Delay Number(5);
v_DelayPb Number(5);

 cursor c_arrival_details is select flight_no,arr_date,ata,dep_location,nature,arr_pob,mail_unloaded,cargo_unloaded,arr_gcd,operator_cd,scheduled,chargeable,WatchExt_Arr from tc_arrival_log where status='A' and Upper(Trim(reg_no))=Upper(Trim(p_reg_no));

 c_arr_details c_arrival_details%rowtype;

 user_ex Exception; --Single user defined exception to jump from the code and to inform the error to user.
Begin

 --Setting the user errors to 0, means initially there are no errors.
 p_user_errors := '0'; --0 means No Errors;

 -- Step 1: To calculate the utc date and time and to find the differnce between the entered date and system date.
 p_user_errors := check_datetime(p_date,p_time);
 If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].actualTime.select();';
 raise user_ex;
 End If;
 --End of Step 1;

 --Step 2: To check for Registration No.
p_user_errors := check_reg_no(p_reg_no);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Reg_No.select();';
 raise user_ex;
End If;
--End of Step 2.

--Step 3: To check for Location.
p_user_errors := check_location(p_location_cd);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Dep_Location.select();';
 raise user_ex;
End If;
--End of Step 3.

--Step 4: To check for Operator.
p_user_errors := check_operator(p_operator_cd);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].OPERATOR_CD.select();';
 raise user_ex;
End If;
--End of Step 4.

--The below condition is to check whether the arrival exists for a given registration no.
p_user_errors := check_arrival(p_reg_no);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Reg_No.select();';
 raise user_ex;
End If;
--End of checking the arrival existence.

--Step 7: To check for last parking date and time.
v_last_parked_datetime := last_parking_datetime(p_reg_no);
v_time_difference := datetime_difference(p_date||' '||p_time,v_last_parked_datetime);
If(v_time_difference <0) Then
 p_user_errors := 'Departing Date and Time cannot be less than or equal or equal to previous parking date and time '||v_last_parked_datetime;
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
End If;
--End of Step 7.

--Step 5: To check slot validation.
p_user_errors:= Slot_Checking(p_reg_no,p_date,p_time);
--p_user_errors := fn_check_slot(p_reg_no,p_date,p_time,'D',p_current_loc);
If(p_user_errors!='0') Then
 --p_user_errors := 'Please change the Departure Date / Time. The Departure Date / Time entered is conflicting with an existing movement, or The Flight is currently not on Bay.';
 p_select_field := 'document.forms[0].date.select();';
 raise user_ex;
Else
 p_user_errors := '0';
End If;
--End of Step 5.

--Step 6: To check for Last parked position.
v_position_type := last_parking_position(p_reg_no);
If(p_nature!='T' AND v_position_type='H') Then
 p_user_errors:= 'This Flight Is Not in Bay. Please Change The Parking Position To Bay Before Departing. Use Parking Register.';
 p_select_field:= 'document.forms[0].date.focus();';
 raise user_ex;
End If;
-- End of Step 6.

--Step 8: To check for credit facility and free facility and billable flag.
v_condition := 'Upper(Trim(Operator_Cd))=Upper(Trim('''||p_operator_cd||''')) and Upper(Trim(Location_Cd))=Upper(Trim('''||p_current_loc||'''))';
v_credit_facility := get_description('Nvl(Decode(CreditFacility,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);

v_free_facility := get_description('Nvl(Decode(FreeFacility_Flag,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);

If(v_free_facility = 'Y') Then
 v_billable_flag := 'N';
End If;
--End of Step 8.

--Step 9: To check whether ca12 no exists, if not generate a new ca12 no.
v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';

v_ca12_no := get_description('al.CA12_No','TC_Arrival_Log al,tc_ca12info ca',v_condition);

If(v_ca12_no != '0') Then
 v_ca12_new := 'F';
 v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';
 v_ncb_status := get_description('ca.ncb_status','TC_Arrival_Log al,tc_ca12info ca',v_condition);

 If(v_ncb_status='P') Then
 p_user_errors := 'The Operator of this Aircraft does not have Credit Facility And Complete Bill should be raised before its departure. Partial Bill has been Raised. Either Non Credit bill or Landing and Parking charges bill has been raised.';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
Else
 If(v_free_facility='N' AND v_credit_facility='N') Then
 p_user_errors := 'Bill has not raised for this Aircraft. The Operator of this Aircraft does not have credit facility and bill should be raised before its departure.';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
 v_ca12_no := get_nextcode_gby('Tc_ca12info','ca12_no',10,date_format('YYMMDD'));
End If;
--End of Step 9.

/*
v_condition := 'Upper(Trim(Flight_No))=Upper(Trim('''||p_flight_no||''')) and (Status in (''OS'',''DL'') OR Status is Null) And Flight_Dt='''||p_date||''' and Dep_Location='''||p_current_loc||'''';
v_bay_allocated := get_description('Bay_Allocated','Tc_EstimatedFlights',v_condition);
v_prioritized := get_description('Prioritized','Tc_EstimatedFlights',v_condition);
*/

/*
--Update estimated flights.
Update Tc_EstimatedFlights set Tow_Status='D', Tow_Time=p_time, Reg_No=Upper(Trim(p_reg_no)), Route_Cd=p_route_cd, Gcd=p_gcd Where Flight_no=p_flight_no and Flight_Dt=p_date and Dest_Location=Upper(Trim(p_location_cd)) and Dep_Location=p_current_loc;
*/

v_condition := 'Upper(Trim(Reg_No))=Upper(Trim('''||p_reg_no||''')) and Status=''A''';
v_fmuid := get_description('Fmuid','Tc_Arrival_Log',v_condition);

--Step 10: To Insert/Update the corresponding tables.

--Insert into parking register.
v_srno := get_nextcode_gby('Tc_Parking_Register','Srno',11,date_format('YYMMDD'));

Insert into Tc_Parking_Register(Fmuid,Srno,Reg_No,Parking_Date,Position_Type,Position_Cd,Actual_time,Status,Entered_Dt,Entered_by) values (v_fmuid,v_srno,Upper(Trim(p_reg_no)),p_date,'D',v_bay_allocated,p_time,'A',p_entered_date, p_entered_by);

--Calculate parking hours.
v_airport_type := get_description('Airport_Type','Tc_Location_Mt','Location_Cd='''||p_current_loc||'''');

v_bay_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'B');
v_bay_parking := roundoff_minutes('P',v_airport_type,p_nature,'R', p_date,v_bay_parking);

v_condition:= 'od.operator_cd=mt.operator_cd and od.location_cd='''||p_current_loc||''' and Upper(Trim(mt.reg_no))=Upper(Trim('''||p_reg_no||'''))';

If(get_description('od.Use_AAI_Housing','TC_OPERATOR_DT od,TC_AIRCRAFT_MT mt',v_condition)='Y') Then
 v_hangar_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'H');
 v_hangar_parking := roundoff_minutes('H',v_airport_type,p_nature,'R', p_date,v_hangar_parking);
Else
 v_hangar_parking := 0;
End If;

v_open_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'O');
v_open_parking := roundoff_minutes('E',v_airport_type,p_nature,'R', p_date,v_open_parking);

--End of Calculating parking hours.

--Update parking register.
Update Tc_parking_Register set Status='D' where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';

v_runway_no := p_runway_no;
if(Trim(p_runway_no)='' or p_runway_no is null) Then
 v_runway_no := get_description('Rwy_No','Tc_Runway_Mt','Rownum <= 1');
End If;

--Insert into departure log.
select TIMEDIFFINMINS(p_time,p_dtd_time) d into v_Delay from dual;
select TIMEDIFFINMINS(p_time,p_start_time) dl into v_DelayPb from dual;
--DelayPbValue
/* Formatted on 2011/06/20 12:28 (Formatter Plus v4.8.8) */
INSERT INTO tc_departure_log
 (fmuid, flight_no, dep_date, dep_reg_no,
 dest_location, startuptime, atd, dtd,
 Delay, delaypb, rwy_no, pilot_in_cmd, hyt_no,
 pob, transit_pax, mail_loaded,
 cargo_loaded, bay_allocated, prioritized, scheduled,
 nature, touchgo_status, ca12_no, uncontrolled_location,
 route_cd, dep_gcd, remarks, entered_by, entered_dt,
 dep_nop, use_aai_xray, hajflight, operator_cd,
 service_units, fullstop_landingdate,
 chargeable, noof_touchdowns, common_counter, TYPE
 )
 VALUES (v_fmuid, p_flight_no, p_date, UPPER (TRIM (p_reg_no)),
 UPPER (TRIM (p_location_cd)),p_start_time, p_time, p_dtd_time, v_Delay,
 v_DelayPb, v_runway_no, p_pilot_in_command, p_ficadc, p_pob,
 p_transit_passengers, p_mail_loaded, p_cargo_loaded,
 v_bay_allocated, v_prioritized, p_scheduled, p_nature,
 p_touchgo_status, v_ca12_no, p_uncontrolled_loc, p_route_cd,
 p_gcd, p_remarks, p_entered_by, p_entered_date, p_nop,
 NVL (p_use_aaixray, 'N'), p_haj_flight, p_operator_cd, '0',
 p_fullstop_landdate, p_chargeable_flight,
 NVL (p_nooftouchdowns, 0), NVL (p_commoncounter, 'N'), p_type
 );
--The below two statements are to fetch corresponding arrival details.
open c_arrival_details;
fetch c_arrival_details into c_arr_details;

--Insert/Update ca12info.
If(v_ca12_new ='T') Then
 Insert Into Tc_Ca12Info(Fmuid,Ca12_No,Reg_No,Arr_Flight_No,Arr_Date,Arr_Gmt,Dep_Location,Arr_Pob, Mail_Unloaded,Cargo_Unloaded,Arr_FlightNature,Dep_Flight_No,Dep_Date,Dep_GMT,Dest_Location,Dep_POB, Mail_Loaded,Cargo_Loaded,Dep_FlightNature,DepScheduled,Billable_Flag,Operator_Cd,Arr_Gcd,Dep_Gcd, Uncontrolled_Location,Manual_Flag,Use_AAI_Xray,Arr_Operator_Cd,Dep_Operator_Cd,ArrScheduled, Arr_Chargeable,Dep_Chargeable,Parking_Hrs,Housing_Hrs,Open_Parking_Hours,Noof_TouchDowns,Common_Counter,Arr_Watch_Hrs,Dep_Watch_Hrs,Type,Entered_By,Entered_dt) Values (v_fmuid,v_ca12_no,Upper(Trim(p_reg_no)),c_arr_details.flight_no,c_arr_details.arr_date,c_arr_details.ata, c_arr_details.dep_location,c_arr_details.Arr_Pob,c_arr_details.Mail_Unloaded, c_arr_details.Cargo_Unloaded,c_arr_details.Nature,p_flight_No,p_date,p_time,p_location_cd,p_pob, p_mail_loaded,p_cargo_loaded,p_nature,p_scheduled,v_billable_flag,p_operator_cd, c_arr_details.arr_gcd,p_gcd,p_uncontrolled_loc,'N',Nvl(p_use_aaixray,'N'),c_arr_details.operator_cd, p_operator_cd,c_arr_details.scheduled,c_arr_details.chargeable,p_chargeable_flight,v_bay_parking, v_hangar_parking,v_open_parking,Nvl(p_nooftouchdowns,0),Nvl(p_commoncounter,'N'),c_arr_details.WatchExt_Arr,p_watch_extension,p_type,p_entered_by,p_entered_date);

Else
 If(v_credit_facility='Y') Then
 Update TC_Ca12Info Set Dep_Flight_No=p_flight_no,Dep_Date=p_date,Dep_GMT=p_time,Dest_Location=Upper(Trim(p_location_cd)), Dep_POB=p_pob,Mail_Loaded=p_mail_loaded,Cargo_Loaded=p_cargo_loaded, Arr_Date=c_arr_details.arr_date,Arr_Gmt=c_arr_details.ata, Dep_FlightNature=p_nature,DepScheduled=p_scheduled,Parking_Hrs=v_bay_parking, Housing_Hrs=v_hangar_parking,Billable_Flag=v_billable_flag,Operator_Cd=p_operator_cd, DEP_GCD=p_gcd,Uncontrolled_Location=p_uncontrolled_loc,Manual_Flag='N',Entered_By=p_entered_by, Entered_Dt=p_entered_date,Open_Parking_Hours=v_open_parking,Use_AAI_Xray=Nvl(p_use_aaixray,'N'), Dep_Operator_Cd=p_operator_cd,Dep_Chargeable=p_chargeable_flight,Noof_TouchDowns=Nvl(p_nooftouchdowns,0),Common_Counter=Nvl(p_commoncounter,'N'),Arr_Watch_Hrs=c_arr_details.WatchExt_Arr,Dep_Watch_Hrs=p_watch_extension,Type=p_type where CA12_No=v_ca12_no;
 --v_temp := 'updating ca12 '||v_ca12_new||' '||v_credit_facility;
 End If;
End If;
--End of Step 10.
close c_arrival_details;

 --Update Arrival Log.
Update Tc_Arrival_Log set Status='D',Ca12_No=v_ca12_no where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';

/***************************************************************************************************
--This block is for testing purpose, where ever you want test the value, just assign that value to v_temp variable.
If( v_temp!='0') Then
 p_user_errors := v_temp;
 p_select_field:= 'document.forms[0].date.focus();';
 raise user_ex;
End If;
*****************************************************************************************************/
 Commit;
 Exception
 When user_ex Then
 rollback;
End;

CREATE OR REPLACE Procedure VABB.Insert_Individual_departure(
p_date IN VARCHAR, --1
p_time IN VARCHAR, --2--------------------------
p_reg_no IN VARCHAR, --3
p_location_cd IN VARCHAR, --4
p_operator_cd IN VARCHAR, --5

p_flight_no IN VARCHAR, --6
p_nature IN VARCHAR, --7
p_pilot_in_command IN VARCHAR, --8
p_pob IN VARCHAR, --9
p_scheduled IN VARCHAR, --10

p_route_cd IN VARCHAR, --11
p_gcd IN VARCHAR, --12
p_position_type IN VARCHAR, --13
p_position_cd IN VARCHAR, --14
p_runway_no IN VARCHAR, --15

p_nop IN VARCHAR, --16
p_diverted_flight IN VARCHAR, --19
p_haj_flight IN VARCHAR, --20

p_chargeable_flight IN VARCHAR, --21
p_ficadc IN VARCHAR, --22
p_remarks IN VARCHAR, --23
p_transit_passengers IN VARCHAR, --24
p_touchgo_status IN VARCHAR, --25

p_uncontrolled_loc IN VARCHAR, --26
p_use_aaixray IN VARCHAR, --27
p_fullstop_landdate IN VARCHAR, --28
p_nooftouchdowns IN VARCHAR, --29
p_commoncounter IN VARCHAR, --30
p_watch_extension IN VARCHAR, --31
p_type IN VARCHAR, --32

p_start_time IN VARCHAR,
p_dtd_time IN VARCHAR,--------------------------

p_entered_by IN VARCHAR,-- This variable refers to the user id.
p_current_loc IN VARCHAR,-- This variable refers to the current airport code.
p_select_field OUT VARCHAR,-- To make the corresponding text box select automatically if any error occurs.
p_user_errors OUT VARCHAR -- To Propogate validation errors to the user.
)

AS

 v_utc_datetime VARCHAR(16);
 v_time_difference NUMBER;

 v_fmuid VARCHAR(20);
 v_flight_no VARCHAR(20);
 v_position_type VARCHAR(2);
 v_last_parked_datetime date;

 v_credit_facility CHAR;
 v_free_facility CHAR;
 v_billable_flag CHAR Default 'Y';
 v_ca12_new CHAR Default 'T';
 v_ca12_no VARCHAR(12);
 v_bay_allocated VARCHAR(10);
 v_prioritized CHAR;
 v_srno Tc_Parking_Register.Srno%TYPE;
 v_airport_type CHAR;

 v_bay_parking NUMBER;
 v_hangar_parking NUMBER;
 v_open_parking NUMBER;

 v_temp VARCHAR(1000);
 v_condition VARCHAR(200);
 v_ncb_status CHAR;
 v_runway_no VARCHAR(10);
 v_arr_watch_extension Number;

p_mail_loaded Number:=0;
p_cargo_loaded Number:=0;
p_entered_date Date:= Sysdate;
v_Delay Number(5);
v_DelayPb Number(5);
v_ardtime varchar2(20);

 cursor c_arrival_details is select flight_no,arr_date,ata,dep_location,nature,arr_pob,mail_unloaded,cargo_unloaded,arr_gcd,operator_cd,scheduled,chargeable,WatchExt_Arr from tc_arrival_log where status='A' and Upper(Trim(reg_no))=Upper(Trim(p_reg_no));

 c_arr_details c_arrival_details%rowtype;

 user_ex Exception; --Single user defined exception to jump from the code and to inform the error to user.
Begin

 --Setting the user errors to 0, means initially there are no errors.
 p_user_errors := '0'; --0 means No Errors;
 v_ardtime :=to_date(p_date||' '||p_time,'DD/Mon/yyyy HH24:MI');

 -- Step 1: To calculate the utc date and time and to find the differnce between the entered date and system date.
 p_user_errors := check_datetime(p_date,p_time);
 If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].actualTime.select();';
 raise user_ex;
 End If;
 --End of Step 1;

 --Step 2: To check for Registration No.
p_user_errors := check_reg_no(p_reg_no);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Reg_No.select();';
 raise user_ex;
End If;
--End of Step 2.

--Step 3: To check for Location.
p_user_errors := check_location(p_location_cd);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Dep_Location.select();';
 raise user_ex;
End If;
--End of Step 3.

--Step 4: To check for Operator.
p_user_errors := check_operator(p_operator_cd);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].OPERATOR_CD.select();';
 raise user_ex;
End If;
--End of Step 4.

--The below condition is to check whether the arrival exists for a given registration no.
p_user_errors := check_arrival(p_reg_no);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Reg_No.select();';
 raise user_ex;
End If;
--End of checking the arrival existence.

--Step 7: To check for last parking date and time.
v_last_parked_datetime := last_parking_datetime(p_reg_no);
If(v_last_parked_datetime IS NOT NULL OR v_ardtime IS NOT NULL) Then
 v_time_difference := datetime_difference(v_ardtime,v_last_parked_datetime);
End If;

-- NAVEEN if( v_last_parked_datetime IS NULL OR v_ardtime IS NULL) Then
 --- v_time_difference := 0;
 --- p_user_errors := 'Previous parking date and Cannot be 000NULL:--'||v_last_parked_datetime||'-----';
 -- raise user_ex;
---NAVEEN End If;

 If(v_time_difference <0) Then
 p_user_errors := 'Departing Date and Time cannot be less than or equal or equal to previous parking date and time:'||v_last_parked_datetime||' & Arrival Time:'||v_ardtime;
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
--End of Step 7.

--Step 5: To check slot validation.
p_user_errors:= Slot_Checking(p_reg_no,p_date,p_time);
--p_user_errors := fn_check_slot(p_reg_no,p_date,p_time,'D',p_current_loc);
If(p_user_errors!='0') Then
 --p_user_errors := 'Please change the Departure Date / Time. The Departure Date / Time entered is conflicting with an existing movement, or The Flight is currently not on Bay.';
 p_select_field := 'document.forms[0].date.select();';
 raise user_ex;
Else
 p_user_errors := '0';
End If;
--End of Step 5.

--Step 6: To check for Last parked position.
v_position_type := last_parking_position(p_reg_no);
If(p_nature!='T' AND v_position_type='H') Then
 p_user_errors:= 'This Flight Is Not in Bay. Please Change The Parking Position To Bay Before Departing. Use Parking Register.';
 p_select_field:= 'document.forms[0].date.focus();';
 raise user_ex;
End If;
-- End of Step 6.

--Step 8: To check for credit facility and free facility and billable flag.
v_condition := 'Upper(Trim(Operator_Cd))=Upper(Trim('''||p_operator_cd||''')) and Upper(Trim(Location_Cd))=Upper(Trim('''||p_current_loc||'''))';
v_credit_facility := get_description('Nvl(Decode(CreditFacility,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);

v_free_facility := get_description('Nvl(Decode(FreeFacility_Flag,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);

If(v_free_facility = 'Y') Then
 v_billable_flag := 'N';
End If;
--End of Step 8.

--Step 9: To check whether ca12 no exists, if not generate a new ca12 no.
v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';

v_ca12_no := get_description('al.CA12_No','TC_Arrival_Log al,tc_ca12info ca',v_condition);

If(v_ca12_no != '0') Then
 v_ca12_new := 'F';
 v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';
 v_ncb_status := get_description('ca.ncb_status','TC_Arrival_Log al,tc_ca12info ca',v_condition);

 If(v_ncb_status='P') Then
 p_user_errors := 'The Operator of this Aircraft does not have Credit Facility And Complete Bill should be raised before its departure. Partial Bill has been Raised. Either Non Credit bill or Landing and Parking charges bill has been raised.';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
Else
 If(v_free_facility='N' AND v_credit_facility='N') Then
 p_user_errors := 'Bill has not raised for this Aircraft. The Operator of this Aircraft does not have credit facility and bill should be raised before its departure.';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
 v_ca12_no := get_nextcode_gby('Tc_ca12info','ca12_no',10,date_format('YYMMDD'));
End If;
--End of Step 9.

/*
v_condition := 'Upper(Trim(Flight_No))=Upper(Trim('''||p_flight_no||''')) and (Status in (''OS'',''DL'') OR Status is Null) And Flight_Dt='''||p_date||''' and Dep_Location='''||p_current_loc||'''';
v_bay_allocated := get_description('Bay_Allocated','Tc_EstimatedFlights',v_condition);
v_prioritized := get_description('Prioritized','Tc_EstimatedFlights',v_condition);
*/

/*
--Update estimated flights.
Update Tc_EstimatedFlights set Tow_Status='D', Tow_Time=p_time, Reg_No=Upper(Trim(p_reg_no)), Route_Cd=p_route_cd, Gcd=p_gcd Where Flight_no=p_flight_no and Flight_Dt=p_date and Dest_Location=Upper(Trim(p_location_cd)) and Dep_Location=p_current_loc;
*/

v_condition := 'Upper(Trim(Reg_No))=Upper(Trim('''||p_reg_no||''')) and Status=''A''';
v_fmuid := get_description('Fmuid','Tc_Arrival_Log',v_condition);

--Step 10: To Insert/Update the corresponding tables.

--Insert into parking register.
v_srno := get_nextcode_gby('Tc_Parking_Register','Srno',11,date_format('YYMMDD'));

Insert into Tc_Parking_Register(Fmuid,Srno,Reg_No,Parking_Date,Position_Type,Position_Cd,Actual_time,Status,Entered_Dt,Entered_by) values (v_fmuid,v_srno,Upper(Trim(p_reg_no)),p_date,'D',v_bay_allocated,p_time,'A',p_entered_date, p_entered_by);

--Calculate parking hours.
v_airport_type := get_description('Airport_Type','Tc_Location_Mt','Location_Cd='''||p_current_loc||'''');

v_bay_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'B');
v_bay_parking := roundoff_minutes('P',v_airport_type,p_nature,'R', p_date,v_bay_parking);

v_condition:= 'od.operator_cd=mt.operator_cd and od.location_cd='''||p_current_loc||''' and Upper(Trim(mt.reg_no))=Upper(Trim('''||p_reg_no||'''))';

If(get_description('od.Use_AAI_Housing','TC_OPERATOR_DT od,TC_AIRCRAFT_MT mt',v_condition)='Y') Then
 v_hangar_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'H');
 v_hangar_parking := roundoff_minutes('H',v_airport_type,p_nature,'R', p_date,v_hangar_parking);
Else
 v_hangar_parking := 0;
End If;

v_open_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'O');
v_open_parking := roundoff_minutes('E',v_airport_type,p_nature,'R', p_date,v_open_parking);

--End of Calculating parking hours.

--Update parking register.
Update Tc_parking_Register set Status='D' where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';

v_runway_no := p_runway_no;
if(Trim(p_runway_no)='' or p_runway_no is null) Then
 v_runway_no := get_description('Rwy_No','Tc_Runway_Mt','Rownum <= 1');
End If;

--Insert into departure log.
select TIMEDIFFINMINS(p_time,p_dtd_time) d into v_Delay from dual;
select TIMEDIFFINMINS(p_time,p_start_time) dl into v_DelayPb from dual;
--DelayPbValue
/* Formatted on 2011/06/20 12:28 (Formatter Plus v4.8.8) */
INSERT INTO tc_departure_log
 (fmuid, flight_no, dep_date, dep_reg_no,
 dest_location, startuptime, atd, dtd,
 Delay, delaypb, rwy_no, pilot_in_cmd, hyt_no,
 pob, transit_pax, mail_loaded,
 cargo_loaded, bay_allocated, prioritized, scheduled,
 nature, touchgo_status, ca12_no, uncontrolled_location,
 route_cd, dep_gcd, remarks, entered_by, entered_dt,
 dep_nop, use_aai_xray, hajflight, operator_cd,
 service_units, fullstop_landingdate,
 chargeable, noof_touchdowns, common_counter, TYPE
 )
 VALUES (v_fmuid, p_flight_no, p_date, UPPER (TRIM (p_reg_no)),
 UPPER (TRIM (p_location_cd)),p_start_time, p_time, p_dtd_time, v_Delay,
 v_DelayPb, v_runway_no, p_pilot_in_command, p_ficadc, p_pob,
 p_transit_passengers, p_mail_loaded, p_cargo_loaded,
 v_bay_allocated, v_prioritized, p_scheduled, p_nature,
 p_touchgo_status, v_ca12_no, p_uncontrolled_loc, p_route_cd,
 p_gcd, p_remarks, p_entered_by, p_entered_date, p_nop,
 NVL (p_use_aaixray, 'N'), p_haj_flight, p_operator_cd, '0',
 p_fullstop_landdate, p_chargeable_flight,
 NVL (p_nooftouchdowns, 0), NVL (p_commoncounter, 'N'), p_type
 );
--The below two statements are to fetch corresponding arrival details.
open c_arrival_details;
fetch c_arrival_details into c_arr_details;

--Insert/Update ca12info.
If(v_ca12_new ='T') Then
 Insert Into Tc_Ca12Info(Fmuid,Ca12_No,Reg_No,Arr_Flight_No,Arr_Date,Arr_Gmt,Dep_Location,Arr_Pob, Mail_Unloaded,Cargo_Unloaded,Arr_FlightNature,Dep_Flight_No,Dep_Date,Dep_GMT,Dest_Location,Dep_POB, Mail_Loaded,Cargo_Loaded,Dep_FlightNature,DepScheduled,Billable_Flag,Operator_Cd,Arr_Gcd,Dep_Gcd, Uncontrolled_Location,Manual_Flag,Use_AAI_Xray,Arr_Operator_Cd,Dep_Operator_Cd,ArrScheduled, Arr_Chargeable,Dep_Chargeable,Parking_Hrs,Housing_Hrs,Open_Parking_Hours,Noof_TouchDowns,Common_Counter,Arr_Watch_Hrs,Dep_Watch_Hrs,Type,Entered_By,Entered_dt) Values (v_fmuid,v_ca12_no,Upper(Trim(p_reg_no)),c_arr_details.flight_no,c_arr_details.arr_date,c_arr_details.ata, c_arr_details.dep_location,c_arr_details.Arr_Pob,c_arr_details.Mail_Unloaded, c_arr_details.Cargo_Unloaded,c_arr_details.Nature,p_flight_No,p_date,p_time,p_location_cd,p_pob, p_mail_loaded,p_cargo_loaded,p_nature,p_scheduled,v_billable_flag,p_operator_cd, c_arr_details.arr_gcd,p_gcd,p_uncontrolled_loc,'N',Nvl(p_use_aaixray,'N'),c_arr_details.operator_cd, p_operator_cd,c_arr_details.scheduled,c_arr_details.chargeable,p_chargeable_flight,v_bay_parking, v_hangar_parking,v_open_parking,Nvl(p_nooftouchdowns,0),Nvl(p_commoncounter,'N'),c_arr_details.WatchExt_Arr,p_watch_extension,p_type,p_entered_by,p_entered_date);

Else
 If(v_credit_facility='Y') Then
 Update TC_Ca12Info Set Dep_Flight_No=p_flight_no,Dep_Date=p_date,Dep_GMT=p_time,Dest_Location=Upper(Trim(p_location_cd)), Dep_POB=p_pob,Mail_Loaded=p_mail_loaded,Cargo_Loaded=p_cargo_loaded, Arr_Date=c_arr_details.arr_date,Arr_Gmt=c_arr_details.ata, Dep_FlightNature=p_nature,DepScheduled=p_scheduled,Parking_Hrs=v_bay_parking, Housing_Hrs=v_hangar_parking,Billable_Flag=v_billable_flag,Operator_Cd=p_operator_cd, DEP_GCD=p_gcd,Uncontrolled_Location=p_uncontrolled_loc,Manual_Flag='N',Entered_By=p_entered_by, Entered_Dt=p_entered_date,Open_Parking_Hours=v_open_parking,Use_AAI_Xray=Nvl(p_use_aaixray,'N'), Dep_Operator_Cd=p_operator_cd,Dep_Chargeable=p_chargeable_flight,Noof_TouchDowns=Nvl(p_nooftouchdowns,0),Common_Counter=Nvl(p_commoncounter,'N'),Arr_Watch_Hrs=c_arr_details.WatchExt_Arr,Dep_Watch_Hrs=p_watch_extension,Type=p_type where CA12_No=v_ca12_no;
 --v_temp := 'updating ca12 '||v_ca12_new||' '||v_credit_facility;
 End If;
End If;
--End of Step 10.
close c_arrival_details;

 --Update Arrival Log.
Update Tc_Arrival_Log set Status='D',Ca12_No=v_ca12_no where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';

/***************************************************************************************************
--This block is for testing purpose, where ever you want test the value, just assign that value to v_temp variable.
If( v_temp!='0') Then
 p_user_errors := v_temp;
 p_select_field:= 'document.forms[0].date.focus();';
 raise user_ex;
End If;
*****************************************************************************************************/
 Commit;
 Exception
 When user_ex Then
 rollback;
End;

CREATE OR REPLACE Procedure VABB.Insert_Individual_departure(
p_date IN VARCHAR, --1
p_time IN VARCHAR, --2--------------------------
p_reg_no IN VARCHAR, --3
p_location_cd IN VARCHAR, --4
p_operator_cd IN VARCHAR, --5

p_flight_no IN VARCHAR, --6
p_nature IN VARCHAR, --7
p_pilot_in_command IN VARCHAR, --8
p_pob IN VARCHAR, --9
p_scheduled IN VARCHAR, --10

p_route_cd IN VARCHAR, --11
p_gcd IN VARCHAR, --12
p_position_type IN VARCHAR, --13
p_position_cd IN VARCHAR, --14
p_runway_no IN VARCHAR, --15

p_nop IN VARCHAR, --16
p_diverted_flight IN VARCHAR, --19
p_haj_flight IN VARCHAR, --20

p_chargeable_flight IN VARCHAR, --21
p_ficadc IN VARCHAR, --22
p_remarks IN VARCHAR, --23
p_transit_passengers IN VARCHAR, --24
p_touchgo_status IN VARCHAR, --25

p_uncontrolled_loc IN VARCHAR, --26
p_use_aaixray IN VARCHAR, --27
p_fullstop_landdate IN VARCHAR, --28
p_nooftouchdowns IN VARCHAR, --29
p_commoncounter IN VARCHAR, --30
p_watch_extension IN VARCHAR, --31
p_type IN VARCHAR, --32

p_start_time IN VARCHAR,
p_dtd_time IN VARCHAR,--------------------------

p_entered_by IN VARCHAR,-- This variable refers to the user id.
p_current_loc IN VARCHAR,-- This variable refers to the current airport code.
p_select_field OUT VARCHAR,-- To make the corresponding text box select automatically if any error occurs.
p_user_errors OUT VARCHAR -- To Propogate validation errors to the user.
)

AS

 v_utc_datetime VARCHAR(16);
 v_time_difference NUMBER;

 v_fmuid VARCHAR(20);
 v_flight_no VARCHAR(20);
 v_position_type VARCHAR(2);
 v_last_parked_datetime date;

 v_credit_facility CHAR;
 v_free_facility CHAR;
 v_billable_flag CHAR Default 'Y';
 v_ca12_new CHAR Default 'T';
 v_ca12_no VARCHAR(12);
 v_bay_allocated VARCHAR(10);
 v_prioritized CHAR;
 v_srno Tc_Parking_Register.Srno%TYPE;
 v_airport_type CHAR;

 v_bay_parking NUMBER;
 v_hangar_parking NUMBER;
 v_open_parking NUMBER;

 v_temp VARCHAR(1000);
 v_condition VARCHAR(200);
 v_ncb_status CHAR;
 v_runway_no VARCHAR(10);
 v_arr_watch_extension Number;

p_mail_loaded Number:=0;
p_cargo_loaded Number:=0;
p_entered_date Date:= Sysdate;
v_Delay Number(5);
v_DelayPb Number(5);
v_ardtime varchar2(20);

 cursor c_arrival_details is select flight_no,arr_date,ata,dep_location,nature,arr_pob,mail_unloaded,cargo_unloaded,arr_gcd,operator_cd,scheduled,chargeable,WatchExt_Arr from tc_arrival_log where status='A' and Upper(Trim(reg_no))=Upper(Trim(p_reg_no));

 c_arr_details c_arrival_details%rowtype;

 user_ex Exception; --Single user defined exception to jump from the code and to inform the error to user.
Begin

 --Setting the user errors to 0, means initially there are no errors.
 p_user_errors := '0'; --0 means No Errors;
 v_ardtime :=to_date(p_date||' '||p_time,'DD/Mon/yyyy HH24:MI');

 -- Step 1: To calculate the utc date and time and to find the differnce between the entered date and system date.
 p_user_errors := check_datetime(p_date,p_time);
 If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].actualTime.select();';
 raise user_ex;
 End If;
 --End of Step 1;

 --Step 2: To check for Registration No.
p_user_errors := check_reg_no(p_reg_no);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Reg_No.select();';
 raise user_ex;
End If;
--End of Step 2.

--Step 3: To check for Location.
p_user_errors := check_location(p_location_cd);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Dep_Location.select();';
 raise user_ex;
End If;
--End of Step 3.

--Step 4: To check for Operator.
p_user_errors := check_operator(p_operator_cd);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].OPERATOR_CD.select();';
 raise user_ex;
End If;
--End of Step 4.

--The below condition is to check whether the arrival exists for a given registration no.
p_user_errors := check_arrival(p_reg_no);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Reg_No.select();';
 raise user_ex;
End If;
--End of checking the arrival existence.

--Step 7: To check for last parking date and time.
v_last_parked_datetime := last_parking_datetime(p_reg_no);
If(v_last_parked_datetime IS NOT NULL OR v_ardtime IS NOT NULL) Then
 v_time_difference := datetime_difference(v_ardtime,v_last_parked_datetime);
End If;

-- NAVEEN if( v_last_parked_datetime IS NULL OR v_ardtime IS NULL) Then
 --- v_time_difference := 0;
 --- p_user_errors := 'Previous parking date and Cannot be 000NULL:--'||v_last_parked_datetime||'-----';
 -- raise user_ex;
---NAVEEN End If;

 If(v_time_difference <0) Then
 p_user_errors := 'Departing Date and Time cannot be less than or equal or equal to previous parking date and time:'||v_last_parked_datetime||' & Arrival Time:'||v_ardtime;
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
--End of Step 7.

--Step 5: To check slot validation.
p_user_errors:= Slot_Checking(p_reg_no,p_date,p_time);
--p_user_errors := fn_check_slot(p_reg_no,p_date,p_time,'D',p_current_loc);
If(p_user_errors!='0') Then
 --p_user_errors := 'Please change the Departure Date / Time. The Departure Date / Time entered is conflicting with an existing movement, or The Flight is currently not on Bay.';
 p_select_field := 'document.forms[0].date.select();';
 raise user_ex;
Else
 p_user_errors := '0';
End If;
--End of Step 5.

--Step 6: To check for Last parked position.
v_position_type := last_parking_position(p_reg_no);
If(p_nature!='T' AND v_position_type='H') Then
 p_user_errors:= 'This Flight Is Not in Bay. Please Change The Parking Position To Bay Before Departing. Use Parking Register.';
 p_select_field:= 'document.forms[0].date.focus();';
 raise user_ex;
End If;
-- End of Step 6.

--Step 8: To check for credit facility and free facility and billable flag.
v_condition := 'Upper(Trim(Operator_Cd))=Upper(Trim('''||p_operator_cd||''')) and Upper(Trim(Location_Cd))=Upper(Trim('''||p_current_loc||'''))';
v_credit_facility := get_description('Nvl(Decode(CreditFacility,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);

v_free_facility := get_description('Nvl(Decode(FreeFacility_Flag,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);

If(v_free_facility = 'Y') Then
 v_billable_flag := 'N';
End If;
--End of Step 8.

--Step 9: To check whether ca12 no exists, if not generate a new ca12 no.
v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';

v_ca12_no := get_description('al.CA12_No','TC_Arrival_Log al,tc_ca12info ca',v_condition);

If(v_ca12_no != '0') Then
 v_ca12_new := 'F';
 v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';
 v_ncb_status := get_description('ca.ncb_status','TC_Arrival_Log al,tc_ca12info ca',v_condition);

 If(v_ncb_status='P') Then
 p_user_errors := 'The Operator of this Aircraft does not have Credit Facility And Complete Bill should be raised before its departure. Partial Bill has been Raised. Either Non Credit bill or Landing and Parking charges bill has been raised.';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
Else
 If(v_free_facility='N' AND v_credit_facility='N') Then
 p_user_errors := 'Bill has not raised for this Aircraft. The Operator of this Aircraft does not have credit facility and bill should be raised before its departure.';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
 v_ca12_no := get_nextcode_gby('Tc_ca12info','ca12_no',10,date_format('YYMMDD'));
End If;
--End of Step 9.

/*
v_condition := 'Upper(Trim(Flight_No))=Upper(Trim('''||p_flight_no||''')) and (Status in (''OS'',''DL'') OR Status is Null) And Flight_Dt='''||p_date||''' and Dep_Location='''||p_current_loc||'''';
v_bay_allocated := get_description('Bay_Allocated','Tc_EstimatedFlights',v_condition);
v_prioritized := get_description('Prioritized','Tc_EstimatedFlights',v_condition);
*/

/*
--Update estimated flights.
Update Tc_EstimatedFlights set Tow_Status='D', Tow_Time=p_time, Reg_No=Upper(Trim(p_reg_no)), Route_Cd=p_route_cd, Gcd=p_gcd Where Flight_no=p_flight_no and Flight_Dt=p_date and Dest_Location=Upper(Trim(p_location_cd)) and Dep_Location=p_current_loc;
*/

v_condition := 'Upper(Trim(Reg_No))=Upper(Trim('''||p_reg_no||''')) and Status=''A''';
v_fmuid := get_description('Fmuid','Tc_Arrival_Log',v_condition);

--Step 10: To Insert/Update the corresponding tables.

--Insert into parking register.
v_srno := get_nextcode_gby('Tc_Parking_Register','Srno',11,date_format('YYMMDD'));

Insert into Tc_Parking_Register(Fmuid,Srno,Reg_No,Parking_Date,Position_Type,Position_Cd,Actual_time,Status,Entered_Dt,Entered_by) values (v_fmuid,v_srno,Upper(Trim(p_reg_no)),p_date,'D',v_bay_allocated,p_time,'A',p_entered_date, p_entered_by);

--Calculate parking hours.
v_airport_type := get_description('Airport_Type','Tc_Location_Mt','Location_Cd='''||p_current_loc||'''');

v_bay_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'B');
v_bay_parking := roundoff_minutes('P',v_airport_type,p_nature,'R', p_date,v_bay_parking);

v_condition:= 'od.operator_cd=mt.operator_cd and od.location_cd='''||p_current_loc||''' and Upper(Trim(mt.reg_no))=Upper(Trim('''||p_reg_no||'''))';

If(get_description('od.Use_AAI_Housing','TC_OPERATOR_DT od,TC_AIRCRAFT_MT mt',v_condition)='Y') Then
 v_hangar_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'H');
 v_hangar_parking := roundoff_minutes('H',v_airport_type,p_nature,'R', p_date,v_hangar_parking);
Else
 v_hangar_parking := 0;
End If;

v_open_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'O');
v_open_parking := roundoff_minutes('E',v_airport_type,p_nature,'R', p_date,v_open_parking);

--End of Calculating parking hours.

--Update parking register.
Update Tc_parking_Register set Status='D' where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';

v_runway_no := p_runway_no;
if(Trim(p_runway_no)='' or p_runway_no is null) Then
 v_runway_no := get_description('Rwy_No','Tc_Runway_Mt','Rownum <= 1');
End If;

--Insert into departure log.
select TIMEDIFFINMINS(p_time,p_dtd_time) d into v_Delay from dual;
select TIMEDIFFINMINS(p_time,p_start_time) dl into v_DelayPb from dual;
--DelayPbValue
/* Formatted on 2011/06/20 12:28 (Formatter Plus v4.8.8) */
INSERT INTO tc_departure_log
 (fmuid, flight_no, dep_date, dep_reg_no,
 dest_location, startuptime, atd, dtd,
 Delay, delaypb, rwy_no, pilot_in_cmd, hyt_no,
 pob, transit_pax, mail_loaded,
 cargo_loaded, bay_allocated, prioritized, scheduled,
 nature, touchgo_status, ca12_no, uncontrolled_location,
 route_cd, dep_gcd, remarks, entered_by, entered_dt,
 dep_nop, use_aai_xray, hajflight, operator_cd,
 service_units, fullstop_landingdate,
 chargeable, noof_touchdowns, common_counter, TYPE
 )
 VALUES (v_fmuid, p_flight_no, p_date, UPPER (TRIM (p_reg_no)),
 UPPER (TRIM (p_location_cd)),p_start_time, p_time, p_dtd_time, v_Delay,
 v_DelayPb, v_runway_no, p_pilot_in_command, p_ficadc, p_pob,
 p_transit_passengers, p_mail_loaded, p_cargo_loaded,
 v_bay_allocated, v_prioritized, p_scheduled, p_nature,
 p_touchgo_status, v_ca12_no, p_uncontrolled_loc, p_route_cd,
 p_gcd, p_remarks, p_entered_by, p_entered_date, p_nop,
 NVL (p_use_aaixray, 'N'), p_haj_flight, p_operator_cd, '0',
 p_fullstop_landdate, p_chargeable_flight,
 NVL (p_nooftouchdowns, 0), NVL (p_commoncounter, 'N'), p_type
 );
--The below two statements are to fetch corresponding arrival details.
open c_arrival_details;
fetch c_arrival_details into c_arr_details;

--Insert/Update ca12info.
If(v_ca12_new ='T') Then
 Insert Into Tc_Ca12Info(Fmuid,Ca12_No,Reg_No,Arr_Flight_No,Arr_Date,Arr_Gmt,Dep_Location,Arr_Pob, Mail_Unloaded,Cargo_Unloaded,Arr_FlightNature,Dep_Flight_No,Dep_Date,Dep_GMT,Dest_Location,Dep_POB, Mail_Loaded,Cargo_Loaded,Dep_FlightNature,DepScheduled,Billable_Flag,Operator_Cd,Arr_Gcd,Dep_Gcd, Uncontrolled_Location,Manual_Flag,Use_AAI_Xray,Arr_Operator_Cd,Dep_Operator_Cd,ArrScheduled, Arr_Chargeable,Dep_Chargeable,Parking_Hrs,Housing_Hrs,Open_Parking_Hours,Noof_TouchDowns,Common_Counter,Arr_Watch_Hrs,Dep_Watch_Hrs,Type,Entered_By,Entered_dt) Values (v_fmuid,v_ca12_no,Upper(Trim(p_reg_no)),c_arr_details.flight_no,c_arr_details.arr_date,c_arr_details.ata, c_arr_details.dep_location,c_arr_details.Arr_Pob,c_arr_details.Mail_Unloaded, c_arr_details.Cargo_Unloaded,c_arr_details.Nature,p_flight_No,p_date,p_time,p_location_cd,p_pob, p_mail_loaded,p_cargo_loaded,p_nature,p_scheduled,v_billable_flag,p_operator_cd, c_arr_details.arr_gcd,p_gcd,p_uncontrolled_loc,'N',Nvl(p_use_aaixray,'N'),c_arr_details.operator_cd, p_operator_cd,c_arr_details.scheduled,c_arr_details.chargeable,p_chargeable_flight,v_bay_parking, v_hangar_parking,v_open_parking,Nvl(p_nooftouchdowns,0),Nvl(p_commoncounter,'N'),c_arr_details.WatchExt_Arr,p_watch_extension,p_type,p_entered_by,p_entered_date);

Else
 If(v_credit_facility='Y') Then
 Update TC_Ca12Info Set Dep_Flight_No=p_flight_no,Dep_Date=p_date,Dep_GMT=p_time,Dest_Location=Upper(Trim(p_location_cd)), Dep_POB=p_pob,Mail_Loaded=p_mail_loaded,Cargo_Loaded=p_cargo_loaded, Arr_Date=c_arr_details.arr_date,Arr_Gmt=c_arr_details.ata, Dep_FlightNature=p_nature,DepScheduled=p_scheduled,Parking_Hrs=v_bay_parking, Housing_Hrs=v_hangar_parking,Billable_Flag=v_billable_flag,Operator_Cd=p_operator_cd, DEP_GCD=p_gcd,Uncontrolled_Location=p_uncontrolled_loc,Manual_Flag='N',Entered_By=p_entered_by, Entered_Dt=p_entered_date,Open_Parking_Hours=v_open_parking,Use_AAI_Xray=Nvl(p_use_aaixray,'N'), Dep_Operator_Cd=p_operator_cd,Dep_Chargeable=p_chargeable_flight,Noof_TouchDowns=Nvl(p_nooftouchdowns,0),Common_Counter=Nvl(p_commoncounter,'N'),Arr_Watch_Hrs=c_arr_details.WatchExt_Arr,Dep_Watch_Hrs=p_watch_extension,Type=p_type where CA12_No=v_ca12_no;
 --v_temp := 'updating ca12 '||v_ca12_new||' '||v_credit_facility;
 End If;
End If;
--End of Step 10.
close c_arrival_details;

 --Update Arrival Log.
Update Tc_Arrival_Log set Status='D',Ca12_No=v_ca12_no where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';

/***************************************************************************************************
--This block is for testing purpose, where ever you want test the value, just assign that value to v_temp variable.
If( v_temp!='0') Then
 p_user_errors := v_temp;
 p_select_field:= 'document.forms[0].date.focus();';
 raise user_ex;
End If;
*****************************************************************************************************/
 Commit;
 Exception
 When user_ex Then
 rollback;
End;

CREATE OR REPLACE Procedure VABB.Insert_Individual_departure(
p_date IN VARCHAR, --1
p_time IN VARCHAR, --2--------------------------
p_reg_no IN VARCHAR, --3
p_location_cd IN VARCHAR, --4
p_operator_cd IN VARCHAR, --5

p_flight_no IN VARCHAR, --6
p_nature IN VARCHAR, --7
p_pilot_in_command IN VARCHAR, --8
p_pob IN VARCHAR, --9
p_scheduled IN VARCHAR, --10

p_route_cd IN VARCHAR, --11
p_gcd IN VARCHAR, --12
p_position_type IN VARCHAR, --13
p_position_cd IN VARCHAR, --14
p_runway_no IN VARCHAR, --15

p_nop IN VARCHAR, --16
p_diverted_flight IN VARCHAR, --19
p_haj_flight IN VARCHAR, --20

p_chargeable_flight IN VARCHAR, --21
p_ficadc IN VARCHAR, --22
p_remarks IN VARCHAR, --23
p_transit_passengers IN VARCHAR, --24
p_touchgo_status IN VARCHAR, --25

p_uncontrolled_loc IN VARCHAR, --26
p_use_aaixray IN VARCHAR, --27
p_fullstop_landdate IN VARCHAR, --28
p_nooftouchdowns IN VARCHAR, --29
p_commoncounter IN VARCHAR, --30
p_watch_extension IN VARCHAR, --31
p_type IN VARCHAR, --32

p_start_time IN VARCHAR,
p_dtd_time IN VARCHAR,--------------------------

p_entered_by IN VARCHAR,-- This variable refers to the user id.
p_current_loc IN VARCHAR,-- This variable refers to the current airport code.
p_select_field OUT VARCHAR,-- To make the corresponding text box select automatically if any error occurs.
p_user_errors OUT VARCHAR -- To Propogate validation errors to the user.
)

AS

 v_utc_datetime VARCHAR(16);
 v_time_difference NUMBER;

 v_fmuid VARCHAR(20);
 v_flight_no VARCHAR(20);
 v_position_type VARCHAR(2);
 v_last_parked_datetime VARCHAR(16);

 v_credit_facility CHAR;
 v_free_facility CHAR;
 v_billable_flag CHAR Default 'Y';
 v_ca12_new CHAR Default 'T';
 v_ca12_no VARCHAR(12);
 v_bay_allocated VARCHAR(10);
 v_prioritized CHAR;
 v_srno Tc_Parking_Register.Srno%TYPE;
 v_airport_type CHAR;

 v_bay_parking NUMBER;
 v_hangar_parking NUMBER;
 v_open_parking NUMBER;

 v_temp VARCHAR(1000);
 v_condition VARCHAR(200);
 v_ncb_status CHAR;
 v_runway_no VARCHAR(10);
 v_arr_watch_extension Number;

p_mail_loaded Number:=0;
p_cargo_loaded Number:=0;
p_entered_date Date:= Sysdate;
v_Delay Number(5);
v_DelayPb Number(5);
v_ardtime varchar2(20);

 cursor c_arrival_details is select flight_no,arr_date,ata,dep_location,nature,arr_pob,mail_unloaded,cargo_unloaded,arr_gcd,operator_cd,scheduled,chargeable,WatchExt_Arr from tc_arrival_log where status='A' and Upper(Trim(reg_no))=Upper(Trim(p_reg_no));

 c_arr_details c_arrival_details%rowtype;

 user_ex Exception; --Single user defined exception to jump from the code and to inform the error to user.
Begin

 --Setting the user errors to 0, means initially there are no errors.
 p_user_errors := '0'; --0 means No Errors;
 v_ardtime :=p_date||' '||p_time;

 -- Step 1: To calculate the utc date and time and to find the differnce between the entered date and system date.
 p_user_errors := check_datetime(p_date,p_time);
 If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].actualTime.select();';
 raise user_ex;
 End If;
 --End of Step 1;

 --Step 2: To check for Registration No.
p_user_errors := check_reg_no(p_reg_no);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Reg_No.select();';
 raise user_ex;
End If;
--End of Step 2.

--Step 3: To check for Location.
p_user_errors := check_location(p_location_cd);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Dep_Location.select();';
 raise user_ex;
End If;
--End of Step 3.

--Step 4: To check for Operator.
p_user_errors := check_operator(p_operator_cd);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].OPERATOR_CD.select();';
 raise user_ex;
End If;
--End of Step 4.

--The below condition is to check whether the arrival exists for a given registration no.
p_user_errors := check_arrival(p_reg_no);
If(p_user_errors!='0') Then
 p_select_field := 'document.forms[0].Reg_No.select();';
 raise user_ex;
End If;
--End of checking the arrival existence.

--Step 7: To check for last parking date and time.
v_last_parked_datetime := last_parking_datetime(p_reg_no);
v_time_difference := datetime_difference(v_ardtime,v_last_parked_datetime);
 If(v_time_difference <0) Then
 p_user_errors := 'Departing Date and Time cannot be less than or equal or equal to previous parking date and time:'||v_last_parked_datetime||' & Arrival Time:'||v_ardtime;
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
--End of Step 7.

--Step 5: To check slot validation.
p_user_errors:= Slot_Checking(p_reg_no,p_date,p_time);
--p_user_errors := fn_check_slot(p_reg_no,p_date,p_time,'D',p_current_loc);
If(p_user_errors!='0') Then
 --p_user_errors := 'Please change the Departure Date / Time. The Departure Date / Time entered is conflicting with an existing movement, or The Flight is currently not on Bay.';
 p_select_field := 'document.forms[0].date.select();';
 raise user_ex;
Else
 p_user_errors := '0';
End If;
--End of Step 5.

--Step 6: To check for Last parked position.
v_position_type := last_parking_position(p_reg_no);
If(p_nature!='T' AND v_position_type='H') Then
 p_user_errors:= 'This Flight Is Not in Bay. Please Change The Parking Position To Bay Before Departing. Use Parking Register.';
 p_select_field:= 'document.forms[0].date.focus();';
 raise user_ex;
End If;
-- End of Step 6.

--Step 8: To check for credit facility and free facility and billable flag.
v_condition := 'Upper(Trim(Operator_Cd))=Upper(Trim('''||p_operator_cd||''')) and Upper(Trim(Location_Cd))=Upper(Trim('''||p_current_loc||'''))';
v_credit_facility := get_description('Nvl(Decode(CreditFacility,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);

v_free_facility := get_description('Nvl(Decode(FreeFacility_Flag,''Y'',''Y'',''N''),''N'')','Tc_Operator_Dt',v_condition);

If(v_free_facility = 'Y') Then
 v_billable_flag := 'N';
End If;
--End of Step 8.

--Step 9: To check whether ca12 no exists, if not generate a new ca12 no.
v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';

v_ca12_no := get_description('al.CA12_No','TC_Arrival_Log al,tc_ca12info ca',v_condition);

If(v_ca12_no != '0') Then
 v_ca12_new := 'F';
 v_condition := 'Status=''A'' And al.CA12_No IS NOT NULL and al.ca12_no=ca.ca12_no And Upper(Trim(al.Reg_No))=Upper(Trim('''||p_reg_no||'''))';
 v_ncb_status := get_description('ca.ncb_status','TC_Arrival_Log al,tc_ca12info ca',v_condition);

 If(v_ncb_status='P') Then
 p_user_errors := 'The Operator of this Aircraft does not have Credit Facility And Complete Bill should be raised before its departure. Partial Bill has been Raised. Either Non Credit bill or Landing and Parking charges bill has been raised.';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
Else
 If(v_free_facility='N' AND v_credit_facility='N') Then
 p_user_errors := 'Bill has not raised for this Aircraft. The Operator of this Aircraft does not have credit facility and bill should be raised before its departure.';
 p_select_field:= 'document.forms[0].date.select();';
 raise user_ex;
 End If;
 v_ca12_no := get_nextcode_gby('Tc_ca12info','ca12_no',10,date_format('YYMMDD'));
End If;
--End of Step 9.

/*
v_condition := 'Upper(Trim(Flight_No))=Upper(Trim('''||p_flight_no||''')) and (Status in (''OS'',''DL'') OR Status is Null) And Flight_Dt='''||p_date||''' and Dep_Location='''||p_current_loc||'''';
v_bay_allocated := get_description('Bay_Allocated','Tc_EstimatedFlights',v_condition);
v_prioritized := get_description('Prioritized','Tc_EstimatedFlights',v_condition);
*/

/*
--Update estimated flights.
Update Tc_EstimatedFlights set Tow_Status='D', Tow_Time=p_time, Reg_No=Upper(Trim(p_reg_no)), Route_Cd=p_route_cd, Gcd=p_gcd Where Flight_no=p_flight_no and Flight_Dt=p_date and Dest_Location=Upper(Trim(p_location_cd)) and Dep_Location=p_current_loc;
*/

v_condition := 'Upper(Trim(Reg_No))=Upper(Trim('''||p_reg_no||''')) and Status=''A''';
v_fmuid := get_description('Fmuid','Tc_Arrival_Log',v_condition);

--Step 10: To Insert/Update the corresponding tables.

--Insert into parking register.
v_srno := get_nextcode_gby('Tc_Parking_Register','Srno',11,date_format('YYMMDD'));

Insert into Tc_Parking_Register(Fmuid,Srno,Reg_No,Parking_Date,Position_Type,Position_Cd,Actual_time,Status,Entered_Dt,Entered_by) values (v_fmuid,v_srno,Upper(Trim(p_reg_no)),p_date,'D',v_bay_allocated,p_time,'A',p_entered_date, p_entered_by);

--Calculate parking hours.
v_airport_type := get_description('Airport_Type','Tc_Location_Mt','Location_Cd='''||p_current_loc||'''');

v_bay_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'B');
v_bay_parking := roundoff_minutes('P',v_airport_type,p_nature,'R', p_date,v_bay_parking);

v_condition:= 'od.operator_cd=mt.operator_cd and od.location_cd='''||p_current_loc||''' and Upper(Trim(mt.reg_no))=Upper(Trim('''||p_reg_no||'''))';

If(get_description('od.Use_AAI_Housing','TC_OPERATOR_DT od,TC_AIRCRAFT_MT mt',v_condition)='Y') Then
 v_hangar_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'H');
 v_hangar_parking := roundoff_minutes('H',v_airport_type,p_nature,'R', p_date,v_hangar_parking);
Else
 v_hangar_parking := 0;
End If;

v_open_parking := get_parking_minutes(Upper(Trim(p_reg_no)),'O');
v_open_parking := roundoff_minutes('E',v_airport_type,p_nature,'R', p_date,v_open_parking);

--End of Calculating parking hours.

--Update parking register.
Update Tc_parking_Register set Status='D' where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';

v_runway_no := p_runway_no;
if(Trim(p_runway_no)='' or p_runway_no is null) Then
 v_runway_no := get_description('Rwy_No','Tc_Runway_Mt','Rownum <= 1');
End If;

--Insert into departure log.
select TIMEDIFFINMINS(p_time,p_dtd_time) d into v_Delay from dual;
select TIMEDIFFINMINS(p_time,p_start_time) dl into v_DelayPb from dual;
--DelayPbValue
/* Formatted on 2011/06/20 12:28 (Formatter Plus v4.8.8) */
INSERT INTO tc_departure_log
 (fmuid, flight_no, dep_date, dep_reg_no,
 dest_location, startuptime, atd, dtd,
 Delay, delaypb, rwy_no, pilot_in_cmd, hyt_no,
 pob, transit_pax, mail_loaded,
 cargo_loaded, bay_allocated, prioritized, scheduled,
 nature, touchgo_status, ca12_no, uncontrolled_location,
 route_cd, dep_gcd, remarks, entered_by, entered_dt,
 dep_nop, use_aai_xray, hajflight, operator_cd,
 service_units, fullstop_landingdate,
 chargeable, noof_touchdowns, common_counter, TYPE
 )
 VALUES (v_fmuid, p_flight_no, p_date, UPPER (TRIM (p_reg_no)),
 UPPER (TRIM (p_location_cd)),p_start_time, p_time, p_dtd_time, v_Delay,
 v_DelayPb, v_runway_no, p_pilot_in_command, p_ficadc, p_pob,
 p_transit_passengers, p_mail_loaded, p_cargo_loaded,
 v_bay_allocated, v_prioritized, p_scheduled, p_nature,
 p_touchgo_status, v_ca12_no, p_uncontrolled_loc, p_route_cd,
 p_gcd, p_remarks, p_entered_by, p_entered_date, p_nop,
 NVL (p_use_aaixray, 'N'), p_haj_flight, p_operator_cd, '0',
 p_fullstop_landdate, p_chargeable_flight,
 NVL (p_nooftouchdowns, 0), NVL (p_commoncounter, 'N'), p_type
 );
--The below two statements are to fetch corresponding arrival details.
open c_arrival_details;
fetch c_arrival_details into c_arr_details;

--Insert/Update ca12info.
If(v_ca12_new ='T') Then
 Insert Into Tc_Ca12Info(Fmuid,Ca12_No,Reg_No,Arr_Flight_No,Arr_Date,Arr_Gmt,Dep_Location,Arr_Pob, Mail_Unloaded,Cargo_Unloaded,Arr_FlightNature,Dep_Flight_No,Dep_Date,Dep_GMT,Dest_Location,Dep_POB, Mail_Loaded,Cargo_Loaded,Dep_FlightNature,DepScheduled,Billable_Flag,Operator_Cd,Arr_Gcd,Dep_Gcd, Uncontrolled_Location,Manual_Flag,Use_AAI_Xray,Arr_Operator_Cd,Dep_Operator_Cd,ArrScheduled, Arr_Chargeable,Dep_Chargeable,Parking_Hrs,Housing_Hrs,Open_Parking_Hours,Noof_TouchDowns,Common_Counter,Arr_Watch_Hrs,Dep_Watch_Hrs,Type,Entered_By,Entered_dt) Values (v_fmuid,v_ca12_no,Upper(Trim(p_reg_no)),c_arr_details.flight_no,c_arr_details.arr_date,c_arr_details.ata, c_arr_details.dep_location,c_arr_details.Arr_Pob,c_arr_details.Mail_Unloaded, c_arr_details.Cargo_Unloaded,c_arr_details.Nature,p_flight_No,p_date,p_time,p_location_cd,p_pob, p_mail_loaded,p_cargo_loaded,p_nature,p_scheduled,v_billable_flag,p_operator_cd, c_arr_details.arr_gcd,p_gcd,p_uncontrolled_loc,'N',Nvl(p_use_aaixray,'N'),c_arr_details.operator_cd, p_operator_cd,c_arr_details.scheduled,c_arr_details.chargeable,p_chargeable_flight,v_bay_parking, v_hangar_parking,v_open_parking,Nvl(p_nooftouchdowns,0),Nvl(p_commoncounter,'N'),c_arr_details.WatchExt_Arr,p_watch_extension,p_type,p_entered_by,p_entered_date);

Else
 If(v_credit_facility='Y') Then
 Update TC_Ca12Info Set Dep_Flight_No=p_flight_no,Dep_Date=p_date,Dep_GMT=p_time,Dest_Location=Upper(Trim(p_location_cd)), Dep_POB=p_pob,Mail_Loaded=p_mail_loaded,Cargo_Loaded=p_cargo_loaded, Arr_Date=c_arr_details.arr_date,Arr_Gmt=c_arr_details.ata, Dep_FlightNature=p_nature,DepScheduled=p_scheduled,Parking_Hrs=v_bay_parking, Housing_Hrs=v_hangar_parking,Billable_Flag=v_billable_flag,Operator_Cd=p_operator_cd, DEP_GCD=p_gcd,Uncontrolled_Location=p_uncontrolled_loc,Manual_Flag='N',Entered_By=p_entered_by, Entered_Dt=p_entered_date,Open_Parking_Hours=v_open_parking,Use_AAI_Xray=Nvl(p_use_aaixray,'N'), Dep_Operator_Cd=p_operator_cd,Dep_Chargeable=p_chargeable_flight,Noof_TouchDowns=Nvl(p_nooftouchdowns,0),Common_Counter=Nvl(p_commoncounter,'N'),Arr_Watch_Hrs=c_arr_details.WatchExt_Arr,Dep_Watch_Hrs=p_watch_extension,Type=p_type where CA12_No=v_ca12_no;
 --v_temp := 'updating ca12 '||v_ca12_new||' '||v_credit_facility;
 End If;
End If;
--End of Step 10.
close c_arrival_details;

 --Update Arrival Log.
Update Tc_Arrival_Log set Status='D',Ca12_No=v_ca12_no where Upper(Trim(Reg_No))=Upper(Trim(p_reg_no)) and Status='A';

/***************************************************************************************************
--This block is for testing purpose, where ever you want test the value, just assign that value to v_temp variable.
If( v_temp!='0') Then
 p_user_errors := v_temp;
 p_select_field:= 'document.forms[0].date.focus();';
 raise user_ex;
End If;
*****************************************************************************************************/
 Commit;
 Exception
 When user_ex Then
 rollback;
End;

abcd

Saturday, May 25, 2013

A Procedure with Some of passing parameters and Returning with the Condition Values

No comments:

Post a Comment

About Me

Blog Archive