VIEW: APPS.PJM_PROJECTS_V

Source

View Text - Preformatted

SELECT P.project_id , P.project_number , P.project_name , P.project_description , P.project_number_sort_order , P.start_date , P.completion_date , 2 , p.org_id FROM HR_ORGANIZATION_INFORMATION PLE , PA_PROJECTS_ALL_BASIC_V P , PA_IMPLEMENTATIONS_ALL IMP WHERE imp.org_id = (select ORG_INFORMATION3 from HR_ORGANIZATION_INFORMATION HOI2 where ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information' and HOI2.ORGANIZATION_ID = FND_PROFILE.VALUE('MFG_ORGANIZATION_ID') ) AND P.charges_allowed_flag = 'Y' AND nvl(P.cc_prvdr_flag, 'N') <> 'Y' AND (P.proj_set_of_books_id = IMP.set_of_books_id OR EXISTS (select 1 from pa_budgetary_control_options bc, pa_projects_all pa where bc.project_id(+) = Pa.project_id and pa.project_id = p.Project_id and nvl(bc.BDGT_CNTRL_FLAG,'N') = 'N') ) 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' ) ) ) ) ) ) ) UNION ALL SELECT S.PROJECT_ID , S.PROJECT_NUMBER , S.PROJECT_NAME , S.PROJECT_NAME , S.PROJECT_NUMBER , TO_DATE(NULL) , TO_DATE(NULL) , 1 SEIBAN_NUMBER_FLAG , S.OPERATING_UNIT ORG_ID FROM PJM_SEIBAN_NUMBERS S

View Text - HTML Formatted

SELECT P.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.PROJECT_DESCRIPTION
, P.PROJECT_NUMBER_SORT_ORDER
, P.START_DATE
, P.COMPLETION_DATE
, 2
, P.ORG_ID
FROM HR_ORGANIZATION_INFORMATION PLE
, PA_PROJECTS_ALL_BASIC_V P
, PA_IMPLEMENTATIONS_ALL IMP
WHERE IMP.ORG_ID = (SELECT ORG_INFORMATION3
FROM HR_ORGANIZATION_INFORMATION HOI2
WHERE ( HOI2.ORG_INFORMATION_CONTEXT || '') ='ACCOUNTING INFORMATION'
AND HOI2.ORGANIZATION_ID = FND_PROFILE.VALUE('MFG_ORGANIZATION_ID') )
AND P.CHARGES_ALLOWED_FLAG = 'Y'
AND NVL(P.CC_PRVDR_FLAG
, 'N') <> 'Y'
AND (P.PROJ_SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID OR EXISTS (SELECT 1
FROM PA_BUDGETARY_CONTROL_OPTIONS BC
, PA_PROJECTS_ALL PA
WHERE BC.PROJECT_ID(+) = PA.PROJECT_ID
AND PA.PROJECT_ID = P.PROJECT_ID
AND NVL(BC.BDGT_CNTRL_FLAG
, 'N') = 'N') )
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' ) ) ) ) ) ) ) UNION ALL SELECT S.PROJECT_ID
, S.PROJECT_NUMBER
, S.PROJECT_NAME
, S.PROJECT_NAME
, S.PROJECT_NUMBER
, TO_DATE(NULL)
, TO_DATE(NULL)
, 1 SEIBAN_NUMBER_FLAG
, S.OPERATING_UNIT ORG_ID
FROM PJM_SEIBAN_NUMBERS S