KK_BD_STATUS_VW (SQL View)	Index Back
Budget Status Hierarchy Used to check for budget status using the hierarchy levels

KK_BD_STATUS_VW

(SQL View)

Budget Status Hierarchy

Used to check for budget status using the hierarchy levels

SELECT A.OPRID ,A.INQUIRY ,A.BUSINESS_UNIT , A.LEDGER_GROUP , B.EFFDT , '1' , ' ' , ' ' , ' ' , ' ' , ' ' , B.BUDGET_STATUS FROM PS_KK_INQ_LDCRI_VW A , PS_KK_BUDGET_TYPE B , PS_SET_CNTRL_GROUP C WHERE C.REC_GROUP_ID ='FS_04' AND C.SETCNTRLVALUE = A.BUSINESS_UNIT AND C.SETID = B.SETID1 AND B.LEDGER_GROUP = A.LEDGER_GROUP AND B.BUDGET_STATUS <> '3' AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_KK_BUDGET_TYPE D WHERE D.SETID1 = B.SETID1 AND D.LEDGER_GROUP = B.LEDGER_GROUP AND D.EFFDT <= %CurrentDateIn AND D.EFF_STATUS = 'A') UNION SELECT A.OPRID , A.INQUIRY , A.BUSINESS_UNIT , A.LEDGER_GROUP , B.EFFDT , '2' , B.CALENDAR_ID , B.BUDGET_PERIOD , ' ' , ' ' , ' ' , B.BUDGET_STATUS FROM PS_KK_INQ_LDCRI_VW A , PS_KK_BT_BP_VALUE B , PS_SET_CNTRL_GROUP C WHERE C.REC_GROUP_ID ='FS_04' AND C.SETCNTRLVALUE = A.BUSINESS_UNIT AND C.SETID = B.SETID1 AND B.LEDGER_GROUP = A.LEDGER_GROUP AND B.BUDGET_STATUS <> '3' AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_KK_BUDGET_TYPE D WHERE D.SETID1 = B.SETID1 AND D.LEDGER_GROUP = B.LEDGER_GROUP AND D.EFFDT <= %CurrentDateIn AND D.EFF_STATUS = 'A') UNION SELECT A.OPRID ,A.INQUIRY ,A.BUSINESS_UNIT , A.LEDGER_GROUP , B.EFFDT , '3' , ' ' , ' ' , B.SETID , B.RANGE_FROM , B.RANGE_TO , B.BUDGET_STATUS FROM PS_KK_INQ_LDCRI_VW A , PS_KK_CF_VALUE B , PS_SET_CNTRL_GROUP C WHERE C.REC_GROUP_ID ='FS_04' AND C.SETCNTRLVALUE = A.BUSINESS_UNIT AND C.SETID = B.SETID1 AND B.LEDGER_GROUP = A.LEDGER_GROUP AND B.BUDGET_STATUS <> '3' AND B.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_KK_BUDGET_TYPE D WHERE D.SETID1 = B.SETID1 AND D.LEDGER_GROUP = B.LEDGER_GROUP AND D.EFFDT <= %CurrentDateIn AND D.EFF_STATUS = 'A')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

2 INQUIRY Character(10) VARCHAR2(10) NOT NULL Inquiry Name

3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

4 LEDGER_GROUP Character(10) VARCHAR2(10) NOT NULL Ledger Group

5 EFFDT Date(10) DATE NOT NULL Effective Date

6 HIERARCHY_LEVEL Character(1) VARCHAR2(1) NOT NULL Hierarchy Level

7 CALENDAR_ID Character(2) VARCHAR2(2) NOT NULL Calendar ID

8 BUDGET_PERIOD Character(8) VARCHAR2(8) NOT NULL Budget Period

9 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Prompt Table: SP_SETID_NONVW

10 RANGE_FROM Character(30) VARCHAR2(30) NOT NULL Lower boundary for detail value that contains a range of values
Prompt Table: %EDITTABLE

11 RANGE_TO Character(30) VARCHAR2(30) NOT NULL Upper boundary for detail value that contains a range of values
Prompt Table: %EDITTABLE

12 BUDGET_STATUS Character(1) VARCHAR2(1) NOT NULL Budget Status
0=Open
1=Closed
2=Hold
3=Default
Default Value: 0

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	INQUIRY	Character(10)	VARCHAR2(10) NOT NULL	Inquiry Name
3	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
4	LEDGER_GROUP	Character(10)	VARCHAR2(10) NOT NULL	Ledger Group
5	EFFDT	Date(10)	DATE NOT NULL	Effective Date
6	HIERARCHY_LEVEL	Character(1)	VARCHAR2(1) NOT NULL	Hierarchy Level
7	CALENDAR_ID	Character(2)	VARCHAR2(2) NOT NULL	Calendar ID
8	BUDGET_PERIOD	Character(8)	VARCHAR2(8) NOT NULL	Budget Period
9	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Prompt Table: SP_SETID_NONVW
10	RANGE_FROM	Character(30)	VARCHAR2(30) NOT NULL	Lower boundary for detail value that contains a range of values Prompt Table: %EDITTABLE
11	RANGE_TO	Character(30)	VARCHAR2(30) NOT NULL	Upper boundary for detail value that contains a range of values Prompt Table: %EDITTABLE
12	BUDGET_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Budget Status 0=Open 1=Closed 2=Hold 3=Default Default Value: 0