GPCH_GL_E1SH_LG

(SQL View)
 Index Back

Balance for charged accounts

Balance for charged accounts within selected month.

SELECT G.ACCOUNT , A.LANGUAGE_CD , A.DESCR , G.CAL_RUN_ID , G.GROUPING_CODE , M.HPYP_CC_ID , %subrec(GP_GL_CHART_SBR, G) FROM PS_GP_GL_DATA G , PS_GL_ACCOUNT_LANG A , PS_BUS_UNIT_TBL_HR BU , PS_GP_CC_MAP M , PS_GP_CALENDAR C WHERE G.ACCOUNT = A.ACCOUNT AND G.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.DEFAULT_SETID = A.SETID AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_GL_ACCOUNT_LANG A1 WHERE A.SETID = A1.SETID AND A.ACCOUNT = A1.ACCOUNT AND A1.EFFDT <= G.POSTING_DATE ) AND G.GP_PAYGROUP = C.GP_PAYGROUP AND G.CAL_ID = C.CAL_ID AND G.PIN_CHART2_VAL = M.PIN_CHART2_VAL AND G.PAY_ENTITY = M.PAY_ENTITY AND G.BUSINESS_UNIT = M.BUSINESS_UNIT AND M.EFFDT = ( SELECT MAX(M1.EFFDT) FROM PS_GP_CC_MAP M1 WHERE M.PAY_ENTITY = M1.PAY_ENTITY AND M.BUSINESS_UNIT = M1.BUSINESS_UNIT AND M.HPYP_CNTRL_AREA = M1.HPYP_CNTRL_AREA AND M.PIN_CHART1_VAL = M1.PIN_CHART1_VAL AND M.PIN_CHART2_VAL = M1.PIN_CHART2_VAL AND M1.EFFDT <= C.CALC_THRU_DT ) GROUP BY G.ACCOUNT,A.LANGUAGE_CD, A.DESCR, G.CAL_RUN_ID ,G.GROUPING_CODE, M.HPYP_CC_ID, %subrec(GP_GL_CHART_SBR, G) HAVING SUM(G.CALC_RSLT_VAL) > 0.00 UNION SELECT G.ACCOUNT , A.LANGUAGE_CD , A.DESCR , G.CAL_RUN_ID , G.GROUPING_CODE , M.HPYP_CC_ID, %subrec(GP_GL_CHART_SBR, G) FROM PS_GP_GL_DATA G , PS_GL_ACCOUNT_LANG A , PS_BUS_UNIT_TBL_HR BU , PS_GP_CC_MAP M , PS_GP_CALENDAR C WHERE G.ACCOUNT = A.ACCOUNT AND G.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.DEFAULT_SETID = A.SETID AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_GL_ACCOUNT_LANG A1 WHERE A.SETID = A1.SETID AND A.ACCOUNT = A1.ACCOUNT AND A1.EFFDT <= G.POSTING_DATE ) AND G.GP_PAYGROUP = C.GP_PAYGROUP AND G.CAL_ID = C.CAL_ID AND G.PIN_CHART2_VAL = M.PIN_CHART2_VAL AND G.PAY_ENTITY = M.PAY_ENTITY AND G.BUSINESS_UNIT = M.BUSINESS_UNIT AND M.EFFDT = ( SELECT MAX(M1.EFFDT) FROM PS_GP_CC_MAP M1 WHERE M.PAY_ENTITY = M1.PAY_ENTITY AND M.BUSINESS_UNIT = M1.BUSINESS_UNIT AND M.HPYP_CNTRL_AREA = M1.HPYP_CNTRL_AREA AND M.PIN_CHART1_VAL = M1.PIN_CHART1_VAL AND M.PIN_CHART2_VAL = M1.PIN_CHART2_VAL AND M1.EFFDT <= C.CALC_THRU_DT ) GROUP BY G.ACCOUNT,A.LANGUAGE_CD, A.DESCR, G.CAL_RUN_ID ,G.GROUPING_CODE, M.HPYP_CC_ID, %subrec(GP_GL_CHART_SBR, G) HAVING SUM(G.CALC_RSLT_VAL) < 0.00

  • Related Language Record for GPCH_GL_E1SH_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
    2 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
    5 GROUPING_CODE Character(15) VARCHAR2(15) NOT NULL Identifier (name) used to group a number of elements (earnings or deductions) that can be treated equally (posted to the same account) for GL purposes.
    6 HPYP_CC_ID Character(12) VARCHAR2(12) NOT NULL Cost Center ID.
    7 PIN_CHART1_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    8 PIN_CHART2_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    9 PIN_CHART3_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value`
    10 PIN_CHART4_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    11 PIN_CHART5_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    12 PIN_CHART6_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    13 PIN_CHART7_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    14 PIN_CHART8_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    15 PIN_CHART9_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    16 PIN_CHART10_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    17 PIN_CHART11_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    18 PIN_CHART12_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    19 PIN_CHART13_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    20 PIN_CHART14_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    21 PIN_CHART15_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    22 PIN_CHART16_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    23 PIN_CHART17_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    24 PIN_CHART18_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    25 PIN_CHART19_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    26 PIN_CHART20_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
    27 PIN_CHART21_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value