Search Results lending_task_id

Overview

PJM_BORROW_PAYBACK_DETAIL_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PJM – Project Manufacturing product family. It exposes inter-project borrow and payback detail for inventory material transactions, allowing project-centric organizations to reconcile quantities issued from a lending project to a borrowing project against quantities subsequently returned. The object is documented as VALID in ETRM for release 12.2.2 and is equally applicable to 12.1.1, since the underlying borrow/payback tables share the same structure across both releases. Its primary role is analytical and integration-oriented: it flattens loan and repayment activity into a single row-per-transaction result set suitable for aging reports, open-balance analysis, and ad hoc inquiry.

Underlying Base Objects

The view is defined over four base objects, all referenced through APPS synonyms:

• PJM_BORROW_TRANSACTIONS – the core borrow record holding loan quantities, outstanding balances, loan dates, and the borrowing and lending project/task identifiers.
• PJM_BORROW_PAYBACKS – the repayment record linking a payback transaction to its originating borrow transaction and carrying the payback quantity.
• MTL_MATERIAL_TRANSACTIONS – supplies the actual transaction date, transaction type identifier, and organization/item context for both the borrow and payback sides.
• MTL_TRANSACTION_TYPES – supplies the descriptive transaction type name used for display.

Structurally, the view is a UNION of two statements. The first statement drives from PJM_BORROW_TRANSACTIONS joined to MTL_MATERIAL_TRANSACTIONS and MTL_TRANSACTION_TYPES on the borrow transaction, populating LOAN_QUANTITY and OUTSTANDING_QUANTITY. The second statement drives from PJM_BORROW_PAYBACKS joined back to PJM_BORROW_TRANSACTIONS, MTL_MATERIAL_TRANSACTIONS, and MTL_TRANSACTION_TYPES on the payback transaction, populating PAYBACK_QUANTITY. Consequently, LOAN and PAYBACK measures are mutually exclusive within a single row; a consumer must aggregate across rows to derive net positions.

Key Columns

• ORGANIZATION_ID / INVENTORY_ITEM_ID / REVISION – identify the inventory context of the borrowed item.
• BORROW_PROJECT_ID / BORROW_TASK_ID / LENDING_PROJECT_ID / LENDING_TASK_ID – define the inter-project relationship, which is the defining characteristic of PJM borrow/payback.
• TRANSACTION_ID / BORROW_TRANSACTION_ID – in a borrow row, TRANSACTION_ID is the borrow transaction; in a payback row, TRANSACTION_ID is the payback transaction and BORROW_TRANSACTION_ID points back to the originating loan.
• TRANSACTION_DATE / TRANSACTION_TYPE_ID / TRANSACTION_TYPE_NAME / TRANSACTION_TYPE_DISP – the activity date and the descriptive type; the display column is prefixed with a blank space for payback rows to distinguish direction in reports.
• LOAN_QUANTITY – quantity borrowed; NULL on payback rows.
• OUTSTANDING_QUANTITY – the unconsumed loan balance carried from the borrow transaction; NULL on payback rows. This is the column most frequently queried, since any value greater than zero represents an open obligation.
• PAYBACK_QUANTITY – quantity returned; NULL on borrow rows.
• SCHEDULED_PAYBACK_DATE – the contractual return date on the borrow record.
• AGING_DAYS – computed as TRUNC(SYSDATE - LOAN_DATE), but only when OUTSTANDING_QUANTITY is non-zero; otherwise NULL. This makes it a direct indicator of overdue open loans.

Common Use Cases and Queries

The most frequent use is identifying open inter-project obligations. Querying OUTSTANDING_QUANTITY greater than zero yields all loans not yet fully repaid, with AGING_DAYS quantifying delinquency against the scheduled payback date.

SELECT borrow_project_id, lending_project_id, inventory_item_id, loan_quantity, outstanding_quantity, scheduled_payback_date, aging_days FROM apps.pjm_borrow_payback_detail_v WHERE outstanding_quantity > 0 AND organization_id = :org_id ORDER BY aging_days DESC;

A second scenario reconciles movement by project pair, summing loan and payback quantities separately.

SELECT borrow_project_id, lending_project_id, SUM(loan_quantity) loan_qty, SUM(payback_quantity) payback_qty FROM apps.pjm_borrow_payback_detail_v GROUP BY borrow_project_id, lending_project_id;

Third, because TRANSACTION_TYPE_NAME is available for both directions, the view supports audit trails that trace an individual borrow transaction through its paybacks using BORROW_TRANSACTION_ID. Finally, the view is suited to aging buckets and exception reporting where open balances are stratified by day ranges derived from AGING_DAYS.