-- B1 DEPENDS: BEFORE:PT:PROCESS_START

CREATE PROCEDURE _TmSp_AliasUpdate_OUSR
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER

AS
	flag int;
BEGIN
	SELECT COUNT(*) INTO flag FROM "PUBLIC"."TABLES"
		WHERE "TABLE_NAME"='TEMP_OUSR';	
	IF not(:flag>0) THEN		
		CREATE GLOBAL TEMPORARY COLUMN TABLE TEMP_OUSR(INTERNAL_K smallint,
		PASSWORD0 nvarchar(254),PASSWORD1 nvarchar(8),PASSWORD2 nvarchar(8),
		_USER_ smallint,USER_CODE nvarchar(25),_U_NAME nvarchar(155),
		GROUPS smallint,PASSWORD4 nvarchar(254),ALLOWENCES NCLOB, 
		SUPERUSER nvarchar(1),DISCOUNT DECIMAL(21,6),PASSWORD3 nvarchar(8), 
        Info1File nvarchar(4), Info1Field smallint, Info2File nvarchar(4),
        Info2Field smallint,Info3File nvarchar(4), Info3Field smallint,
        Info4File nvarchar(4), Info4Field smallint,dType nvarchar(1), 
        E_Mail nvarchar(100), PortNum nvarchar(50), OutOfOffic nvarchar(1), 
        SendEMail nvarchar(1),SendSMS nvarchar(1),
        CASHLIMIT nvarchar(1),DFLTSGROUP nvarchar(8),
		MAXCASHSUM DECIMAL(21,6),BRANCH smallint,DEPARTMENT smallint,
		FAX nvarchar(50),SENDFAX nvarchar(1));	
     END IF;
     
     INSERT INTO OUSR("INTERNAL_K", "PASSWORD", "PASSWORD1", "PASSWORD2", "USERID", 
            "USER_CODE", "U_NAME", "GROUPS", "PASSWORD4", 
            "ALLOWENCES", "SUPERUSER", "DISCOUNT", "PASSWORD3", 
            "Info1File", "Info1Field", "Info2File", "Info2Field",
            "Info3File", "Info3Field", "Info4File", "Info4Field",
            "dType", "E_Mail", "PortNum", "OutOfOffic", "SendEMail", 
            "SendSMS")
	  SELECT INTERNAL_K, PASSWORD0, PASSWORD1, PASSWORD2, _USER_, 
        USER_CODE, _U_NAME, GROUPS, PASSWORD4, 
        ALLOWENCES, SUPERUSER, DISCOUNT, PASSWORD3, 
        Info1File, Info1Field, Info2File, Info2Field,
        Info3File, Info3Field, Info4File, Info4Field,
        dType, E_Mail, PortNum, OutOfOffic, SendEMail, 
        SendSMS FROM TEMP_OUSR;
     
      SELECT COUNT(*) INTO flag FROM "PUBLIC"."TABLE_COLUMNS"
      	INNER JOIN "PUBLIC"."TABLES" ON "PUBLIC"."TABLE_COLUMNS"."TABLE_OID"="PUBLIC"."TABLES"."TABLE_OID"
      	WHERE "PUBLIC"."TABLES"."TABLE_NAME"='TEMP_OUSR' AND "PUBLIC"."TABLE_COLUMNS"."COLUMN_NAME"='CASHLIMIT';
      IF :flag>0 THEN
      	EXEC 'UPDATE OUSR SET "CashLimit"=(SELECT MAX(CashLimit) FROM TEMP_OUSR'||
      		' WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")'||
      		' WHERE EXISTS(SELECT COUNT(*) FROM TEMP_OUSR WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")';
      END IF;
      
      SELECT COUNT(*) INTO flag FROM "PUBLIC"."TABLE_COLUMNS"
      	INNER JOIN "PUBLIC"."TABLES" ON "PUBLIC"."TABLE_COLUMNS"."TABLE_OID"="PUBLIC"."TABLES"."TABLE_OID"
      	WHERE "PUBLIC"."TABLES"."TABLE_NAME"='TEMP_OUSR' AND "PUBLIC"."TABLE_COLUMNS"."COLUMN_NAME"='DFLTSGROUP';
      IF :flag>0 THEN
      	EXEC 'UPDATE OUSR SET "DfltsGroup"=(SELECT MAX(DfltsGroup) FROM TEMP_OUSR'||
      		' WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")'||
      		' WHERE EXISTS(SELECT COUNT(*) FROM TEMP_OUSR WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")';
      END IF;
      
      SELECT COUNT(*) INTO flag FROM "PUBLIC"."TABLE_COLUMNS"
      	INNER JOIN "PUBLIC"."TABLES" ON "PUBLIC"."TABLE_COLUMNS"."TABLE_OID"="PUBLIC"."TABLES"."TABLE_OID"
      	WHERE "PUBLIC"."TABLES"."TABLE_NAME"='TEMP_OUSR' AND "PUBLIC"."TABLE_COLUMNS"."COLUMN_NAME"='MAXCASHSUM';
      IF :flag>0 THEN
      	EXEC 'UPDATE OUSR SET "MaxCashSum"=(SELECT MAX(MaxCashSum) FROM TEMP_OUSR'||
      		' WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")'||
      		' WHERE EXISTS(SELECT COUNT(*) FROM TEMP_OUSR WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")';
      END IF;
      
      SELECT COUNT(*) INTO flag FROM "PUBLIC"."TABLE_COLUMNS"
      	INNER JOIN "PUBLIC"."TABLES" ON "PUBLIC"."TABLE_COLUMNS"."TABLE_OID"="PUBLIC"."TABLES"."TABLE_OID"
      	WHERE "PUBLIC"."TABLES"."TABLE_NAME"='TEMP_OUSR' AND "PUBLIC"."TABLE_COLUMNS"."COLUMN_NAME"='FAX';
      IF :flag>0 THEN
      	EXEC 'UPDATE OUSR SET "Fax"=(SELECT MAX(Fax) FROM TEMP_OUSR'||
      		' WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")'||
      		' WHERE EXISTS(SELECT COUNT(*) FROM TEMP_OUSR WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")';
      END IF;
      
      SELECT COUNT(*) INTO flag FROM "PUBLIC"."TABLE_COLUMNS"
      	INNER JOIN "PUBLIC"."TABLES" ON "PUBLIC"."TABLE_COLUMNS"."TABLE_OID"="PUBLIC"."TABLES"."TABLE_OID"
      	WHERE "PUBLIC"."TABLES"."TABLE_NAME"='TEMP_OUSR' AND "PUBLIC"."TABLE_COLUMNS"."COLUMN_NAME"='SENDFAX';
      IF :flag>0 THEN
      	EXEC 'UPDATE OUSR SET "SendFax"=(SELECT MAX(SendFax) FROM TEMP_OUSR'||
      		' WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")'||
      		' WHERE EXISTS(SELECT COUNT(*) FROM TEMP_OUSR WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")';
      END IF;
      
      /*SELECT COUNT(*) INTO flag FROM "PUBLIC"."TABLE_COLUMNS"
      	INNER JOIN "PUBLIC"."TABLES" ON "PUBLIC"."TABLE_COLUMNS"."TABLE_OID"="PUBLIC"."TABLES"."TABLE_OID"
      	WHERE "PUBLIC"."TABLES"."TABLE_NAME"='TEMP_OUSR' AND "PUBLIC"."TABLE_COLUMNS"."COLUMN_NAME"='ALLOWENCS1';
      IF :flag>0 THEN
      	UPDATE OUSR SET "ALLOWENCS1"=(SELECT MAX(ALLOWENCS1) FROM TEMP_OUSR
      		WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")
      		WHERE EXISTS(SELECT COUNT(*) FROM TEMP_OUSR WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K");
      END IF;
      */      
     SELECT COUNT(*) INTO flag FROM "PUBLIC"."TABLE_COLUMNS"
      	INNER JOIN "PUBLIC"."TABLES" ON "PUBLIC"."TABLE_COLUMNS"."TABLE_OID"="PUBLIC"."TABLES"."TABLE_OID"
      	WHERE "PUBLIC"."TABLES"."TABLE_NAME"='TEMP_OUSR' AND "PUBLIC"."TABLE_COLUMNS"."COLUMN_NAME"='DEPARTMENT';
      IF :flag>0 THEN
      	EXEC 'UPDATE OUSR SET "Department"=(SELECT MAX(Department) FROM TEMP_OUSR'||
      		' WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")'||
      		' WHERE EXISTS(SELECT COUNT(*) FROM TEMP_OUSR WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K"'||
      		' AND TEMP_OUSR.Department IS NOT NULL)';
      END IF;
     
      SELECT COUNT(*) INTO flag FROM "PUBLIC"."TABLE_COLUMNS"
      	INNER JOIN "PUBLIC"."TABLES" ON "PUBLIC"."TABLE_COLUMNS"."TABLE_OID"="PUBLIC"."TABLES"."TABLE_OID"
      	WHERE "PUBLIC"."TABLES"."TABLE_NAME"='TEMP_OUSR' AND "PUBLIC"."TABLE_COLUMNS"."COLUMN_NAME"='BRANCH';
      IF :flag>0 THEN
      	EXEC 'UPDATE OUSR SET "Branch"=(SELECT MAX(Branch) FROM TEMP_OUSR'||
      		' WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K")'||
      		' WHERE EXISTS(SELECT COUNT(*) FROM TEMP_OUSR WHERE TEMP_OUSR.INTERNAL_K=OUSR."INTERNAL_K"'||
  			' AND TEMP_OUSR.Branch IS NOT NULL)';
      END IF;
      DROP TABLE TEMP_OUSR;
END