Results for “mtp_deliver”

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

Overview

APPS.MSC_PO_REQUISITIONS_V is a reporting and integration view within the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) schema. It flattens the requisition interface data held in MSC_PO_REQUISITIONS_INTERFACE into a human-readable form by resolving surrogate identifiers into meaningful codes and names. The view presents planned and interfaced purchase requisition lines with their item, quantity, need-by date, deliver-to location, requestor, authorization status, unit of measure, charge account, source and destination organizations, suggested vendor, and project/task information.

Its role is to serve as a denormalized staging layer between the MSC planning engine and downstream consumers. Because MSC schema columns store numeric surrogate keys (location IDs, partner IDs, inventory item IDs, project/task IDs), direct queries against the interface table are of limited value to report developers. MSC_PO_REQUISITIONS_V applies the decoding logic once, so BI Publisher reports, ASCP extracts, and custom concurrent programs can query semantic values such as LOCATION_CODE, DELIVER_TO_PARTNER_NAME, and PROJECT_NUMBER without repeating joins and function calls.

Underlying Base Objects

The view is defined over the following documented base objects:

• MSC_PO_REQUISITIONS_INTERFACE (SYNONYM) - the driving table, aliased mpr, supplying requisition line data such as source_line_id, item_id, quantity, need_by_date, authorization_status, and the various ID columns.
• MSC_APPS_INSTANCES (SYNONYM) - aliased mai; joined to return INSTANCE_CODE for the source instance.
• MSC_TRADING_PARTNERS (SYNONYM) - used twice: mtp_dest for the destination organization (partner_type = 3) and mtp_deliver for the deliver-to trading partner, supplying partner_name.
• MSC_TRADING_PARTNER_SITES (SYNONYM) - aliased mtp_deliver_sites; an outer join on deliver_to_location_id returns the LOCATION code, which is the column referenced by the search term "mtp_deliver_sites."
• MSC_SYSTEM_ITEMS (SYNONYM) - aliased msi; joined on sr_inventory_item_id = item_id with plan_id = -1 to supply ITEM_NAME.
• MSC_PLANNERS (SYNONYM) - aliased pln; provides the requestor's PLANNER_CODE, defaulted to '-1' via NVL.
• MSC_ST_UTIL (PACKAGE) - supplies functions ret_org_code, ret_partner_name, ret_project_number, and ret_task_number for late decoding of source organization, vendor, project, and task.

Key Columns

• SOURCE_LINE_ID, ITEM_ID, ITEM_NAME - line identifier and the resolved inventory item.
• QUANTITY, UOM_CODE, NEED_BY_DATE - requisition demand attributes.
• DELIVER_TO_LOCATION_ID and LOCATION_CODE - deliver-to site, where LOCATION_CODE originates from mtp_deliver_sites.LOCATION.
• DELIVER_TO_PARTNER_NAME - trading partner name for the delivery site.
• REQUESTOR_CODE - planner code of the requestor, defaulted to '-1' when null.
• AUTHORIZATION_STATUS / AUTHORIZATION_STATUS_CODE - status text plus a decode yielding 1 for APPROVED and 0 otherwise.
• DEST_ORG_CODE, DEST_PARTNER_NAME, SOURCE_ORG_CODE - destination organization parsed after the colon, destination partner, and the decoded source organization.
• PARTNER_NAME (suggested vendor), PARTNER_NAME (destination), VENDOR_SITE_ADDRESS, SUGGESTED_VENDOR_SITE - vendor and site details.
• PROJECT_NUMBER, TASK_NUMBER - decoded project and task identifiers.
• SR_INSTANCE_ID, INSTANCE_CODE - source instance identity linking the row to a specific EBS instance.

Common Use Cases and Queries

Typical uses include ASCP requisition extracts, procurement dashboards, and exception reporting on unapproved or misdirected requisitions. The mtp_deliver_sites join is frequently cited when validating whether a deliver-to location resolves to a known trading partner site.

Sample query returning requisitions and their deliver-to sites:

• SELECT source_line_id, item_name, quantity, uom_code, need_by_date FROM msc_po_requisitions_v;
• SELECT source_line_id, item_name, location_code, deliver_to_partner_name, requestor_code FROM msc_po_requisitions_v WHERE location_code IS NULL ORDER BY need_by_date;
• SELECT project_number, task_number, SUM(quantity) FROM msc_po_requisitions_v WHERE authorization_status = 'APPROVED' GROUP BY project_number, task_number;