| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> trying to improve this process
I have this proc....it is taking wayyy too much time....I am trying to
cut it the
amount of time.. any suggestions...
PROCEDURE chassis
IS
CURSOR his_CUR IS
select i.chassis
,i.chassis_id
,i.ssl_user_code
,INITCAP(s.long_description) SSL_USER_DESCRIPTION
,chm.lt_code
,i.in_date
,i.in_visit
,i.in_reference_id
,i.out_date
,i.out_visit
,i.out_reference_id
,i.in_trucker_code TRUCKER_CODE
,a.name TRUCKER_NAME
,i.remarks
,'F' AREA
from inv_chassis i
,chassis_masters chm
,ssl s
,accounts a
where i.chassis = chm.chassis
and i.ssl_user_code = s.code
and i.in_trucker_code = a.code(+)
UNION ALL
select h.chassis
,h.chassis_id
,h.ssl_user_code
,INITCAP(s.long_description) SSL_USER_DESCRIPTION
,h.lt_code
,h.in_date
,h.in_visit
,h.in_reference_id
,h.out_date
,h.out_visit
,h.out_reference_id
,h.out_trucker_code TRUCKER_CODE
,a.name TRUCKER_NAME
,h.remarks
,'F' AREA
from his_chassis h
,ssl s
,accounts a
where h.voided_date IS NULL
and h.ssl_user_code = s.code
and h.out_trucker_code = a.code(+)
UNION ALL
select i.chassis
,i.chassis_id
,i.ssl_user_code
,INITCAP(s.long_description) SSL_USER_DESCRIPTION
,chm.lt_code
,i.in_date
,i.in_visit
,i.in_reference_id
,i.out_date
,i.out_visit
,i.out_reference_id
,i.in_trucker_code TRUCKER_CODE
,a.name TRUCKER_NAME
,i.remarks
,'T' AREA
from inv_chassis_at_tripoli i
,chassis_masters_at_tripoli chm
,ssl_at_tripoli s
,accounts_at_tripoli a
where i.chassis = chm.chassis
and i.ssl_user_code = s.code
and i.in_trucker_code = a.code(+)
UNION ALL
select h.chassis
,h.chassis_id
,h.ssl_user_code
,INITCAP(s.long_description) SSL_USER_DESCRIPTION
,h.lt_code
,h.in_date
,h.in_visit
,h.in_reference_id
,h.out_date
,h.out_visit
,h.out_reference_id
,h.out_trucker_code TRUCKER_CODE
,a.name TRUCKER_NAME
,h.remarks
,'T' AREA
from his_chassis_at_tripoli h
,ssl_at_tripoli s
,accounts_at_tripoli a
where h.voided_date IS NULL
and h.ssl_user_code = s.code
and h.out_trucker_code = a.code(+);
TYPE his_tab_type IS TABLE OF his_cur%ROWTYPE; hisTab his_tab_type;
BEGIN
EXECUTE IMMEDIATE 'truncate TABLE nvrm_chas_temp';
OPEN his_CUR;
LOOP
FETCH his_CUR BULK COLLECT INTO hisTab LIMIT 5000 ;
nc := 0;
ChassisTab.DELETE;
FOR K IN 1 .. hisTab.COUNT LOOP
out4hours_PROC(his1_REC, hisTab(K));
-- COMMIT;
-- move his rec up...
his1_REC := hisTab(K);
END LOOP;
FORALL K IN 1 .. ChassisTab.COUNT
INSERT INTO nvrm_chas_temp VALUES ChassisTab(K);
COMMIT;
EXIT WHEN his_CUR%NOTFOUND;
IF his_CUR%ISOPEN THEN
CLOSE his_CUR;
END IF;
ROLLBACK;
RAISE_APPLICATION_ERROR(-20006,'Error in nvrm_PKG.chassis -
'||SQLERRM);
end nvrm_PKG;
/
The out4hours_PROC is the following;
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_at_tripoli gc
,gate_visit_at_tripoli gv
,ssl_at_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
)
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
)
FROM gate_containers_at_tripoli gc
,gate_visit_at_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;
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;
ROLLBACK;
RAISE_APPLICATION_ERROR(-20006,'Error in nvrm_PKG.out4hours_PROC
- '||SQLERRM);
 |
 |