Search Results gms_adl_uk

Overview

APPS.GMS_EXPEND_ITEMS_ADJUST_V is a reporting and integration view within the Oracle EBS Grants Management (GMS) module. It presents expenditure item data at the award distribution line level, joining expenditure items to their corresponding award distribution lines. The view is designed to expose expenditure detail enriched with project, task, award, organization, person, vendor, and cost distribution attributes for reporting, analysis, and downstream integration.

The view text begins with the hint /*+INDEX(ADL GMS_ADL_UK)*/, explicitly directing the optimizer to use the GMS_ADL_UK unique key index on GMS_AWARD_DISTRIBUTIONS. This aliasing convention (ADL for award distribution line) confirms that the primary driving access path is the award distribution table, with expenditure items joined to it. This makes the view particularly efficient for award-centric queries in which the query predicate targets award or distribution line attributes. Because it exposes both raw and burdened cost figures derived from the distribution value percentage, the view is commonly used in cost analysis, reconciliation, and interface extraction scenarios.

Underlying Base Objects

The documented base objects underpinning this view span several EBS schemas and functional areas:

• Awards/Grants: GMS_AWARD_DISTRIBUTIONS (SYNONYM), aliased ADL, provides ADL_LINE_NUM, DISTRIBUTION_VALUE, and RAW_COST.
• Projects: PA_PROJECTS, PA_TASKS, PA_PROJECT_TYPES_ALL, PA_EXPENDITURE_TYPES, PA_EXPENDITURE_ITEMS_ALL, PA_EXPENDITURES_ALL, PA_EXPENDITURE_COMMENTS, PA_COST_DIST_LINES_ALL_BAS, PA_SYSTEM_LINKAGES, PA_TRANSACTION_SOURCES, and PA_CONVERSION_TYPES_V.
• Human Resources: PER_PEOPLE_F, PER_JOBS, HR_ALL_ORGANIZATION_UNITS_TL, HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY (packages).
• Purchasing: PO_VENDORS (VIEW).
• Lookups: FND_LOOKUP_VALUES, supplying descriptive meanings for status, unit of measure, system linkage, and project class.

The view consolidates these sources into a single denormalized structure, joining expenditure items to award distributions, with descriptive lookups resolved for coded values.

Key Columns

• ROW_ID, EXPENDITURE_ITEM_ID, EXPENDITURE_ID: Unique identifiers for the row and the underlying expenditure records.
• ADL_LINE_NUM: Award distribution line number from GMS_AWARD_DISTRIBUTIONS.
• PROJECT_ID, PROJECT_NUMBER, PROJECT_NAME, PROJECT_TYPE, PROJECT_CLASS: Project identification and classification attributes.
• TASK_ID, TASK_NUMBER, TASK_NAME: Task level detail for the expenditure.
• EXPENDITURE_ITEM_DATE, EXPENDITURE_TYPE, EXPENDITURE_CATEGORY, REVENUE_CATEGORY_CODE: Expenditure transaction classification.
• INCURRED_BY_PERSON_ID, EMPLOYEE_NAME, EMPLOYEE_NUMBER, JOB_NAME: Employee attribution.
• EXPENDITURE_ORGANIZATION_ID, EXPENDITURE_ORGANIZATION_NAME: Derived via NVL(OVERRIDE_TO_ORGANIZATION_ID, INCURRED_BY_ORGANIZATION_ID).
• VENDOR_ID, VENDOR_NUMBER, VENDOR_NAME: Non-labor vendor attribution.
• EMPLOYEE_VENDOR_NAME, EMPLOYEE_VENDOR_NUMBER: NVL-based reconciliation columns combining employee or vendor values.
• QUANTITY, RAW_COST, BURDENED_COST: Scaled by distribution value percentage, with BURDENED_COST and RAW_COST provided as aggregate amounts. RAW_COST_RATE and BURDENED_COST_RATE supply the rates.
• COST_DISTRIBUTED_FLAG, COST_DIST_REJECTION_CODE, COST_BURDEN_DISTRIBUTED_FLAG: Cost distribution status indicators.
• EXPENDITURE_STATUS_CODE, EXPENDITURE_STATUS_M: Status code and its lookup meaning.
• RECEIPT_CURRENCY_CODE, DENOM_CURRENCY_CODE, ACCT_CURRENCY, RECEIPT_EXCHANGE_RATE: Multi-currency attributes.

Common Use Cases and Queries

Typical uses include award cost reconciliation, expenditure reporting by project or task, and extraction interfaces for grants reporting.

• Award expenditure summary: reporting raw and burdened cost by award distribution line.
• Cost distribution validation: identifying items where COST_DISTRIBUTED_FLAG indicates pending or rejected distribution.
• Cross-functional reporting: combining project, employee, and vendor detail for grants analysis.

SELECT PROJECT_NUMBER, TASK_NUMBER, ADL_LINE_NUM,
       RAW_COST, BURDENED_COST
  FROM APPS.GMS_EXPEND_ITEMS_ADJUST_V
 WHERE EXPENDITURE_STATUS_CODE = 'APPROVED';

SELECT EMPLOYEE_VENDOR_NAME, SUM(RAW_COST) TOTAL_RAW
  FROM APPS.GMS_EXPEND_ITEMS_ADJUST_V
 GROUP BY EMPLOYEE_VENDOR_NAME;

Because the hint forces the GMS_ADL_UK index path, queries filtering on award distribution attributes generally perform best against this view.