COLL_LTRITM_VW

(SQL View)
 Index Back

Collection Letter Item View


SELECT A.SETID , A.TEMPLATE_CODE , B.SEQ_NO , A.MAILING_CD , A.ADDR_USAGE , A.NAME_TYPE , A.SERVICE_IMPACT , B.SCC_LETTER_CD , B.DAYS_IN_COLLECTION , B.ADMIN_FUNCTION , B.COMM_CATEGORY , B.COMM_CONTEXT , B.COMM_METHOD , B.DEPTID FROM PS_COLL_LTRTMP_TBL A , PS_COLL_LTRITM_TBL B WHERE A.SETID = B.SETID AND A.TEMPLATE_CODE = B.TEMPLATE_CODE AND A.EFFDT = B.EFFDT AND A.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_COLL_LTRTMP_TBL C WHERE C.SETID = A.SETID AND C.TEMPLATE_CODE = A.TEMPLATE_CODE AND C.EFFDT <= %CurrentDateIn) AND A.EFF_STATUS = 'A'

  • Parent record: COLL_LTRTMP_TBL

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Prompt Table: SP_SETID_NONVW

    2 TEMPLATE_CODE Character(10) VARCHAR2(10) NOT NULL Template Code
    3 SEQ_NO Number(2,0) SMALLINT NOT NULL Sequence Number
    4 MAILING_CD Character(1) VARCHAR2(1) NOT NULL Mailing Code
    C=Days in Collection
    D=Day of Month
    5 ADDR_USAGE Character(10) VARCHAR2(10) NOT NULL Address Usage
    6 NAME_TYPE Character(3) VARCHAR2(3) NOT NULL Type of Name

    Prompt Table: HCR_NM_TYP_I

    7 SERVICE_IMPACT Character(5) VARCHAR2(5) NOT NULL Service Impact
    8 SCC_LETTER_CD Character(3) VARCHAR2(3) NOT NULL Letter Code

    Prompt Table: SCC_STN_LTR_TBL

    9 DAYS_IN_COLLECTION Number(3,0) SMALLINT NOT NULL Days in Collections
    10 ADMIN_FUNCTION Character(4) VARCHAR2(4) NOT NULL Administrative Function
    11 COMM_CATEGORY Character(6) VARCHAR2(6) NOT NULL Communication Category
    12 COMM_CONTEXT Character(6) VARCHAR2(6) NOT NULL Communication Context
    13 COMM_METHOD Character(1) VARCHAR2(1) NOT NULL Communication Method
    B=Publication
    D=Document
    E=Email
    F=Fax
    L=Letter
    P=Personal Contact
    T=Telephone Call
    W=Web
    14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: HCR_DEPT_I