Financial Aid Example of Core view and Satellite views
Example of modifying ODS view AR_AWARD_BY_PERSON
CREATE OR REPLACE FORCE VIEW AR_AWARD_BY_PERSON
(
PERSON_UID,
AID_YEAR,
AID_YEAR_DESC,
AID_PERIOD,
AID_PERIOD_DESC,
ACADEMIC_PERIOD,
ACADEMIC_PERIOD_DESC,
SUB_ACADEMIC_PERIOD,
SUB_ACADEMIC_PERIOD_DESC,
FUND,
FUND_TITLE,
<snip>
CURRENT_DATE
)
AS SELECT
RPRATRM_PIDM,
RPRATRM_AIDY_CODE,
SUBSTR(GOKODSF.F_GET_DESC(RPRATRM_AIDY_CODE, 'ROBINST','ROBINST_AIDY_DESC', NULL, 'ROBINST_AIDY_CODE'),1,30),
DECODE(' ', ' ', ' '),
DECODE(' ', ' ', ' '),
RPRATRM_TERM_CODE,
SUBSTR(GOKODSF.F_GET_DESC(RPRATRM_TERM_CODE, 'STVTERM'),1,30),
DECODE(' ', ' ', ' '),
DECODE(' ', ' ', ' '),
RPRATRM_FUND_CODE,
SUBSTR(GOKODSF.F_GET_DESC(RPRATRM_FUND_CODE, 'RFRBASE','RFRBASE_FUND_TITLE', NULL,
<snip>
SYSDATE
FROM RTVAWST, RPRAWRD, RPRATRM
WHERE RPRATRM_PIDM = RPRAWRD_PIDM
AND RPRATRM_AIDY_CODE = RPRAWRD_AIDY_CODE
AND RPRATRM_FUND_CODE = RPRAWRD_FUND_CODE
AND RPRAWRD_AWST_CODE = RTVAWST_CODE;
--
SHOW ERRORS VIEW AR_AWARD_BY_PERSON;
grant select on ar_award_by_person to public;
drop public synonym ar_award_by_person;
create public synonym ar_award_by_person for ar_award_by_person;
REM CLP Local version
CREATE OR REPLACE FORCE VIEW ZR_AWARD_BY_PERSON
(
PERSON_UID,
AID_YEAR,
AID_YEAR_DESC,
AID_PERIOD,
AID_PERIOD_DESC,
ACADEMIC_PERIOD,
ACADEMIC_PERIOD_DESC,
SUB_ACADEMIC_PERIOD,
SUB_ACADEMIC_PERIOD_DESC,
FUND,
FUND_TITLE,
<snip>
CURRENT_DATE
)
AS SELECT
RPRATRM_PIDM,
RPRATRM_AIDY_CODE,
SUBSTR(GOKODSF.F_GET_DESC(RPRATRM_AIDY_CODE, 'ROBINST','ROBINST_AIDY_DESC', NULL, 'ROBINST_AIDY_CODE'),1,30),
DECODE(' ', ' ', ' '),
DECODE(' ', ' ', ' '),
RPRATRM_TERM_CODE,
SUBSTR(GOKODSF.F_GET_DESC(RPRATRM_TERM_CODE, 'STVTERM'),1,30),
DECODE(' ', ' ', ' '),
DECODE(' ', ' ', ' '),
RPRATRM_FUND_CODE,
SUBSTR(GOKODSF.F_GET_DESC(RPRATRM_FUND_CODE, 'RFRBASE','RFRBASE_FUND_TITLE', NULL, 'RFRBASE_FUND_CODE'),1,30),
<snip>
SYSDATE
FROM RTVAWST, RPRAWRD, RPRATRM
WHERE RPRATRM_PIDM = RPRAWRD_PIDM
AND RPRATRM_AIDY_CODE = RPRAWRD_AIDY_CODE
AND RPRATRM_FUND_CODE = RPRAWRD_FUND_CODE
AND RPRAWRD_AWST_CODE = RTVAWST_CODE
AND (SUBSTR(FW_BANNER_SECURITY('ZRADHOC'),1,1) = 'Y'
OR
EXISTS (SELECT 'Y'
FROM SESSION_PRIVS
WHERE PRIVILEGE = 'SELECT ANY TABLE')
);
--
SHOW ERRORS VIEW ZR_AWARD_BY_PERSON;
grant select on zr_award_by_person to public;
drop public synonym zr_award_by_person;
create public synonym zr_award_by_person for zr_award_by_person;
FW_BANNER_SECURITY function code
rem fw_banner_security.sql
connect bansecr/&bansecr_passwd
SET SCAN OFF
-- AUDIT TRAIL: 3.0
-- 1. WFP 03/07/1999
-- This function will check BANNER forms/process security. It will
-- return a 'Y' if the user has security for a passed object, or
-- if security is turned off in BANNER.
--
-- AUDIT TRAIL END
CREATE OR REPLACE FUNCTION fw_banner_security
(obj_code VARCHAR2)
RETURN VARCHAR2
AS
--
-- FILE NAME..: fw_banner_security.sql
-- OBJECT_NAME: FW_BANNER_SECURITY
-- PRODUCT....: BANSECR
-- USAGE...... : Function to retrieve BANNER security switch.
--
-- DESCRIPTION:
--
-- This function will check BANNER forms/process security. It will
-- return a 'Y' if the user has security for a passed object, or
-- if security is turned off in BANNER.
--
-- DESCRIPTION END
--
return_field VARCHAR2(1) := NULL;
security_on VARCHAR2(1) := NULL;
CURSOR iprf_cursor IS
SELECT gubiprf_security_enabled_ind
FROM gubiprf
WHERE gubiprf_inst_key = 'INST';
CURSOR uobj_cursor IS
SELECT 'Y'
FROM guruobj
WHERE guruobj_userid = user
AND guruobj_object = obj_code;
CURSOR ucls_cursor IS
SELECT 'Y'
FROM gurucls,
guruobj
WHERE gurucls_userid = user
AND guruobj_object = obj_code
AND guruobj_userid = gurucls_class_code;
BEGIN
OPEN iprf_cursor;
FETCH iprf_cursor
INTO security_on;
IF iprf_cursor%NOTFOUND
THEN security_on := 'N';
END IF;
CLOSE iprf_cursor;
IF security_on = 'N'
THEN RETURN 'Y';
ELSE GOTO check_uobj;
END IF;
<check_uobj>
OPEN uobj_cursor;
FETCH uobj_cursor
INTO security_on;
IF uobj_cursor%NOTFOUND
THEN security_on := 'N';
END IF;
CLOSE uobj_cursor;
IF security_on = 'Y'
THEN RETURN 'Y';
ELSE GOTO check_ucls;
END IF;
<check_ucls>
OPEN ucls_cursor;
FETCH ucls_cursor
INTO security_on;
IF ucls_cursor%NOTFOUND
THEN security_on := 'N';
END IF;
CLOSE ucls_cursor;
IF security_on = 'Y'
THEN RETURN 'Y';
END IF;
RETURN 'N';
END;
/
SHOW ERRORS
SET SCAN ON
DROP PUBLIC SYNONYM fw_banner_security;
CREATE PUBLIC SYNONYM fw_banner_security FOR fw_banner_security;
REM *** BEGINNING OF GURMDBP MODS ***
REM GRANT EXECUTE ON FW_BANNER_SECURITY TO PUBLIC;
WHENEVER SQLERROR CONTINUE
start gurgrtb FW_BANNER_SECURITY
WHENEVER SQLERROR EXIT ROLLBACK
REM *** END OF GURMDBP MODS ***
GRANT EXECUTE ON fw_banner_security TO BANINST1 WITH GRANT OPTION;
Add ZRADHOC Object to Objects using GSASECR.
Create Financial Aid Adhoc user Class that contains ZRADHOC as the only object and assign users to this class.