Results for “pjm_borrow_transactions_u1”

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

Overview

The PJM.PJM_BORROW_TRANSACTIONS table is a transactional data object within the Oracle E-Business Suite Projects (PJM) schema. It captures inter-project borrow transactions at the moment they are entered into MTL_MATERIAL_TRANSACTIONS, forming the authoritative record of loaned material between a lending project/task and a borrowing project/task. In addition to recording the original entry event, the table persists the outstanding loan quantity for each borrow transaction, enabling the system to track partial or complete paybacks over time. Because a single borrow transaction can be settled through multiple payback transactions recorded in PJM_BORROW_PAYBACKS, this table serves as the parent (header-level) container for the full borrowing lifecycle.

From a dimensional modeling perspective, the metadata's heuristic Data Vault classification places this object as satellite-leaning. This suggests it is best modeled as descriptive context attached to a parent borrowing entity — the BORROW_TRANSACTION_ID behaves as the surrogate key (backed by the unique index PJM_BORROW_TRANSACTIONS_U1), while the surrounding project, item, and organization references act as link-style foreign key relationships to their respective hubs.

Key Information Stored

The table resides in the APPS_TS_TX_DATA tablespace and contains 21 documented columns. The most significant include:

• BORROW_TRANSACTION_ID — Surrogate primary key, uniquely identifying each inter-project borrow transaction. It is the sole business-key candidate via the unique index PJM_BORROW_TRANSACTIONS_U1.
• BORROW_PROJECT_ID / BORROW_TASK_ID — Identifier of the project and task receiving the loaned material.
• LENDING_PROJECT_ID / LENDING_TASK_ID — Identifier of the project and task supplying the material.
• ORGANIZATION_ID — Inventory organization context for the transaction (indexed by _N4).
• INVENTORY_ITEM_ID — Loaned inventory item (indexed by _N3).
• REVISION — Revision designation of the loaned item.
• LOAN_QUANTITY — Original quantity loaned in the transaction.
• OUTSTANDING_QUANTITY — Remaining unsettled quantity; reduced as paybacks are recorded.
• LOAN_DATE and SCHEDULED_PAYBACK_DATE — Lifecycle date attributes for loan initiation and expected return.
• Standard Who columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and Extended Who columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) for audit and concurrency tracing.

Common Use Cases and Queries

Typical reporting scenarios include identifying all outstanding loans for a lending project, calculating payback progress, and reconciling borrow balances against material transaction history.

• Outstanding loan report by lending project:

  SELECT borrow_transaction_id, lending_project_id, inventory_item_id,
         loan_quantity, outstanding_quantity, loan_date
  FROM   pjm_borrow_transactions
  WHERE  lending_project_id = :project_id
  AND    outstanding_quantity > 0;

• Payback progress ratio: 1 - (OUTSTANDING_QUANTITY / LOAN_QUANTITY) per transaction.
• Overdue loans where SCHEDULED_PAYBACK_DATE < SYSDATE and OUTSTANDING_QUANTITY > 0.
• Item-level loan exposure by joining to MTL_SYSTEM_ITEMS_B for descriptive flexfields and item descriptions.

Related Objects

• MTL_MATERIAL_TRANSACTIONS — Source of the borrow entry; joined on BORROW_TRANSACTION_ID.
• PJM_BORROW_PAYBACKS — Child table; joins on BORROW_TRANSACTION_ID to record payback events against outstanding quantities.
• PA_PROJECTS_ALL — Referenced twice for borrow and lending projects via BORROW_PROJECT_ID and LENDING_PROJECT_ID.
• PA_TASKS — Referenced for BORROW_TASK_ID and LENDING_TASK_ID task context.
• MTL_SYSTEM_ITEMS_B — Supplies item attributes via INVENTORY_ITEM_ID.
• MTL_ITEM_REVISIONS_B — Supports revision-level detail for loaned items.