--- ABS-119994 / ABS-123937
------------- SORTBATCHDEFINITIONDETAIL UPDATE --------------
CREATE OR REPLACE PROCEDURE INSERT_SBDD AS
BEGIN
  FOR RS IN (SELECT RT.SORTBATCHDEFINITION_ID AS BATCHDEFINITION,
                             RS.ROUTESTOP_ID AS ROUTESTOP,
                             (SELECT NVL(MAX(SBDD.SEQUENCENUMBER), 0) + 1
                                FROM SORTBATCHDEFINITIONDETAIL SBDD
                               WHERE SBDD.SORTBATCHDEFINITION_ID =
                                     SBD.SORTBATCHDEFINITION_ID) AS SQN,
                             SU.SYSTEMUSER_ID AS SYSTEMUSER,
                             SYSDATE
                    FROM SORTBATCHDEFINITION SBD
                    JOIN ROUTE RT
                      ON RT.SORTBATCHDEFINITION_ID = SBD.SORTBATCHDEFINITION_ID
                    JOIN ROUTESTOP RS
                      ON RS.ROUTE_ID = RT.ROUTE_ID
                    JOIN SYSTEMUSER SU
                      ON SU.SYSTEMUSER_ID = RS.SYSTEMUSER_ID
                   WHERE RS.SPECIFICSORTBATCHDEFINITION_ID IS NULL
                   ORDER BY RS.STOPNUMBER ASC) LOOP
    BEGIN
           INSERT INTO SORTBATCHDEFINITIONDETAIL SBDD
                (SORTBATCHDEFINITIONDETAIL_ID,
                 SORTBATCHDEFINITION_ID,
                 ROUTESTOP_ID,
                 SEQUENCENUMBER,
                 SYSTEMUSER,
                 TIMESTAMP)
           VALUES
                 (
                 SEQ_SORTBATCHDEFINITIONDETAIL.NEXTVAL,
                 RS.BATCHDEFINITION,
                 RS.ROUTESTOP,
                NVL((SELECT MAX(SBDD.SEQUENCENUMBER) + 1
                    FROM SORTBATCHDEFINITIONDETAIL SBDD
                   WHERE SBDD.SORTBATCHDEFINITION_ID =
                         RS.BATCHDEFINITION),1),
                RS.SYSTEMUSER,
                 SYSDATE
                 );
             EXCEPTION 
              WHEN DUP_VAL_ON_INDEX THEN 
              NULL;  
                 END;
                 END LOOP;
                 COMMIT;

  FOR RS IN (SELECT RS.SPECIFICSORTBATCHDEFINITION_ID AS BATCHDEFINITION,
                           RS.ROUTESTOP_ID AS ROUTESTOP,
                           (SELECT NVL(MAX(SBDD.SEQUENCENUMBER), 0) + 1
                              FROM SORTBATCHDEFINITIONDETAIL SBDD
                             WHERE SBDD.SORTBATCHDEFINITION_ID =
                                   RS.SPECIFICSORTBATCHDEFINITION_ID) SQN,
                           SU.SYSTEMUSER_ID AS SYSTEMUSER,
                           SYSDATE
                    FROM ROUTESTOP RS
                    JOIN ROUTE RT
                      ON RS.ROUTE_ID = RT.ROUTE_ID
                    JOIN SYSTEMUSER SU
                      ON SU.SYSTEMUSER_ID = RS.SYSTEMUSER_ID
                   WHERE RS.SPECIFICSORTBATCHDEFINITION_ID IS NOT NULL
                   ORDER BY RT.ROUTENUMBER, RS.STOPNUMBER ASC) LOOP
    BEGIN
           INSERT INTO SORTBATCHDEFINITIONDETAIL SBDD
                (SORTBATCHDEFINITIONDETAIL_ID,
                 SORTBATCHDEFINITION_ID,
                 ROUTESTOP_ID,
                 SEQUENCENUMBER,
                 SYSTEMUSER,
                 TIMESTAMP)
           VALUES
                 (
                 SEQ_SORTBATCHDEFINITIONDETAIL.NEXTVAL,
                 RS.BATCHDEFINITION,
                 RS.ROUTESTOP,
                 NVL((SELECT MAX(SBDD.SEQUENCENUMBER) + 1
                    FROM SORTBATCHDEFINITIONDETAIL SBDD
                    WHERE SBDD.SORTBATCHDEFINITION_ID =
                         RS.BATCHDEFINITION),1),
                 RS.SYSTEMUSER,
                 SYSDATE
                 );
             EXCEPTION 
              WHEN DUP_VAL_ON_INDEX THEN 
              NULL; 
                 END;
                 END LOOP;
                 COMMIT;
END INSERT_SBDD;
/
EXECUTE INSERT_SBDD;
DROP PROCEDURE INSERT_SBDD;
