rec_trip    trn_trips%ROWTYPE;
v_status    VARCHAR2(10) := TRN_DISPATCH_PKG.GET_TRIP_STATUS(:B_DISPATCH.TRIP_ID);
BEGIN
  TRN_DISPATCH_PKG.ASSIGN_DRIVER(
    IN_DRIVER_ID => :B_DISPATCH.DRIVER_ID,
    IN_TRIP_ID   => :B_DISPATCH.TRIP_ID,
    OUT_TAKIN    => v_takin);

IF TRN_DISPATCH_PKG.CHK_DRIVER_AVAILABLE(:B0.DRIVER_ID) = 'N' THEN
  message_al('Driver is not available for this time slot');
  RAISE FORM_TRIGGER_FAILURE;
END IF;

TRN_DISPATCH_PKG.CLOSE_TRIP(
  IN_TRIP_ID    => :B_TRIPS.TRIP_ID,
  IN_END_KM     => :B_TRIPS.END_KM,
  OUT_DISTANCE  => v_km_total);

v_fare := TRN_BILLING_PKG.CALC_TRIP_FARE(
  IN_TRIP_ID    => :B_TRIPS.TRIP_ID,
  IN_TARIFF_SEQ => :B_TRIPS.TARIFF_SEQ);
v_discount := TRN_BILLING_PKG.APPLY_DISCOUNT(
  IN_CLIENT_ID => :B0.CLIENT_ID,
  IN_AMOUNT    => v_fare);

:B_CLIENTS.TOTAL_DEBT := TRN_BILLING_PKG.GET_OUTSTANDING(
  IN_CLIENT_ID => :B_CLIENTS.CLIENT_ID);

IF TRN_FLEET_PKG.IS_VEHICLE_AVAILABLE(
     IN_VEHICLE_ID => :B_DISPATCH.VEHICLE_ID,
     IN_FROM_DATE  => :B_DISPATCH.DEPART_TIME,
     IN_TO_DATE    => :B_DISPATCH.EST_RETURN) = 'N' THEN
  message_al('Vehicle already booked for this period');
  RAISE FORM_TRIGGER_FAILURE;
END IF;

IF TRN_DRIVER_PKG.CHK_LICENCE_VALID(
     IN_DRIVER_ID    => :B_DISPATCH.DRIVER_ID,
     IN_VEHICLE_TYPE => :B_DISPATCH.VEHICLE_TYPE) = 'N' THEN
  message_al('Driver licence does not cover this vehicle type');
  RAISE FORM_TRIGGER_FAILURE;
END IF;
v_hours := TRN_DRIVER_PKG.CALC_HOURS_THIS_WEEK(:B_DISPATCH.DRIVER_ID);
IF v_hours > 40 THEN
  message_al('Driver exceeds weekly hours limit');
END IF;

:B_DISPATCH.EST_KM   := TRN_ROUTE_PKG.GET_ROUTE_DISTANCE(
  IN_FROM_LOC => :B_DISPATCH.ORIGIN_CODE,
  IN_TO_LOC   => :B_DISPATCH.DEST_CODE);
:B_DISPATCH.EST_MINS := TRN_ROUTE_PKG.GET_ESTIMATED_TIME(
  IN_ROUTE_SEQ  => :B_DISPATCH.ROUTE_SEQ,
  IN_DEPART_DT  => :B_DISPATCH.DEPART_TIME);

TRN_NOTIFY_PKG.SEND_TRIP_CONFIRM(
  IN_TRIP_ID    => v_new_trip_id,
  IN_CLIENT_ID  => :B0.CLIENT_ID);

PKG_FORM.SET_CURSOR_SHARING_FORCE;
IF PKG_FORM.GET_SYNC = 'Y' THEN
  REFRESH_FORM;
END IF;

IF SCREEN_GUARD_PKG.CHECK_SCREEN_ACTIVE(:SYSTEM.CURRENT_FORM) = 'N' THEN
  message_al('System is under maintenance. Please try again later.');
  RAISE FORM_TRIGGER_FAILURE;
END IF;

SELECT TRIP_ID, CLIENT_ID, DRIVER_ID, VEHICLE_ID, ROUTE_SEQ,
       DEPART_TIME, ARRIVAL_TIME, STATUS_CODE,
       START_KM, END_KM, (END_KM - START_KM) DISTANCE,
       TRN_DISPATCH_PKG.GET_TRIP_STATUS(TRIP_ID) STATUS_DESC,
       NOTES
FROM   TRN_TRIPS
ORDER BY DEPART_TIME DESC

rec_trip TRN_TRIPS%ROWTYPE;
SELECT * INTO rec_trip FROM TRN_TRIPS WHERE TRIP_ID = :B_TRIPS.TRIP_ID;
rec_trip.STATUS_CODE  := 'CLOSED';
rec_trip.ARRIVAL_TIME := SYSDATE;
rec_trip.END_KM       := :B_TRIPS.END_KM;
TRN_DISPATCH_PKG.CLOSE_TRIP(rec_trip, v_ok);

SELECT DRIVER_ID, FULL_NAME, LICENCE_NO, LICENCE_CATEGORY,
       LICENCE_EXPIRY, STATUS_CODE, HIRE_DATE, PERF_SCORE
FROM   TRN_DRIVERS
WHERE  STATUS_CODE != 'TERMINATED'
ORDER BY FULL_NAME

v_lic_cat TRN_DRIVERS.LICENCE_CATEGORY%TYPE;
SELECT LICENCE_CATEGORY INTO v_lic_cat
FROM   TRN_DRIVERS WHERE DRIVER_ID = :B_DISPATCH.DRIVER_ID;

SELECT VEHICLE_ID, PLATE_NO, VEHICLE_TYPE, CAPACITY_KG,
       CURRENT_KM, LAST_MAINT_DATE, FUEL_TYPE, STATUS_CODE
FROM   TRN_VEHICLES
WHERE  STATUS_CODE = 'ACTIVE'
ORDER BY PLATE_NO

v_km TRN_VEHICLES.CURRENT_KM%TYPE;
SELECT CURRENT_KM INTO v_km
FROM   TRN_VEHICLES WHERE VEHICLE_ID = :B_FLEET.VEHICLE_ID;
IF v_km - v_last_maint_km > 10000 THEN
  TRN_FLEET_PKG.SCHEDULE_MAINTENANCE(:B_FLEET.VEHICLE_ID);
END IF;

SELECT CLIENT_ID, COMPANY_NAME, CONTACT_NAME, PHONE,
       CONTRACT_TIER, CREDIT_LIMIT,
       TRN_BILLING_PKG.GET_OUTSTANDING(CLIENT_ID) TOTAL_DEBT
FROM   TRN_CLIENTS
ORDER BY COMPANY_NAME

SELECT INV_SEQ, TRIP_ID, CLIENT_ID, INV_DATE,
       GROSS_AMOUNT, DISCOUNT_AMOUNT, TAX_AMOUNT,
       NET_AMOUNT, STATUS_PAY, DUE_DATE
FROM   TRN_INVOICES
WHERE  CLIENT_ID = :B0.CLIENT_ID
ORDER BY INV_DATE DESC

rec_inv TRN_INVOICES%ROWTYPE;
rec_inv.TRIP_ID        := :B_TRIPS.TRIP_ID;
rec_inv.GROSS_AMOUNT   := v_fare;
rec_inv.DISCOUNT_AMOUNT:= v_discount;
TRN_BILLING_PKG.CREATE_INVOICE(rec_inv, v_ok);

SELECT ROUTE_SEQ, ROUTE_CODE, ORIGIN_CODE, DEST_CODE,
       DISTANCE_KM, AVG_DURATION_MIN, TARIFF_SEQ, ACTIVE_IND
FROM   TRN_ROUTES
WHERE  ACTIVE_IND = 'Y'
ORDER BY ROUTE_CODE

v_route TRN_ROUTES.ROUTE_SEQ%TYPE;
v_route := TRN_ROUTE_PKG.SUGGEST_ROUTE(
  IN_ORIGIN => :B_DISPATCH.ORIGIN_CODE,
  IN_DEST   => :B_DISPATCH.DEST_CODE);
:B_DISPATCH.ROUTE_SEQ := v_route;

SELECT TRIP_ID, DRIVER_FULL_NAME, PLATE_NO, VEHICLE_TYPE,
       ORIGIN_CODE, DEST_CODE, DEPART_TIME, EST_ARRIVAL,
       STATUS_DESC, EST_KM
FROM   TRN_ACTIVE_TRIPS_VW
WHERE  STATUS_CODE = 'IN_PROGRESS'
ORDER BY DEPART_TIME

SELECT BASE_RATE_PER_KM, MIN_CHARGE, NIGHT_SURCHARGE
INTO   v_rate, v_min, v_night
FROM   TRN_TARIFFS_VW
WHERE  TARIFF_SEQ   = :B_DISPATCH.TARIFF_SEQ
  AND  VEHICLE_TYPE = :B_DISPATCH.VEHICLE_TYPE
  AND  SYSDATE BETWEEN VALID_FROM AND NVL(VALID_TO, SYSDATE);

SELECT CODE, DESCRIPTION
FROM   SYSDICT_CODES
WHERE  CODE_TABLE = 'TRIP_STATUS'
ORDER BY SORT_ORDER

SELECT CODE, DESCRIPTION, SADE_3 LICENCE_REQUIRED
FROM   SYSDICT_CODES
WHERE  CODE_TABLE = 'VEHICLE_TYPE'
ORDER BY CODE

MSG_P  SYSDICT_CODES.DESCRIPTION%TYPE;
CODE_P SYSDICT_CODES.CODE%TYPE;
SELECT DESCRIPTION INTO MSG_P
FROM   SYSDICT_CODES WHERE CODE_TABLE = 'ERROR_CODES'
AND CODE = IN_CODE;

SELECT NVL(PROGRAM_TITLE, 'Screen') INTO v_title
FROM   SYSDICT_PROGRAMS
WHERE  PROGRAM_CODE = :SYSTEM.CURRENT_FORM;

WRITE_AUDIT_ENTRY(
  IN_USER       => USER,
  IN_VMS_USER   => :KOT_OP.VMS_USERNAME_OP,
  IN_FORM       => :SYSTEM.CURRENT_FORM,
  IN_ACTION     => 'OPEN');

IF v_outstanding_amount > 0 THEN
  CALL_FORM('DEMO002');
END IF;