DECLARE
    v_poi_tin VARCHAR2(50);
    v_real_employer_tin VARCHAR2(50);
    v_vehicle_owner_id NUMBER(10);
    v_last_sc_load_id NUMBER(10);
BEGIN
-- Get last so transaction : TIN + real_employer_tin + vehicle_owner
SELECT sot.poi_tin, p.real_employer_tin, vpc.vehicle_owner_id
INTO v_poi_tin,v_real_employer_tin,v_vehicle_owner_id
FROM social_office_transaction sot, person p, vo_person_category vpc
WHERE p.person_id = :person_id
AND vpc.vo_person_category_id = p.vo_person_category_id
AND sot.social_office_transaction_id = p.last_so_transaction_id;

IF v_poi_tin != NULL AND v_real_employer_tin != NULL AND v_vehicle_owner_id != NULL THEN

    -- Get last load with vo_id, and TIN
    SELECT MAX(sc_load_id)
    INTO v_last_sc_load_id
    FROM sc_load
    WHERE vehicle_owner_id = v_vehicle_owner_id
    AND status = 'L'
    AND poi_tin = v_poi_tin;
    IF v_last_sc_load_id != NULL THEN

    -- Within last load, get sc chain
        SELECT t1.sc_load_data_id,
               t1.company_name,
               t1.company_tin,
               t1.parent_sc_load_data_id,
               DECODE (t2.children_count, NULL, 0, t2.children_count) AS children_count
               FROM
        (SELECT sld.sc_load_data_id,
               sld.company_name,
               sld.company_tin,
               sld.parent_sc_load_data_id
          FROM sc_load_data sld
         START WITH sld.parent_sc_load_data_id IS NULL
        CONNECT BY sld.parent_sc_load_data_id = PRIOR sld.sc_load_data_id
         ORDER SIBLINGS BY sld.company_name) t1,
         (SELECT parent_sc_load_data_id, COUNT (parent_sc_load_data_id) AS children_count FROM sc_load_data
         GROUP BY parent_sc_load_data_id) t2
         WHERE t1.sc_load_data_id = t2.parent_sc_load_data_id (+)
         AND t1.sc_load_id = v_last_sc_load_id;
    endif;
endif;

END;