VIEW: APPS.PA_QUERY_RES_LIST_MEMBERS_V

Source

View Text - Preformatted

SELECT RL.RESOURCE_LIST_ID , RL.NAME , pa_resources_pkg.get_resource_name(RE.RESOURCE_ID,RE.RESOURCE_TYPE_ID) , RT.NAME , RT.RESOURCE_TYPE_CODE , NVL(RLM1.ALIAS,' ') , NVL(RLM2.PARENT_MEMBER_ID,0) , NULL , NULL , RLM2.ALIAS , RLM2.RESOURCE_LIST_MEMBER_ID , RLM2.SORT_ORDER , RLM2.ENABLED_FLAG , RE.UNIT_OF_MEASURE , RLM2.TRACK_AS_LABOR_FLAG , NULL , NULL , NULL , NULL , NVL(RTA.EVENT_TYPE,' ') , NVL(RTA.EXPENDITURE_TYPE,' ') , NVL(RTA.EXPENDITURE_CATEGORY,' ') , NVL(RTA.REVENUE_CATEGORY,' ') , rlm2.migration_code FROM PA_RESOURCE_LISTS RL, PA_RESOURCES RE, PA_RESOURCE_TYPES RT, PA_RESOURCE_LIST_MEMBERS RLM1, PA_RESOURCE_LIST_MEMBERS RLM2, PA_RESOURCE_TXN_ATTRIBUTES RTA WHERE RL.RESOURCE_LIST_ID = RLM2.RESOURCE_LIST_ID AND RLM2.RESOURCE_ID = RE.RESOURCE_ID AND RE.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID AND RT.RESOURCE_TYPE_CODE NOT IN ('EMPLOYEE','VENDOR','ORGANIZATION','JOB') AND RE.RESOURCE_ID = RTA.RESOURCE_ID (+) AND RLM2.PARENT_MEMBER_ID = RLM1.RESOURCE_LIST_MEMBER_ID (+) AND RLM2.DISPLAY_FLAG = 'Y' AND RLM2.ENABLED_FLAG = 'Y' UNION SELECT RL.RESOURCE_LIST_ID, RL.NAME, pa_resources_pkg.get_resource_name(RE.RESOURCE_ID,RE.RESOURCE_TYPE_ID), RT.NAME, RT.RESOURCE_TYPE_CODE, NVL(RLM1.ALIAS,' '), NVL(RLM2.PARENT_MEMBER_ID,0), PAE.LAST_NAME, PAE.FIRST_NAME, RLM2.ALIAS, RLM2.RESOURCE_LIST_MEMBER_ID, RLM2.SORT_ORDER, RLM2.ENABLED_FLAG, RE.UNIT_OF_MEASURE, RLM2.TRACK_AS_LABOR_FLAG, NULL, NULL, NULL, NULL, NVL(RTA.EVENT_TYPE,' '), NVL(RTA.EXPENDITURE_TYPE,' '), NVL(RTA.EXPENDITURE_CATEGORY,' '), NVL(RTA.REVENUE_CATEGORY,' '), rlm2.migration_code FROM PA_RESOURCE_LISTS RL, PA_RESOURCES RE, PA_RESOURCE_TYPES RT, PA_RESOURCE_LIST_MEMBERS RLM1, PA_RESOURCE_LIST_MEMBERS RLM2, PA_RESOURCE_TXN_ATTRIBUTES RTA, PA_EMPLOYEES PAE WHERE RL.RESOURCE_LIST_ID = RLM2.RESOURCE_LIST_ID AND RLM2.RESOURCE_ID = RE.RESOURCE_ID AND RE.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID AND RT.RESOURCE_TYPE_CODE = 'EMPLOYEE' AND RE.RESOURCE_ID = RTA.RESOURCE_ID (+) AND RTA.PERSON_ID = PAE.PERSON_ID AND RLM2.PARENT_MEMBER_ID = RLM1.RESOURCE_LIST_MEMBER_ID (+) AND RLM2.DISPLAY_FLAG = 'Y' AND RLM2.ENABLED_FLAG = 'Y' UNION SELECT RL.RESOURCE_LIST_ID, RL.NAME, pa_resources_pkg.get_resource_name(RE.RESOURCE_ID,RE.RESOURCE_TYPE_ID), RT.NAME, RT.RESOURCE_TYPE_CODE, NVL(RLM1.ALIAS,' '), NVL(RLM2.PARENT_MEMBER_ID,0), NULL, NULL, RLM2.ALIAS, RLM2.RESOURCE_LIST_MEMBER_ID, RLM2.SORT_ORDER, RLM2.ENABLED_FLAG, RE.UNIT_OF_MEASURE, RLM2.TRACK_AS_LABOR_FLAG, NULL, NULL, NULL, PAJ.NAME, NVL(RTA.EVENT_TYPE,' '), NVL(RTA.EXPENDITURE_TYPE,' '), NVL(RTA.EXPENDITURE_CATEGORY,' '), NVL(RTA.REVENUE_CATEGORY,' '), rlm2.migration_code FROM PA_RESOURCE_LISTS RL, PA_RESOURCES RE, PA_RESOURCE_TYPES RT, PA_RESOURCE_LIST_MEMBERS RLM1, PA_RESOURCE_LIST_MEMBERS RLM2, PA_RESOURCE_TXN_ATTRIBUTES RTA, PA_JOBS_V PAJ WHERE RL.RESOURCE_LIST_ID = RLM2.RESOURCE_LIST_ID AND RLM2.RESOURCE_ID = RE.RESOURCE_ID AND RE.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID AND RT.RESOURCE_TYPE_CODE = 'JOB' AND RE.RESOURCE_ID = RTA.RESOURCE_ID (+) AND RTA.JOB_ID = PAJ.JOB_ID AND RLM2.PARENT_MEMBER_ID = RLM1.RESOURCE_LIST_MEMBER_ID (+) AND RLM2.DISPLAY_FLAG = 'Y' AND RLM2.ENABLED_FLAG = 'Y' UNION SELECT RL.RESOURCE_LIST_ID, RL.NAME, pa_resources_pkg.get_resource_name(RE.RESOURCE_ID,RE.RESOURCE_TYPE_ID), RT.NAME, RT.RESOURCE_TYPE_CODE, NVL(RLM1.ALIAS,' '), NVL(RLM2.PARENT_MEMBER_ID,0), NULL, NULL, RLM2.ALIAS, RLM2.RESOURCE_LIST_MEMBER_ID, RLM2.SORT_ORDER, RLM2.ENABLED_FLAG, RE.UNIT_OF_MEASURE, RLM2.TRACK_AS_LABOR_FLAG, NULL, NULL, POV.VENDOR_NAME, NULL, NVL(RTA.EVENT_TYPE,' '), NVL(RTA.EXPENDITURE_TYPE,' '), NVL(RTA.EXPENDITURE_CATEGORY,' '), NVL(RTA.REVENUE_CATEGORY,' '), rlm2.migration_code FROM PA_RESOURCE_LISTS RL, PA_RESOURCES RE, PA_RESOURCE_TYPES RT, PA_RESOURCE_LIST_MEMBERS RLM1, PA_RESOURCE_LIST_MEMBERS RLM2, PA_RESOURCE_TXN_ATTRIBUTES RTA, PO_VENDORS POV WHERE RL.RESOURCE_LIST_ID = RLM2.RESOURCE_LIST_ID AND RLM2.RESOURCE_ID = RE.RESOURCE_ID AND RE.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID AND RT.RESOURCE_TYPE_CODE = 'VENDOR' AND RE.RESOURCE_ID = RTA.RESOURCE_ID (+) AND RTA.VENDOR_ID = POV.VENDOR_ID AND RLM2.PARENT_MEMBER_ID = RLM1.RESOURCE_LIST_MEMBER_ID (+) AND RLM2.DISPLAY_FLAG = 'Y' AND RLM2.ENABLED_FLAG = 'Y' UNION SELECT RL.RESOURCE_LIST_ID, RL.NAME, pa_resources_pkg.get_resource_name(RE.RESOURCE_ID,RE.RESOURCE_TYPE_ID), RT.NAME, RT.RESOURCE_TYPE_CODE, NVL(RLM1.ALIAS,' '), NVL(RLM2.PARENT_MEMBER_ID,0), NULL, NULL, RLM2.ALIAS, RLM2.RESOURCE_LIST_MEMBER_ID, RLM2.SORT_ORDER, RLM2.ENABLED_FLAG, RE.UNIT_OF_MEASURE, RLM2.TRACK_AS_LABOR_FLAG, NULL, PAO.NAME, NULL, NULL, NVL(RTA.EVENT_TYPE,' '), NVL(RTA.EXPENDITURE_TYPE,' '), NVL(RTA.EXPENDITURE_CATEGORY,' '), NVL(RTA.REVENUE_CATEGORY,' '), rlm2.migration_code FROM PA_RESOURCE_LISTS RL, PA_RESOURCES RE, PA_RESOURCE_TYPES RT, PA_RESOURCE_LIST_MEMBERS RLM1, PA_RESOURCE_LIST_MEMBERS RLM2, PA_RESOURCE_TXN_ATTRIBUTES RTA, PA_ORGANIZATIONS_V PAO WHERE RL.RESOURCE_LIST_ID = RLM2.RESOURCE_LIST_ID AND RLM2.RESOURCE_ID = RE.RESOURCE_ID AND RE.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID AND RT.RESOURCE_TYPE_CODE = 'ORGANIZATION' AND RE.RESOURCE_ID = RTA.RESOURCE_ID (+) AND RTA.ORGANIZATION_ID = PAO.ORGANIZATION_ID AND RLM2.PARENT_MEMBER_ID = RLM1.RESOURCE_LIST_MEMBER_ID (+) AND RLM2.DISPLAY_FLAG = 'Y' AND RLM2.ENABLED_FLAG = 'Y'

View Text - HTML Formatted

SELECT RL.RESOURCE_LIST_ID
, RL.NAME
, PA_RESOURCES_PKG.GET_RESOURCE_NAME(RE.RESOURCE_ID
, RE.RESOURCE_TYPE_ID)
, RT.NAME
, RT.RESOURCE_TYPE_CODE
, NVL(RLM1.ALIAS
, ' ')
, NVL(RLM2.PARENT_MEMBER_ID
, 0)
, NULL
, NULL
, RLM2.ALIAS
, RLM2.RESOURCE_LIST_MEMBER_ID
, RLM2.SORT_ORDER
, RLM2.ENABLED_FLAG
, RE.UNIT_OF_MEASURE
, RLM2.TRACK_AS_LABOR_FLAG
, NULL
, NULL
, NULL
, NULL
, NVL(RTA.EVENT_TYPE
, ' ')
, NVL(RTA.EXPENDITURE_TYPE
, ' ')
, NVL(RTA.EXPENDITURE_CATEGORY
, ' ')
, NVL(RTA.REVENUE_CATEGORY
, ' ')
, RLM2.MIGRATION_CODE
FROM PA_RESOURCE_LISTS RL
, PA_RESOURCES RE
, PA_RESOURCE_TYPES RT
, PA_RESOURCE_LIST_MEMBERS RLM1
, PA_RESOURCE_LIST_MEMBERS RLM2
, PA_RESOURCE_TXN_ATTRIBUTES RTA
WHERE RL.RESOURCE_LIST_ID = RLM2.RESOURCE_LIST_ID
AND RLM2.RESOURCE_ID = RE.RESOURCE_ID
AND RE.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID
AND RT.RESOURCE_TYPE_CODE NOT IN ('EMPLOYEE'
, 'VENDOR'
, 'ORGANIZATION'
, 'JOB')
AND RE.RESOURCE_ID = RTA.RESOURCE_ID (+)
AND RLM2.PARENT_MEMBER_ID = RLM1.RESOURCE_LIST_MEMBER_ID (+)
AND RLM2.DISPLAY_FLAG = 'Y'
AND RLM2.ENABLED_FLAG = 'Y' UNION SELECT RL.RESOURCE_LIST_ID
, RL.NAME
, PA_RESOURCES_PKG.GET_RESOURCE_NAME(RE.RESOURCE_ID
, RE.RESOURCE_TYPE_ID)
, RT.NAME
, RT.RESOURCE_TYPE_CODE
, NVL(RLM1.ALIAS
, ' ')
, NVL(RLM2.PARENT_MEMBER_ID
, 0)
, PAE.LAST_NAME
, PAE.FIRST_NAME
, RLM2.ALIAS
, RLM2.RESOURCE_LIST_MEMBER_ID
, RLM2.SORT_ORDER
, RLM2.ENABLED_FLAG
, RE.UNIT_OF_MEASURE
, RLM2.TRACK_AS_LABOR_FLAG
, NULL
, NULL
, NULL
, NULL
, NVL(RTA.EVENT_TYPE
, ' ')
, NVL(RTA.EXPENDITURE_TYPE
, ' ')
, NVL(RTA.EXPENDITURE_CATEGORY
, ' ')
, NVL(RTA.REVENUE_CATEGORY
, ' ')
, RLM2.MIGRATION_CODE
FROM PA_RESOURCE_LISTS RL
, PA_RESOURCES RE
, PA_RESOURCE_TYPES RT
, PA_RESOURCE_LIST_MEMBERS RLM1
, PA_RESOURCE_LIST_MEMBERS RLM2
, PA_RESOURCE_TXN_ATTRIBUTES RTA
, PA_EMPLOYEES PAE
WHERE RL.RESOURCE_LIST_ID = RLM2.RESOURCE_LIST_ID
AND RLM2.RESOURCE_ID = RE.RESOURCE_ID
AND RE.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID
AND RT.RESOURCE_TYPE_CODE = 'EMPLOYEE'
AND RE.RESOURCE_ID = RTA.RESOURCE_ID (+)
AND RTA.PERSON_ID = PAE.PERSON_ID
AND RLM2.PARENT_MEMBER_ID = RLM1.RESOURCE_LIST_MEMBER_ID (+)
AND RLM2.DISPLAY_FLAG = 'Y'
AND RLM2.ENABLED_FLAG = 'Y' UNION SELECT RL.RESOURCE_LIST_ID
, RL.NAME
, PA_RESOURCES_PKG.GET_RESOURCE_NAME(RE.RESOURCE_ID
, RE.RESOURCE_TYPE_ID)
, RT.NAME
, RT.RESOURCE_TYPE_CODE
, NVL(RLM1.ALIAS
, ' ')
, NVL(RLM2.PARENT_MEMBER_ID
, 0)
, NULL
, NULL
, RLM2.ALIAS
, RLM2.RESOURCE_LIST_MEMBER_ID
, RLM2.SORT_ORDER
, RLM2.ENABLED_FLAG
, RE.UNIT_OF_MEASURE
, RLM2.TRACK_AS_LABOR_FLAG
, NULL
, NULL
, NULL
, PAJ.NAME
, NVL(RTA.EVENT_TYPE
, ' ')
, NVL(RTA.EXPENDITURE_TYPE
, ' ')
, NVL(RTA.EXPENDITURE_CATEGORY
, ' ')
, NVL(RTA.REVENUE_CATEGORY
, ' ')
, RLM2.MIGRATION_CODE
FROM PA_RESOURCE_LISTS RL
, PA_RESOURCES RE
, PA_RESOURCE_TYPES RT
, PA_RESOURCE_LIST_MEMBERS RLM1
, PA_RESOURCE_LIST_MEMBERS RLM2
, PA_RESOURCE_TXN_ATTRIBUTES RTA
, PA_JOBS_V PAJ
WHERE RL.RESOURCE_LIST_ID = RLM2.RESOURCE_LIST_ID
AND RLM2.RESOURCE_ID = RE.RESOURCE_ID
AND RE.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID
AND RT.RESOURCE_TYPE_CODE = 'JOB'
AND RE.RESOURCE_ID = RTA.RESOURCE_ID (+)
AND RTA.JOB_ID = PAJ.JOB_ID
AND RLM2.PARENT_MEMBER_ID = RLM1.RESOURCE_LIST_MEMBER_ID (+)
AND RLM2.DISPLAY_FLAG = 'Y'
AND RLM2.ENABLED_FLAG = 'Y' UNION SELECT RL.RESOURCE_LIST_ID
, RL.NAME
, PA_RESOURCES_PKG.GET_RESOURCE_NAME(RE.RESOURCE_ID
, RE.RESOURCE_TYPE_ID)
, RT.NAME
, RT.RESOURCE_TYPE_CODE
, NVL(RLM1.ALIAS
, ' ')
, NVL(RLM2.PARENT_MEMBER_ID
, 0)
, NULL
, NULL
, RLM2.ALIAS
, RLM2.RESOURCE_LIST_MEMBER_ID
, RLM2.SORT_ORDER
, RLM2.ENABLED_FLAG
, RE.UNIT_OF_MEASURE
, RLM2.TRACK_AS_LABOR_FLAG
, NULL
, NULL
, POV.VENDOR_NAME
, NULL
, NVL(RTA.EVENT_TYPE
, ' ')
, NVL(RTA.EXPENDITURE_TYPE
, ' ')
, NVL(RTA.EXPENDITURE_CATEGORY
, ' ')
, NVL(RTA.REVENUE_CATEGORY
, ' ')
, RLM2.MIGRATION_CODE
FROM PA_RESOURCE_LISTS RL
, PA_RESOURCES RE
, PA_RESOURCE_TYPES RT
, PA_RESOURCE_LIST_MEMBERS RLM1
, PA_RESOURCE_LIST_MEMBERS RLM2
, PA_RESOURCE_TXN_ATTRIBUTES RTA
, PO_VENDORS POV
WHERE RL.RESOURCE_LIST_ID = RLM2.RESOURCE_LIST_ID
AND RLM2.RESOURCE_ID = RE.RESOURCE_ID
AND RE.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID
AND RT.RESOURCE_TYPE_CODE = 'VENDOR'
AND RE.RESOURCE_ID = RTA.RESOURCE_ID (+)
AND RTA.VENDOR_ID = POV.VENDOR_ID
AND RLM2.PARENT_MEMBER_ID = RLM1.RESOURCE_LIST_MEMBER_ID (+)
AND RLM2.DISPLAY_FLAG = 'Y'
AND RLM2.ENABLED_FLAG = 'Y' UNION SELECT RL.RESOURCE_LIST_ID
, RL.NAME
, PA_RESOURCES_PKG.GET_RESOURCE_NAME(RE.RESOURCE_ID
, RE.RESOURCE_TYPE_ID)
, RT.NAME
, RT.RESOURCE_TYPE_CODE
, NVL(RLM1.ALIAS
, ' ')
, NVL(RLM2.PARENT_MEMBER_ID
, 0)
, NULL
, NULL
, RLM2.ALIAS
, RLM2.RESOURCE_LIST_MEMBER_ID
, RLM2.SORT_ORDER
, RLM2.ENABLED_FLAG
, RE.UNIT_OF_MEASURE
, RLM2.TRACK_AS_LABOR_FLAG
, NULL
, PAO.NAME
, NULL
, NULL
, NVL(RTA.EVENT_TYPE
, ' ')
, NVL(RTA.EXPENDITURE_TYPE
, ' ')
, NVL(RTA.EXPENDITURE_CATEGORY
, ' ')
, NVL(RTA.REVENUE_CATEGORY
, ' ')
, RLM2.MIGRATION_CODE
FROM PA_RESOURCE_LISTS RL
, PA_RESOURCES RE
, PA_RESOURCE_TYPES RT
, PA_RESOURCE_LIST_MEMBERS RLM1
, PA_RESOURCE_LIST_MEMBERS RLM2
, PA_RESOURCE_TXN_ATTRIBUTES RTA
, PA_ORGANIZATIONS_V PAO
WHERE RL.RESOURCE_LIST_ID = RLM2.RESOURCE_LIST_ID
AND RLM2.RESOURCE_ID = RE.RESOURCE_ID
AND RE.RESOURCE_TYPE_ID = RT.RESOURCE_TYPE_ID
AND RT.RESOURCE_TYPE_CODE = 'ORGANIZATION'
AND RE.RESOURCE_ID = RTA.RESOURCE_ID (+)
AND RTA.ORGANIZATION_ID = PAO.ORGANIZATION_ID
AND RLM2.PARENT_MEMBER_ID = RLM1.RESOURCE_LIST_MEMBER_ID (+)
AND RLM2.DISPLAY_FLAG = 'Y'
AND RLM2.ENABLED_FLAG = 'Y'