|
SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.DESCR "ProjDescr" , A.SUMMARY_PRJ , A.EFF_STATUS , CO.PC_PROCESS_STATUS "PStatus" , X3.XLATLONGNAME "Proj PStatus" , B.PROJECT_TYPE , B.DESCR "ProjType" , A.START_DT "Proj StartDate" , PS.PROJECT_STATUS , PST.DESCR "Proj Status" , PM.PROJECT_MANAGER , PMV.NAME "Manager Name" , PM.PROJ_ROLE , PR.DESCR "ProjRole" , PCD.CONTRACT_NUM , CCH.DESCR "Contract" , PCD.CONTRACT_LINE_NUM , CCH.CONTRACT_TYPE , PCD.ACTIVITY_ID , PPA.DESCR "Activity" , PPA.EFF_STATUS , PAS.ACTIVITY_STATUS , PSTA.DESCR "Act Status" , CCH.CA_STATUS , CCH.CA_PROC_STATUS , CCH.SOLD_TO_CUST_ID , CUST.NAME1 "SoldToCust Name" , CUST.NAME1 "Cust Name" , DESCR254 "Description" , A.DTTM_STAMP , %Substring(%Cast(A.START_DT, Date, Character), 6, 2) , %Substring(%Cast(A.START_DT, Date, Character), 6, 2) FROM (((((((((((((((PS_PROJECT A LEFT OUTER JOIN PS_PROJ_TYPE_TBL B ON B.PROJECT_TYPE = A.PROJECT_TYPE) LEFT OUTER JOIN PS_PROJECT_DESCR PD ON A.BUSINESS_UNIT = PD.BUSINESS_UNIT AND A.PROJECT_ID = PD.PROJECT_ID) LEFT OUTER JOIN PS_PC_PROJ_COMP CO ON A.BUSINESS_UNIT = CO.BUSINESS_UNIT_PC AND A.PROJECT_ID = CO.PROJECT_ID_2) LEFT OUTER JOIN PS_PROJECT_STATUS PS ON A.BUSINESS_UNIT = PS.BUSINESS_UNIT AND A.PROJECT_ID = PS.PROJECT_ID) LEFT OUTER JOIN PS_PROJECT_MGR PM ON A.BUSINESS_UNIT = PM.BUSINESS_UNIT AND A.PROJECT_ID = PM.PROJECT_ID) LEFT OUTER JOIN PS_PROJ_STATUS_TBL PST ON PST.PROJECT_STATUS = PS.PROJECT_STATUS) LEFT OUTER JOIN PSXLATITEM X3 ON X3.FIELDNAME = 'PC_PROCESS_STATUS' AND X3.FIELDVALUE = CO.PC_PROCESS_STATUS AND X3.EFF_STATUS = 'A') LEFT OUTER JOIN PS_PROJ_ROLE PR ON PR.PROJ_ROLE = PM.PROJ_ROLE) LEFT OUTER JOIN PS_PROJ_MGR_VW PMV ON PMV.PROJECT_MANAGER = PM.PROJECT_MANAGER) LEFT OUTER JOIN PS_CA_DETAIL_PROJ PCD ON PCD.BUSINESS_UNIT_PC = A.BUSINESS_UNIT AND PCD.PROJECT_ID = A.PROJECT_ID) LEFT OUTER JOIN PS_PROJ_ACTIVITY PPA ON PPA.BUSINESS_UNIT = A.BUSINESS_UNIT AND PPA.PROJECT_ID = A.PROJECT_ID AND PPA.ACTIVITY_ID = PCD.ACTIVITY_ID) LEFT OUTER JOIN PS_PROJ_ACT_STATUS PAS ON PAS.BUSINESS_UNIT = A.BUSINESS_UNIT AND PAS.PROJECT_ID = A.PROJECT_ID AND PAS.ACTIVITY_ID = PCD.ACTIVITY_ID) LEFT OUTER JOIN PS_PROJ_STATUS_TBL PSTA ON PSTA.PROJECT_STATUS = PAS.ACTIVITY_STATUS) LEFT OUTER JOIN PS_CA_CONTR_HDR CCH ON CCH.CONTRACT_NUM = PCD.CONTRACT_NUM) LEFT OUTER JOIN PS_CUSTOMER CUST ON CUST.CUST_ID = CCH.SOLD_TO_CUST_ID) WHERE 1 = 1 AND A.EFF_STATUS <> 'T' AND ((B.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC C WHERE C.SETCNTRLVALUE = A.BUSINESS_UNIT AND C.RECNAME = 'PROJ_TYPE_TBL') OR B.SETID IS NULL) AND (B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_PROJ_TYPE_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.PROJECT_TYPE = B_ED.PROJECT_TYPE AND B_ED.EFFDT <= %CurrentDateIn) OR B.EFFDT IS NULL)) AND (PS.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_PROJECT_STATUS B_ED WHERE PS.BUSINESS_UNIT = B_ED.BUSINESS_UNIT AND PS.PROJECT_ID = B_ED.PROJECT_ID AND B_ED.EFFDT <= %CurrentDateIn) OR PS.EFFDT IS NULL) AND (PS.EFFSEQ = ( SELECT MAX(B_ES.EFFSEQ) FROM PS_PROJECT_STATUS B_ES WHERE PS.BUSINESS_UNIT = B_ES.BUSINESS_UNIT AND PS.PROJECT_ID = B_ES.PROJECT_ID AND PS.EFFDT = B_ES.EFFDT) OR PS.EFFSEQ IS NULL) AND (PM.EFFDT = ( SELECT MAX(E_ED.EFFDT) FROM PS_PROJECT_MGR E_ED WHERE PM.BUSINESS_UNIT = E_ED.BUSINESS_UNIT AND PM.PROJECT_ID = E_ED.PROJECT_ID AND E_ED.EFFDT <= %CurrentDateIn) OR PM.EFFDT IS NULL) AND ((PST.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC PST_ED WHERE PST_ED.SETCNTRLVALUE = A.BUSINESS_UNIT AND PST_ED.RECNAME = 'PROJ_STATUS_TBL') OR PST.SETID IS NULL) AND (PST.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_PROJ_STATUS_TBL C_ED WHERE PST.SETID = C_ED.SETID AND PST.PROJECT_STATUS = C_ED.PROJECT_STATUS AND C_ED.EFFDT <= %CurrentDateIn) OR PST.EFFDT IS NULL)) AND (PR.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC PR_ED WHERE PR_ED.SETCNTRLVALUE = A.BUSINESS_UNIT AND PR_ED.RECNAME = 'PROJ_ROLE') OR PR.SETID IS NULL) AND (CUST.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC CUST_ED WHERE CUST_ED.SETCNTRLVALUE = CCH.BUSINESS_UNIT AND CUST_ED.RECNAME = 'CUSTOMER') OR CUST.SETID IS NULL) AND (PAS.EFFDT = ( SELECT MAX(PAS_ED.EFFDT) FROM PS_PROJ_ACT_STATUS PAS_ED WHERE PAS_ED.BUSINESS_UNIT = PAS.BUSINESS_UNIT AND PAS_ED.PROJECT_ID = PAS.PROJECT_ID AND PAS.ACTIVITY_ID = PAS_ED.ACTIVITY_ID AND PAS_ED.EFFDT <= %CurrentDateIn) OR PAS.EFFDT IS NULL) AND (PAS.EFFSEQ = ( SELECT MAX(PAS_ED.EFFSEQ) FROM PS_PROJ_ACT_STATUS PAS_ED WHERE PAS_ED.BUSINESS_UNIT = PAS.BUSINESS_UNIT AND PAS_ED.PROJECT_ID = PAS.PROJECT_ID AND PAS.ACTIVITY_ID = PAS_ED.ACTIVITY_ID AND PAS.EFFDT = PAS_ED.EFFDT) OR PAS.EFFSEQ IS NULL)
|
BUSINESS_UNIT