Advice for scripting

[afelya]

Hi everyone, I am a junior oracle dba and we are bring in our remote site Colorado into our environment in VA. They no longer have their own databases, they remotely connect to us. But also they are using another program for timesheets that is called unanet and this program has its own oracle db which still connect to us and exports time sheet data. We are in the middle of bring in this system here as well. I unfortunately don't have much knowledge about what unanet does yet.

Here is the catch. They used to use these scripts to export data from a finance application (COSTPOINT-deltek) which is basically a user interface to my 2 databases.

My boss is concerned about these scripts because allot of hard coding been done, such as employee names which some of the employees have retired. He thinks these are poorly written scripts. I am not a developer and good at writing scripts, however I do understand my boss's concern. Therefore I am turning for help to you guys. If any of you have the time could you pls take a look at these 2 scripts that exports data from 2 databases we have here. And add your comments and notes on the scripts. Scripts do have allot of comments and explanation but I don't feel comfortable doing any changes unless people like you direct me to it. I do not trust the person who wrote the scripts due to his position which he is being terminated. Now this is on me and I am turning for help to you guys. Please take a look at these scripts and let me know what I can do to improve these scripts so we don't depend on hard coded data. Thank you so very much already...


This is the UNA_SETS script: The est cost database

------------------------------------------------------------------------------------------------------
-- PACKAGE Unanet_export Created: January, 24 2002 by Tom Elliott
--
-- This package contains the procedures and functions used to extract data from
-- Costpoint into flat files formatted for importation into Unanet version 3.4
--
------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE UNANET_EXPORT IS

PROCEDURE export_all;
PROCEDURE unanet_empl_export;
PROCEDURE unanet_proj_export;
PROCEDURE unanet_assign_export;

END UNANET_EXPORT;
/
SHOW ERRORS
/

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

CREATE OR REPLACE PACKAGE BODY UNANET_EXPORT IS

------------------------------------------------------------------------------------------------------
--
-- PROCEDURE EXPORT_ALL
--
-- This procedure executes all the Unanet export procedures in this package.
--
------------------------------------------------------------------------------------------------------


PROCEDURE export_all IS

BEGIN

unanet_export.unanet_empl_export;
unanet_export.unanet_proj_export;
unanet_export.unanet_assign_export;

END;


------------------------------------------------------------------------------------------------------
--
-- PROCEDURE UNANET_EMPL_EXPORT
--
-- This procedure extracts the employee information from Costpoint and
-- creates a flat file called: empl_SETS.lst in the c:\una_in directory of the Costpoint
-- server. The file is written in the proper format to be imported into Unanet version 3.4
--
------------------------------------------------------------------------------------------------------


PROCEDURE unanet_empl_export IS

fileID UTL_FILE.FILE_TYPE; -- Specifications of output file.
this_line VARCHAR2(500); -- Text string written to project output file.
active_fl VARCHAR2(1); -- variable to hold derived active status of employee.
EOT VARCHAR2(8); -- Variable to store the effective date.
exempt_fl VARCHAR2(1); -- Variable to store the exempt status of the employee.
this_date DATE; -- Variable to check for most recent employee record.
user_role VARCHAR2(100); -- Variable to store user roles.
is_pm_fl INTEGER; -- Variable to test for PM role.
this_empl_org VARCHAR2(15); -- Variable to store company name.
vPassword VARCHAR2(15); -- Variable to store employee password.
vLocation VARCHAR2(15); -- Variable to store employee location.
vtoday VARCHAR2(8); -- Variable to store current date.


CURSOR empl_list IS
SELECT distinct a.empl_id,
a.term_dt,
a.first_name,
a.last_name,
a.pref_name,
a.name_sfx_cd,
a.email_id,
a.s_empl_status_cd,
a.orig_hire_dt,
b.org_id,
b.effect_dt,
b.hrly_amt,
b.exmpt_fl,
b.work_state_cd,
b.genl_lab_cat_cd,
c.lab_loc_cd,
c.acct_id,
c.work_comp_cd
FROM empl a,
empl_lab_info b,
dflt_reg_ts c
WHERE a.empl_id LIKE '4%' AND
a.empl_id > '41999' AND
a.empl_id = b.empl_id AND
a.empl_id = c.empl_id AND
b.org_id LIKE '1.01.05%' AND
b.effect_dt = (SELECT MAX(effect_dt)
FROM empl_lab_info
WHERE empl_id = a.empl_id)
ORDER BY a.empl_id;

BEGIN

SELECT TO_CHAR(sysdate - 1, 'MM/DD/YY') INTO vToday FROM dual;

fileID := UTL_FILE.FOPEN('C:\una_in', 'empl_SETS.lst', 'W');

FOR this_employee IN empl_list LOOP

SELECT COUNT(*)
INTO is_pm_fl
FROM proj
WHERE empl_id = this_employee.empl_id;

IF is_pm_fl = 0 THEN
user_role := 'timesheetUser';
ELSE
user_role := 'timesheetUser' || ',' || 'manager';
END IF;

IF this_employee.empl_id = '42003' OR
this_employee.empl_id = '42005' OR
this_employee.empl_id = '42006' OR
this_employee.empl_id = '42007' OR
this_employee.empl_id = '42012' OR
this_employee.empl_id = '42013' OR
this_employee.empl_id = '42014' OR
this_employee.empl_id = '42016' OR
this_employee.empl_id = '42058' OR
this_employee.empl_id = '42062' OR
this_employee.empl_id = '42064' OR
this_employee.empl_id = '42091' OR
this_employee.empl_id = '42093' OR
this_employee.empl_id = '42095' OR
this_employee.empl_id = '42096' OR
this_employee.empl_id = '42099' OR
this_employee.empl_id = '42100' OR
this_employee.empl_id = '42103' OR
this_employee.empl_id = '42104' OR
this_employee.empl_id = '42110' OR
this_employee.empl_id = '42111' OR
this_employee.empl_id = '42112' OR
this_employee.empl_id = '42115' OR
this_employee.empl_id = '42117' OR
this_employee.empl_id = '42120' OR
this_employee.empl_id = '42129' OR
this_employee.empl_id = '42131' OR
this_employee.empl_id = '42132' OR
this_employee.empl_id = '42145' OR
this_employee.empl_id = '42150' OR
this_employee.empl_id = '42154' OR
this_employee.empl_id = '42156' OR
this_employee.empl_id = '42157' OR
this_employee.empl_id = '42160' OR
this_employee.empl_id = '42164' OR
this_employee.empl_id = '42182' OR
this_employee.empl_id = '42183' OR
this_employee.empl_id = '42184' OR
this_employee.empl_id = '42186' OR
this_employee.empl_id = '42187' OR
this_employee.empl_id = '42188' OR
this_employee.empl_id = '42190' OR
this_employee.empl_id = '42191' OR
this_employee.empl_id = '42195' OR
this_employee.empl_id = '42202' OR
this_employee.empl_id = '42207' OR
this_employee.empl_id = '42211' OR
this_employee.empl_id = '42122' OR
this_employee.empl_id = '42213' OR
this_employee.empl_id = '42214' OR
this_employee.empl_id = '42223' OR
this_employee.empl_id = '42253' OR
this_employee.empl_id = '42254' OR
this_employee.empl_id = '42261' OR
this_employee.empl_id = '42262' OR
this_employee.empl_id = '42264' OR
this_employee.empl_id = '42306' OR
this_employee.empl_id = '42309' OR
this_employee.empl_id = '42311' OR
this_employee.empl_id = '42312' OR
this_employee.empl_id = '42316' OR
this_employee.empl_id = '42318' OR
this_employee.empl_id = '42325' OR
this_employee.empl_id = '42338' OR
this_employee.empl_id = '42339' OR
this_employee.empl_id = '42341' OR
this_employee.empl_id = '42353' OR
this_employee.empl_id = '42357' OR
this_employee.empl_id = '42359' OR
this_employee.empl_id = '42368' OR
this_employee.empl_id = '42374' OR
this_employee.empl_id = '42385' OR
this_employee.empl_id = '42391' OR
this_employee.empl_id = '42392' OR
this_employee.empl_id = '42393' OR
this_employee.empl_id = '42402' OR
this_employee.empl_id = '42406' OR
this_employee.empl_id = '42413' OR
this_employee.empl_id = '42420' OR
this_employee.empl_id = '42422' OR
this_employee.empl_id = '42426' OR
this_employee.empl_id = '42430' OR
this_employee.empl_id = '42431' OR
this_employee.empl_id = '42433' OR
this_employee.empl_id = '42441' OR
this_employee.empl_id = '42464' OR
this_employee.empl_id = '42472' OR
this_employee.empl_id = '42477' OR
this_employee.empl_id = '42491' OR
this_employee.empl_id = '42497' OR
this_employee.empl_id = '42498' OR
this_employee.empl_id = '42521' OR
this_employee.empl_id = '42529' OR
this_employee.empl_id = '42547' OR
this_employee.empl_id = '42548' OR
this_employee.empl_id = '42549' OR
this_employee.empl_id = '42554' OR
this_employee.empl_id = '42557' OR
this_employee.empl_id = '42559' OR
this_employee.empl_id = '42584' OR
this_employee.empl_id = '42587' OR
this_employee.empl_id = '42588' OR
this_employee.empl_id = '42589' OR
this_employee.empl_id = '42609' OR
this_employee.empl_id = '42612' OR
this_employee.empl_id = '42652' OR
this_employee.empl_id = '42678' OR
this_employee.empl_id = '42689' OR
this_employee.empl_id = '42693' OR
this_employee.empl_id = '42695' OR
this_employee.empl_id = '42751' OR
this_employee.empl_id = '42746' OR
this_employee.empl_id = '42764' OR
this_employee.empl_id = '42768' OR
this_employee.empl_id = '42817' OR
this_employee.empl_id = '42818' OR
this_employee.empl_id = '42832' OR
this_employee.empl_id = '42885' OR
this_employee.empl_id = '42841' OR
this_employee.empl_id = '42941' OR
this_employee.empl_id = '42943' OR
this_employee.empl_id = '42957' OR
this_employee.empl_id = '42970' OR
this_employee.empl_id = '42977' OR
this_employee.empl_id = '42992' OR
this_employee.empl_id = '43040' OR
this_employee.empl_id = '43132' OR
this_employee.empl_id = '43155' OR
this_employee.empl_id = '43001' OR
this_employee.empl_id = '43181' OR
this_employee.empl_id = '43200' OR
this_employee.empl_id = '43229' OR
this_employee.empl_id = '43235' OR
this_employee.empl_id = '43265' OR
this_employee.empl_id = '43287' OR
this_employee.empl_id = '43558' OR
this_employee.empl_id = '43560' OR
this_employee.empl_id = '43561' OR
this_employee.empl_id = '43567' OR
this_employee.empl_id = '43580' OR
this_employee.empl_id = '43585' OR
this_employee.empl_id = '43594' OR
this_employee.empl_id = '43600' OR
this_employee.empl_id = '43601' OR
this_employee.empl_id = '43603' OR
this_employee.empl_id = '43617' OR
this_employee.empl_id = '43624' OR
this_employee.empl_id = '43644' THEN
user_role := 'timesheetUser' || ',' || 'manager';
END IF;

IF this_employee.empl_id = '42933' THEN
user_role := 'timesheetUser' || ',' || 'administrator';
END IF;

EOT := TO_CHAR(this_employee.effect_dt, 'MM/DD/YY');

IF this_employee.S_EMPL_STATUS_CD != 'IN' THEN
active_fl := 'Y';
ELSE
active_fl := 'N';
END IF;

IF this_employee.exmpt_fl = 'Y' THEN
exempt_fl := 'E';
ELSE
exempt_fl := 'N';
END IF;

this_empl_org := 'SI_INC.';

IF this_employee.lab_loc_cd IS NOT NULL THEN
vLocation := this_employee.lab_loc_cd;
ELSE
SELECT lab_loc_cd INTO vLocation
FROM empl_lab_info
WHERE empl_id = this_employee.empl_id AND
effect_dt = (SELECT MAX(effect_dt)
FROM empl_lab_info
WHERE empl_id = this_employee.empl_id);
END IF;

IF TO_CHAR(this_employee.orig_hire_dt, 'MM/DD/YY') = vToday THEN
vPassword := '"' || this_employee.empl_id || '"';
ELSE
-- vPassword := NULL;
vPassword := '"' || this_employee.empl_id || '"';
END IF;


this_line := '"' || this_employee.empl_id ||
'","' || this_employee.first_name ||
'","' || this_employee.last_name ||
'",' ||
',' ||
',' ||
',"' || exempt_fl ||
'","' || user_role ||
'","Standard"' ||
',"R"' ||
',"0.10"' ||
',' ||
',' ||
',"' || this_employee.empl_id ||
'","' || this_employee.acct_id ||
'",' || vPassword ||
',' ||
',' ||
',"' || this_empl_org ||
'","0.00"' ||
',"0.00"' ||
',' ||
',"' || active_fl ||
'","Y"' ||
',"N"' ||
',,"0.00"' ||
',"' || EOT ||
'",,,,,,"User 01","' || this_employee.work_state_cd ||
'","' || this_employee.genl_lab_cat_cd ||
'","' || vLocation ||
'","' || this_employee.org_id ||
'","' || this_employee.work_comp_cd ||
'","' || this_employee.org_id ||
'","User08","User09","User10"';

UTL_FILE.PUT_LINE(fileID, this_line);

END LOOP;

UTL_FILE.FCLOSE(fileID);

END;


------------------------------------------------------------------------------------------------------
--
-- PROCEDURE UNANET_PROJ_EXPORT
--
-- This procedure extracts the project information from Costpoint and
-- creates a flat file called: proj_SETS.lst in the c:\una_in directory of the Costpoint
-- server. The file is written in the proper format to be imported into Unanet version 3.4
--
------------------------------------------------------------------------------------------------------


PROCEDURE unanet_proj_export IS

Proj_fileID UTL_FILE.FILE_TYPE; -- Specifications of project output file.
Task_fileID UTL_FILE.FILE_TYPE; -- Specifications of task output file.
proj_line VARCHAR2(500); -- Text string written to project output file.
task_line VARCHAR2(500); -- Text string written to task output file.
this_proj_type VARCHAR2(15); -- (DIRECT, INDIRECT, BandP) Overhead, Direct, Bid and Prop.
type_set NUMBER(1); -- Flag to control program flow.
this_level_name VARCHAR2(25); -- Project name at specified project level.
this_proj_start VARCHAR2(8); -- Project Start Date converted to string.
this_proj_end VARCHAR2(8); -- Project End Date converted to string.
this_proj_mgr VARCHAR2(8); -- Project Manager id or admin if null.
this_project_status VARCHAR2(10); -- Project Status (Open, Closed).
this_project_id VARCHAR2(30) := NULL; -- Project number used in emp_proj cursor.
last_project_id VARCHAR2(30) := 0; -- Project number of last project-task processed.
task_id VARCHAR2(40); -- variable to hold task number value.
self_assn_fl VARCHAR2(1); -- Variable to determine if employees can self assign.
this_proj_org VARCHAR2(20); -- Variable for organization name (SI Inc., SI Telcom).
vPLC VARCHAR2(6); -- Variable for Project PLC code.
task_level_assign VARCHAR2(1); -- Variable to determine if task or project level assignment

CURSOR proj_list IS
SELECT a.org_id,
a.proj_id,
a.lvl_no,
a.proj_type_dc,
a.proj_start_dt,
a.proj_end_dt,
a.allow_charges_fl,
b.active_fl,
a.proj_work_frc_fl,
a.top_lvl_wrk_frc_fl,
a.l1_proj_seg_id,
a.l2_proj_seg_id,
a.l3_proj_seg_id,
a.l4_proj_seg_id,
a.l5_proj_seg_id,
a.l6_proj_seg_id,
a.l7_proj_seg_id,
a.l8_proj_seg_id,
a.l9_proj_seg_id,
a.l10_proj_seg_id,
a.l11_proj_seg_id,
a.l12_proj_seg_id,
a.l13_proj_seg_id,
a.l14_proj_seg_id,
a.l15_proj_seg_id,
a.l1_proj_name,
a.l2_proj_name,
a.l3_proj_name,
a.l4_proj_name,
a.l5_proj_name,
a.l6_proj_name,
a.l7_proj_name,
a.l8_proj_name,
a.l9_proj_name,
a.l10_proj_name,
a.l11_proj_name,
a.l12_proj_name,
a.l13_proj_name,
a.l14_proj_name,
b.acct_id
FROM proj a,
proj_org_acct b
WHERE a.lvl_no > 2 AND
a.proj_id = b.proj_id AND
a.proj_type_dc != 'INVENTORY' AND
b.acct_id IN (SELECT acct_id
FROM acct_grp_setup
WHERE s_acct_func_dc = 'LABOR' AND
active_fl = 'Y') AND
a.org_id LIKE '1.01.05%'
ORDER BY a.proj_id;

BEGIN

Proj_fileID := UTL_FILE.FOPEN('C:\una_in', 'proj_SETS.lst', 'W');
Task_fileID := UTL_FILE.FOPEN('C:\una_in', 'task_SETS.lst', 'W');


FOR this_proj IN proj_list LOOP

proj_line := NULL;
task_line := NULL;
this_proj_type := NULL;
type_set := 0;
this_level_name := NULL;
this_proj_start := NULL;
this_proj_end := NULL;
IF this_proj.proj_start_dt < this_proj.proj_end_dt THEN
this_proj_start := TO_CHAR(this_proj.proj_start_dt, 'MM/DD/YY');
this_proj_end := TO_CHAR(this_proj.proj_end_dt, 'MM/DD/YY');
END IF;
this_project_status := 'OPEN';
task_id := NULL;
this_proj_mgr := 'ADMIN';
this_project_id := this_proj.l1_proj_seg_id || '.' || this_proj.l2_proj_seg_id;

IF this_proj.proj_work_frc_fl = 'N' AND
this_proj.top_lvl_wrk_frc_fl = 'N' THEN
self_assn_fl := 'Y';
ELSE
self_assn_fl := 'N';
END IF;

IF this_proj.proj_work_frc_fl = 'Y' AND
this_proj.top_lvl_wrk_frc_fl = 'N' THEN
task_level_assign := 'Y';
ELSE
task_level_assign := 'N';
END IF;

this_proj_org := 'SI_INC.';

IF this_proj.l2_proj_seg_id LIKE 'S5000%' THEN
this_proj_org := 'SI_INTL_MGT';
END IF;

IF this_project_id != last_project_id THEN

this_level_name := this_proj.l2_proj_name;

IF this_proj.l2_proj_seg_id LIKE 'A%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'B%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'C%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'D%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'E%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'F%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'G%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'H%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'I%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'J%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'K%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'L%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'M%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'N%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'O%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'P%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'Q%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'R%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'S%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'T%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'U%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'V%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'W%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'X%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'Y%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'Z%%%%%' THEN

this_proj_type := 'INDIRECT';
type_set := 1;

END IF;

IF type_set = 0 THEN
IF this_proj.l2_proj_seg_id LIKE '95%%%' THEN
this_proj_type := 'B' || '&' || 'P';
type_set := 1;
END IF;
END IF;

IF type_set = 0 THEN
this_proj_type := 'DIRECT';
END IF;

IF this_proj.active_fl = 'N' THEN
this_project_status := 'OPEN';
END IF;

proj_line := '&quot;' || this_proj_org ||
'&quot;,&quot;' || this_project_id ||
'&quot;,&quot;' || this_proj_type ||
'&quot;,&quot;' || this_project_status ||
'&quot;,&quot;' || this_proj_mgr ||
'&quot;,&quot;V&quot;,&quot;' || self_assn_fl ||
'&quot;,&quot;C&quot;,&quot;Y&quot;,&quot;N&quot;,&quot;N&quot;,&quot;Y&quot;,&quot;Y&quot;,&quot;N&quot;,&quot;' || this_proj_start ||
'&quot;,&quot;' || this_proj_end || '&quot;,,,,,,,,,,,,&quot;' || this_project_id ||
'&quot;,&quot;' || this_level_name ||'&quot;,,,,&quot;!Sys_default!&quot;,&quot;!Sys_default!&quot;,&quot;' || task_level_assign || '&quot;,,&quot;' ||
this_proj.acct_id || '&quot;,,,,,,,,';

UTL_FILE.PUT_LINE(Proj_fileID, proj_line);
last_project_id := this_proj.l1_proj_seg_id || '.' || this_proj.l2_proj_seg_id;


END IF;

IF this_proj.lvl_no > 2 THEN
IF this_proj.lvl_no >= 3 THEN
task_id := this_proj.l3_proj_seg_id;
IF this_proj.lvl_no >= 4 THEN
task_id := task_id || '.' || this_proj.l4_proj_seg_id;
IF this_proj.lvl_no >= 5 THEN
task_id := task_id || '.' || this_proj.l5_proj_seg_id;
IF this_proj.lvl_no >= 6 THEN
task_id := task_id || '.' || this_proj.l6_proj_seg_id;
IF this_proj.lvl_no >= 7 THEN
task_id := task_id || '.' || this_proj.l7_proj_seg_id;
IF this_proj.lvl_no >= 8 THEN
task_id := task_id || '.' || this_proj.l8_proj_seg_id;
IF this_proj.lvl_no >= 9 THEN
task_id := task_id || '.' || this_proj.l9_proj_seg_id;
IF this_proj.lvl_no >= 10 THEN
task_id := task_id || '.' || this_proj.l10_proj_seg_id;
IF this_proj.lvl_no >= 11 THEN
task_id := task_id || '.' || this_proj.l11_proj_seg_id;
IF this_proj.lvl_no >= 12 THEN
task_id := task_id || '.' || this_proj.l12_proj_seg_id;
IF this_proj.lvl_no >= 13 THEN
task_id := task_id || '.' || this_proj.l13_proj_seg_id;
IF this_proj.lvl_no >= 14 THEN
task_id := task_id || '.' || this_proj.l14_proj_seg_id;
IF this_proj.lvl_no >= 15 THEN
task_id := task_id || '.' || this_proj.l15_proj_seg_id;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;

IF this_project_status = 'Open' THEN
IF this_proj.allow_charges_fl = 'N' THEN
this_project_status := 'CLOSED';
END IF;
END IF;

task_line := '&quot;' || this_proj_org ||
'&quot;,&quot;' || this_project_id ||
'&quot;,&quot;' || task_id ||
' ' ||this_proj.acct_id ||
'&quot;,&quot;' || this_proj.allow_charges_fl ||
'&quot;,&quot;' || this_proj_start ||
'&quot;,&quot;' || this_proj_end ||
'&quot;,,,,,&quot;' || this_proj.acct_id ||
'&quot;,&quot;' || this_proj.proj_id ||
'&quot;,,,,,,,,,,,,,&quot;USER1&quot;,&quot;' || this_proj.acct_id ||
'&quot;,&quot;USER3&quot;,&quot;USER4&quot;,&quot;USER5&quot;,&quot;USER6&quot;,&quot;USER7&quot;,&quot;USER8&quot;,&quot;USER9&quot;,&quot;USER10&quot;';

UTL_FILE.PUT_LINE(Task_fileID, task_line);

END IF;

END LOOP;

COMMIT;

UTL_FILE.FCLOSE(Proj_fileID);
UTL_FILE.FCLOSE(Task_fileID);

END;


------------------------------------------------------------------------------------------------------
--
-- PROCEDURE UNANET_ASSIGN_EXPORT
--
-- This procedure extracts the organization information from Costpoint and
-- creates a flat file called: org_SETS.lst in the c:\una_in directory of the Costpoint
-- server. The file is written in the proper format to be imported into Unanet version 3.3
--
------------------------------------------------------------------------------------------------------

PROCEDURE unanet_assign_export IS

fileID UTL_FILE.FILE_TYPE; -- Specifications of output file.
this_line VARCHAR2(500); -- Text string written to project output file.
task_id VARCHAR2(40); -- Text string to store value of current task.
task_level VARCHAR2(3);
task_level_id VARCHAR2(40);
this_proj_org VARCHAR2(20); -- Variable for organization name (SI Inc., SI Telcom).
this_project VARCHAR2(40); -- Variable to store project number.


-- Cursor of assignments at the TASK level
CURSOR assign_list IS
SELECT a.proj_id,
a.empl_id,
b.org_id,
b.lvl_no,
b.l1_proj_seg_id,
b.l2_proj_seg_id,
b.l3_proj_seg_id,
b.l4_proj_seg_id,
b.l5_proj_seg_id,
b.l6_proj_seg_id,
b.l7_proj_seg_id,
b.l8_proj_seg_id,
b.l9_proj_seg_id,
b.l10_proj_seg_id,
b.l11_proj_seg_id,
b.l12_proj_seg_id,
b.l13_proj_seg_id,
b.l14_proj_seg_id,
b.l15_proj_seg_id,
c.acct_id
FROM proj_empl a,
proj b,
proj_org_acct c
WHERE b.org_id LIKE '1.01.05%' AND
a.empl_id LIKE '4%%%%' AND
b.lvl_no > 2 AND
b.top_lvl_wrk_frc_fl = 'N' AND
a.proj_id = b.proj_id AND
a.proj_id = c.proj_id AND
b.proj_type_dc != 'INVENTORY' AND
c.acct_id IN (SELECT acct_id
FROM acct_grp_setup
WHERE s_acct_func_dc = 'LABOR' AND
active_fl = 'Y')
ORDER BY a.empl_id, a.proj_id;


-- Cursor of assignments at the Project level
CURSOR assign_list3 IS
SELECT DISTINCT(a.l1_proj_seg_id) as l1_proj_seg_id,
a.l2_proj_seg_id as l2_proj_seg_id,
b.empl_id,
a.org_id
FROM proj a,
proj_empl b
WHERE a.org_id LIKE '1.01.05%' AND
a.top_lvl_wrk_frc_fl = 'Y' AND
a.active_fl = 'Y' AND
a.allow_charges_fl = 'Y' AND
a.l1_proj_seg_id IN (SELECT a.l1_proj_seg_id
FROM proj a
WHERE a.proj_id = b.proj_id AND
a.top_lvl_wrk_frc_fl = 'Y') AND
b.empl_id LIKE '4%%%%' AND
b.empl_id in (SELECT empl_id
FROM empl
WHERE s_empl_status_cd='ACT')
ORDER BY a.l1_proj_seg_id, a.l2_proj_seg_id, b.empl_id;

BEGIN

fileID := UTL_FILE.FOPEN('C:\una_in', 'assn_SETS.lst', 'W');

FOR this_employee IN assign_list LOOP

task_id := NULL;

this_project := this_employee.l1_proj_seg_id || '.' || this_employee.l2_proj_seg_id;

this_proj_org := 'SI_INC.';

IF this_employee.lvl_no >= 3 THEN
task_id := this_employee.l3_proj_seg_id;
IF this_employee.lvl_no >= 4 THEN
task_id := task_id || '.' || this_employee.l4_proj_seg_id;
IF this_employee.lvl_no >= 5 THEN
task_id := task_id || '.' || this_employee.l5_proj_seg_id;
IF this_employee.lvl_no >= 6 THEN
task_id := task_id || '.' || this_employee.l6_proj_seg_id;
IF this_employee.lvl_no >= 7 THEN
task_id := task_id || '.' || this_employee.l7_proj_seg_id;
IF this_employee.lvl_no >= 8 THEN
task_id := task_id || '.' || this_employee.l8_proj_seg_id;
IF this_employee.lvl_no >= 9 THEN
task_id := task_id || '.' || this_employee.l9_proj_seg_id;
IF this_employee.lvl_no >= 10 THEN
task_id := task_id || '.' || this_employee.l10_proj_seg_id;
IF this_employee.lvl_no >= 11 THEN
task_id := task_id || '.' || this_employee.l11_proj_seg_id;
IF this_employee.lvl_no >= 12 THEN
task_id := task_id || '.' || this_employee.l12_proj_seg_id;
IF this_employee.lvl_no >= 13 THEN
task_id := task_id || '.' || this_employee.l13_proj_seg_id;
IF this_employee.lvl_no >= 14 THEN
task_id := task_id || '.' || this_employee.l14_proj_seg_id;
IF this_employee.lvl_no >= 15 THEN
task_id := task_id || '.' || this_employee.l15_proj_seg_id;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;

this_line := '&quot;' || this_proj_org ||
'&quot;,&quot;' || this_project ||
'&quot;,&quot;' || task_id ||
' ' || this_employee.acct_id ||
'&quot;,&quot;' || this_employee.empl_id || '&quot;';

UTL_FILE.PUT_LINE(fileID, this_line);

END LOOP;



FOR this_person IN assign_list3 LOOP

this_proj_org := 'SI_INC.';

this_line := '&quot;' || this_proj_org ||
'&quot;,&quot;' || this_person.l1_proj_seg_id || '.' || this_person.l2_proj_seg_id ||
'&quot;,,&quot;' || this_person.empl_id || '&quot;';

UTL_FILE.PUT_LINE(fileID, this_line);

END LOOP;

UTL_FILE.FCLOSE(fileID);

END;

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


END UNANET_EXPORT;
/
SHOW ERRORS
/


This is UNA_TEL script: The telecomm, colorado database we have here



------------------------------------------------------------------------------------------------------
-- PACKAGE Unanet_export Created: January, 24 2002 by Tom Elliott
--
-- This package contains the procedures and functions used to extract data from
-- Costpoint into flat files formatted for importation into Unanet version 3.4
--
------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE UNANET_EXPORT IS

PROCEDURE export_all;
PROCEDURE unanet_empl_export;
PROCEDURE unanet_proj_export;
PROCEDURE unanet_assign_export;

END UNANET_EXPORT;
/
SHOW ERRORS
/

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

CREATE OR REPLACE PACKAGE BODY UNANET_EXPORT IS

------------------------------------------------------------------------------------------------------
--
-- PROCEDURE EXPORT_ALL
--
-- This procedure executes all the Unanet export procedures in this package.
--
------------------------------------------------------------------------------------------------------


PROCEDURE export_all IS

BEGIN

unanet_export.unanet_empl_export;
unanet_export.unanet_proj_export;
unanet_export.unanet_assign_export;

END;


------------------------------------------------------------------------------------------------------
--
-- PROCEDURE UNANET_EMPL_EXPORT
--
-- This procedure extracts the employee information from Costpoint and
-- creates a flat file called: empl_TEL.lst in the c:\una_in directory of the Costpoint
-- server. The file is written in the proper format to be imported into Unanet version 3.4
--
------------------------------------------------------------------------------------------------------


PROCEDURE unanet_empl_export IS

fileID UTL_FILE.FILE_TYPE; -- Specifications of output file.
this_line VARCHAR2(500); -- Text string written to project output file.
active_fl VARCHAR2(1); -- variable to hold derived active status of employee.
EOT VARCHAR2(8); -- Variable to store the effective date.
exempt_fl VARCHAR2(1); -- Variable to store the exempt status of the employee.
this_date DATE; -- Variable to check for most recent employee record.
user_role VARCHAR2(100); -- Variable to store user roles.
is_pm_fl INTEGER; -- Variable to test for PM role.
this_empl_org VARCHAR2(15); -- Variable to store company name.
vPassword VARCHAR2(15); -- Variable to store employee password.
vLocation VARCHAR2(15); -- Variable to store employee location.
vtoday VARCHAR2(8); -- Variable to store current date.


CURSOR empl_list IS
SELECT distinct a.empl_id,
a.term_dt,
a.first_name,
a.last_name,
a.pref_name,
a.name_sfx_cd,
a.email_id,
a.s_empl_status_cd,
a.orig_hire_dt,
b.org_id,
b.effect_dt,
b.hrly_amt,
b.exmpt_fl,
b.work_state_cd,
b.genl_lab_cat_cd,
c.lab_loc_cd,
c.acct_id,
c.work_comp_cd
FROM empl a,
empl_lab_info b,
dflt_reg_ts c
WHERE a.empl_id LIKE '4%' AND
a.empl_id > '41999' AND
a.empl_id = b.empl_id AND
a.empl_id = c.empl_id AND
b.org_id LIKE '1.02.06%' AND
b.effect_dt = (SELECT MAX(effect_dt)
FROM empl_lab_info
WHERE empl_id = a.empl_id)
ORDER BY a.empl_id;

BEGIN

SELECT TO_CHAR(sysdate - 1, 'MM/DD/YY') INTO vToday FROM dual;

fileID := UTL_FILE.FOPEN('C:\una_in', 'empl_TEL.lst', 'W');

FOR this_employee IN empl_list LOOP

SELECT COUNT(*)
INTO is_pm_fl
FROM proj
WHERE empl_id = this_employee.empl_id;

IF is_pm_fl = 0 THEN
user_role := 'timesheetUser';
ELSE
user_role := 'timesheetUser' || ',' || 'manager';
END IF;

IF this_employee.empl_id = '42003' OR
this_employee.empl_id = '42005' OR
this_employee.empl_id = '42006' OR
this_employee.empl_id = '42007' OR
this_employee.empl_id = '42012' OR
this_employee.empl_id = '42013' OR
this_employee.empl_id = '42014' OR
this_employee.empl_id = '42016' OR
this_employee.empl_id = '42058' OR
this_employee.empl_id = '42062' OR
this_employee.empl_id = '42064' OR
this_employee.empl_id = '42091' OR
this_employee.empl_id = '42093' OR
this_employee.empl_id = '42095' OR
this_employee.empl_id = '42096' OR
this_employee.empl_id = '42099' OR
this_employee.empl_id = '42100' OR
this_employee.empl_id = '42103' OR
this_employee.empl_id = '42104' OR
this_employee.empl_id = '42110' OR
this_employee.empl_id = '42111' OR
this_employee.empl_id = '42112' OR
this_employee.empl_id = '42115' OR
this_employee.empl_id = '42117' OR
this_employee.empl_id = '42120' OR
this_employee.empl_id = '42129' OR
this_employee.empl_id = '42131' OR
this_employee.empl_id = '42132' OR
this_employee.empl_id = '42145' OR
this_employee.empl_id = '42150' OR
this_employee.empl_id = '42154' OR
this_employee.empl_id = '42156' OR
this_employee.empl_id = '42157' OR
this_employee.empl_id = '42160' OR
this_employee.empl_id = '42164' OR
this_employee.empl_id = '42182' OR
this_employee.empl_id = '42183' OR
this_employee.empl_id = '42184' OR
this_employee.empl_id = '42186' OR
this_employee.empl_id = '42187' OR
this_employee.empl_id = '42188' OR
this_employee.empl_id = '42190' OR
this_employee.empl_id = '42191' OR
this_employee.empl_id = '42195' OR
this_employee.empl_id = '42202' OR
this_employee.empl_id = '42207' OR
this_employee.empl_id = '42211' OR
this_employee.empl_id = '42122' OR
this_employee.empl_id = '42213' OR
this_employee.empl_id = '42214' OR
this_employee.empl_id = '42223' OR
this_employee.empl_id = '42253' OR
this_employee.empl_id = '42254' OR
this_employee.empl_id = '42261' OR
this_employee.empl_id = '42262' OR
this_employee.empl_id = '42264' OR
this_employee.empl_id = '42306' OR
this_employee.empl_id = '42309' OR
this_employee.empl_id = '42311' OR
this_employee.empl_id = '42312' OR
this_employee.empl_id = '42316' OR
this_employee.empl_id = '42318' OR
this_employee.empl_id = '42325' OR
this_employee.empl_id = '42338' OR
this_employee.empl_id = '42339' OR
this_employee.empl_id = '42341' OR
this_employee.empl_id = '42353' OR
this_employee.empl_id = '42357' OR
this_employee.empl_id = '42359' OR
this_employee.empl_id = '42368' OR
this_employee.empl_id = '42374' OR
this_employee.empl_id = '42385' OR
this_employee.empl_id = '42391' OR
this_employee.empl_id = '42392' OR
this_employee.empl_id = '42393' OR
this_employee.empl_id = '42402' OR
this_employee.empl_id = '42406' OR
this_employee.empl_id = '42413' OR
this_employee.empl_id = '42420' OR
this_employee.empl_id = '42422' OR
this_employee.empl_id = '42426' OR
this_employee.empl_id = '42430' OR
this_employee.empl_id = '42431' OR
this_employee.empl_id = '42433' OR
this_employee.empl_id = '42441' OR
this_employee.empl_id = '42464' OR
this_employee.empl_id = '42472' OR
this_employee.empl_id = '42477' OR
this_employee.empl_id = '42491' OR
this_employee.empl_id = '42497' OR
this_employee.empl_id = '42498' OR
this_employee.empl_id = '42521' OR
this_employee.empl_id = '42529' OR
this_employee.empl_id = '42547' OR
this_employee.empl_id = '42548' OR
this_employee.empl_id = '42549' OR
this_employee.empl_id = '42554' OR
this_employee.empl_id = '42557' OR
this_employee.empl_id = '42559' OR
this_employee.empl_id = '42584' OR
this_employee.empl_id = '42587' OR
this_employee.empl_id = '42588' OR
this_employee.empl_id = '42589' OR
this_employee.empl_id = '42609' OR
this_employee.empl_id = '42612' OR
this_employee.empl_id = '42652' OR
this_employee.empl_id = '42678' OR
this_employee.empl_id = '42689' OR
this_employee.empl_id = '42693' OR
this_employee.empl_id = '42695' OR
this_employee.empl_id = '42751' OR
this_employee.empl_id = '42746' OR
this_employee.empl_id = '42764' OR
this_employee.empl_id = '42768' OR
this_employee.empl_id = '42817' OR
this_employee.empl_id = '42818' OR
this_employee.empl_id = '42832' OR
this_employee.empl_id = '42885' OR
this_employee.empl_id = '42841' OR
this_employee.empl_id = '42941' OR
this_employee.empl_id = '42943' OR
this_employee.empl_id = '42957' OR
this_employee.empl_id = '42970' OR
this_employee.empl_id = '42977' OR
this_employee.empl_id = '42992' OR
this_employee.empl_id = '43040' OR
this_employee.empl_id = '43132' OR
this_employee.empl_id = '43155' OR
this_employee.empl_id = '43181' OR
this_employee.empl_id = '43200' OR
this_employee.empl_id = '43229' OR
this_employee.empl_id = '43235' OR
this_employee.empl_id = '43265' OR
this_employee.empl_id = '43287' OR
this_employee.empl_id = '43558' OR
this_employee.empl_id = '43560' OR
this_employee.empl_id = '43561' OR
this_employee.empl_id = '43567' OR
this_employee.empl_id = '43580' OR
this_employee.empl_id = '43585' OR
this_employee.empl_id = '43594' OR
this_employee.empl_id = '43600' OR
this_employee.empl_id = '43601' OR
this_employee.empl_id = '43603' OR
this_employee.empl_id = '43617' OR
this_employee.empl_id = '43624' OR
this_employee.empl_id = '43644' THEN
user_role := 'timesheetUser' || ',' || 'manager';
END IF;

IF this_employee.empl_id = '42933' THEN
user_role := 'timesheetUser' || ',' || 'administrator';
END IF;

EOT := TO_CHAR(this_employee.effect_dt, 'MM/DD/YY');

IF this_employee.S_EMPL_STATUS_CD != 'IN' THEN
active_fl := 'Y';
ELSE
active_fl := 'N';
END IF;

IF this_employee.exmpt_fl = 'Y' THEN
exempt_fl := 'E';
ELSE
exempt_fl := 'N';
END IF;

this_empl_org := 'SI_TELECOM';

IF this_employee.lab_loc_cd IS NOT NULL THEN
vLocation := this_employee.lab_loc_cd;
ELSE
SELECT lab_loc_cd INTO vLocation
FROM empl_lab_info
WHERE empl_id = this_employee.empl_id AND
effect_dt = (SELECT MAX(effect_dt)
FROM empl_lab_info
WHERE empl_id = this_employee.empl_id);
END IF;

IF TO_CHAR(this_employee.orig_hire_dt, 'MM/DD/YY') = vToday THEN
vPassword := '&quot;' || this_employee.empl_id || '&quot;';
ELSE
vPassword := NULL;
-- vPassword := '&quot;' || this_employee.empl_id || '&quot;';
END IF;


this_line := '&quot;' || this_employee.empl_id ||
'&quot;,&quot;' || this_employee.first_name ||
'&quot;,&quot;' || this_employee.last_name ||
'&quot;,' ||
',' ||
',' ||
',&quot;' || exempt_fl ||
'&quot;,&quot;' || user_role ||
'&quot;,&quot;Standard&quot;' ||
',&quot;R&quot;' ||
',&quot;0.10&quot;' ||
',' ||
',' ||
',&quot;' || this_employee.empl_id ||
'&quot;,&quot;' || this_employee.acct_id ||
'&quot;,' || vPassword ||
',' ||
',' ||
',&quot;' || this_empl_org ||
'&quot;,&quot;0.00&quot;' ||
',&quot;0.00&quot;' ||
',' ||
',&quot;' || active_fl ||
'&quot;,&quot;Y&quot;' ||
',&quot;N&quot;' ||
',,&quot;0.00&quot;' ||
',&quot;' || EOT ||
'&quot;,,,,,,&quot;User 01&quot;,&quot;' || this_employee.work_state_cd ||
'&quot;,&quot;' || this_employee.genl_lab_cat_cd ||
'&quot;,&quot;' || vLocation ||
'&quot;,&quot;' || this_employee.org_id ||
'&quot;,&quot;' || this_employee.work_comp_cd ||
'&quot;,&quot;' || this_employee.org_id ||
'&quot;,&quot;User08&quot;,&quot;User09&quot;,&quot;User10&quot;';

UTL_FILE.PUT_LINE(fileID, this_line);

END LOOP;

UTL_FILE.FCLOSE(fileID);

END;


------------------------------------------------------------------------------------------------------
--
-- PROCEDURE UNANET_PROJ_EXPORT
--
-- This procedure extracts the project information from Costpoint and
-- creates a flat file called: proj_TEL.lst in the c:\una_in directory of the Costpoint
-- server. The file is written in the proper format to be imported into Unanet version 3.4
--
------------------------------------------------------------------------------------------------------


PROCEDURE unanet_proj_export IS

Proj_fileID UTL_FILE.FILE_TYPE; -- Specifications of project output file.
Task_fileID UTL_FILE.FILE_TYPE; -- Specifications of task output file.
proj_line VARCHAR2(500); -- Text string written to project output file.
task_line VARCHAR2(500); -- Text string written to task output file.
this_proj_type VARCHAR2(15); -- (DIRECT, INDIRECT, BandP) Overhead, Direct, Bid and Prop.
type_set NUMBER(1); -- Flag to control program flow.
this_level_name VARCHAR2(25); -- Project name at specified project level.
this_proj_start VARCHAR2(8); -- Project Start Date converted to string.
this_proj_end VARCHAR2(8); -- Project End Date converted to string.
this_proj_mgr VARCHAR2(8); -- Project Manager id or admin if null.
this_project_status VARCHAR2(10); -- Project Status (Open, Closed).
this_project_id VARCHAR2(30) := NULL; -- Project number used in emp_proj cursor.
last_project_id VARCHAR2(30) := 0; -- Project number of last project-task processed.
task_id VARCHAR2(40); -- variable to hold task number value.
self_assn_fl VARCHAR2(1); -- Variable to determine if employees can self assign.
this_proj_org VARCHAR2(20); -- Variable for organization name (SI Inc., SI Telcom).
vPLC VARCHAR2(6); -- Variable for Project PLC code.
task_level_assign VARCHAR2(1); -- Variable to determine if task or project level assignment

CURSOR proj_list IS
SELECT a.org_id,
a.proj_id,
a.lvl_no,
a.proj_type_dc,
a.proj_start_dt,
a.proj_end_dt,
a.allow_charges_fl,
b.active_fl,
a.proj_work_frc_fl,
a.top_lvl_wrk_frc_fl,
a.l1_proj_seg_id,
a.l2_proj_seg_id,
a.l3_proj_seg_id,
a.l4_proj_seg_id,
a.l5_proj_seg_id,
a.l6_proj_seg_id,
a.l7_proj_seg_id,
a.l8_proj_seg_id,
a.l9_proj_seg_id,
a.l10_proj_seg_id,
a.l11_proj_seg_id,
a.l12_proj_seg_id,
a.l13_proj_seg_id,
a.l14_proj_seg_id,
a.l15_proj_seg_id,
a.l1_proj_name,
a.l2_proj_name,
a.l3_proj_name,
a.l4_proj_name,
a.l5_proj_name,
a.l6_proj_name,
a.l7_proj_name,
a.l8_proj_name,
a.l9_proj_name,
a.l10_proj_name,
a.l11_proj_name,
a.l12_proj_name,
a.l13_proj_name,
a.l14_proj_name,
b.acct_id
FROM proj a,
proj_org_acct b
WHERE a.lvl_no > 2 AND
a.proj_id = b.proj_id AND
a.proj_type_dc != 'INVENTORY' AND
b.acct_id IN (SELECT acct_id
FROM acct_grp_setup
WHERE s_acct_func_dc = 'LABOR' AND
active_fl = 'Y') AND
a.org_id LIKE '1.02.06%'
ORDER BY a.proj_id;

BEGIN

Proj_fileID := UTL_FILE.FOPEN('C:\una_in', 'proj_TEL.lst', 'W');
Task_fileID := UTL_FILE.FOPEN('C:\una_in', 'task_TEL.lst', 'W');


FOR this_proj IN proj_list LOOP

proj_line := NULL;
task_line := NULL;
this_proj_type := NULL;
type_set := 0;
this_level_name := NULL;
this_proj_start := NULL;
this_proj_end := NULL;
IF this_proj.proj_start_dt < this_proj.proj_end_dt THEN
this_proj_start := TO_CHAR(this_proj.proj_start_dt, 'MM/DD/YY');
this_proj_end := TO_CHAR(this_proj.proj_end_dt, 'MM/DD/YY');
END IF;
this_project_status := 'OPEN';
task_id := NULL;
this_proj_mgr := 'ADMIN';
this_project_id := this_proj.l1_proj_seg_id || '.' || this_proj.l2_proj_seg_id;

IF this_proj.proj_work_frc_fl = 'N' AND
this_proj.top_lvl_wrk_frc_fl = 'N' THEN
self_assn_fl := 'Y';
ELSE
self_assn_fl := 'N';
END IF;

IF this_proj.proj_work_frc_fl = 'Y' AND
this_proj.top_lvl_wrk_frc_fl = 'N' THEN
task_level_assign := 'Y';
ELSE
task_level_assign := 'N';
END IF;

this_proj_org := 'SI_TELECOM';

IF this_proj.l2_proj_seg_id LIKE 'S5000%' THEN
this_proj_org := 'SI_INTL_MGT';
END IF;

IF this_project_id != last_project_id THEN

this_level_name := this_proj.l2_proj_name;

IF this_proj.l2_proj_seg_id LIKE 'A%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'B%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'C%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'D%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'E%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'F%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'G%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'H%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'I%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'J%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'K%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'L%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'M%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'N%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'O%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'P%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'Q%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'R%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'S%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'T%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'U%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'V%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'W%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'X%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'Y%%%%%' OR
this_proj.l2_proj_seg_id LIKE 'Z%%%%%' THEN

this_proj_type := 'INDIRECT';
type_set := 1;

END IF;

IF type_set = 0 THEN
IF this_proj.l2_proj_seg_id LIKE '95%%%' THEN
this_proj_type := 'B' || '&' || 'P';
type_set := 1;
END IF;
END IF;

IF type_set = 0 THEN
this_proj_type := 'DIRECT';
END IF;

IF this_proj.active_fl = 'N' THEN
this_project_status := 'OPEN';
END IF;

proj_line := '&quot;' || this_proj_org ||
'&quot;,&quot;' || this_project_id ||
'&quot;,&quot;' || this_proj_type ||
'&quot;,&quot;' || this_project_status ||
'&quot;,&quot;' || this_proj_mgr ||
'&quot;,&quot;V&quot;,&quot;' || self_assn_fl ||
'&quot;,&quot;C&quot;,&quot;Y&quot;,&quot;N&quot;,&quot;N&quot;,&quot;Y&quot;,&quot;Y&quot;,&quot;N&quot;,&quot;' || this_proj_start ||
'&quot;,&quot;' || this_proj_end || '&quot;,,,,,,,,,,,,&quot;' || this_project_id ||
'&quot;,&quot;' || this_level_name ||'&quot;,,,,&quot;!Sys_default!&quot;,&quot;!Sys_default!&quot;,&quot;' || task_level_assign || '&quot;,,&quot;' ||
this_proj.acct_id || '&quot;,,,,,,,,';

UTL_FILE.PUT_LINE(Proj_fileID, proj_line);
last_project_id := this_proj.l1_proj_seg_id || '.' || this_proj.l2_proj_seg_id;


END IF;

IF this_proj.lvl_no > 2 THEN
IF this_proj.lvl_no >= 3 THEN
task_id := this_proj.l3_proj_seg_id;
IF this_proj.lvl_no >= 4 THEN
task_id := task_id || '.' || this_proj.l4_proj_seg_id;
IF this_proj.lvl_no >= 5 THEN
task_id := task_id || '.' || this_proj.l5_proj_seg_id;
IF this_proj.lvl_no >= 6 THEN
task_id := task_id || '.' || this_proj.l6_proj_seg_id;
IF this_proj.lvl_no >= 7 THEN
task_id := task_id || '.' || this_proj.l7_proj_seg_id;
IF this_proj.lvl_no >= 8 THEN
task_id := task_id || '.' || this_proj.l8_proj_seg_id;
IF this_proj.lvl_no >= 9 THEN
task_id := task_id || '.' || this_proj.l9_proj_seg_id;
IF this_proj.lvl_no >= 10 THEN
task_id := task_id || '.' || this_proj.l10_proj_seg_id;
IF this_proj.lvl_no >= 11 THEN
task_id := task_id || '.' || this_proj.l11_proj_seg_id;
IF this_proj.lvl_no >= 12 THEN
task_id := task_id || '.' || this_proj.l12_proj_seg_id;
IF this_proj.lvl_no >= 13 THEN
task_id := task_id || '.' || this_proj.l13_proj_seg_id;
IF this_proj.lvl_no >= 14 THEN
task_id := task_id || '.' || this_proj.l14_proj_seg_id;
IF this_proj.lvl_no >= 15 THEN
task_id := task_id || '.' || this_proj.l15_proj_seg_id;
END IF;
END IF;

 

[afelya]

END IF;

IF type_set = 0 THEN
IF this_proj.l2_proj_seg_id LIKE '95%%%' THEN
this_proj_type := 'B' || '&' || 'P';
type_set := 1;
END IF;
END IF;

IF type_set = 0 THEN
this_proj_type := 'DIRECT';
END IF;

IF this_proj.active_fl = 'N' THEN
this_project_status := 'OPEN';
END IF;

proj_line := '&quot;' || this_proj_org ||
'&quot;,&quot;' || this_project_id ||
'&quot;,&quot;' || this_proj_type ||
'&quot;,&quot;' || this_project_status ||
'&quot;,&quot;' || this_proj_mgr ||
'&quot;,&quot;V&quot;,&quot;' || self_assn_fl ||
'&quot;,&quot;C&quot;,&quot;Y&quot;,&quot;N&quot;,&quot;N&quot;,&quot;Y&quot;,&quot;Y&quot;,&quot;N&quot;,&quot;' || this_proj_start ||
'&quot;,&quot;' || this_proj_end || '&quot;,,,,,,,,,,,,&quot;' || this_project_id ||
'&quot;,&quot;' || this_level_name ||'&quot;,,,,&quot;!Sys_default!&quot;,&quot;!Sys_default!&quot;,&quot;' || task_level_assign || '&quot;,,&quot;' ||
this_proj.acct_id || '&quot;,,,,,,,,';

UTL_FILE.PUT_LINE(Proj_fileID, proj_line);
last_project_id := this_proj.l1_proj_seg_id || '.' || this_proj.l2_proj_seg_id;


END IF;

IF this_proj.lvl_no > 2 THEN
IF this_proj.lvl_no >= 3 THEN
task_id := this_proj.l3_proj_seg_id;
IF this_proj.lvl_no >= 4 THEN
task_id := task_id || '.' || this_proj.l4_proj_seg_id;
IF this_proj.lvl_no >= 5 THEN
task_id := task_id || '.' || this_proj.l5_proj_seg_id;
IF this_proj.lvl_no >= 6 THEN
task_id := task_id || '.' || this_proj.l6_proj_seg_id;
IF this_proj.lvl_no >= 7 THEN
task_id := task_id || '.' || this_proj.l7_proj_seg_id;
IF this_proj.lvl_no >= 8 THEN
task_id := task_id || '.' || this_proj.l8_proj_seg_id;
IF this_proj.lvl_no >= 9 THEN
task_id := task_id || '.' || this_proj.l9_proj_seg_id;
IF this_proj.lvl_no >= 10 THEN
task_id := task_id || '.' || this_proj.l10_proj_seg_id;
IF this_proj.lvl_no >= 11 THEN
task_id := task_id || '.' || this_proj.l11_proj_seg_id;
IF this_proj.lvl_no >= 12 THEN
task_id := task_id || '.' || this_proj.l12_proj_seg_id;
IF this_proj.lvl_no >= 13 THEN
task_id := task_id || '.' || this_proj.l13_proj_seg_id;
IF this_proj.lvl_no >= 14 THEN
task_id := task_id || '.' || this_proj.l14_proj_seg_id;
IF this_proj.lvl_no >= 15 THEN
task_id := task_id || '.' || this_proj.l15_proj_seg_id;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;

IF this_project_status = 'Open' THEN
IF this_proj.allow_charges_fl = 'N' THEN
this_project_status := 'CLOSED';
END IF;
END IF;

task_line := '&quot;' || this_proj_org ||
'&quot;,&quot;' || this_project_id ||
'&quot;,&quot;' || task_id ||
' ' ||this_proj.acct_id ||
'&quot;,&quot;' || this_proj.allow_charges_fl ||
'&quot;,&quot;' || this_proj_start ||
'&quot;,&quot;' || this_proj_end ||
'&quot;,,,,,&quot;' || this_proj.acct_id ||
'&quot;,&quot;' || this_proj.proj_id ||
'&quot;,,,,,,,,,,,,,&quot;USER1&quot;,&quot;' || this_proj.acct_id ||
'&quot;,&quot;USER3&quot;,&quot;USER4&quot;,&quot;USER5&quot;,&quot;USER6&quot;,&quot;USER7&quot;,&quot;USER8&quot;,&quot;USER9&quot;,&quot;USER10&quot;';

UTL_FILE.PUT_LINE(Task_fileID, task_line);

END IF;

END LOOP;

COMMIT;

UTL_FILE.FCLOSE(Proj_fileID);
UTL_FILE.FCLOSE(Task_fileID);

END;


------------------------------------------------------------------------------------------------------
--
-- PROCEDURE UNANET_ASSIGN_EXPORT
--
-- This procedure extracts the organization information from Costpoint and
-- creates a flat file called: org_SETS.lst in the c:\una_in directory of the Costpoint
-- server. The file is written in the proper format to be imported into Unanet version 3.3
--
------------------------------------------------------------------------------------------------------

PROCEDURE unanet_assign_export IS

fileID UTL_FILE.FILE_TYPE; -- Specifications of output file.
this_line VARCHAR2(500); -- Text string written to project output file.
task_id VARCHAR2(40); -- Text string to store value of current task.
task_level VARCHAR2(3);
task_level_id VARCHAR2(40);
this_proj_org VARCHAR2(20); -- Variable for organization name (SI Inc., SI Telcom).
this_project VARCHAR2(40); -- Variable to store project number.


-- Cursor of assignments at the TASK level
CURSOR assign_list IS
SELECT a.proj_id,
a.empl_id,
b.org_id,
b.lvl_no,
b.l1_proj_seg_id,
b.l2_proj_seg_id,
b.l3_proj_seg_id,
b.l4_proj_seg_id,
b.l5_proj_seg_id,
b.l6_proj_seg_id,
b.l7_proj_seg_id,
b.l8_proj_seg_id,
b.l9_proj_seg_id,
b.l10_proj_seg_id,
b.l11_proj_seg_id,
b.l12_proj_seg_id,
b.l13_proj_seg_id,
b.l14_proj_seg_id,
b.l15_proj_seg_id,
c.acct_id
FROM proj_empl a,
proj b,
proj_org_acct c
WHERE b.org_id LIKE '1.01.05%' AND
a.empl_id LIKE '4%%%%' AND
b.lvl_no > 2 AND
b.top_lvl_wrk_frc_fl = 'N' AND
a.proj_id = b.proj_id AND
a.proj_id = c.proj_id AND
b.proj_type_dc != 'INVENTORY' AND
c.acct_id IN (SELECT acct_id
FROM acct_grp_setup
WHERE s_acct_func_dc = 'LABOR' AND
active_fl = 'Y')
ORDER BY a.empl_id, a.proj_id;


-- Cursor of assignments at the Project level
CURSOR assign_list3 IS
SELECT DISTINCT(a.l1_proj_seg_id) as l1_proj_seg_id,
a.l2_proj_seg_id as l2_proj_seg_id,
b.empl_id,
a.org_id
FROM proj a,
proj_empl b
WHERE a.org_id LIKE '1.01.05%' AND
a.top_lvl_wrk_frc_fl = 'Y' AND
a.active_fl = 'Y' AND
a.allow_charges_fl = 'Y' AND
a.l1_proj_seg_id IN (SELECT a.l1_proj_seg_id
FROM proj a
WHERE a.proj_id = b.proj_id AND
a.top_lvl_wrk_frc_fl = 'Y') AND
b.empl_id LIKE '4%%%%' AND
b.empl_id in (SELECT empl_id
FROM empl
WHERE s_empl_status_cd='ACT')
ORDER BY a.l1_proj_seg_id, a.l2_proj_seg_id, b.empl_id;

BEGIN

fileID := UTL_FILE.FOPEN('C:\una_in', 'assn_SETS.lst', 'W');

FOR this_employee IN assign_list LOOP

task_id := NULL;

this_project := this_employee.l1_proj_seg_id || '.' || this_employee.l2_proj_seg_id;

this_proj_org := 'SI_INC.';

IF this_employee.lvl_no >= 3 THEN
task_id := this_employee.l3_proj_seg_id;
IF this_employee.lvl_no >= 4 THEN
task_id := task_id || '.' || this_employee.l4_proj_seg_id;
IF this_employee.lvl_no >= 5 THEN
task_id := task_id || '.' || this_employee.l5_proj_seg_id;
IF this_employee.lvl_no >= 6 THEN
task_id := task_id || '.' || this_employee.l6_proj_seg_id;
IF this_employee.lvl_no >= 7 THEN
task_id := task_id || '.' || this_employee.l7_proj_seg_id;
IF this_employee.lvl_no >= 8 THEN
task_id := task_id || '.' || this_employee.l8_proj_seg_id;
IF this_employee.lvl_no >= 9 THEN
task_id := task_id || '.' || this_employee.l9_proj_seg_id;
IF this_employee.lvl_no >= 10 THEN
task_id := task_id || '.' || this_employee.l10_proj_seg_id;
IF this_employee.lvl_no >= 11 THEN
task_id := task_id || '.' || this_employee.l11_proj_seg_id;
IF this_employee.lvl_no >= 12 THEN
task_id := task_id || '.' || this_employee.l12_proj_seg_id;
IF this_employee.lvl_no >= 13 THEN
task_id := task_id || '.' || this_employee.l13_proj_seg_id;
IF this_employee.lvl_no >= 14 THEN
task_id := task_id || '.' || this_employee.l14_proj_seg_id;
IF this_employee.lvl_no >= 15 THEN
task_id := task_id || '.' || this_employee.l15_proj_seg_id;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;

this_line := '&quot;' || this_proj_org ||
'&quot;,&quot;' || this_project ||
'&quot;,&quot;' || task_id ||
' ' || this_employee.acct_id ||
'&quot;,&quot;' || this_employee.empl_id || '&quot;';

UTL_FILE.PUT_LINE(fileID, this_line);

END LOOP;



FOR this_person IN assign_list3 LOOP

this_proj_org := 'SI_INC.';

this_line := '&quot;' || this_proj_org ||
'&quot;,&quot;' || this_person.l1_proj_seg_id || '.' || this_person.l2_proj_seg_id ||
'&quot;,,&quot;' || this_person.empl_id || '&quot;';

UTL_FILE.PUT_LINE(fileID, this_line);

END LOOP;

UTL_FILE.FCLOSE(fileID);

END;

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


END UNANET_EXPORT;
/
SHOW ERRORS
/

 

[Guest_imported]

I'd start with putting the hard-coded employee Id's in a table and using a sub-select from that table for the in clause, this would minimize some of the maintenence , this probably could be extended to some of the other hard coded data

 

[afelya]

Thank you...