/* 1. Create new table */

SELECT 1 FROM dbc.tables WHERE TableName = 't_pty_access_device_new' AND databasename = 'SANDBOX';

.IF ActivityCount = 1 THEN .GOTO Rename_new

CREATE MULTISET TABLE SANDBOX.t_pty_access_device_new ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      PTY_ID BIGINT NOT NULL Compress (653491713 ,428857857 ,428861954 ,428871939 ,428868868 ,653540613 ,428870406 ,428856070 ,428870663 ,428856071 ,428856072 ,428856074 ,428867338 ,428865035 ,428871692 ,428859916 ,428863758 ,428870415 ,428856592 ,428859920 ,428871697 ,428856341 ,428871702 ,428856342 ,428856343 ,428856344 ,428856345 ,428856346 ,428856347 ,428857883 ,428864796 ,653494813 ,428859423 ,653537313 ,653710113 ,428870946 ,428871458 ,653477413 ,653496613 ,428868649 ,428856873 ,428856874 ,428856875 ,428856876 ,653460013 ,428871725 ,653549613 ,428856877 ,428856878 ,428866863 ,428862000 ,653668913 ,428862001 ,653487413 ,428856631 ,428867384 ,428871737 ,653444413 ,428856637 ,428859965 ,428856638 ,428871233 ,428856129 ,428856641 ,653710913 ,428871234 ,428856130 ,428856898 ,428871235 ,428856131 ,428872004 ,428856132 ,649284680 ,428872009 ,428861772 ,428869453 ,653697613 ,428856910 ,428863310 ,428870479 ,428856911 ,428863311 ,428856912 ,428866896 ,428856913 ,428871250 ,428856916 ,653462613 ,653577813 ,428862038 ,428856919 ,428859735 ,653479513 ,653581913 ,428864089 ,652946011 ,653496413 ,428866911 ,428856929 ,428867685 ,428871781 ,653536613 ,428871783 ,428858220 ,428862060 ,428856176 ,428872051 ,428859257 ,428856955 ,428856956 ,652349309 ,653535613 ,428871551 ,428862079 ,428871552 ,428862080 ,428871553 ,428862081 ,428859266 ,428871043 ,428862083 ,428855940 ,428871557 ,428855941 ,428855942 ,428871559 ,428855943 ,428856967 ,428855944 ,428866184 ,653592713 ,428855945 ,428866185 ,428867980 ,653577613 ,428861837 ,428856974 ,428868751 ,428856975 ,428871312 ,428869521 ,428856466 ,428856467 ,428856468 ,653483413 ,428856469 ,428867221 ,428856470 ,428856471 ,428867736 ,428871320 ,653577113 ,28827674011 ,428858017 ,428872098 ,652349093 ,428856997 ,428856998 ,428856999 ,428871848 ,428856744 ,428857000 ,428857001 ,428857769 ,428857003 ,653354413 ,428871855 ,653460913 ,28827673011 ,653490613 ,428871606 ,428871863 ,428871096 ,428857784 ,428871609 ,653577913 ,652349370 ,428869820 ,652349116 ,651711420 ,428859325 ,428866238 ,652946111 ,428856514 ,428856771 ,428856772 ,428856773 ,428856774 ,428856519 ,428856775 ,428864967 ,428856520 ,428856776 ,428857800 ,428856521 ,428856522 ,428867531 ,428856781 ,428857037 ,428863181 ,428870094 ,428857038 ,428870095 ,428857039 ,428864207 ,428870352 ,428857040 ,428859344 ,428857041 ,428858066 ,393925587 ,428858067 ,428857044 ,428861908 ,428871381 ,428857045 ,428861909 ,428857047 ,428857048 ,428857049 ,428857050 ,428867290 ,428868059 ,428857051 ,428858843 ,428857052 ,428870109 ,428857053 ,428857054 ,428857055 ,652348896 ,428857056 ,428857057 ,428859361 ,428857058 ,428867300 ,428856549 ,652349414 ,428864232 ,652946411 ,428857835 ,428856046 ,428856562 ,428858098 ,653505013 ,428858102 ,428861943 ,428856568 ,428856569 ,428863994 ,428856571 ,428871421 ,428863998 ,428870143 ,428861951 ),
      PTY_ACCESS_DEVICE_ROLE_ID BYTEINT NOT NULL Compress (0 ,1 ,2 ),
      PTY_ACCESS_DEVICE_START_DT DATE Format 'YYYY-MM-DD' NOT NULL Compress (DATE '2015-07-20',DATE '2015-07-21',DATE '2015-07-22',DATE '2015-07-23',DATE '2015-07-25',DATE '2015-07-27',DATE '2015-07-28',DATE '2015-07-29',DATE '2016-02-01',DATE '2016-02-02',DATE '2015-07-31',DATE '2016-02-03',DATE '2016-02-04',DATE '2016-02-05',DATE '2016-02-08',DATE '2016-02-09',DATE '2015-02-26',DATE '2016-02-10',DATE '2015-02-27',DATE '2016-02-11',DATE '2016-02-12',DATE '2015-10-01',DATE '2015-10-02',DATE '2015-10-05',DATE '2015-10-06',DATE '2015-10-07',DATE '2015-10-08',DATE '2015-10-09',DATE '2015-10-12',DATE '2015-10-13',DATE '2015-10-14',DATE '2015-10-15',DATE '2015-10-16',DATE '2015-05-05',DATE '2015-05-06',DATE '2015-05-07',DATE '2015-10-19',DATE '2015-05-08',DATE '2015-10-20',DATE '2015-05-09',DATE '2015-10-21',DATE '2015-10-22',DATE '2015-10-23',DATE '2015-05-12',DATE '2015-10-24',DATE '2015-05-13',DATE '2015-05-14',DATE '2015-10-26',DATE '2015-05-15',DATE '2015-10-27',DATE '2015-10-28',DATE '2015-10-29',DATE '2015-05-18',DATE '2015-10-30',DATE '2015-05-19',DATE '2015-05-20',DATE '2015-05-21',DATE '2015-05-22',DATE '2015-05-25',DATE '2015-05-26',DATE '2015-05-27',DATE '2015-05-28',DATE '2015-05-29',DATE '2015-05-30',DATE '2015-08-02',DATE '2015-08-03',DATE '2015-08-04',DATE '2015-08-05',DATE '2015-08-06',DATE '2015-08-10',DATE '2015-08-11',DATE '2015-08-12',DATE '2015-08-13',DATE '2015-03-02',DATE '2015-08-14',DATE '2015-03-03',DATE '2015-03-04',DATE '2015-03-05',DATE '2015-08-17',DATE '2015-03-06',DATE '2015-08-18',DATE '2015-08-19',DATE '2015-03-08',DATE '2015-08-20',DATE '1900-01-01',DATE '2015-08-21',DATE '2015-03-10',DATE '2015-03-11',DATE '2015-03-12',DATE '2015-08-24',DATE '2015-03-13',DATE '2015-08-25',DATE '2015-08-26',DATE '2015-08-27',DATE '2015-03-16',DATE '2015-08-28',DATE '2015-03-17',DATE '2015-08-29',DATE '2015-03-18',DATE '2015-03-19',DATE '2015-08-31',DATE '2015-03-23',DATE '2015-03-24',DATE '2015-03-25',DATE '2015-03-26',DATE '2015-03-27',DATE '2015-03-28',DATE '2015-03-30',DATE '2015-03-31',DATE '2015-11-02',DATE '2015-11-03',DATE '2015-11-05',DATE '2015-11-06',DATE '2015-11-07',DATE '2015-11-09',DATE '2015-11-10',DATE '2015-11-11',DATE '2015-11-12',DATE '2015-06-01',DATE '2015-11-13',DATE '2015-06-02',DATE '2015-06-03',DATE '2015-06-04',DATE '2015-11-16',DATE '2015-06-05',DATE '2015-11-17',DATE '2015-11-18',DATE '2015-06-07',DATE '2015-11-19',DATE '2015-06-08',DATE '2015-11-20',DATE '2015-06-09',DATE '2015-06-10',DATE '2015-11-22',DATE '2015-06-11',DATE '2015-11-23',DATE '2015-11-24',DATE '2015-11-25',DATE '2015-11-26',DATE '2015-06-15',DATE '2015-11-27',DATE '2015-06-16',DATE '2015-11-28',DATE '2015-06-17',DATE '2015-06-18',DATE '2015-11-30',DATE '2015-06-19',DATE '2015-06-22',DATE '2015-06-23',DATE '2015-06-24',DATE '2015-06-25',DATE '2015-06-26',DATE '2015-06-28',DATE '2015-06-29',DATE '2015-06-30',DATE '2016-01-11',DATE '2016-01-12',DATE '2016-01-13',DATE '2016-01-14',DATE '2016-01-15',DATE '2015-09-01',DATE '2015-09-02',DATE '2016-01-18',DATE '2015-09-03',DATE '2016-01-19',DATE '2015-09-04',DATE '2016-01-20',DATE '2016-01-21',DATE '2015-09-06',DATE '2016-01-22',DATE '2015-09-07',DATE '2015-09-08',DATE '2015-09-09',DATE '2016-01-25',DATE '2015-09-10',DATE '2016-01-26',DATE '2016-01-27',DATE '2011-12-31',DATE '2015-09-12',DATE '2016-01-28',DATE '2015-04-01',DATE '2015-09-13',DATE '2016-01-29',DATE '2015-04-02',DATE '2015-09-14',DATE '2015-04-03',DATE '2015-09-15',DATE '2015-09-16',DATE '2015-09-17',DATE '2015-04-06',DATE '2015-09-18',DATE '2015-04-07',DATE '2015-04-08',DATE '2015-04-09',DATE '2015-09-21',DATE '2015-04-10',DATE '2015-09-22',DATE '2015-04-11',DATE '2015-09-23',DATE '2015-09-24',DATE '2015-04-13',DATE '2015-09-25',DATE '2015-04-14',DATE '2015-04-15',DATE '2015-04-16',DATE '2015-09-28',DATE '2015-04-17',DATE '2015-09-29',DATE '2015-09-30',DATE '2015-04-20',DATE '2015-04-21',DATE '2015-04-22',DATE '2015-04-23',DATE '2015-04-24',DATE '2015-04-25',DATE '2015-04-26',DATE '2015-04-27',DATE '2015-04-28',DATE '2015-04-29',DATE '2015-04-30',DATE '2015-12-01',DATE '2015-12-02',DATE '2015-12-03',DATE '2015-12-04',DATE '2015-12-07',DATE '2015-12-08',DATE '2015-12-09',DATE '2015-12-10',DATE '2015-12-11',DATE '2015-07-01',DATE '2015-07-02',DATE '2015-12-14',DATE '2015-12-15',DATE '2015-12-16',DATE '2015-12-17',DATE '2015-07-06',DATE '2015-12-18',DATE '2015-07-07',DATE '2015-07-08',DATE '2015-07-09',DATE '2015-12-21',DATE '2015-07-10',DATE '2015-12-22',DATE '2015-12-23',DATE '2015-12-24',DATE '2015-07-13',DATE '2015-12-25',DATE '2015-07-14',DATE '2015-12-26',DATE '2015-07-15',DATE '2015-12-27',DATE '2015-07-16',DATE '2015-12-28',DATE '2015-07-17',DATE '2015-12-29'),
      PTY_ACCESS_DEVICE_END_DT DATE Format 'YYYY-MM-DD' Compress (DATE '2015-07-22',DATE '2015-07-26',DATE '2015-07-27',DATE '2015-07-28',DATE '2016-02-01',DATE '2015-07-30',DATE '2016-02-02',DATE '2016-02-03',DATE '2016-02-04',DATE '2016-02-07',DATE '2014-10-08',DATE '2016-02-08',DATE '2016-02-09',DATE '2015-02-26',DATE '2016-02-10',DATE '2016-02-11',DATE '2015-10-01',DATE '2015-10-02',DATE '2015-10-04',DATE '2015-10-05',DATE '2015-10-06',DATE '2015-10-07',DATE '2015-10-08',DATE '2015-10-11',DATE '2015-10-12',DATE '2015-10-13',DATE '2015-10-14',DATE '2015-10-15',DATE '2015-10-18',DATE '2015-10-19',DATE '2015-10-20',DATE '2015-10-21',DATE '2015-10-22',DATE '2015-10-23',DATE '2015-05-12',DATE '2015-05-13',DATE '2015-10-25',DATE '2015-05-14',DATE '2015-10-26',DATE '2015-05-15',DATE '2015-10-27',DATE '2015-10-28',DATE '2015-05-17',DATE '2015-10-29',DATE '2015-05-18',DATE '2015-05-19',DATE '2015-05-20',DATE '2015-05-21',DATE '2015-05-24',DATE '2015-05-25',DATE '2015-05-26',DATE '2015-05-27',DATE '2015-05-28',DATE '2015-05-31',DATE '2015-08-03',DATE '2015-08-09',DATE '2015-08-10',DATE '2015-08-16',DATE '2015-08-17',DATE '2015-08-23',DATE '2015-08-24',DATE '2015-08-25',DATE '2015-08-26',DATE '2015-08-27',DATE '2015-08-30',DATE '2015-08-31',DATE '2015-11-02',DATE '2015-11-03',DATE '2015-11-08',DATE '2015-11-09',DATE '2014-11-26',DATE '2015-11-10',DATE '2015-11-11',DATE '2015-11-12',DATE '2015-06-01',DATE '2015-11-13',DATE '2015-06-02',DATE '2015-06-03',DATE '2015-11-15',DATE '2015-06-04',DATE '2015-11-16',DATE '2015-11-17',DATE '2015-11-18',DATE '2015-06-07',DATE '2015-06-08',DATE '2015-11-20',DATE '2015-06-09',DATE '2015-11-21',DATE '2015-06-10',DATE '2015-11-22',DATE '2015-11-24',DATE '2015-11-25',DATE '2015-06-14',DATE '2015-11-26',DATE '2015-06-15',DATE '2015-11-27',DATE '2015-06-16',DATE '2015-11-29',DATE '2015-06-18',DATE '2015-11-30',DATE '2015-06-22',DATE '2015-06-23',DATE '2015-06-27',DATE '2015-06-28',DATE '2015-06-29',DATE '2016-01-10',DATE '2016-01-11',DATE '2015-01-28',DATE '2016-01-12',DATE '2016-01-13',DATE '2016-01-14',DATE '2016-01-15',DATE '2016-01-16',DATE '2015-09-01',DATE '2016-01-17',DATE '2015-09-02',DATE '2016-01-18',DATE '2016-01-19',DATE '2016-01-20',DATE '2016-01-21',DATE '2015-09-06',DATE '2015-09-08',DATE '2016-01-24',DATE '2016-01-25',DATE '2016-01-26',DATE '2011-12-30',DATE '2016-01-27',DATE '2016-01-28',DATE '2015-09-13',DATE '2016-01-29',DATE '2015-04-02',DATE '2015-09-14',DATE '2015-09-15',DATE '2016-01-31',DATE '2015-09-16',DATE '2015-04-05',DATE '2015-09-17',DATE '2015-04-06',DATE '2015-09-18',DATE '2015-04-07',DATE '2015-09-19',DATE '2015-04-08',DATE '2015-09-20',DATE '2015-04-09',DATE '2015-09-21',DATE '2015-09-23',DATE '2015-04-12',DATE '2015-09-24',DATE '2015-04-13',DATE '2015-04-14',DATE '2015-04-15',DATE '2015-09-27',DATE '2015-04-16',DATE '2015-09-28',DATE '2015-04-17',DATE '2015-09-29',DATE '2015-04-19',DATE '2015-04-20',DATE '2015-04-21',DATE '2015-04-22',DATE '2015-04-23',DATE '2014-12-10',DATE '2015-04-26',DATE '2014-12-11',DATE '2015-04-27',DATE '2015-04-28',DATE '2015-04-29',DATE '2015-12-01',DATE '2015-12-02',DATE '2015-12-03',DATE '2015-12-05',DATE '2015-12-06',DATE '2015-12-07',DATE '2015-12-08',DATE '2015-12-09',DATE '2015-12-10',DATE '2015-07-01',DATE '2015-12-13',DATE '2015-07-02',DATE '2015-12-14',DATE '2015-12-15',DATE '2015-07-05',DATE '2015-12-17',DATE '2015-07-08',DATE '2015-12-20',DATE '2015-12-21',DATE '2015-07-10',DATE '2015-12-22',DATE '2015-12-23',DATE '2015-12-24',DATE '2015-07-13',DATE '2015-12-26',DATE '2015-07-15',DATE '2015-12-27',DATE '2015-12-28',DATE '2015-07-19',DATE '9999-12-31'),
      ACCESS_DEVICE_ID BIGINT NOT NULL,
      DELETED_FLAG CHAR(1) CHARACTER SET Unicode NOT CaseSpecific NOT NULL DEFAULT 'N' Compress ('N','Y'),
      ACTION_CD CHAR(1) CHARACTER SET Unicode NOT CaseSpecific NOT NULL Compress ('F','I','U'),
      WORKFLOW_RUN_ID BIGINT Compress (3944448 ,3714050 ,3780610 ,4069635 ,3498245 ,3835141 ,3976453 ,4064262 ,3502086 ,4037382 ,3464454 ,4030215 ,-8784121 ,3898120 ,3766027 ,3937804 ,3446031 ,3662863 ,3749394 ,3495446 ,3724054 ,3807511 ,3684632 ,4017176 ,3904028 ,3447836 ,3531036 ,3735837 ,3817757 ,-2445539 ,3874847 ,3956000 ,3978020 ,4096037 ,4033573 ,3492902 ,3775782 ,3837223 ,4084520 ,3805225 ,3514410 ,4020524 ,3887407 ,4132912 ,3924785 ,3737650 ,-14016717 ,3622195 ,3761717 ,3856694 ,3607608 ,4009272 ,3558456 ,3528249 ,-14181830 ,3499837 ,3579709 ,-14800066 ,3647807 ,3782207 ,3634756 ,4039236 ,4028232 ,3652425 ,3697738 ,3858507 ,3511884 ,4109388 ,3484493 ,3665229 ,3584335 ,3840848 ,3445842 ,3733842 ,3975763 ,3832660 ,3532628 ,3722069 ,3961942 ,4067928 ,3839320 ,3595864 ,-14391973 ,3421020 ,-2672036 ,3447388 ,3594332 ,3812445 ,3706461 ,3777629 ,3543136 ,3553120 ,3616097 ,3916129 ,3516003 ,-12804763 ,-3872922 ,3682921 ,3753321 ,3561578 ,3849835 ,3854955 ,-17435029 ,-2667667 ,3540592 ,3446896 ,3992690 ,3654003 ,-5573772 ,3842678 ,3819639 ,3810168 ,3605114 ,4116862 ,4082815 ,3967103 ,3589759 ,-10158465 ,3926912 ,3533952 ,3675522 ,3893890 ,3745155 ,3459460 ,3789958 ,3751559 ,3928455 ,3954057 ,3710601 ,4078218 ,4118922 ,3914123 ,3628684 ,3991180 ,3445901 ,3952530 ,3864978 ,-1848173 ,3509396 ,3796629 ,-11303273 ,4071320 ,3525529 ,3965595 ,3712155 ,4111259 ,3763868 ,-7145828 ,3946397 ,3622816 ,4105893 ,3996581 ,4035494 ,3984294 ,3569064 ,3535016 ,3477417 ,3968937 ,3632555 ,3640235 ,3984813 ,3720367 ,-2669136 ,3912624 ,4079282 ,4005555 ,3627187 ,3784115 ,3798708 ,3896758 ,3597750 ,3874232 ,-2678856 ,3447481 ,-7839558 ,3562683 ,3792571 ,3948734 ,3950526 ,-11520321 ,4097727 ,3504320 ,3908288 ,3678147 ,-2660668 ,3625413 ,3579077 ,3469765 ,3918022 ,3481799 ,3930312 ,3729609 ,3638731 ,4107723 ,-2422836 ,3613900 ,3990732 ,-17280051 ,3903693 ,3446221 ,-2437939 ,4002254 ,3680462 ,4015311 ,3428303 ,4022479 ,3445967 ,3725776 ,3564754 ,3867092 ,3610324 ,3994582 ,4066263 ,3935960 ,4092632 ,3545561 ,3814106 ,3636954 ,-10326565 ,4004061 ,3584221 ,4031710 ,3922654 ,3747299 ,3882723 ,3885283 ,-10281499 ,3876069 ,3821797 ,3944678 ,3708647 ,3694056 ,3611882 ,3767532 ,3910381 ,4090606 ,4041199 ,4103922 ,3983858 ,3883507 ,-2679820 ,3647988 ,4007413 ,4018677 ,-2847754 ,3517942 ,3592185 ,3669497 ,3963898 ,3447291 ,3826429 ,3467005 ,3692030 ,3824638 ,3853055 ),
      SESSION_INST_ID INTEGER Compress (0 ,425 ),
      INPUT_FILE_ID BIGINT Compress (4969472 ,4969984 ,4973568 ,4969729 ,4973313 ,4968450 ,4968195 ,4970243 ,4972547 ,4972803 ,4967685 ,4971270 ,4970759 ,4972042 ,4972299 ,4969489 ,4970001 ,4969234 ,4969746 ,4973330 ,4968467 ,4968212 ,4970260 ,4972564 ,4972820 ,4967957 ,4967702 ,4659224 ,3354138 ,4972059 ,4971804 ,4972316 ,4123676 ,4969506 ,4970018 ,4969251 ,4969763 ,4968996 ,4129829 ,4968229 ,4972581 ,4972837 ,4967719 ,4972076 ,4971821 ,4972333 ,4971566 ,4971058 ,4969523 ,4973619 ,4969268 ,4129845 ,4969013 ,4970294 ,4972598 ,4972854 ,4967736 ,4970556 ,3615805 ,4972093 ,4902974 ,4971838 ,4972350 ,4971583 ,4971328 ,4970818 ,4969540 ,4973636 ,4969285 ,4973381 ,4968518 ,4968774 ,4969030 ,4970311 ,4971079 ,4972871 ,4967753 ,4970573 ,4972110 ,4971855 ,4971600 ,4971345 ,4970835 ,4972371 ,4970069 ,4973653 ,4969302 ,4973398 ,4968791 ,4969047 ,4973143 ,4968280 ,4968536 ,4970328 ,4971096 ,4971872 ,4971617 ,4970594 ,4971362 ,4970852 ,4970086 ,4973670 ,4969831 ,4973415 ,4968808 ,4969064 ,4973160 ,4968297 ,4968553 ,4970345 ,4971113 ,4972905 ,4967787 ,1013360 ,4972657 ,4971634 ,4970611 ,4971379 ,4972405 ,2136183 ,4969591 ,4970103 ,4973687 ,4969336 ,4969848 ,4973432 ,4968825 ,4970873 ,4973177 ,4968314 ,4968570 ,4970362 ,4971130 ,4972922 ,4968059 ,3880833 ,4972161 ,4972674 ,4970628 ,4971396 ,4972422 ,4969608 ,4970120 ,4969353 ,4969865 ,4973449 ,4968842 ,4970890 ,4973194 ,4968331 ,4968587 ,4970379 ,4971147 ,4967821 ,4972178 ,4971923 ,4972691 ,4972439 ,4969625 ,4969370 ,4969882 ,4969115 ,4973211 ,4968348 ,4968604 ,4968093 ,4967838 ,4972195 ,4971940 ,4972708 ,4971685 ,4972456 ,4970154 ,4969387 ,4969132 ,4968110 ,4967855 ,4970929 ,4972212 ,4523957 ,4972725 ,4971702 ,4971447 ,4972473 ,1761210 ,4969404 ,4969660 ,4973500 ,4968893 ,4969149 ,4968638 ,4970430 ,4970686 ,4968127 ,4967872 ,4967617 ,4971202 ,4970949 ,4972229 ,4971974 ,4971719 ,4971464 ,4973517 ,4969166 ,4973262 ,4968399 ,4968655 ,4968911 ,4970447 ,4970192 ,4970705 ,4967634 ,4309715 ,4971219 ,4971991 ,4971736 ,4971481 ,4970972 ,4969950 ,4973534 ,3706335 ,4969183 ,4969695 ,4973279 ,4968416 ,4968672 ,4968928 ,4970464 ,4970209 ,4970722 ,4971236 ,4972008 ,4971753 ,4971498 ,4970990 ,4969967 ,4973551 ,4969200 ,4969712 ,4973296 ,4968433 ,4968689 ,4968945 ,4968178 ,4970226 ,4972530 ,4972786 ,4967923 ,4971253 ,1760245 ,4970486 ,4970742 ,4972282 ,4971515 ,4971007 ),
      INFO_SYSTEM_ID SMALLINT Compress 7 )
PRIMARY INDEX ( ACCESS_DEVICE_ID );

.IF Errorcode <> 0 THEN .GOTO Err

/* 1.1. DDL FOR volatile tables */

CREATE MULTISET VOLATILE TABLE t_pty_access_device_step1
     (
      PTY_ID BIGINT NOT NULL ,
      PTY_ACCESS_DEVICE_ROLE_ID BYTEINT NOT NULL ,
      PTY_ACCESS_DEVICE_START_DT DATE Format 'YYYY-MM-DD' NOT NULL ,
      PTY_ACCESS_DEVICE_END_DT DATE Format 'YYYY-MM-DD' ,
      ACCESS_DEVICE_ID BIGINT NOT NULL,
      DELETED_FLAG CHAR(1) CHARACTER SET Unicode NOT CaseSpecific NOT NULL ,
      ACTION_CD CHAR(1) CHARACTER SET Unicode NOT CaseSpecific NOT NULL ,
      WORKFLOW_RUN_ID BIGINT ,
      SESSION_INST_ID INTEGER ,
      INPUT_FILE_ID BIGINT ,
      INFO_SYSTEM_ID SMALLINT
	  )
PRIMARY INDEX ( access_device_id )
ON COMMIT DELETE ROWS ;

.IF Errorcode <> 0 THEN .GOTO Err

CREATE MULTISET VOLATILE TABLE t_pty_access_device_step2
     (
      PTY_ID BIGINT NOT NULL ,
      PTY_ACCESS_DEVICE_ROLE_ID BYTEINT NOT NULL ,
      PTY_ACCESS_DEVICE_START_DT DATE Format 'YYYY-MM-DD' NOT NULL ,
      PTY_ACCESS_DEVICE_END_DT DATE Format 'YYYY-MM-DD' ,
      ACCESS_DEVICE_ID BIGINT NOT NULL,
      DELETED_FLAG CHAR(1) CHARACTER SET Unicode NOT CaseSpecific NOT NULL ,
      ACTION_CD CHAR(1) CHARACTER SET Unicode NOT CaseSpecific NOT NULL ,
      WORKFLOW_RUN_ID BIGINT ,
      SESSION_INST_ID INTEGER ,
      INPUT_FILE_ID BIGINT ,
      INFO_SYSTEM_ID SMALLINT
	  )
PRIMARY INDEX ( access_device_id )
ON COMMIT DELETE ROWS ;

.IF Errorcode <> 0 THEN .GOTO Err

BT; 

/* 2. Volatile table:
		- remove duplicates
		- remove rows where start_dt > end_dt 		*/

INSERT INTO t_pty_access_device_step1 (
	PTY_ID                        
	, PTY_ACCESS_DEVICE_ROLE_ID     
	, PTY_ACCESS_DEVICE_START_DT    
	, PTY_ACCESS_DEVICE_END_DT      
	, ACCESS_DEVICE_ID              
	, DELETED_FLAG                  
	, ACTION_CD                     
	, WORKFLOW_RUN_ID               
	, SESSION_INST_ID               
	, INPUT_FILE_ID                 
	, INFO_SYSTEM_ID                            
)
SELECT 
     PTY_ID                        
	, PTY_ACCESS_DEVICE_ROLE_ID     
	, PTY_ACCESS_DEVICE_START_DT    
	, PTY_ACCESS_DEVICE_END_DT      
	, ACCESS_DEVICE_ID              
	, DELETED_FLAG                  
	, ACTION_CD                     
	, WORKFLOW_RUN_ID               
	, SESSION_INST_ID               
	, INPUT_FILE_ID                 
	, INFO_SYSTEM_ID                
FROM SANDBOX.t_pty_access_device_old
WHERE PTY_ACCESS_DEVICE_START_DT < PTY_ACCESS_DEVICE_END_DT
	AND PTY_ACCESS_DEVICE_ROLE_ID <> 0
	AND (PTY_ACCESS_DEVICE_END_DT <> DATE '9999-12-31'
					OR (PTY_ACCESS_DEVICE_END_DT = DATE '9999-12-31' AND  access_device_id IN (
										SEL access_device_id FROM SANDBOX.t_pty_access_device_old
										MINUS 
										SEL SK_ACCESS_DEVICE FROM SANDBOX.C0160000010008_CARD)
								)
					)
QUALIFY Row_Number() Over (PARTITION BY ACCESS_DEVICE_ID, PTY_ACCESS_DEVICE_ROLE_ID, PTY_ID
																	, PTY_ACCESS_DEVICE_START_DT, deleted_flag, info_system_id         
																	ORDER BY input_file_id, workflow_run_id  DESC ) = 1 ;

.IF Errorcode <> 0 THEN .GOTO Err

/* 3.1. Restore history and last actual row from C-table - pty_access_device_role_id = 0 */

MERGE INTO t_pty_access_device_step1 t 
	USING 
	(
	      SELECT
	            c.LAST_MODIFIED_DT
	            , Coalesce(Max(LAST_MODIFIED_DT - INTERVAL '1' DAY) Over (PARTITION BY  SK_ACCESS_DEVICE
	           		ORDER      BY LAST_MODIFIED_DT, WORKFLOW_RUN_ID 
	            	ROWS BETWEEN 1 Following AND 1 Following), DATE '9999-12-31') AS LEAD_DT
				, c.SK_ACCESS_DEVICE		
				, c.SK_PARTY_CLIENT
				, c.SK_INPUT_FILE
				, c.WORKFLOW_RUN_ID
				, c.SK_INFO_SYSTEM
	      FROM
	            SANDBOX.C0160000010008_CARD c
			WHERE SK_ACCESS_DEVICE>0 AND SK_PARTY_CLIENT>0			
			QUALIFY Row_Number() Over (PARTITION BY SK_ACCESS_DEVICE, LAST_MODIFIED_DT																	
														ORDER BY CARD_OPEN_DATE_W4 DESC, LAST_MODIFIED_DT DESC, SK_INPUT_FILE ) = 1	/* access_device_id 29433005510 with 2 different cards (WAY4_CARD_ID) */						
	) AS c
		ON  t.access_device_id = c.SK_ACCESS_DEVICE
			AND t.PTY_ACCESS_DEVICE_START_DT = c.LAST_MODIFIED_DT
			AND t.PTY_ACCESS_DEVICE_ROLE_ID = 0
			AND t.PTY_ID = c.SK_PARTY_CLIENT
			AND t.DELETED_FLAG = 'N'
WHEN MATCHED THEN
	UPDATE SET PTY_ACCESS_DEVICE_END_DT = LEAD_DT
										, input_file_id = SK_INPUT_FILE
WHEN NOT MATCHED THEN 
		INSERT  (      PTY_ID                        
									, PTY_ACCESS_DEVICE_ROLE_ID     
									, PTY_ACCESS_DEVICE_START_DT    
									, PTY_ACCESS_DEVICE_END_DT      
									, ACCESS_DEVICE_ID              
									, DELETED_FLAG                  
									, ACTION_CD                     
									, WORKFLOW_RUN_ID               
									, SESSION_INST_ID               
									, INPUT_FILE_ID                 
									, INFO_SYSTEM_ID     )
		VALUES ( c.SK_PARTY_CLIENT, 
								0,
								c.LAST_MODIFIED_DT ,
								DATE '9999-12-31' ,
								c.SK_ACCESS_DEVICE ,
								'N' ,
								'U' ,
								c.WORKFLOW_RUN_ID ,
								0 ,
								c.SK_INPUT_FILE ,
								SK_INFO_SYSTEM
								);			

.IF Errorcode <> 0 THEN .GOTO Err

/* 3.2. Restore history and last actual row from C-table - pty_access_device_role_id = 1 */

MERGE INTO t_pty_access_device_step1 t 
	USING 
	(
	      SELECT
	            c.LAST_MODIFIED_DT
	            , Coalesce(Max(c.LAST_MODIFIED_DT - INTERVAL '1' DAY) Over (PARTITION BY  c.SK_ACCESS_DEVICE
	           		ORDER      BY c.LAST_MODIFIED_DT, c.WORKFLOW_RUN_ID 
	            	ROWS BETWEEN 1 Following AND 1 Following), DATE '9999-12-31') AS LEAD_DT
				, c.SK_ACCESS_DEVICE		
				, CASE WHEN c.SK_PARTY_VSP>0 THEN c.SK_PARTY_VSP WHEN  T.SK_PARTY_VSP>0 THEN T.SK_PARTY_VSP ELSE c.SK_PARTY_VSP END AS SK_PARTY
				, c.SK_INPUT_FILE
				, c.WORKFLOW_RUN_ID
				, c.SK_INFO_SYSTEM
			FROM
	            SANDBOX.C0160000010008_CARD c
					LEFT JOIN SANDBOX.C0160000010010_CONTRACT T ON ( c.SK_AGREEMENT=T.SK_AGREEMENT )
			WHERE SK_ACCESS_DEVICE>0
			QUALIFY Row_Number() Over (PARTITION BY c.SK_ACCESS_DEVICE, c.LAST_MODIFIED_DT																	
											ORDER BY c.CARD_OPEN_DATE_W4 DESC, c.LAST_MODIFIED_DT DESC, c.SK_INPUT_FILE ) = 1	/* access_device_id 29433005510 with 2 different cards (WAY4_CARD_ID) */		
	) AS c
		ON  t.access_device_id = c.SK_ACCESS_DEVICE
			AND t.PTY_ACCESS_DEVICE_START_DT = c.LAST_MODIFIED_DT
			AND t.PTY_ACCESS_DEVICE_ROLE_ID = 1
			AND t.PTY_ID = c.SK_PARTY
			AND t.DELETED_FLAG = 'N'
WHEN MATCHED THEN
	UPDATE SET PTY_ACCESS_DEVICE_END_DT = LEAD_DT
										, input_file_id = SK_INPUT_FILE
WHEN NOT MATCHED THEN 
		INSERT  (      PTY_ID                        
									, PTY_ACCESS_DEVICE_ROLE_ID     
									, PTY_ACCESS_DEVICE_START_DT    
									, PTY_ACCESS_DEVICE_END_DT      
									, ACCESS_DEVICE_ID              
									, DELETED_FLAG                  
									, ACTION_CD                     
									, WORKFLOW_RUN_ID               
									, SESSION_INST_ID               
									, INPUT_FILE_ID                 
									, INFO_SYSTEM_ID     )
		VALUES ( c.SK_PARTY, 
								1,
								c.LAST_MODIFIED_DT ,
								DATE '9999-12-31' ,
								c.SK_ACCESS_DEVICE ,
								'N' ,
								'U' ,
								c.WORKFLOW_RUN_ID ,
								0 ,
								c.SK_INPUT_FILE ,
								SK_INFO_SYSTEM
								);			

.IF Errorcode <> 0 THEN .GOTO Err

/* 3.3. Restore history and last actual row from C-table - pty_access_device_role_id = 2 */

MERGE INTO t_pty_access_device_step1 t 
	USING 
	(
	      SELECT
	            c.LAST_MODIFIED_DT
	            , Coalesce(Max(LAST_MODIFIED_DT - INTERVAL '1' DAY) Over (PARTITION BY  SK_ACCESS_DEVICE
	           		ORDER      BY LAST_MODIFIED_DT, WORKFLOW_RUN_ID 
	            	ROWS BETWEEN 1 Following AND 1 Following), DATE '9999-12-31') AS LEAD_DT
				, c.SK_ACCESS_DEVICE		
				, c.SK_PARTY_OSB
				, c.SK_INPUT_FILE
				, c.WORKFLOW_RUN_ID
				, c.SK_INFO_SYSTEM
			FROM
	            SANDBOX.C0160000010008_CARD c
			QUALIFY Row_Number() Over (PARTITION BY SK_ACCESS_DEVICE, LAST_MODIFIED_DT																	
														ORDER BY CARD_OPEN_DATE_W4 DESC, LAST_MODIFIED_DT DESC, SK_INPUT_FILE ) = 1	/* access_device_id 29433005510 with 2 different cards (WAY4_CARD_ID) */						
	) AS c
		ON  t.access_device_id = c.SK_ACCESS_DEVICE
			AND t.PTY_ACCESS_DEVICE_START_DT = c.LAST_MODIFIED_DT
			AND t.PTY_ACCESS_DEVICE_ROLE_ID = 2
			AND t.PTY_ID = c.SK_PARTY_OSB
			AND t.DELETED_FLAG = 'N'
WHEN MATCHED THEN
	UPDATE SET PTY_ACCESS_DEVICE_END_DT = LEAD_DT
										, input_file_id = SK_INPUT_FILE
WHEN NOT MATCHED THEN 
		INSERT  (      PTY_ID                        
									, PTY_ACCESS_DEVICE_ROLE_ID     
									, PTY_ACCESS_DEVICE_START_DT    
									, PTY_ACCESS_DEVICE_END_DT      
									, ACCESS_DEVICE_ID              
									, DELETED_FLAG                  
									, ACTION_CD                     
									, WORKFLOW_RUN_ID               
									, SESSION_INST_ID               
									, INPUT_FILE_ID                 
									, INFO_SYSTEM_ID     )
		VALUES ( c.SK_PARTY_OSB, 
								2,
								c.LAST_MODIFIED_DT ,
								DATE '9999-12-31' ,
								c.SK_ACCESS_DEVICE ,
								'N' ,
								'U' ,
								c.WORKFLOW_RUN_ID ,
								0 ,
								c.SK_INPUT_FILE ,
								SK_INFO_SYSTEM
								);						

.IF Errorcode <> 0 THEN .GOTO Err

/* 4. Restore history in T-table */

INSERT INTO t_pty_access_device_step2 (
	PTY_ID                        
	, PTY_ACCESS_DEVICE_ROLE_ID     
	, PTY_ACCESS_DEVICE_START_DT    
	, PTY_ACCESS_DEVICE_END_DT      
	, ACCESS_DEVICE_ID              
	, DELETED_FLAG                  
	, ACTION_CD                     
	, WORKFLOW_RUN_ID               
	, SESSION_INST_ID               
	, INPUT_FILE_ID                 
	, INFO_SYSTEM_ID                
)
SELECT 
			PTY_ID                        
			, PTY_ACCESS_DEVICE_ROLE_ID     
			, PTY_ACCESS_DEVICE_START_DT    
			, Coalesce(Min(PTY_ACCESS_DEVICE_START_DT)  Over  (PARTITION BY  access_device_id, PTY_ACCESS_DEVICE_ROLE_ID, DELETED_FLAG
					ORDER BY PTY_ACCESS_DEVICE_START_DT, PTY_ACCESS_DEVICE_END_DT, workflow_run_id 
					ROWS BETWEEN 1 Following AND 1 Following) - INTERVAL '1' DAY, DATE '9999-12-31') AS PTY_ACCESS_DEVICE_END_DT      
			, ACCESS_DEVICE_ID              
			, DELETED_FLAG                  
			, ACTION_CD                     
			, WORKFLOW_RUN_ID               
			, SESSION_INST_ID               
			, INPUT_FILE_ID                 
			, INFO_SYSTEM_ID 
FROM t_pty_access_device_step1 ;

.IF Errorcode <> 0 THEN .GOTO Err

/* 5. Insert cured data into new T-table */

INSERT INTO SANDBOX.t_pty_access_device_new (
	PTY_ID                        
	, PTY_ACCESS_DEVICE_ROLE_ID     
	, PTY_ACCESS_DEVICE_START_DT    
	, PTY_ACCESS_DEVICE_END_DT      
	, ACCESS_DEVICE_ID              
	, DELETED_FLAG                  
	, ACTION_CD                     
	, WORKFLOW_RUN_ID               
	, SESSION_INST_ID               
	, INPUT_FILE_ID                 
	, INFO_SYSTEM_ID      
)
SELECT 
	PTY_ID                        
	, PTY_ACCESS_DEVICE_ROLE_ID     
	, PTY_ACCESS_DEVICE_START_DT    
	, PTY_ACCESS_DEVICE_END_DT      
	, ACCESS_DEVICE_ID              
	, DELETED_FLAG                  
	, ACTION_CD                     
	, WORKFLOW_RUN_ID               
	, SESSION_INST_ID               
	, INPUT_FILE_ID                 
	, INFO_SYSTEM_ID              
FROM t_pty_access_device_step2 ;

.IF Errorcode <> 0 THEN .GOTO Err

ET ;

/* 8. Rename new table as main T-table */

.LABEL Rename_new

SELECT 1 FROM dbc.tables WHERE TableName = 't_pty_access_device' AND databasename = 'SANDBOX';

.IF ActivityCount = 1 THEN .GOTO End_

RENAME TABLE SANDBOX.t_pty_access_device_new AS SANDBOX.t_pty_access_device ;

.IF Errorcode <> 0 THEN .GOTO Err

/* 9. Collect stats */

COLLECT STATISTICS ON SANDBOX.t_pty_access_device FROM SANDBOX.t_pty_access_device_old ;

.IF Errorcode <> 0 THEN .GOTO Err

SELECT 'Table changed, all OK' status;  

.GOTO End_

.LABEL Err

SELECT 'Error on table change' status;

.LABEL End_

