select * from fb_organizations where organization_name like '%CTEP%'

select * from fb_organization_types where org_id in (select id from fb_organizations where organization_name like '%CTEP%')

select * from fb_party_addresses where org_id in (select id from fb_organizations where organization_name like '%CTEP%')

select * from fb_party_contact_mechanisms where org_id in (select id from fb_organizations where organization_name like '%CTEP%')

select * from fb_reg_mains where frm_type = 'AR'

INSERT INTO FB_ORGANIZATIONS (
   ID, ORGANIZATION_ID, ORGANIZATION_NAME, 
   NATIONAL_PROVIDER_ID, STATUS, STATUS_DATE, 
   CREATED_USER, CREATED_DATE, LAST_CHANGE_USER, 
   LAST_CHANGE_DATE, UPDATE_STAMP, QC_FLAG) 
VALUES ( org_seq.nextval,'CTEPT', 'Cancer Therapy Evaluation Program Test (CTEPT)',
    NULL,'Active' ,sysdate , 'CreateCTEPOrg'
    , sysdate , NULL, NULL
    ,0 , NULL);

select 	org_seq.currval from dual;
	
INSERT INTO FB_ORGANIZATION_TYPES (
   ID, ORG_ID, OOR_TYPE, 
   REGISTRATION_DURATION_DAYS, PROTOCOL_REG_FLAG, STATUS, 
   STATUS_DATE, CREATED_DATE, CREATED_USER, 
   LAST_CHANGE_DATE, LAST_CHANGE_USER, UPDATE_STAMP, 
   CERTIFICATE_PREFERENCE) 
VALUES ( oor_seq.nextval, org_seq.currval, 'Sponsor'
	   ,null, null,'Active' 
	   , sysdate, sysdate, 'CreateCTEPOrg'
    ,null ,null ,0
    , 'BOTH');	
	
select 	oor_seq.currval from dual;		

select * from fb_addresses


select max(id) from fb_addresses where address_line1 like '%Executive%' order by id desc
	
INSERT INTO FB_PARTY_ADDRESSES (
   ID, ROLE_TYPE, START_DATE, 
   END_DATE, COMMENTS, ORG_ID, 
   PPA_ID, LAB_ID, IRB_ID, 
   ADD_ID, STATUS, STATUS_DATE, 
   CREATED_USER, CREATED_DATE, LAST_CHANGE_USER, 
   UPDATE_STAMP, LAST_CHANGE_DATE) 
VALUES (pad_seq.nextval ,'Primary' ,sysdate 
	,null, null, org_seq.currval
	, null, null, null 
	,(select max(id) from fb_addresses where address_line1 like '%Executive%') ,'Active', sysdate
	, 'CreateCTEPOrg', sysdate, null
	,0,null );

INSERT INTO FB_REG_MAINS ( ID, FRM_ID, OOR_ID, PRO_ID, DURATION_IN_DAYS, FRM_TYPE, STATUS,
STATUS_DATE, CREATED_DATE, CREATED_USER, LAST_CHANGE_DATE, LAST_CHANGE_USER, UPDATE_STAMP,
AR_SEC6_TEXT, AR_SEC7_TEXT, LAST_COMPLETED, SPONSOR_EMAIL, FDA_FLAG ) VALUES ( 
frm_seq.nextval, NULL, oor_seq.currval, NULL, 180, 'AR', 'Active',  TO_Date( '12/04/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
,  sysdate, 'CreateCTEPOrg',  null
, null, 26, 'AR_SEC6_TEXT', 'AR_SEC7_TEXT'
, NULL, 'powerles@mail.nih.gov', 'No'); 

select * from fb_persons where account_name like '%ctep%';

select * from csm_user_group where user_id in (select id from fb_persons where account_name like '%ctep%');

select * from csm_protection_element where object_id like '%AnnualRegistration%';

-- must have a CSM_PROTECTION_ELEMENT record for the AnnualRegistration entry from within fb_reg_mains table

select application_id from csm_application where upper(application_name) = 'FIREBIRD';

INSERT INTO CSM_PROTECTION_ELEMENT (
   PROTECTION_ELEMENT_ID, PROTECTION_ELEMENT_NAME, PROTECTION_ELEMENT_DESCRIPTION, 
   OBJECT_ID, ATTRIBUTE, PROTECTION_ELEMENT_TYPE_ID, 
   APPLICATION_ID, UPDATE_DATE) 
VALUES (CSM_PROTECTIO_PROTECTION_E_SEQ.nextval,'org.crix.firebird.businessobject.AnnualRegistrationMain' ,null 
    ,concat('org.crix.firebird.businessobject.AnnualRegistrationMain_',frm_seq.currval) ,null,null
    ,(select application_id from csm_application where upper(application_name) = 'FIREBIRD'),sysdate );
	
	
select max(protection_group_id) from csm_protection_group where protection_group_name like 'Cancer Therapy Evaluation Program Test (CTEPT)_%'

-- Insert a record into the csm_pg_pe table for the csm_protection_group record that was created and the csm_protection_element that was added earlier	
INSERT INTO CSM_PG_PE (
   PG_PE_ID, PROTECTION_GROUP_ID, PROTECTION_ELEMENT_ID, 
   UPDATE_DATE) 
VALUES ( CSM_PG_PE_PG_PE_ID_SEQ.nextval, (select max(protection_group_id) from csm_protection_group where protection_group_name like 'Cancer Therapy Evaluation Program Test (CTEPT)_%'), CSM_PROTECTIO_PROTECTION_E_SEQ.currval, sysdate);			
			

commit;

	
