RSF_ROLE_ADR_VW

(SQL View)
 Index Back

Role Base Address View


SELECT U.ROLE_TYPE_ID , A.BO_ID , A.PROFILE_CM_SEQ , A.CM_ID , A.BO_CM_START_DT , A.BO_CM_END_DT , U.PRIMARY_IND , A.CM_PURPOSE_TYPE_ID , A.CM_NICKNAME , P.DESCRSHORT , CM.COUNTRY , CM.ADDRESS1 , CM.ADDRESS2 , CM.ADDRESS3 , CM.ADDRESS4 , CM.CITY , CM.NUM1 , CM.NUM2 , CM.HOUSE_TYPE , CM.ADDR_FIELD1 , CM.ADDR_FIELD2 , CM.ADDR_FIELD3 , CM.COUNTY , CM.STATE , CM.POSTAL , CM.GEO_CODE , CM.IN_CITY_LIMIT , CM.TIMEZONE , CM.REGION_ID , CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_BO_CM_USE X5 WHERE X5.BO_ID = A.BO_ID AND X5.PROFILE_CM_SEQ = A.PROFILE_CM_SEQ AND X5.CM_USE_START_DT <= %CurrentDateIn AND X5.CM_USE_END_DT > %CurrentDateIn AND X5.ROLE_TYPE_ID IN(43 , 47)) THEN 'Y' ELSE 'N' END , CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_BO_CM_USE X6 WHERE X6.BO_ID = A.BO_ID AND X6.PROFILE_CM_SEQ = A.PROFILE_CM_SEQ AND X6.CM_USE_START_DT <= %CurrentDateIn AND X6.CM_USE_END_DT > %CurrentDateIn AND X6.ROLE_TYPE_ID IN(43 , 47) AND X6.PRIMARY_IND = 'Y') THEN 'Y' ELSE 'N' END , CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_BO_CM_USE X1 WHERE X1.BO_ID = A.BO_ID AND X1.PROFILE_CM_SEQ = A.PROFILE_CM_SEQ AND X1.CM_USE_START_DT <= %CurrentDateIn AND X1.CM_USE_END_DT > %CurrentDateIn AND X1.ROLE_TYPE_ID IN(41 , 45)) THEN 'Y' ELSE 'N' END , CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_BO_CM_USE X2 WHERE X2.BO_ID = A.BO_ID AND X2.PROFILE_CM_SEQ = A.PROFILE_CM_SEQ AND X2.CM_USE_START_DT <= %CurrentDateIn AND X2.CM_USE_END_DT > %CurrentDateIn AND X2.ROLE_TYPE_ID IN(41 , 45) AND X2.PRIMARY_IND = 'Y') THEN 'Y' ELSE 'N' END , CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_BO_CM_USE X3 WHERE X3.BO_ID = A.BO_ID AND X3.PROFILE_CM_SEQ = A.PROFILE_CM_SEQ AND X3.CM_USE_START_DT <= %CurrentDateIn AND X3.CM_USE_END_DT > %CurrentDateIn AND X3.ROLE_TYPE_ID IN(42 , 46)) THEN 'Y' ELSE 'N' END , CASE WHEN 0 < ( SELECT COUNT(*) FROM PS_BO_CM_USE X4 WHERE X4.BO_ID = A.BO_ID AND X4.PROFILE_CM_SEQ = A.PROFILE_CM_SEQ AND X4.CM_USE_START_DT <= %CurrentDateIn AND X4.CM_USE_END_DT > %CurrentDateIn AND X4.ROLE_TYPE_ID IN(42 , 46) AND X4.PRIMARY_IND = 'Y') THEN 'Y' ELSE 'N' END FROM PS_BO_CM A, PS_CM CM, PS_CM_PURP_TYPE P, PS_BO_CM_USE U WHERE A.BO_CM_START_DT <= %CurrentDateIn AND A.BO_CM_END_DT > %CurrentDateIn AND A.CM_ID = CM.CM_ID AND A.CM_TYPE_ID = 1 AND A.CM_TYPE_ID = P.CM_TYPE_ID AND A.CM_PURPOSE_TYPE_ID = P.CM_PURPOSE_TYPE_ID AND A.BO_ID = U.BO_ID AND A.PROFILE_CM_SEQ = U.PROFILE_CM_SEQ AND U.CM_USE_START_DT <= %CurrentDateIn AND U.CM_USE_END_DT > %CurrentDateIn AND U.ROLE_TYPE_ID IN (2,3, 9)

  • Related Language Record: RSF_ROLEADR_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 ROLE_TYPE_ID_WORK Number(12,0) DECIMAL(12) NOT NULL Stores ROLE_TYPE_ID for the customer
    2 BO_ID_WORK Signed Number(32,0) DECIMAL(31) NOT NULL Business Object ID
    3 PROFILE_CM_SEQ Number(31,0) DECIMAL(31) NOT NULL The Contact Method Profile ID is a key field that is used to reference the details of a Contact Method in various CM tables.
    4 CM_ID_ADDRESS Number(31,0) DECIMAL(31) NOT NULL Contact Method ID
    5 START_DT Date(10) DATE Start date field to store start date value.
    6 END_DT Date(10) DATE This field stores the end date values.
    7 PRIMARY_IND Character(1) VARCHAR2(1) NOT NULL Flag that indicates whether or not it is Primary
    N=No
    Y=Yes
    8 CM_PURPOSE_TYPE_ID Number(6,0) INTEGER NOT NULL Relationship Contact Method Purpose ID

    Prompt Table: CM_PURP_TYPE

    9 CM_NICKNAME Character(30) VARCHAR2(30) NOT NULL Nickname of a Contact Method
    10 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    11 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

    12 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    13 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    14 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    15 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    16 CITY Character(30) VARCHAR2(30) NOT NULL City
    17 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    18 NUM2 Character(4) VARCHAR2(4) NOT NULL Number 2
    19 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat Reference
    WW=House Trailer Reference
    20 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    21 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    22 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    23 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    24 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: %EDIT_STATE

    25 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    26 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    27 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    Default Value: Y

    28 TIMEZONE Character(9) VARCHAR2(9) NOT NULL Time Zone
    29 REGION_ID Character(15) VARCHAR2(15) NOT NULL This field stores the region id value.

    Prompt Table: RSF_REGION_VW

    30 BILL_TO_FLG_XLAT Character(1) VARCHAR2(1) NOT NULL Billing Address
    N=No
    Y=Yes
    31 PRIMARY_BILL_TO Character(1) VARCHAR2(1) NOT NULL Primary Bill To

    Y/N Table Edit

    32 SHIP_TO_FLG_XLAT Character(1) VARCHAR2(1) NOT NULL Shipping Address
    N=No
    Y=Yes
    33 PRIMARY_SHIP_TO Character(1) VARCHAR2(1) NOT NULL Primary Ship To
    34 SOLD_TO_FLG_XLAT Character(1) VARCHAR2(1) NOT NULL Sold To Address
    N=No
    Y=Yes
    35 PRIMARY_SOLD_TO Character(1) VARCHAR2(1) NOT NULL Primary Sold To