Loading Marketplace Catalog Items into PeopleSoft eProcurement

This section provides an overview of loading Marketplace items into PeopleSoft eProcurement and discusses how to download supplier flat files, extract data from a CUP file, export CUP data, load CUP files, define item import defaults, validate and process imported items, edit imported item categories, maintain imported item statuses, load Marketplace items, correct inventory item load errors, and download item attachments.

After you set up connections to a Marketplace, you can load items from Marketplace catalogs into PeopleSoft eProcurement. The items can be loaded into the item master tables or into an express catalog. This topic discusses loading items into the item master tables of PeopleSoft.

For information on the express catalog option, see Setting Up and Using the Express Catalog.

When Marketplace items are loaded into the PeopleSoft item master tables, they can be entered on eProcurement requisitions and purchase orders. You can also update prices and check available quantities for Marketplace items in PeopleSoft eProcurement. After you load the items, you should schedule regular updates to capture changes such as price changes, discontinuation of items, or the addition of new items.

To load a CUP (catalog update process) file into the system, complete these steps:

  1. Import the product and price files to PeopleSoft eProcurement using the Import CUP File process (PV_CUP_LOAD).

    This application engine process loads the product and price flat files into the PV_CP_LOAD and PV_CP_CAT_TMP staging tables. The attachment file is loaded into the PV_CP_ITM_XREF table.

  2. Use the Validate and Process Imported Items page to validate the CUP file data in the staging tables, adding or changing information as necessary.

  3. Use the Edit CUP Item Categories page to review any new item categories added for the items loaded from a Marketplace.

    Each new item category requires a default account (the top level of the PeopleSoft ChartFields). This account is used to record the accounting entries created by ordering items in this item category.

  4. Review the CUP status of the item on the CUP Items Status page.

  5. When loading non-inventory items, be sure to enter a cost element in the Item Default Values page for the applicable SetID. This cost element is used as the default cost element for the new items created by the Load Marketplace Items process.

  6. Run the Load Marketplace Items process (PV_CP_ITM_LOAD).

    This process loads items with a status of Ready to Build Items (new items) or Items to be Updated from the staging tables into the PeopleSoft Item Master tables.

  7. Use the Data Definition Maintenance page to review any errors for items that were not loaded properly.

    Select the ITEM transaction type and enter the SetID for the items. Select the stage table option with an Error status and then click the Query button. Any errors appear at the bottom of the page.

This diagram illustrates the process of loading Marketplace items into PeopleSoft eProcurement by creating a CUP file and then loading the data into the PeopleSoft production tables:

 Load Marketplace items into PeopleSoft eProcurement process flow

To set up the system to use the CUP Load process, you need to complete these tasks:

  1. Provide locations for the product, price, and attachment files from the supplier.

  2. Load the CUP file to extract the data from the input files and place them in the Inventory tables.

  3. Provide item defaults, such as account number and currency information.

  4. Validate items being imported into the PeopleSoft system.

  5. Correct errors that may occur in the extraction process.

Page Name

Definition Name

Usage

Procurement Catalog Load Page

PV_CP_IMPORT_RUN

Import CUP files by loading data from the flat files created by the Export CUP File process into the PeopleSoft eProcurement tables.

Item Defaults Page

PV_CP_RUN_DEFAULTS

Define item import defaults.

Validate and Process Imported Items Page

PV_CP_PROCESS

Validate and process imported items loaded by the Import CUP File process page into the staging tables PV_CP_LOAD and PV_CP_CAT_TMP.

Edit Imported Item Categories Page

PV_CP_CATEGORIES

Edit imported item categories for the items loaded from a Marketplace.

Imported Item Status Page

PV_CP_STATUS

Maintain CUP item statuses in the PV_CP_LOAD and PV_CP_CAT_TMP staging tables, correct errors, add more details, and change the item status.

Item Defaults Page

INV_ITEM_DEFAULTS

For non-inventory items, enter a cost element in the Item Default Values page for the applicable SetID. This cost element is used as the default cost element for the new items created by the Load Marketplace Items process.

Load Item Catalogs Page

PV_CP_ITM_LOAD

Load Marketplace items into PeopleSoft item tables. The data is loaded from the PV_CP_LOAD and PV_CP_CAT_TMP staging tables to the production tables for PeopleSoft items.

Data Definition Maintenance Page

EO_EIP_CTL_MAINT

Correct inventory item load errors.

The PeopleSoft system will search for three files needed to successfully move the supplier data into the PeopleSoft Inventory tables. These three files are:

  • A product file containing item information.

  • A price file containing pricing information.

  • An attachment file containing information about the attachments for each item.

Not all data in the CUP file is retrieved; only the information needed by PeopleSoft is retrieved. This Visual Basic (VB) program is delivered with PeopleSoft eProcurement.

PeopleSoft retrieves this information from a CUP file:

Item Information (Product File)

Table

Field

Data Retrieved

Cup_CatUpdate

CreateDate

The item's creation date.

Cup_SupplierPartnerID

The supplier's identification code.

EffectiveDate

The item's effective date.

Cup_Partner

TPName

The buyer's trading partner name.

Cup_Partner

TPID

The buyer’s trading partner identification.

Cup_PartUpdate

Cup_PartUpdateID

The part update identification code.

ActionCode

The code identifying the action to be performed on this item. The options are: A (add a new item); D (delete an item); or M(modify an existing item).

PartNum

The item's part number.

PartNumExt

The part number extension.

UOM

The unit of measure in which the item is purchased.

Cup_PartUpdateCommodity

CatLevel1, CatLevel2, CatLevel3, and CatLevel4.

The description of the UN/SPSC codes assigned to the item in the AcctLevel1, AcctLevel2, AcctLevel3, and AcctLevel4 fields. The first 30 characters of this field are used for the item category description in the PeopleSoft table ITM_CAT_TBL.

AcctLevel1, AcctLevel2, AcctLevel3, and AcctLevel4.

The four levels of UN/SPSC codes for the item. These codes are used to categorize the items within a catalog (PeopleSoft tree). All four levels are mandatory; if they are not already in the CUP file, enter them manually on the CUP Item Categories page.

Cup_PartUpdateDesc

ShortDesc

The short description for the item. The value in this field is used to populate several item description fields in PeopleSoft's item tables, including DESCRSHORT (first 10 characters), DESCR (first 30 characters), DESCR60 (first 60 characters), and DESCR_254MIXED (254 characters).

LongDesc

A longer description for the item that is loaded into the PeopleSoft eProcurement cross-reference table, PV_CP_ITM_XREF. This description appears on the Search Catalog - Item Description page and on the Requisition Summary - Item Description page.

Cup_PartUpdateInfo

MfrPartNum

The manufacturer's part number for the item.

MfrName

The item manufacturer's name.

LeadTime

The lead time required to deliver the item.

UnitPrice

The item's price per unit.

Cup_Price

CurrencyCode

The currency in which the item's base price is expressed.

QtyMin

The minimum quantity of the item that can be purchased.

This is an example of the product file generated:

^1^,^08/13/2012^,^A^,^8^,^h8aabb80-abcd-1234-12ed-as1230123012^,^SupplierABC^,^0121-0002^,^^,^EA ^,^9.828^,^0121-0002^,^XYZ Mfg. Co., Inc.^,^Structures, Building and Construction Components and Supplies^,^Electrical components and supplies^,^Switches, controls, and relays^,^Other^,^30^,^21^,^19^,^00^,^^,^USD^,^^,^^,^Battery Handle Switch, Dpdt15a^,^Battery Handle Switch, Dpdt15a^

^2^,^08/13/2012^,^A^,^8^,^ h8aabb80-abcd-1234-12ed-as1230123012^,^ SupplierABC ^,^0121-0006^,^^,^EA ^,^20.605^,^0121-0006^,^XYZ Mfg. Co., Inc.^,^Structures, Building and Construction Components and Supplies^,^Electrical components and supplies^,^Switches, controls, and relays^,^Other^,^30^,^21^,^19^,^00^,^^,^USD^,^^,^^,^Battery Handle Switch, Dpdt15a^,^Battery Handle Switch, Dpdt15a^

^3^,^08/13/2012^,^A^,^8^,^ h8aabb80-abcd-1234-12ed-as1230123012^,^ SupplierABC ^,^0121-0009^,^^,^EA ^,^6.695^,^0121-0009^,^XYZ Mfg. Co., Inc.^,^Structures, Building and Construction Components and Supplies^,^Electrical components and supplies^,^Switches, controls, and relays^,^Other^,^30^,^21^,^19^,^00^,^^,^USD^,^^,^^,^Battery Handle Switch, Dpst15a^,^Battery Handle Switch, Dpst15a^

The data retrieved above will be mapped into PeopleSoft fields as follows:

Field Name

PV_CP_SEQ_ID

PV_CP_CREATE_DATE

PV_CP_ACTION

PV_CP_SUPPLIER_ID

PV_MS_SUP_MPID

PV_CP_TP_NAME

PV_CP_SPN

PV_CP_PART_EXT

PV_CP_UOM

PV_CP_LIST_PRICE

PV_CP_MPN

PV_CP_MFR_NAME

PV_CP_CAT_LEVEL1

PV_CP_CAT_LEVEL2

PV_CP_CAT_LEVEL3

PV_CP_CAT_LEVEL4

PV_CP_ACCT_LEVEL1

PV_CP_ACCT_LEVEL2

PV_CP_ACCT_LEVEL3

PV_CP_ACCT_LEVEL4

PV_CP_LEAD_TIME

PV_CP_CURRENCY_CD

PV_CP_EFFDT

PV_CP_QTY_MIN

PV_CP_SHORT_DESCR

PV_CP_DESC1

SETID

INV_ITEM_ID

PV_CP_ITM_KEY

CATEGORY_CD

MFG_ID

MFG_ITM_ID

PV_CP_PROCESS_DT

ECQUEUEINSTANCE

EIP_CTL_ID

VENDOR_SETID

VENDOR_ID

ACCOUNT

PV_CP_APPROVE_FLG

PV_CP_ITM_STATUS

Pricing Information (Price File)

Table

Field

Data Retrieved

Cup_CatUpdate

Cup_SupplierPartnerID

The supplier's identification code.

Cup_Partner

TPName

The buyer's trading partner identification.

Cup_PartPrice

ActionCode

The action to be performed on this item. The values are: A(add a new item); D(delete an item); or M (modify an existing item).

PartNum

The item's part number.

PartNumExt

The part number's extension.

UOM

The unit of measure in which the item is purchased.

Cup_Price

UnitPrice

The item's price per unit.

CurrencyCode

The currency in which the item's base price is expressed.

EffectiveDate

The item price's effective date.

ExpireDate

The item price's expiration date.

QtyMin

The minimum quantity of the item that can be purchased.

Note: The item's long description is loaded from the CUP file into a cross-reference table called PV_CP_ITM_XREF, and the item's image is loaded into a web server directory. The description and image both appear when you view the item in PeopleSoft eProcurement.

Use the Procurement Catalog Load page (PV_CP_IMPORT_RUN) to import CUP files by loading data from the flat files created by the Export CUP File process into the PeopleSoft eProcurement tables.

Navigation:

eProcurement > Administer Procurement > Maintain Supplier Integration > Import Item Catalog File

This example illustrates the fields and controls on the Procurement Catalog Load page. You can find definitions for the fields and controls later on this page.

 Procurement Catalog Load page

Note: The product and price files are loaded into the staging tables PV_CP_LOAD and PV_CP_CAT_TMP. This process adds certain defaults that were not in Marketplace catalog tables to required fields in PeopleSoft's Item tables. The attachment file is loaded into the table PV_CP_ITM_XREF.

Import Catalog Items

Field or Control

Description

Source

Enter the source that provides the data.

Item SetID

Enter the SetID that the system uses in the item table.

Supplier SetID

Enter the SetID that the system uses in the supplier table.

Product File

Enter the location and name of the flat file containing the item details. The product file must be accessible from the PeopleSoft Process Scheduler server.

Attachment File

Enter the location and name of the flat file containing cross-references to images and attachments for the items. PeopleSoft enables you to store and retrieve attachments to an ftp server. The flat file cross-references the names and extensions of each attachment to the corresponding item ID. The attachment can be a Microsoft Word file or Excel spreadsheet for example. Requesters and buyers can view these attachments. The attachment is automatically added to the PO that is created from the requisition and can be sent to the supplier.

Price File

Enter the file location and name of the flat file containing the pricing details for the items. The price file must be accessible from the process scheduler server.

Tree Name

Enter the PeopleSoft tree (item catalog) to be used when this process loads the new categories from the CUP file. If you leave this field blank, the value appears by default from the eProcurement Installation Options page. To ensure that this item catalog is accessible to the user creating requisitions, either add the catalog to the Requester Setup page or clear any catalog restrictions for the requestor.

Note: Before running this process, verify the settings for requiring item approval. If you select the Item Approval Required check box on the Installation Options - Overall/GL page, all items that you load require approval. If you are loading thousands of items, you might not want to approve each item individually.

Options

Field or Control

Description

Run Item Load

Select to indicate that you want the system to load catalog items automatically as soon as it runs the CUP load.

Tree Name

Select the tree name that is used to store the category hierarchy.

Language

Select the language in which the items are to be stored.

Inactive Level

Select the level at which you want to inactivate to make the item not active. Values include the Item Supplier or at the Item Supplier Price level.

Process Imported Items

Click to access the Validate and Process Imported Items page where you can work with imported items before loading them.

The Run Load process loads the data from the PV_CP_LOAD and PV_CP_CAT_TMP staging tables to the production tables for PeopleSoft items.

The process of loading data includes four steps:

  1. The Build PeopleSoft Items process (PV_CP_ITM_LOAD) adds new item categories and then loads Marketplace CUP items into the PO item staging tables (ITM_LOAD_MST_EC, ITM_LOAD_INV_EC, ITM_LOAD_PUR_EC, and ITM_LOAD_VND_EC).

  2. The Load Item Stage process (PO_ITMLOAD) transfers the data to the item loader staging tables.

  3. The Item Loader process (IN_ITMLOAD) transfers the data into the PeopleSoft production tables.

  4. The item status is updated on the CUP Item Status page.

The Load Marketplace Items process also adds certain defaults that were not in Marketplace catalog tables to the required fields of the PeopleSoft Item Master tables. This information is hard coded into the Item Catalog PS_ITM_CAT_TBL table:

Field

Value Inserted

CATEGORY_TYPE

PSF

DESCRSHORT

MKTPLACE

INSPECT_CD

N

INSPECT_UOM_TYPE

S

RECV_REQ

Y

RJCT_OVER_TOL_FLAG

N

REVC_PARTIAL_FLG

1

SRC_METHOD

B

This information is hard coded into the Item PS_ITM_LOAD_PUR_EC table:

Field

Value Inserted

TAXABLE_CD

Y

INSPECT_CD

N

INSPECT_UOM_TYPE

V

RECV_REQ

Y

RJCT_OVER_TOL_FLAG

N

ACCEPT_ALL_VENDOR

N

ACCEPT_ALL_SHIPTO

Y

CONTRACT_REQ

N

RECV_PARTIAL_FLG

1

DESCRSHORT

MKTPLACE

SRC_METHOD

B

ACCEPT_ALL_UOM

Y

PRICE_DT_TYPE

L

PRICE_CAN_CHANGE

P

VNDR_CATALOG_ID

Y

Use the Item Defaults page (PV_CP_RUN_DEFAULTS) to define item import defaults.

Navigation:

eProcurement > Administer Procurement > Maintain Supplier Integration > Import Item Catalog File

Select the Item Defaults tab.

This example illustrates the fields and controls on the Item Defaults page. You can find definitions for the fields and controls later on this page.

 Item Defaults page

Field or Control

Description

Image Folder

Enter the file location that contains images for use with the catalog items that you import.

Inventory Item

Select to indicate this item is also a PeopleSoft Inventory item. This means you can source the item from Inventory.

Account

Select an account to which you want the categories to belong.

Currency

Select the currency to use with this category.

Use the Validate and Process Imported Items page (PV_CP_PROCESS) to validate and process imported items loaded by the Import CUP File process page into the staging tables PV_CP_LOAD and PV_CP_CAT_TMP.

Navigation:

eProcurement > Administer Procurement > Maintain Supplier Integration > Validate and Process Imported Items

Also use this page to review categories and items, to determine the state the items are in, and to load the staged items into the Inventory tables.

This example illustrates the fields and controls on the Validate and Process Imported Items page. You can find definitions for the fields and controls later on this page.

 Validate and Process Imported Items page

Field or Control

Description

Categories

This link takes you to the Edit Imported Items Categories page.

View Items

This link takes you to the Imported Item Status page.

Load Staged Items

When you have determined that the results are satisfactory, you can load the staged items from the eProcurement staging tables into the Inventory tables.

Delete All

Deletes all pending data from the eProcurement staging table.

Use the Edit Imported Item Categories page (PV_CP_CATEGORIES) to edit imported item categories for the items loaded from a Marketplace.

Navigation:

eProcurement > Administer Procurement > Maintain Supplier Integration > Validate and Process Imported Items

Click the Categories link.

This example illustrates the fields and controls on the Edit Imported Item Categories page. You can find definitions for the fields and controls later on this page.

 Edit Imported Item Categories page

Note: These categories are created from the UN/SPSC codes downloaded from a Marketplace. Add an account to each new category. The account is the top level of the ChartFields (chart of accounts) used to create accounting entries in PeopleSoft. If the CUP file does not create any new item categories, this page does not appear.

Field or Control

Description

Account

Enter the default account for this category. The account is the top level of the PeopleSoft ChartField used to create accounting entries in PeopleSoft. This field is required.

Copy

Click to copy the value in the Account field to every row on this page.

Use this page to update information for items that are in staging tables. After making the updates, you can load them into PeopleSoft production tables. You can load an item again if its status is Processing Complete.

Use the Imported Item Status page (PV_CP_STATUS) to maintain CUP item statuses in the PV_CP_LOAD and PV_CP_CAT_TMP staging tables, correct errors, add more details, and change the item status.

Navigation:

eProcurement > Administer Procurement > Maintain Supplier Integration > Validate and Process Imported Items

Click the View Items link.

This example illustrates the fields and controls on the Import Item Status page. You can find definitions for the fields and controls later on this page.

 Import Item Status page

Field or Control

Description

Item Status

The current load status of the items in the staging tables. The drop-down list only displays a status if items currently exist at that status. Values include:

Invalid Unit of Measure: Select to display all items with units of measure that are invalid in PeopleSoft eProcurement.

Invalid or Missing Category: Select to display all items that do not have a valid item category.

Invalid or Missing Supplier: Select to display all items that do not include a valid supplier and supplier.

Item to be Updated: Select to display all items that have been previously loaded into PeopleSoft eProcurement and currently need to be updated with new item information.

Price to be Updated: Select to display all items that have been previously loaded into PeopleSoft eProcurement and currently need to be updated with new pricing information.

Ready to Build Items: Select to display all items that are ready to be loaded into the PeopleSoft Item Master tables.

Items to be Inactivated: Select to delete items from the catalog.

Processing Complete: Appears when the item has been entered successfully into the PeopleSoft production tables.

The actions on this page will display either A for add or D for delete.

Use the Load Item Catalogs page (PV_CP_ITM_LOAD) to load Marketplace items into PeopleSoft item tables.

The data is loaded from the PV_CP_LOAD and PV_CP_CAT_TMP staging tables to the production tables for PeopleSoft items.

Navigation:

eProcurement > Administer Procurement > Maintain Supplier Integration > Validate and Process Imported Items

Click the Load Staged Item button.

This example illustrates the fields and controls on the Load Item Catalogs page. You can find definitions for the fields and controls later on this page.

 Load Item Catalogs page

Field or Control

Description

Run Item Load Process

Select to run the Load Item Stage process (PO_ITMLOAD). If this option is not selected, only the first step in this process is completed. You can run this process separately using the Items Price List page. You can run this process separately to verify or change data in the PO Item staging tables.

Run Inbound Items Process

Select to run the Item Loader process (IN_ITMLOAD). This option is available only if you select the Run Item Load Process check box. You can also run this process separately using the Item Loader page. You can run this process separately to verify or change data in the Item Loader staging tables.

Inventory Item

Select to indicate this item is also a PeopleSoft Inventory item. This means that you can source the item from Inventory.

Update Business Unit Price

Select to update business unit standard prices when an Item and Supplier UOM price record is loaded. This field is available only if Item Pricelist is selected in the Item Load Type field. If this is selected, all business units that match the SetID and item combination and that have the Business Unit Update Price check box selected on the BU Specific Attributes page will have the new price applied. Future effective-dated prices do not go into effect until you run the Price Update process on or after the effective date.

Update Standard Price

Select to update standard prices when an Item and Supplier UOM price record is loaded. This field is available only if Item Pricelist is selected in the Item Load Type field. Future effective-dated prices won't go into effect until you run the Price Update process on or after the effective date.

Item Image Sub Folder

Enter a location where images for use with item catalogs should be stored.

Note: If errors occur during the Item Loader process, use the Data Definition Maintenance page to review them.

Use the Data Def Maint page (EO_EIP_CTL_MAINT) to correct inventory item load errors.

Navigation:

eProcurement > Administer Procurement > Maintain Supplier Integration > Correct Item Load Errors

This example illustrates the fields and controls on the Data Def Maint page.

 Data Def Maint page

Use this page to review and resolve problems with items.

Any item downloaded could have an item image stored as an attachment. You can download these attachments from a Marketplace. The image of the item appears on the Search Catalog - Item Description page and the Requisition Summary - Item Description page.

To download item attachments from a Marketplace into PeopleSoft eProcurement:

  1. Run the Export CUP Data process to create the attachment file from a CUP file.

    This file does not contain the actual attachments (item images), just the file names and extensions of each attachment. The actual attachments are downloaded in a separate step. This attachment file cross-references the names and extensions of each attachment to the corresponding item ID.

    Note: To create an attachment file from the Export CUP Data process, you must create a product file at the same time from the same CUP file.

  2. Run the Import CUP File process to move the data in the attachment file into the eProcurement cross-reference table, PV_CP_ITM_XREF.

    Note: Other attachments—other than images—can be downloaded from a Marketplace. Only attachments with a P (picture) type are loaded into the eProcurement cross-reference table, PV_CP_ITM_XREF, when the Import CUP File process runs. All other attachments are loaded into the PV_CP_ATTACH file for future use.

  3. Define the location where the item attachments should be stored using:

    1. The File Location page, which defines the first part of the directory and extension of the attachments.

    2. The Load Marketplace Items process, which defines the subdirectory.

      For example, if the item attachments that you are downloading are GIF images, the File Locations page defines the file extension (GIF) and the file directory F:\User\Images\. If you enter MktPlc items in the Item Image Sub Directory field of the Load Marketplace Items page, the attachments with the extension GIF are stored in this directory: F:\User\Imagesl\MktPlc items.

  4. Download the zipped file containing the item attachments from a Marketplace.

    Place these item attachments in the directory location defined in Step 3.