KK_OVW_SBPB_VW

(SQL View)
 Index Back

CC Ledger Amts View

CC Budget Overview Budgets by summary budget period used in selecting rows in KK_INQ_WRK.PB_FETCH.FieldChange peoplecode. This is a sum of the corresponding amount columns with expense type budgets and revenue budgets designated columns that also matches the columns in the Budget Overview Inquiry page results grid. ICE 1740660000.

SELECT A.OPRID , A.INQUIRY , A.BUSINESS_UNIT , A.LEDGER_GROUP , A.ACCOUNT , A.DEPTID , %subrec(CF9A_AK_SBR, A) , %subrec(CFCC2_AK_SBR, A) , A.STATISTICS_CODE , A.BUDGET_PERIOD , B.BUDG_TYPE , D.LEDGER_GROUP_TYPE , B.ASSOC_EXP_BD , SUM(A.KK_BUDG_ORIG_AMT) , SUM(A.KK_BUDG_ADJ_AMT) , SUM(A.KK_BUDG_XADJ_AMT) , SUM(A.KK_BUDG_XORIG_AMT) , SUM(A.KK_BUDG_CLOSE_AMT) , SUM(A.KK_BAL_FORWARD) , 0 , CASE WHEN B.BUDG_TYPE = 'E' THEN SUM(A.AMT1) ELSE 0 END , CASE WHEN B.BUDG_TYPE = 'E' THEN SUM(A.AMT2) ELSE 0 END , CASE WHEN B.BUDG_TYPE = 'E' THEN SUM(A.AMT3) ELSE 0 END , CASE WHEN B.BUDG_TYPE = 'E' THEN SUM(A.AMT4) ELSE 0 END , CASE WHEN B.BUDG_TYPE = 'E' THEN SUM(A.AMT6) ELSE 0 END , 0 , CASE WHEN B.BUDG_TYPE = 'E' THEN SUM(A.AMT7) ELSE 0 END , CASE WHEN B.BUDG_TYPE = 'R' THEN SUM(A.AMT1) ELSE 0 END , CASE WHEN B.BUDG_TYPE = 'R' THEN SUM(A.AMT2) ELSE 0 END , CASE WHEN B.BUDG_TYPE = 'R' THEN SUM(A.AMT5) ELSE 0 END , 0 , 0 , CASE WHEN B.BUDG_TYPE = 'R' THEN SUM(A.AMT7) ELSE 0 END FROM PS_KK_OVW_SBPA_VW A , PS_KK_BUDGET_TYPE B , PS_SET_CNTRL_REC C , PS_LED_GRP_BU_VW D WHERE C.SETCNTRLVALUE = A.BUSINESS_UNIT AND C.RECNAME = 'KK_BD_TYPE_VW' AND D.BUSINESS_UNIT = A.BUSINESS_UNIT AND D.LEDGER_GROUP = A.LEDGER_GROUP AND B.SETID1 = C.SETID AND B.LEDGER_GROUP = A.LEDGER_GROUP AND B.EFFDT = ( SELECT MAX(J.EFFDT) FROM PS_KK_BUDGET_TYPE J WHERE J.SETID1 = B.SETID1 AND J.LEDGER_GROUP = B.LEDGER_GROUP AND J.EFFDT <= %CurrentDateIn AND J.EFF_STATUS = 'A') GROUP BY A.OPRID, A.INQUIRY, A.BUSINESS_UNIT , A.LEDGER_GROUP , B.BUDG_TYPE, D.LEDGER_GROUP_TYPE , A.ACCOUNT , A.DEPTID , %subrec(CF9A_AK_SBR, A) , %subrec(CFCC2_AK_SBR, A) , A.STATISTICS_CODE , A.BUDGET_PERIOD, B.ASSOC_EXP_BD

# 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 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

7 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

Prompt Table: OPER_UNIT_TBL

8 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

Prompt Table: PRODUCT_TBL

9 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

Prompt Table: FUND_TBL

10 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

Prompt Table: CLASS_CF_TBL

11 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

Prompt Table: PROGRAM_TBL

12 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

Prompt Table: BUD_REF_TBL

13 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

Prompt Table: AFFILIATE_VW

14 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

Prompt Table: %EDIT_INTRA01

15 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

Prompt Table: %EDIT_INTRA02

16 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

Prompt Table: CHARTFIELD1_TBL

17 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

Prompt Table: CHARTFIELD2_TBL

18 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

Prompt Table: CHARTFIELD3_TBL

19 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

Prompt Table: SP_PCBUGL_NONVW

20 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

Prompt Table: SP_PROJFS_NONVW

21 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

Prompt Table: PROJ_ACT_FVW

22 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type

Prompt Table: PROJ_RESTYPE_FS

23 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
24 BUDGET_PERIOD Character(8) VARCHAR2(8) NOT NULL Budget Period
25 BUDG_TYPE Character(1) VARCHAR2(1) NOT NULL Budget Type
E=Expense
R=Revenue
26 LEDGER_GROUP_TYPE Character(1) VARCHAR2(1) NOT NULL Ledger Group Type
A=Average Daily Balance
B=Budget
E=Commitment Control Expense
R=Commitment Control Revenue
S=Standard
T=Translation
27 ASSOC_EXP_BD Character(10) VARCHAR2(10) NOT NULL Associated Expenditure Budget
28 KK_BUDG_ORIG_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Original Budget Amount
29 KK_BUDG_ADJ_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budget Adjustment Amount
30 KK_BUDG_XADJ_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budget Transfer Adjustment Amt
31 KK_BUDG_XORIG_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budget Transfer Original Amt
32 KK_BUDG_CLOSE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budget Closing Amount
33 KK_BAL_FORWARD Signed Number(28,3) DECIMAL(26,3) NOT NULL Balance Rolled Forward
34 KK_ASSOC_REV_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Associated Revenues
35 AMT1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Used in Virtual Approver to store the various rule amounts that go on each approval step
36 AMT2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Used in Virtual Approver to store the various rule amounts that go on each approval step
37 AMT3 Signed Number(28,3) DECIMAL(26,3) NOT NULL Used in Virtual Approver to store the various rule amounts that go on each approval step
38 AMT4 Signed Number(28,3) DECIMAL(26,3) NOT NULL Used in Virtual Approver to store the various rule amounts that go on each approval step
39 AMT5 Signed Number(28,3) DECIMAL(26,3) NOT NULL Used in Virtual Approver to store the various rule amounts that go on each approval step
40 AMT6 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount 6
41 AMT7 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount 7
42 AMTR1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Revenue 1
43 AMTR2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Revenue 2
44 AMTR3 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Revenue 3
45 AMTR4 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Revenue 4
46 AMTR5 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Revenue 5
47 AMTR6 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Revenue 6