GM_OPPT_YJ_SRCH

(SQL View)
 Index Back

Proposal - gm_awd_setup search

Security check for deptid and emplid. Submitted proposals. Proposals that have not been awarded.

SELECT DISTINCT D.OPRID , A.BUSINESS_UNIT , A.PROPOSAL_ID , A.VERSION_ID , A.TITLE56 , A.EMPLID , A.CUST_ID , A.SALES_PERSON , A.SUBMIT_STATUS , A.DEPTID , A.PROPOSAL_FLG , A.CONTRACT_NUM , A.PPSL_TO_CNTRCT_FLG , A.CURRENT_VERSION FROM PS_GM_PROPOSAL A , PSOPRDEFN D , PS_GM_BU_PM_SETUP Q WHERE A.PROPOSAL_FLG = 'S' AND A.CURRENT_VERSION = 'Y' AND A.SUBMIT_STATUS IN ('DRAF','RDY', 'CMTD') AND A.PPSL_TO_CNTRCT_FLG IN ('N', 'P') AND A.BUSINESS_UNIT = Q.BUSINESS_UNIT AND (D.EMPLID = A.EMPLID OR Q.PM_SECURITY_IND = 'N' OR EXISTS ( SELECT 'X0A' FROM PS_GM_PM_SECTY_EID B WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.PROPOSAL_ID = A.PROPOSAL_ID AND B.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

Prompt Table: GM_PROP_PMPT_VW

4 VERSION_ID Character(7) VARCHAR2(7) NOT NULL Version ID
5 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: GM_ELIG_PI_VW

7 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

8 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

9 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
10 DEPTID2 Character(10) VARCHAR2(10) NOT NULL DEPTID2

Prompt Table: GM_DEPT_TBL_VW

11 PROPOSAL_FLG Character(1) VARCHAR2(1) NOT NULL Proposal Checkbox
G=Grants Proposal
I=Internal Proposal
S=Solicitation Proposal
12 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
13 PPSL_TO_CNTRCT_FLG Character(1) VARCHAR2(1) NOT NULL Proposal To Contract
D=Contract Generated
N=Not Generated
P=Pre-Sales Spending
14 CURRENT_VERSION Character(1) VARCHAR2(1) NOT NULL Current Version