CA_WC_OPPRJ_LVW

(SQL View)
 Index Back

Open Projects

To be used in Workcenter

SELECT HDR.CONTRACT_NUM ,DTL.CONTRACT_LINE_NUM ,PROJ.PROJECT_ID ,DTL.LANGUAGE_CD ,DTL.DESCR ,PROJL.DESCR ,CUST.NAME1 FROM PS_CA_CONTR_HDR HDR , PS_CUSTOMER_LANG CUST , PS_SET_CNTRL_GROUP GRP , PS_CA_DETAIL_LANG DTL , PS_CA_DETAIL_PROJ PROJ , PS_PROJECT_LANG PROJL , PS_PROJECT PROJ1 , PS_PROJECT_STATUS STA WHERE CUST.LANGUAGE_CD = DTL.LANGUAGE_CD AND DTL.LANGUAGE_CD = PROJL.LANGUAGE_CD AND HDR.SOLD_TO_CUST_ID = CUST.CUST_ID AND HDR.BUSINESS_UNIT = GRP.SETCNTRLVALUE AND GRP.REC_GROUP_ID = 'FS_23' AND GRP.SETID = CUST.SETID AND HDR.CA_RQST_SRC <> 'OND' AND HDR.CONTRACT_NUM = DTL.CONTRACT_NUM AND DTL.CONTRACT_NUM = PROJ.CONTRACT_NUM AND DTL.CONTRACT_LINE_NUM = PROJ.CONTRACT_LINE_NUM AND PROJ.PROJECT_ID=PROJ1.PROJECT_ID AND PROJ.PROJECT_ID=STA.PROJECT_ID AND PROJ1.BUSINESS_UNIT = STA.BUSINESS_UNIT AND PROJ1.PROJECT_ID =STA.PROJECT_ID AND %Join(COMMON_KEYS,PROJECT_LANG PROJL,PROJECT PROJ1) AND CA_PROC_STATUS = 'C' AND EFF_STATUS = 'A' AND STA.EFFDT = ( SELECT MAX(EFFDT) FROM PS_PROJECT_STATUS STA1 WHERE STA1.BUSINESS_UNIT = STA.BUSINESS_UNIT AND STA1.PROJECT_ID =STA.PROJECT_ID AND STA1.EFFDT <= %CurrentDateIn) AND STA.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_PROJECT_STATUS STA2 WHERE STA2.BUSINESS_UNIT = STA.BUSINESS_UNIT AND STA2.PROJECT_ID =STA.PROJECT_ID AND STA2.EFFDT = STA.EFFDT)

  • Related Language Record for CA_WC_OPNPRJ_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
    2 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num
    3 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    4 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    5 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    6 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    7 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1