View: PA_REP_REQT_DTS_V

Product:	PA - Projects
Description:	PA_REP_REQT_DTS_V is a view to show the details of the requirements for organizations and projects
Implementation/DBA Data:	APPS.PA_REP_REQT_DTS_V

View Text

SELECT PROJ.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(PROJ.CARRYING_OUT_ORGANIZATION_ID) PROJECT_ORGANIZATION_NAME
, PROJ.PROJECT_ID
, PROJ.NAME PROJECT_NAME
, PROJ.SEGMENT1 PROJECT_NUMBER
, PROJ.NAME || '(' || PROJ.SEGMENT1 || ')' PROJECT_NAME_NUMBER
, ASGN.ASSIGNMENT_ID ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASGN.PROJECT_ROLE_ID PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, ASGN.START_DATE ASMT_START_DATE
, ASGN.END_DATE ASMT_END_DATE
, ASGN.ASSIGNMENT_EFFORT EFFORT_HOURS
, PL.MEANING STAFFING_PRIORITY
, ASGN.MIN_RESOURCE_JOB_LEVEL MIN_JOB_LEVEL
, ASGN.MAX_RESOURCE_JOB_LEVEL MAX_JOB_LEVEL
, ASGN.REVENUE_BILL_RATE || ' ' || ASGN.REVENUE_CURRENCY_CODE BILL_RATE_CURRENCY
, ASGN.REVENUE_BILL_RATE REVENUE_BILL_RATE
, ASGN.REVENUE_CURRENCY_CODE REVENUE_CURRENCY_CODE
, PA_HR_COMPETENCE_UTILS.GET_REQ_COMPETENCES(ASGN.ASSIGNMENT_ID) COMPETENCIES
, FTT.TERRITORY_SHORT_NAME COUNTRY
, LOC.REGION REGION
, LOC.CITY CITY
, DECODE(LOC.LOCATION_ID
, NULL
, NULL
, FTT.TERRITORY_SHORT_NAME || '.' || LOC.REGION || '.' || LOC.CITY) LOCATION
, PST.NAME PROJECT_SUBTEAM_NAME
, ASGN.EXTENSION_POSSIBLE EXTENTION_POSSIBLE
, PL1.MEANING EXPENSE_OWNER_NAME
, ASGN.EXPENSE_LIMIT EXPENSE_LIMIT
, (TRUNC(ASGN.END_DATE) - TRUNC(ASGN.START_DATE) +1 ) ASSIGNMENT_DURATION
, ASGN.ASSIGNMENT_EFFORT * IMP.FTE_DAY EFFORT_DAYS
, ASGN.ASSIGNMENT_EFFORT * IMP.FTE_WEEK EFFORT_WEEKS
, DECODE (ASGN.MULTIPLE_STATUS_FLAG
, 'Y'
, PL2.MEANING
, PS.PROJECT_STATUS_NAME) STATUS_NAME
, ASGN.NO_OF_ACTIVE_CANDIDATES
, ASETS.ACTION_SET_NAME ADVERTISEMENT_RULE
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.EXPENDITURE_ORG_ID) EXPENDITURE_ORG_NAME
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.EXPENDITURE_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_NAME
, ASGN.EXPENDITURE_TYPE
, PJG.DISPLAYED_NAME FCST_JOB_GROUP_NAME
, PJ.NAME FCST_JOB_NAME
, WT.NAME WORK_TYPE_NAME
, ASGN.BILL_RATE_OVERRIDE || ' ' || ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_OVERRIDE_CURR
, ASGN.BILL_RATE_OVERRIDE BILL_RATE_OVERRIDE
, ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_CURR_OVERRIDE
, ASGN.MARKUP_PERCENT MARKUP_PERCENT
, ASGN.MARKUP_PERCENT_OVERRIDE MARKUP_PERCENT_OVERRIDE
, ASGN.TP_RATE_OVERRIDE || ' ' || ASGN.TP_CURRENCY_OVERRIDE TP_RATE_OVERRIDE_CURR
, ASGN.TP_RATE_OVERRIDE TP_RATE_OVERRIDE
, ASGN.TP_CURRENCY_OVERRIDE TP_CURRENCY_OVERRIDE
, ASGN.TP_CALC_BASE_CODE_OVERRIDE TP_BASIS_OVERRIDE
, ASGN.TP_PERCENT_APPLIED_OVERRIDE TP_PERCENT_OVERRIDE
, ASGN.DESCRIPTION
, ASGN.ADDITIONAL_INFORMATION
, ASGN.CALENDAR_TYPE
, ASGN.RESOURCE_CALENDAR_PERCENT
, ASGN.EXPENDITURE_TYPE_CLASS
, ASGN.NOTE_TO_APPROVER
, ASGN.SEARCH_MIN_AVAILABILITY
, ASGN.SEARCH_COUNTRY_CODE
, ASGN.SEARCH_MIN_CANDIDATE_SCORE
, ASGN.LAST_AUTO_SEARCH_DATE
, ASGN.COMPETENCE_MATCH_WEIGHTING
, ASGN.AVAILABILITY_MATCH_WEIGHTING
, ASGN.JOB_LEVEL_MATCH_WEIGHTING
, ASGN.ATTRIBUTE_CATEGORY
, ASGN.ATTRIBUTE1
, ASGN.ATTRIBUTE2
, ASGN.ATTRIBUTE3
, ASGN.ATTRIBUTE4
, ASGN.ATTRIBUTE5
, ASGN.ATTRIBUTE6
, ASGN.ATTRIBUTE7
, ASGN.ATTRIBUTE8
, ASGN.ATTRIBUTE9
, ASGN.ATTRIBUTE10
, ASGN.ATTRIBUTE11
, ASGN.ATTRIBUTE12
, ASGN.ATTRIBUTE13
, ASGN.ATTRIBUTE14
, ASGN.ATTRIBUTE15
FROM PA_PROJECT_ASSIGNMENTS ASGN
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_ROLE_TYPES PRT
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='STAFFING_PRIORITY_CODE') PL
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='EXPENSE_OWNER_TYPE') PL1
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='MULTIPLE_STATUS_TEXT'
AND LOOKUP_CODE='MULTIPLE') PL2
, PA_LOCATIONS LOC
, PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECT_SUBTEAMS PST
, PA_PROJECT_SUBTEAM_PARTIES PSP
, PA_PROJECT_STATUSES PS
, PER_JOB_GROUPS PJG
, PER_JOBS PJ
, PA_WORK_TYPES_V WT
, PA_ACTION_SETS ASETS
, FND_TERRITORIES_TL FTT
, (SELECT INSTANCE_PK1_VALUE
FROM FND_GRANTS FG
, FND_USER FU
, FND_OBJECTS FOB
, (SELECT NVL(PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_PROJ_AUTH')
, -1) MENU_ID
, NVL(PA_SECURITY_PVT.GET_GRANTEE_KEY
, -1) GRANTEE_KEY FROM DUAL) PRJ_AUTH_MENU
WHERE FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.GRANTEE_TYPE = 'USER'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.MENU_ID = PRJ_AUTH_MENU.MENU_ID
AND FG.GRANTEE_KEY = PRJ_AUTH_MENU.GRANTEE_KEY
AND FU.USER_ID = FND_GLOBAL.USER_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))) AUTH_ORG
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL = 'N'
AND ASGN.PROJECT_ID = PROJ.PROJECT_ID
AND ASGN.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND ASGN.STAFFING_PRIORITY_CODE = PL.LOOKUP_CODE(+)
AND ASGN.EXPENSE_OWNER = PL1.LOOKUP_CODE (+)
AND ASGN.LOCATION_ID = LOC.LOCATION_ID (+)
AND ASGN.ASSIGNMENT_ID = PSP.OBJECT_ID (+)
AND ASGN.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND ASGN.FCST_JOB_GROUP_ID = PJG.JOB_GROUP_ID (+)
AND ASGN.FCST_JOB_ID = PJ.JOB_ID (+)
AND ASGN.WORK_TYPE_ID = WT.WORK_TYPE_ID
AND ASGN.EXPENDITURE_ORG_ID = IMP.ORG_ID (+)
AND ASGN.ASSIGNMENT_TYPE = 'OPEN_ASSIGNMENT'
AND ASGN.ASSIGNMENT_ID = ASETS.OBJECT_ID
AND LOC.COUNTRY_CODE = FTT.TERRITORY_CODE (+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND PSP.OBJECT_TYPE(+) = 'PA_PROJECT_ASSIGNMENTS'
AND PSP.PRIMARY_SUBTEAM_FLAG(+) = 'Y'
AND PSP.PROJECT_SUBTEAM_ID = PST.PROJECT_SUBTEAM_ID(+)
AND ASETS.STATUS_CODE <> 'DELETED'
AND ASETS.OBJECT_TYPE = 'OPEN_ASSIGNMENT'
AND ASETS.ACTION_SET_TYPE_CODE = 'ADVERTISEMENT'
AND PROJ.CARRYING_OUT_ORGANIZATION_ID IN AUTH_ORG.INSTANCE_PK1_VALUE UNION SELECT PROJ.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(PROJ.CARRYING_OUT_ORGANIZATION_ID) PROJECT_ORGANIZATION_NAME
, PROJ.PROJECT_ID
, PROJ.NAME PROJECT_NAME
, PROJ.SEGMENT1 PROJECT_NUMBER
, PROJ.NAME || '(' || PROJ.SEGMENT1 || ')' PROJECT_NAME_NUMBER
, ASGN.ASSIGNMENT_ID ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASGN.PROJECT_ROLE_ID PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, ASGN.START_DATE ASMT_START_DATE
, ASGN.END_DATE ASMT_END_DATE
, ASGN.ASSIGNMENT_EFFORT EFFORT_HOURS
, PL.MEANING STAFFING_PRIORITY
, ASGN.MIN_RESOURCE_JOB_LEVEL MIN_JOB_LEVEL
, ASGN.MAX_RESOURCE_JOB_LEVEL MAX_JOB_LEVEL
, ASGN.REVENUE_BILL_RATE || ' ' || ASGN.REVENUE_CURRENCY_CODE BILL_RATE_CURRENCY
, ASGN.REVENUE_BILL_RATE REVENUE_BILL_RATE
, ASGN.REVENUE_CURRENCY_CODE REVENUE_CURRENCY_CODE
, PA_HR_COMPETENCE_UTILS.GET_REQ_COMPETENCES(ASGN.ASSIGNMENT_ID) COMPETENCIES
, FTT.TERRITORY_SHORT_NAME COUNTRY
, LOC.REGION REGION
, LOC.CITY CITY
, DECODE(LOC.LOCATION_ID
, NULL
, NULL
, FTT.TERRITORY_SHORT_NAME || '.' || LOC.REGION || '.' || LOC.CITY) LOCATION
, PST.NAME PROJECT_SUBTEAM_NAME
, ASGN.EXTENSION_POSSIBLE EXTENTION_POSSIBLE
, PL1.MEANING EXPENSE_OWNER_NAME
, ASGN.EXPENSE_LIMIT EXPENSE_LIMIT
, (TRUNC(ASGN.END_DATE) - TRUNC(ASGN.START_DATE) +1 ) ASSIGNMENT_DURATION
, ASGN.ASSIGNMENT_EFFORT * IMP.FTE_DAY EFFORT_DAYS
, ASGN.ASSIGNMENT_EFFORT * IMP.FTE_WEEK EFFORT_WEEKS
, DECODE (ASGN.MULTIPLE_STATUS_FLAG
, 'Y'
, PL2.MEANING
, PS.PROJECT_STATUS_NAME) STATUS_NAME
, ASGN.NO_OF_ACTIVE_CANDIDATES
, ASETS.ACTION_SET_NAME ADVERTISEMENT_RULE
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.EXPENDITURE_ORG_ID) EXPENDITURE_ORG_NAME
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.EXPENDITURE_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_NAME
, ASGN.EXPENDITURE_TYPE
, PJG.DISPLAYED_NAME FCST_JOB_GROUP_NAME
, PJ.NAME FCST_JOB_NAME
, WT.NAME WORK_TYPE_NAME
, ASGN.BILL_RATE_OVERRIDE || ' ' || ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_OVERRIDE_CURR
, ASGN.BILL_RATE_OVERRIDE BILL_RATE_OVERRIDE
, ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_CURR_OVERRIDE
, ASGN.MARKUP_PERCENT MARKUP_PERCENT
, ASGN.MARKUP_PERCENT_OVERRIDE MARKUP_PERCENT_OVERRIDE
, ASGN.TP_RATE_OVERRIDE || ' ' || ASGN.TP_CURRENCY_OVERRIDE TP_RATE_OVERRIDE_CURR
, ASGN.TP_RATE_OVERRIDE TP_RATE_OVERRIDE
, ASGN.TP_CURRENCY_OVERRIDE TP_CURRENCY_OVERRIDE
, ASGN.TP_CALC_BASE_CODE_OVERRIDE TP_BASIS_OVERRIDE
, ASGN.TP_PERCENT_APPLIED_OVERRIDE TP_PERCENT_OVERRIDE
, ASGN.DESCRIPTION
, ASGN.ADDITIONAL_INFORMATION
, ASGN.CALENDAR_TYPE
, ASGN.RESOURCE_CALENDAR_PERCENT
, ASGN.EXPENDITURE_TYPE_CLASS
, ASGN.NOTE_TO_APPROVER
, ASGN.SEARCH_MIN_AVAILABILITY
, ASGN.SEARCH_COUNTRY_CODE
, ASGN.SEARCH_MIN_CANDIDATE_SCORE
, ASGN.LAST_AUTO_SEARCH_DATE
, ASGN.COMPETENCE_MATCH_WEIGHTING
, ASGN.AVAILABILITY_MATCH_WEIGHTING
, ASGN.JOB_LEVEL_MATCH_WEIGHTING
, ASGN.ATTRIBUTE_CATEGORY
, ASGN.ATTRIBUTE1
, ASGN.ATTRIBUTE2
, ASGN.ATTRIBUTE3
, ASGN.ATTRIBUTE4
, ASGN.ATTRIBUTE5
, ASGN.ATTRIBUTE6
, ASGN.ATTRIBUTE7
, ASGN.ATTRIBUTE8
, ASGN.ATTRIBUTE9
, ASGN.ATTRIBUTE10
, ASGN.ATTRIBUTE11
, ASGN.ATTRIBUTE12
, ASGN.ATTRIBUTE13
, ASGN.ATTRIBUTE14
, ASGN.ATTRIBUTE15
FROM PA_PROJECT_ASSIGNMENTS ASGN
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_ROLE_TYPES PRT
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='STAFFING_PRIORITY_CODE') PL
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='EXPENSE_OWNER_TYPE') PL1
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='MULTIPLE_STATUS_TEXT'
AND LOOKUP_CODE='MULTIPLE') PL2
, PA_LOCATIONS LOC
, PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECT_SUBTEAMS PST
, PA_PROJECT_SUBTEAM_PARTIES PSP
, PA_PROJECT_STATUSES PS
, PER_JOB_GROUPS PJG
, PER_JOBS PJ
, PA_WORK_TYPES_V WT
, PA_ACTION_SETS ASETS
, PA_PROJECT_PARTIES PPP
, FND_TERRITORIES_TL FTT
, FND_USER FU
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL = 'N'
AND ASGN.PROJECT_ID = PROJ.PROJECT_ID
AND ASGN.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND ASGN.STAFFING_PRIORITY_CODE = PL.LOOKUP_CODE(+)
AND ASGN.EXPENSE_OWNER = PL1.LOOKUP_CODE (+)
AND ASGN.LOCATION_ID = LOC.LOCATION_ID (+)
AND ASGN.ASSIGNMENT_ID = PSP.OBJECT_ID (+)
AND ASGN.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND ASGN.FCST_JOB_GROUP_ID = PJG.JOB_GROUP_ID (+)
AND ASGN.FCST_JOB_ID = PJ.JOB_ID (+)
AND ASGN.WORK_TYPE_ID = WT.WORK_TYPE_ID
AND ASGN.EXPENDITURE_ORG_ID = IMP.ORG_ID (+)
AND ASGN.ASSIGNMENT_TYPE = 'OPEN_ASSIGNMENT'
AND ASGN.ASSIGNMENT_ID = ASETS.OBJECT_ID
AND LOC.COUNTRY_CODE = FTT.TERRITORY_CODE (+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND PSP.OBJECT_TYPE(+) = 'PA_PROJECT_ASSIGNMENTS'
AND PSP.PRIMARY_SUBTEAM_FLAG(+) = 'Y'
AND PSP.PROJECT_SUBTEAM_ID = PST.PROJECT_SUBTEAM_ID(+)
AND ASETS.STATUS_CODE <> 'DELETED'
AND ASETS.OBJECT_TYPE = 'OPEN_ASSIGNMENT'
AND ASETS.ACTION_SET_TYPE_CODE = 'ADVERTISEMENT'
AND PROJ.PROJECT_ID = PPP.PROJECT_ID
AND PPP.PROJECT_ROLE_ID = 1
AND PPP.RESOURCE_SOURCE_ID = FU.EMPLOYEE_ID
AND FU.USER_ID = FND_GLOBAL.USER_ID UNION SELECT PROJ.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(PROJ.CARRYING_OUT_ORGANIZATION_ID) PROJECT_ORGANIZATION_NAME
, PROJ.PROJECT_ID
, PROJ.NAME PROJECT_NAME
, PROJ.SEGMENT1 PROJECT_NUMBER
, PROJ.NAME || '(' || PROJ.SEGMENT1 || ')' PROJECT_NAME_NUMBER
, ASGN.ASSIGNMENT_ID ASSIGNMENT_ID
, ASGN.ASSIGNMENT_NAME ASSIGNMENT_NAME
, ASGN.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASGN.PROJECT_ROLE_ID PROJECT_ROLE_ID
, PRT.MEANING PROJECT_ROLE_NAME
, ASGN.START_DATE ASMT_START_DATE
, ASGN.END_DATE ASMT_END_DATE
, ASGN.ASSIGNMENT_EFFORT EFFORT_HOURS
, PL.MEANING STAFFING_PRIORITY
, ASGN.MIN_RESOURCE_JOB_LEVEL MIN_JOB_LEVEL
, ASGN.MAX_RESOURCE_JOB_LEVEL MAX_JOB_LEVEL
, ASGN.REVENUE_BILL_RATE || ' ' || ASGN.REVENUE_CURRENCY_CODE BILL_RATE_CURRENCY
, ASGN.REVENUE_BILL_RATE REVENUE_BILL_RATE
, ASGN.REVENUE_CURRENCY_CODE REVENUE_CURRENCY_CODE
, PA_HR_COMPETENCE_UTILS.GET_REQ_COMPETENCES(ASGN.ASSIGNMENT_ID) COMPETENCIES
, FTT.TERRITORY_SHORT_NAME COUNTRY
, LOC.REGION REGION
, LOC.CITY CITY
, DECODE(LOC.LOCATION_ID
, NULL
, NULL
, FTT.TERRITORY_SHORT_NAME || '.' || LOC.REGION || '.' || LOC.CITY) LOCATION
, PST.NAME PROJECT_SUBTEAM_NAME
, ASGN.EXTENSION_POSSIBLE EXTENTION_POSSIBLE
, PL1.MEANING EXPENSE_OWNER_NAME
, ASGN.EXPENSE_LIMIT EXPENSE_LIMIT
, (TRUNC(ASGN.END_DATE) - TRUNC(ASGN.START_DATE) +1 ) ASSIGNMENT_DURATION
, ASGN.ASSIGNMENT_EFFORT * IMP.FTE_DAY EFFORT_DAYS
, ASGN.ASSIGNMENT_EFFORT * IMP.FTE_WEEK EFFORT_WEEKS
, DECODE (ASGN.MULTIPLE_STATUS_FLAG
, 'Y'
, PL2.MEANING
, PS.PROJECT_STATUS_NAME) STATUS_NAME
, ASGN.NO_OF_ACTIVE_CANDIDATES
, ASETS.ACTION_SET_NAME ADVERTISEMENT_RULE
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.EXPENDITURE_ORG_ID) EXPENDITURE_ORG_NAME
, PA_EXPENDITURES_UTILS.GETORGTLNAME(ASGN.EXPENDITURE_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_NAME
, ASGN.EXPENDITURE_TYPE
, PJG.DISPLAYED_NAME FCST_JOB_GROUP_NAME
, PJ.NAME FCST_JOB_NAME
, WT.NAME WORK_TYPE_NAME
, ASGN.BILL_RATE_OVERRIDE || ' ' || ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_OVERRIDE_CURR
, ASGN.BILL_RATE_OVERRIDE BILL_RATE_OVERRIDE
, ASGN.BILL_RATE_CURR_OVERRIDE BILL_RATE_CURR_OVERRIDE
, ASGN.MARKUP_PERCENT MARKUP_PERCENT
, ASGN.MARKUP_PERCENT_OVERRIDE MARKUP_PERCENT_OVERRIDE
, ASGN.TP_RATE_OVERRIDE || ' ' || ASGN.TP_CURRENCY_OVERRIDE TP_RATE_OVERRIDE_CURR
, ASGN.TP_RATE_OVERRIDE TP_RATE_OVERRIDE
, ASGN.TP_CURRENCY_OVERRIDE TP_CURRENCY_OVERRIDE
, ASGN.TP_CALC_BASE_CODE_OVERRIDE TP_BASIS_OVERRIDE
, ASGN.TP_PERCENT_APPLIED_OVERRIDE TP_PERCENT_OVERRIDE
, ASGN.DESCRIPTION
, ASGN.ADDITIONAL_INFORMATION
, ASGN.CALENDAR_TYPE
, ASGN.RESOURCE_CALENDAR_PERCENT
, ASGN.EXPENDITURE_TYPE_CLASS
, ASGN.NOTE_TO_APPROVER
, ASGN.SEARCH_MIN_AVAILABILITY
, ASGN.SEARCH_COUNTRY_CODE
, ASGN.SEARCH_MIN_CANDIDATE_SCORE
, ASGN.LAST_AUTO_SEARCH_DATE
, ASGN.COMPETENCE_MATCH_WEIGHTING
, ASGN.AVAILABILITY_MATCH_WEIGHTING
, ASGN.JOB_LEVEL_MATCH_WEIGHTING
, ASGN.ATTRIBUTE_CATEGORY
, ASGN.ATTRIBUTE1
, ASGN.ATTRIBUTE2
, ASGN.ATTRIBUTE3
, ASGN.ATTRIBUTE4
, ASGN.ATTRIBUTE5
, ASGN.ATTRIBUTE6
, ASGN.ATTRIBUTE7
, ASGN.ATTRIBUTE8
, ASGN.ATTRIBUTE9
, ASGN.ATTRIBUTE10
, ASGN.ATTRIBUTE11
, ASGN.ATTRIBUTE12
, ASGN.ATTRIBUTE13
, ASGN.ATTRIBUTE14
, ASGN.ATTRIBUTE15
FROM PA_PROJECT_ASSIGNMENTS ASGN
, PA_PROJECTS_ALL PROJ
, PA_PROJECT_ROLE_TYPES PRT
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='STAFFING_PRIORITY_CODE') PL
, (SELECT LOOKUP_CODE
, MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='EXPENSE_OWNER_TYPE') PL1
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE='MULTIPLE_STATUS_TEXT'
AND LOOKUP_CODE='MULTIPLE') PL2
, PA_LOCATIONS LOC
, PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECT_SUBTEAMS PST
, PA_PROJECT_SUBTEAM_PARTIES PSP
, PA_PROJECT_STATUSES PS
, PER_JOB_GROUPS PJG
, PER_JOBS PJ
, PA_WORK_TYPES_V WT
, PA_ACTION_SETS ASETS
, FND_TERRITORIES_TL FTT
, (SELECT NVL(FND_PROFILE.VALUE('PA_SUPER_PROJECT_VIEW')
, 'N') VAL
FROM DUAL) PROF
WHERE PROF.VAL = 'Y'
AND ASGN.PROJECT_ID = PROJ.PROJECT_ID
AND ASGN.PROJECT_ROLE_ID = PRT.PROJECT_ROLE_ID
AND ASGN.STAFFING_PRIORITY_CODE = PL.LOOKUP_CODE(+)
AND ASGN.EXPENSE_OWNER = PL1.LOOKUP_CODE (+)
AND ASGN.LOCATION_ID = LOC.LOCATION_ID (+)
AND ASGN.ASSIGNMENT_ID = PSP.OBJECT_ID (+)
AND ASGN.STATUS_CODE = PS.PROJECT_STATUS_CODE (+)
AND ASGN.FCST_JOB_GROUP_ID = PJG.JOB_GROUP_ID (+)
AND ASGN.FCST_JOB_ID = PJ.JOB_ID (+)
AND ASGN.WORK_TYPE_ID = WT.WORK_TYPE_ID
AND ASGN.EXPENDITURE_ORG_ID = IMP.ORG_ID (+)
AND ASGN.ASSIGNMENT_TYPE = 'OPEN_ASSIGNMENT'
AND ASGN.ASSIGNMENT_ID = ASETS.OBJECT_ID
AND LOC.COUNTRY_CODE = FTT.TERRITORY_CODE (+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND PSP.OBJECT_TYPE(+) = 'PA_PROJECT_ASSIGNMENTS'
AND PSP.PRIMARY_SUBTEAM_FLAG(+) = 'Y'
AND PSP.PROJECT_SUBTEAM_ID = PST.PROJECT_SUBTEAM_ID(+)
AND ASETS.STATUS_CODE <> 'DELETED'
AND ASETS.OBJECT_TYPE = 'OPEN_ASSIGNMENT'
AND ASETS.ACTION_SET_TYPE_CODE = 'ADVERTISEMENT'

Columns