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.

Google Online Preview   Download