Search Results billing_method




Overview
Explore in AI Chat

Overview

The APPS.PA_PROJ_RETN_BILL_VALUES_V view is a Projects (PA) module database object that surfaces billing retention rule information for presentation on the Billing Retentions Summary page. Its documented description states that PA_PROJ_RETN_BILL_VALUES is used "for displaying Billing Retentions in Summary page," which identifies it as a reporting rather than transactional object. Because it is a view and not a table, it stores no data of its own; it derives its result set at runtime from the retention billing rule table and several supporting lookup and reference objects.

The view's relevance to the search term "retn_billing_amount" is direct: RETN_BILLING_AMOUNT is one of the eleven columns the view exposes. Consultants, developers and report authors who need to retrieve the retention billing amount configured against a project, customer, or task therefore query this view rather than resolving the underlying PA_PROJ_RETN_BILL_RULES table themselves. It also supplies the retention billing percentage and the retention billing method, giving a complete, presentation-ready picture of retention billing setup in a single query.

Underlying Base Objects

The documented view text defines PA_PROJ_RETN_BILL_VALUES_V over four base objects, each referenced in the view definition:

  • PA_PROJ_RETN_BILL_RULES (SYNONYM) — the primary source of retention billing rule records, supplying retention billing rule ID, project, customer, task, billing method code, cycle, percentage and amount.
  • PA_LOOKUPS (VIEW) — joined on LOOKUP_TYPE = 'RETN_BILLING_METHOD' and LOOKUP_CODE = BILLING_METHOD_CODE to translate the method code into a user-facing meaning.
  • PA_BILLING_CYCLES (SYNONYM) — outer-joined on BILLING_CYCLE_ID to provide the billing cycle name where a cycle is associated with the rule.
  • PA_TASKS (SYNONYM) — outer-joined on TASK_ID to supply task number and task name where the retention rule is task-specific.

Notably, the view is defined as the UNION of two branches. The first branch selects rules where RETN_BILLING_CYCLE_ID IS NULL and derives METHOD_VALUE from a DECODE over the billing method code, returning either the client extension flag, the completed percentage, or the total retention amount. The second branch selects rules where RETN_BILLING_CYCLE_ID IS NOT NULL and uses the billing cycle name as METHOD_VALUE. This UNION structure means a single row in the result set represents one retention billing rule, with cycle-dependent and cycle-independent rules normalised into a common shape.

Key Columns

  • RETN_BILLING_RULE_ID — primary identifier of the retention billing rule.
  • PROJECT_ID / CUSTOMER_ID / TASK_ID — the project, customer and (optional) task context for the rule.
  • TASK_NUMBER / TASK_NAME — descriptive task attributes from PA_TASKS.
  • BILLING_METHOD_CODE — the code value (for example CLIENT_EXTENSION, PERCENT_COMPLETE, TOTAL_RETENTION_AMOUNT).
  • BILLING_METHOD — the lookup meaning corresponding to the code, from PA_LOOKUPS.
  • METHOD_VALUE — a UNION-normalised value: the client extension flag, completed percentage, total retention amount, or billing cycle name depending on the branch and method.
  • RETN_BILLING_PERCENTAGE — the retention percentage applied to the billing.
  • RETN_BILLING_AMOUNT — the retention billing amount, the column most frequently searched for in this context.

Common Use Cases and Queries

Typical uses include retention summary reporting, validation of retention setup during implementation, and integration extracts that feed downstream billing reconciliation. Because the view already resolves lookup meanings and joins task and cycle descriptions, it reduces custom SQL complexity.

SELECT project_id,
       task_number,
       billing_method,
       retn_billing_percentage,
       retn_billing_amount
FROM   apps.pa_proj_retn_bill_values_v
WHERE  project_id = :p_project_id
ORDER  BY task_number;

A further example retrieves total retention for a project:

SELECT SUM(retn_billing_amount) retention_total
FROM   apps.pa_proj_retn_bill_values_v
WHERE  project_id = :p_project_id;

Because the underlying objects are APPS synonyms and views, responsibility-based security is inherited from the base objects; access to this view should be granted only to roles requiring retention billing visibility.