Results for “msc_po_requisitions_v”

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

Overview

MSC_PO_REQUISITIONS_V is an APPS-owned, VALID database view within the MSC – Advanced Supply Chain Planning product. It is designed to present all information necessary to create approved or unapproved requisitions in Oracle Purchasing. The view is primarily consumed by the XML-outbound integration path, where planned supply is converted into requisition documents that are pushed to a Purchasing instance. Because it consolidates item, quantity, need-by date, deliver-to, source, project, and vendor attributes into a single row per source line, it acts as a reporting and integration surface rather than a transactional entity.

For consumers searching on deliver_to_requestor_id, the column is exposed directly by this view and is additionally joined to planner data through MSC_PLANNERS so that a REQUESTOR_CODE is returned (defaulting to '-1' via NVL when no planner code exists). This makes the view the natural access point for resolving both the requestor identifier and its corresponding descriptive code.

Underlying Base Objects

The view is defined over several MSC base objects and one package:

• MSC_PO_REQUISITIONS_INTERFACE — the primary driving table (aliased MPR), supplying the requisition line attributes.
• MSC_APPS_INSTANCES (MAI) — provides INSTANCE_CODE for the source instance (SR_INSTANCE_ID).
• MSC_TRADING_PARTNERS — joined twice: MTP_DEST for the destination organization and MTP_DELIVER for the deliver-to partner.
• MSC_TRADING_PARTNER_SITES (MTP_DELIVER_SITES) — supplies the deliver-to location code.
• MSC_SYSTEM_ITEMS (MSI) — provides ITEM_NAME.
• MSC_PLANNERS (PLN) — resolves the planner code used as REQUESTOR_CODE.
• MSC_ST_UTIL — a package whose functions (RET_ORG_CODE, RET_PARTNER_NAME, RET_PROJECT_NUMBER, RET_TASK_NUMBER) derive descriptive values that are not stored denormalized in the interface.

All base objects are referenced through APPS synonyms, and joins are keyed on SR_INSTANCE_ID to preserve multi-instance integrity.

Key Columns

• SOURCE_LINE_ID, ITEM_ID, ITEM_NAME — line identity and item.
• QUANTITY, UOM_CODE, NEED_BY_DATE — requisition demand details.
• DELIVER_TO_REQUESTOR_ID — the requestor identifier; paired with REQUESTOR_CODE derived from MSC_PLANNERS.PLANNER_CODE via NVL(...,'-1').
• DELIVER_TO_LOCATION_ID / LOCATION_CODE and DELIVER_TO_PARTNER_NAME / DEST_PARTNER_NAME — deliver-to site and partner.
• AUTHORIZATION_STATUS / AUTHORIZATION_STATUS_CODE — DECODE maps 'APPROVED' to 1, else 0.
• CHARGE_ACCOUNT_ID, ITEM_REVISION — accounting and revision context.
• DESTINATION_ORGANIZATION_ID / DEST_ORG_CODE — destination org, with the code parsed after the ':' delimiter.
• SOURCE_ORGANIZATION_ID / SOURCE_ORG_CODE — source org via MSC_ST_UTIL.RET_ORG_CODE.
• SUGGESTED_VENDOR_ID / PARTNER_NAME, SUGGESTED_VENDOR_SITE, VENDOR_SITE_ADDRESS — sourcing information (VENDOR_SITE_ADDRESS is returned NULL).
• PROJECT_ID / PROJECT_NUMBER, TASK_ID / TASK_NUMBER — project and task context.
• SR_INSTANCE_ID, INSTANCE_CODE — instance identification for multi-source deployments.

Common Use Cases and Queries

Typical uses include XML-outbound requisition generation, review of planned requisitions by requestor, and reconciliation of planned supply against source instances.

• Requisition lines for a specific requestor:

  SELECT source_line_id, item_name, quantity, need_by_date,
         deliver_to_requestor_id, requestor_code, authorization_status
  FROM   apps.msc_po_requisitions_v
  WHERE  deliver_to_requestor_id = :requestor_id;

• Approved requisitions only:

  SELECT source_line_id, item_name, quantity
  FROM   apps.msc_po_requisitions_v
  WHERE  authorization_status_code = 1;

• Grouping by requestor and deliver-to location:

  SELECT deliver_to_requestor_id, requestor_code,
         location_code, SUM(quantity)
  FROM   apps.msc_po_requisitions_v
  GROUP  BY deliver_to_requestor_id, requestor_code, location_code;