Search Results ps_ubkt_dtl




Overview
Explore in AI Chat

Overview

PS_UBKT_DTL is a table in the GMP (Process Manufacturing Process Planning) schema of Oracle E-Business Suite. It stores the detailed line-level data behind the Material Activity Report, a planning and inventory analysis output used in process manufacturing environments. Each row represents a single transaction or balance movement for a material item within a reporting bucket, allowing planners and inventory analysts to reconstruct how an item's on-hand quantity changed across a reporting period.

The table carries a foreign key on MATL_REP_ID referencing PS_MATL_HDR, which establishes the reporting run or report header to which each detail line belongs. In Data Vault modeling terms, the mined relationship structure suggests PS_UBKT_DTL behaves as a satellite-leaning table relative to PS_MATL_HDR: it holds descriptive and measurable attributes tied to a parent header record rather than acting as an independent hub or an associative link between two hubs.

Key Information Stored

The documented physical schema contains 18 columns. The most significant include:

  • LINE_ID – the surrogate primary key uniquely identifying each detail line.
  • MATL_REP_ID – the foreign key to PS_MATL_HDR, linking the line to its parent report. Together with ITEM_ID this forms the primary business-key candidate for a report/item combination.
  • ITEM_ID and INVENTORY_ITEM_ID – identifiers for the material item being reported.
  • ORGN_CODE and ORGANIZATION_ID – the inventory organization context for the transaction.
  • WHSE_CODE – the warehouse or subinventory location.
  • DOC_TYPE and DOC_NO – the document type and number of the underlying inventory transaction.
  • TRANS_DATE and TRANS_QTY – the date and quantity of the movement.
  • START_BALANCE, PAST_DUE, and BALANCE – opening balance, past-due quantity, and resulting on-hand balance for the line.
  • PLANNING_CLASS, PLANNING_CATEGORY_ID, and CRITICAL_IND – planning classification, category, and critical-item flag used for prioritization.
  • CUST_VEND – customer or vendor reference associated with the movement.

Common Use Cases and Queries

Typical usage centers on the Material Activity Report and reconciliation of inventory movements. Analysts retrieve all detail lines for a given report header:

SELECT d.line_id, d.item_id, d.orgn_code, d.doc_type, d.doc_no,
       d.trans_date, d.trans_qty, d.start_balance, d.balance
FROM   gmp.ps_ubkt_dtl d
WHERE  d.matl_rep_id = :report_id
ORDER BY d.item_id, d.trans_date;

Reconciliation queries compare the reconstructed balance against the inventory on-hand:

SELECT d.item_id, d.start_balance, SUM(d.trans_qty) AS net_movement, d.balance
FROM   gmp.ps_ubkt_dtl d
WHERE  d.matl_rep_id = :report_id
GROUP BY d.item_id, d.start_balance, d.balance;

Planning-class and critical-item reports filter on PLANNING_CLASS and CRITICAL_IND, while organization-scoped reports join the header and filter on ORGANIZATION_ID or ORGN_CODE. Transaction-trace queries join on DOC_TYPE and DOC_NO to reconcile detail lines against source inventory transactions.

Related Objects

  • PS_MATL_HDR – parent header table; joined via PS_UBKT_DTL.MATL_REP_ID = PS_MATL_HDR.MATL_REP_ID. This is the primary and documented relationship for PS_UBKT_DTL.
  • PS_MATL_HDR (additional attributes) – supplies report-level parameters such as organization and report date used to scope detail queries.
  • Inventory item master – looked up through ITEM_ID / INVENTORY_ITEM_ID to resolve item descriptions.
  • Organization definitions – resolved through ORGANIZATION_ID and ORGN_CODE.
  • Inventory transaction tables – matched on DOC_TYPE and DOC_NO for movement genealogy.
  • Material Activity Report concurrent program – the primary consumer that populates and reads this table.

Because only one foreign key is documented, PS_MATL_HDR remains the essential join point; other relationships are inferred through descriptive columns rather than declared constraints.