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_5_VW(SQL View) |
Index Back |
|---|---|
Payment Update Report view 5 |
| SELECT RCFI.OPRID , RCFI.RUN_CNTL_ID , PERNM.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 , ENPYMT.LM_PO_ID , ' ' , ENPYMT.LM_PYMT_MTHD , ENPYMT.LM_TU_POOL_ID , TUPOOL.LM_TU_POOL_NAME , ENPYMT.EOEC_CCI_NUMBER , SL.CURRENCY_CD , ENPYMT.LM_CHECK_NO , SL.LM_GL_AMOUNT , SL.LM_DEBIT_CREDIT , XLAT.XLATLONGNAME , PRC.LANGUAGE_CD FROM PS_LM_SUBLEDGER SL , PS_LM_SUBLEDGER_ID SLID , PS_LM_ENPYMT_VW ENPYMT , PS_LM_ENRLMT_VW ENRLMT , PS_LM_PER_NAME_VW PERNM , 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 RCFI , PS_LM_TU_POOL_VW TUPOOL , PSXLATITEM XLAT , PS_PRCSRUNCNTL PRC WHERE RCFI.OPRID = PRC.OPRID AND RCFI.RUN_CNTL_ID =PRC.RUN_CNTL_ID AND SL.LM_DEBIT_CREDIT = 'DEB' AND SL.LM_ENRLMT_TYPE = 'ACT' AND ENPYMT.LM_PYMT_MTHD = 'TU' AND SL.LM_ENRLMT_ID IN ( SELECT SQSL.LM_ENRLMT_ID FROM PS_LM_SUBLEDGER SQSL WHERE SQSL.LM_SUBLEDG_GL_STAT = 'UPD') AND SL.LM_SUBLEDGER_ID = SLID.LM_SUBLEDGER_ID AND ENPYMT.LM_ENRLMT_ID = SL.LM_ENRLMT_ID AND ENPYMT.LM_PYMT_ID = SL.LM_PYMT_ID AND ENPYMT.LM_ENRLMT_ID = ENRLMT.LM_ENRLMT_ID AND PERNM.LM_PERSON_ID = ENRLMT.LM_PERSON_ID AND ACT.LM_ACT_ID = ENRLMT.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 ENPYMT.LM_TU_POOL_ID = TUPOOL.LM_TU_POOL_ID AND ORG.LM_ORGANIZATION_ID = ENRLMT.LM_ORGANIZATION_ID AND ( ENRLMT.LM_ORGANIZATION_ID = RCFI.LM_ORGANIZATION_ID OR RCFI.LM_ORGANIZATION_ID = 0) AND (( %DatePart(SL.LM_SUBLEDGER_DT) BETWEEN RCFI.FROMDATE AND RCFI.THRUDATE) OR ( %DatePart(SL.LM_SUBLEDGER_DT) >= RCFI.FROMDATE AND RCFI.THRUDATE IS NULL) OR ( %DatePart(SL.LM_SUBLEDGER_DT) <= RCFI.THRUDATE AND RCFI.FROMDATE IS NULL) OR ( RCFI.FROMDATE IS NULL AND RCFI.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 |