AV_ORG_FND_VW

(SQL View)
 Index Back

Organization Foundation View

Latest fiscal year view of an Organization Foundation.

SELECT A.EXT_ORG_ID , A.EXT_ORG_ID_RELATED , A.AV_ESTAB_DT , A.AV_FND_TYP_CD , A.CNTC_TYPE_CD , A.ORG_CONTACT , B.FISCAL_YEAR , B.AV_VALID_FROM_DT , B.AV_VALID_TO_DT , CASE WHEN B.AV_ASSETS_AMT IS NOT NULL THEN B.AV_ASSETS_AMT ELSE 0 END , CASE WHEN B.AV_EXPENSES_AMT IS NOT NULL THEN B.AV_EXPENSES_AMT ELSE 0 END , CASE WHEN B.AV_DISTRIB_AMT IS NOT NULL THEN B.AV_DISTRIB_AMT ELSE 0 END , CASE WHEN B.AV_GRANT_AMT IS NOT NULL THEN B.AV_GRANT_AMT ELSE 0 END , CASE WHEN B.AV_GRANT_HI_AMT IS NOT NULL THEN B.AV_GRANT_HI_AMT ELSE 0 END , CASE WHEN B.AV_GRANT_LO_AMT IS NOT NULL THEN B.AV_GRANT_LO_AMT ELSE 0 END , CASE WHEN B.AV_GRANT_AVG_AMT IS NOT NULL THEN B.AV_GRANT_AVG_AMT ELSE 0 END , B.CURRENCY_CD , B.RATE_MULT , B.RATE_DIV , CASE WHEN B.AV_GRANT_CNT IS NOT NULL THEN B.AV_GRANT_CNT ELSE 0 END , B.AV_DATE , B.SRC_DESCR60 , A.DESCRLONG FROM PS_AV_ORG_FND_INFO A LEFT OUTER JOIN PS_AV_ORG_FND_FY B ON A.EXT_ORG_ID = B.EXT_ORG_ID WHERE (B.FISCAL_YEAR = ( SELECT MAX(B1.FISCAL_YEAR) FROM PS_AV_ORG_FND_FY B1 WHERE B1.EXT_ORG_ID = B.EXT_ORG_ID) OR B.FISCAL_YEAR IS NULL)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID

Prompt Table: AV_EXT_ORG_SRCH

2 EXT_ORG_ID_RELATED Character(11) VARCHAR2(11) NOT NULL Related ID

Prompt Table: AV_EXT_ORG_VW

3 AV_ESTAB_DT Date(10) DATE Date Established
4 AV_FND_TYP_CD Character(10) VARCHAR2(10) NOT NULL Foundation Type Code

Prompt Table: AV_FND_TYP_TBL

5 CNTC_TYPE_CD Character(6) VARCHAR2(6) NOT NULL Contact Type Code

Prompt Table: AV_C_AC_CNT_VW

6 ORG_CONTACT Number(4,0) SMALLINT NOT NULL Contact Nbr

Prompt Table: ORG_CONTACT

7 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
8 AV_VALID_FROM_DT Date(10) DATE From Date
9 AV_VALID_TO_DT Date(10) DATE To Date
10 AV_ASSETS_AMT Number(16,2) DECIMAL(15,2) NOT NULL Assets Amount
11 AV_EXPENSES_AMT Number(16,2) DECIMAL(15,2) NOT NULL Expenses Amount Base
12 AV_DISTRIB_AMT Number(16,2) DECIMAL(15,2) NOT NULL Distribution Amount Base
13 AV_GRANT_AMT Number(16,2) DECIMAL(15,2) NOT NULL Total Grants Amount Base
14 AV_GRANT_HI_AMT Number(16,2) DECIMAL(15,2) NOT NULL Highest Grant Amount Base
15 AV_GRANT_LO_AMT Number(16,2) DECIMAL(15,2) NOT NULL Lowest Grant Amount Base
16 AV_GRANT_AVG_AMT Number(16,2) DECIMAL(15,2) NOT NULL Average Grant Amount Base
17 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: AV_INSTALL_INST.BASE_CURRENCY

18 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
19 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
20 AV_GRANT_CNT Number(9,0) DECIMAL(9) NOT NULL Foundation Grant Count
21 AV_DATE Date(10) DATE Date
22 SRC_DESCR60 Character(60) VARCHAR2(60) NOT NULL expanded Source Desciption Field
23 DESCRLONG Long Character CLOB Long Description