GPCH_GL_E1SH_VW

(SQL View)
 Index Back

Balance for charged accounts

Balance for charged accounts within selected month.

SELECT G.ACCOUNT , A.DESCR , G.CAL_RUN_ID , G.GROUPING_CODE , M.HPYP_CC_ID ,G.PIN_CHART1_VAL ,G.PIN_CHART2_VAL ,G.PIN_CHART3_VAL ,G.PIN_CHART4_VAL ,G.PIN_CHART5_VAL ,G.PIN_CHART6_VAL ,G.PIN_CHART7_VAL ,G.PIN_CHART8_VAL , SUM(G.CALC_RSLT_VAL) , 0 FROM PS_GP_GL_DATA G , PS_GL_ACCOUNT_TBL 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_TBL 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.DESCR, G.CAL_RUN_ID ,G.GROUPING_CODE, M.HPYP_CC_ID, G.PIN_CHART1_VAL ,G.PIN_CHART2_VAL ,G.PIN_CHART3_VAL ,G.PIN_CHART4_VAL ,G.PIN_CHART5_VAL ,G.PIN_CHART6_VAL ,G.PIN_CHART7_VAL ,G.PIN_CHART8_VAL HAVING SUM(G.CALC_RSLT_VAL) > 0.00 UNION SELECT G.ACCOUNT , A.DESCR , G.CAL_RUN_ID , G.GROUPING_CODE , M.HPYP_CC_ID ,G.PIN_CHART1_VAL ,G.PIN_CHART2_VAL ,G.PIN_CHART3_VAL ,G.PIN_CHART4_VAL ,G.PIN_CHART5_VAL ,G.PIN_CHART6_VAL ,G.PIN_CHART7_VAL ,G.PIN_CHART8_VAL , 0 , SUM(G.CALC_RSLT_VAL) FROM PS_GP_GL_DATA G , PS_GL_ACCOUNT_TBL 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_TBL 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.DESCR, G.CAL_RUN_ID ,G.GROUPING_CODE, M.HPYP_CC_ID, G.PIN_CHART1_VAL ,G.PIN_CHART2_VAL ,G.PIN_CHART3_VAL ,G.PIN_CHART4_VAL ,G.PIN_CHART5_VAL ,G.PIN_CHART6_VAL ,G.PIN_CHART7_VAL ,G.PIN_CHART8_VAL HAVING SUM(G.CALC_RSLT_VAL) < 0.00

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
2 DESCR Character(30) VARCHAR2(30) NOT NULL Description
3 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
4 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.
5 HPYP_CC_ID Character(12) VARCHAR2(12) NOT NULL Cost Center ID.
6 PIN_CHART1_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
7 PIN_CHART2_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
8 PIN_CHART3_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value`
9 PIN_CHART4_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
10 PIN_CHART5_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
11 PIN_CHART6_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
12 PIN_CHART7_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
13 PIN_CHART8_VAL Character(15) VARCHAR2(15) NOT NULL pin chart field value
14 GPCH_RP_AMOUNT1 Signed Number(20,6) DECIMAL(18,6) NOT NULL Rate Result Value
15 GPCH_RP_AMOUNT2 Signed Number(20,6) DECIMAL(18,6) NOT NULL Rate Result Value