CREATE OR REPLACE PROCEDURE TEST_PROJECTS   AS  
BEGIN
 INSERT INTO TONGUSER.PROJECTS(ID,TABLEIDOFGSCORGAN,CODE,GSCAPPROVEPROJECTID,NAME,ISPROMISE,AMOUNT,FLOWSTATUS,ISTURNOUT,GENERATEDTIME,
USERIDCREATOR,USERNAMECREATOR,ORGANIDCREATOR,ORGANNAMECREATOR,UPTIMESTAMP,STATUS,FINISHTIME,FINISHNOTICE,ISAGREED,USERIDFINISHER,
USERNAMEFINISHER,ORGANIDFINISHER,ORGANNAMEFINISHER) select  A1.ACC_CODE ID,C1.TABLEIDOFGSCORGAN TABLEIDOFGSCORGAN,A1.ACC_CODE CODE,
B1.ID GSCAPPROVEPROJECTID,B1.APPPROJECTNAME NAME,'0' ISPROMISE,'1' AMOUNT,
CASE A1.ACC_STATE  WHEN '办结' THEN  'finish' WHEN '审核中' THEN  'accept' ELSE  'apply' END FLOWSTATUS,
'0' ISTURNOUT,A1.ACC_TIME GENERATEDTIME,D1.TABLEIDOFGSCUSER USERIDCREATOR,D1.GSCUSERNAME USERNAMECREATOR,C1.TABLEIDOFGSCORGAN ORGANIDCREATOR,
C1.GSCORGANNAME ORGANNAMECREATOR,SYSDATE,'4' STATUS,A1.FINISHED_TIME FINISHTIME,A1.REMARK FINISHNOTICE,
CASE A1.ACC_STATE WHEN '办结' THEN '1' ELSE '-1' End ISAGREED,
D1.TABLEIDOFGSCUSER USERIDFINISHER,D1.GSCUSERNAME USERNAMEFINISHER,C1.TABLEIDOFGSCORGAN ORGANIDFINISHER,C1.GSCORGANNAME ORGANNAMEFINISHER
 from ITOUCH_FDA_ACC.ACC_BASIC A1
 LEFT JOIN TONGUSER.APPPROJECT B1 ON A1.ACC_TYPE_ID=B1.ACC_TYPE_ID
 LEFT JOIN TONGUSER.GSCORGAN C1 ON C1.TABLEIDOFGSCORGAN=B1.TABLEIDOFGSCORGAN
 LEFT JOIN TONGUSER.GSCUSER D1 ON C1.TABLEIDOFGSCORGAN=D1.TABLEIDOFGSCORGAN AND D1.GSCUSERNAME=A1.ACC_USER_NAME   
 LEFT JOIN TONGUSER.PROJECTS E1 ON A1.ACC_CODE=E1.ID
 WHERE B1.ID IS NOT NULL 
 AND E1.ID IS NULL;
END TEST_PROJECTS;