SSF_DG_TREE_VW

(SQL View)
 Index Back

SF Payment Diagnostic View

This view is created to assist with pulling data for Diagnostic Plugins. This view should not be used for any other purpose.

SELECT DISTINCT A.SETID , B.TREE_NAME , B.TREE_NODE , Z.COMMON_ID , Z.SA_ID_TYPE , Z.BUSINESS_UNIT , M.CHARGE_PRIORITY , Z.ITEM_NBR FROM PSTREEDEFN A , PSTREENODE B , PSTREELEAF C , PS_ITEM_SF Z , PS_PMT_CHRG_PRIOR Y , PS_PMT_CHRG_TBL M WHERE M.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_REC D WHERE D.SETCNTRLVALUE = Z.BUSINESS_UNIT AND D.RECNAME = 'PMT_CHRG_TBL') AND M.EFFDT = ( SELECT MAX(T.EFFDT) FROM PS_PMT_CHRG_TBL T WHERE T.SETID = M.SETID AND T.CHARGE_PRIORITY = M.CHARGE_PRIORITY AND T.EFFDT <= %CurrentDateIn ) AND M.EFF_STATUS = 'A' AND A.TREE_NAME = M.TREE_NAME AND A.SETID = ( SELECT D1.SETID FROM PS_SET_CNTRL_TREE D1 WHERE D1.SETCNTRLVALUE = Z.BUSINESS_UNIT AND D1.TREE_NAME = M.TREE_NAME) AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PSTREEDEFN A1 WHERE A.SETID = A1.SETID AND A.TREE_NAME = A1.TREE_NAME AND A1.EFFDT <= %CurrentDateIn) AND A.VALID_TREE = 'Y' AND B.SETID = A.SETID AND B.TREE_NAME = A.TREE_NAME AND B.EFFDT = A.EFFDT AND C.SETID = B.SETID AND C.EFFDT = B.EFFDT AND C.TREE_NAME = B.TREE_NAME AND C.TREE_BRANCH = B.TREE_BRANCH AND C.TREE_NODE_NUM >= B.TREE_NODE_NUM AND C.TREE_NODE_NUM <= B.TREE_NODE_NUM_END AND Z.ITEM_TYPE >= C.RANGE_FROM AND Z.ITEM_TYPE <= C.RANGE_TO AND Y.SETID = M.SETID AND Y.CHARGE_PRIORITY = M.CHARGE_PRIORITY AND Y.EFFDT = M.EFFDT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
3 TREE_NODE Character(20) VARCHAR2(20) NOT NULL Node name
4 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value
5 SA_ID_TYPE Character(1) VARCHAR2(1) NOT NULL ID Type
O=Organization
P=Person
6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
7 CHARGE_PRIORITY Character(8) VARCHAR2(8) NOT NULL Charge Priority List
8 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr