select * from tanjo_data WHERE P_NO IS NOT NULL --AND IKEYUSE IS NULLselect * from pfs_userm where user_id = 1721select * from pfs_userm where ikeyvalue is not null--UPDATE TANJO_DATA SET IKEYUSE='1' WHERE P_NO IS NOT NULL AND IKEYUSE IS NULL--backupcreate table pfs_userm_0625 as select * from pfs_usermselect count(*) from pfs_userm_0625create table pfs_usergrouprel_0625 as select * from pfs_usergrouprelselect count(*) from pfs_usergrouprel_0625select * from pfs_userm where ikeypath is not nullselect * from pfs_usergroup where ap_id = 18261select * from am.userm@am where ikey is not null and YN_SYSUSER=0--update ikey/*update pfs_userm a set (useikey,ikeypath,ikeyfile1,ikeyfile2,ikeyvalue)=(select b.ikeyuse, b.dir,b.file1id,b.file2id,b.p_no from tanjo_data b where b.pwf_user_id=a.user_id) where exists (select 1 from tanjo_data b where b.pwf_user_id=a.user_id and b.P_NO IS NOT NULL);commit;*/--update pfs_usergrouprelselect * from select * from pfs_usergrouprel where usergroup_id in(1800044,1800045,1800046,1800047,1800048,1800155,1800157);--delete pfs_usergrouprel  where usergroup_id in(1800044,1800045,1800046,1800047,1800048,1800155,1800157);insert into pfs_usergrouprel(usergrouprel_id,usergroup_id,user_id,add_id,add_date) select round(dbms_random.value(1,1000)),1800044,a.pwf_user_id,181241,'20130625' from tanjo_data a,am.userm@am b where a.user_id= b.user_id and rtrim(a.user_id) in (SELECT rtrim(USER_ID) FROM am.MEMBERS@am WHERE RULE_ID IN ('107.001'));insert into pfs_usergrouprel(usergrouprel_id,usergroup_id,user_id,add_id,add_date) select round(dbms_random.value(10001,20000)),1800045,a.pwf_user_id,181241,'20130625' from tanjo_data a,am.userm@am b where a.user_id= b.user_id and a.user_id in (SELECT USER_ID FROM am.MEMBERS@am WHERE RULE_ID IN ('107.002'))and a.user_id not in(select distinct user_id from am.vendermfactuser@am WHERE operater_mk = 'Y' AND red_mk = 'N'AND user_id IN(SELECT user_id FROM tanjo_data)) ;insert into pfs_usergrouprel(usergrouprel_id,usergroup_id,user_id,add_id,add_date) select round(dbms_random.value(2001,3000)),1800046,a.pwf_user_id,181241,'20130625' from tanjo_data a,am.userm@am b where a.user_id= b.user_id and a.user_id in (SELECT USER_ID FROM am.MEMBERS@am WHERE RULE_ID IN ('107.004'));insert into pfs_usergrouprel(usergrouprel_id,usergroup_id,user_id,add_id,add_date) select round(dbms_random.value(3001,4000)),1800047,a.pwf_user_id,181241,'20130625' from tanjo_data a,am.userm@am b where a.user_id= b.user_id and a.user_id in (SELECT USER_ID FROM am.MEMBERS@am WHERE RULE_ID IN ('107.005'));insert into pfs_usergrouprel(usergrouprel_id,usergroup_id,user_id,add_id,add_date) select round(dbms_random.value(10001,2000)),1800048,a.pwf_user_id,181241,'20130625' from tanjo_data a,am.userm@am b where a.user_id= b.user_id and a.user_id in (SELECT USER_ID FROM am.MEMBERS@am WHERE RULE_ID IN ('107.009001'));insert into pfs_usergrouprel(usergrouprel_id,usergroup_id,user_id,add_id,add_date) select round(dbms_random.value(5001,6000)),1800155,a.pwf_user_id,181241,'20130625' from tanjo_data a,am.userm@am b where a.user_id= b.user_id and a.user_id in (SELECT USER_ID FROM am.MEMBERS@am WHERE RULE_ID IN ('107.009002'));insert into pfs_usergrouprel(usergrouprel_id,usergroup_id,user_id,add_id,add_date) select round(dbms_random.value(6001,7000)),1800157,a.pwf_user_id,181241,'20130625' from tanjo_data a,am.userm@am b where a.user_id= b.user_id and a.user_id in (SELECT USER_ID FROM am.MEMBERS@am WHERE RULE_ID IN ('107.009003'));insert into pfs_usergrouprel(usergrouprel_id,usergroup_id,user_id,add_id,add_date) select round(dbms_random.value(8001,9000)),1800156,a.pwf_user_id,181241,'20130625' from tanjo_data a,am.userm@am b where a.user_id= b.user_id and a.user_id in (SELECT USER_ID FROM am.MEMBERS@am WHERE RULE_ID IN ('107.009009'));insert into pfs_usergrouprel(usergrouprel_id,usergroup_id,user_id,add_id,add_date) select round(dbms_random.value(8001,9000)),1800164,aa.user_id,181241,'20130625' from (select distinct user_id from am.vendermfactuser@am WHERE operater_mk = 'Y' AND red_mk = 'N'AND user_id IN(SELECT user_id FROM tanjo_data)) aacommit;select distinct user_id from am.vendermfactuser@am WHERE operater_mk = 'Y' AND red_mk = 'N'AND user_id IN(SELECT user_id FROM tanjo_data)/*107.001          下單人員   1800044107.002          接單人員   1800045 107.004          採購方查詢人員  1800046 107.005          供應商查詢人員  1800047107.009001       出貨人員       1800048107.009002       財務人員       1800155      107.009003       扣款單簽核人員  1800157107.009009       生管人員        1800156     */SELECT COUNT(*) FROM USERMSELECT * FROM PROCESSINGSSELECT * FROM RULES WHERE RULE_PID LIKE '107%' ORDER BY RULE_ID--purCREATE TABLE USER_PUR AS SELECT USER_ID,LOGIN_NAME,USER_NAME,USER_ENAME,USER_SEX,USER_PWD,FACT_NO,USER_EMAIL,USER_TEL,P_NO,IKEY,DIR,FILE1ID,FILE2ID FROM USERM WHERE  CHK_MK='Y' AND (STOP_MK IS NULL OR STOP_MK!='Y') AND USER_ID IN (SELECT USER_ID FROM MEMBERS WHERE RULE_ID IN ('107.001','107.004'))select * from members where user_id in (71)--supCREATE TABLE USER_SUPPLY AS SELECT USER_ID,LOGIN_NAME,USER_NAME,USER_ENAME,USER_SEX,USER_PWD,FACT_NO,USER_EMAIL,USER_TEL,P_NO,IKEY,DIR,FILE1ID,FILE2ID  FROM USERM WHERE CHK_MK='Y' AND (STOP_MK IS NULL OR STOP_MK!='Y') AND USER_ID IN (SELECT USER_ID FROM MEMBERS WHERE RULE_ID IN ('107.002','107.005','107.009001','107.009002','107.009003','107.009009'))SELECT USER_ID,LOGIN_NAME,USER_NAME,USER_ENAME,USER_SEX,USER_PWD,FACT_NO,USER_EMAIL,USER_TEL,P_NO,IKEY,DIR,FILE1ID,FILE2ID  FROM USERM WHERE CHK_MK='Y' AND (STOP_MK IS NULL OR STOP_MK!='Y') AND USER_ID IN (SELECT USER_ID FROM MEMBERS WHERE RULE_ID IN ('107.002'))COMMITSELECT * FROM USER_SUPPLYSELECT * FROM USER_PUR