Hi guys,
Have been playing around with crystal reports and trying to create custom reports for Employee definition and Employee Assigned Rights,
So far the Employee definition seems to work apart from the termination_status table which does not change to active/inactive depending on the current status of the employee as for the Rights report when I try to connect to the db it gives an error that cannot change name to integer, below are the stored procedure and image of the error, anyone with pointers?
Employee Definition Report SP
Employee Privilege Report SP
Have been playing around with crystal reports and trying to create custom reports for Employee definition and Employee Assigned Rights,
So far the Employee definition seems to work apart from the termination_status table which does not change to active/inactive depending on the current status of the employee as for the Rights report when I try to connect to the db it gives an error that cannot change name to integer, below are the stored procedure and image of the error, anyone with pointers?
Employee Definition Report SP
SQL:
IF object_id('sp_emp_def_rpt') IS NULL THEN
create procedure "micros"."sp_emp_def_rpt"()
BEGIN
message 'Dummy procedure';
END;
grant execute on sp_emp_def_rpt to CAEDC_GROUP;
grant execute on sp_emp_def_rpt to DEFINITION_GROUP;
grant execute on sp_emp_def_rpt to REPORTS_GROUP;
grant execute on sp_emp_def_rpt to TRANSACTION_GROUP;
grant execute on sp_emp_def_rpt to MICROS_GROUP;
END IF;
ALTER PROCEDURE "micros"."sp_emp_def_rpt"
(
IN p_parameters varchar(199) // This parameter should begin with 'AND'
)
RESULT (
EMP_OBJ_NUM OBJ_NUM,
EMP_PAYROLL_ID PAYROLL_ID,
EMP_LAST_NAME VARCHAR(17),
EMP_FIRST_NAME NAME8,
EMP_ID EMPL_ID,
EMP_CLASS_NAME OBJ_NAME,
hire_status hire_status,
inactive_from_date DATETIME,
inactive_to_date DATETIME,
termination_status termination_status,
termination_date DATETIME
)
BEGIN
DECLARE LOCAL TEMPORARY TABLE tmp_info
(
EMP_OBJ_NUM OBJ_NUM,
EMP_PAYROLL_ID PAYROLL_ID,
EMP_LAST_NAME VARCHAR(17),
EMP_FIRST_NAME NAME8,
EMP_ID EMPL_ID,
EMP_CLASS_NAME OBJ_NAME,
hire_status HIRE_STATUS,
inactive_from_date DATETIME,
inactive_to_date DATETIME,
termination_status TERMINATION_STATUS,
termination_date DATETIME
)
ON COMMIT PRESERVE ROWS;
DECLARE @parm1 varchar(200);
DECLARE @base_query varchar(5000);
DECLARE @lookfor varchar(200);
DECLARE @condition varchar(200);
DECLARE @minorAge INT;
SET @minorAge = f_GetMinorAge( );
SET @parm1 = p_parameters || ' ';
SET @base_query = '
INSERT INTO tmp_info (
EMP_OBJ_NUM,
EMP_PAYROLL_ID,
EMP_LAST_NAME,
EMP_FIRST_NAME,
EMP_ID,
EMP_CLASS_NAME,
hire_status,
inactive_from_date,
inactive_to_date,
termination_status,
termination_date
)
SELECT
EMP_DEF.Obj_Num,
EMP_DEF.Payroll_id,
f_GetLastNameWithMinor(EMP_DEF.emp_seq, @minorAge) AS EMP_LAST_NAME,//EMP_DEF.Last_Name,
EMP_DEF.First_name,
EMP_DEF.id,
EMP_CLASS_DEF.Name,
hire_status,
inactive_from_date,
inactive_to_date,
termination_status,
termination_date
FROM
micros.vsc_emp_def AS EMP_DEF
,micros.vsc_emp_class_def AS EMP_CLASS_DEF
WHERE
EMP_DEF.emp_class_seq = EMP_CLASS_DEF.emp_class_seq
';
// ------------------------------------------------------
// Determine any additional condition that have been passed in as parameters.
// ------------------------------------------------------
call spem_extract_code ('OBJ_NUM',@parm1, @condition);
IF (length(@condition) > 6 ) THEN
SET @base_query = @base_query || @condition || ' ';
MESSAGE 'EMP OBJ_NUM:' || @condition;
END IF;
SET @base_query = @base_query || '
Order By EMP_DEF.Obj_Num';
// ------------------------------------------------------
// Execute the query after any agumentation has taken place.
// ------------------------------------------------------
MESSAGE @base_query;
MESSAGE '----------------------- Start :' || CURRENT TIMESTAMP || '---------------------------';
EXECUTE IMMEDIATE @base_query;
MESSAGE '----------------------- End :' || CURRENT TIMESTAMP || '---------------------------';
// ------------------------------------------------------
// Get the results from the temporary table.
// ------------------------------------------------------
SELECT
EMP_OBJ_NUM,
EMP_PAYROLL_ID,
EMP_LAST_NAME,
EMP_FIRST_NAME,
EMP_ID,
EMP_CLASS_NAME,
hire_status,
inactive_from_date,
inactive_to_date,
termination_status,
termination_date
FROM
tmp_info
;
ENDEmployee Privilege Report SP
SQL:
IF object_id('sp_emp_priv_rpt') IS NULL THEN
create procedure "micros"."sp_emp_priv_rpt"()
BEGIN
message 'Dummy procedure';
END;
grant execute on sp_emp_priv_rpt to CAEDC_GROUP;
grant execute on sp_emp_priv_rpt to DEFINITION_GROUP;
grant execute on sp_emp_priv_rpt to REPORTS_GROUP;
grant execute on sp_emp_priv_rpt to TRANSACTION_GROUP;
grant execute on sp_emp_priv_rpt to MICROS_GROUP;
END IF;
ALTER PROCEDURE "micros"."sp_emp_priv_rpt" ()
RESULT( emp_class_seq SEQ_NUM,
obj_num OBJ_NUM,
name OBJ_NAME,
proc_priv_lvl PRIV_LEVEL,
su_proc_priv_lvl PRIV_LEVEL,
aseq_priv_lvl PRIV_LEVEL,
mi_priv_lvl PRIV_LEVEL,
dsc_priv_lvl PRIV_LEVEL,
svc_priv_lvl PRIV_LEVEL,
tmed_priv_lvl PRIV_LEVEL,
auth_clk_in_priv_lvl PRIV_LEVEL,
clk_in_priv_lvl PRIV_LEVEL)
BEGIN
DECLARE LOCAL TEMPORARY TABLE data
(emp_class_seq SEQ_NUM,
obj_num OBJ_NUM,
name OBJ_NAME,
proc_priv_lvl PRIV_LEVEL,
su_proc_priv_lvl PRIV_LEVEL,
aseq_priv_lvl PRIV_LEVEL,
mi_priv_lvl PRIV_LEVEL,
dsc_priv_lvl PRIV_LEVEL,
svc_priv_lvl PRIV_LEVEL,
tmed_priv_lvl PRIV_LEVEL,
auth_clk_in_priv_lvl PRIV_LEVEL,
clk_in_priv_lvl PRIV_LEVEL);
INSERT INTO data
SELECT emp_class_seq,name,obj_num,proc_priv_lvl,su_proc_priv_lvl,aseq_priv_lvl,mi_priv_lvl,dsc_priv_lvl,svc_priv_lvl,tmed_priv_lvl,auth_clk_in_priv_lvl,clk_in_priv_lvl
FROM micros.emp_class_def;
SELECT * FROM data;
END