VIEW: APPS.PA_FCST_PROJECT_LIST_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'