Results for “source_reference”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PA_PROJECT_REQUESTS_V is a valid Oracle EBS view owned by the APPS schema and delivered as part of the Oracle Projects (PA) product family. It presents project request records — pre-project intake items that capture demand, funding, and customer details before a formal project is created and approved. The view consolidates data from the PA_PROJECT_REQUESTS transaction table with reference lookups, status definitions, application names, and Oracle Trading Community Architecture (TCA) party, site, location, and customer account information. In EBS 12.1.1 and 12.2.2 this view serves as both a reporting convenience and an integration surface, allowing external systems, concurrent programs, and custom reports to read a fully denormalized project request without writing complex multi-table joins.

The view is significant for the SOURCE_REFERENCE attribute, which carries the external or upstream identifier supplied when the project request was created. For example, when a project request originates from an external CRM, order capture, or third-party demand system, SOURCE_REFERENCE stores that originating key, enabling traceability between the source system and the resulting Oracle Projects request.

Underlying Base Objects

The view is defined over a primary transaction table plus a set of supporting reference objects:

• PA_PROJECT_REQUESTS — the driving table (aliased PR), holding request identity, status codes, source reference, value, currency, and customer identifiers.
• PA_LOOKUPS — resolves REQUEST_TYPE against the PROJECT_REQUEST_TYPE lookup type to produce REQUEST_STATUS_NAME.
• PA_PROJECT_STATUSES — provides PROJECT_STATUS_NAME and PROJECT_SYSTEM_STATUS_CODE where STATUS_TYPE = 'PROJ_REQ'.
• FND_APPLICATION_TL — supplies the application name (SOURCE_NAME) and is joined by language.
• HZ_PARTIES, HZ_PARTY_SITES, HZ_LOCATIONS, HZ_CUST_ACCOUNTS — TCA joins supplying party name/number, address, and customer account details. These are outer joins, so requests without a customer retain their rows.
• FND_ACCESS_CONTROL_UTIL — a package whose GET_ORG_NAME function resolves SOURCE_ORG_ID into SOURCE_ORGANIZATION.

All joins are implemented as inline predicates; no stored view definition resides on the base transaction table itself, so the view reflects current TCA and lookup values at query time.

Key Columns

• REQUEST_ID / REQUEST_NUMBER / REQUEST_NAME — primary keys and human-readable identity of the project request.
• REQUEST_TYPE / REQUEST_STATUS_NAME / REQUEST_SYSTEM_STATUS_CODE — decoded request classification and status.
• SOURCE_REFERENCE — the caller-supplied source key used to correlate the request back to an originating system or document.
• SOURCE_NAME / SOURCE_ORGANIZATION — the originating application and organization.
• VALUE / CURRENCY_CODE — expected request value and its currency.
• PARTY_NAME, PARTY_NUMBER, ADDRESS1-4, CITY, COUNTY, STATE, PROVINCE, COUNTRY, POSTAL_CODE — customer party and address detail.
• CUSTOMER_NUMBER / ACCOUNT_NAME — customer account identifiers.
• EXPECTED_PROJECT_APPROVAL_DATE / CLOSED_DATE — lifecycle date tracking.
• RECORD_VERSION_NUMBER — optimistic locking version.

Common Use Cases and Queries

Typical scenarios include reconciling incoming demand to source documents, monitoring project request backlog by status, and extracting customer and funding information for reporting. A query searching by the source reference would be:

• SELECT request_number, request_name, source_reference, source_name, value, currency_code FROM pa_project_requests_v WHERE source_reference = :p_source_ref;
• SELECT request_number, request_status_name, party_name, value FROM pa_project_requests_v WHERE request_system_status_code = 'OPEN';
• SELECT source_organization, COUNT(*) FROM pa_project_requests_v GROUP BY source_organization;

Because the view performs outer joins to TCA and language-sensitive joins to FND_APPLICATION_TL, queries should constrain on the source reference or status where possible to limit scan cost, and any application joining on SOURCE_REFERENCE should account for possible nulls.