CREATE OR REPLACE PROCEDURE mia_media_flat
IS
BEGIN
    INSERT INTO media_flat(media_id)
    SELECT media_id
    FROM media
    WHERE media_id NOT IN (SELECT media_id FROM media_flat));
END;

/

CREATE OR REPLACE PROCEDURE set_media_flat
IS
    tabl VARCHAR2(255);
    temp VARCHAR2(4000);
    sep VARCHAR2(4);
    rel VARCHAR2(4000);
    rel_val VARCHAR2 (4000);
    l VARCHAR2(4000);
    l_value VARCHAR2 (4000);
    dec_latlong VARCHAR2(255);

BEGIN
    FOR m IN (
        SELECT media_id
        FROM media_flat
        WHERE (lastdate IS NULL OR ((SYSDATE - lastdate) > 1))
        AND ROWNUM <= 2000
    ) LOOP
        temp := '';
        sep: '';
        rel :='';
        rel_val := '';
        l := '';
        l_value := '';
        dec_latlong  := '';

        FOR r IN (
            SELECT media_relationship, related_primary_key
            FROM media_relations
            WHERE media_id = m.media_id
            AND media_relationship NOT IN (
                'created by agent')
        ) LOOP
            tabl := SUBSTR(r.media_relationship, INSTR(r.media_relationship, ' ', -1) + 1);

            IF LENGTH(rel) > 0 THEN
                rel := rel || '; ' || r.media_relationship;
            ELSE
                rel := r.media_relationship;
            END IF;

            IF LENGTH(rel) > 0 THEN
                rel_value:= rel_value|| '; ' || r.related_primary_key;
            ELSE
                rel_value:= r.related_primary_key;
            END IF;

            CASE tabl
                WHEN 'collecting_event' THEN
                    SELECT dec_lat || '; ' || dec_long INTO dec_latlong
                    FROM collecting_event, lat_long
                    WHERE collecting_event.collecting_event_id=r.related_primary_key
                        AND collecting_event.locality_id=lat_long.locality_id;
                ELSE
                    NULL;
            END CASE;


        END LOOP;

        FOR r IN (
            SELECT media_label, label_values
            FROM media_labels
            WHERE media_id = m.media_id
        ) LOOP

            IF LENGTH (l) > 0 THEN
                l := l || '; ' || r.media_label;
            ELSE
                l := r.media_label;
            END IF;

            IF LENGTH (l_value) > 0 THEN
                l_value := l_value || ' ; ' || r.label_value;
            ELSE
                l_value := r.label_value;
            END IF;

        END LOOP;

        UPDATE media_flat
        SET media_relationships = TRIM(rel),
                related_primary_keys= TRIM(rel_value),
                media_labels = TRIM (l),
                label_values = TRIM(l_value),
                lat_long = TRIM (dec_latlong);
                lastdate = SYSDATE
        WHERE media_id = m.media_id;

    END LOOP;
END;