LM_BO006_B2_VW

(SQL View)
 Index Back

For Payment Pending report

For External Customer Payment Pending Report

SELECT K.OPRID , K.RUN_CNTL_ID , H.LM_ORGANIZATION_ID , H.LM_ORG_DESCR , I.COUNTRY , I.ADDRESS1 , I.ADDRESS2 , I.ADDRESS3 , I.ADDRESS4 , I.CITY , I.NUM1 , I.NUM2 , I.STATE , I.POSTAL , J.NAME , E.LM_PRG_CD , E.LM_PRG_ID , E.LM_PRG_LONG_NM , %DateNull , %DateNull , E.LM_ACT_FEE , E.CURRENCY_CD , H.LM_ORG_TYPE , R.LANGUAGE_CD FROM PS_LM_PRG_ENRL_VW E , PS_LM_ORG_VW H , PS_LM_ORG_ADDR I , PS_LM_PER_NAME_VW J , PS_LM_RUNCTL_FI K , PS_PRCSRUNCNTL R WHERE K.OPRID=R.OPRID AND K.RUN_CNTL_ID =R.RUN_CNTL_ID AND E.LM_PRG_STTS = 'PEPA' AND H.LM_ORG_TYPE = 'CUST' AND H.LM_ORGANIZATION_ID = I.LM_ORGANIZATION_ID AND I.EFFDT = ( SELECT MAX(I_ED.EFFDT) FROM PS_LM_ORG_ADDR I_ED WHERE I.LM_ORGANIZATION_ID = I_ED.LM_ORGANIZATION_ID AND I_ED.EFFDT <= %CurrentDateIn ) AND J.LM_PERSON_ID = E.LM_PERSON_ID AND E.LM_ORGANIZATION_ID = H.LM_ORGANIZATION_ID AND ( E.LM_ORGANIZATION_ID = K.LM_ORGANIZATION_ID OR K.LM_ORGANIZATION_ID = 0) AND (( E.LM_PRG_ID = K.LM_PRG_ID) OR ( K.LM_PRG_ID = 0 AND K.LM_ACT_ID = 0))

# 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_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.
17 LM_PRG_ID Number(10,0) DECIMAL(10) NOT NULL Program ID is a unique identifier for curriculum and certification Programs.
18 LM_PRG_LONG_NM Character(200) VARCHAR2(200) NOT NULL Program Long Name
19 LM_START_DT Date(10) DATE Start Date
20 LM_END_DT Date(10) DATE End Date
21 LM_ACT_FEE Number(19,3) DECIMAL(18,3) NOT NULL Activity Fee -- The cost to the learner of enrolling in a class
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