--**Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:) This file is to be used only by SunGardHE Banner clients. Do not make it public. This file contains a collection of Views that are very useful in Banner Ad Hoc Reporting. Most are for Current Record sets for the respective Banner Tables. I have found that most Ad Hoc users will find these completely adequate for 99% of their reports without requiring them to understand or code the Banner Record selection criteria or create a single sub-Query. This gives them a tool that is quickly useful to them. Combining these Views into a new Query using a reporting tool such as Argos just requires JOINing the Views and the Query is working. While programming this way may not give the most efficient code, it will usually result in the Ad Hoc programmer using far less total computing resources because they get the results they need without running dozens of tests. Often they will only run a report a few times a year, but if they do develop a long running report that needs to be run frequently, a programmer can optimize the code for them using their solution as a content guide. Optimizing code is something a Function Ad Hoc user would be unlikely to accomplish. BEFORE YOU USE THESE: Do create you Views one at a time. Do not process these in a batch. Always test your new Views and compare them to your Banner. These Views may have mistakes and Banner sites frequently have very unique data loaded that makes a universal query impossible or very ineffective. Below are some helpful substitutions for making these work for your site: DO NOT Replace All, check each should actually be changed, some of these values are applied to multiple meanings): We use 'WK' for our Work Address and Work Phone codes. Search for them (do include the quote marks) and replace them with your values but be sure it is a value you really should change. In particular, PPRCCMT_CMTY_CODE = 'WT' locates where we store our full Working Titles, but there may be other such multiple uses. We use '^' for our Chart of Accounts (just kidding), search for '^' and replace it with your Chart. We use 'RE' for Residence or Home We use 'CM' for Check Mailing Address We use 'CE' for Cell Phone (SPRTELE_TELE_CODE = 'CE' AND SPRTELE_ATYP_CODE = 'WK') We use 'WK' for Work Phone (SPRTELE_TELE_CODE = 'WK' AND SPRTELE_ATYP_CODE = 'WK') Consider using _CRV or _CRS instead of _V for your Current Record Views (Sets) names. Why is the --DESC Viewname included for each View? I like to check before I zap a perfectly good existing View. Thought you might too. What is TITLES_AGENTS and TITLES_SPECIALIST? Just rather unusual special selections of Titles that are meaningful to Cooperative Extension Service. I thought they might suggest some special very useful Views to you for your unique Titles. Where is the documentation. I might add it after you send it back to me. Most of these are very obvious. Most of the Banner Tables do have documentation and it may prove useful to you. Why are some commented lines left in the code. To emphasize that it is not included. There are many other Tables that you may wish to create a View. We do not have Student or Student related Tables. You may find some Object Access Views to be very useful too. After creating a View, we control access to it using an Oracle Role: grant select on owner.Viewname to Argos_Reporting_Oracle_Role; Your DBA will likely have a strong opinion about how to handle this. You should find these Views below: Active_Employees Very odd Query that is useful for fixing a Query that would otherwise be complete. Active_Jobs Very similar concept Active_Secondary_Jobs Ages Just PIDM and Age. Use with care. I find this info more sensitive to some than their SSN. FGBTRNH_V Just since 06, you might want to adjust that but be careful FTVACCT_POOL_DESC_V FTVACCT_POOL_V FTVACCT_V FTVACTV_V FTVASTY_V FTVATYP_V FTVCOMM_V FTVEDIT_V FTVFMGR_V FTVFTYP_V FTVFUND_V FTVINVM_V FTVLOCN_V FTVORGN_PRED_V FTVORGN_V FTVPROG_V FTVPROJ_V FTVRUCL_V FTVSDAT_V FTVSHIP_V FTVTGRP_V FTVTRAT_V FTVVEND_V FTVVTYP_V GOREMAL_V LENGTH_OF_SERVICE Names Names_Active_Employees Names_Vendors NBBPOSN_V NBRBJOB_V NBRJLBD_V NBRJOBS_V PDRDEDN_V PEBEMPL_V (Why include a Table with no selection criteria? Just for completeness and to TRUNC the dates.) PERREVW_CURRENT_REVIEW_V PEVEMPL_WITH_SOURCE_FIELDS_V PEVJOBS_WITH_SOURCE_FIELDS_V PPRCCMT_WITH_PTVCMTY_V PRRCCMT_WITH_PTVCMTY_V PTRBDPL_V SORDEGR_WITH_STVDEGC_V SPBPERS_V SPRADDR_CHECK_MAILING_V SPRADDR_HOME_V SPRADDR_V SPRADDR_WORK_V SPRIDEN_OTHER_V SPRIDEN_PEOPLE_V SPRIDEN_V SPRIDEN_VENDORS_V SPRTELE_CELL_V SPRTELE_FAX_V SPRTELE_HOME_V SPRTELE_V SPRTELE_WORK_V TEMPLATE_V (I used this to create most of the code for these Views. To avoid some of the busy work.) TITLES_AGENTS (We have County Agents - those nice folks that help you with your weeds, etc.) TITLES_EXTENDED_WORKING TITLES_FOR_APPOINTMENTS TITLES_FOR_APPT_ PCT_PER TITLES_POSITION TITLES_SPECIALIST (Another title group we use frequently.) --DESC ACTIVE_EMPLOYEES -- active_employees_create.sql Current Records VIEW from TABLE PEBEMPL CREATE OR REPLACE VIEW ACTIVE_EMPLOYEES ( PIDM ) AS SELECT -- Active Status pidms per PEBEMPL Employee Base Table PEBEMPL_PIDM PIDM FROM PEBEMPL WHERE pebempl_coas_code_dist = '^' and PEBEMPL_EMPL_STATUS = 'A' AND (PEBEMPL_TERM_DATE > TRUNC(SYSDATE) OR PEBEMPL_TERM_DATE IS NULL) AND PEBEMPL_LAST_WORK_DATE Is Null ; --DESC ACTIVE_JOBS -- active_jobs_create.sql Current Records VIEW from TABLE -- Active Jobs PIDM-POSN CREATE OR REPLACE VIEW ACTIVE_JOBS ( PIDM, POSN ) AS SELECT -- Active Jobs PIDM-POSN NBRJOBS_PIDM, NBRJOBS_POSN FROM NBRJOBS A, NBRBJOB WHERE NBRJOBS_EFFECTIVE_DATE = (SELECT MAX(B.NBRJOBS_EFFECTIVE_DATE) FROM POSNCTL.NBRJOBS B WHERE B.NBRJOBS_PIDM = A.NBRJOBS_PIDM AND B.NBRJOBS_POSN = A.NBRJOBS_POSN AND B.NBRJOBS_SUFF = A.NBRJOBS_SUFF AND B.NBRJOBS_EFFECTIVE_DATE <= SYSDATE) AND NBRJOBS_STATUS = 'A' AND NBRJOBS_PIDM = NBRBJOB_PIDM AND NBRJOBS_POSN = NBRBJOB_POSN AND (TRUNC(NBRBJOB_BEGIN_DATE) < SYSDATE OR NBRBJOB_BEGIN_DATE IS NULL) AND (TRUNC(NBRBJOB_END_DATE) >= SYSDATE OR NBRBJOB_END_DATE IS NULL) ; --DESC ACTIVE_SECONDARY_JOBS -- active_secondary_jobs_create.sql Current Records VIEW from TABLE -- ACTIVE SECONDARY Jobs PIDM-POSN CREATE OR REPLACE VIEW ACTIVE_SECONDARY_JOBS ( PIDM, POSN ) AS SELECT -- Active Jobs PIDM-POSN NBRJOBS_PIDM PIDM, NBRJOBS_POSN POSN FROM NBRJOBS A, NBRBJOB WHERE NBRJOBS_EFFECTIVE_DATE = (SELECT MAX(B.NBRJOBS_EFFECTIVE_DATE) FROM POSNCTL.NBRJOBS B WHERE B.NBRJOBS_PIDM = A.NBRJOBS_PIDM AND B.NBRJOBS_POSN = A.NBRJOBS_POSN AND B.NBRJOBS_SUFF = A.NBRJOBS_SUFF AND B.NBRJOBS_EFFECTIVE_DATE <= SYSDATE) AND NBRJOBS_STATUS = 'A' AND NBRJOBS_PIDM = NBRBJOB_PIDM AND NBRJOBS_POSN = NBRBJOB_POSN AND (TRUNC(NBRBJOB_BEGIN_DATE) < SYSDATE OR NBRBJOB_BEGIN_DATE IS NULL) AND (TRUNC(NBRBJOB_END_DATE) >= SYSDATE OR NBRBJOB_END_DATE IS NULL) AND nbrbjob_contract_type = 'S' ; --DESC AGES -- ages_create.sql Current Records VIEW from TABLE SPBPERS CREATE OR REPLACE VIEW AGES ( PIDM, AGE ) AS SELECT spbpers_pidm, -- based upon age function in Banner f_calculate_age (SYSDATE, spbpers_birth_date, spbpers_dead_date) F_AGE, TRUNC(MONTHS_BETWEEN(TRUNC(NVL(spbpers_dead_date,NVL(TRUNC(SYSDATE),SYSDATE))),TRUNC(spbpers_birth_date)) / 12) FROM spbpers ; --DESC FGBTRNH_V -- FGBTRNH_v_create.sql Current Records VIEW from TABLE FGBTRNH CREATE OR REPLACE VIEW FGBTRNH_V ( FGBTRNH_RUCL_CODE, FGBTRNH_DOC_SEQ_CODE, FGBTRNH_DOC_CODE, FGBTRNH_SUBMISSION_NUMBER, FGBTRNH_ITEM_NUM, FGBTRNH_SEQ_NUM, FGBTRNH_SERIAL_NUM, FGBTRNH_ACTIVITY_DATE, FGBTRNH_USER_ID, FGBTRNH_DOC_REF_NUM, FGBTRNH_TRANS_DATE, FGBTRNH_TRANS_AMT, FGBTRNH_TRANS_DESC, FGBTRNH_DR_CR_IND, FGBTRNH_Trans_Amount, -- If dr_cr_ind is not +,-,D, or C, then use the FGBTRNH_TRANS_AMT value FGBTRNH_BANK_CODE, FGBTRNH_BUD_ID, FGBTRNH_COAS_CODE, FGBTRNH_FSYR_CODE, FGBTRNH_ACCI_CODE, FGBTRNH_FUND_CODE, FGBTRNH_ORGN_CODE, FGBTRNH_ACCT_CODE, FGBTRNH_PROG_CODE, FGBTRNH_ACTV_CODE, FGBTRNH_LOCN_CODE, FGBTRNH_POSTING_PERIOD, FGBTRNH_DEP_NUM, FGBTRNH_ENCD_NUM, FGBTRNH_ENCD_ITEM_NUM, FGBTRNH_ENCD_SEQ_NUM, FGBTRNH_ENCD_ACTION_IND, FGBTRNH_PRJD_CODE, FGBTRNH_AUTO_JRNL_ID, FGBTRNH_REVERSAL_IND, FGBTRNH_DIST_PCT, FGBTRNH_BUD_DISPN, FGBTRNH_CMT_TYPE, FGBTRNH_CMT_PCT, FGBTRNH_ENCB_TYPE, FGBTRNH_VENDOR_PIDM, FGBTRNH_BUDGET_PERIOD, FGBTRNH_ACCRUAL_IND, FGBTRNH_ABAL_OVERRIDE, FGBTRNH_CURR_CODE, FGBTRNH_CONVERT_AMT, FGBTRNH_EXCHANGE_AMT, FGBTRNH_EXCHANGE_DIFF, FGBTRNH_SUM_POST_DOC_CODE, FGBTRNH_COAS_CODE_POOL, FGBTRNH_FUND_CODE_POOL ) AS SELECT -- Created from TABLE FGBTRNH Archive Transaction History Table FGBTRNH_RUCL_CODE, FGBTRNH_DOC_SEQ_CODE, FGBTRNH_DOC_CODE, FGBTRNH_SUBMISSION_NUMBER, FGBTRNH_ITEM_NUM, FGBTRNH_SEQ_NUM, FGBTRNH_SERIAL_NUM, TRUNC(FGBTRNH_ACTIVITY_DATE) FGBTRNH_ACTIVITY_DATE, FGBTRNH_USER_ID, FGBTRNH_DOC_REF_NUM, TRUNC(FGBTRNH_TRANS_DATE) FGBTRNH_TRANS_DATE, FGBTRNH_TRANS_AMT, FGBTRNH_TRANS_DESC, FGBTRNH_DR_CR_IND, -- Use FGBTRNH_Trans_Amount with caution. We are applying the same rule as with NHRDIST here. 6-12-2001 NVL(DECODE(fgbtrnh_dr_cr_ind, '+',fgbtrnh_trans_amt, '-',(0-fgbtrnh_trans_amt), 'D',fgbtrnh_trans_amt, 'C',(0-fgbtrnh_trans_amt), fgbtrnh_trans_amt),0) " FGBTRNH_Trans_Amount", -- If dr_cr_ind is not +,-,D, or C, then use the FGBTRNH_TRANS_AMT value FGBTRNH_BANK_CODE, FGBTRNH_BUD_ID, FGBTRNH_COAS_CODE, FGBTRNH_FSYR_CODE, FGBTRNH_ACCI_CODE, FGBTRNH_FUND_CODE, FGBTRNH_ORGN_CODE, FGBTRNH_ACCT_CODE, FGBTRNH_PROG_CODE, FGBTRNH_ACTV_CODE, FGBTRNH_LOCN_CODE, FGBTRNH_POSTING_PERIOD, FGBTRNH_DEP_NUM, FGBTRNH_ENCD_NUM, FGBTRNH_ENCD_ITEM_NUM, FGBTRNH_ENCD_SEQ_NUM, FGBTRNH_ENCD_ACTION_IND, FGBTRNH_PRJD_CODE, FGBTRNH_AUTO_JRNL_ID, FGBTRNH_REVERSAL_IND, FGBTRNH_DIST_PCT, FGBTRNH_BUD_DISPN, FGBTRNH_CMT_TYPE, FGBTRNH_CMT_PCT, FGBTRNH_ENCB_TYPE, FGBTRNH_VENDOR_PIDM, FGBTRNH_BUDGET_PERIOD, FGBTRNH_ACCRUAL_IND, FGBTRNH_ABAL_OVERRIDE, FGBTRNH_CURR_CODE, FGBTRNH_CONVERT_AMT, FGBTRNH_EXCHANGE_AMT, FGBTRNH_EXCHANGE_DIFF, FGBTRNH_SUM_POST_DOC_CODE, FGBTRNH_COAS_CODE_POOL, FGBTRNH_FUND_CODE_POOL FROM FGBTRNH WHERE FGBTRNH_FSYR_CODE > '06' ; --DESC FTVACCT_POOL_DESC_V -- FTVACCT_POOL_DESC_v_create.sql Current Records VIEW from TABLE FTVACCT_POOL_DESC CREATE OR REPLACE VIEW FTVACCT_POOL_DESC_V ( ACCT_CODE, -- 1st Query TITLE, POOL, POOL_TITLE, EFF_DATE, NCHG_DATE, TERM_DATE ) AS SELECT * FROM ( SELECT -- note that this is the FTVACCT Query with Pool Title Added (only accts and descriptions here, see FTVACCT_POOL for full Query) -- see the UNION ALL The 1st Query is for Non-Pooled Accts -- The 2nd Query is for Pooled Accts FTVACCT_ACCT_CODE ACCT_CODE, -- 1st Query FTVACCT_TITLE TITLE, ' ' POOL, ' ' POOL_TITLE, TRUNC(FTVACCT_EFF_DATE) EFF_DATE, TRUNC(FTVACCT_NCHG_DATE) NCHG_DATE, TRUNC(FTVACCT_TERM_DATE) TERM_DATE FROM FTVACCT WHERE TRUNC(FTVACCT_EFF_DATE) <= SYSDATE AND (FTVACCT_NCHG_DATE > TRUNC(SYSDATE) OR FTVACCT_NCHG_DATE IS NULL) AND (FTVACCT_TERM_DATE > TRUNC(SYSDATE) OR FTVACCT_TERM_DATE IS NULL) AND FTVACCT_ACCT_CODE_POOL IS NULL UNION SELECT A.FTVACCT_ACCT_CODE ACCT_CODE, -- 2nd Query A.FTVACCT_TITLE TITLE, XREF_ACCT_CODE_POOL POOL, POOL_TITLE POOL_TITLE, TRUNC(A.FTVACCT_EFF_DATE) EFF_DATE, TRUNC(A.FTVACCT_NCHG_DATE) NCHG_DATE, TRUNC(A.FTVACCT_TERM_DATE) TERM_DATE FROM (SELECT A.FTVACCT_ACCT_CODE XREF_ACCT_CODE_POOL, -- Actually ACCT A.FTVACCT_TITLE POOL_TITLE FROM FTVACCT A WHERE TRUNC(A.FTVACCT_EFF_DATE) <= SYSDATE AND (A.FTVACCT_NCHG_DATE > TRUNC(SYSDATE) OR A.FTVACCT_NCHG_DATE IS NULL) AND (A.FTVACCT_TERM_DATE > TRUNC(SYSDATE) OR A.FTVACCT_TERM_DATE IS NULL) ), FTVACCT A WHERE TRUNC(A.FTVACCT_EFF_DATE) <= SYSDATE AND (A.FTVACCT_NCHG_DATE > TRUNC(SYSDATE) OR A.FTVACCT_NCHG_DATE IS NULL) AND (A.FTVACCT_TERM_DATE > TRUNC(SYSDATE) OR A.FTVACCT_TERM_DATE IS NULL) AND A.FTVACCT_ACCT_CODE_POOL IS NOT NULL AND A.FTVACCT_ACCT_CODE_POOL = XREF_ACCT_CODE_POOL ) WHERE TRUNC(EFF_DATE) <= SYSDATE AND (NCHG_DATE > TRUNC(SYSDATE) OR NCHG_DATE IS NULL) AND (TERM_DATE > TRUNC(SYSDATE) OR TERM_DATE IS NULL) ; --DESC FTVACCT_POOL_V -- FTVACCT_POOL_V_create.sql Current Records VIEW from TABLE ^ CREATE OR REPLACE VIEW FTVACCT_POOL_V ( FTVACCT_COAS_CODE, FTVACCT_ACCT_CODE, FTVACCT_EFF_DATE, FTVACCT_ACTIVITY_DATE, FTVACCT_USER_ID, FTVACCT_NCHG_DATE, FTVACCT_TERM_DATE, FTVACCT_TITLE, FTVACCT_ATYP_CODE, FTVACCT_NORMAL_BAL, FTVACCT_STATUS_IND, FTVACCT_ACCT_CODE_PRED, FTVACCT_DATA_ENTRY_IND, FTVACCT_ACCT_CODE_FRINGE, FTVACCT_FRINGE_PCT, FTVACCT_ACCT_CODE_POOL, FTVACCT_ACCT_CODE_POOL_TITLE, FTVACCT_ACCT_CODE_ASSET, FTVACCT_ITYP_SEQ_CODE, FTVACCT_ACCT_CLASS_CODE, FTVACCT_ACCT_CODE_DEPR_EXP, FTVACCT_ACCT_CODE_DEPR_ACCUM ) AS SELECT * FROM (SELECT -- note that this is the FTVACCT Query with Pool Title Added -- see the UNION ALL The 1st Query is for Non-Pooled Accts -- The 2nd Query is for Pooled Accts FTVACCT_COAS_CODE FTVACCT_COAS_CODE, FTVACCT_ACCT_CODE FTVACCT_ACCT_CODE, TRUNC(FTVACCT_EFF_DATE) FTVACCT_EFF_DATE, TRUNC(FTVACCT_ACTIVITY_DATE) FTVACCT_ACTIVITY_DATE, FTVACCT_USER_ID FTVACCT_USER_ID, TRUNC(FTVACCT_NCHG_DATE) FTVACCT_NCHG_DATE, TRUNC(FTVACCT_TERM_DATE) FTVACCT_TERM_DATE, FTVACCT_TITLE FTVACCT_TITLE, FTVACCT_ATYP_CODE FTVACCT_ATYP_CODE, FTVACCT_NORMAL_BAL FTVACCT_NORMAL_BAL, FTVACCT_STATUS_IND FTVACCT_STATUS_IND, FTVACCT_ACCT_CODE_PRED FTVACCT_ACCT_CODE_PRED, FTVACCT_DATA_ENTRY_IND FTVACCT_DATA_ENTRY_IND, FTVACCT_ACCT_CODE_FRINGE FTVACCT_ACCT_CODE_FRINGE, FTVACCT_FRINGE_PCT FTVACCT_FRINGE_PCT, ' ' FTVACCT_ACCT_CODE_POOL, ' ' FTVACCT_ACCT_CODE_POOL_TITLE, FTVACCT_ACCT_CODE_ASSET FTVACCT_ACCT_CODE_ASSET, FTVACCT_ITYP_SEQ_CODE FTVACCT_ITYP_SEQ_CODE, FTVACCT_ACCT_CLASS_CODE FTVACCT_ACCT_CLASS_CODE, FTVACCT_ACCT_CODE_DEPR_EXP FTVACCT_ACCT_CODE_DEPR_EXP, FTVACCT_ACCT_CODE_DEPR_ACCUM FTVACCT_ACCT_CODE_DEPR_ACCUM FROM FTVACCT WHERE TRUNC(FTVACCT_EFF_DATE) <= SYSDATE AND (FTVACCT_NCHG_DATE > TRUNC(SYSDATE) OR FTVACCT_NCHG_DATE IS NULL) AND (FTVACCT_TERM_DATE > TRUNC(SYSDATE) OR FTVACCT_TERM_DATE IS NULL) AND FTVACCT_ACCT_CODE_POOL IS NULL UNION SELECT -- 2nd Query FTVACCT_COAS_CODE, FTVACCT_ACCT_CODE, TRUNC(FTVACCT_EFF_DATE) FTVACCT_EFF_DATE, TRUNC(FTVACCT_ACTIVITY_DATE) FTVACCT_ACTIVITY_DATE, FTVACCT_USER_ID, TRUNC(FTVACCT_NCHG_DATE) FTVACCT_NCHG_DATE, TRUNC(FTVACCT_TERM_DATE) FTVACCT_TERM_DATE, FTVACCT_TITLE, FTVACCT_ATYP_CODE, FTVACCT_NORMAL_BAL, FTVACCT_STATUS_IND, FTVACCT_ACCT_CODE_PRED, FTVACCT_DATA_ENTRY_IND, FTVACCT_ACCT_CODE_FRINGE, FTVACCT_FRINGE_PCT, FTVACCT_ACCT_CODE_POOL, POOL_TITLE FTVACCT_ACCT_CODE_POOL_TITLE, FTVACCT_ACCT_CODE_ASSET, FTVACCT_ITYP_SEQ_CODE, FTVACCT_ACCT_CLASS_CODE, FTVACCT_ACCT_CODE_DEPR_EXP, FTVACCT_ACCT_CODE_DEPR_ACCUM FROM (SELECT A.FTVACCT_ACCT_CODE XREF_ACCT_CODE_POOL, -- Actually ACCT A.FTVACCT_TITLE POOL_TITLE FROM FTVACCT A WHERE TRUNC(A.FTVACCT_EFF_DATE) <= SYSDATE AND (A.FTVACCT_NCHG_DATE > TRUNC(SYSDATE) OR A.FTVACCT_NCHG_DATE IS NULL) AND (A.FTVACCT_TERM_DATE > TRUNC(SYSDATE) OR A.FTVACCT_TERM_DATE IS NULL) ), FTVACCT WHERE TRUNC(FTVACCT_EFF_DATE) <= SYSDATE AND (FTVACCT_NCHG_DATE > TRUNC(SYSDATE) OR FTVACCT_NCHG_DATE IS NULL) AND (FTVACCT_TERM_DATE > TRUNC(SYSDATE) OR FTVACCT_TERM_DATE IS NULL) AND FTVACCT_ACCT_CODE_POOL IS NOT NULL AND FTVACCT_ACCT_CODE_POOL = XREF_ACCT_CODE_POOL ) WHERE TRUNC(FTVACCT_EFF_DATE) <= SYSDATE AND (FTVACCT_NCHG_DATE > TRUNC(SYSDATE) OR FTVACCT_NCHG_DATE IS NULL) AND (FTVACCT_TERM_DATE > TRUNC(SYSDATE) OR FTVACCT_TERM_DATE IS NULL) ; --DESC FTVACCT_V -- FTVACCT_v_create.sql Current Records VIEW from TABLE FTVACCT CREATE OR REPLACE VIEW FTVACCT_V ( FTVACCT_COAS_CODE, FTVACCT_ACCT_CODE, FTVACCT_EFF_DATE, FTVACCT_ACTIVITY_DATE, FTVACCT_USER_ID, FTVACCT_NCHG_DATE, FTVACCT_TERM_DATE, FTVACCT_TITLE, FTVACCT_ATYP_CODE, FTVACCT_NORMAL_BAL, FTVACCT_STATUS_IND, FTVACCT_ACCT_CODE_PRED, FTVACCT_DATA_ENTRY_IND, FTVACCT_ACCT_CODE_FRINGE, FTVACCT_FRINGE_PCT, FTVACCT_ACCT_CODE_POOL, FTVACCT_ACCT_CODE_ASSET, FTVACCT_ITYP_SEQ_CODE, FTVACCT_ACCT_CLASS_CODE, FTVACCT_ACCT_CODE_DEPR_EXP, FTVACCT_ACCT_CODE_DEPR_ACCUM ) AS SELECT -- Created from TABLE FTVACCT Account Validation Table FTVACCT_COAS_CODE, FTVACCT_ACCT_CODE, TRUNC(FTVACCT_EFF_DATE) FTVACCT_EFF_DATE, TRUNC(FTVACCT_ACTIVITY_DATE) FTVACCT_ACTIVITY_DATE, FTVACCT_USER_ID, TRUNC(FTVACCT_NCHG_DATE) FTVACCT_NCHG_DATE, TRUNC(FTVACCT_TERM_DATE) FTVACCT_TERM_DATE, FTVACCT_TITLE, FTVACCT_ATYP_CODE, FTVACCT_NORMAL_BAL, FTVACCT_STATUS_IND, FTVACCT_ACCT_CODE_PRED, FTVACCT_DATA_ENTRY_IND, FTVACCT_ACCT_CODE_FRINGE, FTVACCT_FRINGE_PCT, FTVACCT_ACCT_CODE_POOL, FTVACCT_ACCT_CODE_ASSET, FTVACCT_ITYP_SEQ_CODE, FTVACCT_ACCT_CLASS_CODE, FTVACCT_ACCT_CODE_DEPR_EXP, FTVACCT_ACCT_CODE_DEPR_ACCUM FROM FTVACCT WHERE TRUNC(FTVACCT_EFF_DATE) <= SYSDATE AND (FTVACCT_NCHG_DATE > TRUNC(SYSDATE) OR FTVACCT_NCHG_DATE IS NULL) AND (FTVACCT_TERM_DATE > TRUNC(SYSDATE) OR FTVACCT_TERM_DATE IS NULL) ; --DESC FTVACTV_V -- FTVACTV_v_create.sql Current Records VIEW from TABLE FTVACTV CREATE OR REPLACE VIEW FTVACTV_V ( FTVACTV_COAS_CODE, FTVACTV_ACTV_CODE, FTVACTV_EFF_DATE, FTVACTV_ACTIVITY_DATE, FTVACTV_USER_ID, FTVACTV_NCHG_DATE, FTVACTV_TERM_DATE, FTVACTV_TITLE, FTVACTV_STATUS_IND ) AS SELECT -- Created from TABLE FTVACTV Activity Validation Table FTVACTV_COAS_CODE, FTVACTV_ACTV_CODE, TRUNC(FTVACTV_EFF_DATE) FTVACTV_EFF_DATE, TRUNC(FTVACTV_ACTIVITY_DATE) FTVACTV_ACTIVITY_DATE, FTVACTV_USER_ID, TRUNC(FTVACTV_NCHG_DATE) FTVACTV_NCHG_DATE, TRUNC(FTVACTV_TERM_DATE) FTVACTV_TERM_DATE, FTVACTV_TITLE, FTVACTV_STATUS_IND FROM FTVACTV WHERE TRUNC(FTVACTV_EFF_DATE) <= SYSDATE AND (FTVACTV_NCHG_DATE > TRUNC(SYSDATE) OR FTVACTV_NCHG_DATE IS NULL) AND (FTVACTV_TERM_DATE > TRUNC(SYSDATE) OR FTVACTV_TERM_DATE IS NULL) ; --DESC FTVASTY_V -- FTVASTY_v_create.sql Current Records VIEW from TABLE FTVASTY CREATE OR REPLACE VIEW FTVASTY_V ( FTVASTY_ASTY_CODE, FTVASTY_EFF_DATE, FTVASTY_ACTIVITY_DATE, FTVASTY_USER_ID, FTVASTY_NCHG_DATE, FTVASTY_TERM_DATE, FTVASTY_TITLE, FTVASTY_SHORT_TITLE, FTVASTY_STATUS_IND, FTVASTY_DEPR_ALLOWED_IND, FTVASTY_STD_LIFE, FTVASTY_DEPR_CODE_DEF ) AS SELECT -- Created from TABLE FTVASTY Asset Type Table FTVASTY_ASTY_CODE, TRUNC(FTVASTY_EFF_DATE) FTVASTY_EFF_DATE, TRUNC(FTVASTY_ACTIVITY_DATE) FTVASTY_ACTIVITY_DATE, FTVASTY_USER_ID, TRUNC(FTVASTY_NCHG_DATE) FTVASTY_NCHG_DATE, TRUNC(FTVASTY_TERM_DATE) FTVASTY_TERM_DATE, FTVASTY_TITLE, FTVASTY_SHORT_TITLE, FTVASTY_STATUS_IND, FTVASTY_DEPR_ALLOWED_IND, FTVASTY_STD_LIFE, FTVASTY_DEPR_CODE_DEF FROM FTVASTY WHERE TRUNC(FTVASTY_EFF_DATE) <= SYSDATE AND (FTVASTY_NCHG_DATE > TRUNC(SYSDATE) OR FTVASTY_NCHG_DATE IS NULL) AND (FTVASTY_TERM_DATE > TRUNC(SYSDATE) OR FTVASTY_TERM_DATE IS NULL) ; --DESC FTVATYP_V -- FTVATYP_v_create.sql Current Records VIEW from TABLE FTVATYP CREATE OR REPLACE VIEW FTVATYP_V ( FTVATYP_COAS_CODE, FTVATYP_ATYP_CODE, FTVATYP_EFF_DATE, FTVATYP_ACTIVITY_DATE, FTVATYP_USER_ID, FTVATYP_NCHG_DATE, FTVATYP_TERM_DATE, FTVATYP_TITLE, FTVATYP_STATUS_IND, FTVATYP_INTERNAL_ATYP_CODE, FTVATYP_ATYP_CODE_PRED, FTVATYP_NORMAL_BAL ) AS SELECT -- Created from TABLE FTVATYP Account Type Validation Table FTVATYP_COAS_CODE, FTVATYP_ATYP_CODE, TRUNC(FTVATYP_EFF_DATE) FTVATYP_EFF_DATE, TRUNC(FTVATYP_ACTIVITY_DATE) FTVATYP_ACTIVITY_DATE, FTVATYP_USER_ID, TRUNC(FTVATYP_NCHG_DATE) FTVATYP_NCHG_DATE, TRUNC(FTVATYP_TERM_DATE) FTVATYP_TERM_DATE, FTVATYP_TITLE, FTVATYP_STATUS_IND, FTVATYP_INTERNAL_ATYP_CODE, FTVATYP_ATYP_CODE_PRED, FTVATYP_NORMAL_BAL FROM FTVATYP WHERE TRUNC(FTVATYP_EFF_DATE) <= SYSDATE AND (FTVATYP_NCHG_DATE > TRUNC(SYSDATE) OR FTVATYP_NCHG_DATE IS NULL) AND (FTVATYP_TERM_DATE > TRUNC(SYSDATE) OR FTVATYP_TERM_DATE IS NULL) ; --DESC FTVCOMM_V -- FTVCOMM_v_create.sql Current Records VIEW from TABLE FTVCOMM CREATE OR REPLACE VIEW FTVCOMM_V ( FTVCOMM_CODE, FTVCOMM_EFF_DATE, FTVCOMM_ACTIVITY_DATE, FTVCOMM_USER_ID, FTVCOMM_DESC, FTVCOMM_UOMS_CODE, FTVCOMM_TERM_DATE, FTVCOMM_COMM_CODE_PRED, FTVCOMM_AMST_IND, FTVCOMM_TGRP_CODE, FTVCOMM_TAX_OVERRIDE_IND, FTVCOMM_STOCK_IND, FTVCOMM_ONORDER_QTY, FTVCOMM_RESERVED_QTY, FTVCOMM_NOT_INVOICED_QTY, FTVCOMM_TOTAL_VALUE_AMT, FTVCOMM_EST_LIFE_YEARS ) AS SELECT -- Created from TABLE FTVCOMM Commodity Verification Table FTVCOMM_CODE, TRUNC(FTVCOMM_EFF_DATE) FTVCOMM_EFF_DATE, TRUNC(FTVCOMM_ACTIVITY_DATE) FTVCOMM_ACTIVITY_DATE, FTVCOMM_USER_ID, FTVCOMM_DESC, FTVCOMM_UOMS_CODE, TRUNC(FTVCOMM_TERM_DATE) FTVCOMM_TERM_DATE, FTVCOMM_COMM_CODE_PRED, FTVCOMM_AMST_IND, FTVCOMM_TGRP_CODE, FTVCOMM_TAX_OVERRIDE_IND, FTVCOMM_STOCK_IND, FTVCOMM_ONORDER_QTY, FTVCOMM_RESERVED_QTY, FTVCOMM_NOT_INVOICED_QTY, FTVCOMM_TOTAL_VALUE_AMT, FTVCOMM_EST_LIFE_YEARS FROM FTVCOMM WHERE TRUNC(FTVCOMM_EFF_DATE) <= SYSDATE AND (FTVCOMM_TERM_DATE > TRUNC(SYSDATE) OR FTVCOMM_TERM_DATE IS NULL) ; --DESC FTVEDIT_V -- FTVEDIT_v_create.sql Current Records VIEW from TABLE FTVEDIT CREATE OR REPLACE VIEW FTVEDIT_V ( 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 ) AS SELECT -- Created from TABLE FTVEDIT Rule Class Edit Table FTVEDIT_RUCL_CODE, FTVEDIT_EDIT_SEQ_NUM, TRUNC(FTVEDIT_EFF_DATE) 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 FROM FTVEDIT WHERE TRUNC(FTVEDIT_EFF_DATE) <= SYSDATE ; --DESC FTVFMGR_V -- FTVFMGR_v_create.sql Current Records VIEW from TABLE FTVFMGR CREATE OR REPLACE VIEW FTVFMGR_V ( FTVFMGR_FMGR_CODE_PIDM, FTVFMGR_EFF_DATE, FTVFMGR_ACTIVITY_DATE, FTVFMGR_USER_ID, FTVFMGR_NCHG_DATE, FTVFMGR_TERM_DATE, FTVFMGR_TITLE, FTVFMGR_STATUS_IND, FTVFMGR_COAS_CODE, FTVFMGR_LOCN_CODE, FTVFMGR_ORGN_CODE, FTVFMGR_ADDR_TYPE, FTVFMGR_ADDR_SEQNO ) AS SELECT -- Created from TABLE FTVFMGR Financial Manager Validation Table FTVFMGR_FMGR_CODE_PIDM, TRUNC(FTVFMGR_EFF_DATE) FTVFMGR_EFF_DATE, TRUNC(FTVFMGR_ACTIVITY_DATE) FTVFMGR_ACTIVITY_DATE, FTVFMGR_USER_ID, TRUNC(FTVFMGR_NCHG_DATE) FTVFMGR_NCHG_DATE, TRUNC(FTVFMGR_TERM_DATE) FTVFMGR_TERM_DATE, FTVFMGR_TITLE, FTVFMGR_STATUS_IND, FTVFMGR_COAS_CODE, FTVFMGR_LOCN_CODE, FTVFMGR_ORGN_CODE, FTVFMGR_ADDR_TYPE, FTVFMGR_ADDR_SEQNO FROM FTVFMGR WHERE TRUNC(FTVFMGR_EFF_DATE) <= SYSDATE AND (FTVFMGR_NCHG_DATE > TRUNC(SYSDATE) OR FTVFMGR_NCHG_DATE IS NULL) AND (FTVFMGR_TERM_DATE > TRUNC(SYSDATE) OR FTVFMGR_TERM_DATE IS NULL) ; --DESC FTVFTYP_V -- FTVFTYP_v_create.sql Current Records VIEW from TABLE FTVFTYP CREATE OR REPLACE VIEW FTVFTYP_V ( FTVFTYP_COAS_CODE, FTVFTYP_FTYP_CODE, FTVFTYP_EFF_DATE, FTVFTYP_ACTIVITY_DATE, FTVFTYP_USER_ID, FTVFTYP_NCHG_DATE, FTVFTYP_TITLE, FTVFTYP_STATUS_IND, FTVFTYP_INTERNAL_FTYP_CODE, FTVFTYP_TERM_DATE, FTVFTYP_FTYP_CODE_PRED, FTVFTYP_FUND_CODE_CAP, FTVFTYP_ACCT_CODE_CAP, FTVFTYP_BAVL_KEY_FUND, FTVFTYP_BAVL_KEY_ORG, FTVFTYP_BAVL_KEY_ACCT, FTVFTYP_BAVL_KEY_PROG, FTVFTYP_BAVL_PERIOD, FTVFTYP_BAVL_SEVERITY, FTVFTYP_DEF_OVERRIDE_IND, FTVFTYP_RUCL_CODE_ROLL_ENC, FTVFTYP_COMMIT_TYPE, FTVFTYP_BUDGET_ROLL, FTVFTYP_BUDGET_DISPOSITION, FTVFTYP_BUDGET_ROLL_PERCENT, FTVFTYP_RUCL_CODE_BUDG_CF, FTVFTYP_CF_TYPE, FTVFTYP_BUDG_CLASSIFICATION, FTVFTYP_BUDG_CF_PERCENT, FTVFTYP_ACCT_CODE_GAIN, FTVFTYP_ACCT_CODE_LOSS, FTVFTYP_DEPR_POST_CODE, FTVFTYP_ORGN_CODE_CAP_DEF, FTVFTYP_PROG_CODE_CAP_DEF, FTVFTYP_ACTV_CODE_CAP_DEF, FTVFTYP_LOCN_CODE_CAP_DEF, FTVFTYP_DEPR_EXP_EQ_DEF_IND ) AS SELECT -- Created from TABLE FTVFTYP FUND TYPE VALIDATION TABLE FTVFTYP_COAS_CODE, FTVFTYP_FTYP_CODE, TRUNC(FTVFTYP_EFF_DATE) FTVFTYP_EFF_DATE, TRUNC(FTVFTYP_ACTIVITY_DATE) FTVFTYP_ACTIVITY_DATE, FTVFTYP_USER_ID, TRUNC(FTVFTYP_NCHG_DATE) FTVFTYP_NCHG_DATE, FTVFTYP_TITLE, FTVFTYP_STATUS_IND, FTVFTYP_INTERNAL_FTYP_CODE, TRUNC(FTVFTYP_TERM_DATE) FTVFTYP_TERM_DATE, FTVFTYP_FTYP_CODE_PRED, FTVFTYP_FUND_CODE_CAP, FTVFTYP_ACCT_CODE_CAP, FTVFTYP_BAVL_KEY_FUND, FTVFTYP_BAVL_KEY_ORG, FTVFTYP_BAVL_KEY_ACCT, FTVFTYP_BAVL_KEY_PROG, FTVFTYP_BAVL_PERIOD, FTVFTYP_BAVL_SEVERITY, FTVFTYP_DEF_OVERRIDE_IND, FTVFTYP_RUCL_CODE_ROLL_ENC, FTVFTYP_COMMIT_TYPE, FTVFTYP_BUDGET_ROLL, FTVFTYP_BUDGET_DISPOSITION, FTVFTYP_BUDGET_ROLL_PERCENT, FTVFTYP_RUCL_CODE_BUDG_CF, FTVFTYP_CF_TYPE, FTVFTYP_BUDG_CLASSIFICATION, FTVFTYP_BUDG_CF_PERCENT, FTVFTYP_ACCT_CODE_GAIN, FTVFTYP_ACCT_CODE_LOSS, FTVFTYP_DEPR_POST_CODE, FTVFTYP_ORGN_CODE_CAP_DEF, FTVFTYP_PROG_CODE_CAP_DEF, FTVFTYP_ACTV_CODE_CAP_DEF, FTVFTYP_LOCN_CODE_CAP_DEF, FTVFTYP_DEPR_EXP_EQ_DEF_IND FROM FTVFTYP WHERE TRUNC(FTVFTYP_EFF_DATE) <= SYSDATE AND (FTVFTYP_NCHG_DATE > TRUNC(SYSDATE) OR FTVFTYP_NCHG_DATE IS NULL) AND (FTVFTYP_TERM_DATE > TRUNC(SYSDATE) OR FTVFTYP_TERM_DATE IS NULL) ; --DESC ftvfund_V -- ftvfund_v_create.sql Current Records VIEW from TABLE FTVFUND CREATE OR REPLACE VIEW ftvfund_V ( FTVFUND_COAS_CODE, FTVFUND_FUND_CODE, FTVFUND_EFF_DATE, FTVFUND_ACTIVITY_DATE, FTVFUND_USER_ID, FTVFUND_NCHG_DATE, FTVFUND_FTYP_CODE, FTVFUND_TITLE, FTVFUND_STATUS_IND, FTVFUND_DATA_ENTRY_IND, FTVFUND_POOL_IND, FTVFUND_TERM_DATE, FTVFUND_FUND_CODE_PRED, FTVFUND_ORGN_CODE_DEF, FTVFUND_PROG_CODE_DEF, FTVFUND_ACTV_CODE_DEF, FTVFUND_LOCN_CODE_DEF, FTVFUND_FUND_CODE_BAVL, FTVFUND_BAVL_PERIOD, FTVFUND_BAVL_SEVERITY, FTVFUND_HIER_TABLE_IND, FTVFUND_BAVL_KEY_FUND, FTVFUND_BAVL_KEY_ORGN, FTVFUND_BAVL_KEY_ACCT, FTVFUND_BAVL_KEY_PROG, FTVFUND_BANK_CODE, FTVFUND_FMGR_CODE_PIDM, FTVFUND_ACCT_CODE_REV, FTVFUND_ACCT_CODE_ACCR, FTVFUND_ACCT_CODE_PLANT, FTVFUND_FUND_CODE_PLANT, FTVFUND_COST_CODE_GRNT, FTVFUND_INDC_CODE, FTVFUND_CONP_PROJ_NUM, FTVFUND_PROJ_DESC, FTVFUND_PROJ_START_DATE_CONP, FTVFUND_PROJ_END_DATE_CONP, FTVFUND_PROJ_COMPLETE_DATE, FTVFUND_FMGR_CODE_PIDM_CONP, FTVFUND_ACCT_CODE_EQUITY, FTVFUND_FUND_CODE_PLANT_CONP, FTVFUND_CLOSE_PROJ_IND, FTVFUND_FUND_CODE_SOURCE, FTVFUND_ACCT_CODE_CIP, FTVFUND_ACCT_CODE_ASSET, FTVFUND_MAX_CONSTR_AMT, FTVFUND_COST_CODE_CONP, FTVFUND_CUM_COST_AMT, FTVFUND_GRNT_CODE, FTVFUND_POOLING_METHOD, FTVFUND_ARB_BEGIN_UNIT_VALUE, FTVFUND_ACCT_CODE_FUND_BAL, FTVFUND_ACCT_CODE_POOL, FTVFUND_ACCT_CODE_REAL_GAIN, FTVFUND_ORGN_CODE_REAL_GAIN, FTVFUND_PROG_CODE_REAL_GAIN, FTVFUND_ACCT_CODE_REAL_LOSS, FTVFUND_ORGN_CODE_REAL_LOSS, FTVFUND_PROG_CODE_REAL_LOSS, FTVFUND_DISTRIBUTION_FREQ_INC, FTVFUND_DISTRIBUTION_FREQ_RGL, FTVFUND_DISTRIBUTION_ST_DATE, FTVFUND_ACCT_CODE_INCOME, FTVFUND_ORGN_CODE_INCOME, FTVFUND_PROG_CODE_INCOME, FTVFUND_SPENDABLE_RETURN_IND, FTVFUND_FUND_CODE_SPEND_INCM, FTVFUND_ORGN_CODE_SPEND_INCM, FTVFUND_ACCT_CODE_SPEND_INCM, FTVFUND_PROG_CODE_SPEND_INCM, FTVFUND_ACCT_CODE_SPEND_EXCS, FTVFUND_COAS_CODE_MATCH, FTVFUND_FUND_CODE_MATCH, FTVFUND_FUND_CODE_ENDOW_FUNC, FTVFUND_ACCT_CODE_SPD_TR_FRM, FTVFUND_ORGN_CODE_SPD_TR_FRM, FTVFUND_PROG_CODE_SPD_TR_FRM, FTVFUND_ACCT_CODE_SPD_TR_TO, FTVFUND_ACCT_CODE_RECLASS, FTVFUND_ACCT_CODE_CONTRA_INC, FTVFUND_ORGN_CODE_CONTRA_INC, FTVFUND_PROG_CODE_CONTRA_INC, FTVFUND_ACCT_CODE_CONTRA_RG, FTVFUND_ORGN_CODE_CONTRA_RG, FTVFUND_PROG_CODE_CONTRA_RG, FTVFUND_ACCT_CODE_CONTRA_RL, FTVFUND_ORGN_CODE_CONTRA_RL, FTVFUND_PROG_CODE_CONTRA_RL, FTVFUND_CURRENT_YEAR_INCOME, FTVFUND_ACCT_CODE_TRF_INCOME, FTVFUND_ORGN_CODE_TRF_INCOME, FTVFUND_PROG_CODE_TRF_INCOME, FTVFUND_PY_RETAINED_INCOME, FTVFUND_ACCT_CODE_TRF_RI_PY, FTVFUND_ORGN_CODE_TRF_RI_PY, FTVFUND_PROG_CODE_TRF_RI_PY, FTVFUND_REAL_GL, FTVFUND_ACCT_CODE_TRF_RGL, FTVFUND_ORGN_CODE_TRF_RGL, FTVFUND_PROG_CODE_TRF_RGL, FTVFUND_REAL_GL_PY, FTVFUND_ACCT_CODE_TRF_RGL_PY, FTVFUND_ORGN_CODE_TRF_RGL_PY, FTVFUND_PROG_CODE_TRF_RGL_PY, FTVFUND_DISTRIBUTION_IND_INC, FTVFUND_DISTRIBUTION_IND_RGL, FTVFUND_SPENDABLE_RATE, FTVFUND_SPEND_FORMULA_FREQ_IND, FTVFUND_FBAL_IND, FTVFUND_REST_IND, FTVFUND_PROP_CODE, FTVFUND_DEPR_POST_CODE, FTVFUND_FUND_CODE_ALLOC_TGT, FTVFUND_ORGN_CODE_ALLOC_CONTRA, FTVFUND_ACCT_CODE_ALLOC_CONTRA, FTVFUND_PROG_CODE_ALLOC_CONTRA, FTVFUND_ALLOC_PCT, FTVFUND_ACCT_CODE_GAIN, FTVFUND_ACCT_CODE_LOSS, FTVFUND_ORGN_CODE_CAP_DEF, FTVFUND_PROG_CODE_CAP_DEF, FTVFUND_ACTV_CODE_CAP_DEF, FTVFUND_LOCN_CODE_CAP_DEF, FTVFUND_DEPR_EXP_EQ_DEF_IND, FTVFUND_SRC_CAP_SAME_IND, FTVFUND_EXPEND_END_DATE ) AS SELECT FTVFUND_COAS_CODE, FTVFUND_FUND_CODE, TRUNC(FTVFUND_EFF_DATE) FTVFUND_EFF_DATE, TRUNC(FTVFUND_ACTIVITY_DATE) FTVFUND_ACTIVITY_DATE, FTVFUND_USER_ID, TRUNC(FTVFUND_NCHG_DATE) FTVFUND_NCHG_DATE, FTVFUND_FTYP_CODE, FTVFUND_TITLE, FTVFUND_STATUS_IND, FTVFUND_DATA_ENTRY_IND, FTVFUND_POOL_IND, TRUNC(FTVFUND_TERM_DATE) FTVFUND_TERM_DATE, FTVFUND_FUND_CODE_PRED, FTVFUND_ORGN_CODE_DEF, FTVFUND_PROG_CODE_DEF, FTVFUND_ACTV_CODE_DEF, FTVFUND_LOCN_CODE_DEF, FTVFUND_FUND_CODE_BAVL, FTVFUND_BAVL_PERIOD, FTVFUND_BAVL_SEVERITY, FTVFUND_HIER_TABLE_IND, FTVFUND_BAVL_KEY_FUND, FTVFUND_BAVL_KEY_ORGN, FTVFUND_BAVL_KEY_ACCT, FTVFUND_BAVL_KEY_PROG, FTVFUND_BANK_CODE, FTVFUND_FMGR_CODE_PIDM, FTVFUND_ACCT_CODE_REV, FTVFUND_ACCT_CODE_ACCR, FTVFUND_ACCT_CODE_PLANT, FTVFUND_FUND_CODE_PLANT, FTVFUND_COST_CODE_GRNT, FTVFUND_INDC_CODE, FTVFUND_CONP_PROJ_NUM, FTVFUND_PROJ_DESC, TRUNC(FTVFUND_PROJ_START_DATE_CONP) FTVFUND_PROJ_START_DATE_CONP, TRUNC(FTVFUND_PROJ_END_DATE_CONP) FTVFUND_PROJ_END_DATE_CONP, TRUNC(FTVFUND_PROJ_COMPLETE_DATE) FTVFUND_PROJ_COMPLETE_DATE, FTVFUND_FMGR_CODE_PIDM_CONP, FTVFUND_ACCT_CODE_EQUITY, FTVFUND_FUND_CODE_PLANT_CONP, FTVFUND_CLOSE_PROJ_IND, FTVFUND_FUND_CODE_SOURCE, FTVFUND_ACCT_CODE_CIP, FTVFUND_ACCT_CODE_ASSET, FTVFUND_MAX_CONSTR_AMT, FTVFUND_COST_CODE_CONP, FTVFUND_CUM_COST_AMT, FTVFUND_GRNT_CODE, FTVFUND_POOLING_METHOD, FTVFUND_ARB_BEGIN_UNIT_VALUE, FTVFUND_ACCT_CODE_FUND_BAL, FTVFUND_ACCT_CODE_POOL, FTVFUND_ACCT_CODE_REAL_GAIN, FTVFUND_ORGN_CODE_REAL_GAIN, FTVFUND_PROG_CODE_REAL_GAIN, FTVFUND_ACCT_CODE_REAL_LOSS, FTVFUND_ORGN_CODE_REAL_LOSS, FTVFUND_PROG_CODE_REAL_LOSS, FTVFUND_DISTRIBUTION_FREQ_INC, FTVFUND_DISTRIBUTION_FREQ_RGL, TRUNC(FTVFUND_DISTRIBUTION_ST_DATE) FTVFUND_DISTRIBUTION_ST_DATE, FTVFUND_ACCT_CODE_INCOME, FTVFUND_ORGN_CODE_INCOME, FTVFUND_PROG_CODE_INCOME, FTVFUND_SPENDABLE_RETURN_IND, FTVFUND_FUND_CODE_SPEND_INCM, FTVFUND_ORGN_CODE_SPEND_INCM, FTVFUND_ACCT_CODE_SPEND_INCM, FTVFUND_PROG_CODE_SPEND_INCM, FTVFUND_ACCT_CODE_SPEND_EXCS, FTVFUND_COAS_CODE_MATCH, FTVFUND_FUND_CODE_MATCH, FTVFUND_FUND_CODE_ENDOW_FUNC, FTVFUND_ACCT_CODE_SPD_TR_FRM, FTVFUND_ORGN_CODE_SPD_TR_FRM, FTVFUND_PROG_CODE_SPD_TR_FRM, FTVFUND_ACCT_CODE_SPD_TR_TO, FTVFUND_ACCT_CODE_RECLASS, FTVFUND_ACCT_CODE_CONTRA_INC, FTVFUND_ORGN_CODE_CONTRA_INC, FTVFUND_PROG_CODE_CONTRA_INC, FTVFUND_ACCT_CODE_CONTRA_RG, FTVFUND_ORGN_CODE_CONTRA_RG, FTVFUND_PROG_CODE_CONTRA_RG, FTVFUND_ACCT_CODE_CONTRA_RL, FTVFUND_ORGN_CODE_CONTRA_RL, FTVFUND_PROG_CODE_CONTRA_RL, FTVFUND_CURRENT_YEAR_INCOME, FTVFUND_ACCT_CODE_TRF_INCOME, FTVFUND_ORGN_CODE_TRF_INCOME, FTVFUND_PROG_CODE_TRF_INCOME, FTVFUND_PY_RETAINED_INCOME, FTVFUND_ACCT_CODE_TRF_RI_PY, FTVFUND_ORGN_CODE_TRF_RI_PY, FTVFUND_PROG_CODE_TRF_RI_PY, FTVFUND_REAL_GL, FTVFUND_ACCT_CODE_TRF_RGL, FTVFUND_ORGN_CODE_TRF_RGL, FTVFUND_PROG_CODE_TRF_RGL, FTVFUND_REAL_GL_PY, FTVFUND_ACCT_CODE_TRF_RGL_PY, FTVFUND_ORGN_CODE_TRF_RGL_PY, FTVFUND_PROG_CODE_TRF_RGL_PY, FTVFUND_DISTRIBUTION_IND_INC, FTVFUND_DISTRIBUTION_IND_RGL, FTVFUND_SPENDABLE_RATE, FTVFUND_SPEND_FORMULA_FREQ_IND, FTVFUND_FBAL_IND, FTVFUND_REST_IND, FTVFUND_PROP_CODE, FTVFUND_DEPR_POST_CODE, FTVFUND_FUND_CODE_ALLOC_TGT, FTVFUND_ORGN_CODE_ALLOC_CONTRA, FTVFUND_ACCT_CODE_ALLOC_CONTRA, FTVFUND_PROG_CODE_ALLOC_CONTRA, FTVFUND_ALLOC_PCT, FTVFUND_ACCT_CODE_GAIN, FTVFUND_ACCT_CODE_LOSS, FTVFUND_ORGN_CODE_CAP_DEF, FTVFUND_PROG_CODE_CAP_DEF, FTVFUND_ACTV_CODE_CAP_DEF, FTVFUND_LOCN_CODE_CAP_DEF, FTVFUND_DEPR_EXP_EQ_DEF_IND, FTVFUND_SRC_CAP_SAME_IND, TRUNC(FTVFUND_EXPEND_END_DATE) FTVFUND_EXPEND_END_DATE FROM FTVFUND WHERE FTVFUND_EFF_DATE <= SYSDATE AND (FTVFUND_NCHG_DATE > SYSDATE OR FTVFUND_NCHG_DATE IS NULL) AND (FTVFUND_TERM_DATE > SYSDATE OR FTVFUND_TERM_DATE IS NULL) ; --DESC FTVINVM_V -- FTVINVM_v_create.sql Current Records VIEW from TABLE FTVINVM CREATE OR REPLACE VIEW FTVINVM_V ( FTVINVM_COMM_CODE, FTVINVM_EFF_DATE, FTVINVM_ACTIVITY_DATE, FTVINVM_USER_ID, FTVINVM_NCHG_DATE, FTVINVM_UOMS_CODE_ISSD, FTVINVM_TERM_DATE, FTVINVM_COAS_CODE, FTVINVM_FUND_CODE_INVTRY, FTVINVM_ACCT_CODE_INVTRY, FTVINVM_SAFETY_QTY, FTVINVM_REORDER_QTY, FTVINVM_MIN_REORDER_QTY, FTVINVM_ABC_CLASS, FTVINVM_EXRT_CODE, FTVINVM_SHIP_CODE, FTVINVM_SUB_LOCN ) AS SELECT -- Created from TABLE FTVINVM STORES COMMODITY MASTER: Defines inventory parameters for stores FTVINVM_COMM_CODE, TRUNC(FTVINVM_EFF_DATE) FTVINVM_EFF_DATE, TRUNC(FTVINVM_ACTIVITY_DATE) FTVINVM_ACTIVITY_DATE, FTVINVM_USER_ID, TRUNC(FTVINVM_NCHG_DATE) FTVINVM_NCHG_DATE, FTVINVM_UOMS_CODE_ISSD, TRUNC(FTVINVM_TERM_DATE) FTVINVM_TERM_DATE, FTVINVM_COAS_CODE, FTVINVM_FUND_CODE_INVTRY, FTVINVM_ACCT_CODE_INVTRY, FTVINVM_SAFETY_QTY, FTVINVM_REORDER_QTY, FTVINVM_MIN_REORDER_QTY, FTVINVM_ABC_CLASS, FTVINVM_EXRT_CODE, FTVINVM_SHIP_CODE, FTVINVM_SUB_LOCN FROM FTVINVM WHERE TRUNC(FTVINVM_EFF_DATE) <= SYSDATE AND (FTVINVM_NCHG_DATE > TRUNC(SYSDATE) OR FTVINVM_NCHG_DATE IS NULL) AND (FTVINVM_TERM_DATE > TRUNC(SYSDATE) OR FTVINVM_TERM_DATE IS NULL) ; --DESC FTVLOCN_V -- FTVLOCN_v_create.sql Current Records VIEW from TABLE FTVLOCN CREATE OR REPLACE VIEW FTVLOCN_V ( FTVLOCN_COAS_CODE, FTVLOCN_LOCN_CODE, FTVLOCN_EFF_DATE, FTVLOCN_ACTIVITY_DATE, FTVLOCN_USER_ID, FTVLOCN_NCHG_DATE, FTVLOCN_TERM_DATE, FTVLOCN_TITLE, FTVLOCN_STATUS_IND, FTVLOCN_LOCN_CODE_PRED, FTVLOCN_ADDR_LINE1, FTVLOCN_ADDR_LINE2, FTVLOCN_ADDR_LINE3, FTVLOCN_CITY, FTVLOCN_STATE, FTVLOCN_ZIP, FTVLOCN_CNTY_CODE, FTVLOCN_NATN_CODE, FTVLOCN_PHONE_AREA, FTVLOCN_PHONE_NUMBER, FTVLOCN_PHONE_EXT, FTVLOCN_SQUARE_FOOTAGE, FTVLOCN_SQUARE_FOOTAGE_RATE, FTVLOCN_DELIVERY_POINT, FTVLOCN_CORRECTION_DIGIT, FTVLOCN_CARRIER_ROUTE ) AS SELECT -- Created from TABLE FTVLOCN Location Validation Table FTVLOCN_COAS_CODE, FTVLOCN_LOCN_CODE, TRUNC(FTVLOCN_EFF_DATE) FTVLOCN_EFF_DATE, TRUNC(FTVLOCN_ACTIVITY_DATE) FTVLOCN_ACTIVITY_DATE, FTVLOCN_USER_ID, TRUNC(FTVLOCN_NCHG_DATE) FTVLOCN_NCHG_DATE, TRUNC(FTVLOCN_TERM_DATE) FTVLOCN_TERM_DATE, FTVLOCN_TITLE, FTVLOCN_STATUS_IND, FTVLOCN_LOCN_CODE_PRED, FTVLOCN_ADDR_LINE1, FTVLOCN_ADDR_LINE2, FTVLOCN_ADDR_LINE3, FTVLOCN_CITY, FTVLOCN_STATE, FTVLOCN_ZIP, FTVLOCN_CNTY_CODE, FTVLOCN_NATN_CODE, FTVLOCN_PHONE_AREA, FTVLOCN_PHONE_NUMBER, FTVLOCN_PHONE_EXT, FTVLOCN_SQUARE_FOOTAGE, FTVLOCN_SQUARE_FOOTAGE_RATE, FTVLOCN_DELIVERY_POINT, FTVLOCN_CORRECTION_DIGIT, FTVLOCN_CARRIER_ROUTE FROM FTVLOCN WHERE TRUNC(FTVLOCN_EFF_DATE) <= SYSDATE AND (FTVLOCN_NCHG_DATE > TRUNC(SYSDATE) OR FTVLOCN_NCHG_DATE IS NULL) AND (FTVLOCN_TERM_DATE > TRUNC(SYSDATE) OR FTVLOCN_TERM_DATE IS NULL) ; --DESC FTVORGN_PRED_V -- FTVORGN_PRED_v_create.sql Current Records VIEW from TABLE FTVORGN_PRED CREATE OR REPLACE VIEW FTVORGN_PRED_V ( FTVORGN_ORGN_CODE_PRED, FTVORGN_ORGN_CODE, FTVORGN_TITLE, FTVORGN_EFF_DATE, FTVORGN_ACTIVITY_DATE, FTVORGN_USER_ID, FTVORGN_NCHG_DATE, FTVORGN_TERM_DATE, FTVORGN_STATUS_IND, FTVORGN_FUND_CODE_DEF, FTVORGN_PROG_CODE_DEF, FTVORGN_ACTV_CODE_DEF, FTVORGN_LOCN_CODE_DEF, FTVORGN_DATA_ENTRY_IND, FTVORGN_FMGR_CODE_PIDM, FTVORGN_ENCB_POLICY_IND, FTVORGN_ORGN_CODE_NSF, FTVORGN_HIERARCHY_TABLE_IND, FTVORGN_ALT_POOL_IND, Orgn_Reporting, FTVORGN_COAS_CODE ) AS SELECT FTVORGN_ORGN_CODE_PRED, FTVORGN_ORGN_CODE, FTVORGN_TITLE, TRUNC(FTVORGN_EFF_DATE) FTVORGN_EFF_DATE, TRUNC(FTVORGN_ACTIVITY_DATE) FTVORGN_ACTIVITY_DATE, FTVORGN_USER_ID, TRUNC(FTVORGN_NCHG_DATE) FTVORGN_NCHG_DATE, TRUNC(FTVORGN_TERM_DATE) FTVORGN_TERM_DATE, FTVORGN_STATUS_IND, FTVORGN_FUND_CODE_DEF, FTVORGN_PROG_CODE_DEF, FTVORGN_ACTV_CODE_DEF, FTVORGN_LOCN_CODE_DEF, FTVORGN_DATA_ENTRY_IND, FTVORGN_FMGR_CODE_PIDM, FTVORGN_ENCB_POLICY_IND, FTVORGN_ORGN_CODE_NSF, FTVORGN_HIERARCHY_TABLE_IND, FTVORGN_ALT_POOL_IND, DECODE(FTVORGN_Orgn_CODE_PRED,'EXEC','00000','EXEC1','00001','EXEC2','00003',FTVORGN_Orgn_CODE_PRED)||DECODE(FTVORGN_Orgn_CODE,'EXEC','00000','EXEC1','00001','EXEC2','00003',FTVORGN_Orgn_CODE) Orgn_Reporting, FTVORGN_COAS_CODE FROM FTVORGN WHERE TRUNC(FTVORGN_EFF_DATE) <= SYSDATE AND (FTVORGN_NCHG_DATE > TRUNC(SYSDATE) OR FTVORGN_NCHG_DATE IS NULL) AND (FTVORGN_TERM_DATE > TRUNC(SYSDATE) OR FTVORGN_TERM_DATE IS NULL) AND FTVORGN_STATUS_IND = 'A' ; --DESC FTVORGN_V -- FTVORGN_v_create.sql Current Records VIEW from TABLE FTVORGN CREATE OR REPLACE VIEW FTVORGN_V ( Orgn_Reporting, FTVORGN_COAS_CODE, FTVORGN_ORGN_CODE, FTVORGN_EFF_DATE, FTVORGN_ACTIVITY_DATE, FTVORGN_USER_ID, FTVORGN_NCHG_DATE, FTVORGN_TERM_DATE, FTVORGN_TITLE, FTVORGN_STATUS_IND, FTVORGN_ORGN_CODE_PRED, FTVORGN_FUND_CODE_DEF, FTVORGN_PROG_CODE_DEF, FTVORGN_ACTV_CODE_DEF, FTVORGN_LOCN_CODE_DEF, FTVORGN_DATA_ENTRY_IND, FTVORGN_FMGR_CODE_PIDM, FTVORGN_ENCB_POLICY_IND, FTVORGN_ORGN_CODE_NSF, FTVORGN_HIERARCHY_TABLE_IND, FTVORGN_ALT_POOL_IND ) AS SELECT DECODE(FTVORGN_Orgn_CODE_PRED,'EXEC','00000','EXEC1','00001','EXEC2','00003',FTVORGN_Orgn_CODE_PRED)||DECODE(FTVORGN_Orgn_CODE,'EXEC','00000','EXEC1','00001','EXEC2','00003',FTVORGN_Orgn_CODE) Orgn_Reporting, FTVORGN_COAS_CODE, FTVORGN_ORGN_CODE, TRUNC(FTVORGN_EFF_DATE) FTVORGN_EFF_DATE, TRUNC(FTVORGN_ACTIVITY_DATE) FTVORGN_ACTIVITY_DATE, FTVORGN_USER_ID, TRUNC(FTVORGN_NCHG_DATE) FTVORGN_NCHG_DATE, TRUNC(FTVORGN_TERM_DATE) FTVORGN_TERM_DATE, FTVORGN_TITLE, FTVORGN_STATUS_IND, FTVORGN_ORGN_CODE_PRED, FTVORGN_FUND_CODE_DEF, FTVORGN_PROG_CODE_DEF, FTVORGN_ACTV_CODE_DEF, FTVORGN_LOCN_CODE_DEF, FTVORGN_DATA_ENTRY_IND, FTVORGN_FMGR_CODE_PIDM, FTVORGN_ENCB_POLICY_IND, FTVORGN_ORGN_CODE_NSF, FTVORGN_HIERARCHY_TABLE_IND, FTVORGN_ALT_POOL_IND FROM FTVORGN WHERE TRUNC(FTVORGN_EFF_DATE) <= SYSDATE AND (FTVORGN_NCHG_DATE > SYSDATE OR FTVORGN_NCHG_DATE IS NULL) AND (FTVORGN_TERM_DATE > SYSDATE OR FTVORGN_TERM_DATE IS NULL) AND FTVORGN_STATUS_IND = 'A' ; --DESC FTVPROG_V -- FTVPROG_v_create.sql Current Records VIEW from TABLE FTVPROG CREATE OR REPLACE VIEW FTVPROG_V ( FTVPROG_COAS_CODE, FTVPROG_PROG_CODE, FTVPROG_EFF_DATE, FTVPROG_ACTIVITY_DATE, FTVPROG_USER_ID, FTVPROG_NCHG_DATE, FTVPROG_TERM_DATE, FTVPROG_TITLE, FTVPROG_STATUS_IND, FTVPROG_PROG_CODE_PRED, FTVPROG_DATA_ENTRY_IND ) AS SELECT -- Created from TABLE FTVPROG Program Validation Table FTVPROG_COAS_CODE, FTVPROG_PROG_CODE, TRUNC(FTVPROG_EFF_DATE) FTVPROG_EFF_DATE, TRUNC(FTVPROG_ACTIVITY_DATE) FTVPROG_ACTIVITY_DATE, FTVPROG_USER_ID, TRUNC(FTVPROG_NCHG_DATE) FTVPROG_NCHG_DATE, TRUNC(FTVPROG_TERM_DATE) FTVPROG_TERM_DATE, FTVPROG_TITLE, FTVPROG_STATUS_IND, FTVPROG_PROG_CODE_PRED, FTVPROG_DATA_ENTRY_IND FROM FTVPROG WHERE TRUNC(FTVPROG_EFF_DATE) <= SYSDATE AND (FTVPROG_NCHG_DATE > TRUNC(SYSDATE) OR FTVPROG_NCHG_DATE IS NULL) AND (FTVPROG_TERM_DATE > TRUNC(SYSDATE) OR FTVPROG_TERM_DATE IS NULL) ; --DESC FTVPROJ_V -- FTVPROJ_v_create.sql Current Records VIEW from TABLE FTVPROJ CREATE OR REPLACE VIEW FTVPROJ_V ( FTVPROJ_CODE, FTVPROJ_COAS_CODE, FTVPROJ_ORGN_CODE_PROJ, FTVPROJ_EFF_DATE, FTVPROJ_TERM_DATE, FTVPROJ_ACTIVITY_DATE, FTVPROJ_USER_ID, FTVPROJ_SHORT_DESC, FTVPROJ_LONG_DESC, FTVPROJ_MEMO_IND, FTVPROJ_CAP_IND, FTVPROJ_TEXT_IND, FTVPROJ_RUCL_CODE, FTVPROJ_ACCI_CODE, FTVPROJ_FUND_CODE, FTVPROJ_ORGN_CODE, FTVPROJ_ACCT_CODE, FTVPROJ_PROG_CODE, FTVPROJ_ACTV_CODE, FTVPROJ_LOCN_CODE ) AS SELECT -- Created from TABLE FTVPROJ Project Validation Table FTVPROJ_CODE, FTVPROJ_COAS_CODE, FTVPROJ_ORGN_CODE_PROJ, TRUNC(FTVPROJ_EFF_DATE) FTVPROJ_EFF_DATE, TRUNC(FTVPROJ_TERM_DATE) FTVPROJ_TERM_DATE, TRUNC(FTVPROJ_ACTIVITY_DATE) FTVPROJ_ACTIVITY_DATE, FTVPROJ_USER_ID, FTVPROJ_SHORT_DESC, FTVPROJ_LONG_DESC, FTVPROJ_MEMO_IND, FTVPROJ_CAP_IND, FTVPROJ_TEXT_IND, FTVPROJ_RUCL_CODE, FTVPROJ_ACCI_CODE, FTVPROJ_FUND_CODE, FTVPROJ_ORGN_CODE, FTVPROJ_ACCT_CODE, FTVPROJ_PROG_CODE, FTVPROJ_ACTV_CODE, FTVPROJ_LOCN_CODE FROM FTVPROJ WHERE TRUNC(FTVPROJ_EFF_DATE) <= SYSDATE AND (FTVPROJ_TERM_DATE > TRUNC(SYSDATE) OR FTVPROJ_TERM_DATE IS NULL) ; --DESC FTVRUCL_V -- FTVRUCL_v_create.sql Current Records VIEW from TABLE FTVRUCL CREATE OR REPLACE VIEW FTVRUCL_V ( 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 ) AS SELECT -- Created from TABLE FTVRUCL Rule Class Validation Table FTVRUCL_RUCL_CODE, TRUNC(FTVRUCL_EFF_DATE) FTVRUCL_EFF_DATE, TRUNC(FTVRUCL_ACTIVITY_DATE) FTVRUCL_ACTIVITY_DATE, FTVRUCL_USER_ID, TRUNC(FTVRUCL_NCHG_DATE) FTVRUCL_NCHG_DATE, TRUNC(FTVRUCL_TERM_DATE) FTVRUCL_TERM_DATE, FTVRUCL_CLASS_TYPE, FTVRUCL_CLASS_TITLE, FTVRUCL_STATUS_IND, FTVRUCL_POST_BAVL, FTVRUCL_BALANCE_METHOD FROM FTVRUCL WHERE TRUNC(FTVRUCL_EFF_DATE) <= SYSDATE AND (FTVRUCL_NCHG_DATE > TRUNC(SYSDATE) OR FTVRUCL_NCHG_DATE IS NULL) AND (FTVRUCL_TERM_DATE > TRUNC(SYSDATE) OR FTVRUCL_TERM_DATE IS NULL) ; --DESC FTVSDAT_V -- FTVSDAT_v_create.sql Current Records VIEW from TABLE FTVSDAT CREATE OR REPLACE VIEW FTVSDAT_V ( FTVSDAT_SDAT_CODE_ENTITY, FTVSDAT_SDAT_CODE_ATTR, FTVSDAT_SDAT_CODE_OPT_1, FTVSDAT_SDAT_CODE_OPT_2, FTVSDAT_CODE_LEVEL, FTVSDAT_COAS_CODE, FTVSDAT_EFF_DATE, FTVSDAT_TERM_DATE, FTVSDAT_NCHG_DATE, FTVSDAT_STATUS_IND, FTVSDAT_ACTIVITY_DATE, FTVSDAT_USER_ID, FTVSDAT_TITLE, FTVSDAT_SHORT_TITLE, FTVSDAT_DATA ) AS SELECT -- Created from TABLE FTVSDAT System Data Validation Table FTVSDAT_SDAT_CODE_ENTITY, FTVSDAT_SDAT_CODE_ATTR, FTVSDAT_SDAT_CODE_OPT_1, FTVSDAT_SDAT_CODE_OPT_2, FTVSDAT_CODE_LEVEL, FTVSDAT_COAS_CODE, TRUNC(FTVSDAT_EFF_DATE) FTVSDAT_EFF_DATE, TRUNC(FTVSDAT_TERM_DATE) FTVSDAT_TERM_DATE, TRUNC(FTVSDAT_NCHG_DATE) FTVSDAT_NCHG_DATE, FTVSDAT_STATUS_IND, TRUNC(FTVSDAT_ACTIVITY_DATE) FTVSDAT_ACTIVITY_DATE, FTVSDAT_USER_ID, FTVSDAT_TITLE, FTVSDAT_SHORT_TITLE, FTVSDAT_DATA FROM FTVSDAT WHERE TRUNC(FTVSDAT_EFF_DATE) <= SYSDATE AND (FTVSDAT_NCHG_DATE > TRUNC(SYSDATE) OR FTVSDAT_NCHG_DATE IS NULL) AND (FTVSDAT_TERM_DATE > TRUNC(SYSDATE) OR FTVSDAT_TERM_DATE IS NULL) ; --DESC FTVSHIP_V -- FTVSHIP_v_create.sql Current Records VIEW from TABLE FTVSHIP CREATE OR REPLACE VIEW FTVSHIP_V ( FTVSHIP_CODE, FTVSHIP_EFF_DATE, FTVSHIP_ACTIVITY_DATE, FTVSHIP_USER_ID, FTVSHIP_NCHG_DATE, FTVSHIP_TERM_DATE, FTVSHIP_ADDR_LINE1, FTVSHIP_ADDR_LINE2, FTVSHIP_BUILDING, FTVSHIP_FLOOR, FTVSHIP_CITY, FTVSHIP_STATE, FTVSHIP_ZIP_CODE, FTVSHIP_CONTACT, FTVSHIP_PHONE_AREA, FTVSHIP_PHONE_NUM, FTVSHIP_PHONE_EXT, FTVSHIP_COAS_CODE, FTVSHIP_ORGN_CODE, FTVSHIP_ROUTE_CODE, FTVSHIP_TGRP_CODE, FTVSHIP_FUND_CODE_INVTRY, FTVSHIP_ACCT_CODE_INVTRY, FTVSHIP_ORGN_CODE_WARE, FTVSHIP_PROFIT_CENTER_IND, FTVSHIP_LOCN_TYPE_CODE, FTVSHIP_DELIVERY_POINT, FTVSHIP_CORRECTION_DIGIT, FTVSHIP_CARRIER_ROUTE, FTVSHIP_PROG_CODE_WARE, FTVSHIP_ADDR_LINE3, FTVSHIP_NATN_CODE ) AS SELECT -- Created from TABLE FTVSHIP Ship To Verification Table FTVSHIP_CODE, TRUNC(FTVSHIP_EFF_DATE) FTVSHIP_EFF_DATE, TRUNC(FTVSHIP_ACTIVITY_DATE) FTVSHIP_ACTIVITY_DATE, FTVSHIP_USER_ID, TRUNC(FTVSHIP_NCHG_DATE) FTVSHIP_NCHG_DATE, TRUNC(FTVSHIP_TERM_DATE) FTVSHIP_TERM_DATE, FTVSHIP_ADDR_LINE1, FTVSHIP_ADDR_LINE2, FTVSHIP_BUILDING, FTVSHIP_FLOOR, FTVSHIP_CITY, FTVSHIP_STATE, FTVSHIP_ZIP_CODE, FTVSHIP_CONTACT, FTVSHIP_PHONE_AREA, FTVSHIP_PHONE_NUM, FTVSHIP_PHONE_EXT, FTVSHIP_COAS_CODE, FTVSHIP_ORGN_CODE, FTVSHIP_ROUTE_CODE, FTVSHIP_TGRP_CODE, FTVSHIP_FUND_CODE_INVTRY, FTVSHIP_ACCT_CODE_INVTRY, FTVSHIP_ORGN_CODE_WARE, FTVSHIP_PROFIT_CENTER_IND, FTVSHIP_LOCN_TYPE_CODE, FTVSHIP_DELIVERY_POINT, FTVSHIP_CORRECTION_DIGIT, FTVSHIP_CARRIER_ROUTE, FTVSHIP_PROG_CODE_WARE, FTVSHIP_ADDR_LINE3, FTVSHIP_NATN_CODE FROM FTVSHIP WHERE TRUNC(FTVSHIP_EFF_DATE) <= SYSDATE AND (FTVSHIP_NCHG_DATE > TRUNC(SYSDATE) OR FTVSHIP_NCHG_DATE IS NULL) AND (FTVSHIP_TERM_DATE > TRUNC(SYSDATE) OR FTVSHIP_TERM_DATE IS NULL) ; --DESC FTVTGRP_V -- FTVTGRP_v_create.sql Current Records VIEW from TABLE FTVTGRP CREATE OR REPLACE VIEW FTVTGRP_V ( FTVTGRP_TGRP_CODE, FTVTGRP_EFF_DATE, FTVTGRP_ACTIVITY_DATE, FTVTGRP_USER_ID, FTVTGRP_NCHG_DATE, FTVTGRP_TERM_DATE, FTVTGRP_TITLE, FTVTGRP_STATUS_IND, FTVTGRP_COAS_CODE_VALID, FTVTGRP_NON_TAXABLE ) AS SELECT -- Created from TABLE FTVTGRP Tax group validation table FTVTGRP_TGRP_CODE, TRUNC(FTVTGRP_EFF_DATE) FTVTGRP_EFF_DATE, TRUNC(FTVTGRP_ACTIVITY_DATE) FTVTGRP_ACTIVITY_DATE, FTVTGRP_USER_ID, TRUNC(FTVTGRP_NCHG_DATE) FTVTGRP_NCHG_DATE, TRUNC(FTVTGRP_TERM_DATE) FTVTGRP_TERM_DATE, FTVTGRP_TITLE, FTVTGRP_STATUS_IND, FTVTGRP_COAS_CODE_VALID, FTVTGRP_NON_TAXABLE FROM FTVTGRP WHERE TRUNC(FTVTGRP_EFF_DATE) <= SYSDATE AND (FTVTGRP_NCHG_DATE > TRUNC(SYSDATE) OR FTVTGRP_NCHG_DATE IS NULL) AND (FTVTGRP_TERM_DATE > TRUNC(SYSDATE) OR FTVTGRP_TERM_DATE IS NULL) ; --DESC FTVTRAT_V -- FTVTRAT_v_create.sql Current Records VIEW from TABLE FTVTRAT CREATE OR REPLACE VIEW FTVTRAT_V ( FTVTRAT_CODE, FTVTRAT_EFF_DATE, FTVTRAT_TERM_DATE, FTVTRAT_NCHG_DATE, FTVTRAT_ACTIVITY_DATE, FTVTRAT_USER_ID, FTVTRAT_DESC, FTVTRAT_RATE, FTVTRAT_STATUS_IND, FTVTRAT_PIDM_TAXING_AUTHORITY, FTVTRAT_PAY_TAX_TO, FTVTRAT_COAS_CODE, FTVTRAT_FUND_CODE, FTVTRAT_ACCT_CODE, FTVTRAT_PRIORITY_CODE, FTVTRAT_ADDL_CHRG_IND, FTVTRAT_EXEMPT_PCT, FTVTRAT_AR_ACCT_CODE, FTVTRAT_AR_COAS_CODE, FTVTRAT_AR_FUND_CODE, FTVTRAT_INCLUDE_DISC_IND ) AS SELECT -- Created from TABLE FTVTRAT Tax Rate Table FTVTRAT_CODE, TRUNC(FTVTRAT_EFF_DATE) FTVTRAT_EFF_DATE, TRUNC(FTVTRAT_TERM_DATE) FTVTRAT_TERM_DATE, TRUNC(FTVTRAT_NCHG_DATE) FTVTRAT_NCHG_DATE, TRUNC(FTVTRAT_ACTIVITY_DATE) FTVTRAT_ACTIVITY_DATE, FTVTRAT_USER_ID, FTVTRAT_DESC, FTVTRAT_RATE, FTVTRAT_STATUS_IND, FTVTRAT_PIDM_TAXING_AUTHORITY, FTVTRAT_PAY_TAX_TO, FTVTRAT_COAS_CODE, FTVTRAT_FUND_CODE, FTVTRAT_ACCT_CODE, FTVTRAT_PRIORITY_CODE, FTVTRAT_ADDL_CHRG_IND, FTVTRAT_EXEMPT_PCT, FTVTRAT_AR_ACCT_CODE, FTVTRAT_AR_COAS_CODE, FTVTRAT_AR_FUND_CODE, FTVTRAT_INCLUDE_DISC_IND FROM FTVTRAT WHERE TRUNC(FTVTRAT_EFF_DATE) <= SYSDATE AND (FTVTRAT_NCHG_DATE > TRUNC(SYSDATE) OR FTVTRAT_NCHG_DATE IS NULL) AND (FTVTRAT_TERM_DATE > TRUNC(SYSDATE) OR FTVTRAT_TERM_DATE IS NULL) ; --DESC FTVVEND_V -- FTVVEND_v_create.sql Current Records VIEW from TABLE FTVVEND CREATE OR REPLACE VIEW FTVVEND_V ( FTVVEND_PIDM, FTVVEND_EFF_DATE, FTVVEND_TERM_DATE, FTVVEND_ACTIVITY_DATE, FTVVEND_USER_ID, FTVVEND_ITYP_SEQ_CODE, FTVVEND_DISC_CODE, FTVVEND_1099_RPT_ID, FTVVEND_TRAT_CODE, FTVVEND_IN_ST_IND, FTVVEND_GROUPING_IND, FTVVEND_CARRIER_IND, FTVVEND_CONTACT, FTVVEND_PHONE_AREA, FTVVEND_PHONE_NUMBER, FTVVEND_PHONE_EXT, FTVVEND_ATYP_CODE, FTVVEND_ADDR_SEQNO, FTVVEND_FED_WHOLD_PCT, FTVVEND_ST_WHOLD_PCT, FTVVEND_COMBINED_FED_ST_FILER, FTVVEND_COLLECT_TAX, FTVVEND_VEND_CHECK_PIDM, FTVVEND_VEND_CHECK_ATYP_CODE, FTVVEND_VEND_CHECK_ADDR_SEQNO, FTVVEND_CURR_CODE, FTVVEND_ENTITY_IND, FTVVEND_PIDM_OWNER ) AS SELECT -- Current Records VIEW from TABLE FTVVEND FTVVEND_PIDM, TRUNC(FTVVEND_EFF_DATE) FTVVEND_EFF_DATE, TRUNC(FTVVEND_TERM_DATE) FTVVEND_TERM_DATE, TRUNC(FTVVEND_ACTIVITY_DATE) FTVVEND_ACTIVITY_DATE, FTVVEND_USER_ID, FTVVEND_ITYP_SEQ_CODE, FTVVEND_DISC_CODE, FTVVEND_1099_RPT_ID, FTVVEND_TRAT_CODE, FTVVEND_IN_ST_IND, FTVVEND_GROUPING_IND, FTVVEND_CARRIER_IND, FTVVEND_CONTACT, FTVVEND_PHONE_AREA, FTVVEND_PHONE_NUMBER, FTVVEND_PHONE_EXT, FTVVEND_ATYP_CODE, FTVVEND_ADDR_SEQNO, FTVVEND_FED_WHOLD_PCT, FTVVEND_ST_WHOLD_PCT, FTVVEND_COMBINED_FED_ST_FILER, FTVVEND_COLLECT_TAX, FTVVEND_VEND_CHECK_PIDM, FTVVEND_VEND_CHECK_ATYP_CODE, FTVVEND_VEND_CHECK_ADDR_SEQNO, FTVVEND_CURR_CODE, FTVVEND_ENTITY_IND, FTVVEND_PIDM_OWNER FROM FTVVEND WHERE TRUNC(FTVVEND_EFF_DATE) <= SYSDATE AND (FTVVEND_TERM_DATE > TRUNC(SYSDATE) OR FTVVEND_TERM_DATE IS NULL) ; --DESC FTVVTYP_V -- FTVVTYP_v_create.sql Current Records VIEW from TABLE FTVVTYP CREATE OR REPLACE VIEW FTVVTYP_V ( FTVVTYP_CODE, FTVVTYP_EFF_DATE, FTVVTYP_TERM_DATE, FTVVTYP_ACTIVITY_DATE, FTVVTYP_USER_ID, FTVVTYP_DESC ) AS SELECT -- Created from TABLE FTVVTYP Vendor Type Table FTVVTYP_CODE, TRUNC(FTVVTYP_EFF_DATE) FTVVTYP_EFF_DATE, TRUNC(FTVVTYP_TERM_DATE) FTVVTYP_TERM_DATE, TRUNC(FTVVTYP_ACTIVITY_DATE) FTVVTYP_ACTIVITY_DATE, FTVVTYP_USER_ID, FTVVTYP_DESC FROM FTVVTYP WHERE TRUNC(FTVVTYP_EFF_DATE) <= SYSDATE AND (FTVVTYP_TERM_DATE > TRUNC(SYSDATE) OR FTVVTYP_TERM_DATE IS NULL) ; --DESC GOREMAL_V -- GOREMAL_v_create.sql Current Records VIEW from TABLE GOREMAL CREATE OR REPLACE VIEW GOREMAL_V ( GOREMAL_PIDM, GOREMAL_EMAL_CODE, GOREMAL_EMAIL_ADDRESS, GOREMAL_STATUS_IND, GOREMAL_PREFERRED_IND, GOREMAL_ACTIVITY_DATE, GOREMAL_USER_ID, GOREMAL_COMMENT, GOREMAL_DISP_WEB_IND ) AS SELECT -- Created from TABLE GOREMAL Person E-mail repeating table. GOREMAL_PIDM, GOREMAL_EMAL_CODE, GOREMAL_EMAIL_ADDRESS, GOREMAL_STATUS_IND, GOREMAL_PREFERRED_IND, TRUNC(GOREMAL_ACTIVITY_DATE) GOREMAL_ACTIVITY_DATE, GOREMAL_USER_ID, GOREMAL_COMMENT, GOREMAL_DISP_WEB_IND FROM GOREMAL WHERE GOREMAL_STATUS_IND = 'A' AND GOREMAL_PREFERRED_IND = 'Y' ; --DESC LENGTH_OF_SERVICE_CES -- LENGTH_OF_SERVICE_create.sql Current Records VIEW from TABLE ^ CREATE OR REPLACE VIEW LENGTH_OF_SERVICE ( Service_PIDM, ADJ_SERVICE_DATE, YRS_JAN1_CURR_YR, YRS_JUN30_CURR_YR, YRS_DEC31_CURR_YR, YRS_TODAY, ANNIVERSARY, YRS_ON_ANNIVERSARY ) AS SELECT pebempl_pidm Service_PIDM, -- Length of Service on various dates -- Length of Service - Multiple calculations of Years since pebempl_first_work_date for various dates. SUBSTR(TO_CHAR(pebempl_first_work_date,'MM/DD/YYYY'),1,10) adj_service_date, TRUNC(MONTHS_BETWEEN(TO_DATE('01/01/'||TO_CHAR(SYSDATE,'YYYY'),'MM/DD/YYYY'),pebempl_first_work_date)/12,0) YRS_JAN1_CURR_YR, TRUNC(MONTHS_BETWEEN(TO_DATE('06/30/'||TO_CHAR(SYSDATE,'YYYY')||' 23:59:59','MM/DD/YYYY HH24:MI:SS'),pebempl_first_work_date)/12,0) YRS_JUN30_CURR_YR, TRUNC(MONTHS_BETWEEN(TO_DATE('12/31/'||TO_CHAR(SYSDATE,'YYYY')||' 23:59:59','MM/DD/YYYY HH24:MI:SS'),pebempl_first_work_date)/12,0) YRS_DEC31_CURR_YR, TRUNC(MONTHS_BETWEEN(SYSDATE,TRUNC(pebempl_first_work_date))/12,0) YRS_TODAY, TO_CHAR(pebempl_first_work_date,'MM/DD')||'/'||TO_CHAR(SYSDATE,'YYYY') ANNIVERSARY, TRUNC(MONTHS_BETWEEN(TO_DATE(TO_CHAR(pebempl_first_work_date,'MM/DD')||TO_CHAR(SYSDATE,'YYYY'),'MM/DD/YYYY'),pebempl_first_work_date)/12,0) YRS_ON_ANNIVERSARY FROM pebempl WHERE pebempl_coas_code_dist = '^' AND pebempl_last_work_date IS NULL ; --DESC NAMES -- names_create.sql Current Records VIEW from TABLE SPRIDEN-SPBPERS CREATE OR REPLACE VIEW NAMES ( PIDM, ID, SSN, NAME, LEGAL_NAME, SORT_LAST ) AS SELECT SPRIDEN_PIDM, -- PIDM, SPRIDEN_ID, -- ID, SPBPERS_SSN, -- SSN, SUBSTR(DECODE(SPBPERS_NAME_PREFIX,'Mr',NULL,'Mr.',NULL,'Miss',NULL,'Mrs',NULL,'Mrs.',NULL,'Ms',NULL,'Ms.',NULL,NULL,NULL, SPBPERS_NAME_PREFIX||' ')||RTRIM(DECODE(NVL(SPBPERS_PREF_FIRST_NAME,'X'),'X',spriden_first_name,spbpers_pref_first_name))||' '||RTRIM(spriden_last_name),1,25), -- Name, SUBSTR(RTRIM(spriden_last_name)||', '||RTRIM(spriden_first_name)||' '||RTRIM(spriden_mi),1,25), -- Legal_Name, SUBSTR(spriden_last_name,1,15) -- Sort_Last FROM SPRIDEN, SPBPERS WHERE SPRIDEN_PIDM = SPBPERS_PIDM (+) AND SPRIDEN_CHANGE_IND IS NULL AND SPRIDEN_ENTITY_IND = 'P' ; --DESC NAMES_ACTIVE_EMPLOYEES -- names_active_employees_create.sql Current Records VIEW from TABLE SPRIDEN-SPBPERS CREATE OR REPLACE VIEW NAMES_ACTIVE_EMPLOYEES ( PIDM, ID, SSN, NAME, LEGAL_NAME, SORT_LAST ) AS SELECT SPRIDEN_PIDM, -- PIDM, SPRIDEN_ID, -- ID, SPBPERS_SSN, -- SSN, SUBSTR(DECODE(SPBPERS_NAME_PREFIX,'Mr',NULL,'Mr.',NULL,'Miss',NULL,'Mrs',NULL,'Mrs.',NULL,'Ms',NULL,'Ms.',NULL,NULL,NULL, SPBPERS_NAME_PREFIX||' ')||RTRIM(DECODE(NVL(SPBPERS_PREF_FIRST_NAME,'X'),'X',spriden_first_name,spbpers_pref_first_name))||' '||RTRIM(spriden_last_name),1,25), -- Name, SUBSTR(RTRIM(spriden_last_name)||', '||RTRIM(spriden_first_name)||' '||RTRIM(spriden_mi),1,25), -- Legal_Name, SUBSTR(spriden_last_name,1,15) -- Sort_Last FROM SPRIDEN, SPBPERS, PEBEMPL WHERE SPRIDEN_PIDM = SPBPERS_PIDM (+) AND SPRIDEN_PIDM = PEBEMPL_PIDM AND SPRIDEN_CHANGE_IND IS NULL AND SPRIDEN_ENTITY_IND = 'P' AND pebempl_coas_code_dist = '^' AND PEBEMPL_EMPL_STATUS = 'A' AND (PEBEMPL_TERM_DATE > TRUNC(SYSDATE) OR PEBEMPL_TERM_DATE IS NULL) AND PEBEMPL_LAST_WORK_DATE Is Null -- AND NVL(SPBPERS_DEAD_IND,'N') = 'N' ; --DESC NAMES_VENDORS -- names_vendors_create.sql Current Records VIEW from TABLE SPRIDEN-SPBPERS CREATE OR REPLACE VIEW NAMES_VENDORS ( PIDM, ID, SSN, NAME ) AS SELECT SPRIDEN_PIDM, -- PIDM, SPRIDEN_ID, -- ID, SPBPERS_SSN, -- SSN, DECODE(spriden_entity_ind, 'C',spriden_last_name, 'P', substr(DECODE(SPBPERS_NAME_PREFIX, '',(SPRIDEN_FIRST_NAME || ' ' || DECODE(SPRIDEN_MI,'',SPRIDEN_LAST_NAME||' '|| SPBPERS_NAME_SUFFIX, SUBSTR(SPRIDEN_MI,1,1) || '. ' || SPRIDEN_LAST_NAME || ' ' || SPBPERS_NAME_SUFFIX)), SPBPERS_NAME_PREFIX || ' ' || SPRIDEN_FIRST_NAME || ' ' || DECODE(SPRIDEN_MI,'',SPRIDEN_LAST_NAME || ' ' || SPBPERS_NAME_SUFFIX, SUBSTR(SPRIDEN_MI,1,1) || '. ' || SPRIDEN_LAST_NAME || ' ' || SPBPERS_NAME_SUFFIX)),1,60)) -- Name FROM SPRIDEN, SPBPERS WHERE SPRIDEN_PIDM = SPBPERS_PIDM (+) AND spriden_change_ind is null AND SPRIDEN_ID LIKE '@%' ; --DESC NBBPOSN_V -- NBBPOSN_v_create.sql Current Records VIEW from TABLE NBBPOSN CREATE OR REPLACE VIEW NBBPOSN_V ( NBBPOSN_POSN, NBBPOSN_STATUS, NBBPOSN_TITLE, NBBPOSN_PLOC_CODE, NBBPOSN_BEGIN_DATE, NBBPOSN_END_DATE, NBBPOSN_TYPE, NBBPOSN_PCLS_CODE, NBBPOSN_ECLS_CODE, NBBPOSN_POSN_REPORTS, NBBPOSN_AUTH_NUMBER, NBBPOSN_TABLE, NBBPOSN_GRADE, NBBPOSN_STEP, NBBPOSN_APPT_PCT, NBBPOSN_CIPC_CODE, NBBPOSN_ROLL_IND, NBBPOSN_COAS_CODE, NBBPOSN_ACTIVITY_DATE, NBBPOSN_SGRP_CODE, NBBPOSN_PGRP_CODE, NBBPOSN_WKSH_CODE, NBBPOSN_PREMIUM_ROLL_IND, NBBPOSN_PFOC_CODE, NBBPOSN_PNOC_CODE, NBBPOSN_DOTT_CODE, NBBPOSN_CHANGE_DATE_TIME, NBBPOSN_CALIF_TYPE ) AS SELECT -- Created from TABLE NBBPOSN Position Base Table NBBPOSN_POSN, NBBPOSN_STATUS, NBBPOSN_TITLE, NBBPOSN_PLOC_CODE, TRUNC(NBBPOSN_BEGIN_DATE) NBBPOSN_BEGIN_DATE, TRUNC(NBBPOSN_END_DATE) NBBPOSN_END_DATE, NBBPOSN_TYPE, NBBPOSN_PCLS_CODE, NBBPOSN_ECLS_CODE, NBBPOSN_POSN_REPORTS, NBBPOSN_AUTH_NUMBER, NBBPOSN_TABLE, NBBPOSN_GRADE, NBBPOSN_STEP, NBBPOSN_APPT_PCT, NBBPOSN_CIPC_CODE, NBBPOSN_ROLL_IND, NBBPOSN_COAS_CODE, TRUNC(NBBPOSN_ACTIVITY_DATE) NBBPOSN_ACTIVITY_DATE, NBBPOSN_SGRP_CODE, NBBPOSN_PGRP_CODE, NBBPOSN_WKSH_CODE, NBBPOSN_PREMIUM_ROLL_IND, NBBPOSN_PFOC_CODE, NBBPOSN_PNOC_CODE, NBBPOSN_DOTT_CODE, TRUNC(NBBPOSN_CHANGE_DATE_TIME) NBBPOSN_CHANGE_DATE_TIME, NBBPOSN_CALIF_TYPE FROM NBBPOSN WHERE TRUNC(NBBPOSN_BEGIN_DATE) <= SYSDATE AND (NBBPOSN_END_DATE > SYSDATE OR NBBPOSN_END_DATE IS NULL) ; --DESC NBRBJOB_V -- NBRBJOB_v_create.sql Current Records VIEW from TABLE NBRBJOB CREATE OR REPLACE VIEW NBRBJOB_V ( NBRBJOB_PIDM, NBRBJOB_POSN, NBRBJOB_SUFF, NBRBJOB_BEGIN_DATE, NBRBJOB_END_DATE, NBRBJOB_DEFER_BAL, NBRBJOB_CONTRACT_TYPE, NBRBJOB_SALARY_ENCUMBRANCE, NBRBJOB_CONTRACT_BEGIN_DATE, NBRBJOB_CONTRACT_END_DATE, NBRBJOB_TOTAL_CONTRACT_HRS, NBRBJOB_TOTAL_ENCUMBRANCE_HRS, NBRBJOB_STEP_INCR_MON, NBRBJOB_STEP_INCR_DAY, NBRBJOB_COAS_CODE, NBRBJOB_ACTIVITY_DATE, NBRBJOB_ACCRUE_LEAVE_IND, NBRBJOB_CIVIL_SERVICE_IND, NBRBJOB_ENCUMBRANCE_CHANGE_IND, NBRBJOB_FRINGE_ENCUMBRANCE, NBRBJOB_IPEDS_REPT_IND, NBRBJOB_FACL_STATSCAN_REPT_IND ) AS SELECT -- Created from TABLE NBRBJOB Assignment Repeating Base Table NBRBJOB_PIDM, NBRBJOB_POSN, NBRBJOB_SUFF, TRUNC(NBRBJOB_BEGIN_DATE) NBRBJOB_BEGIN_DATE, TRUNC(NBRBJOB_END_DATE) NBRBJOB_END_DATE, NBRBJOB_DEFER_BAL, NBRBJOB_CONTRACT_TYPE, NBRBJOB_SALARY_ENCUMBRANCE, TRUNC(NBRBJOB_CONTRACT_BEGIN_DATE) NBRBJOB_CONTRACT_BEGIN_DATE, TRUNC(NBRBJOB_CONTRACT_END_DATE) NBRBJOB_CONTRACT_END_DATE, NBRBJOB_TOTAL_CONTRACT_HRS, NBRBJOB_TOTAL_ENCUMBRANCE_HRS, NBRBJOB_STEP_INCR_MON, NBRBJOB_STEP_INCR_DAY, NBRBJOB_COAS_CODE, TRUNC(NBRBJOB_ACTIVITY_DATE) NBRBJOB_ACTIVITY_DATE, NBRBJOB_ACCRUE_LEAVE_IND, NBRBJOB_CIVIL_SERVICE_IND, NBRBJOB_ENCUMBRANCE_CHANGE_IND, NBRBJOB_FRINGE_ENCUMBRANCE, NBRBJOB_IPEDS_REPT_IND, NBRBJOB_FACL_STATSCAN_REPT_IND FROM NBRBJOB WHERE (TRUNC(NBRBJOB_BEGIN_DATE) < SYSDATE OR NBRBJOB_BEGIN_DATE IS NULL) AND (TRUNC(NBRBJOB_END_DATE) >= SYSDATE OR NBRBJOB_END_DATE IS NULL) ; --DESC NBRJLBD_V -- NBRJLBD_v_create.sql Current Records VIEW from TABLE NBRJLBD CREATE OR REPLACE VIEW NBRJLBD_V ( NBRJLBD_PIDM, NBRJLBD_POSN, NBRJLBD_SUFF, NBRJLBD_EFFECTIVE_DATE, NBRJLBD_COAS_CODE, NBRJLBD_ACCI_CODE, NBRJLBD_FUND_CODE, NBRJLBD_ORGN_CODE, NBRJLBD_ACCT_CODE, NBRJLBD_PROG_CODE, NBRJLBD_ACTV_CODE, NBRJLBD_LOCN_CODE, NBRJLBD_PROJ_CODE, NBRJLBD_CTYP_CODE, NBRJLBD_ACCT_CODE_EXTERNAL, NBRJLBD_PERCENT, NBRJLBD_ENCD_NUM, NBRJLBD_ENCD_SEQ_NUM, NBRJLBD_SALARY_ENCUMBRANCE, NBRJLBD_SALARY_ENC_TO_POST, NBRJLBD_FRINGE_ENCUMBRANCE, NBRJLBD_FRINGE_ENC_TO_POST, NBRJLBD_FUND_CODE_FRINGE, NBRJLBD_ORGN_CODE_FRINGE, NBRJLBD_ACCT_CODE_FRINGE, NBRJLBD_PROG_CODE_FRINGE, NBRJLBD_ACTV_CODE_FRINGE, NBRJLBD_LOCN_CODE_FRINGE, NBRJLBD_CHANGE_IND, NBRJLBD_ACTIVITY_DATE, NBRJLBD_FRINGE_RESIDUAL, NBRJLBD_FRINGE_RES_TO_POST ) AS SELECT -- Created from TABLE NBRJLBD Assignment Labor Dist. Repeating Table NBRJLBD_PIDM, NBRJLBD_POSN, NBRJLBD_SUFF, TRUNC(NBRJLBD_EFFECTIVE_DATE) NBRJLBD_EFFECTIVE_DATE, NBRJLBD_COAS_CODE, NBRJLBD_ACCI_CODE, NBRJLBD_FUND_CODE, NBRJLBD_ORGN_CODE, NBRJLBD_ACCT_CODE, NBRJLBD_PROG_CODE, NBRJLBD_ACTV_CODE, NBRJLBD_LOCN_CODE, NBRJLBD_PROJ_CODE, NBRJLBD_CTYP_CODE, NBRJLBD_ACCT_CODE_EXTERNAL, NBRJLBD_PERCENT, NBRJLBD_ENCD_NUM, NBRJLBD_ENCD_SEQ_NUM, NBRJLBD_SALARY_ENCUMBRANCE, NBRJLBD_SALARY_ENC_TO_POST, NBRJLBD_FRINGE_ENCUMBRANCE, NBRJLBD_FRINGE_ENC_TO_POST, NBRJLBD_FUND_CODE_FRINGE, NBRJLBD_ORGN_CODE_FRINGE, NBRJLBD_ACCT_CODE_FRINGE, NBRJLBD_PROG_CODE_FRINGE, NBRJLBD_ACTV_CODE_FRINGE, NBRJLBD_LOCN_CODE_FRINGE, NBRJLBD_CHANGE_IND, TRUNC(NBRJLBD_ACTIVITY_DATE) NBRJLBD_ACTIVITY_DATE, NBRJLBD_FRINGE_RESIDUAL, NBRJLBD_FRINGE_RES_TO_POST FROM NBRJLBD X WHERE NBRJLBD_EFFECTIVE_DATE = ( SELECT MAX(NBRJLBD_EFFECTIVE_DATE) FROM NBRJLBD Y WHERE NBRJLBD_PIDM = X.NBRJLBD_PIDM AND NBRJLBD_POSN = X.NBRJLBD_POSN AND NBRJLBD_SUFF = X.NBRJLBD_SUFF AND NBRJLBD_EFFECTIVE_DATE <= SYSDATE AND NBRJLBD_CHANGE_IND = 'A' ) AND NBRJLBD_CHANGE_IND = 'A' ; --DESC NBRJOBS_V -- NBRJOBS_v_create.sql Current Records VIEW from TABLE NBRJOBS CREATE OR REPLACE VIEW NBRJOBS_V ( NBRJOBS_PIDM, NBRJOBS_POSN, NBRJOBS_SUFF, NBRJOBS_EFFECTIVE_DATE, NBRJOBS_STATUS, NBRJOBS_DESC, NBRJOBS_ECLS_CODE, NBRJOBS_PICT_CODE, NBRJOBS_COAS_CODE_TS, NBRJOBS_ORGN_CODE_TS, NBRJOBS_SAL_TABLE, NBRJOBS_SAL_GRADE, NBRJOBS_SAL_STEP, NBRJOBS_APPT_PCT, NBRJOBS_FTE, NBRJOBS_HRS_DAY, NBRJOBS_HRS_PAY, NBRJOBS_SHIFT, NBRJOBS_REG_RATE, NBRJOBS_ASSGN_SALARY, NBRJOBS_FACTOR, NBRJOBS_ANN_SALARY, NBRJOBS_PER_PAY_SALARY, NBRJOBS_PAYS, NBRJOBS_PER_PAY_DEFER_AMT, NBRJOBS_ACTIVITY_DATE, NBRJOBS_JCRE_CODE, NBRJOBS_SGRP_CODE, NBRJOBS_EMPR_CODE, NBRJOBS_LGCD_CODE, NBRJOBS_LOCN_CODE, NBRJOBS_SCHL_CODE, NBRJOBS_SUPERVISOR_PIDM, NBRJOBS_SUPERVISOR_POSN, NBRJOBS_SUPERVISOR_SUFF, NBRJOBS_WKCP_CODE, NBRJOBS_JBLN_CODE, NBRJOBS_PERS_CHG_DATE, NBRJOBS_PCAT_CODE, NBRJOBS_DFPR_CODE, NBRJOBS_ENCUMBRANCE_HRS, NBRJOBS_TIME_ENTRY_METHOD, NBRJOBS_CONTRACT_NO, NBRJOBS_STRS_ASSN_CODE, NBRJOBS_STRS_PAY_CODE, NBRJOBS_PERS_PAY_CODE ) AS SELECT -- Created from TABLE NBRJOBS Assignment Repeating Table NBRJOBS_PIDM, NBRJOBS_POSN, NBRJOBS_SUFF, TRUNC(NBRJOBS_EFFECTIVE_DATE) NBRJOBS_EFFECTIVE_DATE, NBRJOBS_STATUS, NBRJOBS_DESC, NBRJOBS_ECLS_CODE, NBRJOBS_PICT_CODE, NBRJOBS_COAS_CODE_TS, NBRJOBS_ORGN_CODE_TS, NBRJOBS_SAL_TABLE, NBRJOBS_SAL_GRADE, NBRJOBS_SAL_STEP, NBRJOBS_APPT_PCT, NBRJOBS_FTE, NBRJOBS_HRS_DAY, NBRJOBS_HRS_PAY, NBRJOBS_SHIFT, NBRJOBS_REG_RATE, NBRJOBS_ASSGN_SALARY, NBRJOBS_FACTOR, NBRJOBS_ANN_SALARY, NBRJOBS_PER_PAY_SALARY, NBRJOBS_PAYS, NBRJOBS_PER_PAY_DEFER_AMT, TRUNC(NBRJOBS_ACTIVITY_DATE) NBRJOBS_ACTIVITY_DATE, NBRJOBS_JCRE_CODE, NBRJOBS_SGRP_CODE, NBRJOBS_EMPR_CODE, NBRJOBS_LGCD_CODE, NBRJOBS_LOCN_CODE, NBRJOBS_SCHL_CODE, NBRJOBS_SUPERVISOR_PIDM, NBRJOBS_SUPERVISOR_POSN, NBRJOBS_SUPERVISOR_SUFF, NBRJOBS_WKCP_CODE, NBRJOBS_JBLN_CODE, TRUNC(NBRJOBS_PERS_CHG_DATE) NBRJOBS_PERS_CHG_DATE, NBRJOBS_PCAT_CODE, NBRJOBS_DFPR_CODE, NBRJOBS_ENCUMBRANCE_HRS, NBRJOBS_TIME_ENTRY_METHOD, NBRJOBS_CONTRACT_NO, NBRJOBS_STRS_ASSN_CODE, NBRJOBS_STRS_PAY_CODE, NBRJOBS_PERS_PAY_CODE FROM NBRJOBS A WHERE A.NBRJOBS_EFFECTIVE_DATE = (SELECT MAX(B.NBRJOBS_EFFECTIVE_DATE) FROM NBRJOBS B WHERE B.NBRJOBS_PIDM = A.NBRJOBS_PIDM AND B.NBRJOBS_POSN = A.NBRJOBS_POSN AND B.NBRJOBS_SUFF = A.NBRJOBS_SUFF AND TRUNC( B.NBRJOBS_EFFECTIVE_DATE) <= SYSDATE) AND NBRJOBS_STATUS = 'A' ; --DESC PDRDEDN_V -- PDRDEDN_v_create.sql Current Records VIEW from TABLE PDRDEDN CREATE OR REPLACE VIEW PDRDEDN_V ( PDRDEDN_PIDM, PDRDEDN_BDCA_CODE, PDRDEDN_EFFECTIVE_DATE, PDRDEDN_STATUS, PDRDEDN_REF_NO, PDRDEDN_AMOUNT1, PDRDEDN_AMOUNT2, PDRDEDN_AMOUNT3, PDRDEDN_AMOUNT4, PDRDEDN_OPT_CODE1, PDRDEDN_OPT_CODE2, PDRDEDN_OPT_CODE3, PDRDEDN_OPT_CODE4, PDRDEDN_OPT_CODE5, PDRDEDN_ACTIVITY_DATE, PDRDEDN_COVERAGE_DATE ) AS SELECT -- Created from TABLE PDRDEDN Employee Deduction Repeating Table PDRDEDN_PIDM, PDRDEDN_BDCA_CODE, TRUNC(PDRDEDN_EFFECTIVE_DATE) PDRDEDN_EFFECTIVE_DATE, PDRDEDN_STATUS, PDRDEDN_REF_NO, PDRDEDN_AMOUNT1, PDRDEDN_AMOUNT2, PDRDEDN_AMOUNT3, PDRDEDN_AMOUNT4, PDRDEDN_OPT_CODE1, PDRDEDN_OPT_CODE2, PDRDEDN_OPT_CODE3, PDRDEDN_OPT_CODE4, PDRDEDN_OPT_CODE5, TRUNC(PDRDEDN_ACTIVITY_DATE) PDRDEDN_ACTIVITY_DATE, TRUNC(PDRDEDN_COVERAGE_DATE) PDRDEDN_COVERAGE_DATE FROM PDRDEDN A WHERE A.ROWID IN (SELECT B.ROWID FROM pdrdedn B WHERE B.pdrdedn_effective_date = (SELECT MAX(C.pdrdedn_effective_date) FROM pdrdedn C WHERE C.pdrdedn_pidm = B.pdrdedn_pidm AND C.pdrdedn_effective_date <= SYSDATE AND C.pdrdedn_bdca_code = B.pdrdedn_bdca_code ) AND B.pdrdedn_effective_date <= SYSDATE AND B.pdrdedn_bdca_code = A.pdrdedn_bdca_code ) AND A.pdrdedn_effective_date <= SYSDATE ; --DESC PEBEMPL_V -- PEBEMPL_v_create.sql Current Records VIEW from TABLE PEBEMPL CREATE OR REPLACE VIEW PEBEMPL_V ( PEBEMPL_PIDM, PEBEMPL_EMPL_STATUS, PEBEMPL_COAS_CODE_HOME, PEBEMPL_ORGN_CODE_HOME, PEBEMPL_COAS_CODE_DIST, PEBEMPL_ORGN_CODE_DIST, PEBEMPL_ECLS_CODE, PEBEMPL_LCAT_CODE, PEBEMPL_BCAT_CODE, PEBEMPL_FIRST_HIRE_DATE, PEBEMPL_CURRENT_HIRE_DATE, PEBEMPL_ADJ_SERVICE_DATE, PEBEMPL_SENIORITY_DATE, PEBEMPL_LREA_CODE, PEBEMPL_LOA_BEG_DATE, PEBEMPL_LOA_END_DATE, PEBEMPL_TREA_CODE, PEBEMPL_TERM_DATE, PEBEMPL_I9_FORM_IND, PEBEMPL_I9_DATE, PEBEMPL_I9_EXPIRE_DATE, PEBEMPL_ACTIVITY_DATE, PEBEMPL_WKPR_CODE, PEBEMPL_FLSA_IND, PEBEMPL_STGR_CODE, PEBEMPL_DAYS_IN_CANADA, PEBEMPL_1042_RECIPIENT_CD, PEBEMPL_INTERNAL_FT_PT_IND, PEBEMPL_DICD_CODE, PEBEMPL_EGRP_CODE, PEBEMPL_IPEDS_SOFT_MONEY_IND, PEBEMPL_FIRST_WORK_DATE, PEBEMPL_LAST_WORK_DATE, PEBEMPL_CALIF_PENSION_IND, PEBEMPL_NRSI_CODE ) AS SELECT -- PEBEMPL Employee Base Table PEBEMPL_PIDM, PEBEMPL_EMPL_STATUS, PEBEMPL_COAS_CODE_HOME, PEBEMPL_ORGN_CODE_HOME, PEBEMPL_COAS_CODE_DIST, PEBEMPL_ORGN_CODE_DIST, PEBEMPL_ECLS_CODE, PEBEMPL_LCAT_CODE, PEBEMPL_BCAT_CODE, TRUNC(PEBEMPL_FIRST_HIRE_DATE) PEBEMPL_FIRST_HIRE_DATE, TRUNC(PEBEMPL_CURRENT_HIRE_DATE) PEBEMPL_CURRENT_HIRE_DATE, TRUNC(PEBEMPL_ADJ_SERVICE_DATE) PEBEMPL_ADJ_SERVICE_DATE, TRUNC(PEBEMPL_SENIORITY_DATE) PEBEMPL_SENIORITY_DATE, PEBEMPL_LREA_CODE, TRUNC(PEBEMPL_LOA_BEG_DATE) PEBEMPL_LOA_BEG_DATE, TRUNC(PEBEMPL_LOA_END_DATE) PEBEMPL_LOA_END_DATE, PEBEMPL_TREA_CODE, TRUNC(PEBEMPL_TERM_DATE) PEBEMPL_TERM_DATE, PEBEMPL_I9_FORM_IND, TRUNC(PEBEMPL_I9_DATE) PEBEMPL_I9_DATE, TRUNC(PEBEMPL_I9_EXPIRE_DATE) PEBEMPL_I9_EXPIRE_DATE, TRUNC(PEBEMPL_ACTIVITY_DATE) PEBEMPL_ACTIVITY_DATE, PEBEMPL_WKPR_CODE, PEBEMPL_FLSA_IND, PEBEMPL_STGR_CODE, PEBEMPL_DAYS_IN_CANADA, PEBEMPL_1042_RECIPIENT_CD, PEBEMPL_INTERNAL_FT_PT_IND, PEBEMPL_DICD_CODE, PEBEMPL_EGRP_CODE, PEBEMPL_IPEDS_SOFT_MONEY_IND, TRUNC(PEBEMPL_FIRST_WORK_DATE) PEBEMPL_FIRST_WORK_DATE, TRUNC(PEBEMPL_LAST_WORK_DATE) PEBEMPL_LAST_WORK_DATE, PEBEMPL_CALIF_PENSION_IND, PEBEMPL_NRSI_CODE FROM PEBEMPL ; --DESC PERREVW_CURRENT_REVIEW_V -- PERREVW_CURRENT_REVIEW_v_create.sql Current Records VIEW from TABLE PERREVW_CURRENT_REVIEW CREATE OR REPLACE VIEW PERREVW_CURRENT_REVIEW_V ( PERREVW_PIDM, PERREVW_REVT_CODE, PERREVW_REVT_DATE, PERREVW_REVT_COMPLETE, PERREVW_REVT_RATING, PERREVW_ACTIVITY_DATE, PERREVW_REVIEWER_PIDM, PERREVW_COMMENT, PERREVW_COMPLETED_DATE ) AS SELECT PERREVW_PIDM, -- Created from TABLE PERREVW Employee Review Type Repeating Table PERREVW_REVT_CODE, TRUNC(PERREVW_REVT_DATE) PERREVW_REVT_DATE, PERREVW_REVT_COMPLETE, PERREVW_REVT_RATING, TRUNC(PERREVW_ACTIVITY_DATE) PERREVW_ACTIVITY_DATE, PERREVW_REVIEWER_PIDM, PERREVW_COMMENT, TRUNC(PERREVW_COMPLETED_DATE) PERREVW_COMPLETED_DATE FROM PERREVW X WHERE (X.perrevw_revt_date = (SELECT MAX(Y.perrevw_revt_date) FROM PERREVW Y WHERE Y.PERREVW_PIDM = X.PERREVW_PIDM AND TRUNC( Y.perrevw_revt_date) <= SYSDATE) OR X.perrevw_revt_date IS NULL) ; --DESC PEVEMPL_WITH_SOURCE_FIELDS_V -- PEVEMPL_WITH_SOURCE_FIELDS_v_create.sql Current Records VIEW from TABLE PEVEMPL_WITH_SOURCE_FIELDS CREATE OR REPLACE VIEW PEVEMPL_WITH_SOURCE_FIELDS_V ( SPRIDEN_PIDM, SPRIDEN_ID, PEVEMPL_FULL_NAME, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME, SPRIDEN_MI, PEBEMPL_EMPL_STATUS, PEVEMPL_EMPL_STATUS, PEBEMPL_COAS_CODE_HOME, PEBEMPL_ORGN_CODE_HOME, PEBEMPL_COAS_CODE_DIST, PEBEMPL_ORGN_CODE_DIST, PEBEMPL_ECLS_CODE, PTRECLS_LONG_DESC, PEBEMPL_LCAT_CODE, PTVLCAT_DESC, PEBEMPL_BCAT_CODE, PTRBCAT_DESC, PEBEMPL_FIRST_HIRE_DATE, PEBEMPL_CURRENT_HIRE_DATE, PEBEMPL_ADJ_SERVICE_DATE, PEBEMPL_SENIORITY_DATE, PEBEMPL_LREA_CODE, PTRLREA_DESC, PEBEMPL_LOA_BEG_DATE, PEBEMPL_LOA_END_DATE, PEBEMPL_TREA_CODE, PTRTREA_DESC, PEBEMPL_TERM_DATE, PEBEMPL_I9_FORM_IND, PEVEMPL_I9_FORM_IND, PEBEMPL_I9_DATE, PEBEMPL_I9_EXPIRE_DATE, PEBEMPL_WKPR_CODE, PTRWKPR_DESC, PEBEMPL_FLSA_IND, PEVEMPL_FLSA_IND, PEBEMPL_STGR_CODE, PTVSTGR_DESC, PEBEMPL_DAYS_IN_CANADA, SPBPERS_SSN, SPBPERS_BIRTH_DATE, SPBPERS_LGCY_CODE, STVLGCY_DESC, SPBPERS_ETHN_CODE, STVETHN_DESC, SPBPERS_MRTL_CODE, STVMRTL_DESC, SPBPERS_SEX, SPBPERS_CONFID_IND, PEVEMPL_CONFID_IND, SPBPERS_VETC_FILE_NUMBER, SPBPERS_PREF_FIRST_NAME, SPBPERS_NAME_PREFIX, SPBPERS_NAME_SUFFIX, SPBPERS_VERA_IND, PEVEMPL_VERA_IND, SPBPERS_CITZ_CODE, STVCITZ_DESC, SPBPERS_CITY_BIRTH, SPBPERS_DRIVER_LICENSE, SPBPERS_STAT_CODE_DRIVER, STVSTAT_DESC, SPBPERS_NATN_CODE_DRIVER, STVNATN_NATION, SPBPERS_SDVET_IND, PEVEMPL_SDVET_IND, PEBEMPL_1042_RECIPIENT_CD ) AS SELECT -- Created from VIEW PEVEMPL Employee Information View SPRIDEN_PIDM, SPRIDEN_ID, SUBSTR(SPRIDEN_LAST_NAME || ', ' || SPRIDEN_FIRST_NAME || ' ' || SUBSTR(SPRIDEN_MI,1,1),1,25) PEVEMPL_FULL_NAME, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME, SPRIDEN_MI, PEBEMPL_EMPL_STATUS, DECODE(PEBEMPL_EMPL_STATUS, 'A','Active', 'F', 'Leave, full pay/benefits', 'L', 'Leave, no pay/benefits', 'B', 'Leave with benefits', 'P', 'Partial pay/full benefits', 'T', 'Terminated', NULL) PEVEMPL_EMPL_STATUS, PEBEMPL_COAS_CODE_HOME, PEBEMPL_ORGN_CODE_HOME, PEBEMPL_COAS_CODE_DIST, PEBEMPL_ORGN_CODE_DIST, PEBEMPL_ECLS_CODE, PTRECLS_LONG_DESC, PEBEMPL_LCAT_CODE, PTVLCAT_DESC, PEBEMPL_BCAT_CODE, PTRBCAT_DESC, PEBEMPL_FIRST_HIRE_DATE, PEBEMPL_CURRENT_HIRE_DATE, PEBEMPL_ADJ_SERVICE_DATE, PEBEMPL_SENIORITY_DATE, PEBEMPL_LREA_CODE, PTRLREA_DESC, PEBEMPL_LOA_BEG_DATE, PEBEMPL_LOA_END_DATE, PEBEMPL_TREA_CODE, PTRTREA_DESC, PEBEMPL_TERM_DATE, PEBEMPL_I9_FORM_IND, DECODE(PEBEMPL_I9_FORM_IND, 'R','Received', 'N','Not received', 'T','Temporary', 'E','Exempt', NULL) PEVEMPL_I9_FORM_IND, PEBEMPL_I9_DATE, PEBEMPL_I9_EXPIRE_DATE, PEBEMPL_WKPR_CODE, PTRWKPR_DESC, PEBEMPL_FLSA_IND, DECODE(PEBEMPL_FLSA_IND, 'A','Accrual', 'C','Cash', 'N','None', NULL) PEVEMPL_FLSA_IND, PEBEMPL_STGR_CODE, PTVSTGR_DESC, PEBEMPL_DAYS_IN_CANADA, SPBPERS_SSN, SPBPERS_BIRTH_DATE, SPBPERS_LGCY_CODE, STVLGCY_DESC, SPBPERS_ETHN_CODE, STVETHN_DESC, SPBPERS_MRTL_CODE, STVMRTL_DESC, SPBPERS_SEX, SPBPERS_CONFID_IND, DECODE(SPBPERS_CONFID_IND,'Y','YES',NULL) PEVEMPL_CONFID_IND, SPBPERS_VETC_FILE_NUMBER, SPBPERS_PREF_FIRST_NAME, SPBPERS_NAME_PREFIX, SPBPERS_NAME_SUFFIX, SPBPERS_VERA_IND, DECODE(SPBPERS_VERA_IND, 'P','Pre-Vietnam', 'D','During Vietnam', 'X','Post Vietnam') PEVEMPL_VERA_IND, SPBPERS_CITZ_CODE, STVCITZ_DESC, SPBPERS_CITY_BIRTH, SPBPERS_DRIVER_LICENSE, SPBPERS_STAT_CODE_DRIVER, STVSTAT_DESC, SPBPERS_NATN_CODE_DRIVER, STVNATN_NATION, SPBPERS_SDVET_IND, DECODE(SPBPERS_SDVET_IND,'Y','YES',NULL) PEVEMPL_SDVET_IND, PEBEMPL_1042_RECIPIENT_CD FROM SPRIDEN, SPBPERS, PEBEMPL, PTRBCAT, PTRECLS, PTRTREA, PTRLREA, PTRWKPR, PTVLCAT, PTVSTGR, STVLGCY, STVETHN, STVMRTL, STVCITZ, STVSTAT, STVNATN WHERE PTRBCAT_CODE (+) = PEBEMPL_BCAT_CODE AND PTRECLS_CODE (+) = PEBEMPL_ECLS_CODE AND PTRTREA_CODE (+) = PEBEMPL_TREA_CODE AND PTRLREA_CODE (+) = PEBEMPL_LREA_CODE AND PTRWKPR_CODE (+) = PEBEMPL_WKPR_CODE AND PTVLCAT_CODE (+) = PEBEMPL_LCAT_CODE AND PTVSTGR_CODE (+) = PEBEMPL_STGR_CODE AND STVLGCY_CODE (+) = SPBPERS_LGCY_CODE AND STVETHN_CODE (+) = SPBPERS_ETHN_CODE AND STVMRTL_CODE (+) = SPBPERS_MRTL_CODE AND STVCITZ_CODE (+) = SPBPERS_CITZ_CODE AND STVSTAT_CODE (+) = SPBPERS_STAT_CODE_DRIVER AND STVNATN_CODE (+) = SPBPERS_NATN_CODE_DRIVER AND PEBEMPL_PIDM = SPRIDEN_PIDM AND SPRIDEN_CHANGE_IND IS NULL AND SPBPERS_PIDM = PEBEMPL_PIDM AND NVL(SPBPERS_DEAD_IND,'N') = 'N' AND PEBEMPL_EMPL_STATUS <> 'T' ; --DESC PEVJOBS_WITH_SOURCE_FIELDS_V -- PEVJOBS_WITH_SOURCE_FIELDS_v_create.sql Current Records VIEW from TABLE PEVJOBS_WITH_SOURCE_FIELDS CREATE OR REPLACE VIEW PEVJOBS_WITH_SOURCE_FIELDS_V ( NBRBJOB_PIDM, NBRBJOB_POSN, NBRBJOB_SUFF, NBRBJOB_BEGIN_DATE, NBRBJOB_END_DATE, NBRBJOB_DEFER_BAL, NBRBJOB_CONTRACT_TYPE, PEVJOBS_CONTRACT_TYPE, NBRBJOB_SALARY_ENCUMBRANCE, NBRBJOB_CONTRACT_BEGIN_DATE, NBRBJOB_CONTRACT_END_DATE, NBRBJOB_STEP_INCR_MON, NBRBJOB_STEP_INCR_DAY, NBRBJOB_COAS_CODE, NBRBJOB_ACCRUE_LEAVE_IND, PEVJOBS_ACCRUE_LEAVE_IND, NBRBJOB_CIVIL_SERVICE_IND, PEVJOBS_CIVIL_SERVICE_IND, NBRBJOB_FRINGE_ENCUMBRANCE, NBRJOBS_EFFECTIVE_DATE, NBRJOBS_STATUS, PEVJOBS_STATUS_DESC, NBRJOBS_DESC, NBRJOBS_ECLS_CODE, PTRECLS_LONG_DESC, NBRJOBS_PICT_CODE, PTRPICT_DESC, NBRJOBS_COAS_CODE_TS, NBRJOBS_ORGN_CODE_TS, NBRJOBS_SAL_TABLE, NBRJOBS_SAL_GRADE, NBRJOBS_SAL_STEP, NBRJOBS_APPT_PCT, NBRJOBS_FTE, NBRJOBS_HRS_DAY, NBRJOBS_HRS_PAY, NBRJOBS_SHIFT, NBRJOBS_REG_RATE, NBRJOBS_ASSGN_SALARY, NBRJOBS_FACTOR, NBRJOBS_ANN_SALARY, NBRJOBS_PER_PAY_SALARY, NBRJOBS_PAYS, NBRJOBS_PER_PAY_DEFER_AMT, NBRJOBS_JCRE_CODE, PTRJCRE_DESC, NBRJOBS_SGRP_CODE, NBRJOBS_EMPR_CODE, PTREMPR_DESC, NBRJOBS_LGCD_CODE, PTVLGCD_DESC, NBRJOBS_LOCN_CODE, PTRLOCN_DESC, NBRJOBS_SCHL_CODE, PTRSCHL_DESC, NBRJOBS_SUPERVISOR_PIDM, NBRJOBS_SUPERVISOR_POSN, NBBPOSN_TITLE, NBRJOBS_SUPERVISOR_SUFF, NBRJOBS_WKCP_CODE, PTVWKCP_DESC, NBRJOBS_JBLN_CODE, PTRJBLN_DESC, NBRJOBS_PERS_CHG_DATE, NBRJOBS_PCAT_CODE, PTVPCAT_DESC, NBRJOBS_DFPR_CODE, PTRDFPR_DESC ) AS SELECT -- PEVJOBS Employee Jobs Information View with Source Fields NBRBJOB_PIDM, NBRBJOB_POSN, NBRBJOB_SUFF, NBRBJOB_BEGIN_DATE, NBRBJOB_END_DATE, NBRBJOB_DEFER_BAL, NBRBJOB_CONTRACT_TYPE, DECODE(NBRBJOB_CONTRACT_TYPE, 'P','Primary', 'S','Secondary', 'O','Overload', NULL) PEVJOBS_CONTRACT_TYPE, NBRBJOB_SALARY_ENCUMBRANCE, NBRBJOB_CONTRACT_BEGIN_DATE, NBRBJOB_CONTRACT_END_DATE, NBRBJOB_STEP_INCR_MON, NBRBJOB_STEP_INCR_DAY, NBRBJOB_COAS_CODE, NBRBJOB_ACCRUE_LEAVE_IND, DECODE(NBRBJOB_ACCRUE_LEAVE_IND,'Y','Yes','N','No',NULL) PEVJOBS_ACCRUE_LEAVE_IND, NBRBJOB_CIVIL_SERVICE_IND, DECODE(NBRBJOB_CIVIL_SERVICE_IND,'Y','Yes','N','No',NULL) PEVJOBS_CIVIL_SERVICE_IND, NBRBJOB_FRINGE_ENCUMBRANCE, NBRJOBS_EFFECTIVE_DATE, NBRJOBS_STATUS, DECODE(NBRJOBS_STATUS,'A','Active', 'B','Benefits', 'F','Full Pay LV', 'L','Leave', 'P','Part Pay LV', 'T','Terminated', NULL) PEVJOBS_STATUS_DESC, NBRJOBS_DESC, NBRJOBS_ECLS_CODE, PTRECLS_LONG_DESC, NBRJOBS_PICT_CODE, PTRPICT_DESC, NBRJOBS_COAS_CODE_TS, NBRJOBS_ORGN_CODE_TS, NBRJOBS_SAL_TABLE, NBRJOBS_SAL_GRADE, NBRJOBS_SAL_STEP, NBRJOBS_APPT_PCT, NBRJOBS_FTE, NBRJOBS_HRS_DAY, NBRJOBS_HRS_PAY, NBRJOBS_SHIFT, NBRJOBS_REG_RATE, NBRJOBS_ASSGN_SALARY, NBRJOBS_FACTOR, NBRJOBS_ANN_SALARY, NBRJOBS_PER_PAY_SALARY, NBRJOBS_PAYS, NBRJOBS_PER_PAY_DEFER_AMT, NBRJOBS_JCRE_CODE, PTRJCRE_DESC, NBRJOBS_SGRP_CODE, NBRJOBS_EMPR_CODE, PTREMPR_DESC, NBRJOBS_LGCD_CODE, PTVLGCD_DESC, NBRJOBS_LOCN_CODE, PTRLOCN_DESC, NBRJOBS_SCHL_CODE, PTRSCHL_DESC, NBRJOBS_SUPERVISOR_PIDM, NBRJOBS_SUPERVISOR_POSN, NBBPOSN_TITLE, -- PEVJOBS_SUPERVISOR_POSN_DESC NBRJOBS_SUPERVISOR_SUFF, NBRJOBS_WKCP_CODE, PTVWKCP_DESC, NBRJOBS_JBLN_CODE, PTRJBLN_DESC, NBRJOBS_PERS_CHG_DATE, NBRJOBS_PCAT_CODE, PTVPCAT_DESC, NBRJOBS_DFPR_CODE, PTRDFPR_DESC FROM NBBPOSN, PTRECLS, PTRPICT, PTRDFPR, PTREMPR, PTRLOCN, PTRSCHL, PTRJBLN, PTRJCRE, PTVLGCD, PTVWKCP, PTVPCAT, NBRBJOB, NBRJOBS A WHERE NBBPOSN_POSN (+) = NBRJOBS_SUPERVISOR_POSN AND PTRECLS_CODE (+) = NBRJOBS_ECLS_CODE AND PTRPICT_CODE (+) = NBRJOBS_PICT_CODE AND PTRDFPR_CODE (+) = NBRJOBS_DFPR_CODE AND PTRJCRE_CODE (+) = NBRJOBS_JCRE_CODE AND PTREMPR_CODE (+) = NBRJOBS_EMPR_CODE AND PTVLGCD_CODE (+) = NBRJOBS_LGCD_CODE AND PTRLOCN_CODE (+) = NBRJOBS_LOCN_CODE AND PTRSCHL_CODE (+) = NBRJOBS_SCHL_CODE AND PTVWKCP_CODE (+) = NBRJOBS_WKCP_CODE AND PTRJBLN_CODE (+) = NBRJOBS_JBLN_CODE AND PTVPCAT_CODE (+) = NBRJOBS_PCAT_CODE AND NBRBJOB_PIDM = NBRJOBS_PIDM AND NBRBJOB_POSN = NBRJOBS_POSN AND NBRBJOB_SUFF = NBRJOBS_SUFF AND NBRJOBS_EFFECTIVE_DATE = (SELECT MAX(B.NBRJOBS_EFFECTIVE_DATE) FROM POSNCTL.NBRJOBS B WHERE B.NBRJOBS_PIDM = A.NBRJOBS_PIDM AND B.NBRJOBS_POSN = A.NBRJOBS_POSN AND B.NBRJOBS_SUFF = A.NBRJOBS_SUFF AND B.NBRJOBS_EFFECTIVE_DATE <= SYSDATE) ; --DESC PRRCCMT_WITH_PTVCMTY_V -- PRRCCMT_WITH_PTVCMTY_v_create.sql Current Records VIEW from TABLE PRRCCMT_WITH_PTVCMTY CREATE OR REPLACE VIEW PRRCCMT_WITH_PTVCMTY_V ( PPRCCMT_PIDM, PPRCCMT_CMTY_CODE, PTVCMTY_DESC, PPRCCMT_TEXT, PTVCMTY_CODE, PPRCCMT_ACTIVITY_DATE, PTVCMTY_ACTIVITY_DATE ) AS SELECT -- Flattened PPRCCMT Person Coded Comment Repeating Table -- with PTVCMTY Comment Type Validation Table PPRCCMT_PIDM, PPRCCMT_CMTY_CODE, PTVCMTY_DESC, PPRCCMT_TEXT, PTVCMTY_CODE, TRUNC(PPRCCMT_ACTIVITY_DATE) PPRCCMT_ACTIVITY_DATE, TRUNC(PTVCMTY_ACTIVITY_DATE) PTVCMTY_ACTIVITY_DATE FROM PPRCCMT, PTVCMTY WHERE PPRCCMT_CMTY_CODE = PTVCMTY_CODE ; --DESC PTRBDPL_V -- PTRBDPL_v_create.sql Current Records VIEW from TABLE PTRBDPL CREATE OR REPLACE VIEW PTRBDPL_V ( PTRBDPL_BDCA_CODE, PTRBDPL_CODE, PTRBDPL_DESC, PTRBDPL_AMT1, PTRBDPL_AMT2, PTRBDPL_AMT3, PTRBDPL_AMT4, PTRBDPL_ACTIVITY_DATE, PTRBDPL_EFFECTIVE_DATE ) AS SELECT -- Created from TABLE PTRBDPL Benefit/Deduction Valid Plan Rule Table PTRBDPL_BDCA_CODE, PTRBDPL_CODE, PTRBDPL_DESC, PTRBDPL_AMT1, PTRBDPL_AMT2, PTRBDPL_AMT3, PTRBDPL_AMT4, TRUNC(PTRBDPL_ACTIVITY_DATE) PTRBDPL_ACTIVITY_DATE, TRUNC(PTRBDPL_EFFECTIVE_DATE) PTRBDPL_EFFECTIVE_DATE FROM PTRBDPL Y WHERE Y.ptrbdpl_effective_date = (select max(X.ptrbdpl_effective_date) from ptrbdpl X where X.ptrbdpl_effective_date <= SYSDATE and X.ptrbdpl_bdca_code = Y.ptrbdpl_bdca_code) ; --DESC SORDEGR_WITH_STVDEGC_V -- SORDEGR_WITH_STVDEGC_v_create.sql Current Records VIEW from TABLE SORDEGR_WITH_STVDEGC CREATE OR REPLACE VIEW SORDEGR_WITH_STVDEGC_V ( SORDEGR_PIDM, SORDEGR_SBGI_CODE, SORDEGR_DEGC_CODE, STVDEGC_DESC, SORDEGR_DEGR_SEQ_NO, SORDEGR_ATTEND_FROM, SORDEGR_ATTEND_TO, SORDEGR_HOURS_TRANSFERRED, SORDEGR_GPA_TRANSFERRED, SORDEGR_DEGC_DATE, SORDEGR_DEGC_YEAR, SORDEGR_COLL_CODE, SORDEGR_HONR_CODE, SORDEGR_ACTIVITY_DATE, SORDEGR_TERM_DEGREE, STVDEGC_CODE, STVDEGC_LEVEL_ONE, STVDEGC_LEVEL_TWO, STVDEGC_LEVEL_THREE, STVDEGC_FA_COUNT_IND, STVDEGC_ACTIVITY_DATE, STVDEGC_ACAT_CODE, STVDEGC_SYSTEM_REQ_IND, STVDEGC_DLEV_CODE, STVDEGC_VR_MSG_NO, STVDEGC_DISP_WEB_IND ) AS SELECT -- Flattened SORDEGR Prior College Degree Table with STVDEGC Degree Code Validation Table SORDEGR_PIDM, SORDEGR_SBGI_CODE, SORDEGR_DEGC_CODE, STVDEGC_DESC, SORDEGR_DEGR_SEQ_NO, TRUNC(SORDEGR_ATTEND_FROM) SORDEGR_ATTEND_FROM, TRUNC(SORDEGR_ATTEND_TO) SORDEGR_ATTEND_TO, SORDEGR_HOURS_TRANSFERRED, SORDEGR_GPA_TRANSFERRED, TRUNC(SORDEGR_DEGC_DATE) SORDEGR_DEGC_DATE, SORDEGR_DEGC_YEAR, SORDEGR_COLL_CODE, SORDEGR_HONR_CODE, TRUNC(SORDEGR_ACTIVITY_DATE) SORDEGR_ACTIVITY_DATE, SORDEGR_TERM_DEGREE, STVDEGC_CODE, STVDEGC_LEVEL_ONE, STVDEGC_LEVEL_TWO, STVDEGC_LEVEL_THREE, STVDEGC_FA_COUNT_IND, TRUNC(STVDEGC_ACTIVITY_DATE) STVDEGC_ACTIVITY_DATE, STVDEGC_ACAT_CODE, STVDEGC_SYSTEM_REQ_IND, STVDEGC_DLEV_CODE, STVDEGC_VR_MSG_NO, STVDEGC_DISP_WEB_IND FROM SORDEGR, STVDEGC WHERE SORDEGR_DEGC_CODE = STVDEGC_CODE ; --DESC SPBPERS_V -- SPBPERS_v_create.sql Current Records VIEW from TABLE SPBPERS CREATE OR REPLACE VIEW SPBPERS_V ( SPBPERS_PIDM, SPBPERS_SSN, SPBPERS_BIRTH_DATE, SPBPERS_LGCY_CODE, SPBPERS_ETHN_CODE, SPBPERS_MRTL_CODE, SPBPERS_RELG_CODE, SPBPERS_SEX, SPBPERS_CONFID_IND, SPBPERS_DEAD_IND, SPBPERS_VETC_FILE_NUMBER, SPBPERS_LEGAL_NAME, SPBPERS_PREF_FIRST_NAME, SPBPERS_NAME_PREFIX, SPBPERS_NAME_SUFFIX, SPBPERS_ACTIVITY_DATE, SPBPERS_VERA_IND, SPBPERS_CITZ_IND, SPBPERS_DEAD_DATE, SPBPERS_PIN, SPBPERS_CITZ_CODE, SPBPERS_HAIR_CODE, SPBPERS_EYES_CODE, SPBPERS_CITY_BIRTH, SPBPERS_STAT_CODE_BIRTH, SPBPERS_DRIVER_LICENSE, SPBPERS_STAT_CODE_DRIVER, SPBPERS_NATN_CODE_DRIVER, SPBPERS_UOMS_CODE_HEIGHT, SPBPERS_HEIGHT, SPBPERS_UOMS_CODE_WEIGHT, SPBPERS_WEIGHT, SPBPERS_SDVET_IND, SPBPERS_LICENSE_ISSUED_DATE, SPBPERS_LICENSE_EXPIRES_DATE, SPBPERS_INCAR_IND, SPBPERS_WEBID, SPBPERS_WEB_LAST_ACCESS, SPBPERS_PIN_DISABLED_IND, SPBPERS_ITIN ) AS SELECT -- SPBPERS Basic Person Base Table SPBPERS_PIDM, SPBPERS_SSN, TRUNC(SPBPERS_BIRTH_DATE) SPBPERS_BIRTH_DATE, SPBPERS_LGCY_CODE, SPBPERS_ETHN_CODE, SPBPERS_MRTL_CODE, SPBPERS_RELG_CODE, SPBPERS_SEX, SPBPERS_CONFID_IND, SPBPERS_DEAD_IND, SPBPERS_VETC_FILE_NUMBER, SPBPERS_LEGAL_NAME, SPBPERS_PREF_FIRST_NAME, SPBPERS_NAME_PREFIX, SPBPERS_NAME_SUFFIX, TRUNC(SPBPERS_ACTIVITY_DATE) SPBPERS_ACTIVITY_DATE, SPBPERS_VERA_IND, SPBPERS_CITZ_IND, TRUNC(SPBPERS_DEAD_DATE) SPBPERS_DEAD_DATE, SPBPERS_PIN, SPBPERS_CITZ_CODE, SPBPERS_HAIR_CODE, SPBPERS_EYES_CODE, SPBPERS_CITY_BIRTH, SPBPERS_STAT_CODE_BIRTH, SPBPERS_DRIVER_LICENSE, SPBPERS_STAT_CODE_DRIVER, SPBPERS_NATN_CODE_DRIVER, SPBPERS_UOMS_CODE_HEIGHT, SPBPERS_HEIGHT, SPBPERS_UOMS_CODE_WEIGHT, SPBPERS_WEIGHT, SPBPERS_SDVET_IND, TRUNC(SPBPERS_LICENSE_ISSUED_DATE) SPBPERS_LICENSE_ISSUED_DATE, TRUNC(SPBPERS_LICENSE_EXPIRES_DATE) SPBPERS_LICENSE_EXPIRES_DATE, SPBPERS_INCAR_IND, SPBPERS_WEBID, SPBPERS_WEB_LAST_ACCESS, SPBPERS_PIN_DISABLED_IND, SPBPERS_ITIN FROM SPBPERS ; --DESC SPRADDR_CHECK_MAILING_V -- SPRADDR_CHECK_MAILING_V_create.sql Current Records VIEW from TABLE SPRADDR CREATE OR REPLACE VIEW SPRADDR_CHECK_MAILING_V ( SPRADDR_PIDM, SPRADDR_ATYP_CODE, SPRADDR_SEQNO, SPRADDR_FROM_DATE, SPRADDR_TO_DATE, SPRADDR_STREET_LINE1, SPRADDR_STREET_LINE2, SPRADDR_STREET_LINE3, SPRADDR_CITY, SPRADDR_STAT_CODE, SPRADDR_ZIP, SPRADDR_CNTY_CODE, SPRADDR_NATN_CODE, SPRADDR_PHONE_AREA, SPRADDR_PHONE_NUMBER, SPRADDR_PHONE_EXT, SPRADDR_STATUS_IND, SPRADDR_ACTIVITY_DATE, SPRADDR_USER, SPRADDR_ASRC_CODE, SPRADDR_DELIVERY_POINT, SPRADDR_CORRECTION_DIGIT, SPRADDR_CARRIER_ROUTE, SPRADDR_GST_TAX_ID, SPRADDR_REVIEWED_IND, SPRADDR_REVIEWED_USER ) AS SELECT SPRADDR_PIDM, SPRADDR_ATYP_CODE, SPRADDR_SEQNO, TRUNC(SPRADDR_FROM_DATE) SPRADDR_FROM_DATE, TRUNC(SPRADDR_TO_DATE) SPRADDR_TO_DATE, SPRADDR_STREET_LINE1, SPRADDR_STREET_LINE2, SPRADDR_STREET_LINE3, SPRADDR_CITY, SPRADDR_STAT_CODE, SPRADDR_ZIP, SPRADDR_CNTY_CODE, SPRADDR_NATN_CODE, SPRADDR_PHONE_AREA, SPRADDR_PHONE_NUMBER, SPRADDR_PHONE_EXT, SPRADDR_STATUS_IND, TRUNC(SPRADDR_ACTIVITY_DATE) SPRADDR_ACTIVITY_DATE, SPRADDR_USER, SPRADDR_ASRC_CODE, SPRADDR_DELIVERY_POINT, SPRADDR_CORRECTION_DIGIT, SPRADDR_CARRIER_ROUTE, SPRADDR_GST_TAX_ID, SPRADDR_REVIEWED_IND, SPRADDR_REVIEWED_USER FROM SPRADDR Y WHERE ((Y.SPRADDR_STATUS_IND IS NULL) OR (Y.SPRADDR_STATUS_IND = 'A')) AND Y.SPRADDR_ATYP_CODE = 'CM' AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) AND (TRUNC(Y.SPRADDR_FROM_DATE) <= SYSDATE Or Y.SPRADDR_FROM_DATE Is Null) AND Y.SPRADDR_SEQNO =(SELECT MIN(X.SPRADDR_SEQNO) FROM SPRADDR X WHERE X.SPRADDR_PIDM = Y.SPRADDR_PIDM AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) AND X.SPRADDR_ATYP_CODE = Y.SPRADDR_ATYP_CODE AND ((X.SPRADDR_STATUS_IND IS NULL) OR (X.SPRADDR_STATUS_IND = 'A')) AND (TRUNC(X.SPRADDR_FROM_DATE)<= SYSDATE Or X.SPRADDR_FROM_DATE Is Null)) ; --DESC SPRADDR_HOME_V -- SPRADDR_HOME_V_create.sql Current Records VIEW from TABLE SPRADDR CREATE OR REPLACE VIEW SPRADDR_HOME_V ( SPRADDR_PIDM, SPRADDR_ATYP_CODE, SPRADDR_SEQNO, SPRADDR_FROM_DATE, SPRADDR_TO_DATE, SPRADDR_STREET_LINE1, SPRADDR_STREET_LINE2, SPRADDR_STREET_LINE3, SPRADDR_CITY, SPRADDR_STAT_CODE, SPRADDR_ZIP, SPRADDR_CNTY_CODE, SPRADDR_NATN_CODE, SPRADDR_PHONE_AREA, SPRADDR_PHONE_NUMBER, SPRADDR_PHONE_EXT, SPRADDR_STATUS_IND, SPRADDR_ACTIVITY_DATE, SPRADDR_USER, SPRADDR_ASRC_CODE, SPRADDR_DELIVERY_POINT, SPRADDR_CORRECTION_DIGIT, SPRADDR_CARRIER_ROUTE, SPRADDR_GST_TAX_ID, SPRADDR_REVIEWED_IND, SPRADDR_REVIEWED_USER ) AS SELECT SPRADDR_PIDM, SPRADDR_ATYP_CODE, SPRADDR_SEQNO, TRUNC(SPRADDR_FROM_DATE) SPRADDR_FROM_DATE, TRUNC(SPRADDR_TO_DATE) SPRADDR_TO_DATE, SPRADDR_STREET_LINE1, SPRADDR_STREET_LINE2, SPRADDR_STREET_LINE3, SPRADDR_CITY, SPRADDR_STAT_CODE, SPRADDR_ZIP, SPRADDR_CNTY_CODE, SPRADDR_NATN_CODE, SPRADDR_PHONE_AREA, SPRADDR_PHONE_NUMBER, SPRADDR_PHONE_EXT, SPRADDR_STATUS_IND, TRUNC(SPRADDR_ACTIVITY_DATE) SPRADDR_ACTIVITY_DATE, SPRADDR_USER, SPRADDR_ASRC_CODE, SPRADDR_DELIVERY_POINT, SPRADDR_CORRECTION_DIGIT, SPRADDR_CARRIER_ROUTE, SPRADDR_GST_TAX_ID, SPRADDR_REVIEWED_IND, SPRADDR_REVIEWED_USER FROM SPRADDR Y WHERE ((Y.SPRADDR_STATUS_IND IS NULL) OR (Y.SPRADDR_STATUS_IND = 'A')) AND Y.SPRADDR_ATYP_CODE = 'RE' AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) AND (TRUNC(Y.SPRADDR_FROM_DATE) <= SYSDATE Or Y.SPRADDR_FROM_DATE Is Null) AND Y.SPRADDR_SEQNO =(SELECT MIN(X.SPRADDR_SEQNO) FROM SPRADDR X WHERE X.SPRADDR_PIDM = Y.SPRADDR_PIDM AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) AND X.SPRADDR_ATYP_CODE = Y.SPRADDR_ATYP_CODE AND ((X.SPRADDR_STATUS_IND IS NULL) OR (X.SPRADDR_STATUS_IND = 'A')) AND (TRUNC(X.SPRADDR_FROM_DATE)<= SYSDATE Or X.SPRADDR_FROM_DATE Is Null)) ; --DESC SPRADDR_V -- SPRADDR_v_create.sql Current Records VIEW from TABLE SPRADDR CREATE OR REPLACE VIEW SPRADDR_V ( SPRADDR_PIDM, SPRADDR_ATYP_CODE, SPRADDR_SEQNO, SPRADDR_FROM_DATE, SPRADDR_TO_DATE, SPRADDR_STREET_LINE1, SPRADDR_STREET_LINE2, SPRADDR_STREET_LINE3, SPRADDR_CITY, SPRADDR_STAT_CODE, SPRADDR_ZIP, SPRADDR_CNTY_CODE, SPRADDR_NATN_CODE, SPRADDR_PHONE_AREA, SPRADDR_PHONE_NUMBER, SPRADDR_PHONE_EXT, SPRADDR_STATUS_IND, SPRADDR_ACTIVITY_DATE, SPRADDR_USER, SPRADDR_ASRC_CODE, SPRADDR_DELIVERY_POINT, SPRADDR_CORRECTION_DIGIT, SPRADDR_CARRIER_ROUTE, SPRADDR_GST_TAX_ID, SPRADDR_REVIEWED_IND, SPRADDR_REVIEWED_USER ) AS SELECT SPRADDR_PIDM, SPRADDR_ATYP_CODE, SPRADDR_SEQNO, TRUNC(SPRADDR_FROM_DATE) SPRADDR_FROM_DATE, TRUNC(SPRADDR_TO_DATE) SPRADDR_TO_DATE, SPRADDR_STREET_LINE1, SPRADDR_STREET_LINE2, SPRADDR_STREET_LINE3, SPRADDR_CITY, SPRADDR_STAT_CODE, SPRADDR_ZIP, SPRADDR_CNTY_CODE, SPRADDR_NATN_CODE, SPRADDR_PHONE_AREA, SPRADDR_PHONE_NUMBER, SPRADDR_PHONE_EXT, SPRADDR_STATUS_IND, TRUNC(SPRADDR_ACTIVITY_DATE) SPRADDR_ACTIVITY_DATE, SPRADDR_USER, SPRADDR_ASRC_CODE, SPRADDR_DELIVERY_POINT, SPRADDR_CORRECTION_DIGIT, SPRADDR_CARRIER_ROUTE, SPRADDR_GST_TAX_ID, SPRADDR_REVIEWED_IND, SPRADDR_REVIEWED_USER FROM SPRADDR Y WHERE ((Y.SPRADDR_STATUS_IND IS NULL) OR (Y.SPRADDR_STATUS_IND = 'A')) AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) AND (TRUNC(Y.SPRADDR_FROM_DATE) <= SYSDATE Or Y.SPRADDR_FROM_DATE Is Null) AND Y.SPRADDR_SEQNO =(SELECT MIN(X.SPRADDR_SEQNO) FROM SPRADDR X WHERE X.SPRADDR_PIDM = Y.SPRADDR_PIDM AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) AND X.SPRADDR_ATYP_CODE = Y.SPRADDR_ATYP_CODE AND ((X.SPRADDR_STATUS_IND IS NULL) OR (X.SPRADDR_STATUS_IND = 'A')) AND (TRUNC(X.SPRADDR_FROM_DATE)<= SYSDATE Or X.SPRADDR_FROM_DATE Is Null)) ; --DESC SPRADDR_WORK_V -- SPRADDR_WORK_V_create.sql Current Records VIEW from TABLE SPRADDR CREATE OR REPLACE VIEW SPRADDR_WORK_V ( SPRADDR_PIDM, SPRADDR_ATYP_CODE, SPRADDR_SEQNO, SPRADDR_FROM_DATE, SPRADDR_TO_DATE, SPRADDR_STREET_LINE1, SPRADDR_STREET_LINE2, SPRADDR_STREET_LINE3, SPRADDR_CITY, SPRADDR_STAT_CODE, SPRADDR_ZIP, SPRADDR_CNTY_CODE, SPRADDR_NATN_CODE, SPRADDR_PHONE_AREA, SPRADDR_PHONE_NUMBER, SPRADDR_PHONE_EXT, SPRADDR_STATUS_IND, SPRADDR_ACTIVITY_DATE, SPRADDR_USER, SPRADDR_ASRC_CODE, SPRADDR_DELIVERY_POINT, SPRADDR_CORRECTION_DIGIT, SPRADDR_CARRIER_ROUTE, SPRADDR_GST_TAX_ID, SPRADDR_REVIEWED_IND, SPRADDR_REVIEWED_USER ) AS SELECT SPRADDR_PIDM, SPRADDR_ATYP_CODE, SPRADDR_SEQNO, TRUNC(SPRADDR_FROM_DATE) SPRADDR_FROM_DATE, TRUNC(SPRADDR_TO_DATE) SPRADDR_TO_DATE, SPRADDR_STREET_LINE1, SPRADDR_STREET_LINE2, SPRADDR_STREET_LINE3, SPRADDR_CITY, SPRADDR_STAT_CODE, SPRADDR_ZIP, SPRADDR_CNTY_CODE, SPRADDR_NATN_CODE, SPRADDR_PHONE_AREA, SPRADDR_PHONE_NUMBER, SPRADDR_PHONE_EXT, SPRADDR_STATUS_IND, TRUNC(SPRADDR_ACTIVITY_DATE) SPRADDR_ACTIVITY_DATE, SPRADDR_USER, SPRADDR_ASRC_CODE, SPRADDR_DELIVERY_POINT, SPRADDR_CORRECTION_DIGIT, SPRADDR_CARRIER_ROUTE, SPRADDR_GST_TAX_ID, SPRADDR_REVIEWED_IND, SPRADDR_REVIEWED_USER FROM SPRADDR Y WHERE ((Y.SPRADDR_STATUS_IND IS NULL) OR (Y.SPRADDR_STATUS_IND = 'A')) AND Y.SPRADDR_ATYP_CODE = 'WK' AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) AND (TRUNC(Y.SPRADDR_FROM_DATE) <= SYSDATE Or Y.SPRADDR_FROM_DATE Is Null) AND Y.SPRADDR_SEQNO =(SELECT MIN(X.SPRADDR_SEQNO) FROM SPRADDR X WHERE X.SPRADDR_PIDM = Y.SPRADDR_PIDM AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) AND X.SPRADDR_ATYP_CODE = Y.SPRADDR_ATYP_CODE AND ((X.SPRADDR_STATUS_IND IS NULL) OR (X.SPRADDR_STATUS_IND = 'A')) AND (TRUNC(X.SPRADDR_FROM_DATE)<= SYSDATE Or X.SPRADDR_FROM_DATE Is Null)) ; --DESC SPRIDEN_OTHER_V -- SPRIDEN_OTHER_v_create.sql Current Records VIEW from TABLE SPRIDEN_OTHER CREATE OR REPLACE VIEW SPRIDEN_OTHER_V ( PIDM, ID, Organization ) AS SELECT SPRIDEN_PIDM, SPRIDEN_ID, SPRIDEN_LAST_NAME FROM SPRIDEN WHERE SPRIDEN_CHANGE_IND IS NULL AND SPRIDEN_ENTITY_IND != 'P' AND SPRIDEN_ID NOT LIKE '@%' ; --DESC SPRIDEN_PEOPLE_V -- SPRIDEN_PEOPLE_v_create.sql Current Records VIEW from TABLE SPRIDEN_PEOPLE CREATE OR REPLACE VIEW SPRIDEN_PEOPLE_V ( SPRIDEN_PIDM, SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME, SPRIDEN_MI, SPRIDEN_CHANGE_IND, SPRIDEN_ENTITY_IND, SPRIDEN_ACTIVITY_DATE, SPRIDEN_USER, SPRIDEN_ORIGIN, SPRIDEN_SEARCH_LAST_NAME, SPRIDEN_SEARCH_FIRST_NAME, SPRIDEN_SEARCH_MI, SPRIDEN_SOUNDEX_LAST_NAME, SPRIDEN_SOUNDEX_FIRST_NAME, SPRIDEN_NTYP_CODE ) AS SELECT SPRIDEN_PIDM, SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME, SPRIDEN_MI, SPRIDEN_CHANGE_IND, SPRIDEN_ENTITY_IND, TRUNC(SPRIDEN_ACTIVITY_DATE) SPRIDEN_ACTIVITY_DATE, SPRIDEN_USER, SPRIDEN_ORIGIN, SPRIDEN_SEARCH_LAST_NAME, SPRIDEN_SEARCH_FIRST_NAME, SPRIDEN_SEARCH_MI, SPRIDEN_SOUNDEX_LAST_NAME, SPRIDEN_SOUNDEX_FIRST_NAME, SPRIDEN_NTYP_CODE FROM SPRIDEN WHERE SPRIDEN_CHANGE_IND IS NULL AND SPRIDEN_ENTITY_IND = 'P' ; --DESC SPRIDEN_V -- spriden_v_create.sql Current Records VIEW from TABLE SPRIDEN CREATE OR REPLACE VIEW SPRIDEN_V ( SPRIDEN_PIDM, SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME, SPRIDEN_MI, SPRIDEN_CHANGE_IND, SPRIDEN_ENTITY_IND, SPRIDEN_ACTIVITY_DATE, SPRIDEN_USER, SPRIDEN_ORIGIN, SPRIDEN_SEARCH_LAST_NAME, SPRIDEN_SEARCH_FIRST_NAME, SPRIDEN_SEARCH_MI, SPRIDEN_SOUNDEX_LAST_NAME, SPRIDEN_SOUNDEX_FIRST_NAME, SPRIDEN_NTYP_CODE, SPRIDEN_CREATE_USER, SPRIDEN_CREATE_DATE, SPRIDEN_DATA_ORIGIN, SPRIDEN_CREATE_FDMN_CODE ) AS SELECT SPRIDEN_PIDM, SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME, SPRIDEN_MI, SPRIDEN_CHANGE_IND, SPRIDEN_ENTITY_IND, TRUNC(SPRIDEN_ACTIVITY_DATE), SPRIDEN_USER, SPRIDEN_ORIGIN, SPRIDEN_SEARCH_LAST_NAME, SPRIDEN_SEARCH_FIRST_NAME, SPRIDEN_SEARCH_MI, SPRIDEN_SOUNDEX_LAST_NAME, SPRIDEN_SOUNDEX_FIRST_NAME, SPRIDEN_NTYP_CODE, SPRIDEN_CREATE_USER, TRUNC(SPRIDEN_CREATE_DATE), SPRIDEN_DATA_ORIGIN, SPRIDEN_CREATE_FDMN_CODE FROM SPRIDEN WHERE SPRIDEN_CHANGE_IND IS NULL ; --DESC SPRIDEN_VENDORS_V -- SPRIDEN_VENDORS_V_create.sql Current Records VIEW from TABLE SPRIDEN_VENDORS_V CREATE OR REPLACE VIEW SPRIDEN_VENDORS_V ( PIDM, ID, Vendor ) AS SELECT SPRIDEN_PIDM PIDM, SPRIDEN_ID ID, spriden_last_name Vendor FROM SPRIDEN WHERE SPRIDEN_CHANGE_IND IS NULL AND SPRIDEN_ENTITY_IND != 'P' AND SPRIDEN_ID LIKE '@%' ; --DESC SPRTELE_CELL_V -- SPRTELE_CELL_v_create.sql Current Records VIEW from TABLE SPRTELE CREATE OR REPLACE VIEW SPRTELE_CELL_V ( SPRTELE_PIDM, SPRTELE_SEQNO, SPRTELE_TELE_CODE, SPRTELE_ACTIVITY_DATE, CELL, SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER, SPRTELE_PHONE_EXT, SPRTELE_STATUS_IND, SPRTELE_ATYP_CODE, SPRTELE_ADDR_SEQNO, SPRTELE_PRIMARY_IND, SPRTELE_UNLIST_IND, SPRTELE_COMMENT, SPRTELE_INTL_ACCESS ) AS SELECT -- Created from TABLE SPRTELE Telephone Table - Work SPRTELE_PIDM, SPRTELE_SEQNO, SPRTELE_TELE_CODE, TRUNC(SPRTELE_ACTIVITY_DATE) SPRTELE_ACTIVITY_DATE, DECODE(SPRTELE_UNLIST_IND, -- Formatted area-3-4-ext except Unlisted 'Y','Unlisted', (SPRTELE_PHONE_AREA||DECODE(SPRTELE_PHONE_AREA,NULL,NULL,'-')|| SUBSTR(SPRTELE_PHONE_NUMBER,1,3)||DECODE(SPRTELE_PHONE_NUMBER,NULL,NULL,'-')|| SUBSTR(SPRTELE_PHONE_NUMBER,4,7)|| DECODE(SPRTELE_PHONE_EXT,NULL,NULL,'-')|| SPRTELE_PHONE_EXT)), SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER, SPRTELE_PHONE_EXT, SPRTELE_STATUS_IND, SPRTELE_ATYP_CODE, SPRTELE_ADDR_SEQNO, SPRTELE_PRIMARY_IND, SPRTELE_UNLIST_IND, SPRTELE_COMMENT, SPRTELE_INTL_ACCESS FROM SPRTELE X WHERE SPRTELE_STATUS_IND IS NULL AND SPRTELE_TELE_CODE = 'CE' AND SPRTELE_ATYP_CODE = 'WK' AND(X.SPRTELE_PRIMARY_IND = 'Y' OR X.SPRTELE_SEQNO = (SELECT MAX(X.SPRTELE_SEQNO) FROM SPRTELE V WHERE V.SPRTELE_PIDM = X.SPRTELE_PIDM AND V.SPRTELE_TELE_CODE = X.SPRTELE_TELE_CODE AND V.SPRTELE_ADDR_SEQNO = X.SPRTELE_ADDR_SEQNO AND V.SPRTELE_ATYP_CODE = X.SPRTELE_ATYP_CODE)) ; --DESC SPRTELE_FAX_V -- SPRTELE_FAX_v_create.sql Current Records VIEW from TABLE SPRTELE CREATE OR REPLACE VIEW SPRTELE_FAX_V ( SPRTELE_PIDM, SPRTELE_SEQNO, SPRTELE_TELE_CODE, SPRTELE_ACTIVITY_DATE, Fax, SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER, SPRTELE_PHONE_EXT, SPRTELE_STATUS_IND, SPRTELE_ATYP_CODE, SPRTELE_ADDR_SEQNO, SPRTELE_PRIMARY_IND, SPRTELE_UNLIST_IND, SPRTELE_COMMENT, SPRTELE_INTL_ACCESS ) AS SELECT -- Created from TABLE SPRTELE Telephone Table - Fax SPRTELE_PIDM, SPRTELE_SEQNO, SPRTELE_TELE_CODE, TRUNC(SPRTELE_ACTIVITY_DATE) SPRTELE_ACTIVITY_DATE, DECODE(SPRTELE_UNLIST_IND, -- Formatted area-3-4-ext except Unlisted 'Y','Unlisted', (SPRTELE_PHONE_AREA||DECODE(SPRTELE_PHONE_AREA,NULL,NULL,'-')|| SUBSTR(SPRTELE_PHONE_NUMBER,1,3)||DECODE(SPRTELE_PHONE_NUMBER,NULL,NULL,'-')|| SUBSTR(SPRTELE_PHONE_NUMBER,4,7)|| DECODE(SPRTELE_PHONE_EXT,NULL,NULL,'-')|| SPRTELE_PHONE_EXT)), SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER, SPRTELE_PHONE_EXT, SPRTELE_STATUS_IND, SPRTELE_ATYP_CODE, SPRTELE_ADDR_SEQNO, SPRTELE_PRIMARY_IND, SPRTELE_UNLIST_IND, SPRTELE_COMMENT, SPRTELE_INTL_ACCESS FROM SPRTELE W WHERE W.SPRTELE_STATUS_IND IS NULL AND W.SPRTELE_TELE_CODE = 'FX' AND W.SPRTELE_ATYP_CODE = 'WK' AND(W.SPRTELE_PRIMARY_IND = 'Y' OR W.SPRTELE_SEQNO = (SELECT MAX(W.SPRTELE_SEQNO) FROM SPRTELE V WHERE V.SPRTELE_PIDM = W.SPRTELE_PIDM AND V.SPRTELE_TELE_CODE = W.SPRTELE_TELE_CODE AND V.SPRTELE_ADDR_SEQNO = W.SPRTELE_ADDR_SEQNO AND V.SPRTELE_ATYP_CODE = W.SPRTELE_ATYP_CODE)) ; --DESC SPRTELE_HOME_V -- SPRTELE_HOME_v_create.sql Current Records VIEW from TABLE SPRTELE CREATE OR REPLACE VIEW SPRTELE_HOME_V ( SPRTELE_PIDM, SPRTELE_SEQNO, SPRTELE_TELE_CODE, SPRTELE_ACTIVITY_DATE, Home_Phone, SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER, SPRTELE_PHONE_EXT, SPRTELE_STATUS_IND, SPRTELE_ATYP_CODE, SPRTELE_ADDR_SEQNO, SPRTELE_PRIMARY_IND, SPRTELE_UNLIST_IND, SPRTELE_COMMENT, SPRTELE_INTL_ACCESS ) AS SELECT -- Created from TABLE SPRTELE Telephone Table - Work SPRTELE_PIDM, SPRTELE_SEQNO, SPRTELE_TELE_CODE, TRUNC(SPRTELE_ACTIVITY_DATE) SPRTELE_ACTIVITY_DATE, DECODE(SPRTELE_UNLIST_IND, -- Formatted area-3-4-ext except Unlisted 'Y','Unlisted', (SPRTELE_PHONE_AREA||DECODE(SPRTELE_PHONE_AREA,NULL,NULL,'-')|| SUBSTR(SPRTELE_PHONE_NUMBER,1,3)||DECODE(SPRTELE_PHONE_NUMBER,NULL,NULL,'-')|| SUBSTR(SPRTELE_PHONE_NUMBER,4,7)|| DECODE(SPRTELE_PHONE_EXT,NULL,NULL,'-')|| SPRTELE_PHONE_EXT)) "Phone", SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER, SPRTELE_PHONE_EXT, SPRTELE_STATUS_IND, SPRTELE_ATYP_CODE, SPRTELE_ADDR_SEQNO, SPRTELE_PRIMARY_IND, SPRTELE_UNLIST_IND, SPRTELE_COMMENT, SPRTELE_INTL_ACCESS FROM SPRTELE W WHERE SPRTELE_STATUS_IND IS NULL AND W.SPRTELE_TELE_CODE = 'RE' AND W.SPRTELE_ATYP_CODE = 'RE' AND(W.SPRTELE_PRIMARY_IND = 'Y' OR W.SPRTELE_SEQNO = (SELECT MAX(W.SPRTELE_SEQNO) FROM SPRTELE V WHERE V.SPRTELE_PIDM = W.SPRTELE_PIDM AND V.SPRTELE_TELE_CODE = W.SPRTELE_TELE_CODE AND V.SPRTELE_ADDR_SEQNO = W.SPRTELE_ADDR_SEQNO AND V.SPRTELE_ATYP_CODE = W.SPRTELE_ATYP_CODE)) ; --DESC SPRTELE_V -- SPRTELE_v_create.sql Current Records VIEW from TABLE SPRTELE CREATE OR REPLACE VIEW SPRTELE_V ( SPRTELE_PIDM, SPRTELE_SEQNO, SPRTELE_TELE_CODE, SPRTELE_ACTIVITY_DATE, Phone, SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER, SPRTELE_PHONE_EXT, SPRTELE_STATUS_IND, SPRTELE_ATYP_CODE, SPRTELE_ADDR_SEQNO, SPRTELE_PRIMARY_IND, SPRTELE_UNLIST_IND, SPRTELE_COMMENT, SPRTELE_INTL_ACCESS ) AS SELECT -- Created from TABLE SPRTELE Telephone Table - Work SPRTELE_PIDM, SPRTELE_SEQNO, SPRTELE_TELE_CODE, TRUNC(SPRTELE_ACTIVITY_DATE) SPRTELE_ACTIVITY_DATE, DECODE(SPRTELE_UNLIST_IND, -- Formatted area-3-4-ext except Unlisted 'Y','Unlisted', (SPRTELE_PHONE_AREA||DECODE(SPRTELE_PHONE_AREA,NULL,NULL,'-')|| SUBSTR(SPRTELE_PHONE_NUMBER,1,3)||DECODE(SPRTELE_PHONE_NUMBER,NULL,NULL,'-')|| SUBSTR(SPRTELE_PHONE_NUMBER,4,7)|| DECODE(SPRTELE_PHONE_EXT,NULL,NULL,'-')|| SPRTELE_PHONE_EXT)), SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER, SPRTELE_PHONE_EXT, SPRTELE_STATUS_IND, SPRTELE_ATYP_CODE, SPRTELE_ADDR_SEQNO, SPRTELE_PRIMARY_IND, SPRTELE_UNLIST_IND, SPRTELE_COMMENT, SPRTELE_INTL_ACCESS FROM SPRTELE W WHERE SPRTELE_STATUS_IND IS NULL AND(W.SPRTELE_PRIMARY_IND = 'Y' OR W.SPRTELE_SEQNO = (SELECT MAX(W.SPRTELE_SEQNO) FROM SPRTELE V WHERE V.SPRTELE_PIDM = W.SPRTELE_PIDM AND V.SPRTELE_TELE_CODE = W.SPRTELE_TELE_CODE AND V.SPRTELE_ADDR_SEQNO = W.SPRTELE_ADDR_SEQNO AND V.SPRTELE_ATYP_CODE = W.SPRTELE_ATYP_CODE)) ; --DESC SPRTELE_WORK_V -- SPRTELE_WORK_v_create.sql Current Records VIEW from TABLE SPRTELE CREATE OR REPLACE VIEW SPRTELE_WORK_V ( SPRTELE_PIDM, SPRTELE_SEQNO, SPRTELE_TELE_CODE, SPRTELE_ACTIVITY_DATE, WORK_Phone, SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER, SPRTELE_PHONE_EXT, SPRTELE_STATUS_IND, SPRTELE_ATYP_CODE, SPRTELE_ADDR_SEQNO, SPRTELE_PRIMARY_IND, SPRTELE_UNLIST_IND, SPRTELE_COMMENT, SPRTELE_INTL_ACCESS ) AS SELECT -- Work Phone SPRTELE_PIDM, SPRTELE_SEQNO, SPRTELE_TELE_CODE, SPRTELE_ACTIVITY_DATE, DECODE(SPRTELE_UNLIST_IND, -- Formatted area-3-4-ext except Unlisted 'Y','Unlisted', (SPRTELE_PHONE_AREA||DECODE(SPRTELE_PHONE_AREA,NULL,NULL,'-')|| SUBSTR(SPRTELE_PHONE_NUMBER,1,3)||DECODE(SPRTELE_PHONE_NUMBER,NULL,NULL,'-')|| SUBSTR(SPRTELE_PHONE_NUMBER,4,7)|| DECODE(SPRTELE_PHONE_EXT,NULL,NULL,'-')|| SPRTELE_PHONE_EXT)), SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER, SPRTELE_PHONE_EXT, SPRTELE_STATUS_IND, SPRTELE_ATYP_CODE, SPRTELE_ADDR_SEQNO, SPRTELE_PRIMARY_IND, SPRTELE_UNLIST_IND, SPRTELE_COMMENT, SPRTELE_INTL_ACCESS FROM SPRADDR Y, SPRTELE WHERE ((Y.SPRADDR_STATUS_IND IS NULL) OR (Y.SPRADDR_STATUS_IND = 'A')) AND Y.SPRADDR_ATYP_CODE = 'WK' AND (TRUNC(Y.SPRADDR_FROM_DATE)<= SYSDATE Or Y.SPRADDR_FROM_DATE Is Null) AND Y.SPRADDR_SEQNO =(SELECT MIN(X.SPRADDR_SEQNO) FROM SPRADDR X WHERE X.SPRADDR_PIDM = Y.SPRADDR_PIDM AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) AND X.SPRADDR_ATYP_CODE = Y.SPRADDR_ATYP_CODE AND ((X.SPRADDR_STATUS_IND IS NULL) OR (X.SPRADDR_STATUS_IND = 'A')) AND (TRUNC(X.SPRADDR_FROM_DATE)<= SYSDATE Or X.SPRADDR_FROM_DATE Is Null)) AND ((SPRTELE_STATUS_IND IS NULL) OR (SPRTELE_STATUS_IND = 'A')) AND SPRTELE_TELE_CODE = 'WK' AND SPRTELE_ATYP_CODE = 'WK' AND SPRTELE_PRIMARY_IND = 'Y' AND SPRTELE_ADDR_SEQNO = SPRADDR_SEQNO AND SPRTELE_PIDM = SPRADDR_PIDM ; --DESC ^_V -- ^_v_create.sql Current Records VIEW from TABLE ^ CREATE OR REPLACE VIEW ^_V ( FIELDS ) AS SELECT ; --DESC TITLES_AGENTS -- TITLES_AGENTS_create.sql Current Records VIEW from TABLE TITLES_AGENTS CREATE OR REPLACE VIEW TITLES_AGENTS ( NBBPOSN_POSN, Position_Title ) AS SELECT -- Created from TABLE NBBPOSN Position Base Table NBBPOSN_POSN, NBBPOSN_TITLE FROM NBBPOSN WHERE TRUNC(NBBPOSN_BEGIN_DATE) <= SYSDATE AND (NBBPOSN_END_DATE > SYSDATE OR NBBPOSN_END_DATE IS NULL) AND (LOWER(NBBPOSN_TITLE) LIKE '%gent%' AND NBBPOSN_TITLE != 'Purchasing Agent') ; --DESC TITLES_EXTENDED_WORKING -- TITLES_EXTENDED_WORKING_create.sql Current Records VIEW from TABLE PPRCCMT, PPRCCMT_CMTY_CODE = 'WT' CREATE OR REPLACE VIEW TITLES_EXTENDED_WORKING ( PPRCCMT_PIDM, Working_Title ) AS SELECT -- Created from TABLE Working Title (Full or Extended) from PPRCCMT Person Coded Comment Repeating Table CMTY_CODE = 'WT' Only PPRCCMT_PIDM, PPRCCMT_TEXT FROM PPRCCMT WHERE PPRCCMT_CMTY_CODE = 'WT' ; --DESC TITLES_FOR_APPOINTMENTS -- TITLES_FOR_APPOINTMENTS_create.sql CREATE OR REPLACE VIEW TITLES_FOR_APPOINTMENTS ( PIDM, Appt_Pct, Position, Working_Title, Line_Item_Title, nbrbjob_contract_type ) AS SELECT NBRJOBS_PIDM PIDM, -- Working and Line Item Titles for Positions with Percent Appointment NBRJOBS_APPT_PCT, NBRJOBS_POSN, NVL(NBRJOBS_DESC,' '), NVL(NTRPCLS_DESC,' '), nbrbjob_contract_type FROM NBRJOBS G, nbbposn D, ntrpcls, NBRBJOB WHERE nbrjobs_posn = nbbposn_posn (+) AND NBRBJOB_PIDM = NBRJOBS_PIDM AND NBRBJOB_POSN = NBRJOBS_POSN AND nbbposn_status = 'A' AND (nbbposn_end_date IS NULL OR TRUNC(nbbposn_end_date) < SYSDATE) AND D.ROWID IN (SELECT E.ROWID FROM nbbposn E WHERE E.nbbposn_begin_date = (SELECT MAX(F.nbbposn_begin_date) FROM nbbposn F WHERE F.nbbposn_posn = NBRJOBS_POSN AND F.nbbposn_pcls_code = ntrpcls_code AND TRUNC(F.nbbposn_begin_date) <= SYSDATE AND F.nbbposn_status = 'A' ) AND E.nbbposn_posn = NBRJOBS_POSN AND E.nbbposn_pcls_code = ntrpcls_code AND E.nbbposn_status = 'A' AND TRUNC(E.nbbposn_begin_date) <= SYSDATE ) AND TRUNC(nbbposn_begin_date) <= SYSDATE AND nbbposn_posn = NBRJOBS_POSN AND nbbposn_pcls_code = ntrpcls_code AND G.ROWID IN (SELECT H.ROWID FROM NBRJOBS H WHERE H.NBRJOBS_effective_date = (SELECT MAX(I.NBRJOBS_effective_date) FROM NBRJOBS I WHERE I.NBRJOBS_pidm = H.NBRJOBS_pidm AND TRUNC(I.NBRJOBS_effective_date) <= SYSDATE -- note that only the ending record within a position is Terminated, you must select the -- most recent record within a position and then outside of the Max routine select Active -- AND I.NBRJOBS_STATUS = 'A' AND I.NBRJOBS_POSN = H.NBRJOBS_POSN ) AND H.NBRJOBS_pidm = G.NBRJOBS_pidm AND TRUNC(H.NBRJOBS_effective_date) <= SYSDATE -- AND H.NBRJOBS_STATUS = 'A' AND H.NBRJOBS_POSN = G.NBRJOBS_POSN ) AND NBRJOBS_STATUS = 'A' AND TRUNC(NBRJOBS_effective_date) <= SYSDATE AND (TRUNC(NBRBJOB_BEGIN_DATE) < SYSDATE OR NBRBJOB_BEGIN_DATE IS NULL) AND (TRUNC(NBRBJOB_END_DATE) >= SYSDATE OR NBRBJOB_END_DATE IS NULL) ; --DESC TITLES_FOR_APPT_PCT_PER -- TITLES_FOR_APPT_PCT_PER_create.sql CREATE OR REPLACE VIEW TITLES_FOR_APPT_PCT_PER ( POSN_PIDM, PIDM, Appt_Pct, Percent, POSN, Working_Title, Line_Item_Title ) AS SELECT NBRJOBS_POSN||NBRJOBS_PIDM, -- Working and Line Item Titles for Positions with Percent Appointment NBRJOBS_PIDM, NBRJOBS_APPT_PCT, NBRJLBD_PERCENT, NBRJOBS_POSN, NVL(NBRJOBS_DESC,' '), NVL(NTRPCLS_DESC,' ') FROM NBRJOBS G, nbbposn D, ntrpcls, NBRBJOB, nbrjlbd A WHERE A.ROWID IN (SELECT B.ROWID FROM nbrjlbd B WHERE B.nbrjlbd_effective_date = (SELECT MAX(nbrjlbd_effective_date) FROM nbrjlbd C WHERE C.nbrjlbd_pidm = B.nbrjlbd_pidm ) ) AND TRUNC(nbrjlbd_EFFECTIVE_DATE) <= SYSDATE AND NBRJLBD_CHANGE_IND = 'A' AND NBRJLBD_PIDM = NBRJOBS_PIDM AND NBRJLBD_POSN = NBRJOBS_POSN AND nbrjobs_posn = nbbposn_posn (+) AND NBRBJOB_PIDM = NBRJOBS_PIDM AND NBRBJOB_POSN = NBRJOBS_POSN AND nbbposn_status = 'A' AND (nbbposn_end_date IS NULL OR TRUNC(nbbposn_end_date) < SYSDATE) AND D.ROWID IN (SELECT E.ROWID FROM nbbposn E WHERE E.nbbposn_begin_date = (SELECT MAX(F.nbbposn_begin_date) FROM nbbposn F WHERE F.nbbposn_posn = NBRJOBS_POSN AND F.nbbposn_pcls_code = ntrpcls_code AND TRUNC(F.nbbposn_begin_date) <= SYSDATE AND F.nbbposn_status = 'A' ) AND E.nbbposn_posn = NBRJOBS_POSN AND E.nbbposn_pcls_code = ntrpcls_code AND E.nbbposn_status = 'A' AND TRUNC(E.nbbposn_begin_date) <= SYSDATE ) AND TRUNC(nbbposn_begin_date) <= SYSDATE AND nbbposn_posn = NBRJOBS_POSN AND nbbposn_pcls_code = ntrpcls_code AND G.ROWID IN (SELECT H.ROWID FROM NBRJOBS H WHERE H.NBRJOBS_effective_date = (SELECT MAX(I.NBRJOBS_effective_date) FROM NBRJOBS I WHERE I.NBRJOBS_pidm = H.NBRJOBS_pidm AND TRUNC(I.NBRJOBS_effective_date) <= SYSDATE -- note that only the ending record within a position is Terminated, you must select the -- most recent record within a position and then outside of the Max routine select Active -- AND I.NBRJOBS_STATUS = 'A' AND I.NBRJOBS_POSN = H.NBRJOBS_POSN ) AND H.NBRJOBS_pidm = G.NBRJOBS_pidm AND TRUNC(H.NBRJOBS_effective_date) <= SYSDATE -- AND H.NBRJOBS_STATUS = 'A' AND H.NBRJOBS_POSN = G.NBRJOBS_POSN ) AND NBRJOBS_STATUS = 'A' AND TRUNC(NBRJOBS_effective_date) <= SYSDATE AND (TRUNC(NBRBJOB_BEGIN_DATE) < SYSDATE OR NBRBJOB_BEGIN_DATE IS NULL) AND (TRUNC(NBRBJOB_END_DATE) >= SYSDATE OR NBRBJOB_END_DATE IS NULL) ; --DESC TITLES_POSITION -- TITLES_POSITION_create.sql Current Records VIEW from TABLE ^ CREATE OR REPLACE VIEW TITLES_POSITION ( NBBPOSN_POSN, Position_Title ) AS SELECT -- Created from TABLE NBBPOSN Position Base Table NBBPOSN_POSN, NBBPOSN_TITLE "Position Title" FROM NBBPOSN WHERE TRUNC(NBBPOSN_BEGIN_DATE) <= SYSDATE AND (NBBPOSN_END_DATE > SYSDATE OR NBBPOSN_END_DATE IS NULL) ; --DESC TITLES_SPECIALIST -- TITLES_SPECIALIST_create.sql Current Records VIEW from TABLE ^ CREATE OR REPLACE VIEW TITLES_SPECIALIST ( NBBPOSN_POSN, Position_Title ) AS SELECT -- Created from TABLE NBBPOSN Position Base Table NBBPOSN_POSN, NBBPOSN_TITLE FROM NBBPOSN WHERE TRUNC(NBBPOSN_BEGIN_DATE) <= SYSDATE AND (NBBPOSN_END_DATE > SYSDATE OR NBBPOSN_END_DATE IS NULL) AND LOWER(NBBPOSN_TITLE) LIKE '%pecia%' ;