Search Results resource_instance_id

Overview

GMP_RESOURCE_AVAIL_V is a documented view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the GMP Process Manufacturing Process Planning product family. The view exposes resource availability at the organization, calendar, resource, and resource instance level, and is defined with a SELECT DISTINCT clause that filters the availability records held in the base table GMP_RESOURCE_AVAIL. Its role is to provide a reporting- and integration-friendly projection of resource availability that respects the scheduling indicator configured on each resource. In practice, the view answers the question: which resource instances are genuinely available for scheduling, given how each resource is scheduled.

Underlying Base Objects

The view is defined over two referenced objects, both surfaced as APPS synonyms: GMP_RESOURCE_AVAIL (the availability table) and CR_RSRC_DTL (the resource master detail table). The underlying SQL joins them on RESOURCE_ID:

SELECT DISTINCT A.ORGANIZATION_ID, A.CALENDAR_CODE, A.RESOURCE_ID, A.RESOURCE_INSTANCE_ID FROM GMP_RESOURCE_AVAIL A, CR_RSRC_DTL B WHERE B.RESOURCE_ID = A.RESOURCE_ID AND DECODE(B.SCHEDULE_IND, 2, A.RESOURCE_INSTANCE_ID, DECODE(A.RESOURCE_INSTANCE_ID, 0, 0, -1)) <> -1

CR_RSRC_DTL supplies the SCHEDULE_IND attribute and governs which rows qualify. For resources whose SCHEDULE_IND equals 2, the view retains rows keyed by their actual RESOURCE_INSTANCE_ID. For all other scheduling indicators, only the aggregate row (RESOURCE_INSTANCE_ID = 0) is retained, while instance-specific rows (values other than 0, mapped to -1) are excluded by the filter predicate. The DISTINCT clause collapses any duplicate combinations produced by the join.

Key Columns

• ORGANIZATION_ID — identifies the inventory organization that owns the resource availability definition.
• CALENDAR_CODE — the calendar associated with the availability record, establishing the working-time pattern.
• RESOURCE_ID — the resource for which availability is expressed; joins back to CR_RSRC_DTL on this column.
• RESOURCE_INSTANCE_ID — the specific instance of the resource. A value of 0 represents the aggregate resource rather than a discrete instance; non-zero values identify an individual instance. This is the column users most commonly search for when diagnosing why a particular resource instance does or does not appear.

Common Use Cases and Queries

The view is typically used to validate whether resource instances are visible to planning and scheduling logic, to reconcile availability records against the resource master, and to feed downstream reporting. A representative query lists availability for a given organization and resource:

SELECT organization_id, calendar_code, resource_id, resource_instance_id FROM apps.gmp_resource_avail_v WHERE organization_id = :org_id AND resource_id = :resource_id;

A frequent diagnostic is to compare the view against the base table to confirm filtering behavior — for example, checking which resource instances are suppressed because SCHEDULE_IND is not 2:

SELECT v.resource_id, v.resource_instance_id FROM apps.gmp_resource_avail_v v WHERE v.resource_instance_id = :resource_instance_id;

Because the view preserves distinct combinations only, it is safe for existence checks and joins into planning extracts without requiring additional de-duplication. Queries filtering on RESOURCE_INSTANCE_ID = 0 isolate aggregate-level availability, while non-zero filters target instance-level availability applicable to resources scheduled by instance.