博主写的Route安排的pkb源代码,注意关键部位的日期排序算法。

没有什么注释，大家可能看不太清楚。就当一个随笔吧。

 

CREATE OR REPLACE PACKAGE BODY TMS.tms_reschedule
AS
   PROCEDURE reschedule_po
   IS
      v_range_date   DATE := SYSDATE - 20 / 60 / 24;
      v_cnt          INTEGER;
      v_cnt2         INTEGER;
   BEGIN
      EXECUTE IMMEDIATE 'truncate table tms_temp_reschedule_fastrack';

      EXECUTE IMMEDIATE 'truncate table truck_route_temp_vw';

      --The temp table will populate all candidate data to rearrange.
      INSERT INTO tms.tms_temp_reschedule_fastrack (vendor4,
                                                    po_id,
                                                    pu_dt,
                                                    route_no,
                                                    weight,
                                                    intf_dt,
                                                    intf_status_cd,
                                                    truck_route_id,
                                                    dow,
                                                    fastrack_rowid)
         SELECT   ftk.*,
                  TO_NUMBER (SUBSTR (ftk.route_no, 2)) truck_route_id,
                  TO_CHAR (ftk.pu_dt - 1, 'DY') dow,
                  ftk.ROWID fastrack_rowid
           FROM   tms_intf_legacy_fastrack ftk
          WHERE   intf_dt >= v_range_date
                  AND REGEXP_LIKE (
                        ftk.route_no,
                        '^[A, D, F, G, H, I, J, K, M, N, O, S, U, V, W, X]{1}[0-9]+$'
                     )
                  -- AND TO_CHAR (ftk.pu_dt - 1, 'DY') NOT IN ('FRI', 'SAT')
                  AND ftk.po_id IS NOT NULL
                  AND intf_status_cd = 'DONE'
                  AND EXISTS
                        (SELECT   1
                           FROM   tms_location loc
                          WHERE   loc.vendor4 = ftk.vendor4
                                  AND NOT EXISTS
                                        (SELECT   1
                                           FROM   tms.tms_asn_vendors asn
                                          WHERE   asn.location_id =
                                                     loc.location_id))
                  AND EXISTS
                        (SELECT   1
                           FROM   wms_truck_route_vw vw
                          WHERE   vw.truck_route_id =
                                     TO_NUMBER (SUBSTR (ftk.route_no, 2))
                                  AND vw.dow NOT IN ('FRI', 'SAT'))
                  AND NOT EXISTS
                        (SELECT   1
                           FROM   tms_po po,
                                  tms_route_header rh,
                                  tms_route_stop rs,
                                  tms_location loc
                          WHERE       rh.host_created_dt > SYSDATE - 6
                                  AND ftk.po_id = po.po_id
                                  AND rs.stop_id = po.stop_id
                                  AND rh.truck_route_instance_id =
                                        rs.truck_route_instance_id
                                  AND rs.location_id = loc.location_id
                                  AND loc.vendor4 = ftk.vendor4
                                  AND rh.truck_route_id =
                                        SUBSTR (ftk.route_no, 2)
                                  AND rh.dow = TO_CHAR (ftk.pu_dt - 1, 'DY'));

      IF sql%ROWCOUNT = 0
      THEN
         RETURN;
      ELSE
         v_cnt := sql%ROWCOUNT;

         INSERT INTO tms_handheld_log (
                                          device_id,
                                          msg_dt,
                                          severity,
                                          category,
                                          msg_text,
                                          svr_dt,
                                          log_id
                    )
           VALUES   (
                        'RESCHEDULE_PO',
                        SYSDATE,
                        5,
                        'RESCHEDULE_PO',
                           'There are '
                        || v_cnt
                        || ' items to be rescheduled which job started at '
                        || TO_CHAR (v_range_date, 'YYYY-MM-DD HH24:MI:SS'),
                        SYSDATE,
                        tms.tms_handheld_seq.NEXTVAL
                    );
      END IF;

      --create table truck_route_temp_vw which is used to store candidate route/dow with number.
      INSERT INTO truck_route_temp_vw
         WITH candidate_data
                AS (SELECT   DISTINCT vw.truck_route_id, vw.dow
                      FROM   wms_truck_route_vw vw,
                             tms.tms_temp_reschedule_fastrack tmp
                     WHERE   vw.truck_route_id = tmp.truck_route_id
                             AND vw.dow NOT IN ('FRI', 'SAT'))
         SELECT   DISTINCT CASE candidate_data.dow
                              WHEN 'MON' THEN 1
                              WHEN 'TUE' THEN 2
                              WHEN 'WED' THEN 3
                              WHEN 'THU' THEN 4
                              --  WHEN 'FRI' THEN 5
                              --  WHEN 'SAT' THEN 6
                           WHEN 'SUN' THEN 0
                              ELSE 0
                           END
                              AS weekday,
                           candidate_data.truck_route_id,
                           CASE ftk.dow
                              WHEN 'MON' THEN 1
                              WHEN 'TUE' THEN 2
                              WHEN 'WED' THEN 3
                              WHEN 'THU' THEN 4
                              --   WHEN 'FRI' THEN 5
                              --  WHEN 'SAT' THEN 6
                           WHEN 'SUN' THEN 0
                              ELSE 0
                           END
                              default_weekday,
                           candidate_data.dow,
                           ftk.dow
           FROM   candidate_data, tms_temp_reschedule_fastrack ftk
          WHERE   ftk.truck_route_id = candidate_data.truck_route_id;

      --The value 5 is used to order the dow, this is a pace length. When the weekday minus original_weekday
      --gets a negative value , they should be after those positive value.
      UPDATE   tms.tms_temp_reschedule_fastrack tmp
         SET   tmp.alter_dow =
                  (SELECT   nextdow
                     FROM   (SELECT   truck_route_id,
                                      original_weekday,
                                      original_dow,
                                      FIRST_VALUE(vw.dow)
                                         OVER (
                                            PARTITION BY vw.truck_route_id,
                                                         vw.original_weekday
                                            ORDER BY
                                               (DECODE (
                                                   SIGN(vw.weekday
                                                        - vw.original_weekday),
                                                   -1,
                                                   vw.weekday
                                                   + (5 - vw.original_weekday),
                                                   vw.weekday
                                                   - vw.original_weekday
                                                ))
                                         )
                                         nextdow
                               FROM   truck_route_temp_vw vw) vvw
                    WHERE       vvw.truck_route_id = tmp.truck_route_id --  AND vw.dow != tmp.dow
                            AND vvw.original_dow = tmp.dow
                            AND ROWNUM <= 1);

      UPDATE   tms_intf_legacy_fastrack ftk
         SET   intf_status_cd = 'READY',
               ftk.pu_dt =
                  NVL (
                     (SELECT   MIN(CASE tmp.alter_dow
                                      WHEN TO_CHAR (ftk.pu_dt - 1, 'DY')
                                      THEN
                                         ftk.pu_dt
                                      ELSE
                                         NEXT_DAY (ftk.pu_dt - 1,
                                                   tmp.alter_dow)
                                         + 1
                                   END)
                        FROM   tms.tms_temp_reschedule_fastrack tmp
                       WHERE                          -- alter_dow IS NOT NULL
                               --  AND
                               tmp.fastrack_rowid = ftk.ROWID),
                     pu_dt
                  )
       WHERE       ftk.intf_status_cd = 'DONE'
               AND ftk.intf_dt >= v_range_date
               AND EXISTS (SELECT   1
                             FROM   tms_temp_reschedule_fastrack tmp
                            WHERE                  --tmp.alter_dow IS NOT NULL
                                    -- AND
                                    tmp.fastrack_rowid = ftk.ROWID);

      IF sql%ROWCOUNT = 0
      THEN
         INSERT INTO tms_handheld_log (
                                          device_id,
                                          msg_dt,
                                          severity,
                                          category,
                                          msg_text,
                                          svr_dt,
                                          log_id
                    )
           VALUES   (
                        'RESCH_PO_ERR1',
                        SYSDATE,
                        5,
                        'RESCHEDULE_PO',
                        'There is an error that 0 items to be rescheduled which job started at '
                        || TO_CHAR (v_range_date, 'YYYY-MM-DD HH24:MI:SS'),
                        SYSDATE,
                        tms.tms_handheld_seq.NEXTVAL
                    );
      ELSE
         v_cnt2 := sql%ROWCOUNT;

         IF v_cnt2 != v_cnt
         THEN
            INSERT INTO tms_handheld_log (
                                             device_id,
                                             msg_dt,
                                             severity,
                                             category,
                                             msg_text,
                                             svr_dt,
                                             log_id
                       )
              VALUES   (
                           'RESCH_PO_ERR2',
                           SYSDATE,
                           5,
                           'RESCHEDULE_PO',
                           'There are ' || v_cnt2
                           || ' items actually be rescheduled which should be '
                           || v_cnt
                           || ' items which job started at '
                           || TO_CHAR (v_range_date, 'YYYY-MM-DD HH24:MI:SS'),
                           SYSDATE,
                           tms.tms_handheld_seq.NEXTVAL
                       );
         END IF;
      END IF;

      COMMIT;
   END;
END tms_reschedule;
/