BEGIN
    SELECT fim_order_handler_pk,handle_name INTO v_fim_order_handler_SX_pk, v_handle_SX_name
    FROM NI_FIM_ORDER_HANDLER
    WHERE HANDLE_NAME = 'SFCOM_FIMEx';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('FIMEX Pack handler not set');
    GOTO the_end;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Exception occured during Fetch on Handlers, please check the data set on the NI_FIM_ORDER_HANDLER and Continue...');
        RAISE_APPLICATION_ERROR(-20001,'Error - '||SQLCODE||' -ERROR Message- '||SQLERRM);
    GOTO the_end;
END;

BEGIN
    SELECT fim_profile_pk,profile_name BULK COLLECT INTO va_fim_profile_SX_pk, va_profile_SX_name 
    FROM NI_FIM_PROFILE 
    WHERE fim_order_handler_fk = v_fim_order_handler_SX_pk;
    IF va_fim_profile_SX_pk.count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No FIM Profiles Present for FIMEX');
        GOTO the_end;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Exception occured during Fetch on profiles, please check the data set on the NI_FIM_PROFILE and Continue...');
        RAISE_APPLICATION_ERROR(-20001,'Error - '||SQLCODE||' -ERROR Message- '||SQLERRM);
    GOTO the_end;
END

FOR idx_profile IN va_fim_profile_SX_pk.first..va_fim_profile_SX_pk.last LOOP
BEGIN
     SELECT 
         SUBSTR(order_number,INSTR(order_number,'_',1,1)+1,LENGTH(order_number)) 
         BULK COLLECT INTO va_batch_id_sx
     FROM 
         ni_fim_order 
         WHERE 
         fim_profile_fk = va_fim_profile_SX_pk(idx_profile)
     AND 
         parent_order_fk IS NOT NULL;
                                      
fim_order_details_fetch_sx(    v_fim_fimord_udl_pk,
    v_fmsi_fimord_udl_pk,
    va_batch_id_sx,
    va_fim_profile_SX_pk(idx_profile),
    va_profile_SX_name(idx_profile));

    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Exception occured during Fetch For Batch IDs for FIMEX Orders, please check the data set on the NI_FIM_ORDER and Continue...');
        RAISE_APPLICATION_ERROR(-20001,'Error - '||SQLCODE||' -ERROR Message- '||SQLERRM);
        DBMS_OUTPUT.PUT_LINE('');
        CONTINUE;
    END;
END LOOP;

    FOR idx_order in 1..pa_batch_id.count loop
        BEGIN
        SELECT s.iccid,
            s.current_state,
            i.imsi_number,
            i.current_state,
            ud1.data_value as FIMORDER
            BULK COLLECT INTO va_iccid, va_iccid_current_state, va_imsi_number, va_imsi_current_state,va_fim_order
        FROM 
            ni_imsi i,
            ni_fim s,
            ni_user_data ud1, 
            ni_user_data ud2
        WHERE
            ud1.related_user_data_link = p_iccid_udl_pk
            AND ud1.parent_key = imsi_pk
            AND ud1.data_value = va_batch_id(idx_order)
            AND ud2.related_user_data_link = v_imsi_udl_pk
            AND ud2.parent_key = fim_pk
            AND ud2.data_value = va_batch_id(idx_order)
            AND s.fim_pk = i.related_fim;
    END LOOP;