GM_PM_ACTV_RSRC(SQL View) |
Index Back |
|---|---|
PM Activity Search RecDefnUsed as secured search record for Proposal. |
| SELECT DISTINCT D.OPRID , H.BUSINESS_UNIT , H.PROPOSAL_ID , H.VERSION_ID , H.SUB_PROP_NBR , H.BUDGET_ID , H.DESCR , A.EMPLID , A.CUST_ID , A.SALES_PERSON , A.SUBMIT_STATUS , A.DEPTID , A.CURRENT_VERSION FROM PS_GM_PROPOSAL A , PSOPRDEFN D , PS_GM_BU_PM_SETUP Q , PS_GM_BUD_HDR H WHERE A.BUSINESS_UNIT = Q.BUSINESS_UNIT AND A.PROPOSAL_FLG = 'S' AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.PROPOSAL_ID = H.PROPOSAL_ID AND A.VERSION_ID = H.VERSION_ID AND (D.EMPLID = A.EMPLID OR Q.PM_SECURITY_IND = 'N' OR EXISTS ( SELECT 'EPID' FROM PS_GM_PM_SECTY_EID K WHERE K.BUSINESS_UNIT = A.BUSINESS_UNIT AND K.PROPOSAL_ID = A.PROPOSAL_ID AND K.EMPLID = D.EMPLID) OR EXISTS ( SELECT 'X1A' FROM PSTREENODE E , PS_GM_PM_SDEPT_OPR S WHERE E.EFFDT = S.TREE_EFFDT AND D.OPRID = S.OPRID AND E.TREE_NAME = S.TREE_NAME AND E.TREE_NODE = A.DEPTID AND E.SETID = S.TREE_SETID AND E.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END AND S.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X1B' FROM PS_GM_PM_SDEPT_OPR S1 WHERE S1.OPRID = S.OPRID AND S1.ACCESS_CD = 'N' AND S1.TREE_SETID = S.TREE_SETID AND S1.TREE_NAME = S.TREE_NAME AND S1.TREE_EFFDT = S.TREE_EFFDT AND S1.TREE_NODE_NUM <> S.TREE_NODE_NUM AND E.TREE_NODE_NUM BETWEEN S1.TREE_NODE_NUM AND S1.TREE_NODE_NUM_END AND S1.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END)) OR EXISTS ( SELECT 'X2A' FROM PSTREELEAF F , PS_GM_PM_SDEPT_OPR S WHERE F.EFFDT = S.TREE_EFFDT AND F.TREE_NAME = S.TREE_NAME AND D.OPRID = S.OPRID AND A.DEPTID BETWEEN F.RANGE_FROM AND F.RANGE_TO AND F.SETID = S.TREE_SETID AND F.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END AND S.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X2B' FROM PS_GM_PM_SDEPT_OPR S1 WHERE S1.OPRID = S.OPRID AND S1.ACCESS_CD = 'N' AND S1.TREE_SETID = S.TREE_SETID AND S1.TREE_NAME = S.TREE_NAME AND S1.TREE_EFFDT = S.TREE_EFFDT AND S1.TREE_NODE_NUM <> S.TREE_NODE_NUM AND F.TREE_NODE_NUM BETWEEN S1.TREE_NODE_NUM AND S1.TREE_NODE_NUM_END AND S1.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END))) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Default Value: OPR_DEF_TBL_CA.BUSINESS_UNIT Prompt Table: SP_BU_CA_NONVW |
| 3 | PROPOSAL_ID |
Character(25) | VARCHAR2(25) NOT NULL |
Proposal ID
Default Value: NEXT Prompt Table: GM_PROP_PMPT_VW |
| 4 | VERSION_ID |
Character(7) | VARCHAR2(7) NOT NULL |
Version ID
Prompt Table: GM_OPVR_SRCH_VW |
| 5 | SUB_PROP_NBR |
Character(15) | VARCHAR2(15) NOT NULL | SUB_PROP_NBR |
| 6 | BUDGET_ID |
Character(15) | VARCHAR2(15) NOT NULL | BUDGET_ID |
| 7 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 8 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: GM_ELIG_PI_VW |
| 9 | CUST_ID | Character(15) | VARCHAR2(15) NOT NULL |
Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
Prompt Table: CUST_SLD_VW |
| 10 | SALES_PERSON | Character(8) | VARCHAR2(8) NOT NULL |
Specifies the personnel code for the person who works with a customer as a sales representative. This field is required for a customer that functions as a bill-to customer.
Prompt Table: SALES_PERSN_TBL |
| 11 | SUBMIT_STATUS_PM | Character(4) | VARCHAR2(4) NOT NULL |
Status
CMTD=03 - Committed CNCL=05 - Canceled DENY=04 - Denied DRAF=01 - Draft RDY=02 - Ready |
| 12 | DEPTID2 | Character(10) | VARCHAR2(10) NOT NULL |
DEPTID2
Prompt Table: GM_DEPT_TBL_VW |
| 13 | CURRENT_VERSION | Character(1) | VARCHAR2(1) NOT NULL |
Current Version
Y/N Table Edit |