Using PeopleSoft Tables to Store Delivery and Freight Information

This section discusses the table structure and fields used to create and store deliveries and freight data. This section applies to delivery management, the internal PeopleSoft freight calculator, the external third-party freight integration, and the manual entry of freight costs.

Delivery information can be stored at three levels:

Delivery level
Container level
Package level

Delivery and freight information is contained in the following PeopleSoft tables:

Field or Control	Description
IN_DELIVERY	This is the parent level for delivery information. All information is stored at the delivery level, that is, by delivery ID. Freight status, freight amounts, and a pro number can be defined at the delivery level.
IN_DELIVERY_FRT	This is a child table to the IN_DELIVERY table and stores the freight amount applied at order entry time in PeopleSoft Order Management and the freight amount applied during demand fulfillment in PeopleSoft Inventory. The Freight Bill Type field determines which of these freight amounts is used for billing or costing.
IN_DELIVERY_PKG, IN_DEL_PKG_FRT, IN_DEL_PKG_RPT	This is a child level to the IN_DELIVERY table and stores package level weight and volume information. Multiple packages per delivery can exist. Although freight amounts and tracking numbers can be defined and stored at the package level, the freight calculation and manifesting can be done only for an entire delivery. When the EIP sends and receives transactional data with a third-party freight provider, all packages, shipping containers, and demand lines within one delivery are freighted and manifested in the same transaction; you cannot freight and manifest just one part of a delivery.
IN_DELIVERY_ORD	This is a child table to the IN_DELIVERY table and stores the freight amounts to be sent to PeopleSoft Billing for the customer's invoice. These amounts are prorated by sales order demand line based on the delivery freight charge method. Any additional freight charges or miscellaneous charges entered in shipping issues are also stored in this table. The Populate Billing process (OMBILL) updates IN_DELIVERY_ORD.BILLING_STATUS to note that the freight amount has been processed. The freight amount in this table is also passed to PeopleSoft Cost Management.
CM_CST_NSSHIP	Rows are inserted into this table when the Commit Freight for Billing field is set to yes. The costing process picks up all rows with a post status of Not Posted. After the charges are costed, the post status is set to Posted. On a delivery, if the Commit Freight for Billing field is set to yes prior to the delivery being depleted, then the posted status is set to Don't Post. In this case, the Deplete On Hand Qtys process sets the post status to Not Posted when the delivery is depleted.

Note: When applying manual freight to an order that is not assigned to a delivery, a bill only delivery is automatically created by the system. This delivery is not exposed to the user and is used internally by the system to maintain consistency in how freight is passed to the billing and costing processes.

The Delivery_ID field is included in IN_DEMAND (the PeopleSoft Inventory demand fulfillment table) and additional freight fields are in SHIP_CNTR_HDR (the PeopleSoft Inventory shipping container header table).

The IN_DELIVERY Table

The status of a delivery is stored in the IN_DELIVERY table. Statuses are:

Field or Control	Description
Demand Pending	This status is used by PeopleSoft Order Management. These deliveries are awaiting demand line assignment. This delivery status is not available in the Delivery Management Workbench or other delivery processing in PeopleSoft Inventory.
Pending for quote	The delivery was created for freight on a quote. This delivery type is available only for quotes in PeopleSoft Order Management. These deliveries are not available for sales order demand assignment or consolidation.
Open	The delivery is open and available for additions from the sales order, the Delivery Management workbench, shipping, or other fulfillment processing.
Closed for Shipment	The delivery is closed and ready for shipment. The system does not automatically add or remove lines from a delivery.
Complete Delivery	All fulfillment steps for the delivery are complete, and all demand lines in the delivery are ready to be depleted using the Deplete On Hand Qtys process.
Canceled	This status is automatically set if all lines are removed from the delivery.

The IN_DELIVERY table includes several fields related to the creation of a delivery and to freight processing, including:

Field or Control	Description
Error Status for Delivery	This Y/N (yes/no) field indicates whether the delivery has an error.
Error Status for Freight	This Y/N (yes/no) field indicates that an error occurred during the freight process.
Freight Status	Determines the current state of the freight process. The options are: Rate Requested: A freight rate request has been sent to the third-party freight provider system. Rate Received: The third-party freight provider has responded to the freight request and the delivery has been updated.
Manifest Status	Determines the current state of the manifest process. The options are: Manifest Requested: A shipment request for rates and manifesting has been sent to the third-party freight provider system. Manifest Received: The response to the manifest action was received from the third-party freight provider system.
Commit Freight for Billing	This Y/N (yes/no) field indicates whether the freight on this delivery is ready to be processed by PeopleSoft Billing and PeopleSoft Cost Management.

The IN_DELIVERY_FRT Table

The IN_DELIVERY_FRT table includes:

Field or Control	Description
Freight Amount	The delivery level freight amount (FREIGHT_CHARGE field) returned by the third-party provider, manually entered, or calculated by the PeopleSoft internal freight calculator. Depending on the delivery rollup indicator or the rollup indicator used when processing freight, this may also be the freight amount derived from the sum of the lower-level package and container freight amounts.
Freight Audit	The Freight Audit field (FRT_CHRG_AUDIT) within the IN_DELIVERY_FRT table stores the delivery-level freight amount received from the third-party system. The Freight Audit fields are not used for billing or costing. These values can be used for reporting or research.
Pro Number	A tracking number assigned to the delivery level. This value is returned by the third-party provider or can be manually assigned.
Communicated Arrival Date	The arrival date communicated by the third-party provider for the carrier. This is for information only.
Communicated Ship Date	The ship date communicated by the third-party provider for the carrier. This is for information only.
Freight Reference Number	This is a free-form memo field that can be used by your third-party freight provider.
Freight Service Definition	The code used by the third-party freight provider to define the specific carrier freight service. This service definition may be required depending on the third-party provider.
Freight Type Code	Used to identify the generally accepted types of freight services used with a third-party freight system (parcel, LTL, and so on).
Freight Roll-up Indicator	The roll-up indicator can be manually assigned at delivery to define how the incoming third-party delivery level freight amount should be assigned (delivery freight amount, or the sum of the package and container freight amounts).

The Package Tables

The IN_DELIVERY_PKG is a child table of IN_DELIVERY and contains the individual package information for each package assigned to the delivery. Packages can be created manually in the Delivery Management Workbench or can be returned by the third-party freight system. Related to IN_DELIVERY_PKG is the IN_DEL_PKG_FRT table, which contains package-level freight fields that include:

Field or Control	Description
Freight Amount	The package-level freight amount can be returned by the third-party freight provider or manually entered in the Delivery Management Workbench in PeopleSoft Inventory.
Freight Audit	The Freight Audit field (FRT_CHRG_AUDIT) within the IN_DELIVERY_PKG table stores the package-level freight amount received from the third-party system. The Freight Audit fields are not used for billing or costing. These values can be used for reporting or research.
Tracking Number	The tracking number can be returned by the third-party freight provider or entered manually in the Delivery Management Workbench in PeopleSoft Inventory.
Freight Reference Number	This is a free-form memo field that can be used by your third-party freight provider.
IN_DEL_PKG_RPT.URL	For a package, this field contains a URL pointing to a label file when a report type of 1 (label) is used. This URL is updated from a manifest response.

The Shipping Container Tables

The SHIP_CNTR_HDR table is used by PeopleSoft Inventory for shipping container processing. In PeopleSoft Inventory, you can assign individual demand lines to a shipping container using the Packing Session component. When you are integrating with a third-party freight provider, the SHIP_CNTR_HDR table is used to store shipping container level freight amount and tracking numbers.

Field or Control	Description
Freight Amount	Shipping container level freight amount can be returned by the third-party freight provider or entered manually in the Ship Container Detail page.
Freight Audit	The Freight Audit field (FRT_CHRG_AUDIT) within the SHIP_CNTR_HDR table stores the container-level freight amount received from the third-party system. The Freight Audit fields are not used for billing or costing. These values can be used for reporting or research.
Tracking Number	The tracking number can be returned by the third-party freight provider or entered manually in the Ship Container Detail page.
Freight Reference Number	This is a free-form memo field that can be used by your third-party freight provider.
SHIP_CNTR_RPT.URL	For a shipping container, this field contains a URL pointing to a label file when a report type of 1 (label) is used. This URL is updated from a manifest response.