--UPDATE FROM 8.7.2.1 TO 8.7.2.2


DROP TABLE VERSION_8_7_2_1;



/*  VERSION_8_7_2_2  */
CREATE TABLE VERSION_8_7_2_2 (
	 DUMMY	NUMBER	    NULL
)
;


ALTER TABLE XJET DROP COLUMN XJET_USE_COUNTRY_CODE;

ALTER TABLE XJET ADD XJET_LAND_COUNTRY_CODE NUMBER(1);

ALTER TABLE XJET ADD XJET_STAAT_COUNTRY_CODE NUMBER(1);

ALTER TABLE XJET ADD XJET_NATION_COUNTRY_CODE NUMBER(1);

ALTER TABLE MCFG ADD MCFG_READ_TW_INTERVAL NUMBER (5,0);

ALTER TABLE MMOD ADD MMOD_USEFTP NUMBER (1,0);

ALTER TABLE MMOD ADD MMOD_FTP_SERVER VARCHAR2(100);

ALTER TABLE MMOD ADD MMOD_FTP_USER VARCHAR2(30);

ALTER TABLE MMOD ADD MMOD_FTP_PWD VARCHAR2(30);

ALTER TABLE MMOD ADD MMOD_FTP_PORT NUMBER (5,0);

COMMENT ON COLUMN MCFG.MCFG_READ_TW_INTERVAL IS 'Interval of uploading files by filereader';

COMMENT ON COLUMN MMOD.MMOD_USEFTP IS 'Indicates use of FTP within FileReader';

COMMENT ON COLUMN MMOD.MMOD_FTP_SERVER IS 'Server address of FTP server';

COMMENT ON COLUMN MMOD.MMOD_FTP_USER IS 'User name of FTP account';

COMMENT ON COLUMN MMOD.MMOD_FTP_PWD IS 'Password of FTP account';

COMMENT ON COLUMN MMOD.MMOD_FTP_PORT IS 'Port of FTP default is 21';

ALTER TABLE MAVL ADD MAVL_CUR_ID NUMBER (30,0);

COMMENT ON COLUMN MAVL.MAVL_CUR_ID IS 'Currency id for of rate code';

ALTER TABLE XCOU ADD XCOU_PSCANSTRING VARCHAR2(60);

COMMENT ON COLUMN XCOU.XCOU_PSCANSTRING IS 'String to store the passport scanner''s equivalent for this country';

ALTER TABLE XREG ADD XREG_PSCANSTRING VARCHAR2(60);

COMMENT ON COLUMN XREG.XREG_PSCANSTRING IS 'String to store the passport scanner''s equivalent for this region';

ALTER TABLE XSTA ADD (XSTA_PSCANSTRING VARCHAR2(60), XSTA_PSCANDEFAULT NUMBER(1));

COMMENT ON COLUMN XSTA.XSTA_PSCANSTRING IS 'String to store the passport scanner''s equivalent for this state';

COMMENT ON COLUMN XSTA.XSTA_PSCANDEFAULT IS '1 = if this state should be used as default for fast scanning';

COMMENT ON COLUMN XPDG.XPDG_ROLE IS ' Passports = 1, id card=2, drivers licence=3    .. to be continued  ';

ALTER TABLE WPCA ADD (WPCA_PASSSCANNER NUMBER(3));

COMMENT ON COLUMN WPCA.WPCA_PASSSCANNER IS '0 - no pass scanner, 1 - Fuji fi-60F'; 

ALTER TABLE YRGP ADD YRGP_LIMIT NUMBER(30,4);

COMMENT ON COLUMN YRGP.YRGP_LIMIT IS 'Limit value for allowance packages';

ALTER TABLE YRGP ADD YRGP_FLIMIT NUMBER(30,4);

COMMENT ON COLUMN YRGP.YRGP_FLIMIT IS 'Foreign currency limit value for allowance packages';

CREATE TABLE XPMS (
   XPMS_ID NUMBER(30) NOT NULL
  ,XPMS_NAME VARCHAR2(60) NOT NULL
  ,XPMS_XPMR_SET NUMBER(30) NOT NULL 
  ,CONSTRAINT XPMS_PK PRIMARY KEY (XPMS_ID) USING INDEX TABLESPACE V8INDEX) TABLESPACE V8CONF;

CREATE UNIQUE INDEX XPMS_NAME ON XPMS (XPMS_NAME) TABLESPACE V8INDEX;

ALTER TABLE XPMS ADD CONSTRAINT XPMS_NAME UNIQUE (XPMS_NAME);

CREATE SEQUENCE SEQ_XPMS INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

CREATE SEQUENCE SEQ_XPMRSET INCREMENT BY 1 START WITH 1000 NOMAXVALUE NOCYCLE NOCACHE NOORDER;

ALTER TABLE XPMR MODIFY XPMR_SET NUMBER(30);

BEGIN
DECLARE 
   CURSOR MAPPERLST IS SELECT MMOD_ID, MMOD_VENDOR_ID FROM MMOD WHERE MMOD_VENDOR_ID = 'ORS' OR MMOD_VENDOR_ID = 'EXPED' ORDER BY MMOD_ID;
   CURSOR RATECODELST IS SELECT YRCH_ID, YRCH_WEBENABLED, YRCH_ZCUR_ID FROM YRCH WHERE YRCH_WEBENABLED = 1;

   FILL MAPPERLST%ROWTYPE;
   FILL1 RATECODELST%ROWTYPE; 
   BEGIN
       FOR FILL IN MAPPERLST LOOP
          FOR FILL1 IN RATECODELST LOOP
            INSERT INTO MAVL (MAVL.MAVL_ID     , MAVL.MAVL_MMOD_ID , MAVL.MAVL_TYPE , MAVL.MAVL_CODE_ID , MAVL.MAVL_INACTIVE , MAVL.MAVL_CUR_ID) 
            VALUES           (SEQ_MAVL.NEXTVAL , FILL.MMOD_ID      , 'RC'           , FILL1.YRCH_ID     , 0                  , FILL1.YRCH_ZCUR_ID);
          END LOOP;
       END LOOP;
    
   END;
END;
/

