### Example of Loading CVX Input Tables

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/CVX/readme.md

Illustrates loading CVX input tables with specific dates and file names. This example shows sequential loading for different versions.

```sql
SELECT sources.load_input_tables('CVX', TO_DATE('20081201', 'yyyymmdd'), 'CVX Code Set '||TO_DATE('20081201', 'yyyymmdd'));
-- leave the web_cvx.xlsx and replace ValueSetConceptDetailResultSummary.xls with ValueSetConceptDetailResultSummary.xls from version 2
SELECT sources.load_input_tables('CVX', TO_DATE('20091015', 'yyyymmdd'), 'CVX Code Set '||TO_DATE('20091015', 'yyyymmdd'));
```

--------------------------------

### Install Admin Pack Dependencies

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/working/packages/admin_pack/readme.md

Before installing the admin_pack, ensure the pgcrypto and tablefunc extensions are created. Then, run the admin_pack_ddl.sql script following its internal instructions.

```sql
CREATE EXTENSION pgcrypto;
CREATE EXTENSION tablefunc;
--Run admin_pack_ddl.sql (follow the instructions inside)
```

--------------------------------

### Configure FastRecreateSchema with Options

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/working/packages/vocabulary_download/readme.md

Shows an example of calling the FastRecreateSchema function with specific options, such as including the concept ancestor table.

```sql
devv5.FastRecreateSchema(include_concept_ancestor=>true)
```

--------------------------------

### Create Virtual User and Grant Access Separately

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/working/packages/admin_pack/readme.md

Demonstrates creating a virtual user and then granting privileges and vocabulary access using separate SELECT statements. This approach allows for more granular control and is useful when user IDs are retrieved by login.

```sql
	SELECT admin_pack.CreateVirtualUser(
		pUserLogin       =>'dev_jdoe',
		pUserName        =>'John Doe',
		pUserDescription =>'Vocabulary Team',
		pPassWord        =>'password'
	);

	SELECT admin_pack.GrantPrivilege(
		pUserID          =>admin_pack.GetUserIDByLogin('dev_jdoe'),
		pPrivilegeID     =>admin_pack.GetPrivilegeIDByName('MANAGE_ANY_VOCABULARY')
	);

	SELECT admin_pack.GrantVocabularyAccess(
		pUserID          =>admin_pack.GetUserIDByLogin('dev_jdoe'),
		pVocabulary_id   =>'CPT4' --vocabulary_id
	);
```

--------------------------------

### Get Concept Mapping Changes

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/SNOMED Veterinary/readme.md

Identifies changes in concept mappings.

```sql
SELECT DISTINCT * FROM qa_tests.get_changes_concept_mapping();

```

--------------------------------

### Configure Vocabulary Parameters

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/working/packages/vocabulary_download/readme.md

Demonstrates how to set vocabulary-specific parameters, such as fast recreate scripts or load stage paths, using JSON format in the vocabulary_params column.

```sql
vocabulary_params='{"fast_recreate_script": "devv5.FastRecreateSchema()","load_stage_path": "https://github.com/OHDSI/Vocabulary-v5.0/raw/master/SNOMED/load_stage.sql"}'
```

--------------------------------

### Get Vocabulary Summary

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/SNOMED Veterinary/readme.md

Retrieves a summary of concepts from the specified schema.

```sql
SELECT DISTINCT * FROM qa_tests.get_summary('concept', 'devv5');

```

--------------------------------

### Get Relationship Summary

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/SNOMED Veterinary/readme.md

Retrieves a summary of concept relationships from the specified schema.

```sql
SELECT DISTINCT * FROM qa_tests.get_summary('concept_relationship', 'devv5');

```

--------------------------------

### Create relationship_to_concept_manual Table

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/GRR/manual_work/manual_work_readme.md

Run this SQL script once to create the necessary table for manual concept relationship mapping.

```sql
create table relationship_to_concept_manual
	(
		source_attr_name varchar(255),
		source_attr_concept_class varchar(50),
		target_concept_id integer,
		target_concept_code varchar(50),
		target_concept_name varchar(255),
		precedence integer,
		conversion_factor float,
		indicator_rxe varchar(10)
	);
```

--------------------------------

### Get Concept Summary

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/MedDRA/readme.md

Retrieves a summary of concepts for a specific vocabulary, filtering by vocabulary_id_1.

```sql
SELECT * FROM qa_tests.get_summary('concept')
WHERE vocabulary_id_1 = 'MedDRA';
```

--------------------------------

### Show All Set Archive Parameters

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/working/packages/sources_archive/readme.md

Displays all currently set archive parameters for the session. This is used to verify that parameters for all required sources are correctly set.

```SQL
SELECT * FROM sources_archive.ShowArchiveParams();
```

--------------------------------

### Get Newly Added Concepts

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/CVX/readme.md

Identifies and lists concepts that have been newly added to the vocabulary.

```sql
SELECT * FROM qa_tests.get_newly_concepts();
```

--------------------------------

### Get All NDC Data

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/working/packages/APIgrabber/readme.md

Initiates the process to retrieve all NDC data, likely for historical purposes.

```SQL
DO $$
BEGIN
	PERFORM apigrabber.GetAllNDC();
END $$;
```

--------------------------------

### Create Vocabulary Log Sequence and Table

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/working/packages/vocabulary_download/readme.md

Sets up a sequence for logging and a table to store detailed logs of vocabulary operations, including session IDs, timestamps, operation status, and errors.

```sql
CREATE SEQUENCE vocabulary_download.log_seq START WITH 1 INCREMENT BY 1 CACHE 10;
CREATE TABLE vocabulary_download.vocabulary_log (
  object_no int4 primary key,
  vocabulary_id varchar (20) not null,
  session_id int4 not null, /*session identifier during update*/
  operation_time timestamp not null,
  vocabulary_operation text not NULL /*started, stopped, etc*/,
  vocabulary_error text,
  error_details text,
  vocabulary_status int not null /*0 - update started, 1 - operation success, 2 - operation error, 3 - all tasks done*/
);
CREATE INDEX idx_log_sessionid ON vocabulary_download.vocabulary_log (session_id);
```

--------------------------------

### Full Load of Latest CIEL Version

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/CIEL/readme.md

Use this script for a full load of the latest CIEL version. Ensure you replace 'YOUR_TOKEN' with your actual token.

```sql
SELECT * FROM sources.load_ciel_all (
  p_token          := 'YOUR_TOKEN',
  p_source_version := NULL,
  p_clear          := true
  );
```

--------------------------------

### Get Summary Checks

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/EDI/readme.md

Retrieves distinct summary information for a specified table from the 'qa_tests' schema.

```sql
SELECT DISTINCT * FROM qa_tests.get_summary('concept');

```

```sql
SELECT DISTINCT * FROM qa_tests.get_summary('concept_relationship');
    
```

--------------------------------

### Recreate Schema with Synonyms and Deprecated Relations

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/universal_load_stage/readme.md

Run this SQL command to recreate the schema, including synonyms and deprecated relations. This is a prerequisite step before applying manual changes.

```sql
DO $$
BEGIN
	PERFORM devv5.FastRecreateSchema(include_synonyms=>true,include_deprecated_rels=>true,main_schema_name=>'devv5');
END $$;
```

--------------------------------

### Get Standard Concept Changes

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/CVX/readme.md

Tracks changes related to the standard concept status of vocabulary items.

```sql
SELECT * FROM qa_tests.get_standard_concept_changes();
```

--------------------------------

### Recreate Schema with RxNorm Options

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/RxNorm/readme.md

Use this script to recreate the schema with specific options for RxNorm updates. Ensure the main schema name and desired inclusions are correctly set.

```sql
SELECT devv5.FastRecreateSchema(main_schema_name=>'devv5', include_concept_ancestor=> true, include_deprecated_rels=> true, include_synonyms=> true);
   
```

--------------------------------

### Get Concept Summary

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/CVX/readme.md

Retrieves a summary of concepts, useful for understanding the scale and nature of the concept table.

```sql
SELECT * FROM qa_tests.get_summary('concept');
```

--------------------------------

### Get RxNorm to SPL Mappings

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/working/packages/APIgrabber/readme.md

Initiates the process to retrieve and parse RxNorm to SPL mappings from an external API.

```SQL
DO $$
BEGIN
	PERFORM apigrabber.GetRxNorm2SPL_Mappings();
END $$;
```

--------------------------------

### Create Virtual User with Privileges and Vocabulary Access

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/working/packages/admin_pack/readme.md

Creates a virtual user and then grants specific privileges and vocabulary access. This requires the MANAGE_USER privilege. Access can be time-bound and blocked.

```sql
DO $_$ 
DECLARE
iUserID INT4;
BEGIN
	SELECT admin_pack.CreateVirtualUser(
		pUserLogin       =>'dev_jdoe',
		pUserName        =>'John Doe',
		pUserDescription =>'Vocabulary Team',
		pPassWord        =>'password'
	) INTO iUserID;

	--grant MANAGE_SPECIFIC_VOCABULARY to dev_jdoe (can work only with specified vocabulary)
	PERFORM admin_pack.GrantPrivilege(
		pUserID          =>iUserID,
		pPrivilegeID     =>admin_pack.GetPrivilegeIDByName('MANAGE_SPECIFIC_VOCABULARY'),
		pValidStartDate  =>NULL, --access will be granted from the specified day, default CURRENT_DATE (can be omitted)
		pValidEndDate    =>NULL, --access will be granted until the specified expiration date, default 2099-12-31 (can be omitted)
		pIsBlocked       =>FALSE --you can create a blocked access, can be useful if you want to grant access in advance and then just unset the block flag via ModifyUserPrivilege(), can be omitted, default FALSE
	);

	--grant access only to CPT4 to dev_jdoe
	PERFORM admin_pack.GrantVocabularyAccess(
		pUserID          =>iUserID,
		pVocabulary_id   =>'CPT4',
		pValidStartDate  =>NULL, --access to the vocabulary will be granted from the specified day, default CURRENT_DATE (can be omitted)
		pValidEndDate    =>NULL, --access to the vocabulary will be granted until the specified expiration date, default 2099-12-31 (can be omitted)
		pIsBlocked       =>FALSE --you can create a blocked access, can be useful if you want to grant access in advance and then just unset the block flag via ModifyVocabularyAccess(), can be omitted, default FALSE
	);
END $_$;
```

--------------------------------

### Create Vocabulary Schema and Grant Permissions

Source: https://github.com/ohdsi/vocabulary-v5.0/blob/master/working/packages/vocabulary_download/readme.md

Creates the 'vocabulary_download' schema and grants necessary permissions for public access to tables and functions within the schema.

```sql
CREATE SCHEMA vocabulary_download AUTHORIZATION devv5;
ALTER DEFAULT PRIVILEGES IN SCHEMA vocabulary_download GRANT SELECT ON TABLES TO PUBLIC;
GRANT USAGE ON SCHEMA vocabulary_download TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA vocabulary_download GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
```