-- Changing common name to the name of the part according to 
-- the input argument.
-- Created for SPR 1155075.
-- Lijun Wang
-- 08/23/2005

-- Format
-- sqlplus system/manager@intralink @set_common_name_1155075.sql attribute_name attribute_type default_value overwite_existing
-- Where attribute_name is the name of the attribute kept in Intralink.
-- For example, pi_desc, name, piv_desc...
-- attribute_type is the type of the attribute. It must be one of the following:
--    'v: Versioned User-Defined Attribute
--     sv: Versioned System Attribute
--     nv: Non-Versioned User-Defined Attribute
--     snv: Non-Versioned System Attribute
--     l: Lifecycle Attribute
-- default_value is used when the attribute value is null.
-- overwite_existing cane be yes or no

SET SERVEROUTPUT ON SIZE 10000

set verif off
set recsep off

set termout off
col username new_v username noprint

select decode(upper(user), 'SYSTEM', 'PDM.',
                           'SYS',    'PDM.',
              NULL) username
from dual;

WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK

set termout on;

DEFINE attr_name = '&&1'

DEFINE attr_type = '&&2'

DEFINE default_value = '&&3'

DEFINE overwrite_existing ='&&4' 
set feedback off

DECLARE
v_claid		NUMBER;
v_tab		VARCHAR2(256);
v_col		VARCHAR2(256);
v_clname	VARCHAR2(256);

v_num		NUMBER;
v_sql		VARCHAR2(2048);
v_appclass  VARCHAR2(256);
v_class     VARCHAR2(256);
v_desc      VARCHAR2(256);
v_userdef   NUMBER;

v_argok		BOOLEAN;
v_selval    VARCHAR2(256);
v_defaultval VARCHAR2(4000);
v_wherecond VARCHAR2(256);
BEGIN

	v_argok := FALSE;
	
	IF('&&attr_type' = 'v') THEN
		v_appclass := 'PDMc_PIV';
		v_class := 'PDMc_PI';
		v_desc := 'Versioned  User Defined';
		v_userdef := 1;
		v_argok := TRUE;
	END IF;
	
	IF('&&attr_type' = 'nv') THEN
		v_class := 'PDMc_PI';
		v_appclass := 'PDMc_PI';
		v_desc := 'Non-Versioned User Defined';
		v_userdef := 1;
		v_argok := TRUE;
	END IF;

	IF('&&attr_type' = 'l') THEN
		v_class := 'PDMc_LCO';
		v_appclass := 'PDMc_LCOV';
		v_desc := 'Life Cycle User Defined';
		v_userdef := 1;
		v_argok := TRUE;
	END IF;

	IF('&&attr_type' = 'sv') THEN
		v_appclass := 'PDMc_PIV';
		v_class := 'PDMc_PIV';
		v_desc := 'System Versioned';
		v_userdef := 0;
		v_argok := TRUE;
	END IF;

	IF('&&attr_type' = 'snv') THEN
		v_appclass := 'PDMc_PI';
		v_class := 'PDMc_PI';
		v_desc := 'System Versioned';
		v_userdef := 0;
		v_argok := TRUE;
	END IF;

	IF(v_argok = FALSE) THEN
		dbms_output.put_line(chr(10));
		dbms_output.put_line('Wrong attribute type: ''&&attr_type''');
		dbms_output.put_line('Correct ones are one of the following:');
		dbms_output.put_line('v: Versioned User-Defined Attribute');
		dbms_output.put_line('sv: Versioned System Attribute');
		dbms_output.put_line('nv: Non-Versioned User-Defined Attribute');
		dbms_output.put_line('snv: Non-Versioned System Attribute');
		dbms_output.put_line('l: Lifecycle Attribute');
		dbms_output.put_line(chr(10));
		RETURN;
	END IF;
	
	IF(UPPER('&&overwrite_existing') != 'YES') THEN
		v_wherecond := ' where PICOMMONNAME IS NULL ';
	ELSE
		v_wherecond := ' ';
	END IF; 

	BEGIN
		SELECT	attr.claid, cl.clname, DBAATTRTABLE,  DBAATTRCOL, ClADefValue
			INTO v_claid, v_clname, v_tab, v_col, v_defaultval
		FROM &&username.pdm_classattr attr, &&username.pdm_dbattrdef dad,
			 &&username.pdm_classdef cl,  &&username.pdm_classdef c2
		WHERE
       			UPPER(attr.claname) = UPPER('&&attr_name') AND
				attr.claid = dad.claid and
        		attr.clid = cl.clid and
        		cl.clname = v_class and
        		attr.clatype = 5 AND
				attr.CLAAPPLCLID = c2.clid AND
				c2.clname = v_appclass and
				attr.CLAUSERDEF = v_userdef;

	EXCEPTION WHEN NO_DATA_FOUND THEN
		dbms_output.put_line(chr(10));
		dbms_output.put_line('Could not find the ' || v_desc || ' object attribute ''&&attr_name.''');
		dbms_output.put_line('Please make sure you specify the correct attribute name and type.');
		dbms_output.put_line(chr(10));
		RETURN;
	END;
	
	v_selval :=  'ut.' || v_col || ' ';
	
	IF(v_defaultval IS NOT NULL) THEN 
		v_selval := 'nvl(' || v_selval || ',''' || substr(v_defaultval, 2) ||''')';
	END IF;
	
	IF('&&default_value' IS NOT NULL) THEN
		v_selval := 'nvl(' || v_selval || ', ''&&default_value'')'; 
	END IF;
	v_selval := 'substr(' || v_selval || ', 1, 400)';
	
	IF('&&attr_type' = 'snv' OR '&&attr_type' = 'nv') THEN
		v_sql := 'UPDATE &&username.pdm_productitem pi' ||
		         ' set  PICOMMONNAME = (SELECT ' || v_selval  || 
				 ' FROM &&username.' || v_tab || ' ut ' ||
				 ' WHERE ut.piid = pi.piid) ' || v_wherecond;

		 --dbms_output.put_line(v_sql);
		EXECUTE IMMEDIATE v_sql;				 
	END IF;	

	IF('&&attr_type' = 'v' OR '&&attr_type' = 'sv') THEN
		-- set the common name to the latest PIV's version
		v_sql := 'UPDATE &&username.pdm_productitem pi' ||
		         ' set  PICOMMONNAME = (SELECT ' || v_selval  || 
				 ' FROM &&username.' || v_tab || ' ut, ' ||
				 ' &&username.pdm_productitemversion piv1, &&username.pdm_branch br ' ||
				 ' WHERE ut.pivid = piv1.pivid and piv1.brid = br.brid and  br.piid = pi.piid AND br.brpath=''main'' AND ' ||
				 ' NOT EXISTS (SELECT NULL FROM &&username.pdm_productitemversion piv2 ' ||
				 ' where piv2.brid = br.brid and piv2.pivid != piv1.pivid and piv2.PIVBRVER > piv1.PIVBRVER)) ' || v_wherecond;

/*
		dbms_output.put_line('UPDATE &&username.pdm_productitem pi');
		dbms_output.put_line(' set  PICOMMONNAME = (SELECT ' || v_selval);
		dbms_output.put_line(' FROM &&username.' || v_tab || ' ut, ');
		dbms_output.put_line(' &&username.pdm_productitemversion piv1, &&username.pdm_branch br ');
		dbms_output.put_line(' WHERE ut.pivid = piv1.pivid and piv1.brid = br.brid and  br.piid = pi.piid AND br.brpath=''main'' AND  ');
		dbms_output.put_line(' NOT EXISTS (SELECT NULL FROM &&username.pdm_productitemversion piv2 ');
		dbms_output.put_line(' where piv2.brid = br.brid and piv2.pivid != piv1.pivid and piv2.PIVBRVER > piv1.PIVBRVER)) ' || v_wherecond);
*/
		EXECUTE IMMEDIATE v_sql;
	END IF;

	IF('&&attr_type' = 'l') THEN
		v_sql := 'UPDATE &&username.pdm_productitem pi' ||
		         ' set  PICOMMONNAME = (SELECT ' || v_selval ||
				 ' FROM &&username.' || v_tab || ' ut, ' ||
				 ' &&username.pdm_productitemversion piv1, &&username.pdm_branch br, &&username.pdm_lco lco, &&username.pdm_lcov lcov ' ||
				 ' WHERE ut.lcovid = lcov.lcovid AND lcov.lcoid = lco.lcoid AND ' ||
				 ' lco.pivid = piv1.pivid and piv1.brid = br.brid and  br.piid = pi.piid AND ' ||
				 ' lcov.ISLATEST = 1 AND ' ||
				 ' br.brpath = ''main'' AND '  ||
				 ' NOT EXISTS (SELECT NULL FROM &&username.pdm_productitemversion piv2 ' ||
				 ' where piv2.brid = br.brid and piv2.pivid != piv1.pivid and piv2.PIVBRVER > piv1.PIVBRVER)) ' || v_wherecond;

/*
		dbms_output.put_line('UPDATE &&username.pdm_productitem pi');
		dbms_output.put_line(' set  PICOMMONNAME = (SELECT ' || v_selval);
		dbms_output.put_line(' FROM &&username.' || v_tab || ' ut, ');
		dbms_output.put_line(' &&username.pdm_productitemversion piv1, &&username.pdm_branch br, &&username.pdm_lco lco, &&username.pdm_lcov lcov ');
		dbms_output.put_line(' WHERE ut.lcovid = lcov.lcovid AND lcov.lcoid = lco.lcoid AND ');
		dbms_output.put_line(' lco.pivid = piv1.pivid and piv1.brid = br.brid and  br.piid = pi.piid AND ');
		dbms_output.put_line(' lcov.ISLATEST = 1 AND ');
		dbms_output.put_line(' br.brpath = ''main'' AND ');
		dbms_output.put_line(' NOT EXISTS (SELECT NULL FROM &&username.pdm_productitemversion piv2 ');
		dbms_output.put_line(' where piv2.brid = br.brid and piv2.pivid != piv1.pivid and piv2.PIVBRVER > piv1.PIVBRVER)) ' || v_wherecond);
*/		
		EXECUTE IMMEDIATE v_sql;
	END IF;
	
	dbms_output.put_line(chr(10));
	dbms_output.put_line('Common Name  Successfully Updated. Records Updated: ' || SQL%ROWCOUNT);
	dbms_output.put_line(chr(10));
	
EXCEPTION WHEN OTHERS THEN
	dbms_output.put_line(sqlerrm);	
END;
/

set feedback off	
commit;
exit
