VIEW: APPS.PA_ORG_AUTHORITY_V

Source

View Text - Preformatted

SELECT distinct(to_number(fg.instance_pk1_value)) ,pa_resource_utils.get_organization_name(to_number(fg.instance_pk1_value)) FROM fnd_grants fg, fnd_objects fo, per_all_people_f per, wf_roles wfr WHERE per.person_id = pa_resource_utils.get_person_id AND fg.object_id = fo.object_id AND fo.obj_name = 'ORGANIZATION' AND fg.instance_type = 'INSTANCE' AND fg.grantee_key = wfr.name AND wfr.orig_system = 'HZ_PARTY' AND per.party_id = wfr.orig_system_id AND trunc(sysdate) between per.effective_start_date and per.effective_end_date AND TRUNC(SYSDATE) BETWEEN TRUNC(fg.start_date) AND TRUNC(NVL(fg.END_DATE, SYSDATE+1)) AND ( pa_resource_utils.get_selected_flag = 'R' OR pa_resource_utils.get_selected_flag = 'B') UNION SELECT organization_id_child org_id ,pa_resource_utils.get_organization_name(organization_id_child) FROM per_org_structure_elements CONNECT BY PRIOR organization_id_child = organization_id_parent AND org_structure_version_id = pa_resource_utils.get_version_id START WITH organization_id_parent = pa_resource_utils.get_start_org_id AND org_structure_version_id = pa_resource_utils.get_version_id AND ( pa_resource_utils.get_selected_flag = 'O' OR pa_resource_utils.get_selected_flag = 'B') UNION SELECT pa_resource_utils.get_start_org_id ,pa_resource_utils.get_organization_name(pa_resource_utils.get_start_org_id) from dual where ( pa_resource_utils.get_selected_flag = 'O' OR pa_resource_utils.get_selected_flag = 'B')

View Text - HTML Formatted

SELECT DISTINCT(TO_NUMBER(FG.INSTANCE_PK1_VALUE))
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(TO_NUMBER(FG.INSTANCE_PK1_VALUE))
FROM FND_GRANTS FG
, FND_OBJECTS FO
, PER_ALL_PEOPLE_F PER
, WF_ROLES WFR
WHERE PER.PERSON_ID = PA_RESOURCE_UTILS.GET_PERSON_ID
AND FG.OBJECT_ID = FO.OBJECT_ID
AND FO.OBJ_NAME = 'ORGANIZATION'
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.GRANTEE_KEY = WFR.NAME
AND WFR.ORIG_SYSTEM = 'HZ_PARTY'
AND PER.PARTY_ID = WFR.ORIG_SYSTEM_ID
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))
AND ( PA_RESOURCE_UTILS.GET_SELECTED_FLAG = 'R' OR PA_RESOURCE_UTILS.GET_SELECTED_FLAG = 'B') UNION SELECT ORGANIZATION_ID_CHILD ORG_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(ORGANIZATION_ID_CHILD)
FROM PER_ORG_STRUCTURE_ELEMENTS CONNECT BY PRIOR ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT
AND ORG_STRUCTURE_VERSION_ID = PA_RESOURCE_UTILS.GET_VERSION_ID START WITH ORGANIZATION_ID_PARENT = PA_RESOURCE_UTILS.GET_START_ORG_ID
AND ORG_STRUCTURE_VERSION_ID = PA_RESOURCE_UTILS.GET_VERSION_ID
AND ( PA_RESOURCE_UTILS.GET_SELECTED_FLAG = 'O' OR PA_RESOURCE_UTILS.GET_SELECTED_FLAG = 'B') UNION SELECT PA_RESOURCE_UTILS.GET_START_ORG_ID
, PA_RESOURCE_UTILS.GET_ORGANIZATION_NAME(PA_RESOURCE_UTILS.GET_START_ORG_ID)
FROM DUAL
WHERE ( PA_RESOURCE_UTILS.GET_SELECTED_FLAG = 'O' OR PA_RESOURCE_UTILS.GET_SELECTED_FLAG = 'B')