--order 502719    
 /*
      CHANGE REPORT for Table EMPLOYEE
          - Adding column PHONENO
          - Adding column MOBILENO
          - Adding column FAXNO
          - Adding column EMAIL
          - Adding column REMARK
      ACTION is ALTER or modify Table EMPLOYEE
      */

ALTER TABLE EMPLOYEE ADD (PHONENO              VARCHAR2(15) NULL);


ALTER TABLE EMPLOYEE ADD (MOBILENO             VARCHAR2(15) NULL);


ALTER TABLE EMPLOYEE ADD (FAXNO                VARCHAR2(15) NULL);


ALTER TABLE EMPLOYEE ADD (EMAIL                VARCHAR2(300) NULL);


ALTER TABLE EMPLOYEE ADD (REMARK               VARCHAR2(80) NULL);
  
      /*
      ACTION is CREATE Table POSTALCODEACTIVITIES
      */

CREATE TABLE POSTALCODEACTIVITIES (
       POSTALCODE_ID        NUMBER NOT NULL,
       ACTIVITY_ID          NUMBER NOT NULL,
       SALESEMPLOYEE1_ID    NUMBER NULL,
       SALESEMPLOYEE2_ID    NUMBER NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1POSTALCODEACTIVITIES ON POSTALCODEACTIVITIES
(
       POSTALCODE_ID                  ASC
);

CREATE INDEX XIF2POSTALCODEACTIVITIES ON POSTALCODEACTIVITIES
(
       ACTIVITY_ID                    ASC
);

CREATE INDEX XIF3POSTALCODEACTIVITIES ON POSTALCODEACTIVITIES
(
       SALESEMPLOYEE1_ID              ASC
);

CREATE INDEX XIF4POSTALCODEACTIVITIES ON POSTALCODEACTIVITIES
(
       SALESEMPLOYEE2_ID              ASC
);


ALTER TABLE POSTALCODEACTIVITIES
       ADD  ( CONSTRAINT XPKPOSTALCODEACTIVITIES PRIMARY KEY (
              POSTALCODE_ID, ACTIVITY_ID) ) ;

      
      /*
      CHANGE REPORT for Table CUSTOMER
          - Adding column SALESEMPLOYEE1_ID
          - Adding column SALESEMPLOYEE2_ID
      ACTION is ALTER or modify Table CUSTOMER
      */

ALTER TABLE CUSTOMER ADD (SALESEMPLOYEE1_ID    NUMBER NULL);


ALTER TABLE CUSTOMER ADD (SALESEMPLOYEE2_ID    NUMBER NULL);


CREATE INDEX XIF37CUSTOMER ON CUSTOMER
(
       SALESEMPLOYEE1_ID    ASC
);

CREATE INDEX XIF38CUSTOMER ON CUSTOMER
(
       SALESEMPLOYEE2_ID    ASC
);

      
      /*
      CHANGE REPORT for Table CUSTOMERACTIVITIES
          - Adding column SALESEMPLOYEE1_ID
          - Adding column SALESEMPLOYEE2_ID
          - Adding index XIF5CUSTOMERACTIVITIES
          - Adding index XIF6CUSTOMERACTIVITIES
      ACTION is ALTER or modify Table CUSTOMERACTIVITIES
      */

ALTER TABLE CUSTOMERACTIVITIES ADD (SALESEMPLOYEE1_ID    NUMBER NULL);


ALTER TABLE CUSTOMERACTIVITIES ADD (SALESEMPLOYEE2_ID    NUMBER NULL);

CREATE INDEX XIF5CUSTOMERACTIVITIES ON CUSTOMERACTIVITIES
(
       SALESEMPLOYEE1_ID              ASC
);

CREATE INDEX XIF6CUSTOMERACTIVITIES ON CUSTOMERACTIVITIES
(
       SALESEMPLOYEE2_ID              ASC
);

      
      /*
      CHANGE REPORT for Table POSTALCODE
          - Adding column SALESEMPLOYEE1_ID
          - Adding column SALESEMPLOYEE2_ID
          - Adding index XIF2POSTALCODE
          - Adding index XIF3POSTALCODE
      ACTION is ALTER or modify Table POSTALCODE
      */

ALTER TABLE POSTALCODE ADD (SALESEMPLOYEE1_ID    NUMBER NULL);


ALTER TABLE POSTALCODE ADD (SALESEMPLOYEE2_ID    NUMBER NULL);

CREATE INDEX XIF2POSTALCODE ON POSTALCODE
(
       SALESEMPLOYEE1_ID              ASC
);

CREATE INDEX XIF3POSTALCODE ON POSTALCODE
(
       SALESEMPLOYEE2_ID              ASC
);

      
      /*
      CHANGE REPORT for Table PRODUCTLISTPERCUSTOMER
          - Adding column SALESEMPLOYEE2_ID
          - Adding index XIF18PRODUCTLISTPERCUSTOMER
      ACTION is ALTER or modify Table PRODUCTLISTPERCUSTOMER
      */

ALTER TABLE PRODUCTLISTPERCUSTOMER ADD (SALESEMPLOYEE2_ID    NUMBER NULL);

CREATE INDEX XIF18PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER
(
       SALESEMPLOYEE2_ID              ASC
);
      
      /*
      CHANGE REPORT for Table RENTALQTYADJUSTMENT
          - Adding column SALESEMPLOYEE2_ID
          - Adding index XIF6RENTALQTYADJUSTMENT 
      ACTION is ALTER or modify Table RENTALQTYADJUSTMENT
      */

ALTER TABLE RENTALQTYADJUSTMENT ADD (SALESEMPLOYEE2_ID    NUMBER NULL);

CREATE INDEX XIF6RENTALQTYADJUSTMENT ON RENTALQTYADJUSTMENT 
(
       SALESEMPLOYEE2_ID  ASC
); 

      /*
      CHANGE REPORT for Table WORKORDERLINE
          - Adding column SALESEMPLOYEE1_ID
          - Adding column SALESEMPLOYEE2_ID
          - Adding index XIF11WORKORDERLINE
          - Adding index XIF12WORKORDERLINE
      ACTION is ALTER or modify Table WORKORDERLINE
      */

ALTER TABLE WORKORDERLINE ADD (SALESEMPLOYEE1_ID    NUMBER NULL);


ALTER TABLE WORKORDERLINE ADD (SALESEMPLOYEE2_ID    NUMBER NULL);

CREATE INDEX XIF11WORKORDERLINE ON WORKORDERLINE
(
       SALESEMPLOYEE1_ID              ASC
);

CREATE INDEX XIF12WORKORDERLINE ON WORKORDERLINE
(
       SALESEMPLOYEE2_ID              ASC
);


ALTER TABLE POSTALCODEACTIVITIES
       ADD  ( CONSTRAINT POSTALCODEACTIVITIES_SALESEMP2
              FOREIGN KEY (SALESEMPLOYEE2_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE POSTALCODEACTIVITIES
       ADD  ( CONSTRAINT POSTALCODEACTIVITIES_SALESEMP1
              FOREIGN KEY (SALESEMPLOYEE1_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE POSTALCODEACTIVITIES
       ADD  ( CONSTRAINT POSTALCODEACTIVITIES_ACTIVITY_
              FOREIGN KEY (ACTIVITY_ID)
                             REFERENCES ACTIVITY
                             ON DELETE CASCADE ) ;


ALTER TABLE POSTALCODEACTIVITIES
       ADD  ( CONSTRAINT POSTALCODEACTIVITIES_POSTALCOD
              FOREIGN KEY (POSTALCODE_ID)
                             REFERENCES POSTALCODE
                             ON DELETE CASCADE ) ;


ALTER TABLE CUSTOMER
       ADD  ( CONSTRAINT CUST_SALESEMPLOYEE1_ID
              FOREIGN KEY (SALESEMPLOYEE1_ID)
                             REFERENCES EMPLOYEE ) ;

ALTER TABLE CUSTOMER
       ADD  ( CONSTRAINT CUST_SALESEMPLOYEE2_ID
              FOREIGN KEY (SALESEMPLOYEE2_ID)
                             REFERENCES EMPLOYEE ) ;

ALTER TABLE CUSTOMERACTIVITIES
       ADD  ( CONSTRAINT CUSTOMERACTIVITIES_SALESEMPLO2
              FOREIGN KEY (SALESEMPLOYEE2_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE CUSTOMERACTIVITIES
       ADD  ( CONSTRAINT CUSTOMERACTIVITIES_SALESEMPLO1
              FOREIGN KEY (SALESEMPLOYEE1_ID)
                             REFERENCES EMPLOYEE ) ;

ALTER TABLE POSTALCODE
       ADD  ( CONSTRAINT POSTALCODE_SALESEMPLOYEE1_ID
              FOREIGN KEY (SALESEMPLOYEE1_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE POSTALCODE
       ADD  ( CONSTRAINT POSTALCODE_SALESEMPLOYEE2_ID
              FOREIGN KEY (SALESEMPLOYEE2_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE PRODUCTLISTPERCUSTOMER
       ADD  ( CONSTRAINT PLPC_SALESEMPLOYEE2_ID
              FOREIGN KEY (SALESEMPLOYEE2_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE RENTALQTYADJUSTMENT
       ADD  ( CONSTRAINT RENTQTYADJ_SALESEMPLOYEE2_ID
              FOREIGN KEY (SALESEMPLOYEE2_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE WORKORDERLINE
       ADD  ( CONSTRAINT WORKORDERLINE_SALESEMPLOYEE1_I
              FOREIGN KEY (SALESEMPLOYEE1_ID)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE WORKORDERLINE
       ADD  ( CONSTRAINT WORKORDERLINE_SALESEMPLOYEE2_I
              FOREIGN KEY (SALESEMPLOYEE2_ID)
                             REFERENCES EMPLOYEE ) ;
--employeefunctiondialog 
insert into employeefunctiondialog (employeefunction_id,dialog_id,selectable,systemuser_id,timestamp)
select e.employeefunction_id , d.dialog_id, 'Y', getminsystemuser(), getcurrentdatetime()
from employeefunction e, dialogs d
 where d.code in ('CustomerActivities', 'StandardDelivery', 'WorkOrder',
              'PostalCodesPerCountry');

--COUNTRYPOSTALINFO
create or replace procedure CREATEPOSTALCODEINFO(country_code_    in country.code%type,
                                                 pcreturnsstate_  in countrypostalinfo.pcreturnsstate%type,
                                                 pcreturnscounty_ in countrypostalinfo.pcreturnscounty%type,
                                                 pcreturnscity_   in countrypostalinfo.pcreturnscity%type,
                                                 pcreturnsstreet_ in countrypostalinfo.pcreturnsstreet%type)
IS
/*If the POSTALCODEINFO-record already exists for the country, then it is ignored and not overwritten;
Only new entries for non-existing countries will be created
*/
  cursor get_countries(cc_ varchar2) is
    select *
    from country c
    where c.code = cc_
    and not exists (select 1
                    from countrypostalinfo cpi
                    where cpi. country_id = c.country_id);
begin
  for country_rec_ in get_countries(country_code_) loop
     insert into countrypostalinfo
     values (country_rec_.country_id,
             nvl(pcreturnsstate_,'N'), nvl(pcreturnscounty_,'N'),
             nvl(pcreturnscity_,'N'), nvl(pcreturnsstreet_,'N'),
             1, sysdate);
  end loop;
end CREATEPOSTALCODEINFO;
/


exec CREATEPOSTALCODEINFO('NL','N','N','Y','N');
/
exec CREATEPOSTALCODEINFO('DK','N','N','Y','N');
/
exec CREATEPOSTALCODEINFO('US','Y','Y','Y','N');
/
exec CREATEPOSTALCODEINFO('AT','N','N','Y','N');
/

drop procedure CREATEPOSTALCODEINFO;