PV_CNT_CTRL_VW

(SQL View)
 Index Back

Contract Control View


SELECT A.SETID ,A.CNTRCT_ID ,A.VERSION_NBR , B.BUSINESS_UNIT FROM PS_CNTRCT_HDR A LEFT OUTER JOIN PS_CNTRCT_DEFAULTS B ON A.SETID = B.SETID AND A.CNTRCT_ID = B.CNTRCT_ID AND A.VERSION_NBR = B.VERSION_NBR AND A.CNTRCT_CORP_FLG = 'N' WHERE EXISTS( SELECT 'X' FROM PS_CNTRCT_CONTROL CTRL WHERE CTRL.SETID=A.SETID AND ((CTRL.CTRL_BU_ACCESS = 'N' AND A.CONTROL_TYPE = 'BU') OR (CTRL.CTRL_CDOM_ACCESS = 'N' AND A.CONTROL_TYPE = 'CDOM') OR (CTRL.CTRL_SHIPTO_ACCESS = 'N' AND A.CONTROL_TYPE = 'SHIP'))) UNION SELECT A.SETID ,A.CNTRCT_ID ,A.VERSION_NBR , B.BUSINESS_UNIT FROM PS_CNTRCT_HDR A , PS_CNT_CTRL_TYPE B WHERE A.SETID = B.SETID AND A.CNTRCT_ID = B.CNTRCT_ID AND A.VERSION_NBR = B.VERSION_NBR AND A.CONTROL_TYPE = 'BU' AND A.CONTROL_TYPE = B.CONTROL_TYPE AND B.CNTRCT_ID_DST = ' ' AND EXISTS( SELECT 'X' FROM PS_CNTRCT_CONTROL CTRL WHERE CTRL.SETID=A.SETID AND CTRL.CTRL_BU_ACCESS = 'Y') UNION SELECT A.SETID ,A.CNTRCT_ID ,A.VERSION_NBR , '' FROM PS_CNTRCT_HDR A WHERE A.CONTROL_TYPE='SHIP' OR A.CONTROL_TYPE='CDOM' OR A.CONTROL_TYPE=' ' UNION SELECT A.SETID ,A.CNTRCT_ID ,A.VERSION_NBR , B.BUSINESS_UNIT FROM PS_CNTRCT_HDR_MFG A LEFT OUTER JOIN PS_CNTRCT_DEFAULTS B ON A.SETID = B.SETID AND A.CNTRCT_ID = B.CNTRCT_ID AND A.VERSION_NBR = B.VERSION_NBR AND A.CNTRCT_CORP_FLG = 'N' WHERE EXISTS( SELECT 'X' FROM PS_CNTRCT_CONTROL CTRL WHERE CTRL.SETID=A.SETID AND ((CTRL.CTRL_BU_ACCESS = 'N' AND A.CONTROL_TYPE = 'BU') OR (CTRL.CTRL_CDOM_ACCESS = 'N' AND A.CONTROL_TYPE = 'CDOM') OR (CTRL.CTRL_SHIPTO_ACCESS = 'N' AND A.CONTROL_TYPE = 'SHIP'))) UNION SELECT A.SETID ,A.CNTRCT_ID ,A.VERSION_NBR , B.BUSINESS_UNIT FROM PS_CNTRCT_HDR_MFG A , PS_CNT_CTRL_TYPE B WHERE A.SETID = B.SETID AND A.CNTRCT_ID = B.CNTRCT_ID AND A.VERSION_NBR = B.VERSION_NBR AND A.CONTROL_TYPE = 'BU' AND A.CONTROL_TYPE = B.CONTROL_TYPE AND B.CNTRCT_ID_DST = A.CNTRCT_ID_DST AND EXISTS( SELECT 'X' FROM PS_CNTRCT_CONTROL CTRL WHERE CTRL.SETID=A.SETID AND CTRL.CTRL_BU_ACCESS = 'Y') UNION SELECT A.SETID ,A.CNTRCT_ID ,A.VERSION_NBR , '' FROM PS_CNTRCT_HDR_MFG A WHERE A.CONTROL_TYPE='SHIP' OR A.CONTROL_TYPE='CDOM' OR A.CONTROL_TYPE=' '

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
3 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number
4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit