-- Code Fragment 11.24

SELECT PEN_ID, HD_CNT
FROM LOT_LOC
WHERE FDYD_ID = 1 AND LOT_ID_NUM = 219
  AND TO_DATE = TO_DATE('31-DEC-9999', 'DD-MON-YYYY') ;


-- Code Fragment 11.25

SELECT PEN_ID, HD_CNT, FROM_DATE, TO_DATE
FROM LOT_LOC
WHERE FDYD_ID = 1 AND LOT_ID_NUM = 219;


-- Code Fragment 11.26

SELECT PEN_ID, HD_CNT, FROM_DATE, TO_DATE
FROM LOT_LOC
WHERE FDYD_ID = 1 AND LOT_ID_NUM = 219;


-- Code Fragment 11.27

SELECT DISTINCT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID
FROM LOT_LOC L1, LOT_LOC L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
  AND L1.FDYD_ID = L2.FDYD_ID
  AND L1.PEN_ID = L2.PEN_ID;


-- Code Fragment 11.28

SELECT DISTINCT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID
FROM LOT_LOC L1, LOT_LOC L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
  AND L1.FDYD_ID = L2.FDYD_ID
  AND L1.PEN_ID = L2.PEN_ID
  AND L1.TO_DATE = TO_DATE('31-DEC-9999', 'DD-MON-YYYY')
  AND L2.TO_DATE = TO_DATE('31-DEC-9999', 'DD-MON-YYYY') ;


-- Code Fragment 11.29

SELECT DISTINCT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID
FROM LOT_LOC L1, LOT_LOC L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
  AND L1.FDYD_ID = L2.FDYD_ID
  AND L1.PEN_ID = L2.PEN_ID;


-- Code Fragment 11.30

SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L1.FROM_DATE, L1.TO_DATE
FROM LOT_LOC L1, LOT_LOC L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
  AND L1.FDYD_ID = L2.FDYD_ID
  AND L1.PEN_ID = L2.PEN_ID
  AND L2.FROM_DATE <= L1.FROM_DATE
  AND L1.TO_DATE <= L2.TO_DATE
UNION
SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L1.FROM_DATE, L2.TO_DATE
FROM LOT_LOC L1, LOT_LOC L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
  AND L1.FDYD_ID = L2.FDYD_ID
  AND L1.PEN_ID = L2.PEN_ID
  AND L1.FROM_DATE > L2.FROM_DATE
  AND L2.TO_DATE < L1.TO_DATE
  AND L1.FROM_DATE < L2.TO_DATE
UNION
SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L2.FROM_DATE, L1.TO_DATE
FROM LOT_LOC L1, LOT_LOC L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
  AND L1.FDYD_ID = L2.FDYD_ID
  AND L1.PEN_ID = L2.PEN_ID
  AND L2.FROM_DATE > L1.FROM_DATE
  AND L1.TO_DATE < L2.TO_DATE
  AND L2.FROM_DATE < L1.TO_DATE
UNION
SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID, L2.FROM_DATE, L2.TO_DATE
FROM LOT_LOC L1, LOT_LOC L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
  AND L1.FDYD_ID = L2.FDYD_ID
  AND L1.PEN_ID = L2.PEN_ID
  AND L2.FROM_DATE >= L1.FROM_DATE
  AND L2.TO_DATE <= L1.TO_DATE;


-- Code Fragment 11.31

SELECT L1.LOT_ID_NUM, L2.LOT_ID_NUM, L1.PEN_ID,
      CASE WHEN L1.FROM_DATE > L2.FROM_DATE
        THEN L1.FROM_DATE
        ELSE L2.FROM_DATE END,
      CASE WHEN L1.TO_DATE > L2.TO_DATE
        THEN L2.TO_DATE
        ELSE L1.TO_DATE END
FROM LOT_LOC L1, LOT_LOC L2
WHERE L1.LOT_ID_NUM < L2.LOT_ID_NUM
  AND L1.FDYD_ID = L2.FDYD_ID
  AND L1.PEN_ID = L2.PEN_ID
  AND (CASE WHEN L1.FROM_DATE > L2.FROM_DATE
        THEN L1.FROM_DATE
        ELSE L2.FROM_DATE END) <
      (CASE WHEN L1.TO_DATE > L2.TO_DATE
        THEN L2.TO_DATE
        ELSE L1.TO_DATE END);


-- Code Fragment 11.32

/* INSERT INTO LOT
VALUES (433, 'h', SYSDATE, '31-DEC-9999'); */

INSERT INTO LOT VALUES
   (1, 433, 10, 'h', '31-DEC-99', 100, 1, 'R', 'LOT1', SYSDATE, TO_DATE('31-DEC-9999', 'DD-MON-YYYY'), '01-JAN-98', TO_DATE('31-DEC-9999', 'DD-MON-YYYY')) ;


-- Code Fragment 11.33

UPDATE LOT
SET TO_DATE = SYSDATE
WHERE LOT_ID_NUM = 234
  AND TO_DATE >= SYSDATE
  AND FROM_DATE < SYSDATE;

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
  AND FROM_DATE > SYSDATE;


-- Code Fragment 11.34

INSERT INTO LOT
SELECT FDYD_ID, LOT_ID_NUM, LOT_ID, 's' , PROJ_CLOSE_OUT, IN_WEIGHT, VALID, OWNER, LOT_COMMENT, SYSDATE, TO_DATE, START_DATE, STOP_DATE
FROM LOT
WHERE LOT_ID_NUM = 799
  AND FROM_DATE <= SYSDATE
  AND TO_DATE > SYSDATE;

UPDATE LOT
SET TO_DATE = SYSDATE
WHERE LOT_ID_NUM = 799
  AND GNDR_CODE <> 's'
  AND FROM_DATE < SYSDATE
  AND TO_DATE > SYSDATE;

UPDATE LOT
SET GNDR_CODE = 's'
WHERE LOT_ID_NUM = 799
  AND FROM_DATE >= SYSDATE;


-- Code Fragment 11.35

/* INSERT INTO LOT
VALUES (426, 'h', '1998-03-26', '1998-04-14'); */

INSERT INTO LOT VALUES
   (1, 426, 10, 'h', '31-DEC-99', 100, 1, 'R', 'LOT1', '26-MAR-98', '14-APR-98', '01-JAN-98', TO_DATE('31-DEC-9999', 'DD-MON-YYYY')) ;


-- Code Fragment 11.36

INSERT INTO LOT
SELECT FDYD_ID, LOT_ID_NUM, LOT_ID, GNDR_CODE, PROJ_CLOSE_OUT, IN_WEIGHT, VALID, OWNER, LOT_COMMENT, '22-OCT-98', TO_DATE, START_DATE, STOP_DATE 
FROM LOT
WHERE LOT_ID_NUM = 234
  AND FROM_DATE <= '01-OCT-98'
  AND TO_DATE > '22-OCT-98'

UPDATE LOT
SET TO_DATE = '01-OCT-98'
WHERE LOT_ID_NUM = 234
  AND FROM_DATE < '01-OCT-98'
  AND TO_DATE >= '01-OCT-98';

UPDATE LOT
SET FROM_DATE = '22-OCT-98'
WHERE LOT_ID_NUM = 234
  AND FROM_DATE < '22-OCT-98'
  AND TO_DATE >= '22-OCT-98';

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
  AND FROM_DATE >= '01-OCT-98'
  AND TO_DATE <= '22-10-98';


-- Code Fragment 11.37

INSERT INTO LOT
SELECT FDYD_ID, LOT_ID_NUM, LOT_ID, GNDR_CODE, PROJ_CLOSE_OUT, IN_WEIGHT, VALID, OWNER, LOT_COMMENT, FROM_DATE, '01-MAR-98', START_DATE, STOP_DATE
FROM LOT
WHERE LOT_ID_NUM = 799
  AND FROM_DATE < '01-MAR-98'
  AND TO_DATE > '01-MAR-98'

INSERT INTO LOT
SELECT FDYD_ID, LOT_ID_NUM, LOT_ID, GNDR_CODE, PROJ_CLOSE_OUT, IN_WEIGHT, VALID, OWNER, LOT_COMMENT, '01-APR-98', TO_DATE, START_DATE, STOP_DATE
FROM LOT
WHERE LOT_ID_NUM = 799
  AND FROM_DATE < '01-APR-98'
  AND TO_DATE > '01-APR-98';

UPDATE LOT
SET GNDR_CODE = 's'
WHERE LOT_ID_NUM = 799
  AND FROM_DATE < '01-APR-98'
  AND TO_DATE > '01-MAR-98';

UPDATE LOT
SET FROM_DATE = '01-MAR-98'
WHERE LOT_ID_NUM = 799
  AND FROM_DATE < '01-MAR-98'
  AND TO_DATE > '01-MAR-98';

UPDATE LOT
SET TO_DATE = '01-APR-98'
WHERE LOT_ID_NUM = 799
  AND FROM_DATE < '01-APR-98'
  AND TO_DATE > '01-APR-98';


-- Code Fragment 11.38

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
  AND MONTHS_BETWEEN(TO_DATE, FROM_DATE) > 3 ;


-- Code Fragment 11.39

INSERT INTO LOT
SELECT FDYD_ID, LOT_ID_NUM, LOT_ID, GNDR_CODE, PROJ_CLOSE_OUT, IN_WEIGHT, VALID, OWNER, LOT_COMMENT, '22-DEC-98', TO_DATE, START_DATE, STOP_DATE
FROM LOT
WHERE LOT_ID_NUM = 234
  AND FROM_DATE <= '01-OCT-98'
  AND TO_DATE > '22-OCT-98' ;

UPDATE LOT
SET TO_DATE = '01-OCT-98'
WHERE LOT_ID_NUM = 234
  AND FROM_DATE < '01-OCT-98'
  AND TO_DATE >= '01-OCT-98' ;

UPDATE LOT
SET FROM_DATE = '22-OCT-98'
WHERE LOT_ID_NUM = 234
  AND FROM_DATE < '22-OCT-98'
  AND TO_DATE >= '22-OCT-98' ;

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
  AND FROM_DATE >= '01-OCT-98'
  AND TO_DATE <= '22-OCT-98' ;


-- Code Fragment 11.40

INSERT INTO LOT
SELECT FDYD_ID, LOT_ID_NUM, LOT_ID, GNDR_CODE, PROJ_CLOSE_OUT, IN_WEIGHT, VALID, OWNER, LOT_COMMENT, FROM_DATE, '22-MAR-98', START_DATE, STOP_DATE
FROM LOT
WHERE LOT_ID_NUM = 799
  AND FROM_DATE < '01-MAR-98'
  AND TO_DATE > '22-MAR-98' ;

INSERT INTO LOT
SELECT FDYD_ID, LOT_ID_NUM, LOT_ID, GNDR_CODE, PROJ_CLOSE_OUT, IN_WEIGHT, VALID, OWNER, LOT_COMMENT, '01-APR-98', TO_DATE, START_DATE, STOP_DATE
FROM LOT
WHERE LOT_ID_NUM = 799
  AND FROM_DATE < '01-APR-98'
  AND TO_DATE > '22-APR-98' ;

UPDATE LOT
SET GNDR_CODE = 's'
WHERE LOT_ID_NUM = 799
  AND FROM_DATE < '01-APR-98'
  AND TO_DATE > '01-APR-98' ;

UPDATE LOT
SET FROM_DATE = '01-MAR-98'
WHERE LOT_ID_NUM = 799
  AND FROM_DATE < '01-MAR-98'
  AND TO_DATE > '01-MAR-98' ;

UPDATE LOT
SET TO_DATE = '01-APR-98'
WHERE LOT_ID_NUM = 799
  AND FROM_DATE < '01-APR-98'
  AND TO_DATE > '01-APR-98' ;


-- Code Fragment 11.41

DELETE FROM LOT
WHERE LOT_ID_NUM = 234
  AND MONTHS_BETWEEN(TO_DATE, FROM_DATE) > 3 ;



-- Code Fragment 11.42


