Search Results chv_planning_sch

Overview

ECE_SPSO_ITEMS_V is a private APPS-schema view in Oracle E-Business Suite 12.1.1 and 12.2.2, owned by the e-Commerce Gateway (EC) module and categorized under the Planning Schedule business entity (CHV_PLANNING_SCH). Its documented display name is "Planning Schedule Item View," and its lifecycle status is active. The view extracts item-level information for the outbound Planning Schedule transaction set (ANSI X12 830 / EDIFACT DELFOR), which communicates supplier scheduling, release authorizations, cumulative quantities, and supporting item detail to trading partners.

Unlike transactional tables that store data, ECE_SPSO_ITEMS_V is a denormalized read-only projection. It joins scheduling data held in the CHV schemas with item master, purchasing, human resources, and hazard-material tables, producing a flattened row per schedule item suitable for the e-Commerce Gateway's outbound extraction process. The view exposes both transactional scheduling columns (authorized and cumulative quantities) and descriptive columns (item, buyer, ship-to, and hazard classification), allowing the gateway to populate the outbound DELFOR/830 message without additional application logic.

Underlying Base Objects

The documented dependencies of ECE_SPSO_ITEMS_V span several product schemas. The scheduling core is drawn from CHV_SCHEDULE_ITEMS, CHV_SCHEDULE_HEADERS, CHV_CUM_PERIODS, CHV_ITEM_ORDERS, and CHV_ORG_OPTIONS, which supply schedule and item identifiers, cumulative period dates, and the cumulative enablement flag. Item descriptive data comes from MTL_ITEM_FLEXFIELDS, with unit-of-measure codes retrieved from MTL_UNITS_OF_MEASURE and organization context from MTL_PARAMETERS.

Supplier agreement information (supply agreement number, type, and line number) is derived from PO_HEADERS and PO_LINES. Hazard and transportation classification is sourced from PO_HAZARD_CLASSES_B and PO_HAZARD_CLASSES_TL, with UN numbers from PO_UN_NUMBERS_B and PO_UN_NUMBERS_TL. Buyer and contact detail comes from PER_ALL_PEOPLE_F and PER_PHONES. Ship-to address and location data is resolved through HR_LOCATIONS, HR_ALL_ORGANIZATION_UNITS and its TL table, and the HR_GENERAL and HR_SECURITY packages with their security profiles. These dependencies confirm that the view applies organizational and location security filtering via HR_SECURITY, so results are constrained to the operating units and locations the querying user is authorized to access.

Key Columns

The view's key columns fall into functional groups. Scheduling identifiers include SCHEDULE_ID, SCHEDULE_ITEM_ID, and ITEM_ID, which serve as the primary join keys back to the CHV scheduling tables. Item identification columns are ITEM_NUMBER and DESCRIPTION from MTL_ITEM_FLEXFIELDS, with PO_UOM providing the unit of measure code.

Supply agreement columns include SUPPLY_AGREEMENT_NUMBER, SUPPLY_AGREEMENT_TYPE, and SUPPLY_AGREEMENT_LINE_NUMBER, derived from PO_HEADERS and PO_LINES. Cumulative scheduling columns include ENABLE_CUM_FLAG, CUM_PERIOD_START_DATE, STARTING_AUTH_QUANTITY, STARTING_CUM_QUANTITY, STARTING_AUTH_QTY_PRIMARY, STARTING_CUM_QTY_PRIMARY, and LAST_RECEIPT_TRANSACTION_ID, which together enable calculation and reporting of authorized versus received cumulative balances.

Ship-to detail is exposed through ST_NAME, ST_ORG_CODE, ST_LOCATION_CODE, ST_ADDRESS_LINE1 through ST_ADDRESS_LINE3, ST_CITY, ST_COUNTY, ST_STATE, ST_REGION3, ST_COUNTRY, ST_POSTAL_CODE, and ST_TELEPHONE. Buyer information appears as BUYER_LAST_NAME, BUYER_FIRST_NAME, and BUYER_TELEPHONE.

Most directly relevant to the searched term, HAZARD_CLASS_DESCRIPTION is sourced from the PO_HAZARD_CLASSES_TL table (the translation table for hazard classes), while HAZARD_CLASS holds the hazard class code from PO_HAZARD_CLASSES_B and UN_NUMBER holds the associated UN number from PO_UN_NUMBERS_B. These columns allow the outbound Planning Schedule message to carry regulated-material classification data such as hazardous class description and UN number alongside the item. Finally, the SCH_ITEM_ATTRIBUTE1 through SCH_ITEM_ATTRIBUTE8 columns and SCH_ITEM_ATTRIBUTE_CATEGORY expose flexible descriptive flexfield values defined on the scheduling item record.

Common Use Cases and Queries

Typical scenarios include verifying which hazard classes and UN numbers are transmitted on a supplier schedule, troubleshooting an 830/DELFOR outbound interface for missing item or ship-to detail, and reconciling cumulative quantities between the EBS scheduling tables and the trading partner's acknowledgment. Because the view resolves item descriptions through MTL_ITEM_FLEXFIELDS and applies HR security, it is the appropriate source for building ad-hoc extracts and gateway debugging queries rather than for direct high-volume transaction processing.

• Retrieving item detail for a schedule, filtered by hazardous material, using HAZARD_CLASS_DESCRIPTION: SELECT schedule_id, item_number, hazard_class, hazard_class_description, un_number FROM apps.ece_spso_items_v WHERE hazard_class_description IS NOT NULL;
• Extracting ship-to and buyer contact data for a specific schedule: SELECT schedule_id, item_number, st_name, st_city, buyer_last_name, buyer_telephone FROM apps.ece_spso_items_v WHERE schedule_id = :p_schedule_id;
• Comparing starting authorized and cumulative quantities for cumulative-enabled items: SELECT item_number, enable_cum_flag, starting_auth_quantity, starting_cum_quantity, starting_auth_qty_primary FROM apps.ece_spso_items_v WHERE enable_cum_flag = 'Y' ORDER BY item_number;
• Reporting supply agreement context alongside scheduled items: SELECT supply_agreement_number, supply_agreement_type, supply_agreement_line_number, item_number, po_uom FROM apps.ece_spso_items_v ORDER BY supply_agreement_number, supply_agreement_line_number;