KK_OVW_DBPA_VW

(SQL View)
 Index Back

CC Ledger Amts View

CC Budget Overview Planned by detail budget period. This is a union of KK_OVW_DBP0_VW, KK_OVW_DBP1_VW, KK_OVW_DBP2_VW, KK_OVW_DBP3_VW, KK_OVW_DBP4_VW, KK_OVW_DBP5_VW views with the amount columns assigned for each of the ledger types.

SELECT A.OPRID , A.INQUIRY , A.BUSINESS_UNIT , A.LEDGER_GROUP , A.ACCOUNT , A.DEPTID , %subrec(CF9A_AK_SBR, A) , %subrec(CFCC1_AK_SBR, A) , A.STATISTICS_CODE , A.POSTED_TOTAL_AMT , 0 , 0 , 0 , 0 , 0 , A.POSTED_TOTAL_AMT , 0 , 0 , 0 , 0 , 0 , CASE WHEN A.AFFECT_SPEND_OPTN = 'Y' THEN A.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP0_VW A WHERE A.KK_BUDG_TRANS_TYPE = '0' UNION ALL SELECT B.OPRID , B.INQUIRY , B.BUSINESS_UNIT , B.LEDGER_GROUP , B.ACCOUNT , B.DEPTID , %subrec(CF9A_AK_SBR, B) , %subrec(CFCC1_AK_SBR, B) , B.STATISTICS_CODE , 0 , B.POSTED_TOTAL_AMT , 0 , 0 , 0 , 0 , B.POSTED_TOTAL_AMT , 0 , 0 , 0 , 0 , 0 , CASE WHEN B.AFFECT_SPEND_OPTN = 'Y' THEN B.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP0_VW B WHERE B.KK_BUDG_TRANS_TYPE = '1' UNION ALL SELECT C.OPRID , C.INQUIRY , C.BUSINESS_UNIT , C.LEDGER_GROUP , C.ACCOUNT , C.DEPTID , %subrec(CF9A_AK_SBR, C) , %subrec(CFCC1_AK_SBR, C) , C.STATISTICS_CODE , 0 , 0 , C.POSTED_TOTAL_AMT , 0 , 0 , 0 , C.POSTED_TOTAL_AMT , 0 , 0 , 0 , 0 , 0 , CASE WHEN C.AFFECT_SPEND_OPTN = 'Y' THEN C.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP0_VW C WHERE C.KK_BUDG_TRANS_TYPE = '2' UNION ALL SELECT D.OPRID , D.INQUIRY , D.BUSINESS_UNIT , D.LEDGER_GROUP , D.ACCOUNT , D.DEPTID , %subrec(CF9A_AK_SBR, D) , %subrec(CFCC1_AK_SBR, D) , D.STATISTICS_CODE , 0 , 0 , 0 , D.POSTED_TOTAL_AMT , 0 , 0 , D.POSTED_TOTAL_AMT , 0 , 0 , 0 , 0 , 0 , CASE WHEN D.AFFECT_SPEND_OPTN = 'Y' THEN D.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP0_VW D WHERE D.KK_BUDG_TRANS_TYPE = '3' UNION ALL SELECT E.OPRID , E.INQUIRY , E.BUSINESS_UNIT , E.LEDGER_GROUP , E.ACCOUNT , E.DEPTID , %subrec(CF9A_AK_SBR, E) , %subrec(CFCC1_AK_SBR, E) , E.STATISTICS_CODE , 0 , 0 , 0 , 0 , E.POSTED_TOTAL_AMT , 0 , E.POSTED_TOTAL_AMT , 0 , 0 , 0 , 0 , 0 , CASE WHEN E.AFFECT_SPEND_OPTN = 'Y' THEN E.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP0_VW E WHERE E.KK_BUDG_TRANS_TYPE = '4' UNION ALL SELECT F.OPRID , F.INQUIRY , F.BUSINESS_UNIT , F.LEDGER_GROUP , F.ACCOUNT , F.DEPTID , %subrec(CF9A_AK_SBR, F) , %subrec(CFCC1_AK_SBR, F) , F.STATISTICS_CODE , 0 , 0 , 0 , 0 , 0 , F.POSTED_TOTAL_AMT , F.POSTED_TOTAL_AMT , 0 , 0 , 0 , 0 , 0 , CASE WHEN F.AFFECT_SPEND_OPTN = 'Y' THEN F.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP0_VW F WHERE F.KK_BUDG_TRANS_TYPE = '5' UNION ALL SELECT G.OPRID , G.INQUIRY , G.BUSINESS_UNIT , G.LEDGER_GROUP , G.ACCOUNT , G.DEPTID , %subrec(CF9A_AK_SBR, G) , %subrec(CFCC1_AK_SBR, G) , G.STATISTICS_CODE , 0 , 0 , 0 , 0 , 0 , 0 , 0 , G.POSTED_TOTAL_AMT , 0 , 0 , 0 , 0 , CASE WHEN G.AFFECT_SPEND_OPTN = 'Y' THEN G.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP1_VW G UNION ALL SELECT H.OPRID , H.INQUIRY , H.BUSINESS_UNIT , H.LEDGER_GROUP , H.ACCOUNT , H.DEPTID , %subrec(CF9A_AK_SBR, H) , %subrec(CFCC1_AK_SBR, H) , H.STATISTICS_CODE , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , H.POSTED_TOTAL_AMT , 0 , 0 , 0 , CASE WHEN H.AFFECT_SPEND_OPTN = 'Y' THEN H.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP2_VW H UNION ALL SELECT I.OPRID , I.INQUIRY , I.BUSINESS_UNIT , I.LEDGER_GROUP , I.ACCOUNT , I.DEPTID , %subrec(CF9A_AK_SBR, I) , %subrec(CFCC1_AK_SBR, I) , I.STATISTICS_CODE , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , I.POSTED_TOTAL_AMT , 0 , 0 , CASE WHEN N.KK_INCLUDE_PRE_ENC = '1' OR (N.KK_INCLUDE_PRE_ENC = '3' AND I.AFFECT_SPEND_OPTN = 'Y') THEN I.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP3_VW I , PS_BU_LED_GRP_TBL N WHERE N.BUSINESS_UNIT = I.BUSINESS_UNIT AND N.LEDGER_GROUP = I.LEDGER_GROUP UNION ALL SELECT J.OPRID , J.INQUIRY , J.BUSINESS_UNIT , J.LEDGER_GROUP , J.ACCOUNT , J.DEPTID , %subrec(CF9A_AK_SBR, J) , %subrec(CFCC1_AK_SBR, J) , J.STATISTICS_CODE , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , J.POSTED_TOTAL_AMT , 0 , CASE WHEN J.AFFECT_SPEND_OPTN = 'Y' THEN J.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP4_VW J UNION ALL SELECT K.OPRID , K.INQUIRY , K.BUSINESS_UNIT , K.LEDGER_GROUP , K.ACCOUNT , K.DEPTID , %subrec(CF9A_AK_SBR, K) , %subrec(CFCC1_AK_SBR, K) , K.STATISTICS_CODE , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , K.POSTED_TOTAL_AMT , 0 , CASE WHEN K.AFFECT_SPEND_OPTN = 'Y' THEN K.POSTED_TOTAL_AMT ELSE 0 END FROM PS_KK_OVW_DBP5_VW K

# 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: PC_BU_GL_VW

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

Prompt Table: KK_PROJ_ID_VW

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

Prompt Table: PROJ_ACT_FVW2

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

Prompt Table: PROJ_RESTYPE_FS

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

Prompt Table: KK_BP_ALL_VW

24 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
25 KK_BUDG_ORIG_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Original Budget Amount
26 KK_BUDG_ADJ_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budget Adjustment Amount
27 KK_BUDG_XADJ_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budget Transfer Adjustment Amt
28 KK_BUDG_XORIG_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budget Transfer Original Amt
29 KK_BUDG_CLOSE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Budget Closing Amount
30 KK_BAL_FORWARD Signed Number(28,3) DECIMAL(26,3) NOT NULL Balance Rolled Forward
31 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
32 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
33 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
34 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
35 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
36 AMT6 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount 6
37 AMT7 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount 7