-- ABS-43971
---------   PACKINGNOTELINE   ---------------
ALTER TABLE PACKINGNOTELINE ADD (CORRECTIONREASONCODE_ID NUMBER NULL);

CREATE INDEX XIF9PACKINGNOTELINE ON PACKINGNOTELINE (CORRECTIONREASONCODE_ID ASC);

ALTER TABLE PACKINGNOTELINE ADD CONSTRAINT PACKINGNOTELINE_CORREASCODE FOREIGN KEY (CORRECTIONREASONCODE_ID)
REFERENCES REASONCODE (REASONCODE_ID) ON DELETE SET NULL;


CREATE OR REPLACE PROCEDURE UPDATEREASONCODE
AS
BEGIN
  FOR X IN (SELECT PN.PACKINGNOTE_ID, PN.CORRECTIONREASONCODE_ID
              FROM PACKINGNOTE PN
             WHERE PN.CORRECTIONREASONCODE_ID IS NOT NULL) LOOP
    UPDATE PACKINGNOTELINE PNL
       SET PNL.CORRECTIONREASONCODE_ID = X.CORRECTIONREASONCODE_ID
     WHERE PNL.PACKINGNOTE_ID = X.PACKINGNOTE_ID;
  END LOOP;
  COMMIT;
  EXCEPTION
    WHEN OTHERS THEN 
     ROLLBACK;
END UPDATEREASONCODE;
/

EXECUTE UPDATEREASONCODE;
DROP PROCEDURE UPDATEREASONCODE;
