how can I turn this proc into a implicit cursor

lou_nyc · *nix forums beginner Joined: 22 Jun 2005 Posts: 20

Folks,

PROCEDURE out4hours_PROC(his_rec_1 IN chas_his_rec_TYPE
,his_rec_2 IN chas_his_rec_TYPE)
IS
v_booking gate_containers.booking%TYPE := NULL;
v_action gate_containers.action%TYPE := NULL;
v_coop_date DATE := NULL;
v_user ssl.code%TYPE := NULL;
v_user_desc ssl.long_description%TYPE := NULL;

CURSOR fg_cur(pvisit gate_visit.visit%TYPE
,pref_id gate_containers.reference_id%TYPE
)
IS
SELECT gc.action, gc.booking, gv.coop_in_date, gc.ssl_user_code,
s.long_description
FROM gate_containers gc
,gate_visit gv
,ssl s
WHERE gc.visit = pvisit
AND gc.reference_id = pref_id
AND gc.visit = gv.visit
AND gc.ssl_user_code = s.code(+);

CURSOR tg_cur(pvisit gate_visit.visit%TYPE
,pref_id gate_containers.reference_id%TYPE
)
IS
SELECT gc.action, gc.booking, gv.coop_in_date, gc.ssl_user_code,
s.long_description
FROM gate_containers@tripoli gc
,gate_visit@tripoli gv
,ssl@tripoli s
WHERE gc.visit = pvisit
AND gc.reference_id = pref_id
AND gc.visit = gv.visit
AND gc.ssl_user_code = s.code(+);

CURSOR fa_cur(pvisit gate_visit.visit%TYPE
,pref_id gate_containers.reference_id%TYPE
)
IS
SELECT gc.action, gv.coop_in_date
FROM gate_containers gc
,gate_visit gv
WHERE gc.visit = pvisit
AND gc.reference_id = pref_id
AND gc.visit = gv.visit;

CURSOR ta_cur(pvisit gate_visit.visit%TYPE
,pref_id gate_containers.reference_id%TYPE
)
IS
SELECT gc.action, gv.coop_in_date
FROM gate_containers@tripoli gc
,gate_visit@tripoli gv
WHERE gc.visit = pvisit
AND gc.reference_id = pref_id
AND gc.visit = gv.visit;

BEGIN
IF his_rec_1.chassis = his_rec_2.chassis THEN
IF his_rec_1.out_date IS NULL OR his_rec_1.out_visit IS NULL OR
his_rec_1.out_reference_id IS NULL THEN
RAISE EXIT_EXCEPTION;
END IF;
IF his_rec_2.in_visit IS NULL OR his_rec_2.in_reference_id IS
NULL THEN
RAISE EXIT_EXCEPTION;
END IF;
-- Chassis has to have left thru Coop Depot NAKED
IF his_rec_1.area = 'F' THEN
OPEN fg_cur(his_rec_1.out_visit,his_rec_1.out_reference_id);
FETCH fg_cur INTO v_action, v_booking, v_coop_date, v_user,
v_user_desc;
CLOSE fg_cur;
IF v_action IS NULL
THEN
OPEN tg_cur(his_rec_1.out_visit,
his_rec_1.out_reference_id);
FETCH tg_cur INTO v_action, v_booking, v_coop_date,
v_user, v_user_desc;
CLOSE tg_cur;
IF v_action IS NULL
THEN
RAISE EXIT_EXCEPTION;
END IF;
END IF;

ELSE
OPEN tg_cur(his_rec_1.out_visit,his_rec_1.out_reference_id);
FETCH tg_cur INTO v_action, v_booking, v_coop_date, v_user,
v_user_desc;
CLOSE tg_cur;
IF v_action IS NULL
THEN
RAISE EXIT_EXCEPTION;
END IF;

END IF;
IF v_coop_date IS NULL OR v_action <>
global_PKG.DELIVERED_NAKED_CHASSIS THEN
RAISE EXIT_EXCEPTION;
END IF;
-- Chassis has to have returned thru Coop Depot NAKED
OPEN fa_cur(his_rec_2.in_visit,his_rec_2.in_reference_id);
FETCH fa_cur INTO v_action, v_coop_date;
CLOSE fa_cur;
IF v_action IS NULL
THEN
OPEN ta_cur(his_rec_2.in_visit,his_rec_2.in_reference_id);
FETCH ta_cur INTO v_action, v_coop_date;
CLOSE ta_cur;
IF v_action IS NULL
THEN
RAISE EXIT_EXCEPTION;
END IF;
END IF;

IF v_coop_date IS NULL OR v_action <>
global_PKG.RECEIVED_NAKED_CHASSIS THEN
RAISE EXIT_EXCEPTION;
END IF;
-- and, Chassis has to have out for more than 4 hours...
IF his_rec_2.in_date - his_rec_1.out_date > 1/6 THEN
write_to_chas_temp_PROC(his_rec_1
,his_rec_2.in_date
,his_rec_2.in_visit
,v_booking
,v_user
,v_user_desc);
END IF;
END IF;
EXCEPTION
WHEN EXIT_EXCEPTION THEN
NULL;
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20006,'Error in nvrm_PKG.out4hours_PROC
- '||SQLERRM);
END;

Google