Understanding Web Services and PL/SQL Services

This chapter covers the following topics:

• Web Services and PL/SQL Services Overview
• Web Services and PL/SQL Services Detail
• Web Service Exceptions
• Web Service E-Mail Notifications

Web Services and PL/SQL Services Overview

Oracle Pedigree and Serialization Manager (OPSM) can operate in an integrated mode with external transactional systems or it can operate as a standalone application. Using web services, PL/SQL services, and file transfer exchanges external third party systems can integrate with OPSM enabling them to streamline their business processes. OPSM hosts a set of inbound services.

For more information on web services and the Service Oriented Architecture (SOA), see

• Oracle SOA Home page that can be accessed from "http://www.oracle.com/us/technologies/soa/index.htm".

• Oracle SOA Suite User Guides that can be accessed from My Oracle Support > Oracle Fusion Middleware Documentation Library

These tables list the OPSM and Electronic Product Code Information Services (EPCIS) services provided by OPSM and provide a high-level overview of each service:

OPSM Services

Endpoint	Operation	Operation Type	Inbound/Outbound	WSDL
JobInstanceServiceAMService	createJobInstance createJobInstanceAsync	Web Service	Inbound	JobInstanceServiceAMService wsdl
LotsServiceAMService	mergeLot mergeLotAsync	Web Service	Inbound	LotsServiceAMService wsdl
PackagingServiceAMService	packPackage packPackageAsync	Web Service	Inbound	PackagingServiceAMService wsdl
PackagingServiceAMService	unpackPackage unpackPackageAsync	Web Service	Inbound	PackagingServiceAMService wsdl
PackagingServiceAMService	processPackageSQL processPackageSQLAsync processUnpackSQL processUnpackSQLAsync	PL/SQL Service	Inbound	N/A
ProductServiceAMService	mergeProducts mergeProductsAsync	Web Service	Inbound	ProductServiceAMService wsdl
SerialGenRequestServiceAMService	createSerialGenerationRequest createSerialGenerationRequestAsync	Web Service	Inbound	SerialGenRequestServiceAMService wsdl
SerialGenRequestServiceAMService	createProcessSerialGenerationRequest createProcessSerialGenerationRequestAsync	Web Service	Inbound	SerialGenRequestServiceAMService wsdl
SerialsServiceAMService	editSerialStatusAndUsagByLabel editSerialStatusAndUsagByLabelAsync	Web Service	Inbound	SerialsServiceAMService wsdl
SerialsServiceAMService	editSerialStatusAndUsagByList editSerialStatusAndUsagByListAsync	Web Service	Inbound	SerialsServiceAMService wsdl
SerialsServiceAMService	editSerialStatusAndUsagByListSQL editSerialStatusAndUsagByListSQLAsync	PL/SQL Service	Inbound	N/A
SerialsServiceAMService	editSerialStatusAndUsagByPrimaryDocument editSerialStatusAndUsagByPrimaryDocument	Web Service	Inbound	SerialsServiceAMService wsdl
TransactionsServiceAMService	createTransaction createTransactionAsync	Web Service	Inbound	TransactionsServiceAMService wsdl
TransactionsServiceAMService	processTransactionSQL processTransactionSQLAsync	PL/SQL Service	Inbound	N/A
TransactionsServiceAMService	processImportAndPackSQL processImportAndPackSQLAsync	PL/SQL Service	Inbound	N/A

EPCIS Services

Endpoint	Operation	Operation Type	Inbound/Outbound	WSDL
CaptureService	capture	Web Service	Inbound	CaptureService wsdl
QueryService	getQueryNames getVendorVersion getStandardVersion poll getSubscriptionIDs subscribe unsubscribe	Web Service	Inbound	EPCglobalEPCISService wsdl

Web Services and PL/SQL Services Detail

This section lists the OPSM and EPCIS web services and PL/SQL services provided by Oracle Pedigree and Serialization Manager (OPSM) and provides the details for each web service and PL/SQL service.

JobInstanceServiceAMService (Inbound)

The JobInstanceServiceAMService endpoint is an inbound asynchronous business service that is part of the interoperability solution of the OPSM system. This endpoint enables external systems to create job instances with job instance messages. The job instances and job instances messages are used for error notification when processing the createTransaction and mergeLot messages from the JMS Queue.

Operation Type	Operation	Description
Web Service	createJobInstance createJobInstanceAsync	The createJobInstance operations are used to create a new job instance and job instance messages within OPSM from an external system.

Web Services

createJobInstance and createJobInstanceAsync Operations

These operations are used to create error, warning, and informational messages about a specified process into the Job Instances and Job Instance Messages table. It is used primarily in the error handling during asynchronous processing of messages from the JMS Queue.

Key Logic

• Creates a job instance and one or more job instance messages.

• Error Substitution Value 1-5

  Up to five error substitution values can be entered on errors that allow for value substitution.

Required Input

• JobType

• JobStatus

Exceptions

• Required input is not received.

• Invalid error code.

• Invalid lookup value:

  • Job Type

  • Job Status

  • Web Services

jobInstance

Name	Type	Mandatory	Description
JobInstanceId	Long	No	Unique identifier for the job instance.
JobType	String	Yes	Identifies the job type. This must be a valid value in the PAS_JOB_TYPE lookup.
JobStatus	String	Yes	Indicates the status of the job. This must be a valid value in the PAS_JOB_STATUS lookup.
WebServiceName	String	No	Identifies the web service operation associated with the job. This must be a valid value in the PAS_WEB_SERVICES lookup.

jobInstanceMsgVO

Name	Type	Mandatory	Description
JobInstanceMsgId	Long	No	Unique identifier for the job instance message.
ErrorCode	String	No	Identifies the error code from the resource bundle.
ErrorSeverityLevel	String	No	Indicates the severity level of the message.
ErrorSubValue1	String	No	Error substitution value.
ErrorSubValue2	String	No	Error substitution value.
ErrorSubValue3	String	No	Error substitution value.
ErrorSubValue4	String	No	Error substitution value.
ErrorSubValue5	String	No	Error substitution value.

WSDL

oracle.apps.pas.transactions.jobInstanceService.applicationModule.common.serviceinterface.JobInstanceServiceAMService.wsdl

For detailed descriptions of all input and output parameters, please refer to the JobInstanceServiceAMService wsdl for this endpoint.

LotsServiceAMService (Inbound)

The LotsServiceAMService endpoint is an inbound service that provides the ability to add or update lots and lot attributes from an external system into the OPSM system.

Operation Type	Operation	Description
Web Service	mergeLot mergeLotAsync	The mergeLot operations merge lot information by adding a new lot or updating an existing lot in OPSM.

Web Services

If you are using location security, you are only able to:

• Add a lot through the mergeLot operation if the user has update access in the Location Security Setup page for the lot location and for the location or location group associated to the product detail or the product detail related to the item that is sent into the endpoint. Otherwise, the lot is not created and exception messages are issued.

• Update a lot through the mergeLot operation if the user has update access in the Location Security Setup page for the lot location. Otherwise, lot information is not updated and exception messages are issued.

For more information on location security, see Location Security Overview.

mergeLot and mergeLotAsync Operation

These operations are used to merge the lot information from an external system into the OPSM system.

Key Logic

The merge attempts to find the lot in OPSM using the following precedence:

• If LotId is passed in the system uses LotId.

• If LotId and Location are not passed in, then the system uses LotNumber, LotSource, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom.

• If LotId is not passed and Location is passed then the system uses LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom.

• If the product code information is not passed, ItemNumber, ItemSource, and ItemUom are used to identify the ProductCode, ProductCodeType, ProductCodeVariant and ProductUom using the Item Cross Reference definition in OPSM.

• If both product and item information are provided, the product information takes precedence in identifying the product.

• If an existing lot is found in OPSM, the lot is updated with the information that is passed in. If the lot is not found, a new lot is added.

Required Input

One of the following combinations must exist to identify the lot:

• LotId

• LotNumber, LotSource, ProductCode, ProductCodeType, ProductCodeVariant, ProductUom

• LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, ProductUom

• LotNumber, LotSource, ItemNumber, ItemSource, ItemUom

• LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom

Exceptions

If LotId was passed in, and the lot is not found in OPSM, an exception is issued and the lot is not added.

Location Security Exceptions

Adding Lots

• If LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, ProductUom are passed in and the user does not have update access in location security setup for the location, or the location or location group associated to the product detail, an exception is issued and the lot is not added.

• If LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, ProductUom are passed in and the user does have update access in location security setup for the location, but not the location or location group associated to the product detail, an exception is issued and the lot is not added.

• If LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, ProductUom are passed in and the user does not have update access in location security setup for the location, but does have update access for the location or location group associated to the product detail, an exception is issued and the lot is not added.

• If LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom are passed in and the user does not have update access in location security setup for the location, or the location or location group associated to the product detail related to the item, an exception is issued and the lot is not added.

• If LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom are passed in and the user does have update access in location security setup for the location, but not the location or location group associated to the product detail related to the item, an exception is issued and the lot is not added.

• If LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom are passed in and the user does not have update access in location security setup for the location, but does have update access for the location or location group associated to the product detail related to the item, an exception is issued and the lot is not added.

• If LotNumber, LotSource, ProductCode, ProductCodeType, ProductCodeVariant, ProductUom are passed in and the user does have update access in location security setup for the location or location group associated to the product detail, the lot will be added.

  In this case, the lot number is added without a location. The only user that can access this lot is a super user.

Editing Lots

• If Lot Number, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, ProductUom are passed in and the user does not have update access in location security setup for the location, an exception is issued and the lot is not updated. Update access on the product detail location or location group is not required when updating a lot.

• If LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom are passed in and the user does not have update access in location security setup for the location, an exception is issued and the lot is not updated. Update access on the product detail location or location group related to the item is not required when updating a lot.

lotRow

Name	Type	Mandatory	Description
LotId	Long	Conditional: One of the following combinations must be sent to identify the lot: LotId or LotNumber, LotSource, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom or LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom or LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom or LotNumber, LotSource, ItemNumber, ItemSource, ItemUom	Unique identifier for the lot in OPSM. If the lot identifier is provided, the lot identifier is used to locate the lot record to be updated.
LotNumber	String	Conditional: One of the following combinations must be sent to identify the lot: LotId or LotNumber, LotSource, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom or LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom or LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom or LotNumber, LotSource, ItemNumber, ItemSource, ItemUom	Lot number associated to the lot. The LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom are used to uniquely identify the lot number to be created or updated if the LotId is not provided.
LotSource	String	Conditional: One of the following combinations must be sent to identify the lot: LotId or LotNumber, LotSource, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom or LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom or LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom or LotNumber, LotSource, ItemNumber, ItemSource, ItemUom	The source system associated to the lot. The LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant and ProductUom are used to uniquely identify the lot number to be created or updated if the LotId is not provided. This must be a valid value in the PAS_SOURCE_SYSTEM lookup.
Location	String	Conditional: One of the following combinations must be sent to identify the lot: LotId or LotNumber, LotSource, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom or LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCode Variant, and ProductUom or LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom or LotNumber, LotSource, ItemNumber, ItemSource, ItemUom	The location associated to the lot. The location can be null if the source system does not associate a location reference to the lot information. The LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant and ProductUom are used to uniquely identify the lot number to be created or updated if the LotId is not provided. This must be a location defined in OPSM. If you are using location security, to add or edit a lot, the user must have update access in the Location Security Setup page for the lot location passed in the web service. If you create a lot without a location only a super user can edit that lot. For more information on location security, see Location Security Overview.
ParentLot	String	No	The parent lot number associated to the lot.
OriginationDate	Date	No	The date that the lot originated.
ExpirationDate	Date	No	The date that the lot expires.
BestByDate	Date	No	The best by date associated with the lot.
SellByDate	Date	No	The sell by date associated with the lot.
RetestDate	Date	No	The retest date associated with the lot.
ItemNumber	String	Conditional: One of the following combinations must be sent to identify the lot: LotId or LotNumber, LotSource, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom or LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCode Variant, and ProductUom or LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom or LotNumber, LotSource, ItemNumber, ItemSource, ItemUom	ItemNumber, ItemSource, and ItemUom can optionally be used to identify the product associated to the lot in OPSM using the item cross reference. If both product and item information are sent, the product information takes precedence in identifying the product. If you are using location security: To add a lot, the user must have update access in the Location Security Setup page for the location or location group associated to the product detail related to the item number that is passed in the web service. To edit a lot, update access to the location or location groups associated to the product detail related to the item number is not required. For more information on location security, see Location Security Overview.
ProductCode	String	Conditional: One of the following combinations must be sent to identify the lot: LotId or LotNumber, LotSource, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom or LotNumber, LotSource, Location, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom or LotNumber, LotSource, Location, ItemNumber, ItemSource, ItemUom or LotNumber, LotSource, ItemNumber, ItemSource, ItemUom	ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom are used to identify the product detail associated to the lot. If you are using location security: To add a lot, the user must have update access in the Location Security Setup page for the location or location group associated to the product detail that is passed in the web service. To edit a lot, update access to the location or location group associated to the product detail is not required. For more information on location security, see Location Security Overview.
UserField1	String	No	User defined character field.
UserField2	String	No	User defined character field.
UserField3	String	No	User defined character field.
UserField4	String	No	User defined character field.
UserField5	String	No	User defined character field.
UserField6	String	No	User defined character field.
UserField7	String	No	User defined character field.
UserField8	String	No	User defined character field.
UserField9	String	No	User defined character field.
UserField10	String	No	User defined character field.
UserField11	String	No	User defined character field.
UserField12	String	No	User defined character field.
UserField13	String	No	User defined character field.
UserField14	String	No	User defined character field.
UserField15	String	No	User defined character field.
UserNumber1	Decimal	No	User defined numeric field.
UserNumber2	Decimal	No	User defined numeric field.
UserNumber3	Decimal	No	User defined numeric field.
UserNumber4	Decimal	No	User defined numeric field.
UserNumber5	Decimal	No	User defined numeric field.
UserDate1	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
UserDate2	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
UserDate3	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
UserDate4	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
UserDate5	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.

LotProductVO

Name	Type	Mandatory	Description
ProductCode	String	Yes	Identifies the product in OPSM. The product is uniquely identified using the ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom. If you are using location security: To add a lot, the user must have update access in the Location Security Setup page for the location or location group associated to the product detail that is passed in the web service. To edit a lot, update access to the location or location group associated to the product detail is not required. For more information on location security, see Location Security Overview.
ProductCodeType	String	Yes	Identifies the type of product code used in identification of the product in OPSM. This must be a valid value in the PAS_PRODUCT_CODE_TYPE lookup.
ProductCodeVariant	String	No	Identifies the specific variation of the product code when a product code is reused for different products over time. Default: NOVARIANT.
ProductUom	String	Yes	Identifies the product packaging unit where a specified product code is used for multiple packaging levels.

LotItemVO

Name	Type	Mandatory	Description
ItemNumber	String	Yes	Identifies the item number in the transactional system that is cross referenced to a product within OPSM. If you are using location security: To add a lot, the user must have update access in the Location Security Setup page for the location or location group associated to the product detail related to the item number that is passed in the web service. To edit a lot, update access to the location or location group associated to the product detail related to the item number is not required. For more information on location security, see Location Security Overview.
ItemSource	String	Yes	Identifies the source system related to the item number. Item source is used to cross reference the item specific to a source system to identify the product in OPSM.
ItemUom	String	Yes	Identifies the item UOM in the transactional system that is cross referenced to the Product Packaging Unit in OPSM.

WSDL

oracle.apps.pas.serials.serialsService.applicationModule.common.serviceinterface.LotsServiceAMService.wsdl

For detailed descriptions of all input and output parameters, please refer to the LotsServiceAMService wsdl for this endpoint.

PackagingServiceAMService (Inbound)

The PackagingServiceAMService endpoint is an inbound service into the OPSM system, which records the packaging operations (pack and unpack) that are used to maintain the packaging hierarchy within OPSM.

If you are using location security, and you want to pack a package using this endpoint, you must have update access to the location for both the child and topmost parent in the Location Security Setup page. This endpoint reviews the location on the immediate child to be packed and the topmost parent being packed. If the child record has children (items packed within it) those locations are not reviewed for location security access. For example:

Pack Scenario

If a request is sent to pack Package Level 2 into Package Level 1, the system checks to make sure that you have update access to both Location A and Location B. Location C is not considered.

If you are using location security, and you want to unpack a package using this endpoint, you must have update access to the location for the parent level in the Location Security Setup page. This endpoint reviews the location on the parent that is being unpacked. For example:

Unpack Scenario

If a request is sent to unpack Package Level 2 from Package Level 1, the system checks to make sure that you have update access to Location A. Location B and Location C are not considered. The same is true if a request is sent to unpack Package Level 3 from Package Level 2, the system checks to make sure that you have update access to Location A. Location B and Location C are not considered.

Operation Type	Operation	Description
Web Service	packPackage packPackageAsync	The pack request packs or repacks one or multiple single level or a multiple level packaging structure as defined in the request. Resulting in a complete packaging hierarchy within OPSM.
Web Service	unpackPackage unpackPackageAsync	The unpack request unpacks one or more packages within a single unpack request.
PL/SQL Service	processPackageSQL, processPackageSQLAsync, processUnpackSQL, and processUnpackSQLAsync Operations	These PL/SQL services record the creation of a packaging hierarchy within OPSM. The processPackageSQL and processPackageSQLAsync operations pack or repack one or multiple single level or a multiple level packaging structure as defined in the request, resulting in a complete packaging hierarchy within OPSM. The processUnpackSQL and processUnpackSQLAsync operations unpack the child label from its existing parent, removing it from its current packaging hierarchy.

Web Services

packPackage and packPackageAsync Operations

These operations are used to pack one or multiple single level or multiple level packaging structure as defined in the request.

If you are using location security, and you want to pack a package, you must have update access to the location for both the child and topmost parent in the Location Security Setup page. These operations review the location on the immediate child to be packed and the topmost parent being packed. If the child record has children (items packed within it) those locations are not reviewed for location security access.

Key Logic

• The ChildLabel and ChildLabelType are used to identify the package that is packed.

• The ParentLabel and ParentLabelType are used to identify the package into which the child package is packed.

• If the ParentLabelType is not a serial type, the parent package is added if it does not already exist. However, the child package must already exist. The packaging hierarchy must be built from the top level parent to the bottom level package, not bottom to top.

Uniqueness Check

To validate uniqueness for seeded serial types (SGTIN-96, SGTIN-198, and USN-1), this endpoint uses serial type plus serial number.

To validate uniqueness for custom serial types, this endpoint uses product code plus serial number. The following combinations of product information can be used:

1. Product Code Only OR

2. Product Code, Product Code Type, Product Code Variant, and Product UOM OR

3. Product Detail Id

If Product Detail Id is entered the rest of the data is ignored. If the Product Detail Id is provided and it is invalid the processing fails.

Required Input

• ParentLabel

• ParentLabelType

• ChildLabel

• ChildLabelType

Exceptions

• Required parameters not passed.

• Invalid lookup value:

  • Label Type

  • Packaging Unit

  • Label Print Format

• Invalid location security access. To pack, you must have update access to the location for both the child and topmost parent in the Location Security Setup page.

Restrictions

An alternate Label and LabelType cannot be used to identify the package.

packPackage

Name	Type	Mandatory	Description
PackSequence	Long	No	Sequences the order in which the packPackage requests are processed. Default: 1.
ParentLabel	String	Yes	The primary label used to identify the package.
ParentLabelType	String	Yes	The primary label type used to identify the package. This must be a valid user defined value in the PAS_LABEL_TYPE lookup.
ParentProductCode	String	No	Identifies the product code associated with the package.
ParentProductCodeType	String	No	Identifies the product code type associated with the product code.
ParentProductCodeVariant	String	No	Identifies the product code variant associated with the product code.
ParentProductUom	String	No	Identifies the product code unit of measure associated with the product code.
ParentProductDetailID	String	No	Identifies the product detail Id associated with the package. If the product detail Id is entered, it takes precedence over the following entries: Product Code only Product Code, Product Code Type, Product Code Variant, and Product UOM
ParentLocation	String	No	Identifies the location associated with the parent package. You can enter either a parent location or a parent location group but not both at the same time. If you are using location security, you must have update access to the parent location in the Location Security Setup page. This location is only considered if the Parent Label is being created at the time of packaging.
ParentLocationGroup	String	No	Identifies the location group associated with the parent package. You can enter either a parent location or a parent location group but not both at the same time. If you are using location security, you must have update access to the parent location group in the Location Security Setup page. This location group is only considered if the Parent Label is being created at the time of packaging.
ReusableFlag	String	No	Identifies whether the container is reusable. This value is used when adding a new parent container. Valid values: 0,1. Default: 0.
PackagingUnit	String	No	Identifies the packaging unit. This value is used when adding a new parent container. This must be a valid value in the PAS_PACKAGE_UNIT lookup. Default: UNASSIGNED.
LabelPrintFormat	String	No	Identifies the label print format for the package. This value is used when adding a new parent container. This must be a valid value in the PAS_LABEL_PRINT_FORMAT lookup.
ChildLabel	String	Yes	The primary label used to identify the package that is being packed into the parent package. This package must exist in OPSM.
ChildLabelType	String	Yes	The primary label type used to identify the package that is being packed into the parent package. This must be a value in the PAS_LABEL_TYPE lookup.
ChildProductCode	String	No	Identifies the product code associated with the package.
ChildProductCodeType	String	No	Identifies the product code type associated with the product code.
ChildProductCodeVariant	String	No	Identifies the product code variant associated with the product code.
ChildProductUom	String	No	Identifies the product code unit of measure associated with the product code.
ChildProductDetailID	String	No	Identifies the product detail Id associated with the package. If the product detail Id is entered, it takes precedence over the following entries: Product Code only Product Code, Product Code Type, Product Code Variant, and Product UOM

unpackPackage and unpackPackageAsync Operations

These operations are used to unpack one or more packages from a higher level package.

If you are using location security, and you want to unpack a package, you must have update access to the location for the parent level in the Location Security Setup page. These operations review the location on the parent that is being unpacked.

Key Logic

The primary Label and LabelType are used to identify the package that is unpacked. For example if a bottle is packed within a box and the label information for the bottle is sent, the bottle is unpacked from the box.

Required Input

• Label

• LabelType

Exceptions

• Required parameters not passed.

• Invalid Lookup value:

  • Label Type

• Invalid location security access. To unpack, you must have update access to the location or location group for the parent level in the Location Security Setup page.

Restrictions

An alternate Label and LabelType cannot be used to identify the package.

unpackPackage

Name	Type	Mandatory	Description
Label	String	Yes	The primary label used to identify the package.
LabelType	String	Yes	The primary label type used to identify the package. This must be a valid user defined value in the PAS_LABEL_TYPE lookup.
ProductCode	String	No	Identifies the product code associated with the package.
ProductCodeType	String	No	Identifies the product code type associated with the product code.
ProductCodeVariant	String	No	Identifies the product code variant associated with the product code.
ProductUom	String	No	Identifies the product code unit of measure associated with the product code.
ProductDetailId	String	No	Identifies the product detail Id associated with the package. If the product detail Id is entered, it takes precedence over the following entries: Product Code only Product Code, Product Code Type, Product Code Variant, and Product UOM

WSDL

oracle.apps.pas.serials.serialsService.applicationModule.common.serviceinterface.PackagingServiceAMService.wsdl

For detailed descriptions of all input and output parameters, please refer to the PackagingServiceAMService wsdl for this endpoint.

PL/SQL Services

processPackageSQL, processPackageSQLAsync, processUnpackSQL, and processUnpackSQLAsync Operations

These PL/SQL services record the creation of a packaging hierarchy within OPSM. The processPackageSQL and processPackageSQLAsync operations pack one or multiple single level or a multiple level packaging structure as defined in the request, resulting in a complete packaging hierarchy within OPSM. The processUnpackSQL and processUnpackSQLAsync operations unpack the child label from its existing parent, removing it from its current packaging hierarchy.

Security

Since the data is being loaded directly into the PAS_S_PACK_INTERFACE table, OPSM's functional security is not applicable. It is the responsibility of the implementer to ensure that proper access to the database is validated.

Key Logic

• The ChildLabel and ChildLabelType are used to identify the packages that is packed or unpacked.

• The ParentLabel and ParentLabelType are used to identify the package into which the child package is packed if the procedure is creating or changing the packaging hierarchy.

• During a pack or repack, if the ParentLabelType is not a serial type, the parent package is added if it does not already exist. However, the child package must already exist. This implies that the packaging hierarchy must be sequenced bottom up to facilitate adding the next higher level parent before attempting to pack it into the next higher packaging level.

Uniqueness Check

To validate uniqueness for seeded serial types (SGTIN-96, SGTIN-198, and USN-1), this endpoint uses serial type plus serial number. To validate uniqueness for custom serial types, this endpoint uses product code plus serial number. The following combinations of product information can be used:

• Product Code Only OR

• Product Code, Product Code Type, Product Code Variant, and Product UOM OR

• Product Detail Id

Note: If Product Detail Id is entered the rest of the data is ignored. If the Product Detail Id is provided and it is invalid or it does not match the product detail associated to the serial number provided, the processing fails. If Product information is provided and it is invalid or it does not match the product detail associated to the serial number provided, the processing fails.

Required Input

• ParentLabel (for pack and repack only)

• ParentLabelType (for pack and repack only)

• ChildLabel

• ChildLabelType

• Created By

• Last Updated By

Exceptions

• Required parameters not passed: An error of this type is issued if one or more of the fields listed in the Required Input section (above) is not included.

• Invalid lookup value: An error of this type is issued if the value being sent in one of the following fields does not match an existing lookup code in the associated lookup type:

  • Label Type [PAS_LABEL_TYPE]

  • Packaging Unit [PAS_PACKAGE_UNIT]

  • Label Print Format [PAS_LABEL_PRINT_FORMAT]

Restrictions

An alternate Label and LabelType cannot be used to identify the package.

Procedure

To Use these PL/SQL Services

1. Load data into the PAS_S_PACK_INTERFACE table.

   At a minimum, the Package Interface ID, Package Interface Batch ID, Parent Label, Parent Label Type, Child Label, Child Label Type, Created By and Last Updated By fields must be provided.

   This table displays the column names and descriptions and the required fields for the PAS_S_PACK_INTERFACE table:

   PAS_S_PACK_INTERFACE Table

   Column Name	Column Description	Required?
   PACKAGE_INTERFACE_ID	Uniquely identifies each row in the PAS_S_PACK_INTERFACE table.	Y
   PACKAGE_INTERFACE_BATCH_ID	Associates a batch number to the records in the table.	Y
   PARENT_LABEL	The primary identifier of a parent package.	Y
   PARENT_LABEL_TYPE	The primary identifier type of a parent package.	Y
   PARENT_PRODUCT_CODE	The unformatted product class identifier for the pharmaceutical product (e.g., the NDC number) for the parent package.	N
   PARENT_PRODUCT_CODE_TYPE	The type of product code (e.g., NDC442, NDC532, NDC541, NDC542, GTIN) for the parent package.	N
   PARENT_PRODUCT_CODE_VARIANT	A unique key value used in case Product Code and Product Code Type do not uniquely identify the product for the parent package.	N
   PARENT_PRODUCT_UOM	The unit of measure of the product for the parent package.	N
   PARENT_PRODUCT_DETAIL_ID	Internal use only - unique ID for each Product Detail for the parent package.	N
   REUSABLE_FLAG	Flag to indicate if the package is a reusable package.	N
   PACKAGING_UNIT	The unit of packaging for this package (e.g. Box, Case, Pallet. etc.).	N
   LABEL_PRINT_FORMAT	Format the label will be printed in.	N
   CHILD_LABEL	The primary identifier of a child package.	Y
   CHILD_LABEL_TYPE	The primary identifier type of a child package.	Y
   CHILD_PRODUCT_CODE	The unformatted product class identifier for the pharmaceutical product (e.g., the NDC number) for the child package.	N
   CHILD_PRODUCT_CODE_TYPE	The type of product code (e.g., NDC442, NDC532, NDC541, NDC542, GTIN) for the child package.	N
   CHILD_PRODUCT_CODE_VARIANT	A unique key value used in case Product Code and Product Code Type do not uniquely identify the product for the child package.	N
   CHILD_PRODUCT_UOM	The unit of measure of the product for the child package.	N
   CHILD_PRODUCT_DETAIL_ID	Internal use only - unique ID for each Product Detail for the child package.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	N
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	N
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N

2. The Package Interface ID and Package Interface Batch ID must be generated by the user. The Package Interface Batch ID must associate all records for one packaging hierarchy within the table. The Package Interface ID must uniquely identify each row within the table.

3. When executing the PL/SQL service, only the Package Interface Batch ID is required as input into the service. OPSM searches for this Package Interface Batch ID within the PAS_S_PACK_INTERFACE table. If no corresponding Package Interface Batch ID is found, an INVALID BATCH ID ERROR is issued and no further processing occurs.

4. After the endpoint has been executed for a batch, if processing is successful, the records for the batch are removed from the PAS_S_PACK_INTERFACE table, and the packaging hierarchy is saved in OPSM for use in subsequent processing. However, if errors are found during processing, the records for the batch remain in the PAS_S_PACK_INTERFACE table, and the ERROR_STATUS_FLAG within that table is updated to a value of "1" for all record with an error. Additionally, error messages are written to the PAS_S_PACK_INTERFACE_ERR table. After the errors have been resolved, the ERROR_STATUS_FLAG must be updated to a value of "0" for all rows, or the processing will fail with an error stating that there is an ERROR STATUS FLAG error. No further processing is done until the error status flag has the correct value.

   This table displays the column names and descriptions for the PAS_S_PACK_INTERFACE_ERR_TABLE table:

   PAS_S_PACK_INTERFACE_ERR_TABLE Table

   Column Name	Column Description
   PACKAGE_INTERFACE_ID	Indicates the package interface ID from the PAS_S_PACK_INTERFACE table, for which an error occurred.
   PACKAGE_INTERFACE_BATCH_ID	Indicates the package interface batch ID from the PAS_S_PACK_INTERFACE table, for which an error occurred.
   ERROR_CODE	Is the error code of the error message that was issued.
   ERROR_MESSAGE	Is the complete error message that was issued.
   CREATED_BY	Who column: indicates the user who created the row.
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.

5. If processing is successful after correction of the errors, the batch records in the PAS_S_PACK_INTERFACE table as well as the error messages in the PAS_S_PACK_INTERFACE_ERR table are removed.

ProductServiceAMService (Inbound)

The ProductServiceAMService endpoint is an inbound asynchronous business service that is part of the interoperability solution of the OPSM system. This endpoint enables external systems to create or update product and related product detail, product item, and product item UOM records in OPSM. This endpoint service behaves similar to the Product Synchronization ODI process.

If you are using location security, you are only able to:

• Add product information through this endpoint if the user has update access in the Location Security Setup page for the location or location group being assigned to the product details for the product. Otherwise, the product is not created and exception messages are issued.

• Update product information through this endpoint if the user has update access in the Location Security Setup page for the location or location group associated to the product details for the product. Otherwise, the product information is not updated and exception messages are issued.

For more information on location security, see Location Security Overview.

Operation Type	Operation	Description
Web Services	mergeProducts mergeProductsAsync	The mergeProducts operations are used to create a new product and related product detail, product item, and product item UOMs records or update an existing product and related product detail, product item, and product item UOM in OPSM from an external system.

Web Services

mergeProducts and mergeProductsAsync Operations

These operations are used to create a new product or update an existing product in OPSM from an external system. Multiple records can be processed at one time.

Key Logic

ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom determine if the product detail being sent is an existing product detail or a new product detail. SourceSystem and ItemNumber determine if the item being sent is an existing item or a new item. SourceSystem, ItemNumber and ItemUom determine if the item UOM cross reference being sent is an existing item UOM or a new item UOM. Each entity in the input xml (product, product detail, item, and item uom cross reference) can be new or existing. If new, it is created. If it is existing, it is updated. If a field is sent in as blank, the existing data in that field is not updated.

Required Input

At a minimum, the required product information and one required product detail must be provided.

• ProductCode

• ProductCodeType

• ProductUom

• Source System

• Item Number

• Item Uom

Exceptions

• Required input is not received.

• Invalid lookup value:

  • Dosage Form

  • Container Type

  • Label Print Format

  • Packaging Unit

  • Product Category

  • Product Code Type

  • Serial Type

Location Security Exceptions

Attempting to add or update a product when the user does not have update access in the Location Security Setup page for the location or location group associated to the product details for the product.

Product

Name	Type	Mandatory	Description
AttributeChar1	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar2	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar3	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar4	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar5	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar6	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar7	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar8	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar9	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar10	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar11	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar12	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar13	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar14	String	No	Indicates a segment of the user descriptive flexfield.
AttributeChar15	String	No	Indicates a segment of the user descriptive flexfield.
AttributeNumber1	String	No	Indicates a segment of the user descriptive flexfield.
AttributeNumber2	String	No	Indicates a segment of the user descriptive flexfield.
AttributeNumber3	String	No	Indicates a segment of the user descriptive flexfield.
AttributeNumber4	String	No	Indicates a segment of the user descriptive flexfield.
AttributeNumber5	String	No	Indicates a segment of the user descriptive flexfield.
AttributeTimestamp1	DateTime	No	Indicates a segment of the user descriptive flexfield.
AttributeTimestamp2	DateTime	No	Indicates a segment of the user descriptive flexfield.
AttributeTimestamp3	DateTime	No	Indicates a segment of the user descriptive flexfield.
AttributeTimestamp4	DateTime	No	Indicates a segment of the user descriptive flexfield.
AttributeTimestamp5	DateTime	No	Indicates a segment of the user descriptive flexfield.
ContainerSize	Decimal	Conditionally required. Yes if PedigreeControlFlag = 1. Otherwise, No.	Indicates the number of units contained in a package of the product (For example, 60 or 100). This is also known as pack size.
ContainerType	String	No	Indicates the type of packaging container. This must be a valid value in the PAS_CONTAINER_TYPE lookup.
ControlledSubstanceFlag	String	No Default = 0	A flag used to indicate if this product is a controlled substance or not.
CreateSerialsForReceipts	String	No Default = 0	A flag used to indicate if the system will create serials in the Oracle Pedigree and Serialization Manager system at the time of receipt. Turn on when serials do not already exist and must be created. Turn off when serials exist for a receipt transaction.
CreateSerialsForShipFlag	String	No Default = 0	A flag used to indicate if the system will create serials in the Oracle Pedigree and Serialization Manager system at the time of shipment. Turn on when serials do not already exist and must be created. Turn off when serials exist for a shipment transaction.
DosageForm	String	Conditionally required. Yes if PedigreeControlFlag = 1. Otherwise, No.	Indicates the dosage form of the product (For example, TABLET or CAPSULE). This must be a valid value in the PAS_DOSAGE_FORM lookup.
Manufacturer	String	Conditionally required. Yes if PedigreeControlFlag = 1. Otherwise, No.	Identifies the manufacturer for the product.
PedigreeControlFlag	String	No Default = 0	A flag used to indicate if a pedigree must be created for this product when it ships.
ProductCategory	String	No	Identifies the category code for the product (For example, stimulants, depressants, anti-anxiety, and so on). This must be a valid value in the PAS_PRODUCT_CATEGORY lookup.
ProductCode	String	Yes	Identifies the product code.
ProductCodeType	String	Yes	Identifies the type of product code (For example, NDC442, NDC532, NDC541, NDC542, and GTIN). This must be a valid value in the PAS_PRODUCT_CODE_TYPE lookup.
ProductCodeVariant	String	Conditionally required: If ProductCodeVariant = NOVARIANT this field is not required. Default = NOVARIANT. If ProductCodeVariant not equal to NOVARIANT then the ProductCodeVariant is required.	Identifies the product code variant for the product. A unique key value used in case the Produce Code and Product Code Type do not uniquely identify the product.
ProductName	String	Conditionally required. Yes if PedigreeControlFlag = 1. Otherwise, No.	Identifies the name of the product.
ProductOwningSrcSys	String	For internal use only.	Identifies the owning source system for the existing product record.
RecordInternalReceipts	String	No Default = 0	Flag to activate the recording of receipts from internal customers for this product.
RecordInternalReturnFlag	String	No Default = 0	Flag to activate the recording of returns from internal customers for this product.
RecordInternalShipmentFlag	String	No Default = 0	Flag to activate the recording of shipments from internal customers for this product.
SerialControl	String	No Default = OPSM Serialized	Indicates if the serials for this product are generated by the OPSM system or if the serials are externally generated. Values are OPSM Serialized and Externally Generated.
Strength	String	Conditionally required. Yes if PedigreeControlFlag = 1. Otherwise, No.	Indicates the strength or potency for the product including the unit of measure (For example, 60 mg or 25 ml).
Product Details
EpcCompanyPrefix	String	No	Identifies the Electronic Product Code (EPC) company prefix assigned to the product.
EpcFilter	String	Conditionally required. Yes if Serial Type = SGTIN-96 or SGTIN-198	Indicates the default filter to be used when building the epcGlobal serial number.
EpcNumber	String	Conditionally required. Yes if Serial Type = SGTIN-96 or SGTIN-198	Indicates the Electronic Product Code (EPC) number assigned to the product. EPC Number is required for SGTIN serial types or serial types that are associated to a serial type group where the Product Unit Identifier Formula is set to EPC Number. For products associated with a GTIN product code type, the system populates the EPC Number with the value in the Product Code field and it cannot be changed.
LabelPrintFormat	String	No	Identifies the print label format for the product and packaging unit (For example, Barcode, RFID, and so on). This must be a valid value in the PAS_LABEL_PRINT_FORMAT lookup.
LowestSaleableUnitQuantity	Decimal	No	Identifies the total number of the lowest saleable units contained in this packaging unit.
NDCNumber	String	No	Indicates the National Drug Code (NDC) number assigned to the product.
ProdLevelRangeCtrlFlag	String	No Default is 0.	A flag used to indicate whether or not to use ranges when generating serials for product level serial types.
ProductDetailOwningSrcSys	String	For internal use only.	Identifies the owning source system for the existing product detail record.
ProductUom	String	Yes	Identifies the unit of measure for the product.
SerialIncrease	String	No Default is 0.	Indicates how many additional serials must be generated above the quantity/percent requested (For example, 5, 10, or 12).
SerialIncreaseType	String	No Default is Percent.	Indicates the overage type for the amount of serials to be over generated. Values are PERCENT and AMOUNT.
SerialType	String	No	Indicates the type of serial that is generated. This must be a valid value in the PAS_SERIAL_TYPE lookup.
ProductDetailLocation	String	No Product details can only be associated to a location or a location group but not both at the same time.	Identifies the location for the product detail line associated to the product. If you are using location security, you are only able to add or update product information using this web service if you have update access to this product detail location in the Location Security Setup page For more information on location security, see Location Security Overview.
ProductDetailLocationGroup	String	No Product details can only be associated to a location or a location group but not both at the same time.	Identifies the location group for the product detail line associated to the product. If you are using location security, you are only able to add or update product information using this web service if you have update access to this product detail location group in the Location Security Setup page. For more information on location security, see Location Security Overview.
Product Items
ItemDescription	String	No	Identifies the description of the item number from the transactional system.
ItemNumber	String	Yes	Identifies the item number from the transactional system
ItemUom	String	Yes	Identifies the unit of measure of the item number as it exists in the transactional system.
SourceSystem	String	Yes	Indicates the item source (transactional system) from which this item originates. This must be a valid value in the PAS_SOURCE_SYSTEM lookup.

WSDL

oracle.apps.pas.setup.setupService.applicationModule.common.serviceinterface.ProductServiceAMService.wsdl

For detailed descriptions of all input and output parameters, please refer to the ProductServiceAMService.wsdl for this endpoint.

SerialGenRequestServiceAMService (Inbound)

The SerialGenRequestServiceAMService endpoint is an inbound asynchronous business service that is part of the interoperability solution of the OPSM system. This endpoint enables external systems to make requests to OPSM for serial generation.

If you are using location security, you are only able to create and process serial generation requests using this endpoint if you have update access to the following in the Location Security Setup page:

• Serial location provided in the endpoint.

• Product detail location or location group if product information is provided in the endpoint.

• Lot location if lot information is provided in the endpoint.

• GTIN location if GTIN is provided in the endpoint (GTIN location is the same as product detail location).

For more information on location security, see Location Security Overview.

Operation Type	Operation	Description
Web Service	createSerialGenerationRequest createSerialGenerationRequestAsync	Used to create a new serial generation request within OPSM from an external system.
Web Service	createProcessSerialGenerationRequest createProcessSerialGenerationRequestAsync	Used to create and process a new serial generation request within OPSM from an external system.

Web Services

createSerialGenerationRequest and createSerialGenerationRequestAsync Operations

These operations are used to create a new serial generation request within OPSM from an external system. Requests can then be processed to generate serials for the product. Requests can be created for a quantity of serials as well as a starting and ending serial number. Do not send in both types, each request must be for either a quantity or a starting and ending range. An item or product may be passed in as well and up to two referencing documents.

If you are using location security, you must have update access in the Location Security Setup page for serial location, product detail location or location group, lot location, and GTIN location if some or all of this type of information is provided in the web service operation.

Key Logic

• If GenerationStatus is sent in as SUBMITTED, the serial generation request processes automatically. If GenerationStatus is REQUESTED or blank, the system captures the request for future processing. The user needs to submit the request manually using the Serial Generation Request page in OPSM.

• If the product and lot information is provided on the serial generation request, the serials are generated as COMMISSIONED. If either the product or lot information is not provided, serials are generated as RESERVED.

• The Item information can be used to identify the product if the product and item cross reference has been set up in OPSM.

• Optionally, the lot is created in OPSM using the information provided on the serial generation request. OPSM must be configured in the system parameters to allow the creation of the lot for serial generation and the lot cannot already exist in OPSM.

• A request must be made for either a quantity or a range of serials. Both cannot be entered.

• If a StartingSerialNumber is sent but no EndingSerialNumber, then only one serial number will be generated.

• If the location requires identification of the product or the serial type is product specific, the product detail record must be identified.

• The Lot Record can be identified by one of the following combinations listed in order of precedence:

  1. Identified by LotId

  2. Identified by LotSource, LotNumber, LotLocation, and ProductDetailId

  3. Identified by LotSource, LotNumber, and ProductDetailId (where LotLocation is null)

• If using location security, you must have update access in the Location Security Setup page for serial location, product detail location or location group, lot location, and GTIN location if some or all of this type of information is provided in the web service operation.

For information on manually submitting a serial generation request, see Submitting Serial Generation Requests.

Required Input

• SourceSystem

• SerialQuantity or StartingSerialNumber or StartingSerialNumber and EndingSerialNumber

• IgnoreOverageFlag

• LocationId or Location

  If you are using location security, you must have update access to the serial location in the Location Security Setup page.

• If the location requires identification of the product for serial generation or the serial type is product specific, then the serial must be identified through one of the following combinations:

  • ProductDetailId

  • ProductCode, ProductCodeType, ProductCodeVariant, ProductUOM

  • ItemNumber, ItemSource, SerializedUOM

  • Gtin

  If you are using location security, you must have update access to the product detail location or location group in the Location Security Setup page.

Exceptions

• Required parameters not passed.

• Invalid request, either a quantity or a range must be entered.

• Invalid lookup value:

  • Source System

  • Serial Usage

  • Generation Status

  • Product Code Type

• Invalid location security access. You must have update access in the Location Security Setup page for serial location, product detail location or location group, lot location, and GTIN location if some or all of this type of information is provided in the web service operation.

createSerialGenerationRequest

Name	Type	Mandatory	Description
GenerationStatus	String	No	The initial status of the serial generation request can be set to SUBMITTED or REQUESTED. When set to SUBMITTED, the serial generation request is automatically submitted for processing. When set to REQUESTED, processing of the serial generation request must be manually initiated. Valid values: REQUESTED, SUBMITTED. Default: REQUESTED. This must be a valid value in the PAS_GENERATION_STATUS lookup.
SourceSystem	String	Yes	Identifies the source system initiating the serial generation request. The source system must match the source system on the Location setup in OPSM. This must be a valid value in the PAS_SOURCE_SYSTEM lookup.
SerialQuantity	Decimal	Conditional: One of the following combinations must be passed: SerialQuantity StartingSerialNumber StartingSerialNumber and EndingSerialNumber	The quantity of serials requested.
StartingSerialNumber	Decimal	Conditional: One of the following combinations must be passed: SerialQuantity StartingSerialNumber StartingSerialNumber and EndingSerialNumber	The starting serial number used to request a range of serials instead of a quantity. If a starting serial number is passed without an ending serial number, it is assumed that the request is for one serial number.
EndingSerialNumber	Decimal	Conditional: One of the following combinations must be passed: SerialQuantity StartingSerialNumber StartingSerialNumber and EndingSerialNumber	The ending serial number used to request a range of serials instead of a quantity.
IgnoreOverageFlag	String	Yes	Indicates whether the overage amount defined in the product detail definition must or must not be applied in the calculation of the quantity of serials to be generated. Valid values: 1 = Do not apply overage amount in the calculation of the quantity of serials for generation 0 = Apply overage amount Default: 0. Note: When a serial generation request is created through the Work Order Serialization process, this flag is always set to "1" since the work order processing logic calculates the overage amount.
SaleableUnitFlag	String	No	Indicates that the generated serials must be flagged as saleable units. Default: 1.
LocationId	Long	Conditional: Either the LocationId or Location is required.	Identifies the location that initiated the serial generation request. If you are using location security, you must have update access to the serial location in the Location Security Setup page. For more information on location security, see Location Security Overview.
Location	String	Conditional: Either the LocationId or Location is required.	Identifies the location that initiated the serial generation request. If you are using location security, you must have update access to the serial location in the Location Security Setup page. For more information on location security, see Location Security Overview.
SerialUsage	String	No	Identifies the serial usage. This must be a valid user defined value in the PAS_SERIAL_USAGE lookup.
ProductDetailId	Long	Conditional: If the location requires identification of the product or the serial type is product specific, one of the following combinations must be sent to identify the product detail: ProductDetailId ItemNumber, ItemSource, SerializedUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Unique identifier for the Product Detail record. If the ProductDetailId is provided, the ProductDetailId is used to locate the product detail record. If you are using location security, you must have update access to the product detail location or location group in the Location Security Setup page. For more information on location security, see Location Security Overview.
ProductCode	String	Conditional: If the location requires identification of the product or the serial type is product specific, one of the following combinations must be sent to identify the product detail: ProductDetailId ItemNumber, ItemSource, SerializedUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the product in OPSM. The product is uniquely identified using the ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUOM are all provided, ProductDetailId takes precedence in identifying the product. If you are using location security, you must have update access to the product detail location or location group in the Location Security Setup page. For more information on location security, see Location Security Overview.
ProductCodeType	String	Conditional: If the location requires identification of the product or the serial type is product specific, one of the following combinations must be sent to identify the product detail: ProductDetailId ItemNumber, ItemSource, SerializedUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the type of product code used in identification of the product in OPSM. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUOM are all provided, ProductDetailId takes precedence in identifying the product. This must be a valid value in the PAS_PRODUCT_CODE_TYPE lookup.
ProductCodeVariant	String	Conditional: If the location requires identification of the product or the serial type is product specific, one of the following combinations must be sent to identify the product detail: ProductDetailId ItemNumber, ItemSource, SerializedUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the specific variation of the product code when a product code is reused for different products over time. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUOM are all provided, ProductDetailId takes precedence in identifying the product. Default: NOVARIANT.
ProductUom	String	Conditional: If the location requires identification of the product or the serial type is product specific, one of the following combinations must be sent to identify the product detail: ProductDetailId ItemNumber, ItemSource, SerializedUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the product packaging unit where a specified product code is used for multiple packaging levels. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUOM are all provided, ProductDetailId takes precedence in identifying the product.
ItemNumber	String	Conditional: If the location requires identification of the product or the serial type is product specific, one of the following combinations must be sent to identify the product detail: ProductDetailId ItemNumber, ItemSource, SerializedUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the item number in the transactional system that is cross referenced to a product within OPSM. If Item information and Product information are both provided, the Product information takes precedence in identifying the product.
ItemSource	String	Conditional: If the location requires identification of the product or the serial type is product specific, one of the following combinations must be sent to identify the product detail: ProductDetailId ItemNumber, ItemSource, SerializedUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the source system related to the item number. Item source is used to cross reference the item specific to a source system to identify the product in OPSM. If Item information and Product information are both provided, the Product information takes precedence in identifying the product.
SerializedUom	String	Conditional: If the location requires identification of the product or the serial type is product specific, one of the following combinations must be sent to identify the product detail: ProductDetailId ItemNumber, ItemSource, SerializedUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the item UOM in the transactional system that is cross referenced to the product packaging unit in OPSM.
LotId	Long	No	Unique identifier for the lot in OPSM.
LotLocation	String	No	The location associated with the lot. If you are using location security, you must have update access to the lot location in the Location Security Setup page. For more information on location security, see Location Security Overview.
LotSource	String	No	The Source System associated with the lot.
LotNumber	String	No	Lot number associated to the lot. The LotNumber, LotLocation, and ProductDetailId are used to uniquely identify the lot number to be created or updated if the LotId is not provided.
ParentLot	String	No	Identifies the parent lot number associated with the lot.
OriginationDate	Date	No	The origination date of the lot. Origination date is used to create the lot when OPSM is configured to allow creation of the lot when the transaction is processed and the lot does not already exist.
ExpirationDate	Date	No	The expiration date of the lot. Expiration date is used to create the lot when OPSM is configured to allow creation of the lot when the transaction is processed and the lot does not already exist.
BestByDate	Date	No	The lot best by date. Best by date is used to create the lot when OPSM is configured to allow creation of the lot when the transaction is processed and the lot does not already exist.
SellByDate	Date	No	The lot sell by date. Sell by date is used to create the lot when OPSM is configured to allow creation of the lot when the transaction is processed and the lot does not already exist.
RetestDate	Date	No	The lot retest date. Retest date is used to create the lot when OPSM is configured to allow creation of the lot when the transaction is processed and the lot does not already exist.
Segment1	String	No	Identifies the 1st segment of the document (Document Organization) used as reference when commissioning the serials.
Segment2	String	No	Identifies the 2nd segment of the document (Document Number) used as reference when commissioning the serials.
Segment3	String	No	Identifies the 3rd segment of the document (Document Type) used as reference when commissioning the serials.
Segment4	String	No	Identifies the 4th segment of the document (Document Line Number) used as a reference when commissioning serials.
Segment5	String	No	Identifies the 5th segment of the document used as a reference when commissioning serials.
Segment6	String	No	Identifies the 6th segment of the document used as a reference when commissioning serials.
Segment7	String	No	Identifies the 7th segment of the document used as a reference when commissioning serials.
Segment8	String	No	Identifies the 8th segment of the document used as a reference when commissioning serials.
Segment9	String	No	Identifies the 9th segment of the document used as a reference when commissioning serials.
Segment10	String	No	Identifies the 10th segment of the document used as a reference when commissioning serials.
AlternateSegment1	String	No	Identifies the 1st (Document Organization) segment of the alternate document used as a reference when commissioning the serials.
AlternateSegment2	String	No	Identifies the 2nd (Document Number) segment of the alternate document used as a reference when commissioning the serials.
AlternateSegment3	String	No	Identifies the 3rd (Document Type) segment of the alternate document used as a reference when commissioning the serials.
AlternateSegment4	String	No	Identifies the 4th (Document Line Number) segment of the alternate document used as a reference when commissioning the serials.
AlternateSegment5	String	No	Identifies the 5th segment of the alternate document used as a reference when commissioning the serials.
AlternateSegment6	String	No	Identifies the 6th segment of the alternate document used as a reference when commissioning the serials.
AlternateSegment7	String	No	Identifies the 7th segment of the alternate document used as a reference when commissioning the serials.
AlternateSegment8	String	No	Identifies the 8th segment of the alternate document used as a reference when commissioning the serials.
AlternateSegment9	String	No	Identifies the 9th segment of the alternate document used as a reference when commissioning the serials.
AlternateSegment10	String	No	Identifies the 10th segment of the alternate document used as a reference when commissioning the serials.
Gtin	String	Conditional: If the location requires identification of the product or the serial type is product specific, one of the following combinations must be sent to identify the product detail: ProductDetailId ItemNumber, ItemSource, SerializedUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM Gtin	Identifies the Global Trade Identification Number (GTIN) assigned to a product in OPSM. If the GTIN is entered it can be used to identify the product. If Product information, Item information, and GTIN are all provided, the Product information takes precedence in identifying the product. If you are using location security, you must have update access to the GTIN location in the Location Security Setup page. GTIN location is the same as product detail location For more information on location security, see Location Security Overview.

createProcessSerialGenerationRequest and createProcessSerialGenerationRequestAsync Operations

These operations are used to create and process a new serial generation request within OPSM from an external system. Though the asynchronous version, createProcessSerialGenerationRequestAsync, is available only the synchronous version, createProcessSerialGenerationRequest, is supported. These operations differ from the createSerialGenerationRequest and the createSerialGenerationRequestAsync operations in that a separate step for submitting the request for processing is not needed. The xml elements for both the createProcessSerialGenerationRequest and the createSerialGenerationRequest are identical other than the type of the request. Requests can be created for a quantity of serials as well as a starting and ending serial number. Do not send in both types, each request must be for either a quantity or a starting and ending range. An item or product may be passed in as well and up to two referencing documents.

If you are using location security, you must have update access in the Location Security Setup page for serial location, product detail location or location group, lot location, and GTIN location if some or all of this type of information is provided in the web service operation.

Key Logic

• Irrespective of the GenerationStatus in the request xml, it is overwritten with a new status SYNC_REQ_SUBMITTED and the request is processed immediately. The user need not submit the request manually using the Maintain Serial Generation Request page in OPSM.

• If the product and lot information is provided on the serial generation request, the serials are generated as COMMISSIONED. If either the product or lot information is not provided, serials are generated as RESERVED.

• The Item information can be used to identify the product if the product and item cross reference has been set up in OPSM.

• Optionally, the lot is created in OPSM using the information provided on the serial generation request. OPSM must be configured in the system parameters to allow the creation of the lot for serial generation and the lot cannot already exist in OPSM.

• A request must be made for either a quantity or a range of serials. Both cannot be entered.

• If a StartingSerialNumber is sent but no EndingSerialNumber, then only one serial number is generated.

• If the location requires identification of the product or the serial type is product specific, the product detail record must be identified.

• The Lot Record can be identified by one of the following combinations listed in order of precedence:

  1. Identified by LotId

  2. Identified by LotSource, LotNumber, LotLocation, and ProductDetailId

  3. Identified by LotSource, LotNumber, and ProductDetailId (where LotLocation is null)

• If using location security, you must have update access in the Location Security Setup page for serial location, product detail location or location group, lot location, and GTIN location if some or all of this type of information is provided in the web service operation.

Required Input

• SourceSystem

• SerialQuantity or StartingSerialNumber or StartingSerialNumber and EndingSerialNumber

• LocationId or Location

  If you are using location security, you must have update access to the serial location in the Location Security Setup page.

• If the location requires identification of the product for serial generation or the serial type is product specific, then the serial must be identified through one of the following combinations:

  • ProductDetailId

  • ProductCode, ProductCodeType, ProductCodeVariant, ProductUOM

  • ItemNumber, ItemSource, SerializedUOM

  If you are using location security, you must have update access to the product detail location or location group in the Location Security Setup page.

Exceptions

• The exceptions are same as that for the createSerialGenerationRequest. In addition, a status message is returned back when there are errors or warnings as described below.

createProcessSerialGenerationRequest

The request xml for createProcessSerialGenerationRequest is the same as that for createSerialGenerationRequest except for:

Name	Type	Mandatory	Description
GenerationStatus	String	No	Irrespective of the GenerationStatus in the request xml, it is overwritten with a new status SYNC_REQ_SUBMITTED and the request is processed immediately. Values are: Any lookup value in the PAS_GENERATION_STATUS lookup type. Default value is: SYNC_REQ_SUBMITTED.

The createProcessSerialGenerationRequest returns a response xml back which has most of the elements in the request xml. The values are queried from the serial generation record after the processing is done. SerialGenerationRequestResponse has the following additional elements:

Name	Type	Description
EpcNumber	String	The EpcNumber from the Product Detail record.
GlobalLocationNumber	String	The GlobalLocationNumber from the Location record.
StatusMessage	String	When there are errors, the following message is returned: No serials were generated. See the Maintain Serial Generation Request page in OPSM for details. When there are warnings, the following message is returned: Warning messages were generated during serial generation. See the Maintain Serial Generation Request page in OPSM for details. When there are no errors or warnings, no message is returned.

SerialGenerationRequestResponse has a child element SerialNumberList which is a list of serial numbers generated.

WSDL

oracle.apps.pas.transactions.serialGenRequestService.applicationModule.common.serviceinterface.SerialGenRequestServiceAMService.wsdl

For detailed descriptions of all input and output parameters, please refer to the SerialGenRequestServiceAMService wsdl for this endpoint.

SerialsServiceAMService (Inbound)

The SerialsServiceAMService endpoint is an inbound service into the OPSM system that is used to update the status and usage of one or more serials. Common uses for this endpoint are to commission or decommission serials, change the serial usage, and mark serials as counterfeit. The serials can be selected based on various attributes such as primary document number, label, and list. All serials contained within the selection are updated with the passed serial attributes. If an attribute is not passed, then its value is not changed.

For each serial status and usage update performed, the SerialsServiceAMService endpoint creates a transaction. The transactions that can be created by this endpoint are:

• Commission Transaction

  Serial status updated to Commission.

• Decommission Transaction

  Serial status updated to Decommission.

• Reserve Transaction

  Serial status updated to Reserve.

• Edit Serial Transaction

  If any other attributes are updated and do not cause the serial status to be updated, then the update is an Edit Serial transaction. For example, changing counterfeit status.

If you are using location security, depending on the serial status and usage update you are making to a serial you need the following:

• For commissioning, you need update access to the serial location, existing product detail location or location group, new product detail location or location group, and new lot location in the Location Security Setup page.

• For decommissioning, you need update access to the serial location in the Location Security Setup page.

• For updating the attributes of the serial (for example, serial status and usage), you need update access to the serial location in the Location Security Setup page.

• For reserving (removing the Lot ID), you need update access to the serial location and existing lot location in the Location Security Update page.

For more information on location security, see Location Security Overview.

All of the transactions that are created by the SerialsServiceAMService endpoint can be viewed and maintained using the Maintain Transactions page in OPSM.

For more information on managing transactions in OPSM, see Understanding Transactions.

Operation Type	Operation	Description
Web Service	editSerialStatusAndUsageByLabel editSerialStatusAndUsageByLabelAsync	These operations update serial status, serial usage, and counterfeit status information for a given Label and LabelType combination and utilize packaging hierarchy to update the top level serial for the given label and label type and any serial that is packed within it. A Decommission Serials or Edit Serials transaction is created depending on the type of edit performed.
Web Service	editSerialStatusAndUsageByPrimaryDocument editSerialStatusAndUsageByPrimaryDocumentAsync	These operations update serial status, serial usage, and counterfeit status information for a group of serial numbers identified by their primary document. These operations also support the commissioning of serials. A Commission Serials, Decommission Serials, Reserve Serials, or Edit Serials transaction is created depending on the type of edit performed.
Web Service	editSerialStatusAndUsageByList editSerialStatusAndUsageByListAsync	These operations update serial status, serial usage, and counterfeit status information for a given list of serial numbers. These operations also support the commissioning of serials. A Commission Serials, Decommission Serials, Reserve Serials, or Edit Serials transaction is created depending on the type of edit performed.
PL/SQL Service	editSerialStatusAndUsageByListSQL editSerialStatusAndUsageByListSQLAsync	These are PL/SQL services in OPSM that update serial number attributes within OPSM. The editSerialStatusAndUsageByListSQL and editSerialStatusAndUsageByListSQLAsync operations update serial numbers as defined in the request. Serials are updated depending on the data included in the request.

Web Services

editSerialStatusAndUsageByLabel and editSerialStatusAndUsageByLabelAsync Operations

These operations update serial status, serial usage, and counterfeit status information for a Label and LabelType combination and utilize the packaging hierarchy to update the top level serial for the given Label and LabelType and any serial that is packed within it. These operations are performed only against existing serial numbers. A Decommission Serials or Edit Serials transaction is created depending on the type of edit performed.

If you are using location security, you must have update access for the serial location in the Location Security Setup page before you can decommission the serial or edit the attributes on the serial using this web service operation.

Key Logic

• Updating Serials

  • Serials that are updated are identified by the primary Label and LabelType of the serial or a higher level packaging level. All serials within the packaging hierarchy are updated.

  • Serials are updated with the value of the attributes that are passed. If no value is passed for an attribute, that attribute is not updated.

  • Decommission Serials transactions are created when processing of the input data causes the serial status on a serial to be updated to Decommissioned.

  • Edit Serials transactions are created when processing of the input data causes other attributes on a serial to be updated but not the serial status.

• Decommissioning

  • ReasonCode is required if changing SerialStatus to DECOMMISSIONED.

• Updating Serial Usage, Counterfeit Status, and Counterfeit Date

  • SerialUsage, CounterfeitStatus, and CounterfeitDate can be updated at any time even after the serial has been shipped or decommissioned.

  • Specifying a CounterfeitDate is optional when flagging the serial as counterfeit.

• Updating Product Information

  • Product information is only available for update if the Serial Type is defined as non-product specific, and the Serial State is not SHIPPED or RETURNED.

Required Input

• Label and LabelType must be the primary identifier of the package.

  The package can be a higher level container, a higher level packaging unit, or a higher level assembly.

• TransactionSourceName is the source system for the transaction.

Exceptions

• Required input is not received.

• SerialStatus value is invalid:

  • COMMISSIONED

  • RESERVED

• ReasonCode is required if changing serial status to DECOMMISSIONED.

• Serials cannot be found for Label and LabelType.

• Counterfeit date cannot be in the future.

• Lookup value is invalid:

  • Label Type

  • Serial Status

  • Reason Code

  • Serial Usage

  • Counterfeit Status

  • Transaction Source Name

• Location security access is invalid. You must have update access for the serial location in the Location Security Setup page to decommission the serial or to edit the attributes on the serial.

Restrictions

This service does not support reserving or commissioning of serials (SerialStatus = RESERVED or SerialStatus = COMMISSIONED).

This service does not support updating serials based on the alternate packaging Label and LabelType.

editSerialStatusandUsageByLabel

Name	Type	Mandatory	Description
Label	String	Yes	Identifies the primary identifier used for selection of the serials that will be updated.
LabelType	String	Yes	Identifies the type of primary identifier used for selection of the serials that will be updated. For example, SGTIN-96. This must be a valid user defined value in the PAS_LABEL_TYPE lookup.
TransactionSourceName	String	Yes	Identifies the system source name for the transaction. This must be a valid user defined value in the PAS_SOURCE_SYSTEM lookup.
SerialStatus	String	No	Identifies the status that will be used for updating the selected serials. This must be a valid status in the PAS_SERIAL_STATUS lookup.
ReasonCode	String	Conditional: Required if the SerialStatus is DECOMMISSIONED.	Identifies the reason code on a decommissioned serial. This must be a valid user defined value in the PAS_REASON_CODE lookup.
SerialUsage	String	No	Identifies the serial usage. This must be a valid user defined value in the PAS_SERIAL_USAGE lookup.
CounterfeitStatus	String	No	Identifies the serial as COUNTERFEIT, NOTCOUNTERFEIT, or SUSPECT.
CounterfeitDate	Date	No	Notes the date of the counterfeit status.

editSerialStatusAndUsageByPrimaryDocument and editSerialStatusAndUsageByPrimaryDocumentAsync Operations

These operations update SerialStatus, SerialUsage, and CounterfeitStatus information for a group of serial numbers identified by their primary document. These operations also support the commissioning of serials. These operations are performed only against existing serial numbers.

These operations also create transactions in OPSM for every endpoint call. You can view the transactions and their status using the Maintain Transactions page in OPSM. If the endpoint ends in error then error messages are recorded in the database and they can be viewed on the Maintain Transactions page in OPSM.

These operations return a response XML back which has most of the elements in the request XML along with the generic error message if there are any errors that occur during processing. A Reserve Serials, Commission Serials, Decommission Serials, or Edit Serials transaction are created depending on the type of edit performed.

If you are using location security, depending on the serial status and usage update you are making to a serial using this web service operation you need the following:

• To commission a serial, you need update access to the serial location, existing product detail location or location group, new product detail location or location group, and new lot location in the Location Security Setup page.

• To decommission a serial , you need update access to the serial location in the Location Security Setup page.

• For updating the attributes of the serial, you need update access to the serial location in the Location Security Setup page.

• For reserving the serial, you need update access to the serial location and existing lot location in the Location Security Update page.

Key Logic

• Updating Serials

  • Serials that are updated are identified by the Segments of the primary document.

  • Serials are updated with the value of the attributes that are passed. If no value is passed for an attribute, that attribute is not updated.

• Updating Serial Usage, Counterfeit Status, and Counterfeit Date

  • SerialUsage, CounterfeitStatus, and CounterfeitDate can be updated at any time even after the serial has been shipped or decommissioned.

  • Specifying a CounterfeitDate is optional when flagging the serial as counterfeit.

• Updating Product Information

  • Product information is only available for update if the Serial Type is defined as non-product specific and the Serial Statue is not SHIPPED or RETURNED.

• Commissioning

  • Product and lot information is required.

  • If the product and lot are identified on the serial, the SerialStatus is updated to COMMISSIONED.

  • The identified lot must be for the given product. The lot must already be defined within OPSM.

  • The endpoint does not update serials with a state of SHIPPED or RETURNED or a status of DECOMMISSIONED.

  • If the Serial State is SHIPPED or RETURNED, the product or lot information cannot be updated.

  • The lot associated to the serial can be modified if the serial is not shipped or the serial has been returned.

• Decommissioning

  • ReasonCode is required if changing SerialStatus to DECOMMISSIONED.

Required Input

• Segment2 (Primary Document Number)

• Segment3 (Primary Document Type)

• TransactionSourceName (Source system for the transaction)

The primary document information on the serial is the document information recorded on the serial when the serial is generated. Identification of the primary document information at serial generation is optional. Therefore, serials may not contain a primary document identifier if it is not part of the standard business process.

Exceptions

• Required input is not received.

• Required input is received but the serials corresponding to the primary document cannot be found in OPSM.

• ProductCode only is provided and multiple product records are found in OPSM.

• ProductDetailID is provided but no corresponding record can be found in OPSM.

• ProductUom is provided and does not exist in lookup PAS_PRODUCT_UOM and ProductDetailID is not provided.

• Product information (ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom) is received but there are no matching records found in OPSM.

• Counterfeit date cannot be in the future.

• ReasonCode is required if changing SerialStatus to DECOMMISSIONED.

• Serials for Primary Document Number and Document Type cannot be found.

• Lot does not exist.

• Lookup value is invalid:

  • Serial Status

  • Reason Code

  • Serial Usage

  • Counterfeit Status

  • Product Code Type

  • Product Unit of Measure (UOM)

  • Transaction Source Name

• Location security access is invalid. You must have the following:

  • Update access for the serial location and lot location in the Location Security Setup page to reserve a serial.

  • Update access for the serial location, existing product detail location or location group, new product detail location or location group, and new lot location in the Location Security Setup page to commission a serial.

  • Update access for the serial location in the Location Security Setup page to decommission a serial.

  • Update access for the serial location in the Location Security Setup page to update the attributes of a serial.

Restrictions

• This service does not support updating serials based on the non-primary Document Number and Document Type.

editSerialStatusAndUsageByPrimaryDocument

Name	Type	Mandatory	Description
Segment1	String	No	Identifies the 1st segment (Document Organization) of the primary document for selection of serials.
Segment2	String	Yes	Identifies the 2nd segment (Document Number) of the primary document for selection of serials.
Segment3	String	Yes	Identifies the 3rd segment (Document Type) of the primary document for selection of serials. If Segment3 (Document Type) does not exist in the lookup PAS_DOCUMENT_TYPE then it is added to this lookup and a message is sent informing you about this.
Segment4	String	No	Identifies the 4th segment (Document Line Number) of the primary document for selection of serials.
Segment5	String	No	Identifies the 5th segment of the primary document for selection of serials.
Segment6	String	No	Identifies the 6th segment of the primary document for selection of serials.
Segment7	String	No	Identifies the 7th segment of the primary document for selection of serials.
Segment8	String	No	Identifies the 8th segment of the primary document for selection of serials.
Segment9	String	No	Identifies the 9th segment of the primary document for selection of serials.
Segment10	String	No	Identifies the 10th segment of the primary document for selection of serials.
TransactionSourceName	String	Yes	Identifies the system source name for the transaction. This must be a valid user defined value in the PAS_SOURCE_SYSTEM lookup.
OnlyReservedSerials	String	No	Indicates that only serials that are at a RESERVED status must be selected for update. Valid values: 1 = Reserve Serial Only, 0 = All Statuses. Default: 0.
SerialStatus	String	No	Identifies the status that is used for updating the selected serials. This must be a valid status in the PAS_SERIAL_STATUS lookup.
ReasonCode	String	Conditional: Required if the SerialStatus is DECOMMISSIONED.	Identifies the reason code on a decommissioned serial. This must be a valid user defined value in the PAS_REASON_CODE lookup.
SerialUsage	String	No	Identifies the serial usage. This must be a valid user defined value in the PAS_SERIAL_USAGE lookup.
CounterfeitStatus	String	No	Identifies the serial as COUNTERFEIT, NOTCOUNTERFEIT, or SUSPECT.
CounterfeitDate	Date	No	Notes the date of the counterfeit status.
ProductDetailId	Long	Conditional: One of the following combinations is required to identify the product when updating the SerialStatus to COMMISSIONED: ProductDetailId ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom	Unique identifier for the Product Detail record. If the ProductDetailId is provided, the ProductDetailId is used to locate the product detail record. If you are using location security, you must have update access for the product detail location or location group in the Location Security Setup page. For more information on location security, see Location Security Overview.
ProductCode	String	Conditional: One of the following combinations is required to identify the product when updating the SerialStatus to COMMISSIONED: ProductDetailId ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom	Identifies the product in OPSM. The product is uniquely identified using the ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUom are all provided, ProductDetailId takes precedence in identifying the product. If you are using location security, you must have update access for the product detail location or location group in the Location Security Setup page. For more information on location security, see Location Security Overview.
ProductCodeType	String	Conditional: One of the following combinations is required to identify the product when updating the SerialStatus to COMMISSIONED: ProductDetailId ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom	Identifies the type of product code used in identification of the product in OPSM. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUom are all provided, ProductDetailId takes precedence in identifying the product. This must be a valid value in the PAS_PRODUCT_CODE_TYPE lookup.
ProductCodeVariant	String	Conditional: One of the following combinations is required to identify the product when updating the SerialStatus to COMMISSIONED: ProductDetailId ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom	Identifies the specific variation of the product code when a product code is reused for different products over time. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUom are all provided, ProductDetailId takes precedence in identifying the product. Default: NOVARIANT.
ProductUom	String	Conditional: One of the following combinations is required to identify the product when updating the SerialStatus to COMMISSIONED: ProductDetailId ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom	Identifies the product packaging unit where a specified product code is used for multiple packaging levels. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom are all provided, ProductDetailId takes precedence in identifying the product.
LotId	Long	Conditional: One of the following combinations is required to identify the lot when updating the SerialStatus to COMMISSIONED: LotId LotNumber, LotSource, LotLocation	Unique identifier for the Lot record in OPSM. If the LotId is provided, the LotId is used to locate the lot record.
LotNumber	String	Conditional: One of the following combinations is required to identify the lot when updating the SerialStatus to COMMISSIONED: LotId LotNumber, LotSource, LotLocation	Lot number associated to the lot. The LotNumber, LotSource, LotLocation, and ProductDetailId are used to uniquely identify the lot number to be created or updated if the LotId is not provided. If LotId, LotNumber, LotSource and LotLocation are all provided, LotId takes precedence in identifying the lot.
LotSource	String	Conditional: One of the following combinations is required to identify the lot when updating the SerialStatus to COMMISSIONED: LotId LotNumber, LotSource, LotLocation	The source system associated to the lot. If LotId, LotNumber, LotSource and LotLocation are all provided, LotId takes precedence in identifying the lot. This must be a valid value in the PAS_SOURCE_SYSTEM lookup.
LotLocation	String	Conditional: One of the following combinations is required to identify the lot when updating the SerialStatus to COMMISSIONED: LotId LotNumber, LotSource, LotLocation	The location associated to the lot. The location can be null if the source system does not associate a location reference to the lot information. If LotId, LotNumber, LotSource and LotLocation are all provided, LotId takes precedence in identifying the lot. This must be a valid location defined in OPSM. If you are using location security, you must have update access for the lot location in the Location Security Setup page. For more information on location security, see Location Security Overview.
CommissionSegment1	String	No	Identifies the 1st segment of the document (Document Organization) used as reference when commissioning the serials.
CommissionSegment2	String	No	Identifies the 2nd segment of the document (Document Number) used as reference when commissioning the serials.
CommissionSegment3	String	No	Identifies the 3rd segment of the document (Document Type) used as reference when commissioning the serials. If CommissionSegment3 (Document Type) does not exist in the lookup PAS_DOCUMENT_TYPE then it is added to this lookup and a message is sent informing you about this.
CommissionSegment4	String	No	Identifies the 4th segment of the document used as a reference when commissioning serials.
CommissionSegment5	String	No	Identifies the 5th segment of the document used as a reference when commissioning serials.
CommissionSegment6	String	No	Identifies the 6th segment of the document used as a reference when commissioning serials.
CommissionSegment7	String	No	Identifies the 7th segment of the document used as a reference when commissioning serials.
CommissionSegment8	String	No	Identifies the 8th segment of the document used as a reference when commissioning serials.
CommissionSegment9	String	No	Identifies the 9th segment of the document used as a reference when commissioning serials.
CommissionSegment10	String	No	Identifies the 10th segment of the document used as a reference when commissioning serials.

editSerialStatusAndUsageByList and editSerialStatusAndUsageByListAsync Operations

These operations update SerialStatus, SerialUsage, and CounterfeitStatus information for a given list of serial numbers. These operations also support the commissioning of serials. These operations are performed only against existing serial numbers. A Reserve Serials, Commission Serials, Decommission Serials, or Edit Serials transaction is created depending on the type of edit performed.

If you are using location security, depending on the serial status and usage update you are making to a serial using this web service operation you need the following:

• To commission a serial, you need update access to the serial location, existing product detail location or location group, new product detail location or location group, and new lot location in the Location Security Setup page.

• To decommission a serial, you need update access to the serial location in the Location Security Setup page.

• For updating the attributes of the serial, you need update access to the serial location in the Location Security Setup page.

• For reserving the serial, you need update access to the serial location and existing lot location in the Location Security Update page.

Key Logic

• Updating Serials

  • Serials that are updated are identified by a list of one or more ItemSerialNumbers.

  • Serials are checked for uniqueness using this criteria:

    • Serial Type and Item Serial Number if the serial type is a standard type.

    • Item Serial Number if the product information is not passed. In this case, if there are multiple serials with the same item serial number, the duplicate serials exception is created.

    • Product Code and Item Serial Number if the serial type is a custom type.

  • Serials are updated with the value of the attributes that are passed. If no value is passed for an attribute, that attribute is not updated.

• Updating Serial Usage, Counterfeit Status, and Counterfeit Date

  • SerialUsage, CounterfeitStatus, and CounterfeitDate can be updated at any time even after the serial has been shipped or decommissioned.

  • Specifying a CounterfeitDate is optional when flagging the serial as counterfeit.

• Updating Product Information

  • Product information is only available for update if the Serial Type is defined as non-product specific and the Serial State is not SHIPPED or RETURNED.

• Commissioning

  • Product and lot information are required.

  • If the product and lot are identified on the serial, the SerialStatus is updated to COMMISSIONED.

  • The identified lot must be for the given product. The lot must already be defined within OPSM.

  • If the Serial State is SHIPPED or RETURNED, the product or lot information cannot be updated.

  • The lot associated to the serial can be modified if the serial is not shipped or the serial has been returned.

• Decommissioning

  • ReasonCode is required if changing SerialStatus to DECOMMISSIONED.

Required Input

• One or more ItemSerialNumbers must be identified.

• TransactionSourceName (source system for the transaction) must be identified.

Exceptions

• Required input is not received.

• ReasonCode is required if changing SerialStatus to DECOMMISSIONED.

• Serials were not identified for update.

• ProductCode is invalid.

• Lot does not exist.

• LotLocationId is provided and it does not exist in OPSM.

  Note: This validation is only executed when LotId is not provided and LotLocation is provided.

• ProductCode only is provided and multiple product records are found in OPSM.

• Product information (ProductCode, ProductCodeType, ProductCodeVariant, and ProductUom) is provided and there are no matching records in OPSM.

• ProductDetailId is provided and no corresponding record is found in OPSM.

• ProductUom is provided and does not exist in lookup PAS_PRODUCT_UOM and the ProductDetailId is not provided.

• Counterfeit date cannot be in the future.

• Lookup value is invalid:

  • Serial Status

  • Reason Code

  • Serial Usage

  • Counterfeit Status

  • Product Unit of Measure (UOM)

  • Transaction Source Name

• Location security access is invalid. You must have the following:

  • Update access for the serial location and lot location in the Location Security Setup page to reserve a serial.

  • Update access for the serial location, existing product detail location or location group, new product detail location or location group, and new lot location in the Location Security Setup page to commission a serial.

  • Update access for the serial location in the Location Security Setup page to decommission a serial.

  • Update access for the serial location in the Location Security Setup page to update the attributes of a serial.

Restrictions

No Restrictions

editSerialStatusAndUsageByList

Name	Type	Mandatory	Description
TransactionSourceName	String	Yes	Identifies the source name for the transaction. This must be a valid user defined value in the PAS_SOURCE_SYSTEM lookup.
SerialStatus	String	No	Identifies the status that is used for updating the selected serials. This must be a valid status in the PAS_SERIAL_STATUS lookup.
ReasonCode	String	Conditional: Required if the SerialStatus is DECOMMISSIONED.	Identifies the reason code on a decommissioned serial. This must be a valid user defined value in the PAS_REASON_CODE lookup.
SerialUsage	String	No	Identifies the serial usage. This must be a valid user defined value in the PAS_SERIAL_USAGE lookup.
CounterfeitStatus	String	No	Identifies the serial as COUNTERFEIT, NOTCOUNTERFEIT, or SUSPECT.
CounterfeitDate	Date	No	Notes the date of the counterfeit status.
ProductDetailId	Long	Conditional: One of the following combinations is required to identify the product when updating the SerialStatus to COMMISSIONED: ProductDetailId ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Unique identifier for the Product Detail record. If the ProductDetailId is provided, the ProductDetailId is used to locate the product detail record. If you are using location security, you must have update access for the product detail location or location group in the Location Security Setup page. For more information on location security, see Location Security Overview.
ProductCode	String	Conditional: One of the following combinations is required to identify the product when updating the SerialStatus to COMMISSIONED: ProductDetailId ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the product in OPSM. The product is uniquely identified using the ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUOM are all provided, ProductDetailId takes precedence in identifying the product. If you are using location security, you must have update access for the product detail location or location group in the Location Security Setup page. For more information on location security, see Location Security Overview.
ProductCodeType	String	Conditional: One of the following combinations is required to identify the product when updating the SerialStatus to COMMISSIONED: ProductDetailId ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the type of product code used in identification of the product in OPSM. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUOM are all provided, ProductDetailId takes precedence in identifying the product. This must be a valid value in the PAS_PRODUCT_CODE_TYPE lookup.
ProductCodeVariant	String	Conditional: One of the following combinations is required to identify the product when updating the SerialStatus to COMMISSIONED: ProductDetailId ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the specific variation of the product code when a product code is reused for different products over time. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUOM are all provided, ProductDetailId takes precedence in identifying the product. Default: NOVARIANT.
ProductUOM	String	Conditional: One of the following combinations is required to identify the product when updating the SerialStatus to COMMISSIONED: ProductDetailId ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the product packaging unit where a specified product code is used for multiple packaging levels. If ProductDetailId, ProductCode, ProductCodeType, ProductCodeVariant and ProductUOM are all provided, ProductDetailId takes precedence in identifying the product.
LotId	Long	Conditional: One of the following combinations is required to identify the lot when updating the SerialStatus to COMMISSIONED: LotId LotNumber, LotSource, LotLocation	Unique identifier for the Lot record in OPSM. If the LotId is provided, the LotId is used to locate the lot record.
LotNumber	String	Conditional: One of the following combinations is required to identify the lot when updating the SerialStatus to COMMISSIONED: LotId LotNumber, LotSource, LotLocation	Lot number associated to the lot. The LotNumber, LotSource, LotLocation, and ProductDetailId are used to uniquely identify the lot number to be created or updated if the LotId is not provided. If LotId, LotNumber, LotSource and LotLocation are all provided, LotId takes precedence in identifying the lot.
LotSource	String	Conditional: One of the following combinations is required to identify the lot when updating the SerialStatus to COMMISSIONED: LotId LotNumber, LotSource, LotLocation	The source system associated to the lot. If LotId, LotNumber, LotSource and LotLocation are all provided, LotId takes precedence in identifying the lot. This must be a valid value in the PAS_SOURCE_SYSTEM lookup.
LotLocation	String	Conditional: One of the following combinations is required to identify the lot when updating the SerialStatus to COMMISSIONED: LotId LotNumber, LotSource, LotLocation	The location associated to the lot. The location can be null if the source system does not associate a location reference to the lot information. If LotId, LotNumber, LotSource and LotLocation are all provided, LotId takes precedence in identifying the lot. This must be a valid location defined in OPSM. If you are using location security, you must have update access for the lot location in the Location Security Setup page. For more information on location security, see Location Security Overview.
LotLocationID	String	No	The lot location Id associated to the lot location.
CommissionSegment1	String	No	Identifies the 1st segment of the document (Document Organization) used as reference when commissioning the serials.
CommissionSegment2	String	No	Identifies the 2nd segment of the document (Document Number) used as reference when commissioning the serials.
CommissionSegment3	String	No	Identifies the 3rd segment of the document (Document Type) used as reference when commissioning the serials. If CommissionSegment3 (Document Type) does not exist in the lookup PAS_DOCUMENT_TYPE then it is added to this lookup and a message is sent informing you about this.
CommissionSegment4	String	No	Identifies the 4th segment of the document (Document Line Number) used as a reference when commissioning serials.
CommissionSegment5	String	No	Identifies the 5th segment of the document used as a reference when commissioning serials.
CommissionSegment6	String	No	Identifies the 6th segment of the document used as a reference when commissioning serials.
CommissionSegment7	String	No	Identifies the 7th segment of the document used as a reference when commissioning serials.
CommissionSegment8	String	No	Identifies the 8th segment of the document used as a reference when commissioning serials.
CommissionSegment9	String	No	Identifies the 9th segment of the document used as a reference when commissioning serials.
CommissionSegment10	String	No	Identifies the 10th segment of the document used as a reference when commissioning serials.
ItemNumber	String	No	Identifies the item number in the transactional system that is cross referenced to a product within OPSM. You can only update the item information using this web service operation. You cannot use the item information for serial selection.
ItemSource	String	No	Identifies the source system related to the item number. Item source is used to cross reference the item specific to a source system to identify the product in OPSM. This must be a valid value in the PAS_SOURCE_SYSTEM lookup. You can only update the item information using this web service operation. You cannot use the item information for serial selection.
ItemUOM	String	No	Identifies the item UOM in the transactional system that is cross referenced to the product packaging unit in OPSM. You can only update the item information using this web service operation. You cannot use the item information for serial selection.
AttributeChar1	String	No	User defined character field.
AttributeChar2	String	No	User defined character field.
AttributeChar3	String	No	User defined character field.
AttributeChar4	String	No	User defined character field.
AttributeChar5	String	No	User defined character field.
AttributeChar6	String	No	User defined character field.
AttributeChar7	String	No	User defined character field.
AttributeChar8	String	No	User defined character field.
AttributeChar9	String	No	User defined character field.
AttributeChar10	String	No	User defined character field.
AttributeChar11	String	No	User defined character field.
AttributeChar12	String	No	User defined character field.
AttributeChar13	String	No	User defined character field.
AttributeChar14	String	No	User defined character field.
AttributeChar15	String	No	User defined character field.
AttributeNumber1	Decimal	No	User defined number field.
AttributeNumber2	Decimal	No	User defined number field.
AttributeNumber3	Decimal	No	User defined number field.
AttributeNumber4	Decimal	No	User defined number field.
AttributeNumber5	Decimal	No	User defined number field.
AttributeTimestamp1	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
AttributeTimestamp2	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
AttributeTimestamp3	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
AttributeTimestamp4	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
AttributeTimestamp5	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.

SerialsListVO

Name	Type	Mandatory	Description
ItemSerialNumber	String	Yes	Identifies the serials that are updated by the operation.

WSDL

oracle.apps.pas.serials.serialsService.applicationModule.common.serviceinterface.SerialsServiceAMService.wsdl

For detailed descriptions of all input and output parameters, please refer to the SerialsServiceAMService wsdl for this endpoint.

PL/SQL Services

editSerialStatusAndUsageByListSQL and editSerialStatusAndUsageByListSQLAsync Operations

These are PL/SQL services in OPSM that update serial number attributes within OPSM. The editSerialStatusAndUsageByListSQL and editSerialStatusAndUsageByListSQLAsync operations update serial numbers as defined in the request. Serials are updated depending on the data included in the request.

Security

Since the data is being loaded directly into the PAS_S_EDIT_SERIAL_HDR_INT and PAS_S_EDIT_SERIAL_LIST_INT tables, OPSM's functional security is not applicable. It is the responsibility of the implementer to ensure that proper access to the database is validated.

Key Logic

• The Serial Interface Batch ID is used to identify the batch to be processed for Edit Serials by List.

• All records associated to the transaction must share the same Serial Interface Batch ID.

Procedure

To Use these PL/SQL Services

1. Load data into the PAS_S_EDIT_SERIAL_HDR_INT and PAS_S_EDIT_SERIAL_LIST_INT tables.

   Note: At least one record must be provided for each of the two tables listed above.

   These tables display the column names and descriptions and the required fields for the PAS_S_EDIT_SERIAL_HDR_INT and PAS_S_EDIT_SERIAL_LIST_INT tables:

   PAS_S_EDIT_SERIAL_HDR_INT Table

   Column Name	Column Description	Required?
   SERIAL_INTERFACE_BATCH_ID	Unique ID for each edit serial batch.	Y
   TRANSACTION_SOURCE_NAME	Transaction WHO Field.	Y
   SERIAL_STATUS	The status of the serial number (e.g. COMMISSIONED, DECOMMISSIONED).	N
   REASON_CODE	Reason for decommissioning the serial number (e.g. Voided, Expired, Damaged, Returned, etc.).	N
   SERIAL_USAGE	A code used to categorize serials (ex. samples, etc.).	N
   COUNTERFEIT_STATUS	A status used to indicate if this serial number is either suspected to be counterfeit, or is actually a counterfeit.	N
   COUNTERFEIT_DATE	The date that this serial was suspected or confirmed as being a counterfeit.	N
   PRODUCT_DETAIL_ID	The unique ID used to identify the Product Detail record in the PAS_S_PRODUCT_DETAILS table.	N
   PRODUCT_CODE	The unformatted product class identifier for the pharmaceutical product (e.g., the NDC number).	N
   PRODUCT_CODE_TYPE	The type of product code (e.g., NDC442, NDC532, NDC541, NDC542, GTIN).	N
   PRODUCT_CODE_VARIANT	A unique key value used in case Product Code and Product Code Type do not uniquely identify the product.	N
   PRODUCT_UOM	The unit of measure of the product.	N
   LOT_ID	The unique ID used to identify the Lot record in the PAS_S_LOTS table.	N
   LOT_NUMBER	Identification number assigned to a particular quantity or lot of material from a single manufacturer.	N
   LOT_SOURCE	The source system from which this lot is derived.	N
   LOT_LOCATION	The location of the lot number.	N
   LOT_LOCATION_ID	The unique ID used to identify the Location record related to the Lot in the PAS_S_LOCATIONS table.	N
   ITEM_NUMBER	The item number from the transactional system.	N
   ITEM_SOURCE	Transactional system that the ITEM_NUMBER is from.	N
   ITEM_UOM	The unit of measure of the item number as it exists in the transactional system.	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   COMMISSION_SEGMENT_1	Identifies the 1st segment of the document (Document Organization) used as reference when commissioning the serials.	N
   COMMISSION_SEGMENT_2	Identifies the 2nd segment of the document (Document Number) used as reference when commissioning the serials.	N
   COMMISSION_SEGMENT_3	Identifies the 3rd segment of the document (Document Type) used as reference when commissioning the serials.	N
   COMMISSION_SEGMENT_4	Identifies the 4th segment of the document used as a reference when commissioning serials.	N
   COMMISSION_SEGMENT_5	Identifies the 5th segment of the document used as a reference when commissioning serials.	N
   COMMISSION_SEGMENT_6	Identifies the 6th segment of the document used as a reference when commissioning serials.	N
   COMMISSION_SEGMENT_7	Identifies the 7th segment of the document used as a reference when commissioning serials.	N
   COMMISSION_SEGMENT_8	Identifies the 8th segment of the document used as a reference when commissioning serials.	N
   COMMISSION_SEGMENT_9	Identifies the 9th segment of the document used as a reference when commissioning serials.	N
   COMMISSION_SEGMENT_10	Identifies the 10th segment of the document used as a reference when commissioning serials.	N
   ATTRIBUTE_CHAR1	User defined character field 1.	N
   ATTRIBUTE_CHAR2	User defined character field 2.	N
   ATTRIBUTE_CHAR3	User defined character field 3.	N
   ATTRIBUTE_CHAR4	User defined character field 4.	N
   ATTRIBUTE_CHAR5	User defined character field 5.	N
   ATTRIBUTE_CHAR6	User defined character field 6.	N
   ATTRIBUTE_CHAR7	User defined character field 7.	N
   ATTRIBUTE_CHAR8	User defined character field 8.	N
   ATTRIBUTE_CHAR9	User defined character field 9.	N
   ATTRIBUTE_CHAR10	User defined character field 10.	N
   ATTRIBUTE_CHAR11	User defined character field 11.	N
   ATTRIBUTE_CHAR12	User defined character field 12.	N
   ATTRIBUTE_CHAR13	User defined character field 13.	N
   ATTRIBUTE_CHAR14	User defined character field 14.	N
   ATTRIBUTE_CHAR15	User defined character field 15.	N
   ATTRIBUTE_NUMBER1	User defined numeric field 1.	N
   ATTRIBUTE_NUMBER2	User defined numeric field 2.	N
   ATTRIBUTE_NUMBER3	User defined numeric field 3.	N
   ATTRIBUTE_NUMBER4	User defined numeric field 4.	N
   ATTRIBUTE_NUMBER5	User defined numeric field 5.	N
   ATTRIBUTE_TIMESTAMP1	User defined date / time field 1.	N
   ATTRIBUTE_TIMESTAMP2	User defined date / time field 2.	N
   ATTRIBUTE_TIMESTAMP3	User defined date / time field 3.	N
   ATTRIBUTE_TIMESTAMP4	User defined date / time field 4.	N
   ATTRIBUTE_TIMESTAMP5	User defined date / time field 5.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table .	Y (Default=0)

   PAS_S_EDIT_SERIAL_LIST_INT Table

   Column Name	Column Description	Required?
   EDIT_SERIAL_LIST_ID	Unique ID for each item serial number in the edit serial list.	Y
   SERIAL_INTERFACE_BATCH_ID	Unique ID for each edit serial batch.	Y
   ITEM_SERIAL_NUMBER	A unique number assigned for identification which varies from its successor or predecessor by a fixed discrete integer value.	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)

2. The Serial Interface Batch ID must be generated by the user. The Serial Interface Batch ID must associate all records within these two tables for one transaction.

3. In addition to the Serial Interface Batch ID, the user must also supply the EDIT_SERIAL_LIST_ID within the PAS_S_EDIT_SERIAL_LIST_INT table. This ID must uniquely identify each row within the PAS_S_EDIT_SERIAL_LIST_INT table.

4. When executing the PL/SQL service, the Serial Interface Batch ID is required as input into the service. OPSM searches for this Serial Interface Batch ID within the PAS_S_EDIT_SERIAL_HDR_INT and PAS_S_EDIT_SERIAL_LIST_INT tables. If no corresponding Serial Interface Batch ID is found, an INVALID BATCH ID ERROR is issued and no further processing occurs.

5. After the service has been executed for a batch, if processing is successful, the records for the batch are removed from the PAS_S_EDIT_SERIAL_HDR_INT and PAS_S_EDIT_SERIAL_LIST_INT tables, and the serials are updated in OPSM for use in subsequent processing. However, if errors are found during processing, the records for the batch remain in the PAS_S_EDIT_SERIAL_HDR_INT and PAS_S_EDIT_SERIAL_LIST_INT tables, and the ERROR_STATUS_FLAG within the tables is updated to a value of "1" for all records with an error. Additionally, error messages are written to the PAS_S_EDIT_SERIALS_ERRORS_INT table. After the errors have been resolved, the ERROR_STATUS_FLAG must be updated to a value of "0" for all rows, or the processing will fail with an error stating that there is an ERROR STATUS FLAG error. No further processing is done until the error status flag has the correct value.

   This table displays the column names and descriptions for the PAS_S_EDIT_SERIAL_ERRORS_INT table:

   PAS_S_EDIT_SERIAL_ERRORS_INT Table

   Column Name	Column Description
   EDIT_SERIAL_ERROR_ID	Internal use only - unique ID for each edit serial interface error.
   SERIAL_INTERFACE_BATCH_ID	Internal use only - unique ID for each edit serial batch.
   ERROR_CODE	The error code issued for the edit serial error.
   ERROR_SEVERITY_LEVEL	A code indicating the severity level of the transaction error message (i.e. 1=ERRORS, 2=WARNING, 3=MESSAGE).
   CREATED_BY	Who column: indicates the user who created the row.
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.
   ERROR_MESSAGE	Is the complete error message.

6. If processing is successful after correction of the errors, the batch records in the PAS_S_EDIT_SERIAL_HDR_INT and PAS_S_EDIT_SERIAL_LIST_INT tables as well as the error messages in the PAS_S_EDIT_SERIALS_ERRORS_INT table are removed.

TransactionsServiceAMService (Inbound)

The TransactionsServiceAMService endpoint is an inbound asynchronous business service that is part of the interoperability solution of the OPSM system. This endpoint enables external systems to record an import, shipment, receipt, or return transaction into the OPSM system. In addition, the system shall optionally enable:

• Creating serials in OPSM at time of shipment or receipt. (The OPSM system must be configured on the Product or Product Location Overrides to "Create Serials for Shipments" or "Create Serials for Receipts").

  For information on maintaining products, see Maintaining Products and Item Cross Reference.

• Commissioning the serials at time of shipment or receipt.

• Creating lots on a shipment, receipt, return, or import transaction (The OPSM System Parameters must be configured to "Allow Lot Creation" by transaction type.)

  For information on maintaining system parameters, see Maintaining System Parameters.

• Updating packaging hierarchy in OPSM at time of shipment, receipt, or return.

• Transmitting serials to centralized serials databases on a shipment, return, or import (Serial Destination Rules must be defined for transmission of the serials).

  For information on defining serial destination rules, see Maintaining Serial Destinations and Destination Rules.

• Recording the shipment, receipt, or return to an internal customer (The OPSM system must be configured on the Product or Product Location Overrides to "Record Internal Shipments", "Record Internal Receipts" or "Record Internal Returns").

  For information on maintaining products, see Maintaining Products and Item Cross Reference.

• Shipping serials multiple times to external customers is allowed if the serials are not in a packaging hierarchy and the Allow Multiple Shipments check box is selected on the Shipment sub tab of the Maintain System Parameters page.

  For information on maintaining system parameters, see Maintaining System Parameters.

• Receiving serials multiple times from external customers is allowed if the serials are not in a packaging hierarchy and the Allow Multiple Receipts check box is selected on the Receipt sub tab of the Maintain System Parameters page.

  For information on maintaining system parameters, see Maintaining System Parameters

If you are using location security, you need update access to the following data elements in the Location Security Setup page depending on the type of transaction you are capturing using the endpoint. If you do not have the correct update access, then the operation ends and sends an error message.

Shipment Transactions

• To capture a shipment transaction for existing serials, you need update access in the Location Security Setup page for:

  • Transaction location

  During the shipment of existing serials, if both the lot and serials exist, you only need update access to the transaction location to ship those serials. If the lot is being created, you need update access to the lot location and product detail location or location group on the lot. After the lot is created, the endpoint uses the transaction location to validate that you can commission the serials. You can create a lot with no location but after the lot is created, this endpoint enables you to use this lot in the transaction based on update access to the transaction location.

• To capture a shipment transaction and pack existing serials for that shipment, you need update access in the Location Security Setup page for:

  • Transaction location

• To capture a shipment transaction and ship an existing packaging hierarchy for that shipment, you need update access in the Location Security Setup page for:

  • Transaction location

• To capture a shipment transaction and import serials that for that shipment, you need update access in the Location Security Setup page for:

  • Transaction location

  • Product detail location or location group

  • Lot location (new or existing lot)

• To capture a shipment transaction, create new packages, and pack serials for that shipment, you need update access in the Location Security Setup page for:

  • Transaction location

  • Package location or location group

  After the package is created, the endpoint does not check for update access on the existing items being packed. Packaging is enabled based on the transaction location. This endpoint does not use the location or location group fields from the TransactionLabelVO for serials that are being created. The new serials use the create location from the transaction.

• To capture a shipment transaction, create new packages, pack, and import serials for that shipment, you need update access in the Location Security Setup page for:

  • Transaction location

  • Product detail location or location group

  • Package location or location group

  • Lot location

• To capture a shipment transaction and create a lot for that shipment, you need update access in the Location Security Setup page for:

  • Transaction location

  • Product detail location or location group (required to create a lot)

  • Lot location (required to create a lot)

Receipt Transactions

• To capture a receipt transaction for existing serials, you need update access in the Location Security Setup page for:

  • Transaction location

  During the receipt of existing serials, if both the lot and serials exist, you only need update access to the transaction location to receive those serials. If the lot is being created, you need update access to the lot location and product detail location or location group on the lot. After the lot is created, the endpoint uses the transaction location to validate that you can commission the serials. You can create a lot with no location but after the lot is created, this endpoint enables you to use this lot in the transaction based on update access to the transaction location.

• To capture a receipt transaction and pack existing serials for that receipt, you need update access in the Location Security Setup page for:

  • Transaction location

• To capture a receipt transaction and receive an existing packaging hierarchy for that receipt, you need update access in the Location Security Setup page for:

  • Transaction location

• To capture a receipt transaction and import serials that for that receipt, you need update access in the Location Security Setup page for:

  • Transaction location

  • Product detail location or location group

  • Lot location (new or existing lot)

• To capture a receipt transaction, create new packages, and pack serials for that receipt, you need update access in the Location Security Setup page for:

  • Transaction location

  • Package location or location group

  After the package is created, the endpoint does not check for update access on the existing items being packed. Packaging is enabled based on the transaction location. The new serials use the create location from the transaction.

• To capture a receipt transaction, create new packages, pack, and import serials for that receipt, you need update access in the Location Security Setup page for:

  • Transaction location

  • Product detail location or location group

  • Package location or location group

  • Lot location

• To capture a receipt transaction and create a lot for that receipt, you need update access in the Location Security Setup page for:

  • Transaction location

  • Product detail location or location group (required to create a lot)

  • Lot location (required to create a lot)

Return Transactions

• To capture a return transaction for existing serials, you need update access in the Location Security Setup page for:

  • Transaction location

• To capture a return transaction and create a lot, you need update access in the Location Security Setup page for:

  • Transaction location

  • Product detail location or location group for the lot (required to create a lot)

  • Lot location (required to create a lot)

Import Transactions

To capture an import transaction, you need update access in the Location Security Setup page for:

• Transaction location

• Product detail location or location group (if product information is provided)

• Lot location (if lot is provided)

Operation Type	Operation	Description
Web Service	createTransaction createTransactionAsync	The createTransaction operations create a SHIPMENT, RETURN, or IMPORT transaction in OPSM. After the transaction is created, a business event is triggered to automatically process the newly created transaction. Note: You cannot create Receipt transactions using the createTransaction operations.
PL/SQL Service	processTransactionSQL processTransactionSQLAsync	The processTransactionSQL and processTransactionSQLAsync operations process shipment, receipt, or return transactions as defined in the request. Serials are processed and serial state is changed to SHIPPED, RECEIVED, or RETURNED depending on the data included in the request. The Receipt PL/SQL service can also be used to create an aggregation (Ship and Return cannot).
PL/SQL Service	processImportAndPackSQL processImportAndPackSQLAsync	These are PL/SQL services in OPSM, that import, create, and pack serial numbers within OPSM. The processImportAndPackSQL and processImportAndPackSQLAsync operations create serial numbers as defined in the request and then pack them into a packaging hierarchy. Serials are created in a reserved or commissioned status depending on the data included in the request.

Web Services

createTransaction and createTransactionAsync Operations

These operations are used to create a shipment, return, or import transaction in OPSM.

Note: You cannot create Receipt transactions using the createTransaction operations.

Key Logic

• The TransactionSourceName must be the source system identified on the location defined in OPSM.

• Lot Validation and Creation

  • If Allow Lot Creation for the specific transaction type is configured in the System Parameters, the lot is created in OPSM if it does not already exist using the information on the transaction. An existing lot is not updated.

  • If lot creation is not allowed, the lot must already exist in OPSM.

• Shipment Transaction

  • A shipment transaction must contain the following elements:

    • transactionVO - required to have one and only one.

    • transactionProductVO - required to have at least one that identifies the transacted product. More than one may exist for identification of the product for lower level packaging units or components.

    • transactionLabelVO - optional, either labels (for inference) must be identified in the transctionLabelVO or serials must be identified in the transactionSerialAssocVO. If labels or serials are not defined, the transaction is created in OPSM with an exception status.

    • transactionSerialAssocVO - optional, either labels (for inference) must be identified in the transctionLabelVO or serials must be identified in the transactionSerialAssocVO. If labels or serials are not defined, the transaction is created in OPSM with an exception status.

    • transactionDocumentVO - optional, highly recommended for identification of the shipment reference document.

    • transactionAddressVO - optional, highly recommended for identification of trading partners.

  • Internal Shipment

    • OPSM must be configured to accept internal shipment transactions in the Product definition. If the system is not recording internal shipments, the transaction is not processed.

    • If the transaction is flagged as internal, the serial state is not updated.

  • Packaging Hierarchy

    • If a new package is identified on the transactions where the LabelType is not a Serial Type, it is automatically created in OPSM when the shipment transaction is processed.

    • The packaging hierarchy is automatically updated in OPSM based on the packing configuration sent on the shipment transaction.

    • The highest level package is automatically unpacked when the shipment transaction is processed.

  • Inference

    • If a package is identified in the transactionLabelVO that does not have any associated serials, inference is used to identify serials that match the product and lot identified on the transaction. Any lower level serials associated to the matched serials are also identified on the transaction.

    • If a shipped item serial is identified that does not have lower level serials, inference is used to identify lower level serials using the packaging hierarchy in OPSM.

• Return Transaction

  • A return transaction must contain the following elements:

    • transactionVO - required to have one and only one.

    • transactionProductVO - required to have at least one that identifies the transacted product. More than one may exist for identification of the product for lower level packaging units or components.

    • transactionLabelVO - optional, either labels (for inference) must be identified in the transctionLabelVO or serials must be identified in the transactionSerialAssocVO. If labels or serials are not defined, the transaction is created in OPSM with an exception status.

    • transactionSerialAssocVO - optional, either labels (for inference) must be identified in the transctionLabelVO or serials must be identified in the transactionSerialAssocVO. If labels or serials are not defined, the transaction is created in OPSM with an exception status.

    • transactionDocumentVO - optional, highly recommended for identification of the return reference document.

    • transactionAddressVO - optional, highly recommended for identification of trading partners.

  • Internal Return

    • OPSM must be configured to accept internal return transactions in the Product definition. If the system is not recording internal returns, the transaction is not processed.

    • If the transaction is flagged as internal, the serial state is not updated.

  • Packaging Hierarchy

    • If a new package is identified on the transactions where the LabelType is not a Serial Type, it automatically is created in OPSM when the return transaction is processed.

    • The packaging hierarchy is automatically updated in OPSM based on the packing configuration sent on the return transaction.

    • The highest level package is automatically unpacked when the return transaction is processed.

  • Inference

    • If a package is identified in the transactionLabelVO that does not have any associated serials, inference is used to identify serials that match the product and lot identified on the transaction. Any lower level serials associated to the match serials are also identified on the transaction.

    • If a returned item serial is identified that does not have lower level serials, inference is used to identify lower level serials using the packaging hierarchy in OPSM.

• Import Transaction

  • An Import transaction must contain the following elements:

    • transactionVO - required to have one and only one.

    • transactionProductVO - required to have at least one that identifies the transacted product. More than one may exist for identification of the product for lower level packaging units or components.

    • transactionSerialAssocVO - required, the number of transaction serial association records must match the transaction quantity.

    • transactionDocumentVO - optional, highly recommended for identification of the reference document.

  • Serial Creation

    • Serials identified on the import transaction are created in OPSM.

    • If a duplicate serial already exists in OPSM, the duplicate serial is created and both the original and the newly created serial are flagged as suspect counterfeit.

Required Input

• transactionVO (Required)

  • TransLocation

  • TransactionType

  • TransDate

  • TransactionQuantity

  • TransactionUnitOfMeasure

  • TransactionSourceName

  • ItemNumber, ItemSource, ItemUOM or ProductCode, ProductCodeType, ProductCodeVariant, ProductUOM

  • LotNumber

  • LotSource

• TransactionProductVO (Required)

  • ItemNumber, ItemSource, ItemUOM or ProductCode, ProductCodeType, ProductCodeVariant, ProductUOM

  • LotNumber

  • LotSource

• TransactionSerialAssocVO (Optional)

  • ItemSerialNumber

  • SerialType

  • ShippedItemFlag - set to identify the shipped item (for example, item on the sales order line)

• TransactionLabelVO (Optional)

  • Label

  • LabelType

  • ShippedItemFlag - set to identify the shipped item (for example, item on the sales order line)

• TransactionDocumentVO (Optional)

  • PrimaryFlag

• TransactionAddressVO (Optional)

  • AddressCode

  • AddressType

  • Name

Exceptions

• Required parameters not passed.

• The transaction source system does not match the source system defined for the transaction location.

• Invalid lookup value:

  • Source System

  • Transaction Type

  • Serial Usage

  • Product Code Type

  • Product UOM

  • Label Type

  • Packaging Unit

  • Label Print Format

  • Serial Type

  • Address Code

  • Address Type

• Invalid ProductCode provided on shipment and return transactions.

• Invalid Lot information provided (assuming Allow Lot Creation has not been enabled).

• Invalid location security access. Depending on the type of transaction, you need update access in the Location Security Setup page for certain elements provided in the transaction.

Restrictions

An alternate Label and LabelType cannot be used to identify the package.

transactionVO

Name	Type	Mandatory	Description
TransactionSourceName	String	Yes	Uniquely identifies the source system where the transaction originated. This must be a valid value in the PAS_SOURCE_SYSTEM lookup.
TransactionType	String	Yes	Identifies the transaction type. Valid values are: SHIPMENT, RETURN, and IMPORT. This must be a valid value in the PAS_TRANSACTION_TYPE lookup.
InternalTransactionFlag	String	Yes	Indicates whether a change of ownership occurs as a result of the transaction. On a shipment or return transaction, if the transaction is flagged as internal the state of the serial is not modified. Internal transactions can optionally be recorded in OPSM. This setting is configurable through the product definition and product location overrides. Valid values: 1 = Yes (internal) 0 = No
TransDate	dateTime (YYYY-MM-DDTHH:mm:ss:SSSS)	Yes	The date and time that the transaction occurred.
TransLocation	String	Yes	The location where the transaction occurred. This must be a valid location in OPSM. If you are using location security, you need update access to the transaction location in the Location Security Setup page.
TransactionQuantity	Decimal	Yes	The quantity of the product that was transacted.
TransactionUnitofMeasure	String	Yes	The UOM associated with the quantity.
ItemNumber	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the item number in the transactional system that is cross referenced to a product within OPSM.
ItemSource	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the source system related to the item number. Item source is used to cross reference the item specific to a source system to identify the product in OPSM. This must be a valid value in the PAS_SOURCE_SYSTEM lookup.
ItemUOM	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the item UOM in the transactional system that is cross referenced to the product packaging unit in OPSM.
ProductCode	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the product in OPSM. The product is uniquely identified using the ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM. If you are using location security, you need update access to the product detail location or location group in the Location Security Setup page.
ProductCodeType	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the type of product code used in identification of the product in OPSM. This must be a valid value in the PAS_PRODUCT_CODE_TYPE lookup.
ProductCodeVariant	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the specific variation of the product code when a product code is reused for different products over time. Default: NOVARIANT.
ProductUOM	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUOM or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the product packaging unit where a specified product code is used for multiple packaging levels.
LotNumber	String	Yes	Lot number associated to the lot. The LotNumber, LotSource, LotLocation, and Product information are used to uniquely identify the lot number to be created or updated.
LotSource	String	Yes	The source system associated to the lot. This must be a valid value in the PAS_SOURCE_SYSTEM lookup.
LotLocation	String	No	The location associated to the lot. The location can be null if the source system does not associate a location reference to the lot information. The Lot Location must only be included in the request if a location has been assigned to the lot. If there is no location associated to the lot, then this field can be left blank. This applies to all Transactions (Import, Shipment, and Return). This must be a valid location in OPSM. If you are using location security, you need update access to the lot location in the Location Security Setup page.
ParentLot	String	No	Identifies the parent lot number associated with the lot.
ImportSerialsFlag	String	Yes	Not used.
EventCreatedBy	String	No	The created by identity from the capturing application.
EventCreatedDate	dateTime (YYYY-MM-DDTHH:mm:ss:SSSS)	No	The date and time at which the capturing application asserts the event occurred.
EventCreatedSourceName	String	No	The source system identity of the capturing application.
EventUpdatedBy	String	No	The updated by identity from the capturing application.
EventUpdatedDate	dateTime (YYYY-MM-DDTHH:mm:ss:SSSS)	No	The date and time at which the capturing application asserts the event data was updated.
EventUpdatedSourceName	String	No	The source system identity of the capturing application that updated the event.
AttributeCategory	String	No	Structure definition of the user descriptive flexfields.
AttributeChar1	String	No	User defined character field.
AttributeChar2	String	No	User defined character field.
AttributeChar3	String	No	User defined character field.
AttributeChar4	String	No	User defined character field.
AttributeChar5	String	No	User defined character field.
AttributeChar6	String	No	User defined character field.
AttributeChar7	String	No	User defined character field.
AttributeChar8	String	No	User defined character field.
AttributeChar9	String	No	User defined character field.
AttributeChar10	String	No	User defined character field.
AttributeChar11	String	No	User defined character field.
AttributeChar12	String	No	User defined character field.
AttributeChar13	String	No	User defined character field.
AttributeChar14	String	No	User defined character field.
AttributeChar15	String	No	User defined character field.
AttributeNumber1	Decimal	No	User defined numeric field.
AttributeNumber2	Decimal	No	User defined numeric field.
AttributeNumber3	Decimal	No	User defined numeric field.
AttributeNumber4	Decimal	No	User defined numeric field.
AttributeNumber5	Decimal	No	User defined numeric field.
AttributeTimeStamp1	Decimal	No	User defined timestamp field.
AttributeTimeStamp2	Decimal	No	User defined timestamp field.
AttributeTimeStamp3	Decimal	No	User defined timestamp field.
AttributeTimeStamp4	Decimal	No	User defined timestamp field.
AttributeTimeStamp5	Decimal	No	User defined timestamp field.

TransactionProductVO

Name	Type	Mandatory	Description
ProductCode	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUom or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the product in OPSM. The product is uniquely identified using the ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM. If you are using location security, and a lot is being created with this product, then you need update access to the lot location in the Location Security Setup page.
ProductCodeType	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUom or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the type of product code used in identification of the product in OPSM. This must be a valid value in the PAS_PRODUCT_CODE_TYPE lookup.
ProductCodeVariant	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUom or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the specific variation of the product code when a product code is reused for different products over time. Default: NOVARIANT.
ProductUOM	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUom or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the product packaging unit where a specified product code is used for multiple packaging levels.
ItemNumber	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUom or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the item number in the transactional system that is cross referenced to a product within OPSM.
ItemSource	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUom or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the source system related to the item number. Item source is used to cross reference the item specific to a source system to identify the product in OPSM.
ItemUom	String	Conditional: One of the following combinations must be sent to identify the product detail: ItemNumber, ItemSource, ItemUom or ProductCode, ProductCodeType, ProductCodeVariant, and ProductUOM	Identifies the item UOM in the transactional system that is cross referenced to the product packaging unit in OPSM.
ItemIdentifier	Long	No	Not used.
LotNumber	String	Yes	Lot number associated to the lot. The LotNumber, LotSource, LotLocation, and Product information are used to uniquely identify the lot number to be created or updated.
LotSource	String	Yes	The source system associated to the lot. This must be a valid value in the PAS_SOURCE_SYSTEM lookup.
LotLocation	String	No	The location associated to the lot. The location can be null if the source system does not associate a location reference to the lot information. The Lot Location must only be included in the request if a location has been assigned to the lot. If there is no location associated to the lot, then this field can be left blank. This applies to all Transactions (Import, Shipment, and Return). This must be a valid location in OPSM. If you are using location security, and a lot is being created, then you need update access to the lot location in the Location Security Setup page.
ParentLot	String	No	Identifies the parent lot number associated with the lot.
Origination Date	Date	No	The origination date of the lot. Origination date is used to create the lot when OPSM is configured to allow creation of the lot when the transaction is processed and the lot does not already exist.
ExpirationDate	Date	No	The expiration date of the lot. Expiration date is used to create the lot when OPSM is configured to allow creation of the lot when the transaction is processed and the lot does not already exist.
BestByDate	Date	No	The lot best by date. Best by date is used to create the lot when OPSM is configured to allow creation of the lot when the transaction is processed and the lot does not already exist.
SellByDate	Date	No	The lot sell by date. Sell by date is used to create the lot when OPSM is configured to allow creation of the lot when the transaction is processed and the lot does not already exist.
RetestDate	Date	No	The lot retest date. Retest date is used to create the lot when OPSM is configured to allow creation of the lot when the transaction is processed and the lot does not already exist.

TransactionSerialAssocVO

Name	Type	Mandatory	Description
ItemSerialNumber	String	Yes	A unique identifier assigned for identification of a specific unit of a product.
SerialType	String	Yes	Identifies the type of serial number. This must be a valid value in the PAS_SERIAL_TYPE lookup.
SerialIdentification	String	No	Unique identification of the TransactionSerialAssoc record. Used in conjunction with the ParentSerialIdentification to establish hierarchy.
ParentSerialIdentification	String	No	Identifies the TransactionSerialAssoc instance that is the parent of the current TransactionSerialAssoc instance.
ShippedItemFlag	String	Yes	Identifies the serial as the shipped or transacted serial for the transaction. Valid values: 1 = Yes, 0 = No.
ManufacturedDate	Date	No	Identifies the date the product was manufactured. This value is used to populate the Manufactured Date in the Serials table when the serial is added during processing of the shipment transaction.
SerialUsage	String	No	Identifies the serial usage. This value is used to populate the Serial Usage in the Serials table when the serial is added during processing of the shipment transaction. This must be a valid user defined value in the PAS_SERIAL_USAGE lookup.
SaleableUnitFlag	String	No	Identifies the serial as a saleable unit. This value is used to populate the Saleable Unit Flag in the Serials table when the serial is added during processing of the shipment transaction. Valid values: 1 = Yes, 0 = No.
AttributeChar1	String	No	User defined character field.
AttributeChar2	String	No	User defined character field.
AttributeChar3	String	No	User defined character field.
AttributeChar4	String	No	User defined character field.
AttributeChar5	String	No	User defined character field.
AttributeChar6	String	No	User defined character field.
AttributeChar7	String	No	User defined character field.
AttributeChar8	String	No	User defined character field.
AttributeChar9	String	No	User defined character field.
AttributeChar10	String	No	User defined character field.
AttributeNumber1	Decimal	No	User defined numeric field.
AttributeNumber2	Decimal	No	User defined numeric field.
AttributeNumber3	Decimal	No	User defined numeric field.
AttributeNumber4	Decimal	No	User defined numeric field.
AttributeNumber5	Decimal	No	User defined numeric field.
AttributeTimestamp1	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
AttributeTimestamp2	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
AttributeTimestamp3	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
AttributeTimestamp4	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.
AttributeTimestamp5	dateTime (YYYY-MM-DDTHH:mm:ss.SSS)	No	User defined timestamp field.

TransactionLabelVO

Name	Type	Mandatory	Description
Label	String	Yes	The primary label used to identify the package.
LabelType	String	Yes	The primary label type used to identify the package. This must be a valid user defined value in the PAS_LABEL_TYPE lookup.
ParentLabel	String	No	The primary ParentLabel and ParentLabelType are used to establish the packaging hierarchy where the Label and LabelType is packed into the ParentLabel and ParentLabelType.
ParentLabelType	String	No	The primary ParentLabel and ParentLabelType are used to establish the packaging hierarchy where the Label and LabelType is packed into the ParentLabel and ParentLabelType. This must be a valid value in the PAS_LABEL_TYPE lookup.
ShippedItemFlag	String	Yes	Identifies the label as the shipped or transacted serial for the transaction. Valid values: 1 = Yes, 0 = No.
ReusableFlag	String	No	Identifies the pack as reusable. This value is used in the creation of a new pack during processing of the transaction. Valid values: 1 = Yes, 0 = No.
PackageUnit	String	No	Identifies the packaging unit associated with the package. This must be a valid value in the PAS_PACKAGE_UNIT lookup.
LabelPrintFormat	String	No	Identifies the label print format associated with the pack. This value is used for creation of new packs when processing the transaction. This must be a valid value in the PAS_LABEL_PRINT_FORMAT lookup.
Location	String	No	Identifies the location associated with the package. You can only associate a location or a location group to a package. Both cannot be entered at the same time. This must be a valid location in OPSM. If you are using location security, and a package is being created during shipment, then you need update access to the package location in the Location Security Setup page. Otherwise, this field is ignored.
Location Group	String	No	Identifies the location group associated with the package. You can only associate a location or a location group to a package. Both cannot be entered at the same time. This must be a valid location group in OPSM. If you are using location security, and a package is being created during shipment, then you need update access to the package location group in the Location Security Setup page. Otherwise, this field is ignored.

TransactionAddressesVO

Name	Type	Mandatory	Description
AddressCode	String	Yes	Identifies the address as a source or destination address. This must be a valid value in the PAS_ADDRESS_CODE lookup.
AddressType	String	Yes	Identifies the type of address. This must be a valid value in the PAS_ADDRESS_TYPE lookup.
Name	String	Yes	The business name associated with the address.
Street1	String	No	Used to hold a portion of the street address.
Street2	String	No	Used to hold a portion of the street address.
Street3	String	No	Used to hold a portion of the street address.
Street4	String	No	Used to hold a portion of the street address.
City	String	No	The city of the transaction address.
StateOrRegion	String	No	The state or region of the transaction address.
PostalCode	String	No	The postal code of the transaction address.
Country	String	No	The country of the transaction address.
GlobalLocationNumber	String	No	The Global Location Number associated with the transaction address.
Contact Name	String	No	The contact name for the transaction address.
ContactPhone	String	No	The contact phone number for the transaction address.
ContactEmail	String	No	The contact email for the transaction address.

TransactionDocumentVO

Name	Type	Mandatory	Description
PrimaryFlag	String	Yes	Set to indicate that this reference document is the primary reference document. Only one reference document per transaction can be flagged as the primary.
Segment1	String	No	The 1st segment (Document Organization) of the reference document.
Segment2	String	No	The 2nd segment (Document Number) of the reference document.
Segment3	String	No	The 3rd segment (Document Type) of the reference document. OPSM automatically adds a new Document Type to the PAS_DOCUMENT_TYPE lookup when processing the transaction if it does not exist.
Segment4	String	No	The 4th segment (Document Line Number) of the reference document.
Segment5	String	No	The 5th segment of the reference document.
Segment6	String	No	The 6th segment of the reference document.
Segment7	String	No	The 7th segment of the reference document.
Segment8	String	No	The 8th segment of the reference document.
Segment9	String	No	The 9th segment of the reference document.
Segment10	String	No	The 10th segment of the reference document.
PedigreeDocumentType	String	No	The code used to identify the document type on the pedigree.

WSDL

oracle.apps.pas.transactions.transactionsService.applicationModule.common.serviceinterface.TransactionsServiceAMService.wsdl

For detailed descriptions of all input and output parameters, please refer to the TransactionsServiceAMService wsdl for this endpoint.

PL/SQL Services

processTransactionSQL and processTransactionSQLAsync Operations

The processTransactionSQL and processTransactionSQLAsync operations process shipment, receipt, return transactions as defined in the request. Serials are processed and serial state is changed to SHIPPED, RECEIVED, RETURNED depending on the data included in the request.

OPSM does not support the following for the shipment and return transactions:

• Packing, repacking, or unpacking of serial numbers.

• Creating of new serial numbers.

• Creating of new lots.

• Creating product identification using item cross reference.

• Changing of lot or product information associated to the serial.

• Reprocessing shipments in exception status through the Maintain Transactions page.

• Viewing shipments in exception status through the Maintain Transactions page.

Key Logic

• The Transaction Interface Batch ID and the Transaction Type are used to identify the batch to be processed for the shipment, receipt, or return.

  Note: The Transaction Type value must be SHIPMENT for shipment processing, RECEIPT for receipt processing, and RETURN for return processing.

• All records associated to the shipment, receipt, or return transaction must share the same Transaction Interface Batch ID.

• The receipt transaction will consider each product being sent in to determine if serials must be created or not. The shipment transaction only considers the settings of the product in the Transaction Product.

Security

Since the data is being loaded directly into the PAS_S_SHP_SERIAL_ASSOC_INT, PAS_S_SHP_SERIAL_DOCS_INT, PAS_S_SHP_SERIAL_ADDR_INT, PAS_S_SHP_SERIAL_PRODS_INT, PAS_S_SHP_SERIAL_LBL_INT, and PAS_S_SHP_SERIAL_INT tables, OPSM's functional security is not applicable. It is the responsibility of the implementer to ensure that proper access to the database is validated.

Procedure

To Use these PL/SQL Services

1. 1. Load data into the these tables:

      • PAS_S_SHP_SERIAL_INT

      • PAS_S_SHP_SERIAL_PRODS_INT

      • PAS_S_SHP_SERIAL_ADDR_INT

      • PAS_S_SHP_SERIAL_DOCS_INT

   2. Load data into the PAS_S_SHP_SERIAL_ASSOC_INT table for a loose serials shipment or for return of loose serials.

      For receipt transactions, load data in into the PAS_S_SHP_SERIAL_ASSOC_INT table for each serial being imported into OPSM. It must also indicate the parent serialized item if a packaging hierarchy is being created during processing.

   3. Load data into the PAS_S_SHP_SERIAL_LBL_INT table for a shipment or return with inference.

      For receipt transactions, load data into the PAS_S_SHP_SERIAL_LBL_INT table for the top level serials being created during processing or with the non-serialized package used for receipt with inference.

   These tables display the column names and descriptions and the required fields for the PAS_S_SHP_SERIAL_ASSOC_INT, PAS_S_SHP_SERIAL_DOCS_INT, PAS_S_SHP_SERIAL_ADDR_INT, PAS_S_SHP_SERIAL_PRODS_INT, PAS_S_SHP_SERIAL_LBL_INT, and PAS_S_SHP_SERIAL_INT tables:

   Note: For shipment and return transactions, Shipment Date is not retained if it is sent into this service.

   PAS_S_SHP_SERIAL_ASSOC_INT Table

   Column Name	Column Description	Required?
   TRANS_SERIAL_ASSOC_ID	Internal use only - unique identifier for each serial association.	Y
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique identifier for each transaction batch.	Y
   ITEM_SERIAL_NUMBER	A unique number assigned for identification which varies from its successor or predecessor by a fixed discrete integer value.	Y
   SHIPPED_ITEM_FLAG	A flag used to indicate if the item was shipped on the sales order line.	Y (Default=0)
   SALEABLE_UNIT_FLAG	On if this serial number is for a saleable unit.	Y
   SERIAL_TYPE	Unique identifier of the serial type.	N
   SERIAL_IDENTIFICATION	Serial Identification to define hierarchy.	N
   MANUFACTURED_DATE	The date the product was manufactured.	N
   SERIAL_USAGE	A code used to categorize serials (ex. samples, etc.).	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   ATTRIBUTE_CHAR1	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR2	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR3	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR4	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR5	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR6	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR7	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR8	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR9	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR10	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_NUMBER1	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_NUMBER2	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_NUMBER3	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_NUMBER4	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_NUMBER5	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP1	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP2	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP3	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP4	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP5	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)
   TRANSACTION_PRODUCT_ID	Internal use only - unique identifier for each serial association.	Y
   PARENT_SERIAL_IDENTIFICATION	Parent Serial Identification used to define hierarchy.	N

   PAS_S_SHP_SERIAL_DOCS_INT Table

   Column Name	Column Description	Required?
   TRANSACTION_DOC_ID	Internal use only - unique ID for each transaction document.	Y
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique ID for each transaction batch.	Y
   SEGMENT1	The document organization of the reference document.	N
   SEGMENT2	The document number of the reference document.	N
   SEGMENT3	The document type of the reference document.	N
   SEGMENT4	The document line number of the reference document.	N
   SEGMENT5	Segment 5 of the reference document.	N
   SEGMENT6	Segment 6 of the reference document.	N
   SEGMENT7	Segment 7 of the reference document.	N
   SEGMENT8	Segment 8 of the reference document.	N
   SEGMENT9	Segment 9 of the reference document.	N
   SEGMENT10	Segment 10 of the reference document.	N
   PRIMARY_FLAG	A flag used to indicate that this document is the primary document.	Y
   PEDIGREE_DOCUMENT_TYPE	Examples of possible values include INVOICE, SHIPPINGNUMBER, and PURCHASEORDER depending on the setup.	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)

   PAS_S_SHP_SERIAL_PRODS_INT Table

   Column Name	Column Description	Required?
   TRANSACTION_PRODUCT_ID	Internal use only - unique ID for each transaction product.	Y
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique ID for each transaction batch.	Y
   PRODUCT_CODE	The unformatted product class identifier for the pharmaceutical product (e.g., the NDC number).	N
   PRODUCT_CODE_TYPE	The type of product code (e.g., NDC442, NDC532, NDC541, NDC542, GTIN).	N
   PRODUCT_CODE_VARIANT	A unique key value used in case Produce Code and Product Code Type do not uniquely identify the product.	N
   PRODUCT_UOM	The unit of measure of the product.	N
   LOT_NUMBER	Identification number assigned to a particular quantity or lot of material from a single manufacturer.	N
   PARENT_LOT	The identification number assigned to the parent of this lot.	N
   LOT_LOCATION	The location of the lot number.	N
   LOT_SOURCE	The source system from which this lot is derived.	N
   ORIGINATION_DATE	The date the lot was created or originated.	N
   EXPIRATION_DATE	The date the lot expires.	N
   BEST_BY_DATE	The date on or before which the lot is best used.	N
   SELL_BY_DATE	The date the product from this lot should be sold.	N
   RETEST_DATE	The date the quality of this lot has been retested.	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)
   ITEM_NUMBER	The item number from the transactional system.	N
   ITEM_SOURCE	Transactional System that the ITEM_NUMBER is from.	N
   ITEM_IDENTIFIER	Unique Item Identifier on Integration Source.	N
   ITEM_UOM	The unit of measure on the item number as it exists in the transactional system.	N

   PAS_S_SHP_SERIAL_LBL_INT Table

   Column Name	Column Description	Required?
   TRANSACTION_LABEL_ID	Internal use only - unique ID for each transaction/identifier.	Y
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique ID for each transaction batch.	Y
   LABEL	The primary or alternate identifier of a package.	Y
   LABEL_TYPE	The type of identifier on the package (e.g. Serial Number, LPN, etc.).	Y
   SHIPPED_ITEM_FLAG	Flag used to indicate if this item was shipped on the sales order line.	Y
   PACKAGE_UNIT	The unit of packaging for this package (e.g. Box, Case, Pallet. etc.).	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)
   REUSABLE_FLAG	Flag to indicate if the package is a reusable package.	Y
   PARENT_LABEL	The parent primary or alternate identifier of a package.	N
   PARENT_LABEL_TYPE	The type of identifier on the parent package (e.g. Serial Number, LPN, etc.).	N
   LABEL_PRINT_FORMAT	Format the label will be printed in.	N

   PAS_S_SHP_SERIAL_ADDR_INT Table

   Column Name	Column Description	Required?
   TRANSACTION_ADDRESS_ID	Internal use only - unique ID for each transaction/identifier.	Y
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique ID for each transaction batch.	Y
   ADDRESS_CODE	A code used to identify if the address is for a destination or source.	Y
   ADDRESS_TYPE	A code used to identify the type of address (i.e. Business, Shipping, etc.).	Y
   NAME	The name associated with the address.	Y
   STREET1	Used to hold a portion of the street address.	N
   STREET2	Used to hold a portion of the street address.	N
   STREET3	Used to hold a portion of the street address.	N
   STREET4	Used to hold a portion of the street address.	N
   CITY	The city of the transaction address.	N
   STATE_OR_REGION	The state or region of the transaction address.	N
   POSTAL_CODE	The postal code of the transaction address.	N
   COUNTRY	The country of the transaction address.	N
   CONTACT_NAME	The name of the contact for this address.	N
   CONTACT_EMAIL	The e-mail address for the contact of this address.	N
   CONTACT_PHONE	The phone number for the contact of this address	N
   GLOBAL_LOCATION_NUMBER	The Global Location Number (GLN) is a thirteen digit number used to identify parties and physical locations. This is in the EPC Tag URI format.	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)

   PAS_S_SHP_SERIAL_INT Table

   Column Name	Column Description	Required?
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique ID for each transaction batch.	Y
   TRANSACTION_TYPE	The type of transaction (e.g. Shipment, Receipt, Return, etc.).	Y
   TRANS_DATE	The date the transaction occurred.	Y
   TRANSACTION_SOURCE_NAME	Transaction WHO Field.	Y
   TRANS_LOCATION	The location that created the transaction.	N
   PRODUCT_CODE	The unformatted product class identifier for the pharmaceutical product (e.g., the NDC number).	N
   PRODUCT_CODE_TYPE	The type of product code (e.g., NDC442, NDC532, NDC541, NDC542, GTIN).	N
   PRODUCT_CODE_VARIANT	A unique key value used in case Produce Code and Product Code Type do not uniquely identify the product.	N
   PRODUCT_UOM	The unit of measure of the product.	N
   LOT_NUMBER	Identification number assigned to a particular quantity or lot of material from a single manufacturer.	N
   LOT_SOURCE	The source system from which this lot is derived.	N
   LOT_LOCATION	The location of the lot number.	N
   PARENT_LOT	The identification number assigned to the parent of this lot.	N
   INTERNAL_TRANSACTION_FLAG	On when the destination of the transaction is another location within the OPSM system.	Y
   TRANSACTION_QUANTITY	The quantity of serials related to this transaction.	N
   TRANSACTION_UNIT_OF_MEASURE	The unit of measure of the transaction (ie. BOX, CASE, etc.).	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   EVENT_CREATED_SOURCE_NAME	Event Who column: indicates the source system that created the row.	N
   EVENT_CREATED_BY	Event Who column: indicates the user who created the row.	N
   EVENT_CREATED_DATE	Event Who column: indicates the date and time of the creation of the row.	N
   EVENT_UPDATED_SOURCE_NAME	Event Who column: indicates the source system that updated the row.	N
   EVENT_UPDATED_BY	Event Who column: indicates the user who updated the row.	N
   EVENT_UPDATED_DATE	Event Who column: indicates the date and time of the update of the row.	N
   ATTRIBUTE_CATEGORY	Descriptive Flexfield: structure definition of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR1	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR2	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR3	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR4	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR5	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR6	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR7	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR8	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR9	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR10	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR11	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR12	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR13	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR14	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR15	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_NUMBER1	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_NUMBER2	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_NUMBER3	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_NUMBER4	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_NUMBER5	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP1	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP2	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP3	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP4	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP5	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)
   ITEM_NUMBER	The item number from the transactional system.	N
   ITEM_SOURCE	Transactional System that the ITEM_NUMBER is from.	N
   ITEM_UOM	The unit of measure of the item number as it exists in the transactional system.	N

2. The Transaction Interface Batch ID must be generated by the user. The Transaction Interface Batch ID must associate all records within these six tables for one transaction.

3. In addition to the Transaction Interface Batch ID, the user must also supply the following IDs within the six tables listed above: TRANS_SERIAL_ASSOC_ID, TRANSACTION_LABEL_ID, TRANSACTION_DOC_ID, TRANSACTION_ADDRESS_ID, TRANSACTION_PRODUCT_ID, and TRANSACTION_INTERFACE_BATCH_ID. These IDs must uniquely identify each row within the table.

4. When executing the PL/SQL service, the Transaction Interface Batch ID and Transaction Type are required as input into the service. OPSM searches for this Transaction Interface Batch ID within the PAS_S_SHP_SERIAL_INT, PAS_S_SHP_SERIAL_ASSOC_INT, PAS_S_SHP_SERIAL_DOCS_INT, PAS_S_SHP_SERIAL_PRODS_INT, PAS_S_SHP_SERIAL_LBL_INT, and PAS_S_SHP_SERIAL_ADDR_INT tables. If no corresponding Transaction Interface Batch ID is found, an INVALID BATCH ID ERROR is issued and no further processing occurs.

5. After the service has been executed for a batch, if processing is successful, the records for the batch are removed from the PAS_S_SHP_SERIAL_INT, PAS_S_SHP_SERIAL_ASSOC_INT, PAS_S_SHP_SERIAL_DOCS_INT, PAS_S_SHP_SERIAL_PRODS_INT, PAS_S_SHP_SERIAL_LBL_INT, and PAS_S_SHP_SERIAL_ADDR_INT tables, and the serials are saved in OPSM for use in subsequent processing. However, if errors are found during processing, the records for the batch remain in the PAS_S_SHP_SERIAL_INT, PAS_S_SHP_SERIAL_ASSOC_INT, PAS_S_SHP_SERIAL_DOCS_INT, PAS_S_SHP_SERIAL_PRODS_INT, PAS_S_SHP_SERIAL_LBL_INT, and PAS_S_SHP_SERIAL_ADDR_INT tables, and the ERROR_STATUS_FLAG within the tables is updated to a value of "1" for all records with an error. Additionally, error messages are written to the PAS_S_SHP_SERIAL_ERRORS_INT table. After the errors have been resolved, the ERROR_STATUS_FLAG must be updated to a value of "0" for all rows, or the processing will fail with an error stating that there is an ERROR STATUS FLAG error. No further processing is done until the error status flag has the correct value.

   This table displays the column names and descriptions for the PAS_S_SHP_SERIAL_ERRORS_INT table:

   PAS_S_SHP_SERIAL_ERRORS_INT Table

   Column Name	Column Description
   TRANSACTION_ERROR_ID	Internal use only - unique ID for each interface transaction error.
   OBJECT_VERSION_NUMBER	Version number for error record.
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique ID for each transaction batch.
   TRANSACTION_ERROR	A code representing an error, warning, or informational message related to the transaction record.
   ERROR_STATUS	The status of the error (i.e. Detected, Cleared, etc.).
   ERROR_CODE	Is the error code.
   ERROR_SEVERITY_LEVEL	A code indicating the severity level of the transaction error message. (i.e. 1=ERRORS, 2=WARNING, 3=MESSAGE).
   ERROR_SUBVALUE1	Substitution value used as part of the displayed message.
   ERROR_SUBVALUE2	Substitution value used as part of the displayed message.
   ERROR_SUBVALUE3	Substitution value used as part of the displayed message.
   ERROR_SUBVALUE4	Substitution value used as part of the displayed message.
   ERROR_SUBVALUE5	Substitution value used as part of the displayed message.
   DOMAIN	Used to enable a hosted PAS system.
   CREATED_BY	Who column: indicates the user who created the row.
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.
   ERROR_SUBVALUE6	Substitution value used as part of the displayed message.
   ERROR_SUBVALUE7	Substitution value used as part of the displayed message.
   ERROR_SUBVALUE8	Substitution value used as part of the displayed message.
   ERROR_SUBVALUE9	Substitution value used as part of the displayed message.
   ERROR_SUBVALUE10	Substitution value used as part of the displayed message.
   ERROR_MESSAGE	Is the complete error message.

6. If processing is successful after correction of the errors, the batch records in the PAS_S_SHP_SERIAL_INT, PAS_S_SHP_SERIAL_ASSOC_INT, PAS_S_SHP_SERIAL_DOCS_INT, PAS_S_SHP_SERIAL_PRODS_INT, PAS_S_SHP_SERIAL_LBL_INT, and PAS_S_SHP_SERIAL_ADDR_INT tables as well as the error messages in the PAS_S_SHP_SERIAL_ERRORS_INT table are removed.

7. If the "Allow Multiple Receipts" transaction flag is on, serials can be received multiple times even if they are already in a state of RECEIVED. However, the product associated to the serials that are being received multiple times cannot have the flag "Create Serials on Receipt" turned on and the serials cannot be aggregated, they must be loose serials.

processImportAndPackSQL and processImportAndPackSQLAsync Operations

These are PL/SQL services in OPSM that import, create, and pack serial numbers within OPSM. The processImportAndPackSQL and processImportAndPackSQLAsync operations create serial numbers as defined in the request and then pack them into a packaging hierarchy. Serials are created in a reserved or commissioned status depending on the data included in the request.

Security

Since the data is being loaded directly into the PAS_S_IMP_SERIAL_ASSOC_INT, PAS_S_IMP_SERIAL_DOCS_INT, PAS_S_IMP_SERIAL_PRODS_INT, PAS_S_IMP_SERIAL_INT, and PAS_S_PACK_INTERFACE tables, OPSM's functional security is not applicable. It is the responsibility of the implementer to ensure that proper access to the database is validated.

Key Logic

• The Batch ID is used to identify the batch to be processed for import and pack.

• All records associated to the Import and Pack transaction must share the same Batch ID.

Uniqueness Check

To validate uniqueness during packing for seeded serial types (SGTIN-96, STGIN-198, and USN-1), this endpoint uses serial type plus serial number. To validate uniqueness for custom serial types, this endpoint uses product code plus serial number. The following combinations of product information can be used:

• Product Code Only OR

• Product Code, Product Code Type, Product Code Variant, and Product UOM OR

• Product Detail Id

Note: If Product Detail Id is entered the rest of the data is ignored. If the Product Detail Id is provided and it is invalid or it does not match the product detail associated to the serial number provided, the processing fails. If Product information is provided and it is invalid or it does not match the product detail associated to the serial number provided, the processing fails.

Exceptions

• Required parameters not passed: An error of this type is issued if one or more of the required fields is not included.

• Invalid lookup value: An error of this type is issued if the value being sent in does not match an existing lookup code in the associated lookup type.

Restrictions

An alternate Label and LabelType cannot be used to identify the package to create a packaging hierarchy.

Procedure

To Use these PL/SQL Services

1. Load data into these tables:

   • PAS_S_IMP_SERIAL_ASSOC_INT

   • PAS_S_IMP_SERIAL_DOCS_INT

   • PAS_S_IMP_SERIAL_PRODS_INT

   • PAS_S_IMP_SERIAL_INT

   • PAS_S_PACK_INTERFACE

   These tables display the column names and descriptions and the required fields for the PAS_S_IMP_SERIAL_ASSOC_INT, PAS_S_IMP_SERIAL_DOCS_INT, PAS_S_IMP_SERIAL_PRODS_INT, PAS_S_IMP_SERIAL_INT, and PAS_S_PACK_INTERFACE tables:

   Note: At least one record must be provided for each of the tables listed below. For example, if you are importing serials but do not wish to associate a product to those serials during import processing, you must still insert a dummy record in the PAS_S_IMP_SERIAL_PRODS_INT table which includes all of the required fields for that table.

   PAS_S_IMP_SERIAL_ASSOC_INT Table

   Column Name	Column Description	Required?
   TRANS_SERIAL_ASSOC_ID	Internal use only - unique identifier for each serial association.	Y
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique identifier for each transaction batch.	Y
   ITEM_SERIAL_NUMBER	A unique number assigned for identification which varies from its successor or predecessor by a fixed discrete integer value.	Y
   SHIPPED_ITEM_FLAG	A flag used to indicate if the item was shipped on the sales order line.	Y
   SALEABLE_UNIT_FLAG	On if this serial number is for a saleable unit.	Y
   SERIAL_TYPE	Unique identifier of the serial type.	N
   PARENT_SERIAL_IDENTIFICATION	Parent Serial Identification used to define hierarchy.	N
   MANUFACTURED_DATE	The date the product was manufactured.	N
   SERIAL_USAGE	A code used to categorize serials (ex. samples, etc.).	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   ATTRIBUTE_CHAR1	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR2	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR3	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR4	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR5	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR6	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR7	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR8	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR9	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_CHAR10	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_NUMBER1	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_NUMBER2	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_NUMBER3	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_NUMBER4	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_NUMBER5	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP1	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP2	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP3	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP4	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP5	Descriptive Flexfield: segment of user descriptive flexfield.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)
   TRANSACTION_PRODUCT_ID	Internal use only - unique identifier for each serial association.	Y

   PAS_S_IMP_SERIAL_DOCS_INT Table

   Column Name	Column Description	Required?
   TRANSACTION_DOC_ID	Internal use only - unique ID for each transaction document.	Y
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique ID for each transaction batch.	Y
   SEGMENT1	The document organization of the reference document.	N
   SEGMENT2	The document number of the reference document.	N
   SEGMENT3	The document type of the reference document.	N
   SEGMENT4	The document line number of the reference document.	N
   SEGMENT5	Segment 5 of the reference document.	N
   SEGMENT6	Segment 6 of the reference document.	N
   SEGMENT7	Segment 7 of the reference document.	N
   SEGMENT8	Segment 8 of the reference document.	N
   SEGMENT9	Segment 9 of the reference document.	N
   SEGMENT10	Segment 10 of the reference document.	N
   PRIMARY_FLAG	A flag used to indicate that this document is the primary document.	Y
   PEDIGREE_DOCUMENT_TYPE	Examples of possible values include INVOICE, SHIPPINGNUMBER, and PURCHASEORDER depending on the setup.	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)

   PAS_S_IMP_SERIAL_PRODS_INT Table

   Column Name	Column Description	Required?
   TRANSACTION_PRODUCT_ID	Internal use only - unique ID for each transaction product.	Y
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique ID for each transaction batch.	Y
   PRODUCT_CODE	The unformatted product class identifier for the pharmaceutical product (e.g., the NDC number).	N
   PRODUCT_CODE_TYPE	The type of product code (e.g., NDC442, NDC532, NDC541, NDC542, GTIN).	N
   PRODUCT_CODE_VARIANT	A unique key value used in case Produce Code and Product Code Type do not uniquely identify the product.	N
   PRODUCT_UOM	The unit of measure of the product.	N
   ITEM_NUMBER	The item number from the transactional system.	N
   ITEM_SOURCE	Transactional System that the ITEM_NUMBER is from.	N
   ITEM_IDENTIFIER	Unique Item Identifier on Integration Source.	N
   ITEM_UOM	The unit of measure of the item number as it exists in the transactional system.	N
   LOT_NUMBER	Identification number assigned to a particular quantity or lot of material from a single manufacturer.	N
   PARENT_LOT	The identification number assigned to the parent of this lot.	N
   LOT_LOCATION	The location of the lot number.	N
   LOT_SOURCE	The source system from which this lot is derived.	N
   ORIGINATION_DATE	The date the lot was created or originated.	N
   EXPIRATION_DATE	The date the lot expires.	N
   BEST_BY_DATE	The date on or before which the lot is best used.	N
   SELL_BY_DATE	The date the product from this lot should be sold.	N
   RETEST_DATE	The date the quality of this lot has been retested.	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)

   PAS_S_IMP_SERIAL_INT Table

   Column Name	Column Description	Required?
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique ID for each transaction batch.	Y
   TRANSACTION_TYPE	The type of transaction (e.g. Shipment, Return, etc.).	Y
   TRANS_DATE	The date the transaction occurred.	Y
   TRANSACTION_SOURCE_NAME	Transaction WHO Field.	Y
   TRANS_LOCATION	The location that created the transaction.	N
   ITEM_NUMBER	The item number from the transactional system.	N
   ITEM_SOURCE	Transactional System that the ITEM_NUMBER is from.	N
   ITEM_UOM	The unit of measure of the item number as it exists in the transactional system.	N
   PRODUCT_CODE	The unformatted product class identifier for the pharmaceutical product (e.g., the NDC number).	N
   PRODUCT_CODE_TYPE	The type of product code (e.g., NDC442, NDC532, NDC541, NDC542, GTIN).	N
   PRODUCT_CODE_VARIANT	A unique key value used in case Produce Code and Product Code Type do not uniquely identify the product.	N
   PRODUCT_UOM	The unit of measure of the product.	N
   LOT_NUMBER	Identification number assigned to a particular quantity or lot of material from a single manufacturer.	N
   LOT_SOURCE	The source system from which this lot is derived.	N
   LOT_LOCATION	The location of the lot number.	N
   PARENT_LOT	The identification number assigned to the parent of this lot.	N
   IMPORT_SERIALS_FLAG	When on, import the serial, otherwise update the serial.	Y
   INTERNAL_TRANSACTION_FLAG	On when the destination of the transaction is another location within the OPSM system.	Y
   TRANSACTION_QUANTITY	The quantity of serials related to this transaction.	N
   TRANSACTION_UNIT_OF_MEASURE	The unit of measure of the transaction (ie. BOX, CASE, etc.).	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	Y
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	Y
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N
   EVENT_CREATED_SOURCE_NAME	Event Who column: indicates the source system that created the row.	N
   EVENT_CREATED_BY	Event Who column: indicates the user who created the row.	N
   EVENT_CREATED_DATE	Event Who column: indicates the date and time of the creation of the row.	N
   EVENT_UPDATED_SOURCE_NAME	Event Who column: indicates the source system that updated the row.	N
   EVENT_UPDATED_BY	Event Who column: indicates the user who updated the row.	N
   EVENT_UPDATED_DATE	Event Who column: indicates the date and time of the update of the row.	N
   ATTRIBUTE_CATEGORY	Descriptive Flexfield: structure definition of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR1	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR2	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR3	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR4	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR5	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR6	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR7	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR8	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR9	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR10	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR11	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR12	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR13	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR14	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_CHAR15	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_NUMBER1	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_NUMBER2	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_NUMBER3	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_NUMBER4	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_NUMBER5	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP1	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP2	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP3	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP4	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ATTRIBUTE_TIMESTAMP5	Descriptive Flexfield: segment of the user descriptive flexfield.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table.	Y (Default=0)

   PAS_S_PACK_INTERFACE Table

   Column Name	Column Description	Required?
   PACKAGE_INTERFACE_ID	Uniquely identifies each row in the PAS_S_PACK_INTERFACE table.	Y
   PACKAGE_INTERFACE_BATCH_ID	Associates a batch number to the records in the table.	Y
   PARENT_LABEL	The primary identifier of a parent package.	Y
   PARENT_LABEL_TYPE	The primary identifier type of a parent package.	Y
   PARENT_PRODUCT_CODE	The unformatted product class identifier for the pharmaceutical product (e.g., the NDC number) for the parent package.	N
   PARENT_PRODUCT_CODE_TYPE	The type of product code (e.g., NDC442, NDC532, NDC541, NDC542, GTIN) for the parent package.	N
   PARENT_PRODUCT_CODE_VARIANT	A unique key value used in case Product Code and Product Code Type do not uniquely identify the product for the parent package.	N
   PARENT_PRODUCT_UOM	The unit of measure of the product for the parent package.	N
   PARENT_PRODUCT_DETAIL_ID	Internal use only - unique ID for each Product Detail for the parent package.	N
   REUSABLE_FLAG	Flag to indicate if the package is a reusable package.	N
   PACKAGING_UNIT	The unit of packaging for this package (e.g. Box, Case, Pallet. etc.).	N
   LABEL_PRINT_FORMAT	Format the label will be printed in.	N
   CHILD_LABEL	The primary identifier of a child package.	Y
   CHILD_LABEL_TYPE	The primary identifier type of a child package.	Y
   CHILD_PRODUCT_CODE	The unformatted product class identifier for the pharmaceutical product (e.g., the NDC number) for the child package.	N
   CHILD_PRODUCT_CODE_TYPE	The type of product code (e.g., NDC442, NDC532, NDC541, NDC542, GTIN) for the child package.	N
   CHILD_PRODUCT_CODE_VARIANT	A unique key value used in case Product Code and Product Code Type do not uniquely identify the product for the child package.	N
   CHILD_PRODUCT_UOM	The unit of measure of the product for the child package.	N
   CHILD_PRODUCT_DETAIL_ID	Internal use only - unique ID for each Product Detail for the child package.	N
   ERROR_STATUS_FLAG	Flag to identify error records in the interface table	N
   CREATED_BY	Who column: indicates the user who created the row.	Y
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.	N
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.	Y
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.	N
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.	N

2. The Transaction Interface Batch ID and Package Interface Batch ID must be generated by the user. The Transaction Interface Batch ID and Package Interface Batch ID must be equal so that they associate all records within these five tables into one transaction.

3. In addition to the Transaction Interface Batch ID and Package Interface Batch ID, the user must also supply the following IDs within the tables listed above: TRANS_SERIAL_ASSOC_ID, TRANSACTION_DOC_ID, TRANSACTION_PRODUCT_ID, TRANSACTION_INTERFACE_BATCH_ID, and PACKAGE_INTERFACE_ID. These IDs must uniquely identify each row within the tables.

4. After the service has been executed for a batch, if processing is successful, the records for the batch are removed from the PAS_S_IMP_SERIAL_ASSOC_INT, PAS_S_IMP_SERIAL_DOCS_INT, PAS_S_IMP_SERIAL_PRODS_INT, PAS_S_IMP_SERIAL_INT and PAS_S_PACK_INTERFACE tables, and the serials and packaging hierarchy are saved in OPSM for use in subsequent processing. However, if errors are found during processing, the records for the batch remain in the PAS_S_IMP_SERIAL_ASSOC_INT, PAS_S_IMP_SERIAL_DOCS_INT, PAS_S_IMP_SERIAL_PRODS_INT, PAS_S_IMP_SERIAL_INT and PAS_S_PACK_INTERFACE tables, and the ERROR_STATUS_FLAG within the tables is updated to a value of "1" for all records with an error. Additionally, error messages are written to the PAS_S_IMP_SERIAL_ERRORS_INT and PAS_S_PACK_INTERFACE_ERR tables. After the errors have been resolved, the ERROR_STATUS_FLAG must be updated to a value of "0" for all rows, or the processing will fail with an error stating that there is an ERROR STATUS FLAG error. No further processing is done until the error status flag has the correct value.

   These tables display the column names and descriptions for the PAS_S_IMP_SERIAL_ERRORS_INT and PAS_S_PACK_INTERFACE_ERR tables:

   PAS_S_IMP_SERIAL_ERRORS_INT Table

   Column Name	Column Description
   TRANSACTION_ERROR_ID	Internal use only - unique ID for each interface transaction error.
   TRANSACTION_INTERFACE_BATCH_ID	Internal use only - unique ID for each transaction batch.
   ERROR_CODE	Is the error code.
   ERROR_SEVERITY_LEVEL	A code indicating the severity level of the transaction error message. (i.e. 1=ERRORS, 2=WARNING, 3=MESSAGE).
   CREATED_BY	Who column: indicates the user who created the row.
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.
   ERROR_MESSAGE	Is the complete error message.

   PAS_S_PACK_INTERFACE_ERR_TABLE Table

   Column Name	Column Description
   PACKAGE_INTERFACE_ID	Indicates the package interface ID from the PAS_S_PACK_INTERFACE table, for which an error occurred.
   PACKAGE_INTERFACE_BATCH_ID	Indicates the package interface batch ID from the PAS_S_PACK_INTERFACE table, for which an error occurred.
   ERROR_CODE	Is the error code of the error message that was issued.
   ERROR_MESSAGE	Is the complete error message that was issued.
   CREATED_BY	Who column: indicates the user who created the row.
   CREATION_DATE	Who column: indicates the date and time of the creation of the row.
   LAST_UPDATED_BY	Who column: indicates the user who last updated the row.
   LAST_UPDATE_DATE	Who column: indicates the date and time of the last update of the row.
   LAST_UPDATE_LOGIN	Who column: indicates the session login associated to the user who last updated the row.

5. If processing is successful after correction of the errors, the batch records in the PAS_S_IMP_SERIAL_ASSOC_INT, PAS_S_IMP_SERIAL_DOCS_INT, PAS_S_IMP_SERIAL_PRODS_INT, PAS_S_IMP_SERIAL_INT and PAS_S_PACK_INTERFACE tables as well as the error messages in the PAS_S_IMP_SERIAL_ERRORS_INT and PAS_S_PACK_INTERFACE_ERR tables are removed.

CaptureService (Inbound)

The CaptureService endpoint is an inbound business service that is part of the interoperability solution of the OPSM system. This endpoint enables EPCIS events to get captured and stored in the events repository. Captured events can be queried by the EPCIS Query endpoint. The CaptureService endpoint accepts the EPCISDocument form to store the EPCIS events in the repository.

Operation Type	Operation	Description
Web Service	capture	The capture operation is used to capture EPCIS events in the OPSM system.

Web Services

capture Operation

This operation is used to capture EPCIS events in the OPSM system.

Key Logic

• EPCIS event gets validated against the EPCIS xsd schema if the EPCIS event is sent in the web service operation request.

• Each event provided in the web service request is split into individual xmls corresponding to the event.

• Individual xmls corresponding to the event get stored in the OPSM system.

• EPC codes corresponding to the individual xmls get stored in the OPSM system.

Required Input

• SchemaVersion

• CreationDate

• HeaderVersion (is mandatory if and only if StandardBusinessDocumentHeader is provided in the input event xml )

• EPCIS event

  Out of the five EPCIS events at least one event needs to be provided. EPCIS events are:

  • ObjectEvent

  • TransactionEvent

  • AggregationEvent

  • QuantityEvent (deprecated in EPCIS 1.1)

  • Transformation Event

  Note: The Quantity Event is deprecated to conform to EPCIS 1.1 standards.

• EventTime is required for all EPCIS events.

• EventTimeZoneOffset is required for all EPCIS events.

• For Object Event these fields are required:

  • epcList (it must have zero or more occurrences of EPC).

  • action

• For TransactionEvent these fields are required:

  • bizTransactionList (with one or more occurrences of bizTransaction).

  • epcList (with zero or more occurrences of EPC).

  • action

• For AggregationEvent these fields are required:

  • childEPCs

  • action

• For QuantityEvent (deprecated in EPCIS 1.1) these fields are required:

  • epcClass

  • quantity

• For TransformationEvent these fields are required:

  • At least one input (inputEPCList/inputQuantityList) AND at least one output (outputEPCList/outputQuantityList) is required if transformation ID is omitted

  • If transformation ID is included, then at least one input (inputEPCList/inputQuantityList) OR at least one output(outputEPCList/outputQuantityList) is required

Exceptions

• Required parameters are not passed.

• If web service request does not conform with the EPCIS Schema (this validation takes place only when VALIDATE_EPCIS_REPORT_FLAG value is Y in the system parameters table).

• First character in eventTimeZoneOffset is other than + or -.

• EventTimeZoneOffset is not in the range of -14:00 hrs to +14:00 hrs.

• If event type is AggregationEvent and action specified in input is either ADD or DELETE and parentId in input is null or empty.

• If event type is TransactionEvent and action is either ADD or OBSERVE and epcList has zero occurrences.

• If event type is AggregationEvent and action is OBSERVE and childEpc has zero occurrences.

• If event type is AggregationEvent and action is ADD and parentId is null or childEpc has zero occurrences.

• If event type is QuantityEvent and the Quantity provided is zero or blank.

• If event type is Transformation Event and transformation ID and epc Lists are blank.

• If Ignore Pure Identity flag is N then

  • If the parentID sent in does not conform to the Pure Identity format, an exception is thrown.

  • If the epcClass sent in does not conform to the Pure Identity format, an exception is thrown.

  • If the childEpcs sent in does not conform to the Pure Identity format, an exception is thrown.

  • If the epcList sent in does not conform to the Pure Identity format, an exception is thrown.

• If pure component 1 in epc code is other than * and contains non numeric value.

• If pure component 2 in epc code is other than * and contains non numeric value.

• If pure component 3 in epc code is other than * and contains non numeric value.

• If both schedule and trigger are empty for subscription.

capture

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
schemaVersion	Number	Yes	The version of the schema corresponding to which the instance conforms.
creationDate	DateTime	Yes	The date the message was created. Used for auditing and logging.
EPCISHeader	EPCISHeader	No	The XML binding for the Core Event Types data definition module that includes an optional EPCISHeader element, which may be used by industry groups to incorporate additional information required for processing within that industry. The core schema includes a “Standard Business Document Header” (SBDH) as defined in [SBDH] as a required component of the EPCISHeader element. It also contains extensions of EPCISHeader.
EPCISBody	EPCISBody	Yes	The XML binding for the Core Event Types data definition modules that includes EPCISBody a mandatory element. It contains eventList and extensions.

StandardBusinessDocumentHeader

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
HeaderVersion	String	Yes (If EPCIS Header Is provided)	Specifies the version number of the standard business document.
Sender	Partner	Yes (If EPCIS Header Is provided)	Sender of the message, party representing the organization which created the standard business document.
Receiver	Partner	Yes (If EPCIS Header Is provided)	Receiver of the message, party representing the organization which receives the standard business document.
DocumentIdentification	DocumentIdentification	Yes (If EPCIS Header Is provided)	Identification information for the document.
Manifest	Manifest	No	Describes attachments.
BusinessScope	BusinessScope	No	The business scope provides a basis to determine which rules are applicable to the transaction involving the enclosed business documents.
Extension	EPCISDocumentExtensionType	No	Standard document business header supports extensibility so end users can address application needs or use case. User can give any elements under extensions.

Sender

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
Identifier	PartnerIdentification	Yes (If EPCIS Header is provided)	A unique identification key for the sending party.
Authority	String	No	Authority agency of the identification key.
ContactInformation	ContactInformation	No	Name of the contact person or department for the sending party. See ContactInformation.

Receiver

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
Identifier	PartnerIdentification	Yes (If EPCIS Header is provided)	A unique identification key for the receiving party.
Authority	String	No	Authority agency of the identification key.
ContactInformation	ContactInformation	No	Name of the contact person or department for the receiving party. See ContactInformation.

ContactInformation

Name	Type	Mandatory	Description
Contact	String	Yes	Name of contact person or department.
EmailAddress	String	No	Email address of the contact.
FaxNumber	String	No	Fax number of the contact.
TelephoneNumber	String	No	Telephone number of the contact.
ContactTypeIdentifier	String	No	Role of the identifier. For example, SELLER.

DocumentIdentification

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
Standard	String	Yes	The name of the document standard contained in the payload.
TypeVersion	String	Yes	Version information of the document included in the payload of SBDH. This is the "complete" version of the document itself and is different than the "HeaderVersion".
InstanceIdentifier	String	Yes	Description that contains reference information which uniquely identifies this instance of the Standard Business Document (SBD) between the "Sender" and the "Receiver". This identifier identifies this document as being distinct from others.
Type	String	Yes	This element identifies the type of the document. If there is a business need to send multiple types of documents, then the multiple types MUST be business documents related to each other and having the same version identifier. Example; invoice, debitCreditAdvice, tradeItemDocument.
MultipleType	Boolean	Yes	Flag to indicate that there is more than one type of business document in the payload of the SBDH.
CreationDateAndTime	String	Yes	Date and time of the SBDH document creation.

Manifest

Name	Type	Mandatory	Description
NumberOfItems	Integer	Yes	Number of attachments in document.
ManifestItem	ManifestItem	Yes	Specifies information about the attached item in a document. See ManifestItem.

ManifestItem

Name	Type	Mandatory	Description
MimeTypeQualifierCode	MimeTypeQualifierCode	Yes	MIME type of attached item.
UniformResourceIdentifier	anyURI	Yes	URL for the attached item.
Description	String	No	Description of the attached item.
Language	String	No	Language of the attached item.

BusinessScope

Name	Type	Mandatory	Description
Scope	Scope	No	Defines the scope of the business rule to the transactions involving the enclosed business documents. See Scope.

Scope

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
Type	String	No	This element identifies the type of the scope.
InstanceIdentifier	String	No	Description that contains reference information which uniquely identifies the scope.
Identifier	String	No	A unique identification key for the scope.
ScopeInformation	Scope	No	This is an abstract element with a substitution group. The element will be substituted by any one of the other elements that have the same substitution group.

EPCISBody

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
EventList	EventListType	No	Defines the list of events in a document. Events can be objectEvent, transactionEvent, aggregationEvent, or quantityEvent. All events are a subclass of EPCISEvent.
extension	EPCISEventListExtensionType	No	EPCISBody supports extensibility so end users can address application needs or use case. User can give any elements under extensions.

EventList

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
ObjectEvent	ObjectEventType	No	ObjectEvent captures information about an event pertaining to one or more physical objects identified by EPCs.
AggregationEvent	AggregationEventType	No	The event type AggregationEvent describes events that apply to objects that have been physically aggregated to one another. In such an event, there is a set of “contained” objects that have been aggregated within a “containing” entity that's meant to identify the physical aggregation itself.
QuantityEvent	QuantityEventType	No	A QuantityEvent captures an event that takes place with respect to a specified quantity of an object class.
TransactionEvent	TransactionEventType	No	The event type TransactionEvent describes the association or disassociation of physical objects to one or more business transactions. While other event types have an optional bizTransactionList field that may be used to provide context for an event, the TransactionEvent is used to declare in an unequivocal way that certain EPCs have been associated or disassociated with one or more business transactions as part of the event.
TransformationEvent	TransformationEvent Type	No	The event type TransformationEvent represents an event in which input objects are fully or partially consumed and output objects are produced, such that any of the input objects may have contributed to all of the output objects.

ObjectEvent

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
eventTime	DateTime	Yes	The date and time at which the EPCIS Capturing Application asserts the event occurred.
recordTime	DateTime	No	The date and time at which this event was recorded by an EPCIS Repository. This field SHALL be ignored when an event is presented to the EPCIS Capture Interface, and SHALL be present when an event is retrieved through the EPCIS Query Interface.
eventTimeZoneOffset	String	Yes	The time zone offset in effect at the time and place the event occurred, expressed as an offset from UTC.
baseExtension	EPCISEventExtensionType	No	It specifies the base extension for ObjectEvent.
epcList	EPCListType	Yes	An unordered list of one or more EPCs naming the physical objects to which the event pertained. Each element of this list SHALL be a URI [RFC2396] denoting the unique identity for a physical object.
action	ActionType	Yes	The Action field of an ObjectEvent describes the events relationship to the lifecycle of the EPCs named in the event. Actions are ADD, DELETE, or OBSERVE.
bizStep	BusinessStepIDType	No	The business step of which this event was a part.
disposition	DispositionIDType	No	The business condition of the objects associated with the EPCs presumed to hold, until contradicted by a subsequent event.
readPoint	ReadPointType	No	The read point at which the event took place.
bizLocation	BusinessLocationType	No	The business location where the objects associated with the EPCs may be found, until contradicted by a subsequent event.
bizTransactionList	BusinessTransactionListType	No	An unordered list of business transactions that define the context of this event.
extension	ObjectEventExtensionType	No	Extensions for object events are defined under the extensions tag. The main purpose of extensions is to adapt to particular business situations for different organizations.

AggregationEvent

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
eventTime	DateTime	Yes	The date and time at which the EPCIS Capturing Application asserts the event occurred.
recordTime	DateTime	No	The date and time at which this event was recorded by an EPCIS Repository. This field SHALL be ignored when an event is presented to the EPCIS Capture Interface, and SHALL be present when an event is retrieved through the EPCIS Query Interface.
eventTimeZoneOffset	String	Yes	The time zone offset in effect at the time and place the event occurred, expressed as an offset from UTC.
baseExtension	EPCISEventExtensionType	No	It specifies the base extension for AggregationEvent.
parentId	ParentIDType	No	The identifier of the parent of the association.
childEPCs	EPCListType	Yes	An unordered list of the EPCs of the contained objects. Each element of the list SHALL be a URI [RFC2396] denoting the unique identity for a physical object.
action	ActionType	Yes	Action specifies how this event relates to the lifecycle of the aggregation named in this event. Actions are ADD, DELETE, or OBSERVE.
bizStep	BusinessStepIDType	No	The business step of which this event was a part.
disposition	DispositionIDType	No	The business condition of the objects associated with the EPCs presumed to hold, until contradicted by a subsequent event.
readPoint	ReadPointType	No	The read point at which the event took place.
bizLocation	BusinessLocationType	No	The business location where the objects associated with the EPCs may be found, until contradicted by a subsequent event.
bizTransactionList	BusinessTransactionListType	No	An unordered list of business transactions that define the context of this event.
extension	Aggregation EventExtensionType	No	Extensions for aggregation events are defined under the extensions tag. The main purpose of extensions is to adapt to particular business situations for different organizations.

QuantityEvent

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
eventTime	DateTime	Yes	The date and time at which the EPCIS Capturing Application asserts the event occurred.
recordTime	DateTime	No	The date and time at which this event was recorded by an EPCIS Repository. This field SHALL be ignored when an event is presented to the EPCIS Capture Interface, and SHALL be present when an event is retrieved through the EPCIS Query Interface.
eventTimeZoneOffset	String	Yes	The time zone offset in effect at the time and place the event occurred, expressed as an offset from UTC.
baseExtension	EPCISEventExtensionType	No	It specifies the base extension for QuantityEvent.
epcClass	EPCClassType	Yes	The identifier specifying the object class to which the event pertains.
quantity	Integer	Yes	The quantity of the object within the class described by this event.
bizStep	BusinessStepIDType	No	The business step of which this event was a part.
disposition	DispositionIDType	No	The business condition of the objects associated with the EPCs presumed to hold, until contradicted by a subsequent event.
readPoint	ReadPointType	No	The read point at which the event took place.
bizLocation	BusinessLocationType	No	The business location where the objects associated with the EPCs may be found, until contradicted by a subsequent event.
bizTransactionList	BusinessTransactionListType	No	An unordered list of business transactions that define the context of this event.
extension	QuantityEventExtensionType	No	Extensions for quantity events are defined under the extensions tag. The main purpose of extensions is to adapt to particular business situations for different organizations.

TransactionEvent

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
eventTime	DateTime	Yes	The date and time at which the EPCIS Capturing Application asserts the event occurred.
recordTime	DateTime	No	The date and time at which this event was recorded by an EPCIS Repository. This field SHALL be ignored when an event is presented to the EPCIS Capture Interface, and SHALL be present when an event is retrieved through the EPCIS Query Interface.
eventTimeZoneOffset	String	Yes	The time zone offset in effect at the time and place the event occurred, expressed as an offset from UTC.
baseExtension	EPCISEventExtensionType	No	It specifies the base extension for TransactionEvent.
bizTransactionList	BusinessTransactionListType	Yes	An unordered list of business transactions that define the context of this event.
parentId	ParentIDType	No	The identifier of the parent of the EPCs given in the epcList.
epcList	EPCListType	Yes	An unordered list of the EPCs of the objects associated with the business transaction. Each element of the list SHALL be a URI [RFC2396] denoting the unique identity for a physical object.
action	ActionType	Yes	Action specifies how this event relates to the lifecycle of the business transaction named in this event. Actions are ADD, DELETE, or OBSERVE.
bizStep	BusinessStepIDType	No	The business step of which this event was a part.
disposition	DispositionIDType	No	The business condition of the objects associated with the EPCs presumed to hold, until contradicted by a subsequent event.
readPoint	ReadPointType	No	The read point at which the event took place.
bizLocation	BusinessLocationType	No	The business location where the objects associated with the EPCs may be found, until contradicted by a subsequent event.
bizTransactionList	BusinessTransactionListType	No	An unordered list of business transactions that define the context of this event.
extension	TransactionEventExtensionType	No	Extensions for transaction events are defined under the extensions tag. The main purpose of extensions is to adapt to particular business situations for different organizations.

Transformation Event

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
eventTime	Time	Yes	The date and time at which the EPCIS Capturing Application asserts the event occurred.
recordTime	Time	No	The date and time at which this event was recorded by an EPCIS Repository.
eventTimeZoneOffset	String	Yes	The time zone offset in effect at the time and place the event occurred, expressed as an offset from UTC.
inputepcList	List<EPC>	No	An unordered list of one or more EPCs identifying (at the instance level) objects that were inputs to the transformation.
inputQuantityList	List<QuantityElement>	No	An unordered list of one or more QuantityElements identifying (at the class level) objects that were inputs to the transformation.
outputepcList	List<EPC>	No	An unordered list of one or more EPCs identifying (at the instance level) objects that were outputs to the transformation.
outputQuantityList	List<QuantityElement>	No	An unordered list of one or more QuantityElements identifying (at the class level) objects that were outputs to the transformation.
transformationID	TransformationID	No	A unique identifier that links this event to other TransformationEvents having an identical value of transformationID. When specified, all inputs to all events sharing the same value of the transformationID may contribute to all outputs of all events sharing that value of transformationID. If transformationID is omitted, then the inputs of this event may contribute to the outputs of this event, but the inputs and outputs of other events are not connected to this one.
bizStep	BusinessStepID	No	The business step of which this event was a part.
Disposition	DispositionID	No	The business state of the objects associated with the EPCs.
readPoint	readPointID	No	The read point at which the event took place ("'where the objects were at the time of the EPCIS event" ).
bizLocation	BusinessLocationID	No	The business location of the objects associated with the EPCs ("where the objects are following the EPCIS event").
bizTransactionList	Unordered list of zero or more BusinessTransaction instances	No	An unordered list of business transactions (eg: Purchase Order) that define the context of this event.
sourceList	List<Source>	No	An unordered list of Source elements that provide context about the originating endpoint of a business transfer of which this event is a part.
destinationList	List<Destination>	No	An unordered list of Destination elements that provide context about the terminating endpoint of a business transfer of which this event is a part.
Ilmd	ILMD	No	Instance/Lot Master Data that describes the objects created during this event. An example of ILMD is the expiration date of a perishable trade item. Unlike Master Data, this is not the same for all trade items having the same GTIN. Other examples of ILMD are date of manufacture and place of manufacture. It is expected to remain static over the life of an object. An ObjectEvent SHALL NOT contain ilmd if action is OBSERVE or DELETE.

bizLocation

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
id	BusinessLocationIDType	Yes	Indicates the location Id of the business location.
extension	BusinessLocationExtensionType	No	Business location supports extensibility so end users can address application needs or use case. User can give any elements under extensions.

readPoint

The description information in this table was taken from the GS1 EPC Global - EPC Information Services (EPCIS) Version 1.1 Specification Guide. For more information visit the GS1 EPC Global website at: http://www.gs1.org.

Name	Type	Mandatory	Description
id	ReadPointIDType	Yes	Read Point Id is a discretely recorded location that is meant to identify the most specific place at which an EPCIS event took place.
extension	ReadPointExtensionType	No	Read point supports extensibility so end users can address application needs or use case. User can give any elements under extensions.

WSDL

oracle.apps.pas.epc.service.capture.CaptureService.wsdl

For detailed descriptions of all input and output parameters, please refer to the CaptureService wsdl for this endpoint.

QueryService (Inbound)

The QueryService endpoint is an inbound business service that is part of the interoperability solution of the OPSM system. This endpoint enables an external system to fetch the events that are recorded in the OPSM system. In addition, the OPSM system can optionally use EPCIS policies to restrict a user on a particular EPC code.

Operation Type	Operation	Description
Web Service	getQueryNames	This operation returns all the queries that the QueryService endpoint supports.
Web Service	getVendorVersion	This operation returns the version implemented by the vendor.
Web Service	getStandardVersion	This operation returns the EPCIS implemented version.
Web Service	poll	This operation returns the events based on the parameters provided and the access of the user.
Web Service	getSubscriptionIDs	This operation returns a list of all subscriptionIDs currently subscribed to the specified named query.
Web Service	subscribe	This operation registers a subscriber for a previously defined query having the specified name. The params argument provides the values to be used for any named parameters defined by the query. The dest parameter specifies a destination where results from the query are to be delivered, through the Query Callback Interface. The dest parameter is a URI that both identifies a specific binding of the Query Callback Interface to use and specifies addressing information. The controls parameter controls how the subscription is to be processed; in particular, it specifies the conditions under which the query is to be invoked (e.g., specifying a periodic schedule). The subscriptionID is an arbitrary string that is copied into every response delivered to the specified destination, and otherwise not interpreted by the EPCIS service. The client may use the subscriptionID to identify from which subscription a given result was generated, especially when several subscriptions are made to the same destination. The dest argument MAY be null or empty, in which case results are delivered to a pre-arranged destination based on the authenticated identity of the caller. If the EPCIS implementation does not have a destination pre-arranged for the caller, or does not permit this usage, it SHALL raise an InvalidURIException.
Web Service	unsubscribe	This operation removes a previously registered subscription having the specified subscriptionID.

Web Services

getQueryNames Operation

This operation is used to fetch all of the Query Names that the EPCIS QueryService endpoint supports.

Key Logic

There are only two queries (SimpleEventQuery and SimpleMasterDataQuery) that are supported by the EPCIS QueryService endpoint.

Required Input

No input is required.

Exceptions

None

Restrictions

None

getVendorVersion Operation

This operation is used to fetch versions implemented by the vendor.

Key Logic

Return it as null. This value needs to be taken care of by the vendor.

Required Input

No input is required.

Exceptions

None

Restrictions

None

getStandardVersion Operation

This operation is used to fetch the EPCIS version implemented.

Key Logic

EPCIS 1.1 is implemented, so 1.1 is returned every time.

Required Input

No input is required.

Exceptions

None

Restrictions

None

poll Operation

This operation is used to fetch the events present in the EPCIS database based on various input parameters and access of the user.

Key Logic

This operation returns events in the EPCIS database, based on the request from the user.

Required Input

• queryName (Required)

• params (optional)

  • param

    • name

    • value

Exceptions

• Required parameter is incorrect or not provided.

• Optional parameters are provided but are incorrect.

• Setup parameters required are not present.

• Database query takes more time than the specified value.

• Number of events returned are more than the specified value.

• Size of the data returned is more than the specified value.

For more information, see Understanding the EPCIS Repository Setup Process.

Restrictions

None

Input Request for Poll

Name	Type	Mandatory	Description
queryName	String	Yes	It can be either SimpleEventQuery or SimpleMasterDataQuery.
name	String	No/Yes (see details that follow)	Refer to the following tables for all combinations.
value	String/Integer/Float/Date/List	No/Yes (see details that follow)	Refer to the following tables for all combinations.

When queryName = SimpleEventQuery:

<param>	<value> DataType	Can Specify Multiple Values	Enclosed in <string> </string>	Description
eventType	String	Yes	Yes	Identifies the type of event that needs to be fetched.
EQ_action	String	Yes	Yes	Identifies the action in the event to be fetched. It can be ADD, OBSERVE, or DELETE.
GE_eventTime LT_eventTime	ISO 8601 Date Format YYYY-MM-DDTHH:MMZ. Hours, minutes, and offset is mandatory.	No	No	Identifies the eventTime of the event (when the event occurred). GE: Greater than or equal to LT: Less Than
LT_recordTime GE_recordTime	ISO 8601 Date Format YYYY-MM-DDTHH:MMZ. Hours, minutes, and offset is mandatory.	No	No	Identifies the Time when the event was captured. GE: Greater than or equal to LT: Less Than
EQ_bizStep	String	Yes	Yes	Identifies the business step in the event.
EQ_disposition	String	Yes	Yes	Identifies the disposition step in the event.
EQ_readPoint	String	Yes	Yes	Identifies the readpoint in the event.
EQ_bizLocation	String	Yes	Yes	Identifies the business location in the event.
EQ_bizTransaction_type	String	Yes	Yes	Identifies the type in the business step of the event.
MATCH_epc MATCH_parentID MATCH_anyEPC MATCH_epcClass	String, Range	Yes	Yes	Identifies the epc codes in the event. epc: Matches the epcList and childEpc parentId: Matches the parent epc code anyEpc: Matches the parent epc code, child epc, and epc list epcClass: Matches the epc class code
EQ_quantity LT_quantity LE_quantity GE_quantity GT_quantity	Integer, Float	No	No	Identifies the quantity in the event. EQ: Equals LT: Less than LE: Less than or equal to GE: Greater than or equal to GT: Greater than
orderBy	eventTime, recordTime, quantity, <http://namespace>#<customtag>	No	No	Specifies the parameter to be used to arrange the returned events.
orderDirection	ASC, DESC	No	No	Specifies the arrangement order of the events. orderBy is mandatory with this parameter.
eventCountLimit	Integer	No	No	Count of events that should be picked up from the top after arranging them according to the specified order. Cannot be entered with maxEventCount, but orderBy is mandatory with this parameter.
maxEventCount	Integer	No	No	Maximum number of events that should be returned. Cannot be entered with eventCountLimit.
WD_bizLocation	String	Yes	Yes	Identifies the bizlocation parameter of the events with descendents referred from the master data table.
WD_readPoint	String	Yes	Yes	Identifies the read point parameter of the events with descendents referred from the master data table.
EQ_fieldname	String, Integer, Float, Date, List	Yes	Yes (if String type is used)	Identifies the custom tag. EQ: Equal to
GT_fieldname	Integer, Float, Date	No	No	Identifies the custom tag. GT: Greater than
LT_fieldname	Integer, Float, Date	No	No	Identifies the custom tag. LT: Less than
LE_fieldname	Integer, Float, Date	No	No	Identifies the custom tag. LE: Less than or equal to
GE_fieldname	Integer, Float, Date	No	No	Identifies the custom tag . GE: Greater than or equal to
EXISTS_fieldname	N/A	N/A	N/A	Checks for the existence of the custom tag in the event.
HASATTR_fieldname	String	Yes	Yes	Checks for the vocabulary element Id (which has specified value) for the specified custom tag in the event.
EQATTR_fieldname_attrname	String	Yes	Yes	Checks for the value of the specified custom tag which is one of the vocabulary element ids which has the attribute name and value specified.

When queryName = SimpleMasterDataQuery:

<param>	<value> DataType	Can Specify Multiple Values	Enclosed in <string> </string>	Description
includeAttributes (required)	Boolean (true/false)	No	No	Determines whether the vocabulary elements are returned with attributes or not.
includeChildren (required)	Boolean (true/false)	No	No	Determines whether the vocabulary elements are returned with children or not.
vocabularyName	String	Yes	Yes	Identifies the vocabulary type.
attributeNames	String	Yes	Yes	Identifies the vocabulary elements which have the specified attributes.
EQ_name	String	Yes	Yes	Identifies the vocabulary element ID in the vocabulary element.
WD_name	String	Yes	Yes	Identifies the vocabulary element ID in the vocabulary element and includes them with the children.
HASATTR	String	Yes	Yes	Identifies the attribute ID in the vocabulary element and returns all of those vocabulary elements.
EQATTR_attrname	N/A	Yes	Yes	Identifies the vocabulary elements that have a non-null specified attribute.
maxElementCount	Integer	No	No	Identifies the maximum number of vocabulary elements that can be returned in one query.

getSubscriptionIDs Operation

This operation returns a list of all subscriptionIDs currently subscribed to the specified named query.

Key Logic

Returns a list of all subscriptionIDs currently subscribed to the specified named query.

Required Input

Name	Type	Mandatory	Description
queryName	String	Yes	SimpleEventQuery

Exceptions

None

Restrictions

None

Unsubscribe Operation

Removes a previously registered subscription having the specified subscriptionID.

Key Logic

Removes a previously registered subscription having the specified subscriptionID.

Required Input

Name	Type	Mandatory	Description
subscriptionID	String	Yes	Subscription ID for which the subscription has to be cancelled.

Exceptions

None

Restrictions

None

Subscribe Operation

This operation registers a subscriber for a previously defined query having the specified name.

Key Logic

This operation registers a subscriber for a previously defined query having the specified name. The params argument provides the values to be used for any named parameters defined by the query. The dest parameter specifies a destination where results from the query are to be delivered, through the Query Callback Interface. The dest parameter is a URI that both identifies a specific binding of the Query Callback Interface to use and specifies addressing information. The controls parameter controls how the subscription is to be processed; in particular, it specifies the conditions under which the query is to be invoked (e.g., specifying a periodic schedule). The subscriptionID is an arbitrary string that is copied into every response delivered to the specified destination, and otherwise not interpreted by the EPCIS service. The client may use the subscriptionID to identify from which subscription a given result was generated, especially when several subscriptions are made to the same destination.

The dest argument MAY be null or empty, in which case results are delivered to a pre-arranged destination based on the authenticated identity of the caller. If the EPCIS implementation does not have a destination pre-arranged for the caller, or does not permit this usage, it SHALL raise an InvalidURIException.

Required Input

• queryName (Required)

• params (optional)

  • param

    • name

    • value

Exceptions

• Required parameter is incorrect or not provided.

• Optional parameters are provided but are incorrect.

• Setup parameters required are not present.

• Database query takes more time than the specified value.

• Number of events returned is more than the specified value.

• Size of the data returned is more than the specified value.

Restrictions

None

Input Request for Poll

Name	Type	Mandatory	Description
queryName	String	Yes	SimpleEventQuery.
name	String	No/Yes (see details that follow)	Similar to poll operation.
value	String/Integer/Float/Date/List	No/Yes (see details that follow)	Similar to poll operation.

WSDL

WSDL oracle.apps.pas.epc.service.query.EPCglobalEPCISService.wsdl

For detailed descriptions of all input and output parameters, please refer to the EPCglobalEPCISService wsdl for this endpoint.

Web Service Exceptions

Using the Web Service Exceptions page you can view the job instance messages created by these web service operations:

• mergeLot

• createTransaction

• editSerialStatusAndUsageByPrimaryDocument

• editSerialStatusAndUsageByLabel

• editSerialStatusAndUsageByList

• capture

• mergeProducts

A web service operation can fail because the data that is being passed in the message is invalid or the data being passed is incorrect for the message. In addition, you can view the overall system problem errors or endpoints not running errors using the Web Service Exceptions page. If the web service operations process successfully no message is recorded.

The Web Service Exceptions page is located under the Serials section and the Transactions section of the Task menu. The page is the same in both locations.

For more information on the Web Service Exceptions page, see Using the Job Instances Functionality.

Web Service E-Mail Notifications

Oracle Pedigree and Serialization Manager (OPSM) gives you the ability to send e-mail notifications for the following:

• Process errors that occur when processing transactions using these operations:

  • captureEpcisDoc

  • createTransaction

  • editSerialStatusAndUsageByLabel

  • editSerialStatusAndUsageByList

  • editSerialStatusAndUsageByPrimaryDocument

  • HTTPPost

  • mergeLot

• Exceptions that occur when processing shipment and return transactions using the createTransaction operation.

• Counterfeit status updates that occur for a serial using the editSerialStatusAndUsageByLabel, editSerialStatusAndUsageByList, and editSerialStatusAndUsageByPrimaryDocument operations.

When processing transactions, serials, or lots using the createTransaction, editSerialStatusAndUsageByLabel, editSerialStatusAndUsageByList, and editSerialStatusAndUsageByPrimaryDocument, and mergeLot web service operations process errors can occur. For example, a process can be stuck in an "In Progress" status. When this happens no other transactions, serials, or products are allowed to process which halts all other subsequent jobs. Examples of where transactions get stuck "In Progress" are:

• Shipment transactions where input data through the web service operation is a mismatch from the data in OPSM. For example, the unit of measure specified on the input data does not match the unit of measure in OPSM.

• Return transactions where input data through the web service operation is a mismatch from the data in OPSM.

When shipment or return transactions fail while being processed by the createTransaction web service operation an exception is logged and these exceptions are reported in the OPSM Dashboard under the Transaction Exceptions component. These exceptions are also sent in the e-mail notifications only if e-mail notifications are enabled.

Finally, when the counterfeit status is updated on a serial using the editSerialStatusAndUsageByLabel, editSerialStatusAndUsageByList, and editSerialStatusAndUsageByPrimaryDocument web services operations, those status changes are reported in the OPSM Dashboard under the Counterfeit Serial Analysis component. These status changes are also sent in the e-mail notifications only if e-mail notifications are enabled.

OPSM using Oracle Scheduler sends e-mail notifications of these errors, exceptions, or counterfeit status changes to the individuals responsible for this information in your organization. You can send e-mail notifications to both a system-level e-mail address and a location-specific e-mail address. These e-mail addresses can be a single e-mail address or a distribution list. Oracle Scheduler only sends notifications that pertain to errors, exceptions, or counterfeit status updates that occur between each run of the scheduler. A consolidated e-mail containing all of the errors found is sent to each e-mail address you specify. For example, if there are 20 transactions that are in exception, the system-wide e-mail addresses receives one e-mail with the 20 transactions listed along with their associated errors. If 10 of those transactions belong to one location (for example, Detroit) then the Location Contact e-mail address for Detroit receives one e-mail with the 10 transactions and their associated errors.

The Oracle Scheduler is included with the Oracle database that OPSM uses. The Oracle Scheduler is configured to execute a PL/SQL script which OPSM provides. This script queries the database to look for records where the transaction is stuck in a "In Progress" status, the status is in exception, or when the counterfeit status is updated on a serial. Oracle Scheduler uses the information you define in the System Parameters page to determine if notifications are to be sent and the time interval when the scheduler runs. In addition, the Oracle Scheduler also uses the e-mail addresses you define on the System Parameters page and Location Contacts tab of the Location page to determine the individuals that receive the e-mail notifications.

For more information, see:

• Maintaining System Parameters.

• Maintaining Locations.

• Oracle Pedigree and Serialization Manager Installation Guide.

• Oracle Pedigree and Serialization Manager Installation Guide, Installing Oracle Pedigree and Serialization Manager chapter, Post Installation Tasks, Configure an Access Control List File for Web Service E-mail Notifications.

Web Service E-mail Notifications Process Flow

These steps illustrate a simple web services e-mail notification process flow:

Web Service E-mail Notifications Process Flow

1. Oracle Scheduler is installed and the PL/SQL script (procedure) is registered in the database.

   The system registers the procedure for you automatically upon install.

2. Access control list (ACL) file is configured for the web service e-mail notifications.

   For information see, Configure an Access Control List File for Web Service E-mail Notifications section in the Oracle Pedigree and Serialization Manager Installation Guide.

3. Administrator enables e-mail notification for the system and enters the e-mail notification setup information using the System Parameters page - Error Notification sub tab and the Create Location page - Location Contact sub tab.

4. Web service operations (createTransaction, editSerialStatusAndUsageByLabel, editSerialStatusAndUsageByList, and editSerialStatusAndUsageByPrimaryDocument, and mergeLot) are executed and errors, exceptions, or counterfeit status updates occur.

5. Oracle Scheduler automatically queries the database per the time frame (Schedule Execution Interval (minutes) field) set on the System Parameters page.

   E-mail notifications are sent to the system-wide e-mail address or location-specific e-mail address listing the transactions where errors, exceptions, or counterfeit status updates have occurred along with the related information.

6. Individuals review the e-mail sent to them by Oracle Scheduler and take the necessary steps to correct the issues listed in the e-mail.

7. Web service operations (createTransaction, editSerialStatusAndUsageByLabel, editSerialStatusAndUsageByList, and editSerialStatusAndUsageByPrimaryDocument, and mergeLot) that had issues are executed again.