how can I turn this proc into a implicit cursor

From: lou_nyc <lnlx_at_aol.com>
Date: 16 May 2006 07:01:59 -0700
Message-ID: <1147788119.440698.292460_at_j33g2000cwa.googlegroups.com>

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_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
               )

  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_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;
      -- 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; Received on Tue May 16 2006 - 16:01:59 CEST