select pln.FULL_KEY "Plan Full Key",
       pln.DESCRIPTION "Description",
       cvcs.NAME "Repository Name",
       vcs.IS_GLOBAL "Linked Repository"
from  BUILD  pln
join PLAN_VCS_LOCATION  pvl on pln.BUILD_ID = pvl.PLAN_ID
JOIN VCS_LOCATION cvcs on pvl.VCS_LOCATION_ID = cvcs.VCS_LOCATION_ID
join VCS_LOCATION vcs   on  pvl.VCS_LOCATION_ID = cvcs.VCS_LOCATION_ID
where vcs.PARENT_ID IS NULL and pln.BUILD_TYPE ='CHAIN' and  vcs.XML_DEFINITION_DATA  like '%BAMSCRT%';

select VARIABLE_KEY, b.TITLE "PLAN TITLE", b.FULL_KEY, p.TITLE "PROJECT TITLE"
from VARIABLE_DEFINITION vd 
join BUILD b on b.BUILD_ID =vd.PLAN_ID 
join PROJECT p on p.PROJECT_ID = b.PROJECT_ID 
where VARIABLE_KEY  LIKE '%password%'
        OR VARIABLE_KEY LIKE '%passphrase%'
        OR VARIABLE_KEY LIKE '%secret%'
        OR VARIABLE_KEY LIKE '%sshkey%'; 

select p.TITLE "Project" ,b.TITLE,  b.FULL_KEY , b.PROJECT_ID,  *
from BUILD_DEFINITION bd 
join BUILD b on b.BUILD_ID = bd.BUILD_ID
join PROJECT p on p.PROJECT_ID = b.PROJECT_ID 

where 
bd.XML_DEFINITION_DATA like '%BAMSC%' 
 ;

select * from CREDENTIALS c 

SELECT dp.NAME  "Project_name",
       env.NAME " Deployment_Environment",
       vd.variable_key,
       vd.variable_value
FROM  deployment_environment env
       JOIN variable_definition vd
         ON env.environment_id = vd.environment_id
       JOIN deployment_project dp
         ON dp.deployment_project_id = env.package_definition_id
WHERE  vd.variable_key LIKE '%password%'
        OR variable_key LIKE '%passphrase%'
        OR variable_key LIKE '%secret%'
        OR variable_key LIKE '%sshkey%'; 

SELECT environment_id,
       name,
       description
FROM   deployment_environment
WHERE  xml_definition_data LIKE '%BAMSCRT%';