LM_BO001_4_VW

(SQL View)
 Index Back

General Ledger Report view 2


SELECT G.OPRID , G.RUN_CNTL_ID , A.LM_SUBLEDGER_ID , B.LM_SUBLEDGER , B.LM_SUBLEDGER_DESCR , A.LM_SUBLEDG_GL_STAT , A.LM_SUBLEDGER_DT , E.NAME , A.LM_GL_AMOUNT , A.CURRENCY_CD , D.LM_ACT_CD , D.LM_ACT_ID , D.LM_CS_LONG_NM , A.LM_STTS , F.LM_JOURNAL_ID , F.LM_JOURNAL_DATE , P.XLATLONGNAME , Z.XLATLONGNAME , R.LANGUAGE_CD FROM PS_LM_SUBLEDGER A , PS_LM_SUBLEDGER_ID B , PS_LM_ENRLMT_VW C , PS_LM_ACT_CI_VW D , PS_LM_ACCTG_LN F , PS_LM_RUNCTL_FI G , PS_LM_PER_NAME_VW E , PSXLATITEM P , PS_PRCSRUNCNTL R , PSXLATITEM Z WHERE G.OPRID = R.OPRID AND G.RUN_CNTL_ID = R.RUN_CNTL_ID AND A.LM_DEBIT_CREDIT = 'DEB' AND A.LM_SUBLEDG_GL_STAT = 'POS' AND A.LM_SUBLEDGER_ID = B.LM_SUBLEDGER_ID AND B.LM_SUBLEDGER = G.LM_SUBLEDGER AND C.LM_ENRLMT_ID = A.LM_ENRLMT_ID AND D.LM_ACT_ID = C.LM_ACT_ID AND C.LM_PERSON_ID = E.LM_PERSON_ID AND A.LM_SUBLEDGER_ID = F.LM_SUBLEDGER_ID AND A.LM_SUBLEDGR_LN_NUM = F.LM_SUBLEDGR_LN_NUM AND ( %DatePart(A.LM_SUBLEDGER_DT) >= G.LM_EXPORT_DATE OR G.LM_EXPORT_DATE IS NULL) AND ( C.LM_PERSON_ID = G.LM_PERSON_ID OR 0 = G.LM_PERSON_ID) AND (( C.LM_ACT_ID = G.LM_ACT_ID) OR ( G.LM_ACT_ID = 0 AND G.LM_PRG_ID = 0)) AND ( F.LM_JOURNAL_ID = G.LM_JOURNAL_ID OR G.LM_JOURNAL_ID IS NULL OR G.LM_JOURNAL_ID = ' ' ) AND ( F.LM_JOURNAL_DATE >= G.LM_JOURNAL_DATE OR G.LM_JOURNAL_DATE IS NULL) AND P.EFFDT = ( SELECT MAX(P_ED.EFFDT) FROM PSXLATITEM P_ED WHERE P.FIELDNAME = P_ED.FIELDNAME AND P.FIELDVALUE = P_ED.FIELDVALUE AND P_ED.EFFDT <= %CurrentDateIn ) AND A.LM_STTS = P.FIELDVALUE AND P.FIELDNAME = 'LM_STTS' AND Z.EFFDT = ( SELECT MAX(Z_ED.EFFDT) FROM PSXLATITEM Z_ED WHERE Z.FIELDNAME = Z_ED.FIELDNAME AND Z.FIELDVALUE = Z_ED.FIELDVALUE AND Z_ED.EFFDT <= %CurrentDateIn ) AND A.LM_SUBLEDG_GL_STAT= %Substring(Z.FIELDVALUE,1,3) AND Z.FIELDNAME = 'LM_SUBLEDG_GL_STAT'

# 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 RUN_CNTL_ID Character(30) VARCHAR2(30) NOT NULL Run Control ID
3 LM_SUBLEDGER_ID Number(15,0) DECIMAL(15) NOT NULL Sub-Ledger ID. System Generated.
4 LM_SUBLEDGER Character(15) VARCHAR2(15) NOT NULL The name of the Sub-Ledger.
5 LM_SUBLEDGER_DESCR Character(50) VARCHAR2(50) NOT NULL Sub-Ledger Description
6 LM_SUBLEDG_GL_STAT Character(3) VARCHAR2(3) NOT NULL Subledger Flag is used on the ELM Subledger. It indicates if a row on the Subledger has already been sent to GL.
ERR=Error
OLD=History Item Not Posted
PEN=Pending
POS=Posted
UPD=Updated Since Posting
7 LM_SUBLEDGER_DT DateTime(26) TIMESTAMP SubLedger Date - Date and Time row was written to SubLedger
8 NAME Character(50) VARCHAR2(50) NOT NULL Name
9 LM_GL_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL GL Amount
10 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
11 LM_ACT_CD Character(30) VARCHAR2(30) NOT NULL Activity Code
12 LM_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID
13 LM_CS_LONG_NM Character(200) VARCHAR2(200) NOT NULL Long Name - Describes the long name of an object
14 LM_STTS Character(4) VARCHAR2(4) NOT NULL Enrollment Status
CANC=Dropped
COMP=Completed
DECL=Denied
ENRL=Enrolled
INCO=Not Completed
INPO=In-Progress
MACT=Moved to New Activity
NOTS=Not Started
PEAP=Pending Approval
PEPA=Pending Payment
PLAN=Planned
PPYA=Payment Approval
RQST=Learning Request
WAIV=Waived
WTLT=Waitlisted
15 LM_JOURNAL_ID Character(10) VARCHAR2(10) NOT NULL Identifies a journal entry, consisting of a header and one or more lines. The Journal ID itself does not have to be unique, but together with the journal business unit and journal date, it forms a unique journal identifier.
16 LM_JOURNAL_DATE Date(10) DATE Specifies the date the journal was created.
17 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
18 LM_SUB_GL_STAT_STR Character(30) VARCHAR2(30) NOT NULL Subledger Status
19 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code