1.       FUNCTION getLastScLoadIdFromCreationNumber(p_so_creation_number IN NUMBER) RETURN NUMBER IS
  2.         v_poi_tin VARCHAR2(50);
  3.         v_real_employer_tin VARCHAR2(50);
  4.         v_vehicle_owner_id NUMBER(10);
  5.         v_last_sc_load_id NUMBER(10);
  6.     BEGIN
  7.     -- Get last so transaction : TIN + real_employer_tin + vehicle_owner
  8.     BEGIN
  9.         SELECT sot.poi_tin, p.real_employer_tin, vpc.vehicle_owner_id
  10.         INTO v_poi_tin,v_real_employer_tin,v_vehicle_owner_id
  11.         FROM social_office_transaction sot, person p, vo_person_category vpc
  12.         WHERE sot.so_creation_number = p_so_creation_number
  13.         AND vpc.vo_person_category_id = p.vo_person_category_id
  14.         AND sot.social_office_transaction_id = p.last_so_transaction_id;
  15.         EXCEPTION WHEN NO_DATA_FOUND THEN
  16.               RETURN NULL;
  17.     END;
  18.     IF v_poi_tin IS NOT NULL AND v_real_employer_tin IS NOT NULL AND v_vehicle_owner_id IS NOT NULL THEN
  19.  
  20.         -- Get last load with vo_id, and TIN
  21.         BEGIN
  22.         SELECT MAX(sc_load_id)
  23.         INTO v_last_sc_load_id
  24.         FROM sc_load
  25.         WHERE vehicle_owner_id = v_vehicle_owner_id
  26.         AND status = 'L'
  27.         AND poi_tin = v_poi_tin;
  28.         EXCEPTION WHEN NO_DATA_FOUND THEN
  29.               RETURN NULL;
  30.  
  31.         END;
  32.         IF v_last_sc_load_id IS NOT NULL THEN
  33.  
  34.         -- Within last load
  35.         RETURN v_last_sc_load_id;
  36.  
  37.         END IF;
  38.     END IF;
  39.     RETURN NULL;
  40.     END;