GM_WC_PSLMX_LVW(SQL View) |
Index Back |
---|---|
Manage ProposalsLanguage record for GM_WC_PPSLMX_VW |
SELECT DISTINCT OPR.OPRID , PSL.BUSINESS_UNIT , PSL.PROPOSAL_ID , PSL.VERSION_ID , PSLLNG.LANGUAGE_CD , PSLLNG.DESCR20A , PSLLNG.TITLE56 FROM PS_GM_PROPOSAL PSL , PS_GM_PROP_PROJ PSLP , PSOPRDEFN OPR , PS_GM_PROPOSAL_LNG PSLLNG WHERE %Join(COMMON_KEYS, GM_PROPOSAL PSL, GM_PROPOSAL_LNG PSLLNG) AND PSL.BUSINESS_UNIT = PSLP.BUSINESS_UNIT AND PSL.PROPOSAL_ID = PSLP.PROPOSAL_ID AND PSL.VERSION_ID = PSLP.VERSION_ID AND PSL.PROPOSAL_FLG = 'G' AND PSL.TMPLT_FLG = 'N' AND PSL.PPSL_TO_CNTRCT_FLG IN ('N', 'P') AND PSL.CONTINUATION_FLAG <> 'Y' AND (OPR.EMPLID = PSL.EMPLID OR EXISTS ( SELECT 'X1' FROM PS_GM_PROP_PROF PROF WHERE PROF.EMPLID = OPR.EMPLID AND PROF.BUSINESS_UNIT = PSLP.BUSINESS_UNIT AND PROF.PROPOSAL_ID = PSLP.PROPOSAL_ID AND PROF.VERSION_ID = PSLP.VERSION_ID AND PROF.SUB_PROP_NBR = PSLP.SUB_PROP_NBR AND PROF.PROF_ROLE_TYPE IN ('PI', 'CPI', 'AUTH', 'PAA') ) OR EXISTS ( SELECT 'X1' FROM PSTREENODE TRE , PS_GM_SEC_DEPT_OPR DEPT WHERE TRE.EFFDT = DEPT.TREE_EFFDT AND OPR.OPRID = DEPT.OPRID AND TRE.TREE_NAME = DEPT.TREE_NAME AND TRE.TREE_NODE = PSLP.DEPTID AND TRE.SETID = DEPT.TREE_SETID AND TRE.TREE_NODE_NUM BETWEEN DEPT.TREE_NODE_NUM AND DEPT.TREE_NODE_NUM_END AND DEPT.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X2' FROM PS_GM_SEC_DEPT_OPR DEPT1 WHERE DEPT1.OPRID = DEPT.OPRID AND DEPT1.ACCESS_CD = 'N' AND DEPT1.TREE_SETID = DEPT.TREE_SETID AND DEPT1.TREE_NAME = DEPT.TREE_NAME AND DEPT1.TREE_EFFDT = DEPT.TREE_EFFDT AND DEPT1.TREE_NODE_NUM <> DEPT.TREE_NODE_NUM AND TRE.TREE_NODE_NUM BETWEEN DEPT1.TREE_NODE_NUM AND DEPT1.TREE_NODE_NUM_END AND DEPT1.TREE_NODE_NUM BETWEEN DEPT.TREE_NODE_NUM AND DEPT.TREE_NODE_NUM_END ) ) OR EXISTS ( SELECT 'X1' FROM PSTREELEAF LF , PS_GM_SEC_DEPT_OPR DEPT WHERE LF.EFFDT = DEPT.TREE_EFFDT AND LF.TREE_NAME = DEPT.TREE_NAME AND OPR.OPRID = DEPT.OPRID AND PSLP.DEPTID BETWEEN LF.RANGE_FROM AND LF.RANGE_TO AND LF.SETID = DEPT.TREE_SETID AND LF.TREE_NODE_NUM BETWEEN DEPT.TREE_NODE_NUM AND DEPT.TREE_NODE_NUM_END AND DEPT.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X2' FROM PS_GM_SEC_DEPT_OPR DEPT1 WHERE DEPT1.OPRID = DEPT.OPRID AND DEPT1.ACCESS_CD = 'N' AND DEPT1.TREE_SETID = DEPT.TREE_SETID AND DEPT1.TREE_NAME = DEPT.TREE_NAME AND DEPT1.TREE_EFFDT = DEPT.TREE_EFFDT AND DEPT1.TREE_NODE_NUM <> DEPT.TREE_NODE_NUM AND LF.TREE_NODE_NUM BETWEEN DEPT1.TREE_NODE_NUM AND DEPT1.TREE_NODE_NUM_END AND DEPT1.TREE_NODE_NUM BETWEEN DEPT.TREE_NODE_NUM AND DEPT.TREE_NODE_NUM_END ) )) AND NOT EXISTS ( SELECT 'X' FROM PS_GM_PROPOSAL PSL1 WHERE PSL1.BUSINESS_UNIT = PSL.BUSINESS_UNIT AND PSL1.PROPOSAL_ID = PSL.PROPOSAL_ID AND PSL1.VERSION_ID <> PSL.VERSION_ID AND PSL1.PPSL_TO_CNTRCT_FLG IN ('D', 'P') ) AND PSL.VERSION_ID = ( SELECT MAX(PSL2.VERSION_ID) FROM PS_GM_PROPOSAL PSL2 WHERE PSL2.BUSINESS_UNIT = PSL.BUSINESS_UNIT AND PSL2.PROPOSAL_ID = PSL.PROPOSAL_ID ) |
# | 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
Prompt Table: SP_BU_CA_NONVW |
3 | PROPOSAL_ID | Character(25) | VARCHAR2(25) NOT NULL |
Proposal ID
Prompt Table: GM_PROP_ID_SRCH |
4 | VERSION_ID | Character(7) | VARCHAR2(7) NOT NULL | Version ID |
5 | LANGUAGE_CD | Character(3) | VARCHAR2(3) NOT NULL | Language Code |
6 | DESCR20A | Character(20) | VARCHAR2(20) NOT NULL | Short Description |
7 | TITLE56 | Character(56) | VARCHAR2(56) NOT NULL | TITLE56 |