LM_BO001_3_VW

(SQL View)
 Index Back

For GL Detail report

For GL Detail Report

SELECT M.OPRID , M.RUN_CNTL_ID , I.LM_SUBLEDGER_ID , J.LM_SUBLEDGER , J.LM_SUBLEDGER_DESCR , I.LM_SUBLEDG_GL_STAT , I.LM_SUBLEDGER_DT , L.NAME , I.LM_GL_AMOUNT , I.CURRENCY_CD , H.LM_PRG_CD , H.LM_PRG_ID , H.LM_PRG_LONG_NM , I.LM_PRG_STTS , K.LM_JOURNAL_ID , K.LM_JOURNAL_DATE , P.XLATLONGNAME , Z.XLATLONGNAME , R.LANGUAGE_CD FROM PS_LM_PRG_ENRL_VW H , PS_LM_SUBLEDGER I , PS_LM_SUBLEDGER_ID J , PS_LM_ACCTG_LN K , PS_LM_PER_NAME_VW L , PS_LM_RUNCTL_FI M , PSXLATITEM P , PS_PRCSRUNCNTL R , PSXLATITEM Z WHERE M.OPRID = R.OPRID AND M.RUN_CNTL_ID = R.RUN_CNTL_ID AND I.LM_DEBIT_CREDIT = 'DEB' AND I.LM_SUBLEDG_GL_STAT = 'POS' AND J.LM_SUBLEDGER = M.LM_SUBLEDGER AND I.LM_SUBLEDGER_ID = J.LM_SUBLEDGER_ID AND H.LM_PRG_REG_ID = I.LM_ENRLMT_ID AND H.LM_PERSON_ID = L.LM_PERSON_ID AND I.LM_SUBLEDGER_ID = K.LM_SUBLEDGER_ID AND I.LM_SUBLEDGR_LN_NUM = K.LM_SUBLEDGR_LN_NUM AND (%DatePart(I.LM_SUBLEDGER_DT) >= M.LM_EXPORT_DATE OR M.LM_EXPORT_DATE IS NULL) AND ( H.LM_PERSON_ID = M.LM_PERSON_ID OR M.LM_PERSON_ID = 0) AND ( K.LM_JOURNAL_ID = M.LM_JOURNAL_ID OR M.LM_JOURNAL_ID IS NULL OR M.LM_JOURNAL_ID = ' ') AND ( K.LM_JOURNAL_DATE >= M.LM_JOURNAL_DATE OR M.LM_JOURNAL_DATE IS NULL) AND (( H.LM_PRG_ID = M.LM_PRG_ID) OR ( M.LM_PRG_ID = 0 AND M.LM_ACT_ID = 0)) 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 I.LM_PRG_STTS = P.FIELDVALUE AND P.FIELDNAME = 'LM_PRG_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 I.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_PRG_CD Character(30) VARCHAR2(30) NOT NULL Program Code is a user defined field. This field allows duplicates and is not part of the primary key structure.
12 LM_PRG_ID Number(10,0) DECIMAL(10) NOT NULL Program ID is a unique identifier for curriculum and certification Programs.
13 LM_PRG_LONG_NM Character(200) VARCHAR2(200) NOT NULL Program Long Name
14 LM_PRG_STTS Character(4) VARCHAR2(4) NOT NULL Program Reg Status
CANC=Dropped
COMP=Completed
DECL=Decline
ENRL=Registered
EXPR=Expired
INCO=Not Completed
INPO=In-Progress
PEAP=Pending Approval
PEPA=Pending Payment
PLAN=Planned
PPYA=Payment Approval
REIS=Reissue
REVK=Revoked
WAIV=Waived
WARN=Warning
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