FND Design Data [Home] [Help]

View: PA_FCST_PROJECT_LIST_V

Product: PA - Projects
Description: PA_FCST_PROJECT_LIST_V is a view that shows forecasting periods
Implementation/DBA Data: ViewAPPS.PA_FCST_PROJECT_LIST_V
View Text

SELECT PPA.PROJECT_ID PROJECT_ID
, PPA.NAME PROJECT_NAME
, PPA.SEGMENT1 PROJECT_NUMBER
, SUBSTR(PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PPA.PROJECT_ID)
, 1
, 45) CUSTOMER_NAME
, PARTIES.RESOURCE_SOURCE_ID PERSON_ID
, PEOPLE.FULL_NAME PERSON_NAME
, PPA.PROJFUNC_CURRENCY_CODE PROJECT_CURRENCY_CODE
, PBM.PROBABILITY_PERCENTAGE PROBABILITY_PERCENTAGE
, PPA.PROBABILITY_MEMBER_ID PROBABILITY_MEMBER_ID
, PPA.CARRYING_OUT_ORGANIZATION_ID CARRYING_OUT_ORGANIZATION_ID
, PPA.ORG_ID ORG_ID
, PPA.PROJECT_TYPE PROJECT_TYPE
, PPA.PROJECT_STATUS_CODE PROJECT_STATUS_CODE
, PPA.START_DATE START_DATE
, PPA.COMPLETION_DATE COMPLETION_DATE
FROM PA_PROJECTS_ALL PPA
, PA_PROBABILITY_MEMBERS PBM
, FND_GRANTS FG
, FND_OBJECTS FO
, FND_MENUS FM
, PER_ALL_PEOPLE_F PEOPLE
, PA_PROJECT_PARTIES PARTIES
, ( SELECT PA_FCST_GLOBAL.ISCROSSPROJECTVIEWUSER ISCROSSPROJFLG
FROM DUAL) PASEC
, ( SELECT PA_FCST_GLOBAL.GETPROJECTNUMBER PROJECT_NUMBER
, PA_FCST_GLOBAL.GETPROJECTNAME PROJECT_NAME
, PA_FCST_GLOBAL.GETPROJTYPE PROJECT_TYPE
, PA_FCST_GLOBAL.GETPROJECTORGID PROJECT_ORG_ID
, PA_FCST_GLOBAL.GETPROJECTORGNAME PROJECT_ORG_NAME
, PA_FCST_GLOBAL.GETPROJSTATUSCODE PROJECT_STATUS_CODE
, PA_FCST_GLOBAL.GETPROJECTSTARTDATE PROJECT_START_DATE
, PA_FCST_GLOBAL.GETPROJECTSTARTDATEOPT PROJECT_START_DATE_OPT
, PA_FCST_GLOBAL.GETPROJECTCOMPDATE PROJECT_COMP_DATE
, PA_FCST_GLOBAL.GETPROJECTCOMPDATEOPT PROJECT_COMP_DATE_OPT
, PA_FCST_GLOBAL.GETPROJECTMANGERNAME PROJECT_MANAGER_NAME
, PA_FCST_GLOBAL.GETPROJECTMANGERID PROJECT_MANAGER_ID
, PA_FCST_GLOBAL.GETPROJECTCUSTOMERNAME PROJECT_CUSTOMER_NAME
, PA_FCST_GLOBAL.GETPAGEFIRSTFLAG PAGE_FIRST_FLAG
, PA_FCST_GLOBAL.GETPERIODSETNAME PERIOD_SET_NAME
FROM DUAL) FILTERBY
, PA_IMPLEMENTATIONS_ALL PIA
, GL_SETS_OF_BOOKS SOB
WHERE PASEC.ISCROSSPROJFLG = 'N'
AND FG.GRANTEE_KEY = (SELECT WR.NAME
FROM WF_ROLES WR
WHERE WR.ORIG_SYSTEM = 'HZ_PARTY'
AND WR.ORIG_SYSTEM_ID = PEOPLE.PARTY_ID
AND ROWNUM = 1)
AND FG.GRANTEE_TYPE = 'USER'
AND PPA.CARRYING_OUT_ORGANIZATION_ID = FG.INSTANCE_PK1_VALUE
AND FG.OBJECT_ID = FO.OBJECT_ID
AND FM.MENU_NAME = 'PA_PRM_FCST_AUTH'
AND FG.MENU_ID = FM.MENU_ID
AND FO.OBJ_NAME = 'ORGANIZATION'
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND TRUNC(SYSDATE) >= TRUNC(FG.START_DATE)
AND TRUNC(SYSDATE) <= TRUNC(NVL(FG.END_DATE
, SYSDATE+1))
AND NVL(PIA.ORG_ID
, -99) = NVL(PPA.ORG_ID
, -99)
AND PIA.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND SOB.PERIOD_SET_NAME = FILTERBY.PERIOD_SET_NAME
AND PPA.TEMPLATE_FLAG <> 'Y'
AND PPA.PROBABILITY_MEMBER_ID = PBM.PROBABILITY_MEMBER_ID(+)
AND PARTIES.PROJECT_ROLE_ID (+) = 1
AND PARTIES.PROJECT_ID (+) = PPA.PROJECT_ID
AND SYSDATE BETWEEN PARTIES.START_DATE_ACTIVE(+)
AND NVL(PARTIES.END_DATE_ACTIVE(+)
, SYSDATE)
AND PARTIES.RESOURCE_SOURCE_ID = PEOPLE.PERSON_ID (+)
AND SYSDATE BETWEEN PEOPLE.EFFECTIVE_START_DATE (+)
AND PEOPLE.EFFECTIVE_END_DATE (+)
AND DECODE(FILTERBY.PAGE_FIRST_FLAG
, 'Y'
, 'N'
, 'N') = FILTERBY.PAGE_FIRST_FLAG
AND DECODE(FILTERBY.PROJECT_NUMBER
, 'ALL'
, 'ALL'
, PPA.SEGMENT1) LIKE '%'||FILTERBY.PROJECT_NUMBER||'%'
AND DECODE(FILTERBY.PROJECT_NAME
, 'XXXXXXXXXXXXXXX'
, 'XXXXXXXXXXXXXXX'
, PPA.NAME) LIKE '%'||FILTERBY.PROJECT_NAME||'%'
AND DECODE(FILTERBY.PROJECT_TYPE
, 'ALL'
, 'ALL'
, PPA.PROJECT_TYPE) = FILTERBY.PROJECT_TYPE
AND DECODE(FILTERBY.PROJECT_ORG_ID
, -99
, -99
, PPA.CARRYING_OUT_ORGANIZATION_ID) = FILTERBY.PROJECT_ORG_ID
AND DECODE(FILTERBY.PROJECT_STATUS_CODE
, 'ALL'
, 'ALL'
, PPA.PROJECT_STATUS_CODE) = FILTERBY.PROJECT_STATUS_CODE
AND DECODE(FILTERBY.PROJECT_MANAGER_NAME
, 'XXXXXXXXXXXXXXX'
, 'XXXXXXXXXXXXXXX'
, PEOPLE.FULL_NAME) LIKE '%'||FILTERBY.PROJECT_MANAGER_NAME||'%'
AND DECODE(FILTERBY.PROJECT_MANAGER_ID
, NULL
, -99
, PARTIES.RESOURCE_SOURCE_ID) = NVL(FILTERBY.PROJECT_MANAGER_ID
, -99)
AND DECODE(FILTERBY.PROJECT_CUSTOMER_NAME
, 'XXXXXXXXXXXXXXX'
, 'XXXXXXXXXXXXXXX'
, SUBSTR(PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PPA.PROJECT_ID)
, 1
, 45)) LIKE '%'|| FILTERBY.PROJECT_CUSTOMER_NAME||'%' UNION SELECT PPA.PROJECT_ID PROJECT_ID
, PPA.NAME PROJECT_NAME
, PPA.SEGMENT1 PROJECT_NUMBER
, SUBSTR(PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PPA.PROJECT_ID)
, 1
, 45) CUSTOMER_NAME
, PARTIES.RESOURCE_SOURCE_ID PERSON_ID
, PEOPLE.FULL_NAME PERSON_NAME
, PPA.PROJFUNC_CURRENCY_CODE PROJECT_CURRENCY_CODE
, PBM.PROBABILITY_PERCENTAGE PROBABILITY_PERCENTAGE
, PPA.PROBABILITY_MEMBER_ID PROBABILITY_MEMBER_ID
, PPA.CARRYING_OUT_ORGANIZATION_ID CARRYING_OUT_ORGANIZATION_ID
, PPA.ORG_ID ORG_ID
, PPA.PROJECT_TYPE PROJECT_TYPE
, PPA.PROJECT_STATUS_CODE PROJECT_STATUS_CODE
, PPA.START_DATE START_DATE
, PPA.COMPLETION_DATE COMPLETION_DATE
FROM PA_PROJECTS_ALL PPA
, PA_PROJECT_PLAYERS PPP
, PA_PROBABILITY_MEMBERS PBM
, PER_ALL_PEOPLE_F PEOPLE
, PA_PROJECT_PARTIES PARTIES
, ( SELECT PA_FCST_GLOBAL.ISCROSSPROJECTVIEWUSER ISCROSSPROJFLG
, PA_UTILS.GETEMPIDFROMUSER( FND_GLOBAL.USER_ID ) PPID
FROM DUAL) PASEC
, ( SELECT PA_FCST_GLOBAL.GETPROJECTNUMBER PROJECT_NUMBER
, PA_FCST_GLOBAL.GETPROJECTNAME PROJECT_NAME
, PA_FCST_GLOBAL.GETPROJTYPE PROJECT_TYPE
, PA_FCST_GLOBAL.GETPROJECTORGID PROJECT_ORG_ID
, PA_FCST_GLOBAL.GETPROJECTORGNAME PROJECT_ORG_NAME
, PA_FCST_GLOBAL.GETPROJSTATUSCODE PROJECT_STATUS_CODE
, PA_FCST_GLOBAL.GETPROJECTSTARTDATE PROJECT_START_DATE
, PA_FCST_GLOBAL.GETPROJECTSTARTDATEOPT PROJECT_START_DATE_OPT
, PA_FCST_GLOBAL.GETPROJECTCOMPDATE PROJECT_COMP_DATE
, PA_FCST_GLOBAL.GETPROJECTCOMPDATEOPT PROJECT_COMP_DATE_OPT
, PA_FCST_GLOBAL.GETPROJECTMANGERNAME PROJECT_MANAGER_NAME
, PA_FCST_GLOBAL.GETPROJECTMANGERID PROJECT_MANAGER_ID
, PA_FCST_GLOBAL.GETPROJECTCUSTOMERNAME PROJECT_CUSTOMER_NAME
, PA_FCST_GLOBAL.GETPAGEFIRSTFLAG PAGE_FIRST_FLAG
, PA_FCST_GLOBAL.GETPERIODSETNAME PERIOD_SET_NAME
FROM DUAL) FILTERBY
, PA_IMPLEMENTATIONS_ALL PIA
, GL_SETS_OF_BOOKS SOB
WHERE PASEC.ISCROSSPROJFLG= 'N'
AND PPP.PERSON_ID = PASEC.PPID
AND PPP.PROJECT_ID = PPA.PROJECT_ID
AND PPA.TEMPLATE_FLAG <> 'Y'
AND TRUNC(SYSDATE) >= TRUNC(PPP.START_DATE_ACTIVE)
AND TRUNC(SYSDATE) <= TRUNC(NVL(PPP.END_DATE_ACTIVE
, SYSDATE+1))
AND NVL(PIA.ORG_ID
, -99) = NVL(PPA.ORG_ID
, -99)
AND PIA.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND SOB.PERIOD_SET_NAME = FILTERBY.PERIOD_SET_NAME
AND PPA.PROBABILITY_MEMBER_ID = PBM.PROBABILITY_MEMBER_ID(+)
AND PARTIES.PROJECT_ROLE_ID (+) = 1
AND PARTIES.PROJECT_ID (+) = PPA.PROJECT_ID
AND SYSDATE BETWEEN PARTIES.START_DATE_ACTIVE(+)
AND NVL(PARTIES.END_DATE_ACTIVE(+)
, SYSDATE)
AND PARTIES.RESOURCE_SOURCE_ID = PEOPLE.PERSON_ID (+)
AND SYSDATE BETWEEN PEOPLE.EFFECTIVE_START_DATE (+)
AND PEOPLE.EFFECTIVE_END_DATE (+)
AND DECODE(FILTERBY.PAGE_FIRST_FLAG
, 'Y'
, 'N'
, 'N') = FILTERBY.PAGE_FIRST_FLAG
AND DECODE(FILTERBY.PROJECT_NUMBER
, 'ALL'
, 'ALL'
, PPA.SEGMENT1) LIKE '%'||FILTERBY.PROJECT_NUMBER||'%'
AND DECODE(FILTERBY.PROJECT_NAME
, 'XXXXXXXXXXXXXXX'
, 'XXXXXXXXXXXXXXX'
, PPA.NAME) LIKE '%'||FILTERBY.PROJECT_NAME||'%'
AND DECODE(FILTERBY.PROJECT_TYPE
, 'ALL'
, 'ALL'
, PPA.PROJECT_TYPE) = FILTERBY.PROJECT_TYPE
AND DECODE(FILTERBY.PROJECT_ORG_ID
, -99
, -99
, PPA.CARRYING_OUT_ORGANIZATION_ID) = FILTERBY.PROJECT_ORG_ID
AND DECODE(FILTERBY.PROJECT_STATUS_CODE
, 'ALL'
, 'ALL'
, PPA.PROJECT_STATUS_CODE) = FILTERBY.PROJECT_STATUS_CODE
AND DECODE(FILTERBY.PROJECT_MANAGER_NAME
, 'XXXXXXXXXXXXXXX'
, 'XXXXXXXXXXXXXXX'
, PEOPLE.FULL_NAME) LIKE '%'||FILTERBY.PROJECT_MANAGER_NAME||'%'
AND DECODE(FILTERBY.PROJECT_MANAGER_ID
, NULL
, -99
, PARTIES.RESOURCE_SOURCE_ID) = NVL(FILTERBY.PROJECT_MANAGER_ID
, -99)
AND DECODE(FILTERBY.PROJECT_CUSTOMER_NAME
, 'XXXXXXXXXXXXXXX'
, 'XXXXXXXXXXXXXXX'
, SUBSTR(PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PPA.PROJECT_ID)
, 1
, 45)) LIKE '%'|| FILTERBY.PROJECT_CUSTOMER_NAME||'%' UNION SELECT PPA.PROJECT_ID PROJECT_ID
, PPA.NAME PROJECT_NAME
, PPA.SEGMENT1 PROJECT_NUMBER
, SUBSTR(PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PPA.PROJECT_ID)
, 1
, 45) CUSTOMER_NAME
, PARTIES.RESOURCE_SOURCE_ID PERSON_ID
, PEOPLE.FULL_NAME PERSON_NAME
, PPA.PROJFUNC_CURRENCY_CODE PROJECT_CURRENCY_CODE
, PBM.PROBABILITY_PERCENTAGE PROBABILITY_PERCENTAGE
, PPA.PROBABILITY_MEMBER_ID PROBABILITY_MEMBER_ID
, PPA.CARRYING_OUT_ORGANIZATION_ID CARRYING_OUT_ORGANIZATION_ID
, PPA.ORG_ID ORG_ID
, PPA.PROJECT_TYPE PROJECT_TYPE
, PPA.PROJECT_STATUS_CODE PROJECT_STATUS_CODE
, PPA.START_DATE START_DATE
, PPA.COMPLETION_DATE COMPLETION_DATE FROM PA_PROJECTS_ALL PPA
, PA_PROBABILITY_MEMBERS PBM
, PER_ALL_PEOPLE_F PEOPLE
, PA_PROJECT_PARTIES PARTIES
, ( SELECT PA_FCST_GLOBAL.ISCROSSPROJECTVIEWUSER ISCROSSPROJFLG
FROM DUAL) PASEC
, ( SELECT PA_FCST_GLOBAL.GETPROJECTNUMBER PROJECT_NUMBER
, PA_FCST_GLOBAL.GETPROJECTNAME PROJECT_NAME
, PA_FCST_GLOBAL.GETPROJTYPE PROJECT_TYPE
, PA_FCST_GLOBAL.GETPROJECTORGID PROJECT_ORG_ID
, PA_FCST_GLOBAL.GETPROJECTORGNAME PROJECT_ORG_NAME
, PA_FCST_GLOBAL.GETPROJSTATUSCODE PROJECT_STATUS_CODE
, PA_FCST_GLOBAL.GETPROJECTSTARTDATE PROJECT_START_DATE
, PA_FCST_GLOBAL.GETPROJECTSTARTDATEOPT PROJECT_START_DATE_OPT
, PA_FCST_GLOBAL.GETPROJECTCOMPDATE PROJECT_COMP_DATE
, PA_FCST_GLOBAL.GETPROJECTCOMPDATEOPT PROJECT_COMP_DATE_OPT
, PA_FCST_GLOBAL.GETPROJECTMANGERNAME PROJECT_MANAGER_NAME
, PA_FCST_GLOBAL.GETPROJECTMANGERID PROJECT_MANAGER_ID
, PA_FCST_GLOBAL.GETPROJECTCUSTOMERNAME PROJECT_CUSTOMER_NAME
, PA_FCST_GLOBAL.GETPAGEFIRSTFLAG PAGE_FIRST_FLAG
, PA_FCST_GLOBAL.GETPERIODSETNAME PERIOD_SET_NAME
FROM DUAL) FILTERBY
, PA_IMPLEMENTATIONS_ALL PIA
, GL_SETS_OF_BOOKS SOB WHERE PASEC.ISCROSSPROJFLG= 'Y'
AND NVL(PIA.ORG_ID
, -99) = NVL(PPA.ORG_ID
, -99)
AND PIA.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND SOB.PERIOD_SET_NAME = FILTERBY.PERIOD_SET_NAME
AND PPA.PROBABILITY_MEMBER_ID = PBM.PROBABILITY_MEMBER_ID(+)
AND PARTIES.PROJECT_ROLE_ID (+) = 1
AND PARTIES.PROJECT_ID (+) = PPA.PROJECT_ID
AND SYSDATE BETWEEN PARTIES.START_DATE_ACTIVE(+)
AND NVL(PARTIES.END_DATE_ACTIVE(+)
, SYSDATE)
AND PARTIES.RESOURCE_SOURCE_ID = PEOPLE.PERSON_ID (+)
AND SYSDATE BETWEEN PEOPLE.EFFECTIVE_START_DATE (+)
AND PEOPLE.EFFECTIVE_END_DATE (+)
AND DECODE(FILTERBY.PAGE_FIRST_FLAG
, 'Y'
, 'N'
, 'N') = FILTERBY.PAGE_FIRST_FLAG
AND DECODE(FILTERBY.PROJECT_NUMBER
, 'ALL'
, 'ALL'
, PPA.SEGMENT1) LIKE '%'||FILTERBY.PROJECT_NUMBER||'%'
AND DECODE(FILTERBY.PROJECT_NAME
, 'XXXXXXXXXXXXXXX'
, 'XXXXXXXXXXXXXXX'
, PPA.NAME) LIKE '%'||FILTERBY.PROJECT_NAME||'%'
AND DECODE(FILTERBY.PROJECT_TYPE
, 'ALL'
, 'ALL'
, PPA.PROJECT_TYPE) = FILTERBY.PROJECT_TYPE
AND DECODE(FILTERBY.PROJECT_ORG_ID
, -99
, -99
, PPA.CARRYING_OUT_ORGANIZATION_ID) = FILTERBY.PROJECT_ORG_ID
AND DECODE(FILTERBY.PROJECT_STATUS_CODE
, 'ALL'
, 'ALL'
, PPA.PROJECT_STATUS_CODE) = FILTERBY.PROJECT_STATUS_CODE
AND DECODE(FILTERBY.PROJECT_MANAGER_NAME
, 'XXXXXXXXXXXXXXX'
, 'XXXXXXXXXXXXXXX'
, PEOPLE.FULL_NAME) LIKE '%'||FILTERBY.PROJECT_MANAGER_NAME||'%'
AND DECODE(FILTERBY.PROJECT_MANAGER_ID
, NULL
, -99
, PARTIES.RESOURCE_SOURCE_ID) = NVL(FILTERBY.PROJECT_MANAGER_ID
, -99)
AND DECODE(FILTERBY.PROJECT_CUSTOMER_NAME
, 'XXXXXXXXXXXXXXX'
, 'XXXXXXXXXXXXXXX'
, SUBSTR(PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(PPA.PROJECT_ID)
, 1
, 45)) LIKE '%'|| FILTERBY.PROJECT_CUSTOMER_NAME||'%'
AND PPA.TEMPLATE_FLAG <> 'Y'

Columns

Name
PROJECT_ID
PROJECT_NAME
PROJECT_NUMBER
CUSTOMER_NAME
PERSON_ID
PERSON_NAME
PROJECT_CURRENCY_CODE
PROBABILITY_PERCENTAGE
PROBABILITY_MEMBER_ID
CARRYING_OUT_ORGANIZATION_ID
ORG_ID
PROJECT_TYPE
PROJECT_STATUS_CODE
START_DATE
COMPLETION_DATE