SQL for Project Consolidation/Merge Check

========================================================================================
                  Project Details Quary :
========================================================================================
Step 1 : Identify No. of objects that are not present in the consolidated project
Step 2 : Object Count of all Project & consolidated Project
Step 3 : Project dependancy with each other

==========================================================================
Step 1 : Identify No. of objects that are not present in the consolidated project
==========================================================================
/*No. of objects that are not present in the consolidated project*/
SELECT   Count(objecttype) FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_CR_681_SR', 'UNBLR_TAM_CR681_PRINT')  -- List of Induvisual Project
 and OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  Not IN (
select Distinct OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  FullObject  FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_BATCHIV_2_23FEB2012'))  -- Consolidated Project
/*No. of objects (Object Typewise) that are not present in the consolidated project*/
SELECT  case objecttype
WHEN 0 THEN 'Record'
WHEN 2  THEN 'Field'
WHEN 5 THEN 'Page'
WHEN 30 THEN  'SQL'
WHEN 25 THEN  'Message Cateloge'
WHEN 44 THEN 'Page Peoplecode'
WHEN 46 THEN  'Component Peoplecode'
WHEN 4 THEN 'Translate Value'
WHEN 8 THEN 'Record Peoplecode'
WHEN 47 THEN 'Component Record peoplecode'
WHEN 48 THEN  'Component Record Field Peoplecode'
WHEN 58 THEN  'Application Package Peoplecode'
else
'Others :'|| objecttype
End objecttype,
 Count(objecttype) FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_CR_623','UNBLR_TAM_CR_631','UNBLR_TAM_CR635', 'UNBKK_CR638','UNBKK_CR639', 'UNBLR_TAM_CR_643','UNBLR_TAM_CR_644','UNBLR_TAM_CR649','UNBLR_TAM_CR650','UNBLR_TAM_CR_661'
 ,'UNBLR_TAM_CR_662','UNBLR_TAM_CR665','UNBLR_TAM_CR668','UNBLR_TAM_CR_669','UNBLR_TAM_CR671','UNBLR_TAM_CR_673','UNBLR_TAM_CR674'
 ,'UNBLR_TAM_CR_675','UNBLR_TAM_CR677','UNBLR_TAM_CR_680','UNBLR_TAM_CR_691','UNBLR_TAM_CR_640','UNBLR_TAM_CR_618','UNBLR_TAM_DEF_641','UNBLR_TAM_DEF_637') -- List of Induvisual Project
 and OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  Not IN (
select Distinct OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  FullObject  FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_BATCHIV_28NOV2011')) -- Consolidated Project
Group By  objecttype

/* List of objects that are not present in the consolidated project*/
select Distinct PROJECTNAME,
 case objecttype
WHEN 0 THEN 'Record'
WHEN 2  THEN 'Field'
WHEN 5 THEN 'Page'
WHEN 30 THEN  'SQL'
WHEN 25 THEN  'Message Cateloge'
WHEN 44 THEN 'Page Peoplecode'
WHEN 46 THEN  'Component Peoplecode'
WHEN 4 THEN 'Translate Value'
WHEN 8 THEN 'Record Peoplecode'
WHEN 47 THEN 'Component Record peoplecode'
WHEN 48 THEN  'Component Record Field Peoplecode'
WHEN 58 THEN  'Application Package Peoplecode'
else
'Others'|| objecttype
End objecttype,
OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4  FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_CR_623','UNBLR_TAM_CR_631','UNBLR_TAM_CR635', 'UNBKK_CR638','UNBKK_CR639', 'UNBLR_TAM_CR_643','UNBLR_TAM_CR_644','UNBLR_TAM_CR649','UNBLR_TAM_CR650','UNBLR_TAM_CR_661'
 ,'UNBLR_TAM_CR_662','UNBLR_TAM_CR665','UNBLR_TAM_CR668','UNBLR_TAM_CR_669','UNBLR_TAM_CR671','UNBLR_TAM_CR_673','UNBLR_TAM_CR674'
 ,'UNBLR_TAM_CR_675','UNBLR_TAM_CR677','UNBLR_TAM_CR_680','UNBLR_TAM_CR_691','UNBLR_TAM_CR_640','UNBLR_TAM_CR_618','UNBLR_TAM_DEF_641','UNBLR_TAM_DEF_637')  -- List of Induvisual Project
 and OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  Not IN (
select Distinct OBJECTVALUE1||'.'||OBJECTVALUE2||'.'||OBJECTVALUE3||'.'||OBJECTVALUE4  FullObject  FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_BATCHIV_28NOV2011')) -- Consolidated Project

==========================================================================
Step 2 : Object Count of all Project & consolidated Project
==========================================================================

/* Object Count of all Project & consolidated Project*/
--------------------------------------------------------
SELECT  case A.objecttype
WHEN 0 THEN 'Record'
WHEN 2  THEN 'Field'
WHEN 5 THEN 'Page'
WHEN 30 THEN  'SQL'
WHEN 25 THEN  'Message Cateloge'
WHEN 44 THEN 'Page Peoplecode'
WHEN 46 THEN  'Component Peoplecode'
WHEN 4 THEN 'Translate Value'
WHEN 8 THEN 'Record Peoplecode'
WHEN 47 THEN 'Component Record peoplecode'
WHEN 48 THEN  'Component Record Field Peoplecode'
WHEN 58 THEN  'Application Package Peoplecode'
else
'Others'
End objecttype, ProjectCount, ConsolidateProjectCount FROm
(SELECT   objecttype,
 Count(objecttype) ProjectCount   FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN  ('UNBLR_TAM_CR_629','UNBLR_TAM_CR_664_664B','UNBLR_TAM_DEF_671', 'UNBLR_TAM_DEF_673','UNBLR_TAM_DEF_674','UNBLR_TAM_DEF_675','UNBLR_TAM_DEF_678')  -- List of Induvisual Project
Group By  objecttype) A, (SELECT   objecttype,
 Count(objecttype) ConsolidateProjectCount FROM PSPROJECTITEM
 WHERE  PROJECTNAME IN ('UNBLR_TAM_BATCHIV_5_27MAR2012') -- Consolidated Project
Group By  objecttype) B
WHERE A.objecttype = B.objecttype(+)

==========================================================================
Step 3 : Project dependancy with each other
==========================================================================
/*Project dependancy with each other*/
-------------------------------------
SELECT  A.PROJECTNAME, B.PROJECTNAME ConflictingProject,  Count(A.objecttype) FROM PSPROJECTITEM A, PSPROJECTITEM B
 WHERE  A.PROJECTNAME IN ('UNBLR_TAM_CR_629','UNBLR_TAM_CR_664_664B','UNBLR_TAM_DEF_671', 'UNBLR_TAM_DEF_673','UNBLR_TAM_DEF_674','UNBLR_TAM_DEF_675','UNBLR_TAM_DEF_678')  -- List of Induvisual Project
  AND  B.PROJECTNAME IN  ('UNBLR_TAM_CR_629','UNBLR_TAM_CR_664_664B','UNBLR_TAM_DEF_671', 'UNBLR_TAM_DEF_673','UNBLR_TAM_DEF_674','UNBLR_TAM_DEF_675','UNBLR_TAM_DEF_678')  -- List of Induvisual Project
 and A.OBJECTVALUE1||'.'||A.OBJECTVALUE2||'.'||A.OBJECTVALUE3||'.'||A.OBJECTVALUE4  =  B.OBJECTVALUE1||'.'||B.OBJECTVALUE2||'.'||B.OBJECTVALUE3||'.'||B.OBJECTVALUE4
 and A.PROJECTNAME <> B.PROJECTNAME
 Group BY A.PROJECTNAME, B.PROJECTNAME