VIEW: APPS.PA_LAUNCH_PAGE_ACTIONS_V

Source

View Text - Preformatted

SELECT ppe.proj_element_id, dlvrver.element_version_id, class.lookup_code, class.meaning, Ppa.name, ppa.segment1, ppe.project_id, Dlvr.name, dlvr.element_number, dlvr.proj_element_id, Ppe.name, Pa_proj_elements_utils.get_pa_lookup_meaning('PA_DLVR_ACTION_FUNCTION', ppe.function_code), Ppevsch.scheduled_finish_date, ppevsch.scheduled_finish_date-TRUNC(SYSDATE), Requestor.party_name, requestor.party_id, owner.party_name, owner.party_id, ppe.manager_person_id, Status.project_status_name, Status.project_status_code, status.project_system_status_code FROM pa_proj_elements ppe, pa_proj_element_versions ppev, Pa_proj_elem_ver_schedule ppevsch, pa_projects_all ppa, Pa_object_relationships por, pa_proj_elements dlvr, pa_proj_element_versions dlvrver, Hz_parties requestor, hz_parties owner, pa_project_statuses status, pa_lookups class WHERE ppe.object_type='PA_ACTIONS' AND dlvr.object_type='PA_DELIVERABLES' AND Ppev.project_id=ppe.project_id AND Ppev.proj_element_id=ppe.proj_element_id AND Ppevsch.element_version_id=ppev.element_version_id AND Ppa.project_id=ppe.project_id AND Por.object_id_to2=ppe.proj_element_id AND Por.object_type_to='PA_ACTIONS' AND Por.object_type_from='PA_DELIVERABLES' AND Dlvr.proj_element_id=por.object_id_from2 AND requestor.party_id(+)=pa_utils.get_party_id(ppe.created_by) AND substr(owner.orig_system_reference,5)=ppe.manager_person_id AND substr(owner.orig_system_reference,1,4) = 'PER:' AND status.status_type(+) ='DELIVERABLE' AND status.project_status_code(+) =ppe.status_code AND class.lookup_type='PA_OBJECTS' AND class.lookup_code='PA_DELIVERABLES' AND dlvr.proj_element_id = dlvrver.proj_element_id AND por.relationship_type = 'A' AND por.relationship_subtype = 'DELIVERABLE_TO_ACTION' AND nvl(ppa.template_flag, 'N') <> 'Y' AND por.object_id_to1=ppev.element_version_id UNION ALL SELECT pca.ci_action_id, to_number(null), pcitb.ci_type_class_code, class.meaning, Ppa.name, ppa.segment1, pci.project_id, Pcittl.short_name, pci.ci_number, pca.ci_id, Pcc.comment_text, Type.meaning, Pca.date_required, pca.date_required-TRUNC(SYSDATE), Requestor.party_name, requestor.party_id, owner.party_name, owner.party_id, to_number(null), Status.project_status_name, Status.project_status_code, status.project_system_status_code FROM pa_ci_actions pca, pa_ci_comments pcc, pa_control_items pci, pa_ci_types_b pcitb, pa_ci_types_tl pcittl, pa_projects_all ppa, hz_parties requestor, hz_parties owner, pa_project_statuses status, pa_lookups type, pa_lookups class WHERE pci.ci_id=pca.ci_id AND pcittl.ci_type_id=pci.ci_type_id AND pcitb.ci_type_id = pci.ci_type_id AND ppa.project_id=pci.project_id AND status.status_type='CI_ACTION' AND status.project_status_code=pca.status_code AND type.lookup_type='PA_CI_ACTION_TYPES' AND type.lookup_code=pca.type_code AND requestor.party_id=pa_utils.get_party_id(pca.created_by) AND owner.party_id = pca.assigned_to AND pcc.ci_id=pca.ci_id AND pcc.ci_action_id=pca.ci_action_id AND pcc.type_code='REQUESTOR' AND class.lookup_type='PA_CI_TYPE_CLASSES' AND class.lookup_code=pcitb.ci_type_class_code AND pcittl.LANGUAGE = userenv('LANG') AND pci.status_code NOT IN (select project_status_code from pa_project_statuses where status_type = 'CONTROL_ITEM' and project_system_status_code = 'CI_DRAFT')

View Text - HTML Formatted

SELECT PPE.PROJ_ELEMENT_ID
, DLVRVER.ELEMENT_VERSION_ID
, CLASS.LOOKUP_CODE
, CLASS.MEANING
, PPA.NAME
, PPA.SEGMENT1
, PPE.PROJECT_ID
, DLVR.NAME
, DLVR.ELEMENT_NUMBER
, DLVR.PROJ_ELEMENT_ID
, PPE.NAME
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DLVR_ACTION_FUNCTION'
, PPE.FUNCTION_CODE)
, PPEVSCH.SCHEDULED_FINISH_DATE
, PPEVSCH.SCHEDULED_FINISH_DATE-TRUNC(SYSDATE)
, REQUESTOR.PARTY_NAME
, REQUESTOR.PARTY_ID
, OWNER.PARTY_NAME
, OWNER.PARTY_ID
, PPE.MANAGER_PERSON_ID
, STATUS.PROJECT_STATUS_NAME
, STATUS.PROJECT_STATUS_CODE
, STATUS.PROJECT_SYSTEM_STATUS_CODE
FROM PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_SCHEDULE PPEVSCH
, PA_PROJECTS_ALL PPA
, PA_OBJECT_RELATIONSHIPS POR
, PA_PROJ_ELEMENTS DLVR
, PA_PROJ_ELEMENT_VERSIONS DLVRVER
, HZ_PARTIES REQUESTOR
, HZ_PARTIES OWNER
, PA_PROJECT_STATUSES STATUS
, PA_LOOKUPS CLASS
WHERE PPE.OBJECT_TYPE='PA_ACTIONS'
AND DLVR.OBJECT_TYPE='PA_DELIVERABLES'
AND PPEV.PROJECT_ID=PPE.PROJECT_ID
AND PPEV.PROJ_ELEMENT_ID=PPE.PROJ_ELEMENT_ID
AND PPEVSCH.ELEMENT_VERSION_ID=PPEV.ELEMENT_VERSION_ID
AND PPA.PROJECT_ID=PPE.PROJECT_ID
AND POR.OBJECT_ID_TO2=PPE.PROJ_ELEMENT_ID
AND POR.OBJECT_TYPE_TO='PA_ACTIONS'
AND POR.OBJECT_TYPE_FROM='PA_DELIVERABLES'
AND DLVR.PROJ_ELEMENT_ID=POR.OBJECT_ID_FROM2
AND REQUESTOR.PARTY_ID(+)=PA_UTILS.GET_PARTY_ID(PPE.CREATED_BY)
AND SUBSTR(OWNER.ORIG_SYSTEM_REFERENCE
, 5)=PPE.MANAGER_PERSON_ID
AND SUBSTR(OWNER.ORIG_SYSTEM_REFERENCE
, 1
, 4) = 'PER:'
AND STATUS.STATUS_TYPE(+) ='DELIVERABLE'
AND STATUS.PROJECT_STATUS_CODE(+) =PPE.STATUS_CODE
AND CLASS.LOOKUP_TYPE='PA_OBJECTS'
AND CLASS.LOOKUP_CODE='PA_DELIVERABLES'
AND DLVR.PROJ_ELEMENT_ID = DLVRVER.PROJ_ELEMENT_ID
AND POR.RELATIONSHIP_TYPE = 'A'
AND POR.RELATIONSHIP_SUBTYPE = 'DELIVERABLE_TO_ACTION'
AND NVL(PPA.TEMPLATE_FLAG
, 'N') <> 'Y'
AND POR.OBJECT_ID_TO1=PPEV.ELEMENT_VERSION_ID UNION ALL SELECT PCA.CI_ACTION_ID
, TO_NUMBER(NULL)
, PCITB.CI_TYPE_CLASS_CODE
, CLASS.MEANING
, PPA.NAME
, PPA.SEGMENT1
, PCI.PROJECT_ID
, PCITTL.SHORT_NAME
, PCI.CI_NUMBER
, PCA.CI_ID
, PCC.COMMENT_TEXT
, TYPE.MEANING
, PCA.DATE_REQUIRED
, PCA.DATE_REQUIRED-TRUNC(SYSDATE)
, REQUESTOR.PARTY_NAME
, REQUESTOR.PARTY_ID
, OWNER.PARTY_NAME
, OWNER.PARTY_ID
, TO_NUMBER(NULL)
, STATUS.PROJECT_STATUS_NAME
, STATUS.PROJECT_STATUS_CODE
, STATUS.PROJECT_SYSTEM_STATUS_CODE
FROM PA_CI_ACTIONS PCA
, PA_CI_COMMENTS PCC
, PA_CONTROL_ITEMS PCI
, PA_CI_TYPES_B PCITB
, PA_CI_TYPES_TL PCITTL
, PA_PROJECTS_ALL PPA
, HZ_PARTIES REQUESTOR
, HZ_PARTIES OWNER
, PA_PROJECT_STATUSES STATUS
, PA_LOOKUPS TYPE
, PA_LOOKUPS CLASS
WHERE PCI.CI_ID=PCA.CI_ID
AND PCITTL.CI_TYPE_ID=PCI.CI_TYPE_ID
AND PCITB.CI_TYPE_ID = PCI.CI_TYPE_ID
AND PPA.PROJECT_ID=PCI.PROJECT_ID
AND STATUS.STATUS_TYPE='CI_ACTION'
AND STATUS.PROJECT_STATUS_CODE=PCA.STATUS_CODE
AND TYPE.LOOKUP_TYPE='PA_CI_ACTION_TYPES'
AND TYPE.LOOKUP_CODE=PCA.TYPE_CODE
AND REQUESTOR.PARTY_ID=PA_UTILS.GET_PARTY_ID(PCA.CREATED_BY)
AND OWNER.PARTY_ID = PCA.ASSIGNED_TO
AND PCC.CI_ID=PCA.CI_ID
AND PCC.CI_ACTION_ID=PCA.CI_ACTION_ID
AND PCC.TYPE_CODE='REQUESTOR'
AND CLASS.LOOKUP_TYPE='PA_CI_TYPE_CLASSES'
AND CLASS.LOOKUP_CODE=PCITB.CI_TYPE_CLASS_CODE
AND PCITTL.LANGUAGE = USERENV('LANG')
AND PCI.STATUS_CODE NOT IN (SELECT PROJECT_STATUS_CODE
FROM PA_PROJECT_STATUSES
WHERE STATUS_TYPE = 'CONTROL_ITEM'
AND PROJECT_SYSTEM_STATUS_CODE = 'CI_DRAFT')