RC_REL_LABEL_VW

(SQL View)
 Index Back

Relationship labels

View to get the cases and its relationship in a relationship type.

SELECT DISTINCT a.case_id , b.relationship_type , CASE WHEN(a.case_id = b.case_id AND c.hierarchical_ind = 'Y') THEN ( SELECT DISTINCT %Upper(parent_lbl) FROM ps_rc_relation_tbl d WHERE c.relationship_type = d.relationship_type AND d.setid = ( SELECT setid FROM ps_SET_CNTRL_TBL WHERE setcntrlvalue = a.business_unit) AND d.parent_lbl <> ' ') WHEN(a.case_id = b.related_case_id AND c.hierarchical_ind = 'Y') THEN ( SELECT DISTINCT %Upper(child_lbl) FROM ps_rc_relation_tbl e WHERE c.relationship_type = e.relationship_type AND e.setid = ( SELECT setid FROM ps_SET_CNTRL_TBL WHERE setcntrlvalue = a.business_unit) AND e.child_lbl <> ' ') WHEN(a.case_id = b.case_id AND c.hierarchical_ind = 'N') THEN ( SELECT DISTINCT %Upper(equivalent_lbl) FROM ps_rc_relation_tbl f WHERE c.relationship_type = f.relationship_type AND f.setid = ( SELECT setid FROM ps_SET_CNTRL_TBL WHERE setcntrlvalue = a.business_unit) AND f.equivalent_lbl <> ' ') WHEN(a.case_id = b.related_case_id AND c.hierarchical_ind = 'N') THEN ( SELECT DISTINCT %Upper(equivalent_lbl) FROM ps_rc_relation_tbl g WHERE c.relationship_type = g.relationship_type AND g.setid = ( SELECT setid FROM ps_SET_CNTRL_TBL WHERE setcntrlvalue = a.business_unit) AND g.equivalent_lbl <> ' ') END, a.syncid, %DateTimeOut(a.syncdttm) FROM ps_rc_case a, PS_RC_RELATIONSHIP b, ps_rc_relation_tbl c WHERE(a.case_id = b.case_id OR a.case_id = b.related_case_id) AND b.relationship_type = c.relationship_type

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CASE_ID Number(15,0) DECIMAL(15) NOT NULL Case ID. This is a unique ID assigned to a case when it is saved. Unique ID is ensured by getting the next value from the auto-numbering table and updating the table.

Default Value: 999999999999999

2 RELATIONSHIP_TYPE Character(5) VARCHAR2(5) NOT NULL Case Relationship Type
3 LABELFIELDNAME Character(18) VARCHAR2(18) NOT NULL Relationship
4 SYNCID Number(10,0) DECIMAL(10) NOT NULL The Synchronization ID field stores a value generated by the sync processor. The value is used to identify the type of object that the sync processor is about to handle.
5 SYNCDTTM DateTime(26) TIMESTAMP Synchronization Last Update Date Time