pgethc1.sql

基于SQL/PLUS的脚本

----*************************************************************************
----*****版 本 ：1.0.0
----*****功 能 ：寻找所有的焊材材汇表。
----*****编制者：黄刚
----*****时 间 ：2005-03-04
----***************************************************************************
create or replace PROCEDURE cappdb2.CAPPDB2_pGetHC       
as
	v_UnitContentID  CAPPDB2.CUnitsInCAPPFile.ID%type;
	v_fileName 	 cappdb2.ccappfiles.name%type;
	v_createtime     cappdb2.ccappfiles.createtime%type;
	v_modifytime 	 cappdb2.ccappfiles.modifytime%type;
	v_createusername 	 cappdb2.ccappfiles.createusername%type;
	v_fileId  	 CAPPDB2.CUnitsInCAPPFile.CAPPFileID%type; 	
	v_modifytimetemp cappdb2.ccappfiles.modifytime%type:=sysdate - 10;
	m_count  	 integer:=0;
    cursor csr is SELECT CAPPFileID,ID from CAPPDB2.CUnitsInCAPPFile
	where UNITTYPEID =846 order by cappfileid asc;  
		------焊材846
    cursor content_refcur(vCAPPFILEID in number) is SELECT name,createtime,modifytime,createusername
	from cappdb2.ccappfiles where id = vCAPPFileID and name not like '%扩%' and name not like '%删%'
                                and name not like '%旧%' and name like '%HC%'
                                and id not in (select cappfileid from cappdb2.thcapp 
				where productid in (select childnodeid from cappdb2.nodechildrenlist 
				where nodeid = 100101628203))			-------借用工艺区ID为100101628203。
                                and id in (select cappfileid from Cappdb2.thcapp);
			--------and trunc(modifytime) >= trunc(v_modifytimetemp);
    
    
    mCAPPFileID CAPPDB2.CUnitsInCAPPFile.CAPPFileID%TYPE;    
    mUnitContentID CAPPDB2.CUnitsInCAPPFile.ID%type;
	
    mFileName cappdb2.ccappfiles.name%type;    
    mCreatetime cappdb2.ccappfiles.CREATETIME%type;
    mModifytime cappdb2.ccappfiles.MODIFYTIME%type;
    mCreateUserName cappdb2.ccappfiles.createusername%type;
    mField2      cappdb2.NodeProductPropertyList.field2%type;

BEGIN
    delete from CAPPDB2.t_HCItable;
    delete from CAPPDB2.t_HCMTable;
    delete from cappdb2.t_mtablename;
    commit;

    OPEN csr;
    FETCH csr INTO mCAPPFileID,mUnitContentID; 
   
    WHILE csr%FOUND LOOP
	
         OPEN content_refcur(mCAPPFileID);

      	 FETCH content_refcur INTO mFileName,mCreatetime,mModifytime,mCreateUserName;      
			
	 while content_refcur%found loop

		v_fileName:=mFileName;
		v_fileID:=mCAPPFileID;
		v_UnitContentID:=mUnitContentID;
		v_createtime:=trunc(mCreateTime);
		v_modifytime:=trunc(mModifytime);
		v_createusername:=mCreateUserName;

		insert into CAPPDB2.t_HCItable			                (CAPPFileID,CAPPFileName,UnitContentID,FileVersion,FileCreatetime,FileModify)
		values(v_fileID,v_fileName,v_UnitContentID,1,v_createtime,v_modifytime);

		COMMIT;

		select count(*) into m_count from CAPPDB2.t_MTableName
		WHERE substr(trim(CAPPDB2.t_MTableName.CAPPFileName),1,instr(trim(CAPPDB2.t_MTableName.CAPPFileName),'HC',1))
		= substr(trim(v_fileName),1,instr(trim(v_fileName),'HC',1));
		
		if (m_count > 0) then
    			update CAPPDB2.t_MTableName 
        	set FileVersion = FileVersion + 1                     
		WHERE substr(trim(CAPPDB2.t_MTableName.CAPPFileName),1,instr(trim(CAPPDB2.t_MTableName.CAPPFileName),'HC',1))
			= substr(trim(v_fileName),1,instr(trim(v_fileName),'HC',1));	
		else
   			insert into CAPPDB2.t_MTableName(CAPPFileID,CAPPFileName,FileVersion,CreateUserName)
			values(v_fileID,v_fileName,1,mCreateUserName);
		end if;
		commit;
		-----------------------------------------------------------------------------------------------------------
		-----------------------------------------------------------------------------------------------------------
		-------****控制产品名称如：5XX981.
		m_count := 0;
		select count(field2) into m_count from cappdb2.nodeproductpropertylist
		       where nodeid in (select productid from cappdb2.thcapp 
                                        where cappfileid = mCAPPFileID);
		if (m_count > 0) then
		   select field2 into mField2 from cappdb2.nodeproductpropertylist
		       where nodeid in (select productid from cappdb2.thcapp 
                                        where cappfileid = mCAPPFileID);    			
		else
		   mField2 := 'AAAA';
   			
		end if;
		commit;	
	
		----------------------------------------------------------------------------------------------------------
		----------------------------------------------------------------------------------------------------------
		
		CAPPDB2_GetHC(v_UnitContentID,v_fileID,v_fileName,v_createtime,v_modifytime,mField2);
	 	FETCH content_refcur INTO mFileName,mCreatetime,mModifytime,mCreateUserName;    
		
         end loop;

        CLOSE content_refcur; 

	FETCH csr INTO mCAPPFileID,mUnitContentID;   
 
	
    END LOOP;
    CLOSE csr;
--------------********更新版本纪录t_Itable,t_MTable
	update CAPPDB2.t_HCItable 
	set FileVersion = (Select MIN(CAPPDB2.t_MTableName.FileVersion)
	                      from CAPPDB2.t_MTableName where CAPPDB2.t_HCItable.CAPPFileID 
				   =  CAPPDB2.t_MTableName.CAPPFileID);
	
	update CAPPDB2.t_HCMTable 
	set M_Version = (Select MIN(CAPPDB2.t_MTableName.FileVersion)
	                    from CAPPDB2.t_MTableName where CAPPDB2.t_HCMTable.M_UnitID 
				   =  CAPPDB2.t_MTableName.CAPPFileID);
		
	commit;
END;
/