create or replace package fbors_projections is /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Author : Brian G / Larry W Created : 7/25/2012 Purpose : To manage FBORS Salary (Salaried / Hourly) projections UC Riverside ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ TYPE tv_data IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER; FUNCTION audit_transaction( p_base_table IN VARCHAR2, p_base_seq_id IN VARCHAR2, p_audit_seq_id IN VARCHAR2, p_net_id IN VARCHAR2, p_operation IN VARCHAR2 ) RETURN VARCHAR2; PROCEDURE check_excessive_fte( p_employee_id IN VARCHAR2, p_result OUT VARCHAR2 ); PROCEDURE view_data( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'se', sort IN VARCHAR2 DEFAULT NULL ); PROCEDURE view_salary_emp_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'se' ); PROCEDURE view_hourly_emp_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'he' ); PROCEDURE view_student_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'hs' ); PROCEDURE view_nonsal_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'ns' ); PROCEDURE view_budget_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'bud' ); PROCEDURE view_benefit_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'ben' ); PROCEDURE save_data( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2, p_grid_data IN VARCHAR2 ); PROCEDURE save_salary_emp_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'se', p_grid_data IN VARCHAR2 ); PROCEDURE save_hourly_emp_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'he', p_grid_data IN VARCHAR2 ); PROCEDURE save_student_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'hs', p_grid_data IN VARCHAR2 ); PROCEDURE save_nonsal_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'ns', p_grid_data IN VARCHAR2 ); PROCEDURE save_budget_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'bud', p_grid_data IN VARCHAR2 ); PROCEDURE save_benefit_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'ben', p_grid_data IN VARCHAR2 ); PROCEDURE delete_data( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2, p_grid_data IN VARCHAR2 ); /*PROCEDURE delete_employee_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'se', p_grid_data IN VARCHAR2 );*/ PROCEDURE delete_budget_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'bud', p_grid_data IN VARCHAR2 ); END fbors_projections; / create or replace package body fbors_projections IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Global Variables and constants ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ SUBTYPE st_maxvarchar2 IS VARCHAR2(32767); v_sql st_maxvarchar2 := NULL; v_sql_projs st_maxvarchar2 := NULL; v_result st_maxvarchar2 := NULL; v_return st_maxvarchar2 := NULL; v_grid_data st_maxvarchar2 := NULL; v_save_message st_maxvarchar2 := NULL; v_error_message st_maxvarchar2 := NULL; v_url st_maxvarchar2 := NULL; v_fiscal_year VARCHAR2(4) := ''; v_accounting_period VARCHAR2(2) := ''; v_current_year VARCHAR2(4) := to_char(SYSDATE, 'YYYY'); v_start_year VARCHAR2(4) := ''; v_end_year VARCHAR2(4) := ''; v_max_closed_date DATE := null; --VARCHAR2(100) := ''; v_start_month VARCHAR2(2) := ''; v_end_month VARCHAR2(2) := ''; v_last_closed_month INTEGER := 0; v_months_between INTEGER := 0; v_sql_rowcount INTEGER := 0; v_sql_count INTEGER := 0; v_seq_nextval INTEGER := 0; v_start_time NUMBER; v_run_time VARCHAR2(100) := ''; -- Activity golden tree information --v_org_code activity_denorm_tree_vw.org_code%TYPE; --v_div_code activity_denorm_tree_vw.div_code%TYPE; --v_dept_code activity_denorm_tree_vw.dept_code%TYPE; v_activity_denorm_tree_values activity_denorm_tree_vw%ROWTYPE; v_app_settings app_settings%ROWTYPE; v_items fbors_funclib.tv_data; -- Example: http:// gk_REQUEST_PROTOCOL CONSTANT st_maxvarchar2 := lower(owa_util.get_cgi_env(param_name => 'REQUEST_PROTOCOL')) || '://'; -- Example: iscotsdev.ucr.edu gk_HTTP_HOST CONSTANT st_maxvarchar2 := lower(owa_util.get_cgi_env(param_name => 'HTTP_HOST')); -- Example: /iscots_dev/ gk_SCRIPT_NAME CONSTANT st_maxvarchar2 := lower(owa_util.get_cgi_env(param_name => 'SCRIPT_NAME')) || '/'; -- Example: /totals_popup.accounting_period gv_PATH_INFO st_maxvarchar2 := lower(owa_util.get_cgi_env(param_name => 'PATH_INFO')); -- Example: http://iscotsdev.ucr.edu/iscots_dev/ gk_APPLICATION_URL CONSTANT st_maxvarchar2 := gk_REQUEST_PROTOCOL || gk_HTTP_HOST || gk_SCRIPT_NAME; FUNCTION action2word(p_mode IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN CASE WHEN p_mode = 'A' THEN ' added ' WHEN p_mode = 'D' THEN ' deleted ' WHEN p_mode = 'M' THEN ' updated ' ELSE '' END; END action2word; FUNCTION audit_transaction( p_base_table IN VARCHAR2, p_base_seq_id IN VARCHAR2, p_audit_seq_id IN VARCHAR2, p_net_id IN VARCHAR2, p_operation IN VARCHAR2 ) RETURN VARCHAR2 IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Function : audit_transaction Author : Larry W, Brian G Created : December 2012 Params : p_base_table = The base table to Audit p_base_seq_id = The base table's sequence to look up p_audit_seq_id = the Audit table's sequence to use p_net_id = who did the UPDATE/DELETE/INSERT p_operation = What the SQL operation was: UPDATE/DELETE/INSERT Purpose : To record changes to the data. The AUDIT table needs to be cloned from the BASE table with the additional fields: audit_id, audit_net_id, audit_datetime, audit_operation taked onto the beginning of the table's columns. Also, each AUDIT table has it's own SEQUENCE. Examples : v_return := audit_transaction('fbors_salary_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'UPDATE'); v_return := audit_transaction('fbors_salary_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'DELETE'); v_return := audit_transaction('fbors_salary_projs_tbl', v_seq_nextval, fbors_projs_audit_seq.nextval, p_net_id, 'INSERT'); UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ v_table_name VARCHAR2(100) := ''; v_audit_table VARCHAR2(100) := ''; v_audit_id INTEGER := 0; BEGIN dbms_application_info.set_action( 'Auditing the ' || p_base_table || ' table @line #' || $$PLSQL_LINE || ' in ' || $$PLSQL_UNIT); -- Get a comma separated list of the base table's columns SELECT LISTAGG(LOWER(c.column_name), ', ') WITHIN GROUP (ORDER BY c.column_id) INTO v_result FROM user_tab_cols c WHERE c.table_name = upper(p_base_table); -- DBMS_OUTPUT.PUT_LINE('v_result = ' || v_result); -- Get the AUDIT table name based on the base table's name v_audit_table := REGEXP_REPLACE( UPPER(p_base_table), '_TBL', '_AUDIT'); -- Record the information into the Audit table using the same fields as the base table, plus the additional Audit fields: audit_id, audit_net_id, audit_datetime, audit_operation v_sql := 'INSERT INTO ' || v_audit_table || '(audit_id, audit_net_id, audit_datetime, audit_operation, ' || v_result || ' ) SELECT :p_audit_seq_id, ''' || p_net_id || ''', SYSDATE, ''' || p_operation || ''', p.* FROM ' || p_base_table || ' p WHERE p.sequence_id = :p_base_seq_id '; -- DBMS_OUTPUT.PUT_LINE('v_sql = ' || v_sql); EXECUTE IMMEDIATE v_sql USING p_audit_seq_id, p_base_seq_id; COMMIT; RETURN 'SUCCESS'; EXCEPTION WHEN OTHERS THEN -- v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'text'); RETURN SQLERRM; END audit_transaction; PROCEDURE check_excessive_fte( p_employee_id IN VARCHAR2, p_result OUT VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Function : check_excessive_fte Author : Larry W, Brian G Created : Yesember 2012 Params : p_employee_id = the current counter p_result = the overall count Purpose : Loop through every month in FY and determine if the given employee's FTE exceeds 1.0 UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ v_month DATE := NULL; v_Sum_FTE NUMBER := 0; BEGIN p_result := '.'; v_month := TO_DATE('07/01/' || v_current_year, 'MM/DD/YYYY'); -- Looop through the year and add up the FTEs recorded in the Projections table per month for the latest projected accounting period FOR i IN 1..12 LOOP SELECT sum(p.mod_fte) INTO v_Sum_FTE FROM fbors_salary_projs_tbl p WHERE p.fiscal_year = v_fiscal_year AND p.employee_id = p_employee_id AND p.accounting_period IN (v_accounting_period, v_accounting_period+1) AND v_month BETWEEN p.mod_begin_date AND p.mod_end_date; IF v_Sum_FTE > 1 THEN p_result := '


Warning: Please note that during the month of ' || trim(to_char(v_month, 'Month')) || ', the total FTE for this employee exceeds 1.0. Please review the projection data for any possible inconsistencies.'; -- p_result := 'In the month of ' || v_month || ' the FTE is ' || v_Sum_FTE; EXIT; ELSE v_month := ADD_MONTHS(v_month, 1); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN p_result := '.'; end check_excessive_fte; PROCEDURE check_activity_in_eacs( p_activity IN VARCHAR2, p_net_id IN VARCHAR2, p_result OUT VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Function : check_activity_in_eacs Author : Larry W, Brian G Created : Yesember 2012 Params : p_net_id = the user p_activity = activity p_result = NULL means no match was found and therefore issue a WARNING Purpose : Determine if the activity is outside their EACS accountability structure UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ BEGIN WITH activity_denorm_tree_wvw AS ( SELECT a.org_code, a.div_code, a.dept_code FROM activity_denorm_tree_vw a WHERE a.activity = p_activity ) SELECT DISTINCT e.org_value INTO p_result FROM fbors_eacs_session_tbl e, activity_denorm_tree_wvw a WHERE e.net_id = p_net_id AND ( a.org_code IN e.org_value OR a.div_code IN e.org_value OR a.dept_code IN e.org_value); p_result := NULL; EXCEPTION WHEN OTHERS THEN p_result := '


Warning: Please note that you have added an activity, ' || p_activity || ', that is outside your EACS accountability structure and will therefore not show up on this particular projection grid when you close this informational window. Please review the projection data for any possible inconsistencies.'; END check_activity_in_eacs; PROCEDURE get_actv_denorm_tree_values( p_activity IN VARCHAR2, p_activity_denorm_tree_row OUT activity_denorm_tree_vw%ROWTYPE, v_result OUT VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Function : get_org_div_dept_by_activity Author : Larry W, Brian G Created : Rocktober 2012 Params : p_counter = the current counter p_count = the overall count Purpose : Get the Activity Golden Tree field values by Activity UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ -- Get the employees's activity denormalized information BEGIN SELECT DISTINCT a.* INTO p_activity_denorm_tree_row FROM activity_denorm_tree_vw a WHERE a.activity = upper(p_activity); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info('SYSTEM', DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); v_result := fbors_funclib.cleanup4json(v_error_message); RETURN; END get_actv_denorm_tree_values; PROCEDURE get_account_info( p_account IN VARCHAR2) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Function : get_org_div_dept_by_activity Author : Larry W, Brian G Created : Rocktober 2012 Params : p_counter = the current counter p_count = the overall count Purpose : Get the Account Golden Tree field values by account UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ BEGIN NULL; /* SELECT DISTINCT a.ucr_acct_sum, a.ucr_acct_sum_dsc, a.ucr_acct_bdgt, a.ucr_acct_bdgt_dsc INTO v_summary_account, v_summary_acct_descr, v_budget_category, v_budcategory_descr FROM ps_ucr_acct_lv_tbl a WHERE a.ucr_acct_bdgt = p_account; */ EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info('SYSTEM', DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); v_result := fbors_funclib.cleanup4json(v_error_message); RETURN; END get_account_info; PROCEDURE view_data( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'se', sort IN VARCHAR2 DEFAULT NULL -- why juan? why? ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : View Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To redirect and then view projections based on the Projection type Params : p_report_type = org / div / dept / actv p_fiscalyear = irrelevant, we only view current period projections p_accounting_period = irrelevant p_value = Org, Div Dept or Activity code p_proj_type = the type of projection to view UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ BEGIN -- Switch to the appropriate procedure to save the projection v_url := gk_APPLICATION_URL || CASE p_proj_type -- Salaried Employees WHEN 'se' THEN 'fbors_projections.view_salary_emp_proj' -- Hourly Staff WHEN 'he' THEN 'fbors_projections.view_hourly_emp_proj' -- Hourly students WHEN 'hs' THEN 'fbors_projections.view_student_proj' -- Non salaried employees WHEN 'ns' THEN 'fbors_projections.view_nonsal_proj' -- Benefits WHEN 'ben' THEN 'fbors_projections.view_benefit_proj' -- Budgets WHEN 'bud' THEN 'fbors_projections.view_budget_proj' -- Wo0t! Infinite loop! <--- Your infinte loop has been terminated, dirt bag! ELSE -- View Salary Employees 'fbors_projections.view_employee_proj' END || '?p_callback=' || p_callback || '&p_report_type=' || p_report_type || '&p_net_id=' || p_net_id || '&p_value=' || p_value || '&p_proj_type=' || p_proj_type; owa_util.redirect_url(v_url, TRUE); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; RETURN; END view_data; PROCEDURE view_salary_emp_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'se' ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : View Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To return projections based on the tree node selected. The projections are shown for the current period only (based on SYSDATE) and there is no switching between periods unlike the PIWRS. Note : This procedure handle two different cases: se = Salaried Employees he = Hourly Employees Both are stored in the same table with Net IDs and Names, unlike the Hourly Student UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ TYPE tr_projections IS RECORD( sequence_id fbors_salary_projs_tbl.sequence_id%TYPE, manual_entry VARCHAR2(5), net_id fbors_salary_projs_tbl.net_id%TYPE, employee_id fbors_salary_projs_tbl.employee_id%TYPE, first_name fbors_salary_projs_tbl.first_name%TYPE, last_name fbors_salary_projs_tbl.last_name%TYPE, fiscal_year fbors_salary_projs_tbl.fiscal_year%TYPE, accounting_period fbors_salary_projs_tbl.accounting_period%TYPE, account fbors_salary_projs_tbl.account%TYPE, fund fbors_salary_projs_tbl.fund%TYPE, activity fbors_salary_projs_tbl.activity%TYPE, function fbors_salary_projs_tbl.function%TYPE, cost_center fbors_salary_projs_tbl.cost_center%TYPE, project_id fbors_salary_projs_tbl.project_id%TYPE, title_code fbors_salary_projs_tbl.title_code%TYPE, title_code_descr fbors_salary_projs_tbl.title_code_descr%TYPE, bargaining_unit fbors_salary_projs_tbl.bargaining_unit%TYPE, proj_begin_date VARCHAR2(10), -- fbors_salary_projs_tbl.pps_begin_date%TYPE, proj_end_date VARCHAR2(10), -- fbors_salary_projs_tbl.pps_end_date%TYPE, begin_period VARCHAR2(2), -- fbors_salary_projs_tbl.pps_begin_date%TYPE, end_period VARCHAR2(2), -- fbors_salary_projs_tbl.pps_end_date%TYPE, dos fbors_salary_projs_tbl.dos%TYPE, monthly_hours fbors_salary_projs_tbl.monthly_hours%TYPE, fte fbors_salary_projs_tbl.fte%TYPE, mod_fte fbors_salary_projs_tbl.fte%TYPE, payrate fbors_salary_projs_tbl.payrate%TYPE, mod_payrate fbors_salary_projs_tbl.mod_payrate%TYPE, projected_amount fbors_salary_projs_tbl.projected_amount%TYPE, comments fbors_salary_projs_tbl.comments%TYPE, deletable VARCHAR2(1), editable VARCHAR2(1), last_updated_by VARCHAR2(100) ); TYPE tt_projections IS TABLE OF tr_projections; vt_projections tt_projections ; --v_max_closed_date DATE := ''; BEGIN log_usage.record_data(p_net_id, DECLARATIONS.gk_IP_ADDRESS, DECLARATIONS.gk_BROWSER_TYPE, DECLARATIONS.gk_APPLICATION_URL, DECLARATIONS.gk_PATH_INFO, DECLARATIONS.gk_QUERY_STRING, ''); -- v_accounting_period := 1; /* -- Convert the current date into the relevant fiscal year and accounting period v_fiscal_year := fbors_funclib.current_fiscal_year; v_max_closed_date := to_date(fbors_funclib.max_closed_date, 'MM/DD/YYYY'); -- ie: 8/8/2012 for closing July v_last_closed_month := to_char(v_max_closed_date, 'MM') -1; -- ie: 8-1 so 7 for July -- v_last_closed_month := 9; -- debugging for other months IF v_last_closed_month > 6 THEN v_accounting_period := v_last_closed_month - 6; ELSE v_accounting_period := v_last_closed_month + 6; END IF; */ -- The main SQL v_sql_projs := ' SELECT DISTINCT sequence_id, decode(p.system_generated, ''N'', ''true'', ''Y'', ''false'' ) manual_entry, p.net_id, p.employee_id, p.first_name, p.last_name, p.fiscal_year, p.accounting_period, p.account, p.fund, p.activity, p.function, p.cost_center, p.project_id, p.title_code, p.title_code_descr, p.bargaining_unit, TO_CHAR(p.mod_begin_date, ''MM/DD/YYYY'') proj_begin_date, TO_CHAR(p.mod_end_date, ''MM/DD/YYYY'') proj_end_date, CASE WHEN p.mod_begin_date < TO_DATE(''07/01/' || (v_fiscal_year-1) || ''', ''MM/DD/YYYY'') THEN 1 WHEN to_number(TO_CHAR(p.mod_begin_date, ''MM'')) > 6 THEN to_number(TO_CHAR(p.mod_begin_date, ''MM'')) - 6 ELSE to_number(TO_CHAR(p.mod_begin_date, ''MM'')) + 6 END proj_begin_period, CASE WHEN p.mod_end_date > TO_DATE(''06/30/' || v_fiscal_year || ''', ''MM/DD/YYYY'') THEN 12 WHEN to_number(TO_CHAR(p.mod_end_date, ''MM'')) > 6 THEN to_number(TO_CHAR(p.mod_end_date, ''MM'')) - 6 ELSE to_number(TO_CHAR(p.mod_end_date, ''MM'')) + 6 END proj_end_period, dos, monthly_hours, NVL(p.fte, 0) fte, NVL(p.mod_fte, 0) mod_fte, p.payrate, NVL(p.mod_payrate, 0) mod_payrate, NVL(p.projected_amount, 0) projected_amount, p.comments, DECODE(p.hrdw_download_date, NULL, ''Y'', ''N'') deletable, CASE WHEN TO_CHAR(p.mod_end_date, ''MM/DD/YYYY'') <= max_closed_date AND TO_CHAR(SYSDATE, ''MM/DD/YYYY'') > max_closed_date THEN ''N'' ELSE ''Y'' END editable, p.updated_by || '' '' || TO_CHAR(p.updated_on, ''MM/DD/YYYY HH:MI'') last_updated_by FROM fbors_salary_projs_tbl p WHERE p.fiscal_year = ' || v_fiscal_year || ' AND p.accounting_period = ' || v_accounting_period || ' AND p.rate_code = ''' || (CASE WHEN p_proj_type='se' THEN 'A' ELSE 'H' END) || ''' AND p.activity IN (SELECT DISTINCT activity FROM activity_denorm_tree_vw a WHERE a.org_code = ''' || p_value || ''' OR a.div_code = ''' || p_value || ''' OR a.dept_code = ''' || p_value || ''' OR a.activity = '''|| p_value || ''') '; dbms_output.put_line('-- SQL for Projections: ' || v_sql_projs || ' ;' ); --RETURN; dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'Generating and executing SQL '); BEGIN dbms_application_info.set_action( 'Executing the SQL @line #' || $$PLSQL_LINE ); EXECUTE IMMEDIATE v_sql_projs BULK COLLECT INTO vt_projections; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || fbors_funclib.cleanup4json(v_sql_projs) || '" } )') ; RETURN; END; v_sql_count := SQL%ROWCOUNT; htp.p(p_callback || '( { "rows" : [ '); IF v_sql_count > 0 THEN FOR I IN 1 .. v_sql_count LOOP htp.p(' { "rownum" : ' || vt_projections(i).sequence_id || ', "rowname" : "", "line_type" : "blank", "manual_entry":' || vt_projections(i).manual_entry || ', "net_id" : "' || vt_projections(i).net_id || '", "employee_id" : "' || vt_projections(i).employee_id || '", "first_name" : "' || vt_projections(i).first_name || '", "last_name" : "' || vt_projections(i).last_name || '", "account" : "' || vt_projections(i).account || '", "activity" : "' || vt_projections(i).activity|| '", "fund" : "' || vt_projections(i).fund || '", "func" : "' || vt_projections(i).function || '", "title_code" : "' || vt_projections(i).title_code || '", "title_code_descr" : "' || vt_projections(i).title_code_descr || '", "bargaining_unit" : "' || vt_projections(i).bargaining_unit || '", "dos" : "' || vt_projections(i).dos || '", "monthly_hours" : ' || vt_projections(i).monthly_hours || ', "fte" : ' || vt_projections(i).fte || ', "mod_fte" : ' || vt_projections(i).mod_fte || ', "payrate" : ' || vt_projections(i).payrate || ', "mod_payrate" : ' || vt_projections(i).mod_payrate || ', "projected_amount" : ' || vt_projections(i).projected_amount || ', "start_date" : "' || vt_projections(i).proj_begin_date || '", "end_date" : "' || vt_projections(i).proj_end_date || '", "start_period" : "' || vt_projections(i).begin_period || '", "end_period" : "' || vt_projections(i).end_period || '", "comments" : "' || utl_url.escape(vt_projections(i).comments) || '", "deletable" : "' || vt_projections(i).deletable || '", "editable" : "' || vt_projections(i).editable || '", "last_updated_by" : "' || vt_projections(i).last_updated_by || '" }' || fbors_funclib.add_comma(i, v_sql_count) ); END LOOP; -- FOR I IN 1 .. V_SQL_ROWCOUNT LOOP END IF; -- IF SQL%ROWCOUNT > 0 THEN -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); htp.p(' ], "success" : true, "text" : "Projections generated for this and that", "runTime": "' || v_run_time || '", "totalCount" : ' || v_sql_count || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || v_sql_projs || '" } )') ; RETURN; END view_salary_emp_proj; PROCEDURE view_hourly_emp_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'he' ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : View Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To return projections based on the tree node selected. The projections are shown for the current period only (based on SYSDATE) and there is no switching between periods unlike the PIWRS. Note : This procedure handle two different cases: se = Salaried Employees he = Hourly Employees Both are stored in the same table with Net IDs and Names, unlike the Hourly Student UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ TYPE tr_projections IS RECORD( sequence_id fbors_salary_projs_tbl.sequence_id%TYPE, manual_entry VARCHAR2(5), net_id fbors_salary_projs_tbl.net_id%TYPE, employee_id fbors_salary_projs_tbl.employee_id%TYPE, first_name fbors_salary_projs_tbl.first_name%TYPE, last_name fbors_salary_projs_tbl.last_name%TYPE, fiscal_year fbors_salary_projs_tbl.fiscal_year%TYPE, accounting_period fbors_salary_projs_tbl.accounting_period%TYPE, account fbors_salary_projs_tbl.account%TYPE, fund fbors_salary_projs_tbl.fund%TYPE, activity fbors_salary_projs_tbl.activity%TYPE, function fbors_salary_projs_tbl.function%TYPE, cost_center fbors_salary_projs_tbl.cost_center%TYPE, project_id fbors_salary_projs_tbl.project_id%TYPE, title_code fbors_salary_projs_tbl.title_code%TYPE, title_code_descr fbors_salary_projs_tbl.title_code_descr%TYPE, bargaining_unit fbors_salary_projs_tbl.bargaining_unit%TYPE, proj_begin_date VARCHAR2(10), -- fbors_salary_projs_tbl.pps_begin_date%TYPE, proj_end_date VARCHAR2(10), -- fbors_salary_projs_tbl.pps_end_date%TYPE, begin_period VARCHAR2(2), -- fbors_salary_projs_tbl.pps_begin_date%TYPE, end_period VARCHAR2(2), -- fbors_salary_projs_tbl.pps_end_date%TYPE, dos fbors_salary_projs_tbl.dos%TYPE, monthly_hours fbors_salary_projs_tbl.monthly_hours%TYPE, fte fbors_salary_projs_tbl.fte%TYPE, mod_fte fbors_salary_projs_tbl.fte%TYPE, payrate fbors_salary_projs_tbl.payrate%TYPE, mod_payrate fbors_salary_projs_tbl.mod_payrate%TYPE, projected_amount fbors_salary_projs_tbl.projected_amount%TYPE, comments fbors_salary_projs_tbl.comments%TYPE, deletable VARCHAR2(1), editable VARCHAR2(1), last_updated_by VARCHAR2(100) ); TYPE tt_projections IS TABLE OF tr_projections; vt_projections tt_projections ; --v_max_closed_date DATE := ''; BEGIN log_usage.record_data(p_net_id, DECLARATIONS.gk_IP_ADDRESS, DECLARATIONS.gk_BROWSER_TYPE, DECLARATIONS.gk_APPLICATION_URL, DECLARATIONS.gk_PATH_INFO, DECLARATIONS.gk_QUERY_STRING, ''); -- v_accounting_period := 1; /* -- Convert the current date into the relevant fiscal year and accounting period v_fiscal_year := fbors_funclib.current_fiscal_year; v_max_closed_date := to_date(fbors_funclib.max_closed_date, 'MM/DD/YYYY'); -- ie: 8/8/2012 for closing July v_last_closed_month := to_char(v_max_closed_date, 'MM') -1; -- ie: 8-1 so 7 for July -- v_last_closed_month := 9; -- debugging for other months IF v_last_closed_month > 6 THEN v_accounting_period := v_last_closed_month - 6; ELSE v_accounting_period := v_last_closed_month + 6; END IF; */ -- The main SQL v_sql_projs := ' SELECT DISTINCT sequence_id, decode(p.system_generated, ''N'', ''true'', ''Y'', ''false'' ) manual_entry, p.net_id, p.employee_id, p.first_name, p.last_name, p.fiscal_year, p.accounting_period, p.account, p.fund, p.activity, p.function, p.cost_center, p.project_id, p.title_code, p.title_code_descr, p.bargaining_unit, TO_CHAR(p.mod_begin_date, ''MM/DD/YYYY'') proj_begin_date, TO_CHAR(p.mod_end_date, ''MM/DD/YYYY'') proj_end_date, CASE WHEN p.mod_begin_date < TO_DATE(''07/01/' || (v_fiscal_year-1) || ''', ''MM/DD/YYYY'') THEN 1 WHEN to_number(TO_CHAR(p.mod_begin_date, ''MM'')) > 6 THEN to_number(TO_CHAR(p.mod_begin_date, ''MM'')) - 6 ELSE to_number(TO_CHAR(p.mod_begin_date, ''MM'')) + 6 END proj_begin_period, CASE WHEN p.mod_end_date > TO_DATE(''06/30/' || v_fiscal_year || ''', ''MM/DD/YYYY'') THEN 12 WHEN to_number(TO_CHAR(p.mod_end_date, ''MM'')) > 6 THEN to_number(TO_CHAR(p.mod_end_date, ''MM'')) - 6 ELSE to_number(TO_CHAR(p.mod_end_date, ''MM'')) + 6 END proj_end_period, dos, monthly_hours, NVL(p.fte, 0) fte, NVL(p.mod_fte, 0) mod_fte, p.payrate, NVL(p.mod_payrate, 0) mod_payrate, NVL(p.projected_amount, 0) projected_amount, p.comments, DECODE(p.hrdw_download_date, NULL, ''Y'', ''N'') deletable, CASE WHEN TO_CHAR(p.mod_end_date, ''MM/DD/YYYY'') <= max_closed_date AND TO_CHAR(SYSDATE, ''MM/DD/YYYY'') > max_closed_date THEN ''N'' ELSE ''Y'' END editable, p.updated_by || '' '' || TO_CHAR(p.updated_on, ''MM/DD/YYYY HH:MI'') last_updated_by FROM fbors_salary_projs_tbl p WHERE p.fiscal_year = ' || v_fiscal_year || ' AND p.accounting_period = ' || v_accounting_period || ' AND p.rate_code = ''' || (CASE WHEN p_proj_type='se' THEN 'A' ELSE 'H' END) || ''' AND p.activity IN (SELECT DISTINCT activity FROM activity_denorm_tree_vw a WHERE a.org_code = ''' || p_value || ''' OR a.div_code = ''' || p_value || ''' OR a.dept_code = ''' || p_value || ''' OR a.activity = '''|| p_value || ''') '; dbms_output.put_line('-- SQL for Projections: ' || v_sql_projs || ' ;' ); --RETURN; dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'Generating and executing SQL '); BEGIN dbms_application_info.set_action( 'Executing the SQL @line #' || $$PLSQL_LINE ); EXECUTE IMMEDIATE v_sql_projs BULK COLLECT INTO vt_projections; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || fbors_funclib.cleanup4json(v_sql_projs) || '" } )') ; RETURN; END; v_sql_count := SQL%ROWCOUNT; htp.p(p_callback || '( { "rows" : [ '); IF v_sql_count > 0 THEN FOR I IN 1 .. v_sql_count LOOP htp.p(' { "rownum" : ' || vt_projections(i).sequence_id || ', "rowname" : "", "line_type" : "blank", "manual_entry":' || vt_projections(i).manual_entry || ', "net_id" : "' || vt_projections(i).net_id || '", "employee_id" : "' || vt_projections(i).employee_id || '", "first_name" : "' || vt_projections(i).first_name || '", "last_name" : "' || vt_projections(i).last_name || '", "account" : "' || vt_projections(i).account || '", "activity" : "' || vt_projections(i).activity|| '", "fund" : "' || vt_projections(i).fund || '", "func" : "' || vt_projections(i).function || '", "title_code" : "' || vt_projections(i).title_code || '", "title_code_descr" : "' || vt_projections(i).title_code_descr || '", "bargaining_unit" : "' || vt_projections(i).bargaining_unit || '", "dos" : "' || vt_projections(i).dos || '", "monthly_hours" : ' || vt_projections(i).monthly_hours || ', "fte" : ' || vt_projections(i).fte || ', "mod_fte" : ' || vt_projections(i).mod_fte || ', "payrate" : ' || vt_projections(i).payrate || ', "mod_payrate" : ' || vt_projections(i).mod_payrate || ', "projected_amount" : ' || vt_projections(i).projected_amount || ', "start_date" : "' || vt_projections(i).proj_begin_date || '", "end_date" : "' || vt_projections(i).proj_end_date || '", "start_period" : "' || vt_projections(i).begin_period || '", "end_period" : "' || vt_projections(i).end_period || '", "comments" : "' || utl_url.escape(vt_projections(i).comments) || '", "deletable" : "' || vt_projections(i).deletable || '", "editable" : "' || vt_projections(i).editable || '", "last_updated_by" : "' || vt_projections(i).last_updated_by || '" }' || fbors_funclib.add_comma(i, v_sql_count) ); END LOOP; -- FOR I IN 1 .. V_SQL_ROWCOUNT LOOP END IF; -- IF SQL%ROWCOUNT > 0 THEN -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); htp.p(' ], "success" : true, "text" : "Projections generated for this and that", "runTime": "' || v_run_time || '", "totalCount" : ' || v_sql_count || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || v_sql_projs || '" } )') ; RETURN; END view_hourly_emp_proj; PROCEDURE view_student_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'hs' ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : View Hourly Staff / Student Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To return projections based on the tree node selected. The projections are shown for the current period only (based on SYSDATE) and there is no switching between periods unlike the PIWRS. UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ --v_sql_activities st_maxvarchar2 := ''; TYPE tr_projections IS RECORD( sequence_id fbors_hourly_projs_tbl.sequence_id%TYPE, manual_entry VARCHAR2(5), fiscal_year fbors_hourly_projs_tbl.fiscal_year%TYPE, accounting_period fbors_hourly_projs_tbl.accounting_period%TYPE, account fbors_hourly_projs_tbl.account%TYPE, fund fbors_hourly_projs_tbl.fund%TYPE, activity fbors_hourly_projs_tbl.activity%TYPE, function fbors_hourly_projs_tbl.function%TYPE, title_code fbors_hourly_projs_tbl.title_code%TYPE, title_code_descr fbors_hourly_projs_tbl.title_code_descr%TYPE, bargaining_unit fbors_hourly_projs_tbl.bargaining_unit%TYPE, proj_begin_date VARCHAR2(10), -- fbors_salary_projs_tbl.pps_begin_date%TYPE, proj_end_date VARCHAR2(10), -- fbors_salary_projs_tbl.pps_end_date%TYPE, begin_period VARCHAR2(2), -- fbors_salary_projs_tbl.pps_begin_date%TYPE, end_period VARCHAR2(2), -- fbors_salary_projs_tbl.pps_end_date%TYPE, dos fbors_hourly_projs_tbl.dos%TYPE, fte fbors_hourly_projs_tbl.sum_fte%TYPE, mod_fte fbors_hourly_projs_tbl.mod_sum_fte%TYPE, hourly_wage fbors_hourly_projs_tbl.avg_hourly_wage%TYPE, mod_wage fbors_hourly_projs_tbl.mod_avg_hourly_wage%TYPE, projected_amount fbors_hourly_projs_tbl.mod_proj_amt%TYPE, comments fbors_hourly_projs_tbl.comments%TYPE, deletable VARCHAR2(1), editable VARCHAR2(1), last_updated_by VARCHAR2(100) ); TYPE tt_projections IS TABLE OF tr_projections; vt_projections tt_projections ; BEGIN log_usage.record_data(p_net_id, DECLARATIONS.gk_IP_ADDRESS, DECLARATIONS.gk_BROWSER_TYPE, DECLARATIONS.gk_APPLICATION_URL, DECLARATIONS.gk_PATH_INFO, DECLARATIONS.gk_QUERY_STRING, ''); -- v_accounting_period := 1; /* -- Convert the current date into the relevant fiscal year and accounting period v_fiscal_year := fbors_funclib.current_fiscal_year; v_max_closed_date := to_date(fbors_funclib.max_closed_date, 'MM/DD/YYYY'); -- ie: 8/8/2012 for closing July v_last_closed_month := to_char(v_max_closed_date, 'MM') -1; -- ie: 8-1 so 7 for July -- v_last_closed_month := 9; -- debugging for other months IF v_last_closed_month > 6 THEN v_accounting_period := v_last_closed_month - 6; ELSE v_accounting_period := v_last_closed_month + 6; END IF; */ -- The main SQL v_sql_projs := ' SELECT DISTINCT sequence_id, decode(p.system_generated, ''N'', ''true'', ''Y'', ''false'' ) manual_entry, -- p.net_id, p.employee_id, p.first_name, p.last_name, p.fiscal_year, p.accounting_period, p.account, p.fund, p.activity, p.function, p.title_code, p.title_code_descr, p.bargaining_unit, TO_CHAR(p.mod_begin_date, ''MM/DD/YYYY'') proj_begin_date, TO_CHAR(p.mod_end_date, ''MM/DD/YYYY'') proj_end_date, CASE WHEN p.mod_begin_date < TO_DATE(''07/01/' || (v_fiscal_year-1) || ''', ''MM/DD/YYYY'') THEN 1 WHEN to_number(TO_CHAR(p.mod_begin_date, ''MM'')) > 6 THEN to_number(TO_CHAR(p.mod_begin_date, ''MM'')) - 6 ELSE to_number(TO_CHAR(p.mod_begin_date, ''MM'')) + 6 END proj_begin_period, CASE WHEN p.mod_end_date > TO_DATE(''06/30/' || v_fiscal_year || ''', ''MM/DD/YYYY'') THEN 12 WHEN to_number(TO_CHAR(p.mod_end_date, ''MM'')) > 6 THEN to_number(TO_CHAR(p.mod_end_date, ''MM'')) - 6 ELSE to_number(TO_CHAR(p.mod_end_date, ''MM'')) + 6 END proj_end_period, dos, NVL(p.sum_fte, 0) fte, NVL(p.mod_sum_fte, 0) mod_fte, p.avg_hourly_wage, NVL(p.mod_avg_hourly_wage, 0) mod_avg_hourly_wage, NVL(p.mod_proj_amt, 0) mod_proj_amt, p.comments, DECODE(p.hrdw_download_date, NULL, ''Y'', ''N'') deletable, CASE WHEN TO_CHAR(p.mod_end_date, ''MM/DD/YYYY'') <= max_closed_date AND TO_CHAR(SYSDATE, ''MM/DD/YYYY'') > max_closed_date THEN ''N'' ELSE ''Y'' END editable, p.updated_by || '' '' || TO_CHAR(p.updated_on, ''MM/DD/YYYY HH:MI'') last_updated_by FROM fbors_hourly_projs_tbl p WHERE p.fiscal_year = ' || v_fiscal_year || ' AND p.accounting_period = ' || v_accounting_period || ' AND p.activity IN (SELECT DISTINCT activity FROM activity_denorm_tree_vw a WHERE a.org_code = ''' || p_value || ''' OR a.div_code = ''' || p_value || ''' OR a.dept_code = ''' || p_value || ''' OR a.activity = '''|| p_value || ''') '; dbms_output.put_line('-- SQL for Projections: ' || v_sql_projs || ' ;' ); --RETURN; dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'Generating and executing SQL '); BEGIN dbms_application_info.set_action( 'Executing the SQL @line #' || $$PLSQL_LINE ); EXECUTE IMMEDIATE v_sql_projs BULK COLLECT INTO vt_projections; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || fbors_funclib.cleanup4json(v_sql_projs) || '" } )') ; RETURN; END; v_sql_count := SQL%ROWCOUNT; htp.p(p_callback || '( { "rows" : [ '); IF v_sql_count > 0 THEN FOR I IN 1 .. v_sql_count LOOP htp.p(' { "rownum" : ' || vt_projections(i).sequence_id || ', "rowname" : "", "line_type" : "blank", "manual_entry":' || vt_projections(i).manual_entry || ', "account" : "' || vt_projections(i).account || '", "activity" : "' || vt_projections(i).activity|| '", "fund" : "' || vt_projections(i).fund || '", "func" : "' || vt_projections(i).function || '", "title_code" : "' || vt_projections(i).title_code || '", "title_code_descr" : "' || vt_projections(i).title_code_descr || '", "bargaining_unit" : "' || vt_projections(i).bargaining_unit || '", "dos" : "' || vt_projections(i).dos || '", "fte" : ' || vt_projections(i).fte || ', "mod_fte" : ' || vt_projections(i).mod_fte || ', "payrate" : ' || vt_projections(i).hourly_wage || ', "mod_payrate" : ' || vt_projections(i).mod_wage || ', "projected_amount" : ' || vt_projections(i).projected_amount || ', "start_date" : "' || vt_projections(i).proj_begin_date || '", "end_date" : "' || vt_projections(i).proj_end_date || '", "start_period" : "' || vt_projections(i).begin_period || '", "end_period" : "' || vt_projections(i).end_period || '", "comments" : "' || utl_url.escape(vt_projections(i).comments) || '", "deletable" : "' || vt_projections(i).deletable || '", "editable" : "' || vt_projections(i).editable || '", "last_updated_by" : "' || vt_projections(i).last_updated_by || '" }' || fbors_funclib.add_comma(i, v_sql_count) ); END LOOP; -- FOR I IN 1 .. V_SQL_ROWCOUNT LOOP END IF; -- IF SQL%ROWCOUNT > 0 THEN -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); htp.p(' ], "success" : true, "text" : "Projections generated for this and that", "runTime": "' || v_run_time || '", "totalCount" : ' || v_sql_count || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || v_sql_projs || '" } )') ; RETURN; END view_student_proj; PROCEDURE view_nonsal_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'ns' ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : View Non Salary Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To return non salary projections based on the tree node selected. The projections are shown for the current period only (based on SYSDATE) and there is no switching between periods unlike the PIWRS. UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ --v_module_name VARCHAR2(1000) := ''; --v_action_name VARCHAR2(1000) := ''; --v_client_info VARCHAR2(1000) := ''; TYPE tr_projections IS RECORD( sequence_id fbors_nonsalary_projs_tbl.Sequence_Id%TYPE, manual_entry VARCHAR2(5), budget_category fbors_nonsalary_projs_tbl.budget_category%TYPE, budcategory_descr fbors_nonsalary_projs_tbl.budcategory_descr%TYPE, fund fbors_nonsalary_projs_tbl.fund%TYPE, org_code fbors_nonsalary_projs_tbl.org_code%TYPE, div_code fbors_nonsalary_projs_tbl.div_code%TYPE, dept_code fbors_nonsalary_projs_tbl.dept_code%TYPE, activity fbors_nonsalary_projs_tbl.activity%TYPE, FUNCTION fbors_nonsalary_projs_tbl.function%TYPE, calc_aggreg_amt fbors_nonsalary_projs_tbl.calc_aggreg_amt%TYPE, calc_orig_avg_amt fbors_nonsalary_projs_tbl.calc_orig_avg_amt%TYPE, calc_mod_avg_amt fbors_nonsalary_projs_tbl.calc_mod_avg_amt%TYPE, mod_aggreg_amt fbors_nonsalary_projs_tbl.mod_aggreg_amt%TYPE, mod_avg_pct fbors_nonsalary_projs_tbl.mod_avg_pct%TYPE, mod_pct_begin_per VARCHAR2(2), mod_pct_end_per VARCHAR2(2), mod_avg_amt fbors_nonsalary_projs_tbl.mod_avg_amt%TYPE, mod_amt_begin_per VARCHAR2(2), mod_amt_end_per VARCHAR2(2), comments fbors_nonsalary_projs_tbl.comments%TYPE, system_generated fbors_nonsalary_projs_tbl.system_generated%TYPE, last_updated_by VARCHAR2(100), editable VARCHAR2(1) ); TYPE tt_projections IS TABLE OF tr_projections; vt_projections tt_projections ; BEGIN log_usage.record_data(p_net_id, DECLARATIONS.gk_IP_ADDRESS, DECLARATIONS.gk_BROWSER_TYPE, DECLARATIONS.gk_APPLICATION_URL, DECLARATIONS.gk_PATH_INFO, DECLARATIONS.gk_QUERY_STRING, ''); -- v_accounting_period := 3; -- debugging /* -- Convert the current date into the relevant fiscal year and accounting period v_fiscal_year := fbors_funclib.current_fiscal_year; v_max_closed_date := to_date(fbors_funclib.max_closed_date, 'MM/DD/YYYY'); -- ie: 8/8/2012 for closing July v_last_closed_month := TO_CHAR(TO_DATE(v_max_closed_date, 'DD-MON-YYYY'), 'MM') -1; -- ie: 8-1 so 7 for July -- v_last_closed_month := 8; -- debugging for other months IF v_last_closed_month > 6 THEN v_accounting_period := v_last_closed_month - 6; ELSE v_accounting_period := v_last_closed_month + 6; END IF; */ v_sql_projs := ' SELECT sequence_id, decode(f.system_generated, ''N'', ''true'', ''Y'', ''false'' ) manual_entry, budget_category, budcategory_descr, fund, org_code, div_code,dept_code, activity, function, f.calc_aggreg_amt, f.calc_orig_avg_amt, f.calc_mod_avg_amt, f.mod_aggreg_amt, f.mod_avg_pct, CASE WHEN to_char(f.mod_pct_begin_date, ''MM'') >6 THEN (to_char(f.mod_pct_begin_date, ''MM'')-6) ELSE (to_char(f.mod_pct_begin_date, ''MM'')+6) END mod_pct_begin_per, CASE WHEN to_char(f.mod_pct_end_date, ''MM'') >6 THEN (to_char(f.mod_pct_end_date, ''MM'')-6) ELSE (to_char(f.mod_pct_end_date, ''MM'')+6) END mod_pct_end_per, f.mod_avg_amt, CASE WHEN to_char(f.mod_amt_begin_date, ''MM'') >6 THEN (to_char(f.mod_amt_begin_date, ''MM'')-6) ELSE (to_char(f.mod_amt_begin_date, ''MM'')+6) END mod_amt_begin_per, CASE WHEN to_char(f.mod_amt_end_date, ''MM'') >6 THEN (to_char(f.mod_amt_end_date, ''MM'')-6) ELSE (to_char(f.mod_amt_end_date, ''MM'')+6) END mod_amt_end_per, comments, system_generated, f.updated_by || '' '' || TO_CHAR(f.updated_on, ''MM/DD/YYYY HH:MI'') last_updated_by, CASE WHEN TO_CHAR(f.mod_amt_end_date, ''MM/DD/YYYY'') <= max_closed_date AND TO_CHAR(SYSDATE, ''MM/DD/YYYY'') > max_closed_date THEN ''N'' ELSE ''Y'' END editable FROM fbors_nonsalary_projs_tbl f WHERE f.fiscal_year = ' || v_fiscal_year || ' AND f.accounting_period = ' || v_accounting_period || ' AND f.activity IN (SELECT DISTINCT activity FROM activity_denorm_tree_vw a WHERE a.org_code = ''' || p_value || ''' OR a.div_code = ''' || p_value || ''' OR a.dept_code = ''' || p_value || ''' OR a.activity = ''' || p_value || ''') ORDER BY fiscal_year, accounting_period, budget_category, activity, fund, function '; dbms_output.put_line(v_sql_projs); -- RETURN; dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'Generating and executing SQL '); BEGIN dbms_application_info.set_action( 'Executing the SQL @line #'|| $$PLSQL_LINE ); EXECUTE IMMEDIATE v_sql_projs BULK COLLECT INTO vt_projections; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(v_sql_projs); v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || fbors_funclib.cleanup4json(v_sql_projs) || '" } )') ; RETURN; END; v_sql_count := SQL%ROWCOUNT; htp.p(p_callback || '( { "rows" : [ '); IF v_sql_count > 0 THEN FOR I IN 1 .. v_sql_count LOOP htp.p(' { "rownum" : "'|| vt_projections(i).sequence_id || '", "rowname" : "", "line_type" : "blank", "manual_entry":' || vt_projections(i).manual_entry || ', "budget_category":"' || vt_projections(i).budget_category || '", "budcategory_descr" : "' || vt_projections(i).budcategory_descr || '", "fund" : "' || vt_projections(i).fund || '", "activity" : "' || vt_projections(i).activity || '", "func" : "' || vt_projections(i).function || '", "calc_aggreg_amt" : "' || vt_projections(i).calc_aggreg_amt || '", "calc_orig_avg_amt" : "' || vt_projections(i).calc_orig_avg_amt || '", "calc_mod_avg_amt" : "' || vt_projections(i).calc_mod_avg_amt || '", "mod_aggreg_amt" : "' || vt_projections(i).mod_aggreg_amt || '", "mod_avg_pct_real" : "' || vt_projections(i).mod_avg_pct || '", "mod_avg_pct" : "0", "mod_pct_begin_per" : "' || vt_projections(i).mod_pct_begin_per || '", "mod_pct_end_per": "' || vt_projections(i).mod_pct_end_per || '", "mod_avg_amt_real" : "' || vt_projections(i).mod_avg_amt || '", "mod_avg_amt" : "0", "mod_amt_begin_per":"' || vt_projections(i).mod_amt_begin_per || '", "mod_amt_end_per":"' || vt_projections(i).mod_amt_end_per || '", "comments" : "' || utl_url.escape(vt_projections(i).comments) || '", "deletable" : "N", "editable" : "' || vt_projections(i).editable || '", "system_generated" : "' || vt_projections(i).system_generated || '", "last_updated_by" : "' || vt_projections(i).last_updated_by || '" }' || fbors_funclib.add_comma(i, v_sql_count) ); END LOOP; -- FOR I IN 1 .. V_SQL_ROWCOUNT LOOP END IF; -- IF SQL%ROWCOUNT > 0 THEN -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); htp.p(' ], "success" : true, "text" : "Projections generated for this and that", "runTime": "' || v_run_time || '", "totalCount" : ' || v_sql_count || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || v_sql_projs || '" } )') ; RETURN; END view_nonsal_proj; PROCEDURE view_budget_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'bud' ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : View Budget Projection Data Author : Larry W, Brian G Created : Sept 2012 Purpose : To return budget projections based on the tree node selected. The projections are shown for the current period only (based on SYSDATE) and there is no switching between periods unlike the PIWRS. UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ --v_module_name VARCHAR2(1000) := ''; --v_action_name VARCHAR2(1000) := ''; --v_client_info VARCHAR2(1000) := ''; /*v_fiscal_year INTEGER := 0; v_accounting_period INTEGER := 0;*/ /* TYPE tr_projections IS RECORD( budcategory fbors_budget_projs_tbl.budcategory%TYPE, budcategory_descr fbors_nonsalary_projs_tbl.budcategory_descr%TYPE, sum_amt fbors_nonsalary_projs_tbl.calc_sum_amt%TYPE, avg_amt fbors_nonsalary_projs_tbl.calc_avg_amt%TYPE ); TYPE tt_projections IS TABLE OF tr_projections; */ TYPE tt_projections IS TABLE OF fbors_budget_projs_tbl%ROWTYPE; vt_projections tt_projections ; BEGIN log_usage.record_data(p_net_id, DECLARATIONS.gk_IP_ADDRESS, DECLARATIONS.gk_BROWSER_TYPE, DECLARATIONS.gk_APPLICATION_URL, DECLARATIONS.gk_PATH_INFO, DECLARATIONS.gk_QUERY_STRING, ''); --v_accounting_period := 1; -- debugging v_sql_projs := ' SELECT sequence_id, fiscal_year, accounting_period, ledger, descr, budget_category, fund, org_code, div_code, dept_code, activity, function, amount, proj_begin_date, proj_end_date, comments, updated_by, updated_on FROM fbors_budget_projs_tbl f WHERE f.fiscal_year = ' || v_fiscal_year || ' AND f.accounting_period = ' || v_accounting_period || ' AND f.activity IN (SELECT DISTINCT activity FROM activity_denorm_tree_vw a WHERE a.org_code = ''' || p_value || ''' OR a.div_code = ''' || p_value || ''' OR a.dept_code = ''' || p_value || ''' OR a.activity = '''|| p_value || ''') ORDER BY f.budget_category '; dbms_output.put_line(v_sql_projs); --RETURN; dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'Generating and executing SQL '); BEGIN dbms_application_info.set_action( 'Executing the SQL @line #'|| $$PLSQL_LINE ); EXECUTE IMMEDIATE v_sql_projs BULK COLLECT INTO vt_projections; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(v_sql_projs); v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || fbors_funclib.cleanup4json(v_sql_projs) || '" } )') ; RETURN; END; v_sql_count := SQL%ROWCOUNT; htp.p(p_callback || '( { "rows" : [ '); IF v_sql_count > 0 THEN FOR I IN 1 .. v_sql_count LOOP htp.p(' { "rownum" : "'|| vt_projections(i).sequence_id || '", "rowname" : "", "line_type" : "blank", "type" : "' || vt_projections(i).ledger || '", "descr" : "' || vt_projections(i).descr || '", "budget_category":"' || vt_projections(i).budget_category || '", "activity":"' || vt_projections(i).activity || '", "fund":"' || vt_projections(i).fund || '", "function":"' || vt_projections(i).function || '", "amount" : "' || vt_projections(i).amount || '", "proj_begin_date" : "' || vt_projections(i).proj_begin_date || '", "proj_end_date" : "' || vt_projections(i).proj_end_date || '", "deletable" : "Y", "editable" : "' || (CASE WHEN vt_projections(i).proj_end_date <= max_closed_date AND TO_CHAR(SYSDATE, 'MM/DD/YYYY') > max_closed_date THEN 'N' ELSE 'Y' END) || '", "comments" : "' || utl_url.escape(vt_projections(i).comments) || '", "last_updated_by" : "' || vt_projections(i).updated_by || '" }' ||fbors_funclib.add_comma(i, v_sql_count) ); END LOOP; -- FOR I IN 1 .. V_SQL_ROWCOUNT LOOP END IF; -- IF SQL%ROWCOUNT > 0 THEN -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); htp.p(' ], "success" : true, "text" : "Projections generated for this and that", "runTime": "' || v_run_time || '", "totalCount" : ' || v_sql_count || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || v_sql_projs || '" } )') ; RETURN; END view_budget_proj; PROCEDURE view_benefit_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_report_type IN VARCHAR2 DEFAULT NULL, p_value IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'ben' ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : View Benefit Projection Data Author : Larry W, Brian G Created : Sept 2012 Purpose : To return SDOPE benefit projections based on the tree node selected. The projections are shown for the current period only (based on SYSDATE) and there is no switching between periods unlike the PIWRS. UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ --v_module_name VARCHAR2(1000) := ''; --v_action_name VARCHAR2(1000) := ''; --v_client_info VARCHAR2(1000) := ''; /*v_fiscal_year INTEGER := 0; v_accounting_period INTEGER := 0;*/ TYPE tr_projections IS RECORD( sequence_id fbors_benefits_projs_tbl.sequence_id%TYPE, manual_entry VARCHAR2(5), account fbors_benefits_projs_tbl.account%TYPE, budget_category fbors_benefits_projs_tbl.budget_category%TYPE, budcategory_descr fbors_benefits_projs_tbl.budcategory_descr%TYPE, activity fbors_benefits_projs_tbl.activity%TYPE, fund fbors_benefits_projs_tbl.fund%TYPE, FUNCTION fbors_benefits_projs_tbl.function%TYPE, total_benefits_amt fbors_benefits_projs_tbl.total_benefits_amt%TYPE, mod_total_benefits_amt fbors_benefits_projs_tbl.total_benefits_amt%TYPE, mod_begin_date VARCHAR2(10), -- fbors_benefits_projs_tbl.begin_date%TYPE, mod_end_date VARCHAR2(10), -- fbors_benefits_projs_tbl.end_date%TYPE, comments fbors_benefits_projs_tbl.comments%TYPE, system_generated fbors_benefits_projs_tbl.system_generated%TYPE, last_updated_by VARCHAR2(100) ); TYPE tt_projections IS TABLE OF tr_projections; -- TYPE tt_projections IS TABLE OF fbors_benefits_projs_tbl%ROWTYPE; vt_projections tt_projections ; BEGIN log_usage.record_data(p_net_id, DECLARATIONS.gk_IP_ADDRESS, DECLARATIONS.gk_BROWSER_TYPE, DECLARATIONS.gk_APPLICATION_URL, DECLARATIONS.gk_PATH_INFO, DECLARATIONS.gk_QUERY_STRING, ''); --v_accounting_period := 1; -- debugging v_sql_projs := ' SELECT sequence_id, decode(f.system_generated, ''N'', ''true'', ''Y'', ''false'' ) manual_entry, account, budget_category, budcategory_descr, activity, fund, function, total_benefits_amt, --TO_CHAR(begin_date, ''MM/DD/YYYY'') begin_date, --TO_CHAR(end_date, ''MM/DD/YYYY'') end_date, mod_total_benefits_amt, TO_CHAR(mod_begin_date, ''MM/DD/YYYY'') mod_begin_date, TO_CHAR(mod_end_date, ''MM/DD/YYYY'') mod_end_date, comments, system_generated, f.updated_by || '' '' || TO_CHAR(f.updated_on, ''MM/DD/YYYY HH:MI'') last_updated_by FROM fbors_benefits_projs_tbl f WHERE f.fiscal_year = ' || v_fiscal_year || ' AND f.accounting_period = ' || v_accounting_period || ' AND f.activity IN (SELECT DISTINCT a.activity FROM activity_denorm_tree_vw a WHERE a.org_code = ''' || p_value || ''' OR a.div_code = ''' || p_value || ''' OR a.dept_code = ''' || p_value || ''' OR a.activity = ''' || p_value || ''') ORDER BY activity, fund, function, budget_category '; dbms_output.put_line(v_sql_projs); --RETURN; dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'Generating and executing SQL '); BEGIN dbms_application_info.set_action( 'Executing the SQL @line #'|| $$PLSQL_LINE ); EXECUTE IMMEDIATE v_sql_projs BULK COLLECT INTO vt_projections; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(v_sql_projs); v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || fbors_funclib.cleanup4json(v_sql_projs) || '" } )') ; RETURN; END; v_sql_count := SQL%ROWCOUNT; htp.p(p_callback || '( { "rows" : [ '); IF v_sql_count > 0 THEN FOR I IN 1 .. v_sql_count LOOP htp.p(' { "rownum" : "'|| vt_projections(i).sequence_id || '", "rowname" : "", "line_type" : "blank", "manual_entry":' || vt_projections(i).manual_entry || ', "budget_category" : "' || vt_projections(i).budget_category || '", "budcategory_descr" : "' || vt_projections(i).budcategory_descr || '", "account":"' || vt_projections(i).account || '", "activity":"' || vt_projections(i).activity || '", "fund":"' || vt_projections(i).fund || '", "func":"' || vt_projections(i).function || '", "amount" : "' || vt_projections(i).total_benefits_amt || '", "mod_amount" : "' || vt_projections(i).mod_total_benefits_amt || '", "mod_start_date" : "' || vt_projections(i).mod_begin_date || '", "mod_end_date" : "' || vt_projections(i).mod_end_date || '", "deletable" : "N", "editable" : "' || (CASE WHEN vt_projections(i).mod_end_date <= max_closed_date AND TO_CHAR(SYSDATE, 'MM/DD/YYYY') > max_closed_date THEN 'N' ELSE 'Y' END) || '", "comments" : "' || utl_url.escape(vt_projections(i).comments) || '", "last_updated_by" : "' || vt_projections(i).last_updated_by || '" }' || fbors_funclib.add_comma(i, v_sql_count) ); /* REMOVED since we are summarizing the SDOPE information by BC "start_date" : "' || vt_projections(i).begin_date || '", "end_date" : "' || vt_projections(i).end_date || '", */ END LOOP; -- FOR I IN 1 .. V_SQL_ROWCOUNT LOOP END IF; -- IF SQL%ROWCOUNT > 0 THEN -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); htp.p(' ], "success" : true, "text" : "Projections generated for this and that", "runTime": "' || v_run_time || '", "totalCount" : ' || v_sql_count || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "sql":"' || v_sql_projs || '" } )') ; RETURN; END view_benefit_proj; PROCEDURE save_data( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2, p_grid_data IN VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : Save Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To redirect and then save projections based on the Projection type Params : p_grid_data : M~2~alanr~856788673~ALAN~ROY~7277~0.25~1500~July~January~Updated projection for Alan July - January~500110~A01369~66095~72 p_employee_id : 850165183 p_proj_type : se, he, nse, ben, bud UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ BEGIN -- Switch to the appropriate procedure to save the projection -- TO DO: NEED TO UPDATE HOW WE CONSTRUCT THE URL: v_url := gk_APPLICATION_URL || 'fbors_projections.' || CASE p_proj_type -- Salary employees --WHEN 'se' THEN 'save_sal_emp_proj' -- Hourly employees --WHEN 'he' THEN 'save_hrly_emp_proj' -- Non salaried employees WHEN 'ns' THEN 'save_nonsal_proj' -- Benefits WHEN 'ben' THEN 'save_benefit_proj' -- Budgets WHEN 'bud' THEN 'save_budget_proj' -- Wo0t! Infinite loop! <--- Your infinte loop has been terminated, dirt bag! -- Hourly Employees WHEN 'he' THEN 'save_hourly_emp_proj' -- Hourly Students WHEN 'hs' THEN 'save_student_proj' ELSE --'save_data' 'save_salary_emp_proj' -- Salary Employees END || '?p_callback=' || p_callback || '&p_net_id=' || p_net_id || '&p_employee_id=' || p_employee_id || '&p_grid_data=' || p_grid_data || '&p_proj_type=' || p_proj_type; owa_util.redirect_url(v_url, TRUE); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; RETURN; END save_data; PROCEDURE save_salary_emp_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'se', p_grid_data IN VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : Save Salaried Employees Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To save projections to the FBORS_SALARY_PROJS_TBL table Params : p_grid_data : the list of FORM elements passed as a javasript array into a single parameter here M~2~alanr~856788673~ALAN~ROY~7277~0.25~1500~July~January~Updated projection for Alan July - January~500110~A01369~66095~72 A~-1~~BR~GR~3074~1~1500~September~September~Added by Brian~500110~A01375~19900~72 UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ -- Array positions to parse through p_grid_data k_ADD_MOD CONSTANT INTEGER := 1; k_SEQ_ID CONSTANT INTEGER := 2; k_NET_ID CONSTANT INTEGER := 3; k_EMPL_ID CONSTANT INTEGER := 4; k_FIRST_NAME CONSTANT INTEGER := 5; k_LAST_NAME CONSTANT INTEGER := 6; k_TITLE_CODE CONSTANT INTEGER := 7; K_ACCOUNT CONSTANT INTEGER := 8; K_ACTIVITY CONSTANT INTEGER := 9; K_FUND CONSTANT INTEGER := 10; K_FUNCTION CONSTANT INTEGER := 11; k_FTE CONSTANT INTEGER := 12; -- This field is over-loaded for MONTHLY HOURS K_PAY_RATE CONSTANT INTEGER := 13; K_TOTAL CONSTANT INTEGER := 14; K_BEGIN_PER CONSTANT INTEGER := 15; K_END_PER CONSTANT INTEGER := 16; K_COMMENTS CONSTANT INTEGER := 17; -- M~53770~sbiggers~851878503~SCOTT~BIGGERSTAFF~9899~501110~A01210~19900~72~0~520.19~0~1~12~System generated projection -- test -- M~55378~feleciaw~854006169~DARLENE~WILSON~1~7230~500110~A01210~19900~72~160~25.65~$4,104.00~7~12~System generated projection - test -- A~-1~larryw~856232490~Larry~Whittaker~7275~501110~A01210~19900~72~0.5~12~0~11~12~test -- Projection Start and End Dates --v_start_fiscal_year fbors_salary_projs_tbl.fiscal_year%TYPE; --v_start_accounting_period fbors_salary_projs_tbl.accounting_period%TYPE; v_summary_account fbors_salary_projs_tbl.summary_account%TYPE; v_summary_acct_descr fbors_salary_projs_tbl.summary_acct_descr%TYPE; v_budget_category fbors_salary_projs_tbl.budget_category%TYPE; v_budcategory_descr fbors_salary_projs_tbl.budcategory_descr%TYPE; v_title_code_descr vw_title_codes_appt.title_code_descr%TYPE; v_pay_begin_date fbors_salary_projs_tbl.pay_begin_date%TYPE; v_pay_end_date fbors_salary_projs_tbl.pay_end_date%TYPE; v_employee VARCHAR2(100) := ''; BEGIN -- v_accounting_period := 1; dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'STARTING'); v_grid_data := utl_url.unescape(p_grid_data); -- Parse through p_grid_data and determine whether to INSERT a new entry or UPDATE an exisiing one v_items := fbors_funclib.parse_string(p_in_string => v_grid_data, p_delim => '~'); --v_item_count := v_items.COUNT; /* -- Prevent the user from trying to create a projection in the past (should already be done on the front end) IF TO_DATE(to_char(v_items(K_BEGIN_PER) || '/01/' || v_current_year), 'MM/DD/YYYY') <= TO_DATE(fbors_funclib.max_closed_date, 'MM/DD/YYYY') THEN htp.p(p_callback || '( { "success":false, "message":"You may not start a projection in which the month has already been closed by Accounting.", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; */ -- Determine the projection's start / end year IF v_items(k_ADD_MOD) <> 'D' THEN IF v_items(K_BEGIN_PER) >= 7 THEN v_start_year := v_fiscal_year; ELSE v_start_year := v_current_year; END IF; IF v_items(K_END_PER) >= 7 THEN v_end_year := v_fiscal_year; ELSE v_end_year := v_current_year; END IF; END IF; -- Prevent 0 amounts for new additions on Salary Employees only IF p_proj_type = 'se' AND ( ( v_items(k_FTE) = 0 ) OR ( v_items(K_PAY_RATE) = 0 ) ) THEN v_save_message := v_save_message || '


Warning: Please note that when either the FTE or Pay Rate is 0, this effectively `turns off` the salary projection. '; END IF; -- Check to see if the updated FTE now exceeds 1.0 for this employee starting with the Start Month of the projection check_excessive_fte(v_items(k_EMPL_ID), v_result); v_save_message := v_save_message || v_result; -- MODIFY: If this projection was modified... IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN -- Determine the number of months between the start and end date for the annualized projection amount -- v_months_between := months_between(TO_DATE(v_items(K_BEGIN_PER) || v_start_year, 'Month YYYY'), LAST_DAY(TO_DATE(v_items(K_END_PER) || v_end_year, 'Month YYYY'))); v_months_between := v_items(K_END_PER) - v_items(K_BEGIN_PER); -- Audit this transaction before updating v_return := audit_transaction('fbors_salary_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'UPDATE'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- Update the data BEGIN dbms_application_info.set_action( 'Updating the Salary Projections table @line #' || $$PLSQL_LINE); UPDATE fbors_salary_projs_tbl p SET p.net_id = v_items(k_NET_ID), mod_fte = NVL(v_items(k_FTE), 0), mod_payrate = NVL(v_items(K_PAY_RATE), 0), calc_mod_payrate = ROUND(v_items(K_PAY_RATE)*v_items(k_FTE), 2), calc_mod_annual_payrate = ROUND(v_items(K_PAY_RATE)*v_items(k_FTE)*(v_months_between), 2), -- Need to convert the accounting period into a calendar month mod_begin_date = TO_DATE( CASE WHEN v_items(K_BEGIN_PER) <= 6 THEN v_items(K_BEGIN_PER) + 6 ELSE v_items(K_BEGIN_PER) - 6 END || ' ' || v_start_year, 'MM YYYY'), mod_end_date = LAST_DAY(TO_DATE( CASE WHEN v_items(K_END_PER) <= 6 THEN v_items(K_END_PER) + 6 ELSE v_items(K_END_PER) - 6 END || ' ' || v_end_year, 'MM YYYY')), comments = v_items(K_COMMENTS), system_generated = 'N', updated_by = p_net_id, updated_on = SYSDATE WHERE p.sequence_id = v_items(k_SEQ_ID); v_sql_rowcount := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- ADD: Insert a new projection ELSIF instr(v_items(k_ADD_MOD), 'A') > 0 THEN -- Validate the FAU IF fbors_funclib.validate_fau(v_items(K_ACCOUNT), v_items(K_ACTIVITY), v_items(K_FUND), v_items(K_FUNCTION)) < 4 THEN htp.p(p_callback || '( { "success":false, "message":"The FAU provided is incorrect. Please review your input, ' || v_items(K_ACCOUNT) || ' / ' || v_items(K_ACTIVITY) || ' / ' || v_items(K_FUND) || ' / ' || v_items(K_FUNCTION) || ' and try again. ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- Get the higher level Account values for easier querying on the Summary / Detail reports BEGIN SELECT DISTINCT a.ucr_acct_sum, a.ucr_acct_sum_dsc, a.ucr_acct_bdgt, a.ucr_acct_bdgt_dsc INTO v_summary_account, v_summary_acct_descr, v_budget_category, v_budcategory_descr FROM ps_ucr_acct_lv_tbl a WHERE a.ucr_acct = v_items(K_ACCOUNT); EXCEPTION WHEN OTHERS THEN htp.p(p_callback || '( { "success":false, "message":"An invalid Account was provided. Please select an Account from the list provided and try saving your projection again.", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Get the Title Code description BEGIN SELECT DISTINCT tc.title_code_descr INTO v_title_code_descr FROM vw_title_codes_appt tc WHERE tc.title_code= v_items(k_TITLE_CODE); EXCEPTION WHEN OTHERS THEN htp.p(p_callback || '( { "success":false, "message":"An invalid Title Code was provided. Please select a Title Code from the list provided and try saving your projection again.", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Get the employees's activity denormalized information -- get_actv_denorm_tree_values(v_items(K_ACTIVITY), v_org_code, v_div_code, v_dept_code, v_result); get_actv_denorm_tree_values(v_items(K_ACTIVITY), v_activity_denorm_tree_values, v_result); IF v_result IS NOT NULL THEN htp.p(p_callback || '( { "success":false, "message":"' || v_result || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- Check to see if the activity is within the user's EACS Accountability Structure check_activity_in_eacs(UPPER(v_items(K_ACTIVITY)), p_net_id, v_result); v_save_message := v_save_message || v_result; -- Convert the Begin/End periods into calendar dates -- For Projection Percentages, determine the projection's start / end year IF v_items(K_BEGIN_PER) < 7 THEN v_start_month := v_items(K_BEGIN_PER) + 6; v_start_year := v_current_year; ELSE v_start_month := v_items(K_BEGIN_PER) - 6; v_start_year := v_fiscal_year; END IF; IF v_items(K_END_PER) < 7 THEN v_end_month := v_items(K_END_PER) + 6; v_end_year := v_current_year; ELSE v_end_month := v_items(K_END_PER) - 6; v_end_year := v_fiscal_year; END IF; -- In case the Hourlt projection is for just a Title Code and no Employee ID or Name is given, the IF v_items(k_FIRST_NAME) IS NULL THEN v_items(k_FIRST_NAME) := 'None'; END IF; IF v_items(k_LAST_NAME) IS null THEN v_items(k_LAST_NAME) := 'None'; END IF; v_pay_begin_date := TO_DATE(v_start_month || '/01/' || v_start_year, 'MM/DD/YYYY'); v_pay_end_date := last_day(TO_DATE(v_end_month || '/01/' || v_end_year, 'MM/DD/YYYY')); v_seq_nextval := fbors_salary_projs_seq.nextval; -- The actual INSERT INTO BEGIN dbms_application_info.set_action( 'Inserting the Salary Projection data @line #'|| $$PLSQL_LINE ); INSERT INTO fbors_salary_projs_tbl (sequence_id, net_id, employee_id, first_name, last_name, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, FUNCTION, cost_center, project_id, title_code, title_code_descr, bargaining_unit, pay_begin_date, pay_end_date, mod_begin_date, mod_end_date, rate_code, fte, mod_fte, payrate, calc_payrate, mod_payrate, calc_mod_payrate, calc_annual_payrate, calc_mod_annual_payrate, comments, system_generated, hrdw_download_date, updated_by, updated_on) VALUES (v_seq_nextval, v_items(k_NET_ID), v_items(k_EMPL_ID), upper(v_items(k_FIRST_NAME)), upper(v_items(k_LAST_NAME)), v_fiscal_year, v_accounting_period, 0, --v_load_seq, v_items(K_ACCOUNT), v_summary_account, v_summary_acct_descr, v_budget_category, v_budcategory_descr, -- v_items(K_FUND), v_org_code, v_div_code, v_dept_code, v_items(K_ACTIVITY), v_items(K_FUND), v_activity_denorm_tree_values.org_code, v_activity_denorm_tree_values.div_code, v_activity_denorm_tree_values.dept_code, UPPER(v_items(K_ACTIVITY)), v_items(K_FUNCTION), '', '', -- v_cost_center, v_program_code, v_items(k_TITLE_CODE), v_title_code_descr, '99', null, null, -- pay_begin_date, pay_end_date v_pay_begin_date, v_pay_end_date, -- mod_begin_date, mod_end_date (CASE WHEN p_proj_type='se' THEN 'A' ELSE 'H' END), -- rate code 0, --fte v_items(k_FTE), -- mod_fte 0, 0, -- payrate, calc_payrate, v_items(K_PAY_RATE), round(v_items(k_FTE)*v_items(K_PAY_RATE), 2), -- mod_payrate, calc_mod_payrate, 0, -- calc_annual_payrate abs(round(v_items(K_PAY_RATE) * months_between(v_pay_begin_date, v_pay_end_date), 2)), -- calc_mod_annual_payrate v_items(K_COMMENTS), 'N', NULL, p_net_id, SYSDATE); v_sql_rowcount := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN -- v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, 'json'); v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Audit this transaction after INSERTing v_return := audit_transaction('fbors_salary_projs_tbl', v_seq_nextval, fbors_projs_audit_seq.nextval, p_net_id, 'INSERT'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; /* -- Audit this transaction before updating BEGIN dbms_application_info.set_action( 'Auditing the Salary Projections table @line #' || $$PLSQL_LINE); INSERT INTO fbors_salary_projs_audit (audit_id, audit_net_id, audit_datetime, audit_operation, sequence_id, net_id, employee_id, first_name, last_name, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, FUNCTION, cost_center, project_id, title_code, title_code_descr, bargaining_unit, pay_begin_date, pay_end_date, rate_code, fte, payrate, calc_payrate, calc_annual_payrate, mod_fte, mod_payrate, calc_mod_payrate, calc_mod_annual_payrate, mod_begin_date, mod_end_date, comments, system_generated, updated_by, updated_on, hrdw_download_date) SELECT fbors_projs_audit_seq.nextval, p_net_id, SYSDATE, 'INSERT', p.* FROM fbors_salary_projs_tbl p WHERE p.sequence_id = v_seq_nextval; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END;*/ -- DELETE an employee projection ELSIF instr(v_items(k_ADD_MOD), 'D') > 0 THEN /* -- Switch to the appropriate procedure to delete the projection v_url := gk_APPLICATION_URL || 'fbors_projections.delete_employee_proj' || '?p_callback=' || p_callback || '&p_net_id=' || p_net_id || '&p_employee_id=' || p_employee_id || '&p_grid_data=' || p_grid_data || '&p_proj_type=' || p_proj_type; owa_util.redirect_url(v_url, TRUE);*/ /* -- Audit this transaction before deleting BEGIN dbms_application_info.set_action( 'Auditing the Salary Projections table @line #' || $$PLSQL_LINE); INSERT INTO fbors_salary_projs_audit (audit_id, audit_net_id, audit_datetime, audit_operation, sequence_id, net_id, employee_id, first_name, last_name, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, FUNCTION, cost_center, project_id, title_code, title_code_descr, bargaining_unit, pay_begin_date, pay_end_date, rate_code, fte, payrate, calc_payrate, calc_annual_payrate, mod_fte, mod_payrate, calc_mod_payrate, calc_mod_annual_payrate, mod_begin_date, mod_end_date, comments, system_generated, updated_by, updated_on, hrdw_download_date) SELECT fbors_projs_audit_seq.nextval, p_net_id, SYSDATE, 'DELETE', p.* FROM fbors_salary_projs_tbl p WHERE p.sequence_id = v_items(k_SEQ_ID); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; */ -- Audit this transaction before DELETing v_return := audit_transaction('fbors_salary_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'DELETE'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; dbms_application_info.set_action( 'Deleting from the Salary Projections table @line #' || $$PLSQL_LINE); BEGIN DELETE FROM fbors_salary_projs_tbl p WHERE p.sequence_id = v_items(k_SEQ_ID); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; v_sql_rowcount := SQL%ROWCOUNT; COMMIT; /*htp.p(p_callback || '( { "success":true, "message":' || CASE WHEN v_sql_rowcount > 0 THEN ' "Your salaried employee projection has been deleted. ' ELSE ' "Your salaried employee projection was NOT deleted. ' END || ' ",' || '"totalCount": ' || v_sql_rowcount || ' } )') ;*/ END IF; -- IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN COMMIT; -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); IF v_items(k_EMPL_ID) = '999999999' THEN v_employee := ' "Your new projection for a new employee'; ELSE v_employee := ' "Your projection for salaried employee ' || v_items(k_FIRST_NAME) || ' ' || v_items(k_LAST_NAME); END if; htp.p(p_callback || '( { "success":true, "message":' || CASE WHEN v_sql_rowcount > 0 THEN v_employee || ' has been ' || action2word(v_items(k_ADD_MOD)) || ' for ' || fbors_funclib.accounting_period_month_name(v_accounting_period) || ' / ' || v_current_year || '.' ELSE v_employee || ' was NOT ' || action2word(v_items(k_ADD_MOD)) || '. ' END || v_save_message || ' ",' || ' "totalCount": ' || v_sql_rowcount || ', "runTime": "' || v_run_time || '" } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); -- Not so good :( EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END save_salary_emp_proj; PROCEDURE save_hourly_emp_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'he', p_grid_data IN VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : Save Salaried Employees Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To save projections to the FBORS_SALARY_PROJS_TBL table Params : p_grid_data : the list of FORM elements passed as a javasript array into a single parameter here M~2~alanr~856788673~ALAN~ROY~7277~0.25~1500~July~January~Updated projection for Alan July - January~500110~A01369~66095~72 A~-1~~BR~GR~3074~1~1500~September~September~Added by Brian~500110~A01375~19900~72 UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ -- Array positions to parse through p_grid_data k_ADD_MOD CONSTANT INTEGER := 1; k_SEQ_ID CONSTANT INTEGER := 2; k_NET_ID CONSTANT INTEGER := 3; k_EMPL_ID CONSTANT INTEGER := 4; k_FIRST_NAME CONSTANT INTEGER := 5; k_LAST_NAME CONSTANT INTEGER := 6; k_FTE CONSTANT INTEGER := 7;-- This field is over-loaded for MONTHLY HOURS k_TITLE_CODE CONSTANT INTEGER := 8; K_ACCOUNT CONSTANT INTEGER := 9; K_ACTIVITY CONSTANT INTEGER := 10; K_FUND CONSTANT INTEGER := 11; K_FUNCTION CONSTANT INTEGER := 12; K_HOURS CONSTANT INTEGER := 13; K_PAY_RATE CONSTANT INTEGER := 14; K_TOTAL CONSTANT INTEGER := 15; K_BEGIN_PER CONSTANT INTEGER := 16; K_END_PER CONSTANT INTEGER := 17; K_COMMENTS CONSTANT INTEGER := 18; -- M~53770~sbiggers~851878503~SCOTT~BIGGERSTAFF~9899~501110~A01210~19900~72~0~520.19~0~1~12~System generated projection -- test -- M~55378~feleciaw~854006169~DARLENE~WILSON~1~7230~500110~A01210~19900~72~160~25.65~$4,104.00~7~12~System generated projection - test -- A~-1~larryw~856232490~Larry~Whittaker~7275~501110~A01210~19900~72~0.5~12~0~11~12~test -- Projection Start and End Dates --v_start_fiscal_year fbors_salary_projs_tbl.fiscal_year%TYPE; --v_start_accounting_period fbors_salary_projs_tbl.accounting_period%TYPE; v_summary_account fbors_salary_projs_tbl.summary_account%TYPE; v_summary_acct_descr fbors_salary_projs_tbl.summary_acct_descr%TYPE; v_budget_category fbors_salary_projs_tbl.budget_category%TYPE; v_budcategory_descr fbors_salary_projs_tbl.budcategory_descr%TYPE; v_title_code_descr vw_title_codes_appt.title_code_descr%TYPE; v_pay_begin_date fbors_salary_projs_tbl.pay_begin_date%TYPE; v_pay_end_date fbors_salary_projs_tbl.pay_end_date%TYPE; v_employee VARCHAR2(100) := ''; BEGIN -- v_accounting_period := 1; dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'STARTING'); v_grid_data := utl_url.unescape(p_grid_data); dbms_output.put_line(v_grid_data); -- Parse through p_grid_data and determine whether to INSERT a new entry or UPDATE an exisiing one v_items := fbors_funclib.parse_string(p_in_string => v_grid_data, p_delim => '~'); --v_item_count := v_items.COUNT; /* -- Prevent the user from trying to create a projection in the past (should already be done on the front end) IF TO_DATE(to_char(v_items(K_BEGIN_PER) || '/01/' || v_current_year), 'MM/DD/YYYY') <= TO_DATE(fbors_funclib.max_closed_date, 'MM/DD/YYYY') THEN htp.p(p_callback || '( { "success":false, "message":"You may not start a projection in which the month has already been closed by Accounting.", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; */ -- Determine the projection's start / end year IF v_items(k_ADD_MOD) <> 'D' THEN IF v_items(K_BEGIN_PER) >= 7 THEN v_start_year := v_fiscal_year; ELSE v_start_year := v_current_year; END IF; IF v_items(K_END_PER) >= 7 THEN v_end_year := v_fiscal_year; ELSE v_end_year := v_current_year; END IF; END IF; -- Prevent 0 amounts for new additions on Salary Employees only IF p_proj_type = 'he' AND ( ( v_items(k_FTE) = 0 ) OR ( v_items(K_PAY_RATE) = 0 ) ) THEN v_save_message := v_save_message || '


Warning: Please note that when either the FTE or Pay Rate is 0, this effectively `turns off` the salary projection. '; END IF; -- Check to see if the updated FTE now exceeds 1.0 for this employee starting with the Start Month of the projection check_excessive_fte(v_items(k_EMPL_ID), v_result); v_save_message := v_save_message || v_result; -- MODIFY: If this projection was modified... IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN -- Determine the number of months between the start and end date for the annualized projection amount -- v_months_between := months_between(TO_DATE(v_items(K_BEGIN_PER) || v_start_year, 'Month YYYY'), LAST_DAY(TO_DATE(v_items(K_END_PER) || v_end_year, 'Month YYYY'))); v_months_between := v_items(K_END_PER) - v_items(K_BEGIN_PER); -- Audit this transaction before updating v_return := audit_transaction('fbors_salary_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'UPDATE'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- Update the data BEGIN dbms_application_info.set_action( 'Updating the Salary Projections table @line #' || $$PLSQL_LINE); UPDATE fbors_salary_projs_tbl p SET p.net_id = v_items(k_NET_ID), mod_fte = NVL(v_items(k_FTE), 0), mod_payrate = NVL(v_items(K_PAY_RATE), 0), monthly_hours = v_items(k_hours), calc_mod_payrate = ROUND(v_items(K_PAY_RATE)*v_items(k_FTE), 2), calc_mod_annual_payrate = ROUND(v_items(K_PAY_RATE)*v_items(k_FTE)*(v_months_between), 2), -- Need to convert the accounting period into a calendar month mod_begin_date = TO_DATE( CASE WHEN v_items(K_BEGIN_PER) <= 6 THEN v_items(K_BEGIN_PER) + 6 ELSE v_items(K_BEGIN_PER) - 6 END || ' ' || v_start_year, 'MM YYYY'), mod_end_date = LAST_DAY(TO_DATE( CASE WHEN v_items(K_END_PER) <= 6 THEN v_items(K_END_PER) + 6 ELSE v_items(K_END_PER) - 6 END || ' ' || v_end_year, 'MM YYYY')), comments = v_items(K_COMMENTS), system_generated = 'N', updated_by = p_net_id, updated_on = SYSDATE WHERE p.sequence_id = v_items(k_SEQ_ID); v_sql_rowcount := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- ADD: Insert a new projection ELSIF instr(v_items(k_ADD_MOD), 'A') > 0 THEN -- Validate the FAU IF fbors_funclib.validate_fau(v_items(K_ACCOUNT), v_items(K_ACTIVITY), v_items(K_FUND), v_items(K_FUNCTION)) < 4 THEN htp.p(p_callback || '( { "success":false, "message":"The FAU provided is incorrect. Please review your input, ' || v_items(K_ACCOUNT) || ' / ' || v_items(K_ACTIVITY) || ' / ' || v_items(K_FUND) || ' / ' || v_items(K_FUNCTION) || ' and try again. ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- Get the higher level Account values for easier querying on the Summary / Detail reports BEGIN SELECT DISTINCT a.ucr_acct_sum, a.ucr_acct_sum_dsc, a.ucr_acct_bdgt, a.ucr_acct_bdgt_dsc INTO v_summary_account, v_summary_acct_descr, v_budget_category, v_budcategory_descr FROM ps_ucr_acct_lv_tbl a WHERE a.ucr_acct = v_items(K_ACCOUNT); EXCEPTION WHEN OTHERS THEN htp.p(p_callback || '( { "success":false, "message":"An invalid Account was provided. Please select an Account from the list provided and try saving your projection again.", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Get the Title Code description BEGIN SELECT DISTINCT tc.title_code_descr INTO v_title_code_descr FROM vw_title_codes_appt tc WHERE tc.title_code= v_items(k_TITLE_CODE); EXCEPTION WHEN OTHERS THEN htp.p(p_callback || '( { "success":false, "message":"An invalid Title Code was provided. Please select a Title Code from the list provided and try saving your projection again.", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Get the employees's activity denormalized information -- get_actv_denorm_tree_values(v_items(K_ACTIVITY), v_org_code, v_div_code, v_dept_code, v_result); get_actv_denorm_tree_values(v_items(K_ACTIVITY), v_activity_denorm_tree_values, v_result); IF v_result IS NOT NULL THEN htp.p(p_callback || '( { "success":false, "message":"' || v_result || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- Check to see if the activity is within the user's EACS Accountability Structure check_activity_in_eacs(UPPER(v_items(K_ACTIVITY)), p_net_id, v_result); v_save_message := v_save_message || v_result; -- Convert the Begin/End periods into calendar dates -- For Projection Percentages, determine the projection's start / end year IF v_items(K_BEGIN_PER) < 7 THEN v_start_month := v_items(K_BEGIN_PER) + 6; v_start_year := v_current_year; ELSE v_start_month := v_items(K_BEGIN_PER) - 6; v_start_year := v_fiscal_year; END IF; IF v_items(K_END_PER) < 7 THEN v_end_month := v_items(K_END_PER) + 6; v_end_year := v_current_year; ELSE v_end_month := v_items(K_END_PER) - 6; v_end_year := v_fiscal_year; END IF; -- In case the Hourlt projection is for just a Title Code and no Employee ID or Name is given, the IF v_items(k_FIRST_NAME) IS NULL THEN v_items(k_FIRST_NAME) := 'None'; END IF; IF v_items(k_LAST_NAME) IS null THEN v_items(k_LAST_NAME) := 'None'; END IF; v_pay_begin_date := TO_DATE(v_start_month || '/01/' || v_start_year, 'MM/DD/YYYY'); v_pay_end_date := last_day(TO_DATE(v_end_month || '/01/' || v_end_year, 'MM/DD/YYYY')); v_seq_nextval := fbors_salary_projs_seq.nextval; -- The actual INSERT INTO BEGIN dbms_application_info.set_action( 'Inserting the Salary Projection data @line #'|| $$PLSQL_LINE ); INSERT INTO fbors_salary_projs_tbl (sequence_id, net_id, employee_id, first_name, last_name, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, FUNCTION, cost_center, project_id, title_code, title_code_descr, bargaining_unit, pay_begin_date, pay_end_date, mod_begin_date, mod_end_date, rate_code, monthly_hours, fte, mod_fte, payrate, calc_payrate, mod_payrate, calc_mod_payrate, calc_annual_payrate, calc_mod_annual_payrate, comments, system_generated, hrdw_download_date, updated_by, updated_on) VALUES (v_seq_nextval, v_items(k_NET_ID), v_items(k_EMPL_ID), upper(v_items(k_FIRST_NAME)), upper(v_items(k_LAST_NAME)), v_fiscal_year, v_accounting_period, 0, --v_load_seq, v_items(K_ACCOUNT), v_summary_account, v_summary_acct_descr, v_budget_category, v_budcategory_descr, -- v_items(K_FUND), v_org_code, v_div_code, v_dept_code, v_items(K_ACTIVITY), v_items(K_FUND), v_activity_denorm_tree_values.org_code, v_activity_denorm_tree_values.div_code, v_activity_denorm_tree_values.dept_code, UPPER(v_items(K_ACTIVITY)), v_items(K_FUNCTION), '', '', -- v_cost_center, v_program_code, v_items(k_TITLE_CODE), v_title_code_descr, '99', null, null, -- pay_begin_date, pay_end_date v_pay_begin_date, v_pay_end_date, -- mod_begin_date, mod_end_date (CASE WHEN p_proj_type='he' THEN 'H' ELSE 'A' END), -- rate code v_items(k_hours), -- Monthly_hours 0, --fte v_items(k_FTE), -- mod_fte 0, 0, -- payrate, calc_payrate, v_items(K_PAY_RATE), round(v_items(k_FTE)*v_items(K_PAY_RATE), 2), -- mod_payrate, calc_mod_payrate, 0, -- calc_annual_payrate abs(round(v_items(K_PAY_RATE) * months_between(v_pay_begin_date, v_pay_end_date), 2)), -- calc_mod_annual_payrate v_items(K_COMMENTS), 'N', NULL, p_net_id, SYSDATE); v_sql_rowcount := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN -- v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, 'json'); v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Audit this transaction after INSERTing v_return := audit_transaction('fbors_salary_projs_tbl', v_seq_nextval, fbors_projs_audit_seq.nextval, p_net_id, 'INSERT'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; /* -- Audit this transaction before updating BEGIN dbms_application_info.set_action( 'Auditing the Salary Projections table @line #' || $$PLSQL_LINE); INSERT INTO fbors_salary_projs_audit (audit_id, audit_net_id, audit_datetime, audit_operation, sequence_id, net_id, employee_id, first_name, last_name, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, FUNCTION, cost_center, project_id, title_code, title_code_descr, bargaining_unit, pay_begin_date, pay_end_date, rate_code, fte, payrate, calc_payrate, calc_annual_payrate, mod_fte, mod_payrate, calc_mod_payrate, calc_mod_annual_payrate, mod_begin_date, mod_end_date, comments, system_generated, updated_by, updated_on, hrdw_download_date) SELECT fbors_projs_audit_seq.nextval, p_net_id, SYSDATE, 'INSERT', p.* FROM fbors_salary_projs_tbl p WHERE p.sequence_id = v_seq_nextval; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END;*/ -- DELETE an employee projection ELSIF instr(v_items(k_ADD_MOD), 'D') > 0 THEN /* -- Switch to the appropriate procedure to delete the projection v_url := gk_APPLICATION_URL || 'fbors_projections.delete_employee_proj' || '?p_callback=' || p_callback || '&p_net_id=' || p_net_id || '&p_employee_id=' || p_employee_id || '&p_grid_data=' || p_grid_data || '&p_proj_type=' || p_proj_type; owa_util.redirect_url(v_url, TRUE);*/ /* -- Audit this transaction before deleting BEGIN dbms_application_info.set_action( 'Auditing the Salary Projections table @line #' || $$PLSQL_LINE); INSERT INTO fbors_salary_projs_audit (audit_id, audit_net_id, audit_datetime, audit_operation, sequence_id, net_id, employee_id, first_name, last_name, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, FUNCTION, cost_center, project_id, title_code, title_code_descr, bargaining_unit, pay_begin_date, pay_end_date, rate_code, fte, payrate, calc_payrate, calc_annual_payrate, mod_fte, mod_payrate, calc_mod_payrate, calc_mod_annual_payrate, mod_begin_date, mod_end_date, comments, system_generated, updated_by, updated_on, hrdw_download_date) SELECT fbors_projs_audit_seq.nextval, p_net_id, SYSDATE, 'DELETE', p.* FROM fbors_salary_projs_tbl p WHERE p.sequence_id = v_items(k_SEQ_ID); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; */ -- Audit this transaction before DELETing v_return := audit_transaction('fbors_salary_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'DELETE'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; dbms_application_info.set_action( 'Deleting from the Salary Projections table @line #' || $$PLSQL_LINE); BEGIN DELETE FROM fbors_salary_projs_tbl p WHERE p.sequence_id = v_items(k_SEQ_ID); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; v_sql_rowcount := SQL%ROWCOUNT; COMMIT; /*htp.p(p_callback || '( { "success":true, "message":' || CASE WHEN v_sql_rowcount > 0 THEN ' "Your salaried employee projection has been deleted. ' ELSE ' "Your salaried employee projection was NOT deleted. ' END || ' ",' || '"totalCount": ' || v_sql_rowcount || ' } )') ;*/ END IF; -- IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN COMMIT; -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); IF v_items(k_EMPL_ID) = '999999999' THEN v_employee := ' "Your new projection for a new employee'; ELSE v_employee := ' "Your projection for salaried employee ' || v_items(k_FIRST_NAME) || ' ' || v_items(k_LAST_NAME); END if; htp.p(p_callback || '( { "success":true, "message":' || CASE WHEN v_sql_rowcount > 0 THEN v_employee || ' has been ' || action2word(v_items(k_ADD_MOD)) || ' for ' || fbors_funclib.accounting_period_month_name(v_accounting_period) || ' / ' || v_current_year || '.' ELSE v_employee || ' was NOT ' || action2word(v_items(k_ADD_MOD)) || '. ' END || v_save_message || ' ",' || ' "totalCount": ' || v_sql_rowcount || ', "runTime": "' || v_run_time || '" } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); -- Not so good :( EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END save_hourly_emp_proj; PROCEDURE save_student_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'hs', p_grid_data IN VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : Save Salaried Employees Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To save projections to the FBORS_SALARY_PROJS_TBL table Params : p_grid_data : the list of FORM elements passed as a javasript array into a single parameter here M~2~alanr~856788673~ALAN~ROY~7277~0.25~1500~July~January~Updated projection for Alan July - January~500110~A01369~66095~72 A~-1~~BR~GR~3074~1~1500~September~September~Added by Brian~500110~A01375~19900~72 UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ -- Array positions to parse through p_grid_data k_ADD_MOD CONSTANT INTEGER := 1; k_SEQ_ID CONSTANT INTEGER := 2; k_NET_ID CONSTANT INTEGER := 3; k_SUM_FTE CONSTANT INTEGER := 4; -- This field is over-loaded for MONTHLY HOURS k_TITLE_CODE CONSTANT INTEGER := 5; K_ACCOUNT CONSTANT INTEGER := 6; K_ACTIVITY CONSTANT INTEGER := 7; K_FUND CONSTANT INTEGER := 8; K_FUNCTION CONSTANT INTEGER := 9; K_MOD_FTE CONSTANT INTEGER := 10; K_AVG_WAGE CONSTANT INTEGER := 11; K_ADJ_PCT CONSTANT INTEGER := 12; K_PROJ_AMT CONSTANT INTEGER := 13; K_BEGIN_PER CONSTANT INTEGER := 14; K_END_PER CONSTANT INTEGER := 15; K_COMMENTS CONSTANT INTEGER := 16; -- A~-1~none~0.5~1044~501110~A01206~19900~72~0~12~11~6.66~11~12~test~ -- M~55862~none~0.06~4922~504120~A01207~66160~72~~10~11~0.67~7~12~System generated projection - test~ -- Projection Start and End Dates --v_start_fiscal_year fbors_salary_projs_tbl.fiscal_year%TYPE; --v_start_accounting_period fbors_salary_projs_tbl.accounting_period%TYPE; v_summary_account fbors_salary_projs_tbl.summary_account%TYPE; v_summary_acct_descr fbors_salary_projs_tbl.summary_acct_descr%TYPE; v_budget_category fbors_salary_projs_tbl.budget_category%TYPE; v_budcategory_descr fbors_salary_projs_tbl.budcategory_descr%TYPE; v_title_code_descr vw_title_codes_appt.title_code_descr%TYPE; v_pay_begin_date fbors_salary_projs_tbl.pay_begin_date%TYPE; v_pay_end_date fbors_salary_projs_tbl.pay_end_date%TYPE; v_employee VARCHAR2(100) := ''; v_sql VARCHAR2(4000) := ''; BEGIN -- v_accounting_period := 1; dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'STARTING'); v_grid_data := utl_url.unescape(p_grid_data); -- Parse through p_grid_data and determine whether to INSERT a new entry or UPDATE an exisiing one v_items := fbors_funclib.parse_string(p_in_string => v_grid_data, p_delim => '~'); --v_item_count := v_items.COUNT; /* -- Prevent the user from trying to create a projection in the past (should already be done on the front end) IF TO_DATE(to_char(v_items(K_BEGIN_PER) || '/01/' || v_current_year), 'MM/DD/YYYY') <= TO_DATE(fbors_funclib.max_closed_date, 'MM/DD/YYYY') THEN htp.p(p_callback || '( { "success":false, "message":"You may not start a projection in which the month has already been closed by Accounting.", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; */ -- Determine the projection's start / end year IF v_items(K_BEGIN_PER) >= 7 THEN v_start_year := v_fiscal_year; ELSE v_start_year := v_current_year; END IF; IF v_items(K_END_PER) >= 7 THEN v_end_year := v_fiscal_year; ELSE v_end_year := v_current_year; END IF; -- Prevent 0 amounts for new additions on Salary Employees only IF p_proj_type = 'se' AND ( ( v_items(k_SUM_FTE) = 0 ) OR ( v_items(K_AVG_WAGE) = 0 ) ) THEN v_save_message := v_save_message || '


Warning: Please note that when either the FTE or Pay Rate is 0, this effectively `turns off` the salary projection. '; END IF; -- Check to see if the updated FTE now exceeds 1.0 for this employee starting with the Start Month of the projection --check_excessive_fte(v_items(k_EMPL_ID), v_result); v_save_message := v_save_message || v_result; -- MODIFY: If this projection was modified... IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN -- Determine the number of months between the start and end date for the annualized projection amount -- v_months_between := months_between(TO_DATE(v_items(K_BEGIN_PER) || v_start_year, 'Month YYYY'), LAST_DAY(TO_DATE(v_items(K_END_PER) || v_end_year, 'Month YYYY'))); v_months_between := v_items(K_END_PER) - v_items(K_BEGIN_PER); -- Audit this transaction before updating v_return := audit_transaction('fbors_salary_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'UPDATE'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- Update the data BEGIN dbms_application_info.set_action( 'Updating the Salary Projections table @line #' || $$PLSQL_LINE); UPDATE fbors_hourly_projs_tbl p SET --p.net_id = v_items(k_NET_ID), mod_sum_fte = NVL(v_items(k_SUM_FTE), 0), mod_avg_hourly_wage = NVL(v_items(K_AVG_WAGE), 0), mod_proj_amt = NVL(v_items(K_AVG_WAGE),0), adj_proj_amt_pct = NVL(v_items(K_ADJ_PCT),0), -- Need to convert the accounting period into a calendar month mod_begin_date = TO_DATE( CASE WHEN v_items(K_BEGIN_PER) <= 6 THEN v_items(K_BEGIN_PER) + 6 ELSE v_items(K_BEGIN_PER) - 6 END || ' ' || v_start_year, 'MM YYYY'), mod_end_date = LAST_DAY(TO_DATE( CASE WHEN v_items(K_END_PER) <= 6 THEN v_items(K_END_PER) + 6 ELSE v_items(K_END_PER) - 6 END || ' ' || v_end_year, 'MM YYYY')), comments = v_items(K_COMMENTS), system_generated = 'N', updated_by = p_net_id, updated_on = SYSDATE WHERE p.sequence_id = v_items(k_SEQ_ID); v_sql_rowcount := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- ADD: Insert a new projection ELSIF instr(v_items(k_ADD_MOD), 'A') > 0 THEN -- Validate the FAU IF fbors_funclib.validate_fau(v_items(K_ACCOUNT), v_items(K_ACTIVITY), v_items(K_FUND), v_items(K_FUNCTION)) < 4 THEN htp.p(p_callback || '( { "success":false, "message":"The FAU provided is incorrect. Please review your input, ' || v_items(K_ACCOUNT) || ' / ' || v_items(K_ACTIVITY) || ' / ' || v_items(K_FUND) || ' / ' || v_items(K_FUNCTION) || ' and try again. ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- Get the higher level Account values for easier querying on the Summary / Detail reports BEGIN SELECT DISTINCT a.ucr_acct_sum, a.ucr_acct_sum_dsc, a.ucr_acct_bdgt, a.ucr_acct_bdgt_dsc INTO v_summary_account, v_summary_acct_descr, v_budget_category, v_budcategory_descr FROM ps_ucr_acct_lv_tbl a WHERE a.ucr_acct = v_items(K_ACCOUNT); EXCEPTION WHEN OTHERS THEN htp.p(p_callback || '( { "success":false, "message":"An invalid Account was provided. Please select an Account from the list provided and try saving your projection again.", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Get the Title Code description BEGIN SELECT DISTINCT tc.title_code_descr INTO v_title_code_descr FROM vw_title_codes_appt tc WHERE tc.title_code= v_items(k_TITLE_CODE); EXCEPTION WHEN OTHERS THEN htp.p(p_callback || '( { "success":false, "message":"An invalid Title Code was provided. Please select a Title Code from the list provided and try saving your projection again.", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Get the employees's activity denormalized information -- get_actv_denorm_tree_values(v_items(K_ACTIVITY), v_org_code, v_div_code, v_dept_code, v_result); get_actv_denorm_tree_values(v_items(K_ACTIVITY), v_activity_denorm_tree_values, v_result); IF v_result IS NOT NULL THEN htp.p(p_callback || '( { "success":false, "message":"' || v_result || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- Check to see if the activity is within the user's EACS Accountability Structure check_activity_in_eacs(UPPER(v_items(K_ACTIVITY)), p_net_id, v_result); v_save_message := v_save_message || v_result; -- Convert the Begin/End periods into calendar dates -- For Projection Percentages, determine the projection's start / end year IF v_items(K_BEGIN_PER) < 7 THEN v_start_month := v_items(K_BEGIN_PER) + 6; v_start_year := v_current_year; ELSE v_start_month := v_items(K_BEGIN_PER) - 6; v_start_year := v_fiscal_year; END IF; IF v_items(K_END_PER) < 7 THEN v_end_month := v_items(K_END_PER) + 6; v_end_year := v_current_year; ELSE v_end_month := v_items(K_END_PER) - 6; v_end_year := v_fiscal_year; END IF; -- In case the Hourlt projection is for just a Title Code and no Employee ID or Name is given, the /* IF v_items(k_FIRST_NAME) IS NULL THEN v_items(k_FIRST_NAME) := 'None'; END IF; IF v_items(k_LAST_NAME) IS null THEN v_items(k_LAST_NAME) := 'None'; END IF;*/ v_pay_begin_date := TO_DATE(v_start_month || '/01/' || v_start_year, 'MM/DD/YYYY'); v_pay_end_date := last_day(TO_DATE(v_end_month || '/01/' || v_end_year, 'MM/DD/YYYY')); v_seq_nextval := fbors_salary_projs_seq.nextval; -- The actual INSERT INTO BEGIN dbms_application_info.set_action( 'Inserting the Salary Projection data @line #'|| $$PLSQL_LINE ); v_sql :=' INSERT INTO fbors_hourly_projs_tbl (sequence_id, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, function, title_code, title_code_descr, bargaining_unit, dos, sum_fte, avg_hourly_wage, calc_proj_amt, mod_sum_fte, mod_avg_hourly_wage, mod_proj_amt, adj_proj_amt_pct, mod_begin_date, mod_end_date, comments, system_generated, updated_by, updated_on, hrdw_download_date) VALUES (' || v_seq_nextval ||',' || v_fiscal_year||',' || v_accounting_period||',' || 0||',' || --v_load_seq||',' || v_items(K_ACCOUNT)||',' || v_summary_account||',' || v_summary_acct_descr||',' || v_budget_category||',' || v_budcategory_descr||',' || v_items(K_FUND)||',' || v_activity_denorm_tree_values.org_code||',' || v_activity_denorm_tree_values.div_code||',' || v_activity_denorm_tree_values.dept_code||',' || UPPER(v_items(K_ACTIVITY))||',' || v_items(K_FUNCTION)||',' || v_items(k_TITLE_CODE)||',' || v_title_code_descr||',''99'',''REG'' ' || -- DOS Code v_items(k_SUM_FTE)||',' || --sum_fte v_items(K_AVG_WAGE)||',' || -- avg_hourly_wage v_items(K_PROJ_AMT)||',' || -- calc_proj_amt v_items(k_SUM_FTE)||',' || -- mod_sum_fte v_items(K_AVG_WAGE)||',' || -- mod_avg_hourly_wage v_items(K_PROJ_AMT)||',' || -- mod_proj_amt v_items(K_ADJ_PCT)||',' || -- adj_proj_amt v_pay_begin_date||',' || -- mod_begin_date v_pay_end_date||',' || -- mod_end_date v_items(K_COMMENTS)||',| ''N'', NULL,' || p_net_id||',SYSDATE)'; dbms_output.put_line(v_sql); INSERT INTO fbors_hourly_projs_tbl (sequence_id, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, function, title_code, title_code_descr, bargaining_unit, dos, sum_fte, avg_hourly_wage, calc_proj_amt, mod_sum_fte, mod_avg_hourly_wage, mod_proj_amt, adj_proj_amt_pct, mod_begin_date, mod_end_date, comments, system_generated, updated_by, updated_on, hrdw_download_date) VALUES (v_seq_nextval, v_fiscal_year, v_accounting_period, 0, --v_load_seq, v_items(K_ACCOUNT), v_summary_account, v_summary_acct_descr, v_budget_category, v_budcategory_descr, v_items(K_FUND), v_activity_denorm_tree_values.org_code, v_activity_denorm_tree_values.div_code, v_activity_denorm_tree_values.dept_code, UPPER(v_items(K_ACTIVITY)), v_items(K_FUNCTION), v_items(k_TITLE_CODE), v_title_code_descr, '99', 'REG', -- DOS Code v_items(k_SUM_FTE), --sum_fte v_items(K_AVG_WAGE), -- avg_hourly_wage v_items(K_PROJ_AMT), -- calc_proj_amt v_items(k_SUM_FTE), -- mod_sum_fte v_items(K_AVG_WAGE), -- mod_avg_hourly_wage v_items(K_PROJ_AMT), -- mod_proj_amt v_items(K_ADJ_PCT), -- adj_proj_amt v_pay_begin_date, -- mod_begin_date v_pay_end_date, -- mod_end_date v_items(K_COMMENTS), 'N', p_net_id, SYSDATE, NULL); v_sql_rowcount := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN -- v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, 'json'); v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Audit this transaction after INSERTing v_return := audit_transaction('fbors_salary_projs_tbl', v_seq_nextval, fbors_projs_audit_seq.nextval, p_net_id, 'INSERT'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; /* -- Audit this transaction before updating BEGIN dbms_application_info.set_action( 'Auditing the Salary Projections table @line #' || $$PLSQL_LINE); INSERT INTO fbors_salary_projs_audit (audit_id, audit_net_id, audit_datetime, audit_operation, sequence_id, net_id, employee_id, first_name, last_name, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, FUNCTION, cost_center, project_id, title_code, title_code_descr, bargaining_unit, pay_begin_date, pay_end_date, rate_code, fte, payrate, calc_payrate, calc_annual_payrate, mod_fte, mod_payrate, calc_mod_payrate, calc_mod_annual_payrate, mod_begin_date, mod_end_date, comments, system_generated, updated_by, updated_on, hrdw_download_date) SELECT fbors_projs_audit_seq.nextval, p_net_id, SYSDATE, 'INSERT', p.* FROM fbors_salary_projs_tbl p WHERE p.sequence_id = v_seq_nextval; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END;*/ -- DELETE an employee projection ELSIF instr(v_items(k_ADD_MOD), 'D') > 0 THEN /* -- Switch to the appropriate procedure to delete the projection v_url := gk_APPLICATION_URL || 'fbors_projections.delete_employee_proj' || '?p_callback=' || p_callback || '&p_net_id=' || p_net_id || '&p_employee_id=' || p_employee_id || '&p_grid_data=' || p_grid_data || '&p_proj_type=' || p_proj_type; owa_util.redirect_url(v_url, TRUE);*/ /* -- Audit this transaction before deleting BEGIN dbms_application_info.set_action( 'Auditing the Salary Projections table @line #' || $$PLSQL_LINE); INSERT INTO fbors_salary_projs_audit (audit_id, audit_net_id, audit_datetime, audit_operation, sequence_id, net_id, employee_id, first_name, last_name, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, FUNCTION, cost_center, project_id, title_code, title_code_descr, bargaining_unit, pay_begin_date, pay_end_date, rate_code, fte, payrate, calc_payrate, calc_annual_payrate, mod_fte, mod_payrate, calc_mod_payrate, calc_mod_annual_payrate, mod_begin_date, mod_end_date, comments, system_generated, updated_by, updated_on, hrdw_download_date) SELECT fbors_projs_audit_seq.nextval, p_net_id, SYSDATE, 'DELETE', p.* FROM fbors_salary_projs_tbl p WHERE p.sequence_id = v_items(k_SEQ_ID); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; */ -- Audit this transaction before DELETing v_return := audit_transaction('fbors_salary_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'DELETE'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; dbms_application_info.set_action( 'Deleting from the Salary Projections table @line #' || $$PLSQL_LINE); BEGIN DELETE FROM fbors_hourly_projs_tbl p WHERE p.sequence_id = v_items(k_SEQ_ID); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; v_sql_rowcount := SQL%ROWCOUNT; COMMIT; /*htp.p(p_callback || '( { "success":true, "message":' || CASE WHEN v_sql_rowcount > 0 THEN ' "Your salaried employee projection has been deleted. ' ELSE ' "Your salaried employee projection was NOT deleted. ' END || ' ",' || '"totalCount": ' || v_sql_rowcount || ' } )') ;*/ END IF; -- IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN COMMIT; /* -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); IF v_items(k_EMPL_ID) = '999999999' THEN v_employee := ' "Your new projection for a new employee'; ELSE v_employee := ' "Your projection for salaried employee ' || v_items(k_FIRST_NAME) || ' ' || v_items(k_LAST_NAME); END if;*/ htp.p(p_callback || '( { "success":true, "message":' || CASE WHEN v_sql_rowcount > 0 THEN '"The projection has been ' || action2word(v_items(k_ADD_MOD)) || ' for ' || fbors_funclib.accounting_period_month_name(v_accounting_period) || ' / ' || v_current_year || '.' ELSE v_employee || ' was NOT ' || action2word(v_items(k_ADD_MOD)) || '. ' END || v_save_message || ' ",' || ' "totalCount": ' || v_sql_rowcount || ', "runTime": "' || v_run_time || '" } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); -- Not so good :( EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END save_student_proj; PROCEDURE save_nonsal_proj ( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'ns', p_grid_data IN VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : Save NON Salaried Projection Data Author : Larry W, Brian G Created : Rocktober 2012 Purpose : To save projections to the FBORS_NONSALARY_PROJS_TBL table Params : p_grid_data : the list of FORM elements passed as a javasript array into a single parameter here Example : M~1~undefined~BC40~19900~A01369~72~806~806~false~0~268.67~false~5~6~0~false~5~12~System generated.~true~ UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ -- Array positions to parse through p_grid_data -- These constants match the front ends FORM fields by ID / name -- Example: M~-1~undefined~BC40~19900~A01369~72~0~806~0~268.67~false~10~true~5~6~0~false~5~12~...~false~ -- Initial k_ADD_MOD CONSTANT INTEGER := 1; k_SEQ_ID CONSTANT INTEGER := 2; k_UNDEFINED CONSTANT INTEGER := 3; -- FAU k_BUDGET_CATEGORY CONSTANT INTEGER := 4; k_FUND CONSTANT INTEGER := 5; k_ACTIVITY CONSTANT INTEGER := 6; k_FUNCTION CONSTANT INTEGER := 7; -- 1) Expenditure Amounts k_MOD_AGGREG_AMT CONSTANT INTEGER := 8; k_CALC_AGGREG_AMT CONSTANT INTEGER := 9; k_CALC_MOD_AVG_AMT CONSTANT INTEGER := 10; k_CALC_ORIG_AVG_AMT CONSTANT INTEGER := 11; k_DIRTY_AGGREG_AMT CONSTANT INTEGER := 12; -- 2) Increase / Decrease Projection Percent k_MOD_AVG_PCT CONSTANT INTEGER := 13; k_DIRTY_AVG_PCT CONSTANT INTEGER := 14; k_MOD_PCT_BEGIN_PER CONSTANT INTEGER := 15; k_MOD_PCT_END_PER CONSTANT INTEGER := 16; -- 3) Increase / Decrease Projection Amount k_MOD_AVG_AMT CONSTANT INTEGER := 17; k_DIRTY_AVG_AMT CONSTANT INTEGER := 18; k_MOD_AMT_BEGIN_PER CONSTANT INTEGER := 19; k_MOD_AMT_END_PER CONSTANT INTEGER := 20; -- Comments k_COMMENTS CONSTANT INTEGER := 21; k_DIRTY_COMMENTS CONSTANT INTEGER := 22; v_selected_projection_model VARCHAR2(100) := ''; v_mod_aggreg_amt fbors_nonsalary_projs_tbl.mod_aggreg_amt%TYPE := 0; v_mod_avg_pct fbors_nonsalary_projs_tbl.mod_avg_pct%TYPE := 0; v_mod_avg_amt fbors_nonsalary_projs_tbl.mod_avg_amt%TYPE := 0; v_calc_mod_avg_amt fbors_nonsalary_projs_tbl.calc_mod_avg_amt%TYPE := 0; v_mod_pct_begin_date fbors_nonsalary_projs_tbl.mod_pct_begin_date%TYPE := NULL; v_mod_pct_end_date fbors_nonsalary_projs_tbl.mod_pct_end_date%TYPE := NULL; v_mod_amt_begin_date fbors_nonsalary_projs_tbl.mod_amt_begin_date%TYPE := NULL; v_mod_amt_end_date fbors_nonsalary_projs_tbl.mod_amt_end_date%TYPE := NULL; BEGIN --v_accounting_period := 1; -- debugging dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'STARTING'); v_grid_data := utl_url.unescape(p_grid_data); -- Parse through p_grid_data and determine whether to INSERT a new entry or UPDATE an exisiing one v_items := fbors_funclib.parse_string(p_in_string => v_grid_data, p_delim => '~'); -- Validate the FAU IF fbors_funclib.validate_fau(v_items(k_BUDGET_CATEGORY), v_items(K_ACTIVITY), v_items(K_FUND), v_items(K_FUNCTION)) < 4 THEN htp.p(p_callback || '( { "success":false, "message":"The FAU provided is incorrect. Please review your input, ' || v_items(k_BUDGET_CATEGORY) || ' / ' || v_items(K_ACTIVITY) || ' / ' || v_items(K_FUND) || ' / ' || v_items(K_FUNCTION) || ' and try again. ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- MODIFY: If this projection was modified... IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN -- Only allow one projection model to be used, if more than 1 was udpated (meaning t for true), then return IF nvl(LENGTH(REGEXP_REPLACE(v_items(k_DIRTY_AGGREG_AMT) || v_items(k_DIRTY_AVG_PCT) || v_items(k_DIRTY_AVG_AMT),'[^t]')), 0) > 1 THEN htp.p(p_callback || '( { "success":false, "message":"You may modify only one projection model at a time. Choose one of the three presented and then press [Save].", "totalCount":0 } )') ; ROLLBACK; RETURN; -- If somehow the periods are blank then return ELSIF v_items(k_MOD_AMT_BEGIN_PER) IS NULL OR v_items(k_MOD_AMT_END_PER) IS NULL OR v_items(k_MOD_PCT_END_PER) IS NULL OR v_items(k_MOD_PCT_END_PER) IS NULL THEN htp.p(p_callback || '( { "success":false, "message":"Neither the Start Month nor the End Month can be empty. Please review the selected months and save again.", "totalCount":0 } )') ; ROLLBACK; RETURN; -- If all three were not modified, (meaning f for false) then return ELSIF nvl(LENGTH(REGEXP_REPLACE(v_items(k_DIRTY_AGGREG_AMT) || v_items(k_DIRTY_AVG_PCT) || v_items(k_DIRTY_AVG_AMT),'[^f]')), 0) = 3 AND v_items(k_DIRTY_COMMENTS) = 'false' THEN htp.p(p_callback || '( { "success":true, "message":"No changes were saved.", "totalCount":0 } )') ; ROLLBACK; RETURN; -- Otherwise, determine which was changed and UPDATE accordingly ELSE /* -- Prevent the modified aggregate amount from exceeding the original amount IF v_items(k_MOD_AGGREG_AMT) > v_items(k_CALC_AGGREG_AMT) THEN htp.p(p_callback || '( { "success":false, "message":"The revised aggregate amount may not exceed the original aggregate amount. Please enter a value less than ' || to_char(v_items(k_CALC_AGGREG_AMT), '$999,999,999.99') || '", "totalCount":0 } )') ; ROLLBACK; RETURN; END if;*/ CASE -- If the aggreg amount was changed WHEN v_items(k_DIRTY_AGGREG_AMT) = 'true' THEN v_selected_projection_model := 'agg'; v_mod_aggreg_amt := v_items(k_MOD_AGGREG_AMT); v_mod_avg_amt := CASE WHEN v_accounting_period > 1 then ROUND(v_items(k_MOD_AGGREG_AMT)/(v_accounting_period-1), 2) ELSE ROUND(v_items(k_MOD_AGGREG_AMT)/(v_accounting_period), 2) END; v_calc_mod_avg_amt := v_mod_avg_amt; -- If the percentage was changed WHEN v_items(k_DIRTY_AVG_PCT) = 'true' THEN v_selected_projection_model := 'pct'; -- The Percent increase/decrease v_mod_avg_pct := v_items(k_MOD_AVG_PCT); -- Recalc the Average amount increase/derease v_calc_mod_avg_amt := ROUND(v_items(K_CALC_ORIG_AVG_AMT)*v_items(k_MOD_AVG_PCT)/100 + v_items(K_CALC_ORIG_AVG_AMT), 2); v_mod_avg_amt := v_calc_mod_avg_amt; -- Recalc the modified Aggregate amount v_mod_aggreg_amt :=v_calc_mod_avg_amt*(v_accounting_period-1); -- If the lump sum amount was changed WHEN v_items(k_DIRTY_AVG_AMT) = 'true' THEN v_selected_projection_model := 'amt'; v_calc_mod_avg_amt := v_items(k_CALC_MOD_AVG_AMT) + v_items(k_MOD_AVG_AMT); v_mod_aggreg_amt := v_calc_mod_avg_amt * (v_accounting_period-1); v_mod_avg_pct := 0; -- If the comments alone were changed, then dont worry about it WHEN v_items(k_DIRTY_COMMENTS) = 'true' THEN NULL; -- What else could happen? ELSE htp.p(p_callback || '( { "success":false, "message":"An unexpected CASE WHEN ELSE was trapped @line #' || $$PLSQL_LINE || '", "totalCount":0 } )') ; ROLLBACK; RETURN; END CASE; END IF; -- IF nvl(LENGTH(REGEXP_REPLACE(v_items(k_DIRTY_AGGREG_AMT) || v_items(k_DIRTY_AVG_PCT) || v_items(k_DIRTY_AVG_AMT),'[^t]')), 0) > 1 THEN -- For Projection Percentages, determine the projection's start / end year IF v_items(k_MOD_PCT_BEGIN_PER) < 7 THEN v_start_month := v_items(k_MOD_PCT_BEGIN_PER) + 6; v_start_year := v_fiscal_year; ELSE v_start_month := v_items(k_MOD_PCT_BEGIN_PER) - 6; v_start_year := v_current_year; END IF; IF v_items(k_MOD_PCT_END_PER) < 7 THEN v_end_month := v_items(k_MOD_PCT_END_PER) + 6; v_end_year := v_fiscal_year; ELSE v_end_month := v_items(k_MOD_PCT_END_PER) - 6; v_end_year := v_current_year; END IF; -- Translate into calendar dates v_mod_pct_begin_date := TO_DATE(v_start_month || '/01/' || v_start_year, 'MM/DD/YYYY'); v_mod_pct_end_date := LAST_DAY(TO_DATE(v_end_month || '/01/' || v_end_year, 'MM/DD/YYYY')); -- For Projection Amounts, determine the projection's start / end year IF v_items(k_MOD_AMT_BEGIN_PER) < 7 THEN v_start_month := v_items(k_MOD_AMT_BEGIN_PER) + 6; v_start_year := v_fiscal_year; ELSE v_start_month := v_items(k_MOD_AMT_BEGIN_PER) - 6; v_start_year := v_current_year; END IF; IF v_items(k_MOD_AMT_END_PER) < 7 THEN v_end_month := v_items(k_MOD_AMT_END_PER) + 6; v_end_year := v_fiscal_year; ELSE v_end_month := v_items(k_MOD_AMT_END_PER) - 6; v_end_year := v_current_year; END IF; v_mod_amt_begin_date := TO_DATE(v_start_month || '/01/' || v_start_year, 'MM/DD/YYYY'); v_mod_amt_end_date := LAST_DAY(TO_DATE(v_end_month || '/01/' || v_end_year, 'MM/DD/YYYY')); BEGIN dbms_application_info.set_action( 'Updating the Non Salary Projections data @line #' || $$PLSQL_LINE ); -- Audit this transaction before updating v_return := audit_transaction('fbors_nonsalary_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'UPDATE'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; UPDATE fbors_nonsalary_projs_tbl f SET mod_aggreg_amt = v_mod_aggreg_amt, mod_avg_pct = v_mod_avg_pct, mod_avg_amt = v_mod_avg_amt, calc_mod_avg_amt = v_calc_mod_avg_amt, mod_pct_begin_date = v_mod_pct_begin_date, mod_pct_end_date = v_mod_pct_end_date, system_generated = 'N', updated_by = p_net_id, updated_on = SYSDATE, selected_projection_model = v_selected_projection_model, comments = v_items(k_COMMENTS) WHERE f.sequence_id = v_items(k_SEQ_ID); /*fiscal_year = v_fiscal_year AND accounting_period = v_accounting_period AND budget_category = v_items(k_BUDGET_CATEGORY) AND fund = v_items(k_FUND) AND activity = v_items(k_ACTIVITY) AND function = v_items(k_FUNCTION);*/ EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END ; -- UDPATE v_sql_rowcount := SQL%ROWCOUNT; ELSE htp.p(p_callback || '( { "success":false, "message":"You may not add a new non-salary projection. These are system generated only.", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; COMMIT; -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); -- Result htp.p(p_callback || '( { "success":true, "message":"Your non-salaried projection for ' || v_items(k_BUDGET_CATEGORY) || ' / ' || v_items(k_ACTIVITY) || ' / ' || v_items(k_FUND) || ' / ' || v_items(k_FUNCTION) || CASE WHEN v_sql_rowcount > 0 THEN ' has been' ELSE ' was NOT' END || ' saved for ' || fbors_funclib.accounting_period_month_name(v_accounting_period) || ' / ' || v_current_year || '.", "runTime": "' || v_run_time || '", "totalCount": ' || v_sql_rowcount || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END save_nonsal_proj; PROCEDURE save_budget_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'bud', p_grid_data IN VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : Save Salaried Employees Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To save projections to the FBORS_SALARY_PROJS_TBL table Params : p_grid_data : the list of FORM elements passed as a javasript array into a single parameter here M~2~undefined~BC25~A01369~19900~72~PERM~Brian's PERM for $10,000~10000~October~June~Brians PERM for $10,000...~ UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ -- Array positions to parse through p_grid_data k_ADD_MOD CONSTANT INTEGER := 1; k_SEQ_ID CONSTANT INTEGER := 2; k_NONE CONSTANT INTEGER := 3; k_BUDGET_CATEGORY CONSTANT INTEGER := 4; k_ACTIVITY CONSTANT INTEGER := 5; k_FUND CONSTANT INTEGER := 6; K_FUNCTION CONSTANT INTEGER := 7; k_LEDGER CONSTANT INTEGER := 8; k_PROJ_DESC CONSTANT INTEGER := 9; K_AMOUNT CONSTANT INTEGER := 10; K_PROJ_START CONSTANT INTEGER := 11; K_PROJ_END CONSTANT INTEGER := 12; K_COMMENTS CONSTANT INTEGER := 13; -- Projection Start and End Dates v_sequence_id fbors_budget_projs_tbl.sequence_id%TYPE; v_start_fiscal_year fbors_budget_projs_tbl.fiscal_year%TYPE; v_start_accounting_period fbors_budget_projs_tbl.accounting_period%TYPE; v_summary_account fbors_salary_projs_tbl.summary_account%TYPE; v_summary_acct_descr fbors_salary_projs_tbl.summary_acct_descr%TYPE; v_budget_category fbors_budget_projs_tbl.budget_category%TYPE; v_budcategory_descr fbors_salary_projs_tbl.budcategory_descr%TYPE; v_pay_begin_date fbors_budget_projs_tbl.proj_begin_date%TYPE; v_pay_end_date fbors_budget_projs_tbl.proj_end_date%TYPE; BEGIN --v_accounting_period := 1; -- debugging dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'STARTING'); v_grid_data := utl_url.unescape(p_grid_data); -- Parse through p_grid_data and determine whether to INSERT a new entry or UPDATE an exisiing one v_items := fbors_funclib.parse_string(p_in_string => v_grid_data, p_delim => '~'); --v_item_count := v_items.COUNT; /* -- Prevent the user from trying to create a projection in the past (should already be done on the front end) IF TO_DATE(v_items(K_PROJ_START)) <= TO_DATE(max_closed_date) THEN htp.p(p_callback || '( { "success":false, "message":"You may not start a projection in a month already closed by Accounting. ", "totalCount":0 } )') ; RETURN; -- Otherwise, get the FY/AP based on the Projection's start date ELSE convert_cal_date_2_fy_ap(TO_DATE(v_items(K_PROJ_START)), v_start_fiscal_year, v_start_accounting_period); END IF; */ -- fbors_funclib.convert_cal_date_2_fy_ap(TO_DATE(v_items(K_PROJ_START), 'Month'), v_start_fiscal_year, v_start_accounting_period); fbors_funclib.convert_cal_date_2_fy_ap(SYSDATE, v_start_fiscal_year, v_start_accounting_period); -- v_default_proj_amt := round(v_items(k_FTE)*v_items(K_PAY_RATE), 2); /* -- Convert the current date into the relevant fiscal year and accounting period v_fiscal_year := fbors_funclib.current_fiscal_year; v_max_closed_date := to_date(fbors_funclib.max_closed_date, 'MM/DD/YYYY'); -- ie: 8/8/2012 for closing July v_last_closed_month := TO_CHAR(TO_DATE(v_max_closed_date, 'DD-MON-YYYY'), 'MM') -1; -- ie: 8-1 so 7 for July v_last_closed_month := 8; -- debugging for other months IF v_last_closed_month > 6 THEN v_accounting_period := v_last_closed_month - 6; ELSE v_accounting_period := v_last_closed_month + 6; END IF; */ -- Determine the projection's start / end year IF EXTRACT(MONTH FROM TO_DATE(v_items(K_PROJ_START), 'MM')) < 7 THEN v_start_year := v_fiscal_year; ELSE v_start_year := v_current_year; END IF; IF EXTRACT(MONTH FROM TO_DATE(v_items(K_PROJ_END), 'MM')) < 7 THEN v_end_year := v_fiscal_year; ELSE v_end_year := v_current_year; END IF; -- Validate the FAU IF fbors_funclib.validate_fau(v_items(k_BUDGET_CATEGORY), v_items(K_ACTIVITY), v_items(K_FUND), v_items(K_FUNCTION)) < 4 THEN htp.p(p_callback || '( { "success":false, "message":"The FAU provided is incorrect. Please review your input, ' || v_items(k_BUDGET_CATEGORY) || ' / ' || v_items(K_ACTIVITY) || ' / ' || v_items(K_FUND) || ' / ' || v_items(K_FUNCTION) || ' and try again. ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- MODIFY: If this projection was modified... IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN -- Determine the number of months between the start and end date for the annualized projection amount v_months_between := months_between(TO_DATE(v_items(K_PROJ_START) || v_start_year, 'Month YYYY'), LAST_DAY(TO_DATE(v_items(K_PROJ_END) || v_end_year, 'Month YYYY'))); v_budget_category := get_budget_category_fast('UCR',v_items(k_BUDGET_CATEGORY)); BEGIN v_pay_begin_date := to_char( '' || to_char(to_date(v_items(K_PROJ_START),'Month'),'MM') || '/01/' || v_start_year || ''); v_pay_end_date := to_char( '' || to_char(to_date(v_items(K_PROJ_END),'Month'),'MM') || '/01/' || v_end_year || ''); dbms_application_info.set_action( 'Updating the table @line #' || $$PLSQL_LINE); -- Audit this transaction before updating v_return := audit_transaction('fbors_budget_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'UPDATE'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; UPDATE fbors_budget_projs_tbl p SET amount = v_items(K_AMOUNT), proj_begin_date = v_pay_begin_date, proj_end_date = to_char(last_day(to_date(v_pay_end_date,'MM/DD/YYYY')),'MM/DD/YYYY'), comments = v_items(K_COMMENTS), updated_by = p_net_id, updated_on = SYSDATE WHERE p.sequence_id = v_items(k_SEQ_ID) /*p.fiscal_year = v_fiscal_year AND p.accounting_period = v_accounting_period AND p.budget_category = v_budget_category AND p.activity = v_items(K_ACTIVITY) AND p.fund = v_items(K_FUND) AND p.function = v_items(K_FUNCTION)*/ ; v_sql_rowcount := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- ADD: Insert a new projection ELSIF instr(v_items(k_ADD_MOD), 'A') > 0 THEN -- Validate the FAU IF fbors_funclib.validate_fau(v_items(k_BUDGET_CATEGORY), v_items(K_ACTIVITY), v_items(K_FUND), v_items(K_FUNCTION)) = 0 THEN htp.p(p_callback || '( { "success":false, "message":"The FAU provided is incorrect. ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- Get the higher level Account values for easier querying on the Summary / Detail reports BEGIN SELECT DISTINCT a.ucr_acct_sum, a.ucr_acct_sum_dsc, a.ucr_acct_bdgt, a.ucr_acct_bdgt_dsc INTO v_summary_account, v_summary_acct_descr, v_budget_category, v_budcategory_descr FROM ps_ucr_acct_lv_tbl a WHERE a.ucr_acct_bdgt = upper(v_items(k_BUDGET_CATEGORY)); EXCEPTION WHEN OTHERS THEN htp.p(p_callback || '( { "success":false, "message":"An invalid Account was provided. Please select an Account from the list provided and try saving your projection again.", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Get the employees's activity denormalized information -- get_actv_denorm_tree_values(v_items(K_ACTIVITY), v_activity_denorm_tree_row, v_result); get_actv_denorm_tree_values(v_items(K_ACTIVITY), v_activity_denorm_tree_values, v_result); IF v_result IS NOT NULL THEN htp.p(p_callback || '( { "success":false, "message":"' || v_result || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; v_pay_begin_date := to_char( '' || to_char(to_date(v_items(K_PROJ_START),'Month'),'MM') || '/01/' || v_start_year || ''); v_pay_end_date := to_char( '' || to_char(to_date(v_items(K_PROJ_END),'Month'),'MM') || '/01/' || v_end_year || ''); --v_pay_begin_date := TO_DATE(to_char(TO_DATE(v_items(K_PROJ_START), 'Month'), 'MM') || '01/' || v_current_year, 'MM/DD/YYYY'); --v_pay_end_date := last_day(TO_DATE(to_char(TO_DATE(v_items(K_PROJ_START), 'Month'), 'MM') || '01/' || v_current_year, 'MM/DD/YYYY')); -- The actual INSERT INTO BEGIN dbms_application_info.set_action( 'Inserting the data @line #'|| $$PLSQL_LINE ); v_seq_nextval := fbors_budget_projs_seq.nextval; INSERT INTO fbors_budget_projs_tbl (sequence_id, fiscal_year, accounting_period, ledger, descr, budget_category, fund, org_code, div_code, dept_code, activity, function, amount, proj_begin_date, proj_end_date, comments, updated_by, updated_on) VALUES (v_seq_nextval, v_fiscal_year, v_accounting_period, v_items(K_LEDGER), v_items(k_PROJ_DESC), v_budget_category, v_items(K_FUND), v_activity_denorm_tree_values.org_code, v_activity_denorm_tree_values.div_code, v_activity_denorm_tree_values.dept_code, upper(v_items(K_ACTIVITY)), v_items(K_FUNCTION), v_items(K_AMOUNT), v_pay_begin_date, to_char(last_day(to_date(v_pay_end_date,'MM/DD/YYYY')),'MM/DD/YYYY'), -- to_char(v_pay_begin_date,'MM/DD/YYYY'), to_char(v_pay_end_date,'MM/DD/YYYY'), v_items(K_COMMENTS), p_net_id, SYSDATE); v_sql_rowcount := SQL%ROWCOUNT; -- If there were any rows inserted, update their sequence ID IF v_sql_rowcount > 0 THEN -- Now update the sequence id. Is there a better to do this? UPDATE fbors_budget_projs_tbl p SET p.sequence_id = fbors_budget_projs_seq.nextval WHERE p.sequence_id = -1; END IF; -- IF v_sql_rowcount > 0 THEN EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Audit this transaction after INSERTing v_return := audit_transaction('fbors_budget_projs_tbl', v_seq_nextval, fbors_projs_audit_seq.nextval, p_net_id, 'INSERT'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; END IF; -- IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN COMMIT; check_activity_in_eacs(UPPER(v_items(K_ACTIVITY)), p_net_id, v_result); v_save_message := v_save_message || v_result; -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); -- Result htp.p(p_callback || '( { "success":true, "message":"Your budget projection for ' || v_items(k_BUDGET_CATEGORY) || ' / ' || v_items(k_ACTIVITY) || ' / ' || v_items(k_FUND) || ' / ' || v_items(k_FUNCTION) || CASE WHEN v_sql_rowcount > 0 THEN ' has been' ELSE ' was NOT' END || ' saved for ' || fbors_funclib.accounting_period_month_name(v_accounting_period) || ' / ' || v_current_year || '.' || v_save_message || '", "runTime": "' || v_run_time || '", "totalCount": ' || v_sql_rowcount || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); -- Not so good :( EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END save_budget_proj; PROCEDURE save_benefit_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'ben', p_grid_data IN VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : Save Salaried Employees Projection Data Author : Larry W, Brian G Created : July 2012 Purpose : To save projections to the FBORS_SALARY_PROJS_TBL table Params : p_grid_data : the list of FORM elements passed as a javasript array into a single parameter here A~-1~none~BC41~A01374~19900~72~-1~7~9~Comment~ UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ -- A~-1~none~BC41~A01374~19900~72~-1~7~9~Comment~ -- Array positions to parse through p_grid_data k_ADD_MOD CONSTANT INTEGER := 1; k_SEQ_ID CONSTANT INTEGER := 2; k_UNDEFINED CONSTANT INTEGER := 3; k_BUDGET_CATEGORY CONSTANT INTEGER := 4; K_ACTIVITY CONSTANT INTEGER := 5; K_FUND CONSTANT INTEGER := 6; K_FUNCTION CONSTANT INTEGER := 7; K_AMOUNT CONSTANT INTEGER := 8; k_BEGIN_PER CONSTANT INTEGER := 9; k_END_PER CONSTANT INTEGER := 10; K_COMMENTS CONSTANT INTEGER := 11; -- Projection Start and End Dates v_start_fiscal_year fbors_salary_projs_tbl.fiscal_year%TYPE; v_start_accounting_period fbors_salary_projs_tbl.accounting_period%TYPE; --v_end_fiscal_year fbors_salary_projs_tbl.fiscal_year%TYPE; --v_end_accounting_period fbors_salary_projs_tbl.accounting_period%TYPE; v_summary_account fbors_salary_projs_tbl.summary_account%TYPE; v_summary_acct_descr fbors_salary_projs_tbl.summary_acct_descr%TYPE; v_budget_category fbors_salary_projs_tbl.budget_category%TYPE; v_budcategory_descr fbors_salary_projs_tbl.budcategory_descr%TYPE; --v_title_code_descr vw_title_codes_appt.title_code_descr%TYPE; v_pay_begin_date fbors_salary_projs_tbl.pay_begin_date%TYPE; v_pay_end_date fbors_salary_projs_tbl.pay_end_date%TYPE; -- v_sql VARCHAR2(32000); BEGIN --v_accounting_period := 1; -- debugging dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'STARTING'); v_grid_data := utl_url.unescape(p_grid_data); -- Parse through p_grid_data and determine whether to INSERT a new entry or UPDATE an exisiing one v_items := fbors_funclib.parse_string(p_in_string => v_grid_data, p_delim => '~'); --v_item_count := v_items.COUNT; /* -- Prevent the user from trying to create a projection in the past (should already be done on the front end) IF TO_DATE(v_items(k_BEGIN_PER)) <= TO_DATE(max_closed_date) THEN htp.p(p_callback || '( { "success":false, "message":"You may not start a projection in a month already closed by Accounting. ", "totalCount":0 } )') ; RETURN; -- Otherwise, get the FY/AP based on the Projection's start date ELSE convert_cal_date_2_fy_ap(TO_DATE(v_items(k_BEGIN_PER)), v_start_fiscal_year, v_start_accounting_period); END IF; */ -- fbors_funclib.convert_cal_date_2_fy_ap(TO_DATE(v_items(k_BEGIN_PER), 'Month'), v_start_fiscal_year, v_start_accounting_period); fbors_funclib.convert_cal_date_2_fy_ap(SYSDATE, v_start_fiscal_year, v_start_accounting_period); -- v_default_proj_amt := round(v_items(k_FTE)*v_items(K_PAY_RATE), 2); /* -- Convert the current date into the relevant fiscal year and accounting period v_fiscal_year := fbors_funclib.current_fiscal_year; v_max_closed_date := to_date(fbors_funclib.max_closed_date, 'MM/DD/YYYY'); -- ie: 8/8/2012 for closing July v_last_closed_month := TO_CHAR(TO_DATE(v_max_closed_date, 'DD-MON-YYYY'), 'MM') -1; -- ie: 8-1 so 7 for July v_last_closed_month := 8; -- debugging for other months IF v_last_closed_month > 6 THEN v_accounting_period := v_last_closed_month - 6; ELSE v_accounting_period := v_last_closed_month + 6; END IF; */ /* -- Determine the projection's start / end year IF EXTRACT(MONTH FROM TO_DATE(v_items(k_BEGIN_PER), 'MM')) < 7 THEN v_start_year := v_fiscal_year; ELSE v_start_year := v_current_year; END IF; IF EXTRACT(MONTH FROM TO_DATE(v_items(k_END_PER), 'MM')) < 7 THEN v_end_year := v_fiscal_year; ELSE v_end_year := v_current_year; END IF; */ IF v_items(K_BEGIN_PER) < 7 THEN v_start_month := v_items(K_BEGIN_PER) + 6; v_start_year := v_current_year; ELSE v_start_month := v_items(K_BEGIN_PER) - 6; v_start_year := v_fiscal_year; END IF; IF v_items(K_END_PER) < 7 THEN v_end_month := v_items(K_END_PER) + 6; v_end_year := v_current_year; ELSE v_end_month := v_items(K_END_PER) - 6; v_end_year := v_fiscal_year; END IF; -- Validate the FAU IF fbors_funclib.validate_fau(v_items(k_BUDGET_CATEGORY), v_items(K_ACTIVITY), v_items(K_FUND), v_items(K_FUNCTION)) < 4 AND instr(v_items(k_ADD_MOD), 'D') = 0 THEN htp.p(p_callback || '( { "success":false, "message":"The FAU provided is incorrect. Please review your input, ' || v_items(k_BUDGET_CATEGORY) || ' / ' || v_items(K_ACTIVITY) || ' / ' || v_items(K_FUND) || ' / ' || v_items(K_FUNCTION) || ' and try again. ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- MODIFY: If this projection was modified... IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN -- Determine the number of months between the start and end date for the annualized projection amount --v_months_between := months_between(TO_DATE(v_items(k_BEGIN_PER) || v_start_year, 'Month YYYY'), LAST_DAY(TO_DATE(v_items(k_END_PER) || v_end_year, 'Month YYYY'))); BEGIN --v_pay_begin_date := TO_DATE(v_items(k_BEGIN_PER) || '/' || '01/' || v_start_year, 'MM/DD/YYYY'); --v_pay_end_date := last_day(TO_DATE(v_items(k_END_PER) || '/' || '01/' || v_end_year, 'MM/DD/YYYY')); v_pay_begin_date := TO_DATE(v_start_month || '/01/' || v_start_year, 'MM/DD/YYYY'); v_pay_end_date := last_day(TO_DATE(v_end_month || '/01/' || v_end_year, 'MM/DD/YYYY')); --v_pay_begin_date := TO_DATE(to_char(TO_DATE(v_items(k_BEGIN_PER), 'Month'), 'MM') || '01/' || v_start_year, 'MM/DD/YYYY'); --v_pay_end_date := last_day(TO_DATE(to_char(TO_DATE(v_items(k_END_PER), 'Month'), 'MM') || '01/' || v_end_year, 'MM/DD/YYYY')); --v_pay_begin_date := to_char( '' || to_char(to_date(v_items(k_BEGIN_PER),'Month'),'MM') || '/01/' || v_start_year || ''); --v_pay_end_date := to_char( '' || to_char(to_date(v_items(k_END_PER),'Month'),'MM') || '/01/' || v_end_year || ''); dbms_output.put_line(v_pay_begin_date); dbms_output.put_line(v_pay_end_date); --RETURN; v_sql := 'UPDATE fbors_benefits_projs_tbl p SET mod_total_benefits_amt = ' || v_items(k_AMOUNT) || ', mod_begin_date = to_date(''' || v_pay_begin_date || ''',''MM/DD/YYYY''), mod_end_date = to_date(''' || v_pay_end_date || ''',''MM/DD/YYYY''), comments = ' || v_items(K_COMMENTS) || ', system_generated = ''N'', updated_by = ''' || p_net_id || ''', updated_on = SYSDATE WHERE p.sequence_id = ''' || v_items(k_SEQ_ID) || ''''; dbms_output.put_line(v_sql); -- RETURN; dbms_application_info.set_action( 'Updating the table @line #' || $$PLSQL_LINE); -- Audit this transaction before updating v_return := audit_transaction('fbors_benefits_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'UPDATE'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; UPDATE fbors_benefits_projs_tbl p SET mod_total_benefits_amt = v_items(k_AMOUNT), mod_begin_date = v_pay_begin_date, mod_end_date = v_pay_end_date, comments = v_items(K_COMMENTS), system_generated = 'N', updated_by = p_net_id, updated_on = SYSDATE WHERE p.sequence_id = v_items(k_SEQ_ID) /*p.fiscal_year = v_fiscal_year AND p.accounting_period = v_accounting_period AND p.budget_category = v_items(K_ACCOUNT) AND p.activity = v_items(K_ACTIVITY) AND p.fund = v_items(K_FUND) AND p.function = v_items(K_FUNCTION)*/ ; v_sql_rowcount := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- ADD: Insert a new projection ELSIF instr(v_items(k_ADD_MOD), 'A') > 0 THEN /* -- Validate the FAU IF fbors_funclib.validate_fau(v_items(K_ACCOUNT), v_items(K_ACTIVITY), v_items(K_FUND), v_items(K_FUNCTION)) = 0 THEN htp.p(p_callback || '( { "success":false, "message":"The FAU provided is incorrect. ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF;*/ /* -- Prevent 0 amounts for new additions IF ( v_items(k_FTE) = 0 ) OR ( v_items(K_PAY_RATE) = 0 ) THEN htp.p(p_callback || '( { "success":false, "message":"Neither the FTE nor the Pay Rate can be 0 for new projections. Please review the projection data before continuing. ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF;*/ -- Get the higher level Account values for easier querying on the Summary / Detail reports BEGIN SELECT DISTINCT a.ucr_acct_sum, a.ucr_acct_sum_dsc, a.ucr_acct_bdgt, a.ucr_acct_bdgt_dsc INTO v_summary_account, v_summary_acct_descr, v_budget_category, v_budcategory_descr FROM ps_ucr_acct_lv_tbl a WHERE a.ucr_acct_bdgt = v_items(k_BUDGET_CATEGORY); EXCEPTION WHEN OTHERS THEN htp.p(p_callback || '( { "success":false, "message":"An invalid Account was provided. Please select an Account from the list provided and try saving your projection again.", "totalCount":0 } )') ; ROLLBACK; RETURN; END; /* -- Get the Title Code description BEGIN SELECT DISTINCT tc.title_code_descr INTO v_title_code_descr FROM vw_title_codes_appt tc WHERE tc.title_code= v_items(k_TITLE_CODE); EXCEPTION WHEN OTHERS THEN htp.p(p_callback || '( { "success":false, "message":"An invalid Title Code was provided. Please select a Title Code from the list provided and try saving your projection again.", "totalCount":0 } )') ; ROLLBACK; RETURN; END; */ -- Get the employees's activity denormalized information -- get_actv_denorm_tree_values(v_items(K_ACTIVITY), v_org_code, v_div_code, v_dept_code, v_result); get_actv_denorm_tree_values(UPPER(v_items(K_ACTIVITY)), v_activity_denorm_tree_values, v_result); IF v_result IS NOT NULL THEN htp.p(p_callback || '( { "success":false, "message":"' || v_result || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; /* -- Get the employees's activity denormalized information BEGIN SELECT DISTINCT a.ucr_org, a.ucr_div, a.ucr_dept INTO v_org_code, v_div_code, v_dept_code FROM rpt_user.ps_ucr_actv_lv_tbl a WHERE a.ucr_actv = v_items(K_ACTIVITY); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END ;*/ dbms_output.put_line(v_start_year); -- dbms_output.put_line(TO_DATE(to_char(TO_DATE(v_items(k_BEGIN_PER), 'Month'), 'MM') || '01/' || v_start_year, 'MM/DD/YYYY')); -- dbms_output.put_line(last_day(TO_DATE(to_char(TO_DATE(v_items(k_END_PER), 'Month'), 'MM') || '01/' || v_end_year, 'MM/DD/YYYY'))); --v_pay_begin_date := TO_DATE(to_char(TO_DATE(v_items(k_BEGIN_PER), 'Month'), 'MM') || '01/' || v_start_year, 'MM/DD/YYYY'); --v_pay_end_date := last_day(TO_DATE(to_char(TO_DATE(v_items(k_END_PER), 'Month'), 'MM') || '01/' || v_end_year, 'MM/DD/YYYY')); --v_pay_begin_date := to_char( '' || to_char(to_date(v_items(k_BEGIN_PER),'Month'),'MM') || '/01/' || v_start_year || ''); --v_pay_end_date := to_char( '' || to_char(to_date(v_items(k_END_PER),'Month'),'MM') || '/01/' || v_end_year || ''); v_pay_begin_date := TO_DATE(v_start_month || '/01/' || v_start_year, 'MM/DD/YYYY'); v_pay_end_date := last_day(TO_DATE(v_end_month || '/01/' || v_end_year, 'MM/DD/YYYY')); -- The actual INSERT INTO BEGIN dbms_application_info.set_action( 'Inserting the data @line #'|| $$PLSQL_LINE ); v_seq_nextval := fbors_benefits_projs_seq.nextval; INSERT INTO fbors_benefits_projs_tbl (sequence_id, fiscal_year, accounting_period, summary_account, summary_acct_descr, budget_category, budcategory_descr, account, account_descr, org_code, div_code, dept_code, activity, fund, function, total_benefits_amt, begin_date, end_date, mod_total_benefits_amt, mod_begin_date, mod_end_date, comments, system_generated, updated_by, updated_on, download_date) VALUES (v_seq_nextval, v_fiscal_year, v_accounting_period, v_summary_account, v_summary_acct_descr, v_items(k_BUDGET_CATEGORY), get_budget_category_fast('UCR',v_items(k_BUDGET_CATEGORY)), -- v_items(K_ACCOUNT), get_account_desc_fast(v_items(K_ACCOUNT)), '', '', v_activity_denorm_tree_values.org_code, v_activity_denorm_tree_values.div_code, v_activity_denorm_tree_values.dept_code, v_items(K_ACTIVITY),v_items(K_FUND), -- '', '', '', '', '', v_items(K_FUNCTION), v_items(K_AMOUNT), --'', v_items(K_AMOUNT), v_pay_begin_date, v_pay_end_date, v_items(K_AMOUNT), v_pay_begin_date, v_pay_end_date, v_items(k_COMMENTS), 'N', p_net_id, SYSDATE, NULL); v_sql_rowcount := SQL%ROWCOUNT; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; -- Audit this transaction after INSERTing v_return := audit_transaction('fbors_benefits_projs_tbl', v_seq_nextval, fbors_projs_audit_seq.nextval, p_net_id, 'INSERT'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; -- DELETE a Benefit projectio ELSIF instr(v_items(k_ADD_MOD), 'D') > 0 THEN -- Audit this transaction before DELETing v_return := audit_transaction('fbors_benefits_projs_tbl', v_items(k_SEQ_ID), fbors_projs_audit_seq.nextval, p_net_id, 'DELETE'); IF v_return <> 'SUCCESS' THEN htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_return) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END IF; dbms_application_info.set_action( 'Deleting from the Benefit Projections table @line #' || $$PLSQL_LINE); BEGIN DELETE FROM fbors_benefits_projs_tbl p WHERE p.sequence_id = v_items(k_SEQ_ID); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; v_sql_rowcount := SQL%ROWCOUNT; COMMIT; END IF; -- IF instr(v_items(k_ADD_MOD), 'M') > 0 THEN COMMIT; -- Get the run time of this procedure v_run_time := to_char((dbms_utility.get_time - v_start_time)/100,'000.00'); -- Result htp.p(p_callback || '( { "success":true, "message":"Your benefit projection for ' || CASE WHEN v_sql_rowcount > 0 THEN ' has been' ELSE ' was NOT' END || ' saved for ' || fbors_funclib.accounting_period_month_name(v_accounting_period) || ' / ' || v_current_year || '.", "runTime": "' || v_run_time || '", "totalCount": ' || v_sql_rowcount || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); -- Not so good :( EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END save_benefit_proj; PROCEDURE delete_data( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2, p_grid_data IN VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : Delete Projection Data Author : Larry W, Brian G Created : Yesember 2012 Purpose : To redirect and then delete user-created projections based on the Projection type. This only applies to Salary / Hourly / Budget projections Params : p_grid_data : D~7350~~~~~~~~~~~~~~ p_employee_id : 850165183 p_proj_type : se, he, nse, ben, bud UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ BEGIN -- Switch to the appropriate procedure to delete the projection v_url := gk_APPLICATION_URL || 'fbors_projections.' || CASE p_proj_type -- Non salaried employees -- WHEN 'ns' THEN 'delete_nonsal_proj' -- Benefits WHEN 'ben' THEN 'save_benefit_proj' -- Budgets WHEN 'bud' THEN 'delete_budget_proj' -- Salary employees / Hourly employees ELSE --'save_data' 'save_employee_proj' END || '?p_callback=' || p_callback || '&p_net_id=' || p_net_id || '&p_employee_id=' || p_employee_id || '&p_grid_data=' || p_grid_data || '&p_proj_type=' || p_proj_type; owa_util.redirect_url(v_url, TRUE); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; RETURN; END delete_data; /*PROCEDURE delete_employee_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'se', p_grid_data IN VARCHAR2 ) IS \* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : Delete Salaried Employees Projection row Author : Larry W, Brian G Created : July 2012 Purpose : To delete a projection from the FBORS_SALARY_PROJS_TBL table Params : UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - *\ v_items fbors_funclib.tv_data; -- Array positions to parse through p_grid_data k_DEL CONSTANT INTEGER := 1; k_SEQ_ID CONSTANT INTEGER := 2; BEGIN dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'STARTING'); v_grid_data := utl_url.unescape(p_grid_data); v_items := fbors_funclib.parse_string(p_in_string => v_grid_data, p_delim => '~'); -- DELETE: If this projection is to be deleted IF instr(v_items(k_DEL), 'D') > 0 THEN -- Audit this transaction before deleting BEGIN dbms_application_info.set_action( 'Auditing the Salary Projections table @line #' || $$PLSQL_LINE); INSERT INTO fbors_salary_projs_audit (audit_id, audit_net_id, audit_datetime, audit_operation, sequence_id, net_id, employee_id, first_name, last_name, fiscal_year, accounting_period, load_seq, account, summary_account, summary_acct_descr, budget_category, budcategory_descr, fund, org_code, div_code, dept_code, activity, FUNCTION, cost_center, project_id, title_code, title_code_descr, bargaining_unit, pay_begin_date, pay_end_date, rate_code, fte, payrate, calc_payrate, calc_annual_payrate, mod_fte, mod_payrate, calc_mod_payrate, calc_mod_annual_payrate, mod_begin_date, mod_end_date, comments, system_generated, updated_by, updated_on, hrdw_download_date) SELECT fbors_projs_audit_seq.nextval, p_net_id, SYSDATE, 'DELETE', p.* FROM fbors_salary_projs_tbl p WHERE p.sequence_id = v_items(k_SEQ_ID); EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END; dbms_application_info.set_action( 'Deleting from the Salary Projections table @line #' || $$PLSQL_LINE); DELETE FROM fbors_salary_projs_tbl p WHERE p.sequence_id = v_items(k_SEQ_ID); v_sql_rowcount := SQL%ROWCOUNT; COMMIT; htp.p(p_callback || '( { "success":true, "message":' || CASE WHEN v_sql_rowcount > 0 THEN ' "Your salaried employee projection has been deleted. ' ELSE ' "Your salaried employee projection was NOT deleted. ' END || ' ",' || '"totalCount": ' || v_sql_rowcount || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); END IF; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END delete_employee_proj;*/ PROCEDURE delete_budget_proj( p_callback IN VARCHAR2 DEFAULT NULL, p_net_id IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_proj_type IN VARCHAR2 DEFAULT 'bud', p_grid_data IN VARCHAR2 ) IS /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Procedure : Delete Non Salaried Projection row Author : Larry W, Brian G Created : July 2012 Purpose : To delete a projection from the FBORS_NONSALARY_PROJS_TBL table Params : UC Riverside 2012 ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ v_items fbors_funclib.tv_data; -- Array positions to parse through p_grid_data k_DEL CONSTANT INTEGER := 1; k_SEQ_ID CONSTANT INTEGER := 2; BEGIN dbms_application_info.set_module(module_name => gv_PATH_INFO, action_name => 'STARTING'); v_grid_data := utl_url.unescape(p_grid_data); v_items := fbors_funclib.parse_string(p_in_string => v_grid_data, p_delim => '~'); -- DELETE: If this projection is to be deleted IF instr(v_items(k_DEL), 'D') > 0 THEN dbms_application_info.set_action( 'Deleting from the Salary Projections table @line #' || $$PLSQL_LINE); DELETE FROM fbors_budget_projs_tbl p WHERE p.sequence_id = v_items(k_SEQ_ID); v_sql_rowcount := SQL%ROWCOUNT; COMMIT; htp.p(p_callback || '( { "success":true, "message":' || CASE WHEN v_sql_rowcount > 0 THEN ' "Your budget projection has been deleted. ' ELSE ' "Your budget projection was NOT deleted. ' END || ' ",' || '"totalCount": ' || v_sql_rowcount || ' } )') ; -- Clean up and reset MODULE and ACTION as we exit. dbms_application_info.set_module( null,null ); END IF; EXCEPTION WHEN OTHERS THEN v_error_message := fbors_funclib.display_error_info(p_net_id, DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'json'); htp.p(p_callback || '( { "success":false, "message":"' || fbors_funclib.cleanup4json(v_error_message) || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; END delete_budget_proj; /* ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - Main ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - ~ - */ BEGIN -- Get the current time case you want to determine the run time v_start_time := dbms_utility.get_time; -- If we're running a Test of the package, then the path info would be NULL IF gv_PATH_INFO IS NULL THEN gv_PATH_INFO := 'via PLSQL Developer' ; END IF; -- Get the Closing window's number of days SELECT * INTO v_app_settings FROM app_settings a WHERE a.name = 'fbors'; -- Convert the current date into the relevant fiscal year and accounting period v_fiscal_year := fbors_funclib.current_fiscal_year; v_max_closed_date := to_date(fbors_funclib.max_closed_date, 'MM/DD/YYYY'); -- ie: 8/8/2012 for closing July v_last_closed_month := max_closed_period; v_accounting_period := v_last_closed_month ; /* -- If the current date is between the Closing date and Closing Date + 5 days IF trunc(SYSDATE) BETWEEN v_max_closed_date AND (v_max_closed_date + v_app_settings.closing_window) THEN v_last_closed_month := TO_CHAR(TO_DATE(v_max_closed_date, 'DD-MON-YYYY'), 'MM') -1; -- ie: 11-1 so 10 for October -- Otherwise go to the next month ELSE v_last_closed_month := TO_CHAR(TO_DATE(v_max_closed_date, 'DD-MON-YYYY'), 'MM'); -- ie: 11 for November END IF;*/ /* -- If the current date is between the Closing date and Closing Date + 5 days IF trunc(SYSDATE) BETWEEN v_max_closed_date AND (v_max_closed_date + v_app_settings.closing_window) THEN v_last_closed_month := max_closed_period; -- Otherwise go to the next month ELSE v_last_closed_month := max_closed_period+1; END IF; */ -- v_last_closed_month := 8; -- debugging for other months /* IF v_last_closed_month > 6 THEN v_accounting_period := v_last_closed_month - 6; ELSE v_accounting_period := v_last_closed_month + 6; END IF;*/ EXCEPTION WHEN OTHERS THEN v_error_message := 'There was a problem accessing the package.'; htp.p('( { "success":false, "message":"' || v_error_message || ' ", "totalCount":0 } )') ; ROLLBACK; RETURN; end fbors_projections; /