BST project import queries

Because the SQL queries used to pull billing codes from BST are fairly complex, we recommend creating views in your BST database to store the queries. Use the queries below on the BST server to create the custom views:

 

Projects View:

CREATE VIEW ArgosProjects AS
SELECT
t1.prj_code AS ProjectNumber,
t1.prj_name AS ProjectName
FROM prj_info AS t1
INNER JOIN prj_processing AS t2 ON t1.prj_code = t2.prj_code
WHERE t2.prj_status_ind = 'A'
GO

Phases View:

CREATE VIEW ArgosPhases AS
SELECT
t1.prj_code AS ParentProjectNumber,
t1.phase_code AS PhaseNumber,
t1.task_name AS PhaseName
FROM prj_task AS t1
WHERE
(t1.phase_code = '****'
AND t1.task_code = '****')
AND t1.prj_code in
(SELECT t1.prj_code
FROM prj_info AS t1
INNER JOIN prj_processing AS t2 ON t1.prj_code = t2.prj_code
WHERE t2.prj_status_ind = 'A')
GO

Tasks View:

CREATE VIEW ArgosTasks AS
SELECT
t1.prj_code AS ParentProjectNumber,
t1.phase_code AS ParentPhaseNumber,
t1.task_code AS TaskNumber,
t1.task_name AS TaskName
FROM prj_task AS t1
WHERE
t1.phase_code = '****'
AND t1.phase_code in (SELECT PhaseNumber from ArgosPhases)
AND t1.prj_code in (SELECT ParentProjectNumber from ArgosPhases)
GO 

 

Once custom views have been created in the BST database, use the queries below to complete the custom SQL import from BST:

Project level:

SELECT ProjectNumber, ProjectName FROM ArgosProjects

Phase level:

SELECT ParentProjectNumber, PhaseNumber, PhaseName FROM ArgosPhases

Task level:

SELECT ParentProjectNumber, ParentPhaseNumber, TaskNumber, TaskName FROM ArgosTasks

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.