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_1_VW(SQL View) |
Index Back |
|---|---|
Payment Update Report view 1 |
| SELECT LMRC.OPRID , LMRC.RUN_CNTL_ID , PNM.NAME , ORG.LM_ORGANIZATION_ID , ORG.LM_ORG_DESCR , ACT.LM_ACT_ID , CI.LM_CS_LONG_NM , SL.LM_ENRLMT_ID , SL.LM_PYMT_ID , SL.LM_SUBLEDGER_ID , SL.LM_SUBLEDGR_LN_NUM , SL.LM_SUBLEDG_GL_STAT , SLID.LM_SUBLEDGER , PYMT.LM_PO_ID , ' ' , PYMT.LM_PYMT_MTHD , PYMT.LM_TU_POOL_ID , ' ' , PYMT.EOEC_CCI_NUMBER , SL.CURRENCY_CD , PYMT.LM_CHECK_NO , SL.LM_GL_AMOUNT , SL.LM_DEBIT_CREDIT , XLAT.XLATLONGNAME , RC.LANGUAGE_CD FROM PS_LM_SUBLEDGER SL , PS_LM_SUBLEDGER_ID SLID , PS_LM_ENPYMT_VW PYMT , PS_LM_ENRLMT_VW D , PS_LM_PER_NAME_VW PNM , PS_LM_ACT ACT , PS_LM_DMTHD_TBL DM , PS_LM_DMTH_TYP_TBL DMTYP , PS_LM_CI_TBL CI , PS_LM_ORG_VW ORG , PS_LM_RUNCTL_FI LMRC , PSXLATITEM XLAT , PS_PRCSRUNCNTL RC WHERE LMRC.OPRID = RC.OPRID AND LMRC.RUN_CNTL_ID = RC.RUN_CNTL_ID AND SL.LM_DEBIT_CREDIT = 'DEB' AND SL.LM_ENRLMT_TYPE = 'ACT' AND SL.LM_ENRLMT_ID IN ( SELECT Q.LM_ENRLMT_ID FROM PS_LM_SUBLEDGER Q WHERE Q.LM_SUBLEDG_GL_STAT = 'UPD') AND PYMT.LM_PO_ID = 0 AND PYMT.LM_TU_POOL_ID = 0 AND SL.LM_SUBLEDGER_ID = SLID.LM_SUBLEDGER_ID AND PYMT.LM_ENRLMT_ID = SL.LM_ENRLMT_ID AND PYMT.LM_PYMT_ID = SL.LM_PYMT_ID AND PYMT.LM_ENRLMT_ID = D.LM_ENRLMT_ID AND PNM.LM_PERSON_ID = D.LM_PERSON_ID AND ACT.LM_ACT_ID = D.LM_ACT_ID AND DM.LM_DMTHD_TYPE_ID = DMTYP.LM_DMTHD_TYPE_ID AND %CurrentDateIn BETWEEN DM.EFFDT AND DM.LM_END_EFFDT AND DM.LM_DM_STATUS = '10' AND DM.LM_CI_ID = CI.LM_CI_ID AND DM.LM_CI_ID = ACT.LM_CI_ID AND %CurrentDateIn BETWEEN CI.EFFDT AND CI.LM_END_EFFDT AND CI.LM_CI_STATUS='10' AND ORG.LM_ORGANIZATION_ID = D.LM_ORGANIZATION_ID AND ( ORG.LM_ORGANIZATION_ID = LMRC.LM_ORGANIZATION_ID OR LMRC.LM_ORGANIZATION_ID = 0) AND (( %DatePart(SL.LM_SUBLEDGER_DT) BETWEEN LMRC.FROMDATE AND LMRC.THRUDATE) OR ( %DatePart(SL.LM_SUBLEDGER_DT) >= LMRC.FROMDATE AND LMRC.THRUDATE IS NULL) OR ( %DatePart(SL.LM_SUBLEDGER_DT) <= LMRC.THRUDATE AND LMRC.FROMDATE IS NULL) OR ( LMRC.FROMDATE IS NULL AND LMRC.THRUDATE IS NULL)) AND XLAT.EFFDT = ( SELECT MAX(P_ED.EFFDT) FROM PSXLATITEM P_ED WHERE XLAT.FIELDNAME = P_ED.FIELDNAME AND XLAT.FIELDVALUE = P_ED.FIELDVALUE AND P_ED.EFFDT <= %CurrentDateIn ) AND SL.LM_SUBLEDG_GL_STAT = XLAT.FIELDVALUE AND XLAT.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_ACT_ID | Number(10,0) | DECIMAL(10) NOT NULL | Activity ID |
| 7 | LM_CS_LONG_NM | Character(200) | VARCHAR2(200) NOT NULL | Long Name - Describes the long name of an object |
| 8 | LM_ENRLMT_ID | Number(10,0) | DECIMAL(10) NOT NULL | Enrollment ID |
| 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 |