DECLARE 
    CURSOR multi_svc IS 
      SELECT DISTINCT wonum, 
                      wk_area 
      FROM   uns_premisemore 
      WHERE  ts_last_update > SYSDATE - 2 
             -- need to figure out what to do for timeframe here... 
             AND id_premise <> ' ' 
             AND cis_premise IS NULL 
             AND ts_sent = To_date('1/1/1900', 'DD/MM/YYYY') 
      -- identifies it as a multi-premise service WR 
      ORDER  BY wonum; 
    v_sql        NUMBER(7) := 0; 
    v_text       VARCHAR2(1000); 
    v_prem_count NUMBER(6) := 0; 
    n            NUMBER(4) := 0; 
BEGIN 
    FOR svc_rec IN multi_svc LOOP 
        /* Get the count of GIS premise IDs for this multi-premise WR */ 
        SELECT Count(id_premise) 
        INTO   v_prem_count 
        FROM   uns_premisemore 
        WHERE  wonum = svc_rec.wonum 
               AND wk_area = svc_rec.wk_area 
               AND id_premise <> ' ' 
               AND cis_premise IS NULL; 

        IF v_prem_count > 1 THEN 
          -- it's a multi-premise svc WR that has gotten all GIS IDs back from BUD
           /* For the service WO rows, get the child R1M WRs from Maximo */ 
          BEGIN 
              FOR multi_rec IN (SELECT wonum, 
                                       wk_area, 
                                       id_premise, 
                                       gis_sp_id 
                                FROM   uns_premisemore 
                                WHERE  cd_seq = 1 
                                       AND id_premise <> ' ' 
                                       AND wonum = svc_rec.wonum 
                                       AND wk_area = svc_rec.wk_area) LOOP 
                  dbms_output.Put_line('Outer multi-rec id premise: ' 
                                       || multi_rec.id_premise); 

                  /* Get next R1M WR number from Maximo to update with this GIS premise ID */
                   FOR mx_child_rec IN (SELECT wonum, 
                                              t_workarea 
                                       FROM   workorder 
                                       WHERE  parent = multi_rec.wonum 
                                              AND t_jobtype = 'R1M') LOOP 
                      dbms_output.Put_line('Inner mx_child_rec wonum: ' 
                                           || mx_child_rec.wonum); 

                      BEGIN 
                          /* Update this R1M WR with the svc WR's GIS premise ID and SP ID */
                           UPDATE uns_premisemore 
                          SET    id_premise = multi_rec.id_premise, 
                                 gis_sp_id = multi_rec.gis_sp_id, 
                                 ts_last_update = SYSDATE 
                          WHERE  wonum = mx_child_rec.wonum 
                                 AND wk_area = mx_child_rec.t_workarea 
                                 AND id_premise = ' '; 
                      EXCEPTION 
                          WHEN OTHERS THEN 
                            v_sql := SQLCODE; 

                            v_text := 'WO# ' 
                                      || mx_child_rec.wonum 
                                      || 
                  ' - Error updating R1M WR with GIS prem ID from svc WR ' 
                            || multi_rec.wonum 
                            || ': ' 
                            || Substr(SQLERRM, 1, 200); 

                  dbms_output.Put_line(v_text); -- for testing 
                      END; 
                  END LOOP; 
              END LOOP; 
          EXCEPTION 
              WHEN no_data_found THEN 
                v_sql := SQLCODE; 

                v_text := 'WO# ' 
                          || svc_rec.wonum 
                          || 
' - Unable to populate GIS IDs into R1M WRs; APWR remark missing for svc WR' 
        ; 

dbms_output.Put_line(v_text); 
WHEN OTHERS THEN 
v_sql := SQLCODE; 

v_text := 'WO# ' 
          || svc_rec.wonum 
          || ' - Error populating GIS IDs into R1M WRs for svc WR: ' 
          || Substr(SQLERRM, 1, 200); 

dbms_output.Put_line(v_text); 
END; 
ELSE 
  v_sql := 12; 

  v_text := 'WO# ' 
            || svc_rec.wonum 
            || 
' - Supposed to be a multi-premise WR, but there is not more than 1 premise'; 

dbms_output.Put_line(v_text); 
END IF; 
END LOOP; 
END; 
/

              FOR multi_rec IN (SELECT wonum, 
                                       wk_area, 
                                       id_premise, 
                                       gis_sp_id,
                                       ROW_NUMBER () OVER (ORDER BY wonum) rnouter  
                                FROM   uns_premisemore 
                                WHERE  cd_seq = 1 
                                       AND id_premise <> ' ' 
                                       AND wonum = svc_rec.wonum 
                                       AND wk_area = svc_rec.wk_area) LOOP 
                  dbms_output.Put_line('Outer multi-rec id premise: ' 
                                       || multi_rec.id_premise); 

                  /* Get next R1M WR number from Maximo to update with this GIS premise ID */
                   FOR mx_child_rec IN (SELECT wonum, t_workarea
                                        FROM   (SELECT wonum, 
                                                       t_workarea,
                                                       ROW_NUMBER () OVER (PARTITION BY parent ORDER BY wonum) rninner
                                                FROM   workorder 
                                                WHERE  parent = multi_rec.wonum 
                                                AND t_jobtype = 'R1M') 
                                        WHERE  rninner = multi_rec.rnouter) LOOP 
                      dbms_output.Put_line('Inner mx_child_rec wonum: ' 
                                           || mx_child_rec.wonum);