Professoinal Services Service Report
Foothill de Anza
Consulting and Conversion Support - Remote
09-MAR-2009 through 13-MAR-2009
SERVICE INFORMATION
|Description |: |General Consulting and Conversion Support |
|Prepared By |: |Dominic Martinez, Consultant Title, SunGard Higher Education |
| | |Dominic.Martinez@ |
| | |972.800.5479 |
DISTRIBUTION
|Name |Title |Company |Email |
|Rob Bailey |Project Manager |SGHE |rob.bailey@ |
|Kathy Kyne |EIS Project Manager |FHDA |kynekathy@deanza.edu |
|Linda Wooden |Project Manager |SGHE |Linda.wooden@ |
| | | | |
OBJECTIVES
To begin work on a process by which to load and post Payroll encumbrance data as processed by the current HRS legacy system.
ACCOMPLISHMENTS
Process: FZRPENC
I am attaching the proposed code and with this trip report will also attach two files, an SQL file detailing the listed code in executable form, as well as a setup SQL script used to input the proper startup values for required elements of job submission. A shell script has not yet been developed.
Further development will be required in order to finalize this process as job submission as well as to include the Budget Side entries which will require a new RUCL code (to default budget period).
FZRPENC Execution SQL
/*
Process: FZRPENC
Version: 1.0
Purpose: Load HRS Encumbrance Values into Banner Finance
Created By: Dominic Martinez, SGHE Technical Consultant
Payroll Encumbrance Transaction Interface
-- Process Flow
-- EXT: Load Entry File using SQL Loader into FZRPENC
-- SQL: Determine if any open encumbrance transaction exists
-- -- If so, Load reversal entries into GURFEED (Header and Detail)
-- SQL: Add header record for detail records in FZRPENC
-- SQL: Load GURFEED with detail records from FZRPENC
New Tables
-- FZRPENC
*/
-- START gzqrpls nzpfenc
SET ECHO OFF
SET HEAD OFF
SET FEEDBACK OFF
SET NEWPAGE NONE
SET SERVEROUTPUT ON
DECLARE
-----------------------------------------------------------
-- Variables
-----------------------------------------------------------
-- General Variables
V_TRANS_AMT_SUM NUMBER(12,2):= 0;
v_cnt_check NUMBER(4):= 0;
v_frs_string VARCHAR2(10) := NULL;
v_frs_string_acct VARCHAR2(6) := NULL;
v_frs_string_objc VARCHAR2(4) := NULL;
-- Encumbrance Header Table Variables
FGBENCH_NUM VARCHAR2(8):= NULL;
FGBENCH_ACTIVITY_DATE DATE;
FGBENCH_USER_ID VARCHAR2(30):= NULL;
FGBENCH_DESC VARCHAR2(30):= NULL;
FGBENCH_TRANS_DATE DATE;
FGBENCH_DOC_AMT NUMBER(12,2):= 0;
FGBENCH_TYPE VARCHAR2(1):= NULL;
FGBENCH_STATUS_IND VARCHAR2(1):= NULL;
FGBENCH_STATUS VARCHAR2(1):= NULL;
FGBENCH_EDIT_DEFERRAL VARCHAR2(1):= NULL;
FGBENCH_ESTAB_DATE DATE;
FGBENCH_APPR_IND VARCHAR2(1):= NULL;
FGBENCH_NSF_ON_OFF_IND VARCHAR2(1):= NULL;
-- Gurfeed Specific Variables
GURFEED_SYSTEM_ID VARCHAR2(9):= NULL;
GURFEED_SYSTEM_TIME_STAMP VARCHAR2(14):= NULL;
GURFEED_DOC_CODE VARCHAR2(8):= NULL;
GURFEED_REC_TYPE NUMBER(1):= 0;
GURFEED_SUBMISSION_NUMBER NUMBER(1):= 0;
GURFEED_ITEM_NUM NUMBER(1):= 0;
GURFEED_SEQ_NUM NUMBER(4):= 0;
GURFEED_ACTIVITY_DATE DATE;
GURFEED_USER_ID VARCHAR2(30):= NULL;
GURFEED_RUCL_CODE VARCHAR2(4):= NULL;
GURFEED_TRANS_DATE DATE;
GURFEED_TRANS_DESC VARCHAR2(30):= NULL;
GURFEED_DR_CR_IND VARCHAR2(1):= NULL;
GURFEED_ENCD_NUM VARCHAR2(8):= NULL;
GURFEED_ENCD_ITEM_NUM NUMBER(1):= 0;
GURFEED_ENCD_SEQ_NUM NUMBER(4):= 0;
GURFEED_ENCD_ACTION_IND VARCHAR2(1):= NULL;
GURFEED_CMT_TYPE VARCHAR2(1):= NULL;
GURFEED_ENCB_TYPE VARCHAR2(1):= NULL;
GURFEED_DOC_REF_NUM VARCHAR2(8):= NULL;
GURFEED_COAS_CODE VARCHAR2(1):= NULL;
GURFEED_FUND_CODE VARCHAR2(6):= NULL;
GURFEED_ORGN_CODE VARCHAR2(6):= NULL;
GURFEED_ACCT_CODE VARCHAR2(6):= NULL;
GURFEED_PROG_CODE VARCHAR2(6):= NULL;
GURFEED_ACTV_CODE VARCHAR2(6):= NULL;
GURFEED_LOCN_CODE VARCHAR2(6):= NULL;
GURFEED_TRANS_AMT NUMBER(12,2):= 0;
-----------------------------------------------------------
-- Cursors
-----------------------------------------------------------
Cursor c_enc_liq
Is
Select
FGBENCH_NUM,
FGBENCD_ITEM,
FGBENCD_SEQ_NUM,
FGBENCH_TRANS_DATE,
FGBENCD_COAS_CODE,
FGBENCD_FUND_CODE,
FGBENCD_ORGN_CODE,
FGBENCD_ACCT_CODE,
FGBENCD_PROG_CODE,
FGBENCD_ACTV_CODE,
FGBENCD_LOCN_CODE,
FGBENCP_ORIG_ENCB_AMT
From FGBENCH, FGBENCD, FGBENCP
Where FGBENCH_NUM = FGBENCD_NUM
And FGBENCD_NUM = FGBENCP_NUM
And FGBENCD_ITEM = FGBENCP_ITEM
And FGBENCD_SEQ_NUM = FGBENCP_SEQ_NUM
And FGBENCH_STATUS_IND = 'O'
And FGBENCD_STATUS = 'O'
And FGBENCP_ORIG_ENCB_AMT + FGBENCP_SUM_ENCB_ADJT +
FGBENCP_SUM_ENCB_LIQ 0
And SUBSTR(FGBENCH_NUM,1,2) = 'PR';
-----------------------------------------------------------
Cursor c_enc_set
Is
Select
FZRPENC_REC_TYPE,
FZRPENC_FRS_ACCT,
FZRPENC_FRS_SUBCODE,
FZRPENC_DESC,
FZRPENC_AMOUNT,
FZRPENC_ACTIVITY_DATE,
FZRPENC_LINE,
FZRPENC_STATUS
From FZRPENC
Where FZRPENC_STATUS = 'N'
And FZRPENC_REC_TYPE = '053';
-----------------------------------------------------------
Cursor c_bud_set
Is
Select
FZRPENC_REC_TYPE,
FZRPENC_FRS_ACCT,
FZRPENC_FRS_SUBCODE,
FZRPENC_DESC,
FZRPENC_AMOUNT,
FZRPENC_DRCR_IND,
FZRPENC_ACTIVITY_DATE,
FZRPENC_LINE,
FZRPENC_STATUS
From FZRPENC
Where FZRPENC_STATUS = 'N'
And FZRPENC_REC_TYPE = '021';
-----------------------------------------------------------
-- Functions
-----------------------------------------------------------
FUNCTION f_get_xwlk_foap
(frs_string IN VARCHAR2 DEFAULT NULL,
return_entity IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2 IS
banner_value VARCHAR2(2000) := NULL;
BEGIN
CASE upper(return_entity)
WHEN 'FUND' THEN
SELECT FZVACCX_BAN_FUND_CODE INTO banner_value
FROM FZVACCX
WHERE FZVACCX_FRS_ACCOUNT = substr((ltrim(rtrim(frs_string))),1,6);
WHEN 'ORGN' THEN
SELECT FZVACCX_BAN_ORGN_CODE INTO banner_value
FROM FZVACCX
WHERE FZVACCX_FRS_ACCOUNT = substr((ltrim(rtrim(frs_string))),1,6);
WHEN 'PROG' THEN
SELECT FZVACCX_BAN_PROG_CODE INTO banner_value
FROM FZVACCX
WHERE FZVACCX_FRS_ACCOUNT = substr((ltrim(rtrim(frs_string))),1,6);
WHEN 'ACTV' THEN
SELECT FZVACCX_BAN_ACTV_CODE INTO banner_value
FROM FZVACCX
WHERE FZVACCX_FRS_ACCOUNT = substr((ltrim(rtrim(frs_string))),1,6);
WHEN 'LOCN' THEN
SELECT FZVACCX_BAN_LOCN_CODE INTO banner_value
FROM FZVACCX
WHERE FZVACCX_FRS_ACCOUNT = substr((ltrim(rtrim(frs_string))),1,6);
WHEN 'ACCT' THEN
IF substr(frs_string,1,1) = 0 THEN --GL
SELECT FZVOBJX_BAN_ACCT_CODE INTO banner_value
FROM FZVOBJX
WHERE FZVOBJX_FRS_OBJECT = substr((ltrim(rtrim(frs_string))),7,4) --||'G'
AND FZVOBJX_FRS_LEDGER_IND = 'G';
ELSE --SL
SELECT FZVOBJX_BAN_ACCT_CODE INTO banner_value
FROM FZVOBJX
WHERE FZVOBJX_FRS_OBJECT = substr((ltrim(rtrim(frs_string))),7,4) --||'S'
AND FZVOBJX_FRS_LEDGER_IND = 'S';
END IF;
ELSE
RETURN 'YYYYYY';
dbms_output.put_line ('Invalid Parameter Entered for '||frs_string);
END CASE;
RETURN banner_value;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'XXXXXX';
dbms_output.put_line ('No Crosswalk Match for '||frs_string);
WHEN TOO_MANY_ROWS THEN
RETURN 'ZZZZZZ';
dbms_output.put_line ('Multiple Crosswalk Matches for '||frs_string);
WHEN OTHERS THEN
Return '000000';
dbms_output.put_line ('Undefined Error '||frs_string);
END f_get_xwlk_foap;
-----------------------------------------------------------
FUNCTION f_get_feed_seqn (sysid_in VARCHAR2)
RETURN VARCHAR2 AS
doc_out VARCHAR2(8);
BEGIN
UPDATE FOBFSEQ
SET FOBFSEQ_MAXSEQNO = FOBFSEQ_MAXSEQNO + 1,
FOBFSEQ_ACTIVITY_DATE = SYSDATE,
FOBFSEQ_USER_ID = USER
WHERE FOBFSEQ_SYSTEM_ID = sysid_in;
--
SELECT FOBFSEQ_DOCNO_PREFIX || LPAD(to_char(FOBFSEQ_MAXSEQNO),6,'0')
INTO doc_out
FROM FOBFSEQ
WHERE FOBFSEQ_SYSTEM_ID = sysid_in;
--
RETURN doc_out;
EXCEPTION
WHEN OTHERS THEN
DECLARE
err_msg VARCHAR2(100);
BEGIN
err_msg := 'ERR - '||SUBSTR(SQLERRM, 1,100);
RETURN err_msg;
END;
END f_get_feed_seqn;
-----------------------------------------------------------
Function f_get_feed_count
RETURN NUMBER AS
v_count NUMBER(4);
BEGIN
Select Count(*)
Into v_count
From FZRPENC
Where FZRPENC_STATUS = 'N';
Return v_count;
Exception
When NO_DATA_FOUND Then
Return 0;
WHEN OTHERS THEN
Return NULL;
dbms_output.put_line ('ERR - '||SUBSTR(SQLERRM, 1,100));
End f_get_feed_count;
-----------------------------------------------------------
Function f_get_enc_count
RETURN NUMBER AS
v_count NUMBER(4);
BEGIN
Select Count(FGBENCD_FUND_CODE)
Into v_count
From FGBENCD, FGBENCH
Where FGBENCH_NUM = FGBENCD_NUM
And FGBENCH_STATUS_IND = 'O'
And FGBENCD_STATUS = 'O'
And SUBSTR(FGBENCH_NUM,1,2) = 'PR';
Return v_count;
Exception
When NO_DATA_FOUND Then
Return 0;
WHEN OTHERS THEN
Return NULL;
dbms_output.put_line ('ERR - '||SUBSTR(SQLERRM, 1,100));
End f_get_enc_count;
-----------------------------------------------------------
-- Procedures
-----------------------------------------------------------
Procedure Print (string in varchar) is
BEGIN
dbms_output.put_line(string);
END Print;
-----------------------------------------------------------
-- PROCESS BEGINS
-----------------------------------------------------------
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-----------------------------------------------------------
-- STEP 1: Insert Liquidation Records into GURFEED
-- This step should not be performed if processing
-- the first pay period of a new fiscal year.
-----------------------------------------------------------
-- Assign GURFEED Variables
GURFEED_SYSTEM_ID := 'TRANENC';
GURFEED_SYSTEM_TIME_STAMP := TO_CHAR(SYSDATE,'YYYYMMDDHHMMSS');
GURFEED_DOC_CODE := f_get_feed_seqn (GURFEED_SYSTEM_ID);
GURFEED_REC_TYPE := '2';
GURFEED_SUBMISSION_NUMBER := 0;
GURFEED_ITEM_NUM := 1;
GURFEED_ACTIVITY_DATE := SYSDATE;
GURFEED_USER_ID := USER;
GURFEED_RUCL_CODE := 'E032';
GURFEED_DR_CR_IND := '-';
GURFEED_ENCD_ITEM_NUM := 0;
GURFEED_ENCD_ACTION_IND := 'T';
GURFEED_CMT_TYPE := 'U';
GURFEED_ENCB_TYPE := 'L';
v_cnt_check := f_get_enc_count();
If v_cnt_check > 0 Then
-- Assign LOOP Variables and Insert into GURFEED
For v_enc IN c_enc_liq LOOP
Exit When c_enc_liq%NOTFOUND;
GURFEED_ENCD_NUM := v_enc.FGBENCH_NUM;
GURFEED_ENCD_ITEM_NUM := v_enc.FGBENCD_ITEM;
GURFEED_ENCD_SEQ_NUM := v_enc.FGBENCD_SEQ_NUM;
GURFEED_TRANS_DATE := v_enc.FGBENCH_TRANS_DATE;
GURFEED_COAS_CODE := v_enc.FGBENCD_COAS_CODE;
GURFEED_FUND_CODE := v_enc.FGBENCD_FUND_CODE;
GURFEED_ORGN_CODE := v_enc.FGBENCD_ORGN_CODE;
GURFEED_ACCT_CODE := v_enc.FGBENCD_ACCT_CODE;
GURFEED_PROG_CODE := v_enc.FGBENCD_PROG_CODE;
GURFEED_ACTV_CODE := v_enc.FGBENCD_ACTV_CODE;
GURFEED_LOCN_CODE := v_enc.FGBENCD_LOCN_CODE;
GURFEED_TRANS_AMT := v_enc.FGBENCP_ORIG_ENCB_AMT*-1;
GURFEED_TRANS_DESC:= 'Encumbrance Liquidation';
GURFEED_SEQ_NUM := GURFEED_SEQ_NUM + 1;
GURFEED_DOC_REF_NUM := GURFEED_ENCD_NUM;
V_TRANS_AMT_SUM := V_TRANS_AMT_SUM + GURFEED_TRANS_AMT;
Insert Into GURFEED (
GURFEED_SYSTEM_ID,
GURFEED_SYSTEM_TIME_STAMP,
GURFEED_DOC_CODE,
GURFEED_REC_TYPE,
GURFEED_SUBMISSION_NUMBER,
GURFEED_ITEM_NUM,
GURFEED_SEQ_NUM,
GURFEED_ACTIVITY_DATE,
GURFEED_USER_ID,
GURFEED_RUCL_CODE,
GURFEED_DOC_REF_NUM,
GURFEED_TRANS_DATE,
GURFEED_TRANS_AMT,
GURFEED_TRANS_DESC,
GURFEED_DR_CR_IND,
GURFEED_COAS_CODE,
GURFEED_FUND_CODE,
GURFEED_ORGN_CODE,
GURFEED_ACCT_CODE,
GURFEED_PROG_CODE,
GURFEED_ACTV_CODE,
GURFEED_LOCN_CODE,
GURFEED_ENCD_NUM,
GURFEED_ENCD_ITEM_NUM,
GURFEED_ENCD_SEQ_NUM,
GURFEED_ENCD_ACTION_IND,
GURFEED_CMT_TYPE,
GURFEED_ENCB_TYPE)
VALUES (
GURFEED_SYSTEM_ID,
GURFEED_SYSTEM_TIME_STAMP,
GURFEED_DOC_CODE,
GURFEED_REC_TYPE,
GURFEED_SUBMISSION_NUMBER,
GURFEED_ITEM_NUM,
GURFEED_SEQ_NUM,
GURFEED_ACTIVITY_DATE,
GURFEED_USER_ID,
GURFEED_RUCL_CODE,
GURFEED_DOC_REF_NUM,
GURFEED_TRANS_DATE,
GURFEED_TRANS_AMT,
GURFEED_TRANS_DESC,
GURFEED_DR_CR_IND,
GURFEED_COAS_CODE,
GURFEED_FUND_CODE,
GURFEED_ORGN_CODE,
GURFEED_ACCT_CODE,
GURFEED_PROG_CODE,
GURFEED_ACTV_CODE,
GURFEED_LOCN_CODE,
GURFEED_ENCD_NUM,
GURFEED_ENCD_ITEM_NUM,
GURFEED_ENCD_SEQ_NUM,
GURFEED_ENCD_ACTION_IND,
GURFEED_CMT_TYPE,
GURFEED_ENCB_TYPE);
END LOOP;
GURFEED_REC_TYPE := '1';
GURFEED_SEQ_NUM := '0';
GURFEED_TRANS_DESC := 'Encumbrance Liquidation';
-- Header Record for GURFEED Liquidated Transactions
Insert Into GURFEED (
GURFEED_SYSTEM_ID,
GURFEED_SYSTEM_TIME_STAMP,
GURFEED_DOC_CODE,
GURFEED_REC_TYPE,
GURFEED_SUBMISSION_NUMBER,
GURFEED_ITEM_NUM,
GURFEED_SEQ_NUM,
GURFEED_ACTIVITY_DATE,
GURFEED_USER_ID,
GURFEED_DOC_REF_NUM,
GURFEED_TRANS_DATE,
GURFEED_TRANS_AMT,
GURFEED_TRANS_DESC)
Values (
GURFEED_SYSTEM_ID,
GURFEED_SYSTEM_TIME_STAMP,
GURFEED_DOC_CODE,
GURFEED_REC_TYPE,
GURFEED_SUBMISSION_NUMBER,
GURFEED_ITEM_NUM,
GURFEED_SEQ_NUM,
GURFEED_ACTIVITY_DATE,
GURFEED_USER_ID,
GURFEED_DOC_REF_NUM,
GURFEED_TRANS_DATE,
V_TRANS_AMT_SUM,
GURFEED_TRANS_DESC);
Print ('Encumbrance Liquidated: '||GURFEED_DOC_CODE);
Print ('Total Amount: '||V_TRANS_AMT_SUM);
Print (Chr(13));
Else
Print ('No Encumbrance to Liquidate');
Print (Chr(13));
End If;
-----------------------------------------------------------
-- STEP 2: Insert New Transactions Records from FZRPENC
-- Into GURFEED
-----------------------------------------------------------
-- Assign GURFEED Variables
GURFEED_SYSTEM_ID := 'TRANENC';
GURFEED_SYSTEM_TIME_STAMP := TO_CHAR(SYSDATE,'YYYYMMDDHHMMSS');
GURFEED_DOC_CODE := f_get_feed_seqn (GURFEED_SYSTEM_ID);
GURFEED_REC_TYPE := '2';
GURFEED_SUBMISSION_NUMBER := 0;
GURFEED_ITEM_NUM := 1;
GURFEED_ACTIVITY_DATE := SYSDATE;
GURFEED_USER_ID := USER;
GURFEED_RUCL_CODE := 'E10';
GURFEED_DR_CR_IND := '+';
GURFEED_ENCD_ITEM_NUM := 0;
GURFEED_ENCD_ACTION_IND := 'T';
GURFEED_CMT_TYPE := 'U';
GURFEED_ENCD_NUM := GURFEED_DOC_CODE;
GURFEED_ENCB_TYPE := 'L';
GURFEED_ENCD_ITEM_NUM := 1;
GURFEED_ENCD_SEQ_NUM := 0;
GURFEED_TRANS_DATE := SYSDATE;
GURFEED_COAS_CODE := 'C';
V_TRANS_AMT_SUM := 0;
v_cnt_check := f_get_feed_count();
If v_cnt_check > 0 Then
For c_row IN c_enc_set LOOP
Exit When c_enc_set%NOTFOUND;
v_frs_string_acct := c_row.FZRPENC_FRS_ACCT;
v_frs_string_objc := c_row.FZRPENC_FRS_SUBCODE;
v_frs_string := v_frs_string_acct||v_frs_string_objc;
GURFEED_FUND_CODE := f_get_xwlk_foap(v_frs_string, 'FUND');
GURFEED_ORGN_CODE := f_get_xwlk_foap(v_frs_string, 'ORGN');
GURFEED_ACCT_CODE := f_get_xwlk_foap(v_frs_string, 'ACCT');
GURFEED_PROG_CODE := f_get_xwlk_foap(v_frs_string, 'PROG');
GURFEED_ACTV_CODE := f_get_xwlk_foap(v_frs_string, 'ACTV');
GURFEED_LOCN_CODE := f_get_xwlk_foap(v_frs_string, 'LOCN');
GURFEED_TRANS_AMT := c_row.FZRPENC_AMOUNT;
GURFEED_TRANS_DESC:= 'Payroll Encumb -'||v_frs_string;
GURFEED_SEQ_NUM := GURFEED_SEQ_NUM +1;
GURFEED_ENCD_SEQ_NUM := GURFEED_ENCD_SEQ_NUM +1;
V_TRANS_AMT_SUM := V_TRANS_AMT_SUM + GURFEED_TRANS_AMT;
Insert Into GURFEED (
GURFEED_SYSTEM_ID,
GURFEED_SYSTEM_TIME_STAMP,
GURFEED_DOC_CODE,
GURFEED_REC_TYPE,
GURFEED_SUBMISSION_NUMBER,
GURFEED_ITEM_NUM,
GURFEED_SEQ_NUM,
GURFEED_ACTIVITY_DATE,
GURFEED_USER_ID,
GURFEED_RUCL_CODE,
GURFEED_DOC_REF_NUM,
GURFEED_TRANS_DATE,
GURFEED_TRANS_AMT,
GURFEED_TRANS_DESC,
GURFEED_DR_CR_IND,
GURFEED_COAS_CODE,
GURFEED_FUND_CODE,
GURFEED_ORGN_CODE,
GURFEED_ACCT_CODE,
GURFEED_PROG_CODE,
GURFEED_ACTV_CODE,
GURFEED_LOCN_CODE,
GURFEED_ENCD_NUM,
GURFEED_ENCD_ITEM_NUM,
GURFEED_ENCD_SEQ_NUM,
GURFEED_ENCD_ACTION_IND,
GURFEED_CMT_TYPE,
GURFEED_ENCB_TYPE)
VALUES (
GURFEED_SYSTEM_ID,
GURFEED_SYSTEM_TIME_STAMP,
GURFEED_DOC_CODE,
GURFEED_REC_TYPE,
GURFEED_SUBMISSION_NUMBER,
GURFEED_ITEM_NUM,
GURFEED_SEQ_NUM,
GURFEED_ACTIVITY_DATE,
GURFEED_USER_ID,
GURFEED_RUCL_CODE,
GURFEED_DOC_REF_NUM,
GURFEED_TRANS_DATE,
GURFEED_TRANS_AMT,
GURFEED_TRANS_DESC,
GURFEED_DR_CR_IND,
GURFEED_COAS_CODE,
GURFEED_FUND_CODE,
GURFEED_ORGN_CODE,
GURFEED_ACCT_CODE,
GURFEED_PROG_CODE,
GURFEED_ACTV_CODE,
GURFEED_LOCN_CODE,
GURFEED_ENCD_NUM,
GURFEED_ENCD_ITEM_NUM,
GURFEED_ENCD_SEQ_NUM,
GURFEED_ENCD_ACTION_IND,
GURFEED_CMT_TYPE,
GURFEED_ENCB_TYPE);
Update FZRPENC
Set FZRPENC_STATUS = 'C'
Where c_row.FZRPENC_LINE = FZRPENC_LINE;
END LOOP;
-- Open Cursor for FZRPENC Budget Transactions
GURFEED_RUCL_CODE := 'BD2';
For c_row IN c_bud_set LOOP
Exit When c_bud_set%NOTFOUND;
v_frs_string_acct := c_row.FZRPENC_FRS_ACCT;
v_frs_string_objc := c_row.FZRPENC_FRS_SUBCODE;
v_frs_string := v_frs_string_acct||v_frs_string_objc;
GURFEED_FUND_CODE := f_get_xwlk_foap(v_frs_string, 'FUND');
GURFEED_ORGN_CODE := f_get_xwlk_foap(v_frs_string, 'ORGN');
GURFEED_ACCT_CODE := f_get_xwlk_foap(v_frs_string, 'ACCT');
GURFEED_PROG_CODE := f_get_xwlk_foap(v_frs_string, 'PROG');
GURFEED_ACTV_CODE := f_get_xwlk_foap(v_frs_string, 'ACTV');
GURFEED_LOCN_CODE := f_get_xwlk_foap(v_frs_string, 'LOCN');
GURFEED_TRANS_AMT := c_row.FZRPENC_AMOUNT;
GURFEED_DR_CR_IND := c_row.FZRPENC_DRCR_IND;
GURFEED_TRANS_DESC:= 'Payroll Budget Adj-'||v_frs_string;
GURFEED_SEQ_NUM := GURFEED_SEQ_NUM +1;
GURFEED_ENCD_SEQ_NUM := GURFEED_ENCD_SEQ_NUM +1;
V_TRANS_AMT_SUM := V_TRANS_AMT_SUM + GURFEED_TRANS_AMT;
Insert Into GURFEED (
GURFEED_SYSTEM_ID,
GURFEED_SYSTEM_TIME_STAMP,
GURFEED_DOC_CODE,
GURFEED_REC_TYPE,
GURFEED_SUBMISSION_NUMBER,
GURFEED_ITEM_NUM,
GURFEED_SEQ_NUM,
GURFEED_ACTIVITY_DATE,
GURFEED_USER_ID,
GURFEED_RUCL_CODE,
GURFEED_DOC_REF_NUM,
GURFEED_TRANS_DATE,
GURFEED_TRANS_AMT,
GURFEED_TRANS_DESC,
GURFEED_DR_CR_IND,
GURFEED_COAS_CODE,
GURFEED_FUND_CODE,
GURFEED_ORGN_CODE,
GURFEED_ACCT_CODE,
GURFEED_PROG_CODE,
GURFEED_ACTV_CODE,
GURFEED_LOCN_CODE)
VALUES (
GURFEED_SYSTEM_ID,
GURFEED_SYSTEM_TIME_STAMP,
GURFEED_DOC_CODE,
GURFEED_REC_TYPE,
GURFEED_SUBMISSION_NUMBER,
GURFEED_ITEM_NUM,
GURFEED_SEQ_NUM,
GURFEED_ACTIVITY_DATE,
GURFEED_USER_ID,
GURFEED_RUCL_CODE,
GURFEED_DOC_REF_NUM,
GURFEED_TRANS_DATE,
GURFEED_TRANS_AMT,
GURFEED_TRANS_DESC,
GURFEED_DR_CR_IND,
GURFEED_COAS_CODE,
GURFEED_FUND_CODE,
GURFEED_ORGN_CODE,
GURFEED_ACCT_CODE,
GURFEED_PROG_CODE,
GURFEED_ACTV_CODE,
GURFEED_LOCN_CODE);
Update FZRPENC
Set FZRPENC_STATUS = 'C'
Where c_row.FZRPENC_LINE = FZRPENC_LINE;
END LOOP;
-- Header Record for GURFEED Transactions
GURFEED_REC_TYPE := '1';
GURFEED_SEQ_NUM := '0';
GURFEED_TRANS_DESC:= 'Payroll Encumbrance';
Insert Into GURFEED (
GURFEED_SYSTEM_ID,
GURFEED_SYSTEM_TIME_STAMP,
GURFEED_DOC_CODE,
GURFEED_REC_TYPE,
GURFEED_SUBMISSION_NUMBER,
GURFEED_ITEM_NUM,
GURFEED_SEQ_NUM,
GURFEED_ACTIVITY_DATE,
GURFEED_USER_ID,
GURFEED_DOC_REF_NUM,
GURFEED_TRANS_DATE,
GURFEED_TRANS_AMT,
GURFEED_TRANS_DESC)
Values (
GURFEED_SYSTEM_ID,
GURFEED_SYSTEM_TIME_STAMP,
GURFEED_DOC_CODE,
GURFEED_REC_TYPE,
GURFEED_SUBMISSION_NUMBER,
GURFEED_ITEM_NUM,
GURFEED_SEQ_NUM,
GURFEED_ACTIVITY_DATE,
GURFEED_USER_ID,
GURFEED_DOC_REF_NUM,
GURFEED_TRANS_DATE,
V_TRANS_AMT_SUM,
GURFEED_TRANS_DESC);
-- Header Record for FGBENCH Table
FGBENCH_NUM := GURFEED_DOC_CODE;
FGBENCH_ACTIVITY_DATE := SYSDATE;
FGBENCH_USER_ID := USER;
FGBENCH_DESC := 'Payroll Encumbrance Load';
FGBENCH_TRANS_DATE := SYSDATE;
FGBENCH_DOC_AMT := V_TRANS_AMT_SUM;
FGBENCH_TYPE := 'L';
FGBENCH_STATUS_IND := 'O';
FGBENCH_STATUS := 'C';
FGBENCH_EDIT_DEFERRAL := 'N';
FGBENCH_ESTAB_DATE := SYSDATE;
FGBENCH_APPR_IND := 'Y';
FGBENCH_NSF_ON_OFF_IND := 'Y';
Insert Into FGBENCH (
FGBENCH_NUM,
FGBENCH_ACTIVITY_DATE,
FGBENCH_USER_ID,
FGBENCH_DESC,
FGBENCH_TRANS_DATE,
FGBENCH_DOC_AMT,
FGBENCH_TYPE,
FGBENCH_STATUS_IND,
FGBENCH_STATUS,
FGBENCH_EDIT_DEFERRAL,
FGBENCH_ESTAB_DATE,
FGBENCH_APPR_IND,
FGBENCH_NSF_ON_OFF_IND)
Values (
FGBENCH_NUM,
FGBENCH_ACTIVITY_DATE,
FGBENCH_USER_ID,
FGBENCH_DESC,
FGBENCH_TRANS_DATE,
FGBENCH_DOC_AMT,
FGBENCH_TYPE,
FGBENCH_STATUS_IND,
FGBENCH_STATUS,
FGBENCH_EDIT_DEFERRAL,
FGBENCH_ESTAB_DATE,
FGBENCH_APPR_IND,
FGBENCH_NSF_ON_OFF_IND);
-- Summary Information
Print ('Encumbrance Established: '||GURFEED_DOC_CODE);
Print ('Total Amount: '||V_TRANS_AMT_SUM);
Print (Chr(13));
Else
Print ('No Records in FZRPENC to Establish');
Print (Chr(13));
End If;
Print ('***** FZRPENC Complete *****');
End;
/
Exit
/
FZRPENC Setup SQL
-----------------------------------------------------------
-- Create New Tables
-----------------------------------------------------------
Drop Table FZRPENC;
Create Table FZRPENC
(
FZRPENC_REC_TYPE VARCHAR2(3),
FZRPENC_FRS_ACCT VARCHAR2(6),
FZRPENC_FRS_SUBCODE VARCHAR2(4),
FZRPENC_DESC VARCHAR2(35),
FZRPENC_AMOUNT NUMBER(12,2),
FZRPENC_DRCR_IND VARCHAR2(1),
FZRPENC_ACTIVITY_DATE DATE,
FZRPENC_LINE NUMBER(4),
FZRPENC_STATUS VARCHAR2(1)
);
/
Drop Public Synonym FZRPENC;
Create Public Synonym FZRPENC for FZRPENC;
/
Grant All on FZRPENC to PUBLIC;
/
-- FZVACCX
-- FRS Account Crosswalk Table
DROP TABLE FZVACCX;
CREATE TABLE FZVACCX (
FZVACCX_FRS_ACCOUNT VARCHAR2(6),
FZVACCX_FRS_DESCRIPTION VARCHAR2(50),
FZVACCX_BAN_FUND_CODE VARCHAR2(6),
FZVACCX_BAN_ORGN_CODE VARCHAR2(6),
FZVACCX_BAN_PROG_CODE VARCHAR2(6),
FZVACCX_BAN_ACTV_CODE VARCHAR2(6),
FZVACCX_BAN_LOCN_CODE VARCHAR2(6),
CONSTRAINT PK_FZVACCX PRIMARY KEY (FZVACCX_FRS_ACCOUNT) ENABLE);
/
DROP PUBLIC SYNONYM FZVACCX;
CREATE PUBLIC SYNONYM FZVACCX FOR FZVACCX;
/
Grant All on FZVACCX to PUBLIC;
/
-- FZVOBJX
-- FRS Subcode/Object Code Crosswalk Table
-- Many clients place a ledger indicator after the object code
-- Example: 0100G, or 0123S
DROP TABLE FZVOBJX;
CREATE TABLE FZVOBJX (
FZVOBJX_FRS_OBJECT VARCHAR2(4),
FZVOBJX_FRS_LEDGER_IND VARCHAR2(1),
FZVOBJX_FRS_DESCRIPTION VARCHAR2(50),
FZVOBJX_BAN_ACCT_CODE VARCHAR2(6),
CONSTRAINT PK_FZVOBJX PRIMARY KEY (FZVOBJX_FRS_OBJECT, FZVOBJX_FRS_LEDGER_IND) ENABLE);
/
DROP PUBLIC SYNONYM FZVOBJX;
CREATE PUBLIC SYNONYM FZVOBJX FOR FZVOBJX;
/
Grant All on FZVOBJX to PUBLIC;
/
-----------------------------------------------------------
-- Create New Rule Class Code Entry
-----------------------------------------------------------
-- New RUCL Code E10
Delete From FTVEDIT Where FTVEDIT_RUCL_CODE = 'E10';
Delete from FTVRULP Where FTVRULP_RUCL_CODE = 'E10';
Delete from FTVRUCL Where FTVRUCL_RUCL_CODE = 'E10';
-- INSERTING into FTVRUCL
Insert into FTVRUCL (
FTVRUCL_RUCL_CODE,
FTVRUCL_EFF_DATE,
FTVRUCL_ACTIVITY_DATE,
FTVRUCL_USER_ID,
FTVRUCL_NCHG_DATE,
FTVRUCL_TERM_DATE,
FTVRUCL_CLASS_TYPE,
FTVRUCL_CLASS_TITLE,
FTVRUCL_STATUS_IND,
FTVRUCL_POST_BAVL,
FTVRUCL_BALANCE_METHOD)
Values (
'E10',
SYSDATE-365,
SYSDATE,
USER,
null,
null,
'J',
'Post Original Encumbrance NEW',
'A',
'E',
'S'
);
-- INSERTING into FTVRULP
Insert into FTVRULP (
FTVRULP_RUCL_CODE,
FTVRULP_RULP_CODE,
FTVRULP_EFF_DATE,
FTVRULP_PROC_CODE,
FTVRULP_POSTING_ACTION,
FTVRULP_ACCRUAL_IMPACT,
FTVRULP_AMT_IND,
FTVRULP_POST_ZERO_IND)
Values (
'E10',
10,
SYSDATE-365,
'I011',
'N',
'C',
'C',
'Y'
);
Insert into FTVRULP (
FTVRULP_RUCL_CODE,
FTVRULP_RULP_CODE,
FTVRULP_EFF_DATE,
FTVRULP_PROC_CODE,
FTVRULP_POSTING_ACTION,
FTVRULP_ACCRUAL_IMPACT,
FTVRULP_AMT_IND,
FTVRULP_POST_ZERO_IND)
Values (
'E10',
20,
SYSDATE-365,
'E010',
'N',
'C',
'C',
'Y'
);
-- INSERTING into FTVEDIT
Insert into FTVEDIT (
FTVEDIT_RUCL_CODE,
FTVEDIT_EDIT_SEQ_NUM,
FTVEDIT_EFF_DATE,
FTVEDIT_FIELD,
FTVEDIT_EDIT_CODE,
FTVEDIT_CONTINUE_ON_ERROR,
FTVEDIT_ERROR_SEVERITY,
FTVEDIT_OPERAND_1_ENTITY,
FTVEDIT_OPERAND_1_ELEMENT,
FTVEDIT_OPERATOR,
FTVEDIT_OPERAND_2_ENTITY,
FTVEDIT_OPERAND_2_ELEMENT,
FTVEDIT_LITERAL_1,
FTVEDIT_LITERAL_2,
FTVEDIT_ERROR_MESSAGE)
Values (
'E10',
10,
SYSDATE-365,
'BUDGET_PERIOD',
'0005',
'Y',
null,
null,
null,
'=',
null,
null,
'01',
null,
'ERROR - Budget Period Required'
);
Insert into FTVEDIT (
FTVEDIT_RUCL_CODE,
FTVEDIT_EDIT_SEQ_NUM,
FTVEDIT_EFF_DATE,
FTVEDIT_FIELD,
FTVEDIT_EDIT_CODE,
FTVEDIT_CONTINUE_ON_ERROR,
FTVEDIT_ERROR_SEVERITY,
FTVEDIT_OPERAND_1_ENTITY,
FTVEDIT_OPERAND_1_ELEMENT,
FTVEDIT_OPERATOR,
FTVEDIT_OPERAND_2_ENTITY,
FTVEDIT_OPERAND_2_ELEMENT,
FTVEDIT_LITERAL_1,
FTVEDIT_LITERAL_2,
FTVEDIT_ERROR_MESSAGE)
Values (
'E10',
20,
SYSDATE-365,
'BANK_CODE',
'2802',
'Y',
null,
null,
null,
null,
null,
null,
null,
null,
'Error - Bank Code did not default.'
);
/
Commit;
/
-----------------------------------------------------------
-- Functions
-----------------------------------------------------------
-- f_get_jobsub_parm
-- This function obtains the job submission variables entered
-- by the user.
DROP FUNCTION f_get_jobsub_parm;
CREATE OR REPLACE
FUNCTION f_get_jobsub_parm (process VARCHAR2,
parm_num VARCHAR2,
oneup VARCHAR2)
RETURN VARCHAR2 IS
return_field VARCHAR2(99);
return_err VARCHAR2(99);
BEGIN
SELECT gjbprun_value
INTO return_field
FROM gjbprun
WHERE upper(process) = upper(gjbprun_job)
AND parm_num = gjbprun_number
AND to_number(oneup) = gjbprun_one_up_no;
RETURN return_field;
EXCEPTION
When NO_DATA_FOUND Then
return_err := 'No data found';
dbms_output.put_line(return_err);
RETURN NULL;
WHEN OTHERS THEN
return_err := substr('Missing or invalid parameter for '||
process ||' '||oneup||' '||parm_num||' ['||SQLERRM||']',1,59);
dbms_output.put_line(return_err);
RETURN NULL;
END f_get_jobsub_parm;
/
DROP PUBLIC SYNONYM f_get_jobsub_parm;
CREATE PUBLIC SYNONYM f_get_jobsub_parm FOR f_get_jobsub_parm;
/
GRANT EXECUTE ON f_get_jobsub_parm TO PUBLIC;
/
-----------------------------------------------------------
-- Security Objects
-----------------------------------------------------------
-- Security Object (GURAOBJ)
DELETE FROM BANSECR.GURAOBJ
WHERE GURAOBJ_OBJECT = 'FZRPENC';
INSERT INTO BANSECR.GURAOBJ
(GURAOBJ_OBJECT,
GURAOBJ_DEFAULT_ROLE,
GURAOBJ_CURRENT_VERSION,
GURAOBJ_SYSI_CODE,
GURAOBJ_ACTIVITY_DATE,
GURAOBJ_CHECKSUM)
VALUES
('FZRPENC',
'BAN_DEFAULT_M',
'8.0',
'F',
SYSDATE,
NULL);
COMMIT;
-- Security Class (GURUOBJ)
DELETE FROM BANSECR.GURUOBJ
WHERE GURUOBJ_OBJECT = 'FZRPENC';
INSERT INTO BANSECR.GURUOBJ
(GURUOBJ_OBJECT,
GURUOBJ_ROLE,
GURUOBJ_USERID,
GURUOBJ_ACTIVITY_DATE)
VALUES
('FZRPENC',
'BAN_DEFAULT_M',
'BAN_FINANCE_C',
SYSDATE);
COMMIT;
/
/******************************************************
****** Job Submission
******************************************************/
-- Job Submission Object (GUBOBJS)
DELETE FROM gubobjs
WHERE gubobjs_name = 'FZRPENC';
INSERT INTO gubobjs
(gubobjs_name,
gubobjs_desc,
gubobjs_objt_code,
gubobjs_sysi_code,
gubobjs_user_id,
gubobjs_activity_date,
gubobjs_help_ind,
gubobjs_extract_enabled_ind)
VALUES
('FZRPENC',
'Payroll Encumbrance Load',
'JOBS',
'F',
USER,
SYSDATE,
'N',
'N');
COMMIT;
-- Job Submission Job (GJBJOBS)
DELETE FROM GJBJOBS
WHERE GJBJOBS_NAME = 'FZRPENC';
INSERT INTO GJBJOBS
(GJBJOBS_NAME,
GJBJOBS_TITLE,
GJBJOBS_ACTIVITY_DATE,
GJBJOBS_SYSI_CODE,
GJBJOBS_JOB_TYPE_IND,
GJBJOBS_DESC,
GJBJOBS_COMMAND_NAME)
VALUES
('FZRPENC',
'HRS Encumbrance Load',
SYSDATE,
'F',
'P',
'HRS Encumbrance Load',
'fzrpenc');
COMMIT;
/
-- Job Submission Definitions (GJBPDEF)
Delete from GJBPDEF
Where GJBPDEF_JOB = 'FZRPENC';
INSERT INTO GJBPDEF
(GJBPDEF_JOB,
GJBPDEF_NUMBER,
GJBPDEF_DESC,
GJBPDEF_LENGTH,
GJBPDEF_TYPE_IND,
GJBPDEF_OPTIONAL_IND,
GJBPDEF_SINGLE_IND,
GJBPDEF_ACTIVITY_DATE,
GJBPDEF_HELP_TEXT)
VALUES
('FZRPENC',
'01',
'File Path',
'25',
'C',
'R',
'S',
SYSDATE,
'Enter the path with a following slash (/)');
INSERT INTO GJBPDEF
(GJBPDEF_JOB,
GJBPDEF_NUMBER,
GJBPDEF_DESC,
GJBPDEF_LENGTH,
GJBPDEF_TYPE_IND,
GJBPDEF_OPTIONAL_IND,
GJBPDEF_SINGLE_IND,
GJBPDEF_ACTIVITY_DATE,
GJBPDEF_HELP_TEXT)
VALUES
('FZRPENC',
'02',
'File Name',
'25',
'C',
'R',
'S',
SYSDATE,
'Enter filename, case sensitive');
COMMIT;
/
-- Job Submission Default Definitions (GJBPDFT)
DELETE FROM GJBPDFT
WHERE GJBPDFT_JOB = 'FZRPENC';
INSERT INTO GJBPDFT
(GJBPDFT_JOB,
GJBPDFT_NUMBER,
GJBPDFT_ACTIVITY_DATE)
VALUES
('FZRPENC',
'01',
SYSDATE);
INSERT INTO GJBPDFT
(GJBPDFT_JOB,
GJBPDFT_NUMBER,
GJBPDFT_ACTIVITY_DATE)
VALUES
('FZRPENC',
'02',
SYSDATE);
COMMIT;
/
/******************************************************
****** System Data
******************************************************/
DELETE FROM FTVSDAT
WHERE (FTVSDAT_SDAT_CODE_ENTITY = 'FGBTRNI'
AND FTVSDAT_SDAT_CODE_OPT_1 = 'TRANENC');
INSERT INTO FTVSDAT
(FTVSDAT_SDAT_CODE_ENTITY,
FTVSDAT_SDAT_CODE_ATTR,
FTVSDAT_SDAT_CODE_OPT_1,
FTVSDAT_EFF_DATE,
FTVSDAT_STATUS_IND,
FTVSDAT_ACTIVITY_DATE,
FTVSDAT_USER_ID,
FTVSDAT_TITLE,
FTVSDAT_SHORT_TITLE,
FTVSDAT_DATA)
VALUES
('FGBTRNI',
'SYSTEM_ID',
'TRANENC',
ADD_MONTHS(SYSDATE,-36),
'A',
SYSDATE,
USER,
'HRS Encumbrance Load',
'HRS Encumbrance Load',
'DS');
COMMIT;
-- User Sequence Table (FOBFSEQ)
DELETE FROM FOBFSEQ
WHERE FOBFSEQ_SYSTEM_ID = 'TRANENC';
INSERT INTO FOBFSEQ
(FOBFSEQ_SYSTEM_ID,
FOBFSEQ_DOCNO_PREFIX,
FOBFSEQ_MAXSEQNO,
FOBFSEQ_DESC,
FOBFSEQ_ACTIVITY_DATE,
FOBFSEQ_USER_ID)
VALUES
('TRANENC',
'PR',
000001,
'HRS Encumbrance Load',
SYSDATE,
USER);
COMMIT;
/
Exit
/
RECOMMENDATIONS
1. Test this and all development work thoroughly before using in a production environment.
ACTION ITEMS FOR FOLLOW-UP
|Date |Item |Owner |Date Due |Status |
| | | | | |
| | | | | |
Supplemental Documents
The following supplemental documents support and detail the activities and services performed:
|Document Name |Description |
|20090313_fzrpenc_setup.txt |Setup SQL for the FZRPENC process, incomplete for entire job |
| |submission finalization. |
|20090313_fzrpenc_sql.txt |Process SQL for the FZRPENC process, incomplete for job submission |
| |finalization. |
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- support services worker job description
- management services department circulars
- rushmore financial services inc
- amazon web services revenue
- acura financial services my account
- acura financial services payoff address
- community support services job description
- american financial services scam
- acura financial services po box
- access financial management services colorado
- acura financial services leasing address
- acura financial services overnight address