View: PA_JOB_RELATIONSHIPS_VIEW

Product:	PA - Projects
Description:	PA_JOB_RELATIONSHIPS_VIEW is a view that identifies relationships between jobs via the master group job.
Implementation/DBA Data:	APPS.PA_JOB_RELATIONSHIPS_VIEW

View Text

SELECT FROM_JOB.NAME FROM_JOB_NAME
, JR1.FROM_JOB_ID FROM_JOB_ID
, TO_JOB.NAME TO_JOB_NAME
, JR2.TO_JOB_ID TO_JOB_ID
, GRP1.DISPLAYED_NAME FROM_JOB_GROUP_NAME
, JR1.FROM_JOB_GROUP_ID FROM_JOB_GROUP_ID
, GRP2.DISPLAYED_NAME TO_JOB_GROUP_NAME
, JR2.TO_JOB_GROUP_ID TO_JOB_GROUP_ID
FROM PA_JOB_RELATIONSHIPS JR1
, PA_JOB_RELATIONSHIPS JR2
, PER_JOB_GROUPS GRP
, PER_JOB_GROUPS GRP1
, PER_JOB_GROUPS GRP2
, PER_JOBS FROM_JOB
, PER_JOBS TO_JOB
WHERE JR1.FROM_JOB_ID = FROM_JOB.JOB_ID AND JR2.TO_JOB_ID = TO_JOB.JOB_ID
AND ((PA_CROSS_BUSINESS_GRP.ISCROSSBGPROFILE= 'N'
AND FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') = GRP.BUSINESS_GROUP_ID) OR PA_CROSS_BUSINESS_GRP.ISCROSSBGPROFILE= 'Y')
AND JR1.TO_JOB_ID = JR2.FROM_JOB_ID
AND GRP.JOB_GROUP_ID = JR1.TO_JOB_GROUP_ID
AND GRP.MASTER_FLAG = 'Y'
AND GRP1.JOB_GROUP_ID = JR1.FROM_JOB_GROUP_ID
AND GRP1.MASTER_FLAG = 'N'
AND GRP2.JOB_GROUP_ID = JR2.TO_JOB_GROUP_ID
AND GRP2.MASTER_FLAG = 'N' /* CHANGE STARTS FOR BUG 1654186 THIS SELECT CLAUSE WILL CATER TO SITUATION WHEN HR JOB == MASTER JOB -->PA JOB THIS SELECT STATEMENT WILL FETCH ROWS ALSO WHEN HR -->MASTER -->PA FOR THE ROW MASTER -->PA IN PA_JOB_RELATIONSHIPS TABLE. */ UNION SELECT FROM_JOB.NAME FROM_JOB_NAME
, JR.FROM_JOB_ID FROM_JOB_ID
, TO_JOB.NAME TO_JOB_NAME
, JR.TO_JOB_ID TO_JOB_ID
, GRP.DISPLAYED_NAME FROM_JOB_GROUP_NAME
, JR.FROM_JOB_GROUP_ID FROM_JOB_GROUP_ID
, GRP1.DISPLAYED_NAME TO_JOB_GROUP_NAME
, JR.TO_JOB_GROUP_ID TO_JOB_GROUP_ID
FROM PA_JOB_RELATIONSHIPS JR
, PER_JOB_GROUPS GRP
, PER_JOB_GROUPS GRP1
, PER_JOBS FROM_JOB
, PER_JOBS TO_JOB
WHERE JR.FROM_JOB_ID = FROM_JOB.JOB_ID AND JR.TO_JOB_ID = TO_JOB.JOB_ID
AND DECODE(PA_CROSS_BUSINESS_GRP.ISCROSSBGPROFILE
, 'N'
, FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
, 'Y'
, GRP.BUSINESS_GROUP_ID) = GRP.BUSINESS_GROUP_ID
AND GRP.JOB_GROUP_ID = JR.FROM_JOB_GROUP_ID
AND GRP.MASTER_FLAG = 'Y'
AND GRP1.JOB_GROUP_ID = JR.TO_JOB_GROUP_ID
AND GRP1.MASTER_FLAG = 'N' UNION /* ADDED FOR BUG 4027727 - TO GET NON-MASTER TO MASTER MAPPINGS */ SELECT FROM_JOB.NAME FROM_JOB_NAME
, JR.FROM_JOB_ID FROM_JOB_ID
, TO_JOB.NAME TO_JOB_NAME
, JR.TO_JOB_ID TO_JOB_ID
, GRP.DISPLAYED_NAME FROM_JOB_GROUP_NAME
, JR.FROM_JOB_GROUP_ID FROM_JOB_GROUP_ID
, GRP1.DISPLAYED_NAME TO_JOB_GROUP_NAME
, JR.TO_JOB_GROUP_ID TO_JOB_GROUP_ID
FROM PA_JOB_RELATIONSHIPS JR
, PER_JOB_GROUPS GRP
, PER_JOB_GROUPS GRP1
, PER_JOBS FROM_JOB
, PER_JOBS TO_JOB
WHERE JR.FROM_JOB_ID = FROM_JOB.JOB_ID
AND JR.TO_JOB_ID = TO_JOB.JOB_ID
AND DECODE(PA_CROSS_BUSINESS_GRP.ISCROSSBGPROFILE
, 'N'
, FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
, 'Y'
, GRP1.BUSINESS_GROUP_ID) = GRP1.BUSINESS_GROUP_ID
AND GRP.JOB_GROUP_ID = JR.FROM_JOB_GROUP_ID
AND GRP.MASTER_FLAG = 'N'
AND GRP1.JOB_GROUP_ID = JR.TO_JOB_GROUP_ID
AND GRP1.MASTER_FLAG = 'Y'

Columns