Search Results mrp_ap_org_units_sn

Overview

MRP_AP_ORGANIZATION_SITES_V is a reporting view in the Oracle E-Business Suite (EBS) Master Scheduling/MRP (MRP) module. It consolidates organization unit and site (location) information drawn from HR-style snapshots that are maintained within the MRP schema. The view exposes a partner-oriented projection of organization units joined to their associated locations, producing identifiers, addresses, and type/Rank columns suitable for downstream integration and reporting.

The "AP" prefix and the _SN suffix indicate that this is a snapshot-style construct used by Advanced Planning (AP) and supply chain planning processes. It is designed for read-only consumption, providing a stable, denormalized representation of organizations and their sites. In EBS releases 12.1.1 and 12.2.2, this view is available under the MRP module and is typically referenced by planning integrations, partner data extracts, and interface programs. The ETRM metadata notes that the view is "Not implemented in this database," meaning it is not deployed as a persistent object in every environment and is provided as a definitional/metadata artifact; consumers should probe for its existence before relying on it.

Underlying Base Objects

The view's definition is documented as a three-way outer join across snapshot tables in the MRP schema:

• MRP_AP_HR_LOC_ALL_SN HL — supplies location/address attributes including LOCATION_ID, LOCATION_CODE, address lines, region, postal code, country, business group, and row rank (RN).
• MRP_AP_ORG_UNITS_SN HAO — supplies organization unit records keyed by ORGANIZATION_ID and LOCATION_ID, with row rank (RN). It is the join driver between locations and organizations.
• MRP_AP_MRP_PARAS_SN ORG — supplies organization parameters keyed by ORGANIZATION_ID, with row rank (RN).

The join predicates apply a business-group filter using NVL(HL.BUSINESS_GROUP_ID, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID,-99)) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID,-99), then left-join locations to organization units (HL.LOCATION_ID (+)= HAO.LOCATION_ID), and organization units to organization parameters (HAO.ORGANIZATION_ID (+)= ORG.ORGANIZATION_ID). The ETRM metadata lists no documented referenced base objects, so this chain is grounded directly in the view text.

Key Columns

• SR_TP_ID — the source trading partner identifier, aliased from ORG.ORGANIZATION_ID. Identifies the organization.
• SR_TP_SITE_ID — the source trading partner site identifier, NVL(HL.LOCATION_ID, -1). Defaults to -1 when no location is associated.
• LOCATION — the location code from HL.LOCATION_CODE.
• PARTNER_ADDRESS — a concatenation of ADDRESS_LINE_1..3, REGION_1, REGION_2, POSTAL_CODE, and COUNTRY, separated by commas.
• PARTNER_TYPE — a constant value of 3, classifying the partner type.
• RN1, RN2, RN3 — row-number/rank values from ORG, HAO, and HL respectively, typically used for de-duplication or filtering to a single row per site.

Common Use Cases and Queries

Typical uses include partner/site address extraction for planning integrations, validating organization-to-site mappings, and feeding downstream trading-partner models. RN columns are frequently filtered to 1 to resolve duplicates.

• List partner sites with addresses for a business group.
• Identify organizations without a mapped location (SR_TP_SITE_ID = -1).
• De-duplicate to one address per site using RN1/RN2/RN3 = 1.

Sample SQL:

• SELECT SR_TP_ID, SR_TP_SITE_ID, LOCATION, PARTNER_ADDRESS FROM MRP_AP_ORGANIZATION_SITES_V WHERE RN1=1 AND RN2=1 AND RN3=1 ORDER BY SR_TP_ID;
• SELECT SR_TP_ID, SR_TP_SITE_ID FROM MRP_AP_ORGANIZATION_SITES_V WHERE SR_TP_SITE_ID = -1;
• SELECT COUNT(DISTINCT SR_TP_SITE_ID) FROM MRP_AP_ORGANIZATION_SITES_V;