LM_BO006_B1_VW

(SQL View)
 Index Back

For Payment Pending Report

For External Customer payment Pending Report

SELECT G.OPRID , G.RUN_CNTL_ID , C.LM_ORGANIZATION_ID , C.LM_ORG_DESCR , D.COUNTRY , D.ADDRESS1 , D.ADDRESS2 , D.ADDRESS3 , D.ADDRESS4 , D.CITY , D.NUM1 , D.NUM2 , D.STATE , D.POSTAL , A.NAME , F.LM_ACT_CD , F.LM_ACT_ID , F.LM_CS_LONG_NM , F.LM_START_DT , F.LM_END_DT , F.LM_EXT_PRICE , F.CURRENCY_CD , C.LM_ORG_TYPE , R.LANGUAGE_CD FROM PS_LM_ENRLMT_VW B , PS_LM_ORG_VW C , PS_LM_ORG_ADDR D , PS_LM_ACT_CI_VW F , PS_LM_RUNCTL_FI G , PS_LM_PER_NAME_VW A , PS_PRCSRUNCNTL R WHERE G.OPRID=R.OPRID AND G.RUN_CNTL_ID =R.RUN_CNTL_ID AND B.LM_STTS = 'PEPA' AND C.LM_ORG_TYPE = 'CUST' AND C.LM_ORGANIZATION_ID = B.LM_ORGANIZATION_ID AND C.LM_ORGANIZATION_ID = D.LM_ORGANIZATION_ID AND D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_LM_ORG_ADDR D_ED WHERE D.LM_ORGANIZATION_ID = D_ED.LM_ORGANIZATION_ID AND D_ED.EFFDT <= %CurrentDateIn) AND A.LM_PERSON_ID = B.LM_PERSON_ID AND F.LM_ACT_ID = B.LM_ACT_ID AND ( C.LM_ORGANIZATION_ID = G.LM_ORGANIZATION_ID OR 0 = G.LM_ORGANIZATION_ID) AND (( B.LM_ACT_ID = G.LM_ACT_ID) OR ( G.LM_ACT_ID = 0 AND G.LM_PRG_ID = 0)) AND (( G.LM_ACTIVITY_DT = 'S' AND ( F.LM_START_DT BETWEEN G.FROMDATE AND G.THRUDATE) OR ( G.FROMDATE IS NULL AND F.LM_START_DT <= G.THRUDATE) OR ( G.THRUDATE IS NULL AND F.LM_START_DT >= G.FROMDATE) OR ( G.FROMDATE IS NULL AND G.THRUDATE IS NULL)) OR ( G.LM_ACTIVITY_DT = 'E' AND ( F.LM_END_DT BETWEEN G.FROMDATE AND G.THRUDATE) OR ( G.FROMDATE IS NULL AND F.LM_END_DT <= G.THRUDATE) OR ( G.THRUDATE IS NULL AND F.LM_END_DT >= G.FROMDATE) OR ( G.FROMDATE IS NULL AND G.THRUDATE IS NULL)))

# 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_ORGANIZATION_ID Number(15,0) DECIMAL(15) NOT NULL Organization ID
4 LM_ORG_DESCR Character(50) VARCHAR2(50) NOT NULL Customer Description
5 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
6 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
7 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
8 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
9 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
10 CITY Character(30) VARCHAR2(30) NOT NULL City
11 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
12 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
13 STATE Character(6) VARCHAR2(6) NOT NULL State
14 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
15 NAME Character(50) VARCHAR2(50) NOT NULL Name
16 LM_ACT_CD Character(30) VARCHAR2(30) NOT NULL Activity Code
17 LM_ACT_ID Number(10,0) DECIMAL(10) NOT NULL Activity ID
18 LM_CS_LONG_NM Character(200) VARCHAR2(200) NOT NULL Long Name - Describes the long name of an object
19 LM_START_DT Date(10) DATE Start Date
20 LM_END_DT Date(10) DATE End Date
21 LM_EXT_PRICE Number(19,3) DECIMAL(18,3) NOT NULL External Learner - Price
22 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
23 LM_ORG_TYPE Character(15) VARCHAR2(15) NOT NULL Organization Type
24 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code