Search Results pjm_borrow_payback_summary_v




Overview
Explore in AI Chat

Overview

PJM_BORROW_PAYBACK_SUMMARY_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, defined within the Project Manufacturing (PJM) product family. Its documented description is the "Inter-project borrow/payback summary view." The view consolidates outstanding borrow and payback activity that occurs between a borrowing project and a lending project, aggregating inventory quantities and aging them into configurable time buckets. It serves as a reporting and inquiry layer on top of the transaction-level borrow data held in PJM_BORROW_TRANSACTIONS, allowing users to monitor how much material remains unpaid at any point in time.

In both Oracle EBS 12.1.1 and 12.2.2, Project Manufacturing supports the sharing of inventory across projects through borrow/payback transactions. Rather than a true physical transfer, the borrowing project records a liability against a lending project, and the lending project records a corresponding loan. This view exists to answer the recurring operational question: how much material is still owed, and for how long has it been outstanding? By summarizing rather than listing individual transactions, it supports dashboard-style reporting and reduces query load against the transaction table.

Underlying Base Objects

The view is defined over four referenced objects: PJM_BORROW_TRANSACTIONS (accessed via a synonym), PJM_PROJECT_PARAMETERS (synonym), CST_COST_GROUPS (synonym), and the PJM_PROJECT package. It also references the PJM_BORROW_PAYBACK package, which contributes the BUCKET_SIZE value used to age the loans. The central fact source is PJM_BORROW_TRANSACTIONS, aliased as PBT, which supplies organization, item, revision, project, task, loan date, and outstanding quantity.

PJM_PROJECT_PARAMETERS is joined twice: once as PP1 for the borrowing project and once as PP2 for the lending project, keyed on organization and project. These joins retrieve the costing group assignment for each side. CST_COST_GROUPS is likewise joined twice (CG1 and CG2) using NVL of the project parameter's costing group and the default value 1, producing human-readable cost group identifiers for both the borrowing and lending project. The PJM_PROJECT package is called repeatedly in the SELECT list through its IDTONUM and IDTONAME functions to convert numeric project and task IDs into display values.

Key Columns

The view exposes grouping and identity columns including ORGANIZATION_ID, INVENTORY_ITEM_ID, and REVISION, which together identify the borrowed material. Project and task identity are captured on both sides: BORROW_PROJECT_ID, BORROW_TASK_ID, LENDING_PROJECT_ID, and LENDING_TASK_ID, each accompanied by numeric and name conversions generated by the PJM_PROJECT package functions.

Cost ownership is represented by CG1.COST_GROUP_ID/COST_GROUP for the borrowing project and CG2.COST_GROUP_ID/COST_GROUP for the lending project. The quantitative measure is SUM(PBT.OUTSTANDING_QUANTITY), the aggregate remaining unpaid quantity. Four additional SUM(DECODE(...)) expressions classify that outstanding quantity into aging buckets based on the number of bucket periods elapsed since LOAN_DATE, where each period equals PJM_BORROW_PAYBACK.BUCKET_SIZE days. These produce current, one-period, two-period, and greater-than-two-period outstanding balances respectively. All rows are GROUP BY organization, item, revision, both project IDs, both task IDs, and both cost group IDs.

Common Use Cases and Queries

Typical usage includes aging analyses to identify long-outstanding borrows, cost group reconciliation across projects, and item-level inquiries to locate which projects are holding unpaid material. A representative query is:

  • SELECT organization_id, inventory_item_id, borrow_project_id, lending_project_id, SUM(outstanding_quantity) FROM pjm_borrow_payback_summary_v GROUP BY organization_id, inventory_item_id, borrow_project_id, lending_project_id;
  • SELECT borrow_project_id, lending_project_id, SUM(outstanding_quantity) FROM pjm_borrow_payback_summary_v WHERE organization_id = :org_id GROUP BY borrow_project_id, lending_project_id ORDER BY 3 DESC;
  • SELECT inventory_item_id, SUM(outstanding_quantity) FROM pjm_borrow_payback_summary_v WHERE borrow_project_id = :project_id GROUP BY inventory_item_id;

Because the view already aggregates, callers should treat it as a reporting source rather than a transactional interface. Aging bucket values depend on the runtime SYSDATE and the BUCKET_SIZE parameter, so results vary by query date.