SSF_BILL_XREF

(SQL View)
 Index Back

Billing Corp - Student Xref Vw

This view maps the Corp Billing Line Item Type to the Contract Emplid's Item Type for use in printing Corporate Bills with contracts that use the Student Cross Reference field.

SELECT B.BUSINESS_UNIT , B.COMMON_ID , B.SA_ID_TYPE , B.ITEM_NBR , B.LINE_SEQ_NBR , C.ITEM_TYPE , D.DESCR , A.ITEM_TYPE , B.DESCR , A.CONTRACT_NUM , C.EMPLID FROM PS_ITEM_SF A , PS_ITEM_LINE_SF B , PS_ITEM_SF C , PS_ITEM_TYPE_TBL D , PS_BUS_UNIT_TBL_SF E , PS_TP_CONTRACT F WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.COMMON_ID = B.COMMON_ID AND A.SA_ID_TYPE = B.SA_ID_TYPE AND A.ITEM_NBR = B.ITEM_NBR AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.CONTRACT_EMPLID = C.COMMON_ID AND C.SA_ID_TYPE = 'P' AND B.SSF_ITEM_NBR_STCHG = C.ITEM_NBR AND D.SETID = E.INSTITUTION AND D.ITEM_TYPE = C.ITEM_TYPE AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_ITEM_TYPE_TBL D1 WHERE D1.SETID = D.SETID AND D1.ITEM_TYPE = D.ITEM_TYPE AND D1.EFFDT <= C.ITEM_EFFECTIVE_DT) AND D.EFF_STATUS = 'A' AND E.BUSINESS_UNIT = A.BUSINESS_UNIT AND A.BUSINESS_UNIT = F.BUSINESS_UNIT AND A.CONTRACT_NUM = F.CONTRACT_NUM AND F.SSF_REF_STDNT_CHRG = 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value
3 SA_ID_TYPE Character(1) VARCHAR2(1) NOT NULL ID Type
O=Organization
P=Person
4 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr
5 LINE_SEQ_NBR Number(3,0) SMALLINT NOT NULL Line Sequence Nbr
6 SSF_ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 SSF_ITEM_TYPE_OLD Character(12) VARCHAR2(12) NOT NULL Old Item Type
9 DESCR_EXT_ORG Character(30) VARCHAR2(30) NOT NULL Org Name
10 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
11 CONTRACT_EMPLID Character(11) VARCHAR2(11) NOT NULL Contract ID