Inconsistencies between RPT and SQL*Forms
Date: 1997/03/03
Message-ID: <5fev1e$c8s_at_knot.queensu.ca>
Hello:
Warning: This posting is rather long. But it needs to be, because I'm having one heck of a problem that I'd like to find a solution for. :-(
Basically, using identical code segments and algorithms, I get one result using RPT report writer (ie. the correct results), and inconsistent or inaccurate results using SQL*Forms 3.0 (ie. no data, or missing data). This is driving me nuts, and holding up some query screens I had promised to have ready.
As part of an absence management system I've been developing, I am creating query screens and reports to track employee absences.
I wanted a way to restrict absence data to only those who are authorized to see them (ie. supervisory staff). To do so, I took advantage of some information in our payroll system (a table containing job descriptions, with a "Reports To" field) to ensure this. Here's a brief description:
Financial Services
Manager
/ \
Payroll Accounting
Supervisor Supervisor
/ \ / \
PClerk 1 PClerk 2 AClerk 1 AClerk 2
In my example, the Financial Services manager can see all staff members in the heirarchy. But the Payroll Supervisor can only see the PClerk 1 & 2 employees, while the Accounting Supervisor can only see the AClerk 1 & 2 employees.
I accomplish this, by populating a auth_table containing a list of positions that a given "report runner" (whoever they happen to be in the heirarchical model) can see. After this table is populated, the appropriate report or query screen is executed.
Here is the code I am using to populate the auth_table (you can skip by it if you aren't interested, the code works great):
- begin code snippet ---
/* Create the table of jobs the "report runner" is allowed to see. */ create or replace table fl_absence_reportauth_temp
(job_code varchar2(9),
location_code varchar2(8),
establishment_code varchar2(8));
/* Insert the "report runner's" info -- he can see his *own* data :-) */
/* The "1" passed parameter is his employee_id #. */
insert into fl_absence_reportauth_temp
select lpad(&1,9), null, null
from dual;
/* Level 1: People who report directly to the "report runner" */
insert into fl_absence_reportauth_temp
select pos.job_code, pos.location_code, pos.establishment_code
from ec_employee_positions pos
where pos.employee_id = &1
and pos.absn_group_code is not null
and nvl(pos.position_end_date,to_char(sysdate,'YYMMDD')) >=
to_char(sysdate,'YYMMDD')
and pos.record_status = 'A';
commit;
/* This is a recursive PL/SQL procedure that then fills the table */ /* with the next n levels, ie. any job which reports to any job */ /* already in the table. :-) */declare
procedure build_reportauth_table(start_count integer) is reportauth_size integer;
begin
select count(*)
into reportauth_size
from fl_absence_reportauth_temp;
if (reportauth_size <> start_count) then
insert into fl_absence_reportauth_temp
select opc.job_code, opc.location_code, opc.establishment_code
from ec_positions opc, fl_absence_reportauth_temp ath
where opc.reporting_job_code = ath.job_code
and opc.reporting_location_code = ath.location_code
and opc.reporting_establishment_code = ath.establishment_code
and 1 not in (select 1
from fl_absence_reportauth_temp tst
where tst.job_code = opc.job_code
and tst.location_code = opc.location_code
and tst.establishment_code =
opc.establishment_code)
and nvl(opc.position_term_date,to_char(sysdate,'YYMMDD')) >=
to_char(sysdate,'YYMMDD')
and opc.record_status = 'A';
commit;
build_reportauth_table(reportauth_size);
end if;
end;
begin
build_reportauth_table(0);
end;
- end code snippet --
Are you still with me? Ah, you are *most* patient. :-)
When running the above code segment, I will get a table containing the following (example) data:
JOB_CODE LOCATION ESTABLIS
--------- -------- --------
10263 // ID # for "report runner"
MANAGER 01 FINANCE // The Financial Services manager
SUPERVSR 01 PAYROLL // Supervisor of Payroll
SUPERVSR 01 ACCNTNG // Supervisor of Accounting
PCLERK 01 PAYROLL // Payroll clerical staff
ACLERK 01 ACCNTNG // Accounting clerical staff
Using this data, my reports can then present absence data for any employees having either (a) an employee # that matchs the record with no location or establishment code, or (b) a job, location, and establishment code that matches any in the auth_table. Pretty nifty eh? :-)
The following code, taken from an RPT report, will do just that:
- begin code snippet ---
select pos.employee_id,
rtrim(emp.surname)||', '||rtrim(emp.first_name)||' '||
substr(emp.second_name,1,1)||decode(emp.second_name,NULL,'','.'),
job.description_text
into employee_id, name, job_description
from ec_employee_positions pos, ec_employee emp, ec_jobs job,
fl_absence_reportauth_temp ath
where pos.employee_id = emp.employee_id
and pos.absn_group_code is not null
and pos.job_code = ath.job_code
and pos.location_code = ath.location_code
and pos.establishment_code = ath.establishment_code
and nvl(pos.position_end_date,to_char(sysdate,'YYMMDD')) >=
to_char(sysdate,'YYMMDD')
and pos.record_status = 'A'
and pos.job_code = job.job_code
order by emp.surname, emp.first_name, job.description_text;
- end code snippet ---
This works *great*! All my RPT reports produce the correct results. However, plugging this code into the MASTER block of an SQL*Forms 3.0 application, as follows, doesn't give consistant results:
- begin code segment --- DEFINE BLOCK
NAME = MASTER
TABLE = FL_ABSENCE_VIEW
IN_MENU = OFF
ROWS_DISPLAYED = 1
ROWS_BUFFERED = 99
BASE_LINE = 1
LINES_PER_ROW = 0
ARRAY_SIZE = 0
ORDERING = <<<
where (employee_id, location_code) in
(select pos.employee_id, pos.location_code
from ec_employee_positions pos,
fl_absence_reportauth_temp ath
where pos.location_code = ath.location_code
and pos.job_code = ath.job_code
and pos.establishment_code =
ath.establishment_code
and pos.position_start_date <=
to_char(sysdate,'YYMMDD')
and nvl(pos.position_end_date,
to_char(sysdate,'YYMMDD')) >=
to_char((sysdate-31),'YYMMDD')
and pos.record_status = 'A')
and upper(name) like upper(nvl(:MASTER.NAME_DISPLAY,'%'))
order by name
>>>
- end code segment ---
What's strange about this is, it's so inconsistent! With some "report runners", it works great. With others, it doesn't! In some cases, removing the "order by name" makes the difference (which is completely illogical).
Are there subtle differences between RPT and SQL*Forms implementations of SQL? Any ideas why this is working fine with my RPT files and not with my Forms? They are both using the same authorization information in the same table...
I'm using RPT 1.1.10.3.2. SQL*FORMS 3.0.16.12.0 (with Oracle Toolkit 1.00.20.00.00 and PL/SQL 1.00.35.01.00) Upgrading these are not an option (we are at the mercy of using "supported" released -- supported, that is, by the vendor who writes our business applications.
Thanks in advance...
---------------< LINUX: The choice of a GNU generation. >---------------
Steve Frampton http://qlink.queensu.ca/~3srfWork: <frampton_at_mail.flarc.edu.on.ca> School: <3srf_at_qlink.queensu.ca> --< NOTICE! I do not accept unsolicited commercial e-mail messages! >-- Received on Mon Mar 03 1997 - 00:00:00 CET