/***********************************************************************
**			View of some cFOPAL elements
************************************************************************
**
**	This is an example of how to create a view that can be used to
**   provide a view of chosen data.  Views are useful SQL constructs that 
**   once created can be used as though they are tables.  
************************************************************************
**
** 				This code is not for production.  
**			Run this in test and verify the data are what is
**			expected before using in production. 
**
************************************************************************
** 
**	© 2002, 2006 Sungard Higher Education.  All rights reserved.
** 			Not for Web publication
**
************************************************************************
**  Audit Trail
**  Modified 8-Sep-2006		Jim Beyer
**  Added header, notices and formatted to reference pack standard.
**
************************************************************************


/* removing the force tells you what errors you may have, i.e. like
** insufficient rights. */

Create Or Replace Force View f_acct_levels
(
COAS_CODE_KEY,
FSYR_KEY,
ACCT_CODE_KEY,
ACCT_DESC,
ACCT_CODE_1,
ACCT_DESC_1,
ACCT_CODE_2,
ACCT_DESC_2,
ACCT_CODE_3,
ACCT_DESC_3,
ACCT_CODE_4,
ACCT_DESC_4,
ATYP_CODE_1,
ATYP_DESC_1,
ATYP_CODE_2,
ATYP_DESC_2
  )
As
Select
  FGBOPAL_COAS_CODE,
  FGBOPAL_FSYR_CODE,
  FGBOPAL_ACCT_CODE,
FTVACCT_TITLE,
  Substr(F_ACCT_HIER1_FNC(FGBOPAL_COAS_CODE,FGBOPAL_ACCT_CODE,1,SYSDATE),1,6),
  Substr(F_ACCT_HIER2_FNC(FGBOPAL_COAS_CODE,FGBOPAL_ACCT_CODE,1,SYSDATE),1,35),
  Substr(F_ACCT_HIER1_FNC(FGBOPAL_COAS_CODE,FGBOPAL_ACCT_CODE,2,SYSDATE),1,6),
  Substr(F_ACCT_HIER2_FNC(FGBOPAL_COAS_CODE,FGBOPAL_ACCT_CODE,2,SYSDATE),1,35),
  Substr(F_ACCT_HIER1_FNC(FGBOPAL_COAS_CODE,FGBOPAL_ACCT_CODE,3,SYSDATE),1,6),
  Substr(F_ACCT_HIER2_FNC(FGBOPAL_COAS_CODE,FGBOPAL_ACCT_CODE,3,SYSDATE),1,35),
  Substr(F_ACCT_HIER1_FNC(FGBOPAL_COAS_CODE,FGBOPAL_ACCT_CODE,4,SYSDATE),1,6),
  Substr(F_ACCT_HIER2_FNC(FGBOPAL_COAS_CODE,FGBOPAL_ACCT_CODE,4,SYSDATE),1,35),
  Substr(F_ATYP_HIER1_FNC(FGBOPAL_COAS_CODE,FTVACCT_ATYP_CODE,1,SYSDATE),1,6),
  Substr(F_ATYP_HIER2_FNC(FGBOPAL_COAS_CODE,FTVACCT_ATYP_CODE,1,SYSDATE),1,35),
  Substr(F_ATYP_HIER1_FNC(FGBOPAL_COAS_CODE,FTVACCT_ATYP_CODE,2,SYSDATE),1,6),
  Substr(F_ATYP_HIER2_FNC(FGBOPAL_COAS_CODE,FTVACCT_ATYP_CODE,2,SYSDATE),1,35)
  
From 
	FTVACCT, 
	FGBOPAL

Where 
FTVACCT_COAS_CODE = FGBOPAL_COAS_CODE
And FTVACCT_ACCT_CODE = FGBOPAL_ACCT_CODE
And FTVACCT_EFF_DATE <= SYSDATE
And FTVACCT_NCHG_DATE > SYSDATE
;