create or replace package app is
SUBTYPE st_maxvarchar2 IS VARCHAR2(32767) ;
PROCEDURE data(
p_callback IN VARCHAR2,
p_net_id IN VARCHAR2
);
PROCEDURE get_org_tree(p_callback IN VARCHAR2,
p_node IN VARCHAR2 DEFAULT NULL,
p_netid IN VARCHAR2 DEFAULT NULL);
end app;
/
CREATE OR REPLACE PACKAGE BODY app IS
-- SUBTYPE st_maxvarchar2 IS VARCHAR2(32767) ;
v_result st_maxvarchar2:='';
PROCEDURE data(
p_callback IN VARCHAR2,
p_net_id IN VARCHAR2
)
IS
/* -------------------------------------------------------------------------------------------
Procedure: data
Author: Brian Griffin
Created: June 7 2012
Purpose: Given that the application is now ready, return the user's login information
Parameters:
p_callback
p_net_id
Example usage:
Copyright UC Riverside, 2012
------------------------------------------------------------------------------------------- */
-- EACS Variables:
v_EACS_URL VARCHAR2(100) := '';
v_schema VARCHAR2(10) := '';
k_APP_ID CONSTANT VARCHAR2(10) := '129'; -- FBO Reporting System
k_EACSDEV CONSTANT VARCHAR2(100) := 'http://eacsdev.ucr.edu/';
k_EACSTEST CONSTANT VARCHAR2(190) := 'http://iastest.ucr.edu/';
k_EACSPROD CONSTANT VARCHAR2(190) := 'http://eacs.ucr.edu/';
v_EACS_Acct_Struct st_maxvarchar2 := '';
v_netid VARCHAR2(100) := '';
v_first VARCHAR2(100) := '';
v_last VARCHAR2(100) := '';
-- XML Variables:
v_XML_Req VARCHAR2(2000); -- The XML request
v_XML_data xmltype;
v_xml_value st_maxvarchar2 := '';
v_xml_user_role st_maxvarchar2 := '';
v_pos INTEGER :=1;
BEGIN
-- ALlow for a switch
IF p_net_id = 'griff' THEN
v_netid := 'griff';
ELSE
v_netid := p_net_id;
END IF;
-- Determine the environment we are in
SELECT CASE
WHEN instr(uu.username, 'DEV') > 0 THEN '_dev'
WHEN instr(uu.username, 'TEST') > 0 THEN '_test'
ELSE ''
END environ_url_suffix
INTO v_schema
FROM user_users uu;
-- Construct the URL to call
v_EACS_URL := '/eacs' || v_schema || '/EACS_CNC_UTIL.Check_user?v_user_netid=' || v_netid || '&v_app_id=' || k_APP_ID;
IF v_schema = '_dev' THEN
v_EACS_URL := k_EACSDEV || v_EACS_URL;
ELSIF v_schema = '_test' THEN
v_EACS_URL := k_EACSTEST || v_EACS_URL;
ELSE
v_EACS_URL := k_EACSPROD || v_EACS_URL;
END if;
-- Using a web service, request the data
v_XML_Req := utl_http.request(v_EACS_URL);
-- and convert it into an Oracle XML object that can be parsed.
v_XML_data := xmltype.createxml(v_XML_Req);
-- Pull out the first occurance of the USER ROLE
SELECT EXTRACTVALUE(v_XML_data, '/USER_XML/SAALIST/SAA_INFO[1]/USER_ROLE')
INTO v_xml_user_role
FROM dual;
-- Pull out the first occurance of the EACS role
SELECT EXTRACTVALUE(v_XML_data, '/USER_XML/SAALIST/SAA_INFO[1]/ORG_VALUE')
INTO v_xml_value
FROM dual;
-- Since multiple roles maybe assigned to a user, we must scan through them all until NULL are returned
WHILE v_xml_value IS NOT NULL LOOP
v_pos := v_pos + 1;
v_EACS_Acct_Struct := v_EACS_Acct_Struct || '''' || v_xml_value || ''', ';
SELECT EXTRACTVALUE(v_XML_data, '/USER_XML/SAALIST/SAA_INFO[' || v_pos || ']/ORG_VALUE')
INTO v_xml_value
FROM dual;
END LOOP;
v_EACS_Acct_Struct := rtrim(v_EACS_Acct_Struct, ', ');
SELECT u.first, u.last
INTO v_first, v_last
FROM ucr_netid@acctuser_prod10 u
WHERE u.net_id = p_net_id;
/*htp.p(p_callback || '
({
"success": true,
"welcome_message":"Welcome to PIWRS",
"appData": {
"netid":"' || p_net_id || '",
"user_name":"' || v_first || '",
"last_name":"' || v_last || '",
"acct_struct":"'|| v_EACS_Acct_Struct ||'",
"user_role":"' || v_xml_user_role || '",
"fiscal_year":"2012",
"accounting_period":"12",
"app_name":"PIWRS",
"app_title":"Financial Budget Office Reporting System",
"app_version":"0.1"
}
})
');*/
htp.p(p_callback || '({
"success": true,
"welcome_message":"Welcome to PIWRS",
"appData": {
"netid" : "paulinel",
"fiscal_year" : "2014",
"cal_year" : "2013",
"accounting_period" : "6",
"window_closed" : "Y",
"user_name" : "Juan",
"last_name" : "Gonzales",
"role_name" : "admin",
"switch_user" : true,
"acct_struct" : "ORG??",
"num_funds_to_certify": 0,
"num_funds_collab" : 0,
"viewPiList" : false
}
})
');
EXCEPTION WHEN OTHERS THEN
htp.p(p_callback || '({
"success": false,
"message": "' || SQLERRM || '",
"appData": {
"netid" : "' || p_net_id || '",
"user_name" : "' || v_first || '",
"last_name" : "' || v_last || '",
"acct_struct" : "'|| v_EACS_Acct_Struct ||'",
"user_role" : "' || v_xml_user_role || '",
"fiscal_year" : "2012",
"accounting_period": "12",
"app_name" : "PIWRS",
"app_title" : "PI Web Reporting System",
"app_version":"0.1"
}
})
');
END data;
PROCEDURE get_org_tree(p_callback IN VARCHAR2,
p_node IN VARCHAR2 DEFAULT NULL,
p_netid IN VARCHAR2 DEFAULT NULL) IS
v_response st_maxvarchar2 := '';
BEGIN
-- At the root level of the Nav tree, we'll need to get the user's EACS accountability structure
IF p_node = 'root' THEN
v_response := p_callback || '({
"text" : ".",
"children": [
{
"text" : "Welcome",
"iconCls" : "task",
"leaf" : "true",
"id" : "welc",
"report_type": "dept",
"qtip" : "Click here to view PI Reports",
"comments" : "update",
"expanded" : "false"
},
{
"text" : "View PI Reports",
"iconCls" : "task",
"leaf" : "true",
"id" : "vpir",
"report_type": "dept",
"qtip" : "Click here to view PI Reports",
"comments" : "update",
"expanded" : "false"
},
{
"text" : "Annual Certifications Due (0)",
"iconCls" : "task",
"leaf" : "true",
"id" : "acd",
"report_type": "dept",
"qtip" : "Click to view Annual Certs Due",
"comments" : "update",
"expanded" : "false"
},
';
IF p_netid = 'newAlias' THEN
v_response := v_response || '
{
"text" : "Annual Certifications Completed",
"iconCls" : "task",
"leaf" : "true",
"id" : "acc",
"report_type": "dept",
"qtip" : "Click to view Annual Certs Completed",
"comments" : "update",
"expanded" : "false"
},
{
"text" : "View Reports",
"iconCls" : "task",
"leaf" : "true",
"id" : "vpa",
"report_type": "dept",
"qtip" : "Click to view Admin Reports",
"comments" : "update",
"expanded" : "false"
},
';
END IF;
v_response := v_response || '
{
"text" : "Shared Contract and Grant Funds",
"iconCls" : "task",
"leaf" : "true",
"id" : "D01299",
"report_type": "cng",
"qtip" : "Click to view Shared C&G Funds",
"comments" : "update",
"expanded" : "false"
},
{
"text" : "Preferences",
"iconCls" : "task",
"leaf" : "true",
"id" : "pref",
"report_type": "dept",
"qtip" : "Click to view Preferences",
"comments" : "update",
"expanded" : "false"
},
{
"text" : "Understanding PIWRS",
"iconCls" : "task",
"leaf" : "true",
"id" : "help",
"report_type": "dept",
"qtip" : "Click to view PIWRS help info",
"comments" : "update",
"expanded" : "false"
},
{
"text" : "Exit Application",
"iconCls" : "task",
"leaf" : "true",
"id" : "exit",
"report_type": "dept",
"qtip" : "Click to exit application",
"comments" : "update",
"expanded" : "false"
}
],
"success": true
})
';
/*ELSIF SUBSTR(p_node, 1, 3) = 'ORG' THEN
v_response := p_callback || '(
{
"text":".",
"children": [
{
"text": "D01050 - Nematology",
"iconCls":"task-folder",
"id": "D01050",
"report_type": "dept",
"qtip": "Nematology",
"comments": "update",
"expanded": "false"},{
"text": "D01209 - Dining Services",
"iconCls":"task-folder",
"id": "D01209",
"report_type": "dept",
"qtip": "Dining Services",
"comments": "update",
"expanded": "false"},{
"text": "DIV171 - Academic Personnel",
"iconCls":"task-folder",
"id": "DIV171",
"report_type": "div",
"qtip": "Academic Personnel",
"comments": "update",
"expanded": "false"},{
"text": "ORG21 - Computing & Communications",
"iconCls":"task-folder",
"id": "ORG21",
"report_type": "org",
"qtip": "Computing & Communications",
"comments": "update",
"expanded": "false"},{
"text": "ORG39 - Finance & Business Operations",
"iconCls":"task-folder",
"id": "ORG39",
"report_type": "org",
"qtip": "Finance & Business Operations",
"comments": "update",
"expanded": "false"
}
],
"success":true
})
';*/
ELSE
htp.p(p_callback || '( {
"success":false,
"message":"' || translate(dbms_utility.format_error_stack || ' ' || dbms_utility.format_error_backtrace, '"', '`') || ' ",
"v_response":"' || v_response || '"
} )') ;
RETURN;
END IF;
htp.p(v_response);
EXCEPTION WHEN OTHERS THEN
--v_result := login.display_error_info('griff', DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'text');
htp.p(p_callback || '( {
"success":false,
"message":"' || v_result || '"
} )') ;
RETURN;
END get_org_tree;
BEGIN
NULL;
END app;
/