SELECT PROJECT_NUMBER , PROJECT_NAME , PROJECT_ID , PROJECT_TYPE_CLASS_CODE , PROJECT_STATUS_CODE , ORG_ID , OPERATING_UNIT FROM PA_PROJECTS_BASIC_V WHERE TEMPLATE_FLAG <> 'Y' AND PA_EXPENDITURE_INQUIRY.GET_MODE = 'PROJECT' AND PA_PROJECT_STUS_UTILS.IS_PROJECT_IN_PURGE_STATUS(PROJECT_STATUS_CODE) <> 'Y' UNION ALL SELECT P.PROJECT_NUMBER , P.PROJECT_NAME , P.PROJECT_ID , P.PROJECT_TYPE_CLASS_CODE , P.PROJECT_STATUS_CODE , P.ORG_ID , P.OPERATING_UNIT FROM PA_PROJECTS_ALL_BASIC_V P , HR_ORGANIZATION_INFORMATION PLE , PA_IMPLEMENTATIONS IMP WHERE P.TEMPLATE_FLAG <> 'Y' AND PA_EXPENDITURE_INQUIRY.GET_MODE = 'CROSS-PROJECT' AND PA_PROJECT_STUS_UTILS.IS_PROJECT_IN_PURGE_STATUS(P.PROJECT_STATUS_CODE) <> 'Y' AND PLE.ORGANIZATION_ID (+) = IMP.ORG_ID AND PLE.ORG_INFORMATION_CONTEXT (+) = 'OPERATING UNIT INFORMATION' AND ( P.ORG_ID = IMP.ORG_ID OR P.ALLOW_CROSS_CHARGE_FLAG = 'Y' ) AND ( P.ORG_ID = IMP.ORG_ID OR (EXISTS (SELECT 1 FROM HR_ORGANIZATION_INFORMATION RLE WHERE RLE.ORG_INFORMATION_CONTEXT (+) = 'OPERATING UNIT INFORMATION' AND RLE.ORGANIZATION_ID (+) = P.ORG_ID AND ( P.ORG_ID = IMP.ORG_ID OR ( PLE.ORG_INFORMATION2 = RLE.ORG_INFORMATION2 AND ( EXISTS ( SELECT NULL FROM PA_CC_ORG_RELATIONSHIPS CO WHERE CO.PRVDR_ORG_ID = IMP.ORG_ID AND CO.RECVR_ORG_ID = P.ORG_ID AND CO.PRVDR_ALLOW_CC_FLAG = 'Y') OR (IMP.CC_ALLOW_IU_FLAG = 'Y' AND NOT EXISTS ( SELECT NULL FROM PA_CC_ORG_RELATIONSHIPS CO WHERE CO.PRVDR_ORG_ID = IMP.ORG_ID AND CO.RECVR_ORG_ID = P.ORG_ID AND CO.PRVDR_ALLOW_CC_FLAG = 'N') ) ) ) OR ( PLE.ORG_INFORMATION2 <> RLE.ORG_INFORMATION2 AND P.PROJECT_TYPE_CLASS_CODE <> 'CAPITAL' AND EXISTS ( SELECT NULL FROM PA_CC_ORG_RELATIONSHIPS CO WHERE CO.PRVDR_ORG_ID = IMP.ORG_ID AND CO.RECVR_ORG_ID = P.ORG_ID AND CO.PRVDR_ALLOW_CC_FLAG = 'Y' AND (( CO.PRVDR_PROJECT_ID IS NOT NULL AND CO.VENDOR_SITE_ID IS NOT NULL AND CO.CROSS_CHARGE_CODE = 'I') OR CO.CROSS_CHARGE_CODE = 'N' ) ) ) ) ) ) )