OPRIDRUN_CNTL_IDERR=Error
OLD=History Item Not Posted
PEN=Pending
POS=Posted
UPD=Updated Since Posting
CASH=Cash
CHCK=Check
CHGB=ChargeBack
CRCD=Credit Card
NOAP=Not Applicable
PO=Purchase Order
SEL=Select...
TU=Training Units
CRD=Credit
DEB=Debit
LM_BO002_2_VW(SQL View) |
Index Back |
|---|---|
Payment Update Report view 2 |
| SELECT O.OPRID , O.RUN_CNTL_ID , M.NAME , N.LM_ORGANIZATION_ID , N.LM_ORG_DESCR , L.LM_PRG_ID , L.LM_PRG_LONG_NM , L.LM_PRG_REG_ID , K.LM_PYMT_ID , G.LM_SUBLEDGER_ID , G.LM_SUBLEDGR_LN_NUM , G.LM_SUBLEDG_GL_STAT , J.LM_SUBLEDGER , K.LM_PO_ID , ' ' , K.LM_PYMT_MTHD , K.LM_TU_POOL_ID , ' ' , K.EOEC_CCI_NUMBER , G.CURRENCY_CD , K.LM_CHECK_NO , G.LM_GL_AMOUNT , G.LM_DEBIT_CREDIT , P.XLATLONGNAME , R.LANGUAGE_CD FROM PS_LM_SUBLEDGER G , PS_LM_SUBLEDGER_ID J , PS_LM_ENPYMT_VW K , PS_LM_PRG_ENRL_VW L , PS_LM_PER_NAME_VW M , PS_LM_ORG_VW N , PS_LM_RUNCTL_FI O , PSXLATITEM P , PS_PRCSRUNCNTL R WHERE O.OPRID=R.OPRID AND O.RUN_CNTL_ID =R.RUN_CNTL_ID AND G.LM_DEBIT_CREDIT = 'DEB' AND G.LM_ENRLMT_TYPE <> 'ACT' AND G.LM_ENRLMT_ID IN ( SELECT P.LM_ENRLMT_ID FROM PS_LM_SUBLEDGER P WHERE P.LM_SUBLEDG_GL_STAT = 'UPD') AND K.LM_PO_ID = 0 AND K.LM_TU_POOL_ID = 0 AND G.LM_SUBLEDGER_ID = J.LM_SUBLEDGER_ID AND K.LM_PYMT_ID = G.LM_PYMT_ID AND K.LM_ENRLMT_ID = G.LM_ENRLMT_ID AND G.LM_ENRLMT_ID = L.LM_PRG_REG_ID AND G.LM_PERSON_ID = M.LM_PERSON_ID AND L.LM_ORGANIZATION_ID = N.LM_ORGANIZATION_ID AND ( L.LM_ORGANIZATION_ID = O.LM_ORGANIZATION_ID OR O.LM_ORGANIZATION_ID = 0) AND (( %DATEPART(G.LM_SUBLEDGER_DT) BETWEEN O.FROMDATE AND O.THRUDATE) OR ( %DATEPART(G.LM_SUBLEDGER_DT) >= O.FROMDATE AND O.THRUDATE IS NULL) OR ( %DATEPART(G.LM_SUBLEDGER_DT) <= O.THRUDATE AND O.FROMDATE IS NULL) OR ( O.FROMDATE IS NULL AND O.THRUDATE 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 G.LM_SUBLEDG_GL_STAT = P.FIELDVALUE AND P.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 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 4 | LM_ORGANIZATION_ID | Number(15,0) | DECIMAL(15) NOT NULL | Organization ID |
| 5 | LM_ORG_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Customer Description |
| 6 | LM_PRG_ID | Number(10,0) | DECIMAL(10) NOT NULL | Program ID is a unique identifier for curriculum and certification Programs. |
| 7 | LM_PRG_LONG_NM | Character(200) | VARCHAR2(200) NOT NULL | Program Long Name |
| 8 | LM_PRG_REG_ID | Number(10,0) | DECIMAL(10) NOT NULL | The Program Registration ID is a unique identifier for a learner's program enrollment. |
| 9 | LM_PYMT_ID | Number(10,0) | DECIMAL(10) NOT NULL | Payment ID |
| 10 | LM_SUBLEDGER_ID | Number(15,0) | DECIMAL(15) NOT NULL | Sub-Ledger ID. System Generated. |
| 11 | LM_SUBLEDGR_LN_NUM | Number(20,0) | DECIMAL(20) NOT NULL | Subledger Line Number |
| 12 | 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 |
| 13 | LM_SUBLEDGER | Character(15) | VARCHAR2(15) NOT NULL | The name of the Sub-Ledger. |
| 14 | LM_PO_ID | Number(10,0) | DECIMAL(10) NOT NULL | Purchase Order Id. Unique key for purchase orders. |
| 15 | LM_PO_NAME | Character(20) | VARCHAR2(20) NOT NULL | PO Number field in the PO table. |
| 16 | LM_PYMT_MTHD | Character(4) | VARCHAR2(4) NOT NULL |
Payment Method. The payment method can be ChargeBack, Training Units
CASH=Cash CHCK=Check CHGB=ChargeBack CRCD=Credit Card NOAP=Not Applicable PO=Purchase Order SEL=Select... TU=Training Units |
| 17 | LM_TU_POOL_ID | Number(10,0) | DECIMAL(10) NOT NULL | Training Unit Pool No. Unique key for Training Unit Pool Record. |
| 18 | LM_TU_POOL_NAME | Character(20) | VARCHAR2(20) NOT NULL | Training Unit Pool |
| 19 | EOEC_CCI_NUMBER | Character(200) | VARCHAR2(200) NOT NULL | Use in the Credit Card Number Capture Process |
| 20 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 21 | LM_CHECK_NO | Character(15) | VARCHAR2(15) NOT NULL | Check No used for payment |
| 22 | LM_GL_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | GL Amount |
| 23 | LM_DEBIT_CREDIT | Character(3) | VARCHAR2(3) NOT NULL |
Debit or Credit Indicator
CRD=Credit DEB=Debit |
| 24 | XLATLONGNAME | Character(30) | VARCHAR2(30) NOT NULL | Translate Long Name |
| 25 | LANGUAGE_CD | Character(3) | VARCHAR2(3) NOT NULL | Language Code |