The following sections describe the XML mapping for Oracle Advanced Planning and Scheduling, Oracle Order Management, Oracle Payables, Oracle Purchasing, and Oracle Shipping Execution.
The following table shows the mapping of the referenced XML data between Oracle Advanced Planning and Scheduling and Oracle Transportation Management, whether the data is required or optional, and a brief description.
| Optional/Required | OTM Element | EBS Table.Column | Description |
|---|---|---|---|
| R | N/A | MSC_TRANSPORTATION_UPDATES.ORDER_TYPE, | 1 is for PO |
| R | TransOrderLineGid.Gid.Xid | MSC_TRANSPORTATION_UPDATES.PO_LINE_LOCATION_ID | xid = LINE-<PO_LINE_ID>-SCHED-<LINE_LOCATION_ID> |
| R | TransOrderLineGid.Gid.Xid | MSC_TRANSPORTATION_UPDATES.PO_LINE_ID | xid = LINE-<PO_LINE_ID>-SCHED-<LINE_LOCATION_ID> |
| R | Release.ReleaseLine. LatestEstimatedArrivalDate | MSC_TRANSPORTATION_UPDATES.UPDATED_ARRIVAL_DATE | This is the latest estimated arrival date passed from Oracle Transportation Management. |
| Optional/Required | OTM Element | EBS Table.Column | Description |
|---|---|---|---|
| R | Release.ReleaseLine.ReleaseLineGid | MSC_TRANSPORTATION_UPDATES.WSH_DELIVERY_DETAIL_ID | ReleaseLineGid =delivery_detail_id |
| R | Release.ReleaseLine. LatestEstimatedArrivalDate | MSC_TRANSPORTATION_UPDATES.UPDATED_ARRIVAL_DATE | This is the latest estimated arrival date passed from Oracle Transportation Management. |
Oracle Order Management to Oracle Transportation Execution XML Mapping
The following table shows the mapping of the referenced XML carrier data between Oracle E-Business Suite and Oracle Transportation Management, whether the data is required or optional, and a brief description. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite)
| Optional/Required | OTM Element | EBS Table.Column | Description |
|---|---|---|---|
| R | LocationGid.Xid | Ship_From/To location id from the PL/SQL FTE_PROCESS_REQUESTS.fte_source_line_tab In the following format: Use same domain name (Profile Option OTM: Domain Name) for all different locations. (customer/supplier/carrier/org).XID part of GID will be concatenation of three fields to generate a unique combination. For example, for customer site it could be CUS-201-101 where 201 is customer ID and 101 is location_id. For carrier site, it could be CAR-301-401 where 301 is carrier ID and 401 is carrier_site_id. For organization location, it could be ORG-501-601 where 501 is organization ID and 601 is location_id. | Order source location ID |
| O | SourceAddress.CorporationGid.Xid | ORG-201 (if 201 is the org ID) | Source address ID |
| O | DestAddress.CorporationGid.Xid | CUS-1091 (if 1091 is the customer ID) | Destination address ID |
| O | City | Is queried from the database using the location ID.WSH_LOCATIONS.CITY | City. DataType: A30 |
| O | ProvinceCode | Use the Region Location mapping to see if the state code exists. If not, and if the state length is two, then use that as the state code. If not, then no state code is passed. | A two-character province code. In the United States, ProviceCode corresponds to a two character state code such as PA. DataType: A2. |
| O | CountryCode3Gid | WSH_LOCATIONS.COUNTRY has the 2 letter ISO code. For the three letter codes FND_TERRITORIES.ISO_TERRITORY_CODE must be used. FND_TERRITORIES.TERRITORY_CODE maps to WSH_LOCATIONS.COUNTRY. For a given location ID, the three letter country code can be determined by:SELECT t.iso_territory_code from FND_TERRITORIES t, WSH_LOCATIONS l where l.location_id= X and l.country=t.territory_code. The domain is PUBLIC. Same as not specifying a domain. | The three character ISO country code global identifier. Validation: References COUNTRY_CODE table. |
| O | PostalCode | WSH_LOCATIONS.POSTAL_CODE | The postal code component of Address. For United States, this is a 3, 5 or 9 character zip code. DataType: A15 |
| O | EstimatedShipDate | Ship_date from PL/SQL records formatted into YYYYMMDDHHMMSS | Estimated ship date. Format: YYYYMMDDHHMMSS |
| O | EstimatedArrivalDate | Arrival_date from PL/SQL records formatted into YYYYMMDDHHMMSS | Estimated arrival date. Format: YYYYMMDDHHMMSS |
| O | TimeZoneGid | Obtained using FND_TIMEZONES.get_server_timezone_code. The domain is PUBLIC. Same as not specifying a domain. | The time zone global identifier. Validation: References TIME_ZONE table. This is the Java timezone code |
| O | ShipUnitCount | 1 | Hardcoded to 1 |
| O | PackagedItemGid.Xid | Inventory_item_id from PL/SQL records, ”inventory_organization_id” + “-“ + “inventory_item_id” (example: 207-149). | Packaged item ID |
| O | WeightValue. VolumeValue | Weight/Volume from PL/SQL records | Weight and Volume values |
| O | WeightUOMGid.Xid, VolumeUOMGid.Xid | Check Attribute15 of descriptive flex field for the UOM in the PL/SQL Record. If there is a value, then use that; otherwise, use the uom_code. The domain for the UOM is PUBLIC. Same as not specifying a domain. | Weight and Volume UOM ID |
| O | Length/Width/Height and UOMs | Queried using inventory_item_id. Check Attribute15 of descriptive flex field for the UOM in the PL/SQL Record. If there is a value, then use that; otherwise, use the uom_code. The domain for the UOM is PUBLIC. Same as not specifying a domain. | Length, width, height, and UOMs |
| O | PaymentMethodeCode | Freight_terms_code from PL/SQL records. The domain is PUBLIC. Same as not specifying a domain. | Payment method code. |
| O | TransportModeGid | Same as mode of transport code. Mode_of_transport from PL/SQL records. | Mode of transportation Global ID |
| O | ServiceProviderGid | Same as carrier ID. Carrier_id from PL/SQL records. | Service provider Global ID |
| O | RateServiceGid | Same as service_level_code. From service_level from PL/SQL records. | Service rate Global ID |
| O | RIQRequestType | For CHOOSE SHIP METHODS: All Options For GET FREIGHT RATES: LowestCost. |
Delivery Request. Validation: (LowestCost, FastestTransit, AllOptions). Default: LowestCost. |
| O | Perspective | For CHOOSE SHIP METHODS: B For GET FREIGHT RATES: B |
Shipment Perspective. Used to ensure that the order and shipment data shown to the end user makes sense from that users perspective or point of reference. Possible values are B = Buy Side, and S = Sell Side. For the RIQQuery element, a value of 'A' can be used to query for both Buy Side and Sell Side. |
| O | UseRIQRoute | For CHOOSE SHIP METHODS: N For GET FREIGHT RATES: N |
Y, N. Specifies whether to use the RIQ Route functionality, or use the RIQ Rate behavior. Default is N. |
| O | UserName, Password | From the profile options. |
The following table shows the mapping of the required referenced data between Oracle E-Business Suite and Oracle Transportation Management and a brief description of each. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite)
| Target AP Invoices Interface | Source OTM / BPEL AP Integration Usage | Description |
|---|---|---|
| exchangeRateType | In BPEL set to "CORPORATE." | Exchange rate type for foreign currency invoices. EXCHANGE_RATE_TYPE: Type of exchange rate used to calculate the exchange rate between theinvoice currency and your functional currency. If the value in the INVOICE_CURRENCY_CODE column is a foreign currency code, then enter a value in this column to indicate which exchange rate type you are using. You can enter a predefined or user–defined exchange rate type. If the invoice record has a foreign currency and you do not enter a value for EXCHANGE_RATE_TYPE, then during import the system uses the Exchange Rate Type selected in the Payables Options window. If the invoice currency and your functional currency are associated fixed–rate currencies, such as euro and another EMU currency, then enter EMU Fixed.Validation - Payables uses five types of exchange rates: User, Spot, Corporate, EMU Fixed, and user–defined. If you use Spot, Corporate, or any user–defined rate type, the value you enter here is validated against the GL Daily Rates table. If you use EMU Fixed, Payables provides the exchange rate during import. If you use User as the exchange rate type, You must either enter a value for EXCHANGE_RATE or, if the Payables OptionCalculate User Exchange Rate is enabled, you can enter a value for NO_XRATE_BASE_AMOUNT instead. Destination - AP_INVOICES_ALL.EXCHANGE_RATE_TYPE |
| invoiceAmount | Map from OTM Voucher AmountToPay | Invoice Amount INVOICE_AMOUNT: Amount of the invoice. Do not exceed the precision of the currency of the invoice. For example, if you are entering an amount in US dollars, then do not enter more than two numbers after the decimal point. Validation - This value must equal the sum of the AMOUNT values in the AP_INVOICE_LINES_INTERFACE table for lines with the same INVOICE_ID. The amount must correspond to the invoice type. For example, Standard invoices must have an amount of zero or greater. Destination - AP_INVOICES_ALL.INVOICE_AMOUNT |
| invoiceCurrencyCode | ISO standard OTM GlobalCurrencyCode | Currency of invoice. INVOICE_CURRENCY_CODE: Currency code for the invoice. If you do not enter a value, then the supplier site value defaults during import. Validation - If you enter a code in a foreign currency, then you must enter a valid, active currency code from Destination - FND_CURRENCIES.CURRENCY_CODE.AP_INVOICES_ALL.INVOICE_CURRENCY_CODE |
| invoiceDate | Map to InvoiceDate from voucher. Supplier's invoice date This date is used to drive Accounting Date/GL Date. | Invoice Date INVOICE_DATE: Date of the invoice. If you do not enter a value, then the system uses the date that you submit the Payables Open Interface Import as the invoice date. Validation - The value must be in valid date format. Destination - AP_INVOICES_ALL.INVOICE_DATE |
| invoiceId | Call AP to retrieve sequence, and then apply to header and lines. | Invoice ID Required, Primary key. This value is assigned in the Quick Invoices window by the AP_INVOICES_INTERFACE_S sequence. Unique identifier for this invoice within this batch. You assign the same value to the invoice’s lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice. |
| invoiceNum | Concatenated field InvoiceNum plus VoucherGid AP Invoice Number <= 50 characters. Must be a unique number for the supplier. Carrier Invoice Num = "JBHT-1002322-1" Voucher Gid = "EBS.12345678." Invoice number will be a concatentated field of CarrierInvoiceNum plus VoucherGID = "JBHT-1002322-1|EBS.2345678" | Vendors number/invoice number Required if there is more than one invoice for the supplier during import. INVOICE_NUM: Enter the invoice number that you want to assign to the invoice created in Payables from this record. The number must be unique for the supplier. If you do not enter a value, then during import, Payables uses the system date at the time of import as a default. If you enter more than one invoice for a supplier, then be sure to enter unique invoice numbers rather than using the default, or the invoices will have duplicate invoice numbers and will be rejected during import. Validation - Must be a unique number for the supplier. If you assign a duplicate number for the supplier, Payables Open Interface Import does not create an invoice from this record. Destination - AP_INVOICES_ALL.INVOICE_NUM |
| invoiceTypeLookupCode | BPEL transformation. Positive amount voucher = "STANDARD," Negative amounts on voucher = "CREDIT." | Type of Invoice (can be STANDARD or CREDIT) INVOICE_TYPE_LOOKUP_CODE: Type of invoice: Credit or Standard. If you do not enter a value, then the system assigns a value during Payables Open Interface Import based on the value of INVOICE_AMOUNT. If INVOICE_AMOUNT is less than zero, the invoice will be Credit. If INVOICE_AMOUNT is zero or greater, the invoice will be Standard. Validation - The value must be Credit or Standard. The invoice type must correspond to the invoice amount if it is Credit or Standard. For example, a Credit invoice must have an invoice amount that is less than zero. Destination - AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE |
| source | Source = "TRANSPORTATION MANAGEMENT." | Source of the invoices. SOURCE: Source of the invoice data. Examples include Quick Invoices, EDI Gateway (e–Commerce Gateway), Credit Card, Oracle Assets, Oracle Property Manager, ERS (Evaluated Receipt Settlement), RTS (Return toSupplier), iSupplier Portal, XML Gateway, and user–defined. You define additional values for Source in the Oracle Payables Lookups window. If you have defined additional sources, you should use a source name for only one type of source. For example, do not use the same source name for invoices that you enter in Quick Invoices, and invoices you load with SQL*Loader. The Source name also determines which records will be selected for import or purge. You specify a Source name when you submit Payables Open Interface Import or Purge Payables Open Interface. Validation - If you do not use a predefined source you must enter the name exactly as you have defined the lookup value in the Oracle Payables Lookups window, or Payables Open Interface Import will not create an invoice from the record. The lookup value must have the Type SOURCE. See: Lookups in the Oracles Payables User Guide. Destination - AP_INVOICES_ALL.SOURCE |
| vendorNum | Map to ServiceProvider Alias Qualf ="VENDOR_NUM." | Supplier number VENDOR_NUM: Supplier number. You must identify the supplier by entering a value for one of the following columns in this table: VENDOR_ID,VENDOR_NUM, VENDOR_SITE_ID, VENDOR_SITE CODE, or PO_NUMBER. If you have not yet entered the supplier in the Suppliers window, then enter it before import. Validation - The number must be for an existing, valid supplier. You can obtain a list of valid values from PO_VENDORS.SEGMENT1. None. This value is used to enter Destination - AP_INVOICES_ALL.VENDOR_ID. |
| vendorSiteCode | Map from SeerviceProvider Alias Qualf = "VENDOR_SITE_CODE." | Supplier site code VENDOR_SITE_CODE: Supplier site name. If you do not provide a valid value to identify the pay site in VENDOR_SITE_CODE or VENDOR_SITE_ID, then import searches for a valid supplier pay site in the following order: Primary pay site for supplier Single existing pay site for supplier Derived from PO number matched at the header level Import rejects the invoice if it cannot identify a valid supplier site. Validation - This must be a valid, active supplier site for the supplier you specify (VENDOR_NUM or VENDOR_ID). You can obtain a list of valid values from PO_VENDORS.VENDOR_SITE_CODE. The site must also be a pay site. Destination - None. This value is used to enter AP_INVOICES_ALL.VENDOR_SITE_ID. |
| amount | Represents the amount for this invoice line. One line per invoice in delivered integration. "CREDIT" = negative "STANDARD" = positive. | Line amount |
| lineTypeLookupCode | BPEL set to "FREIGHT" | Type of invoice line (Item, Freight, Tax, Miscellaneous) |
| DistCodeConcatenated | BPEL conditional logic in mapped to location reference number qualifier “ACCOUNT_NUMBER” from the source location or if not available on the source location the value is taken from “ACCOUNT_NUMBER” on the service provider location. | Accounting flexfield for account associated with a distribution line |
APInvoices Interface Sample XML
<PushToEBS_insert_InputVariable> <part name="ApInvoicesInterfaceCollection" > <ApInvoicesInterfaceCollection> <ApInvoicesInterface> <description>-002647*GUEST.02420*</description> <exchangeRateType>CORPORATE</exchangeRateType> <invoiceAmount>1300.0</invoiceAmount> <invoiceCurrencyCode>USD</invoiceCurrencyCode> <invoiceDate>2006-04-03T08:00:00</invoiceDate> <invoiceId>46836</invoiceId> <invoiceNum>ZIPPY-000004|GUEST.20060402-0001</invoiceNum> <invoiceTypeLookupCode>STANDARD</invoiceTypeLookupCode> <source>TRANSPORTATION MANAGEMENT</source> <vendorSiteCode>ZIPPY</vendorSiteCode> <apInvoiceLinesInterfaceCollection> <ApInvoiceLinesInterface> <amount>1300.0</amount> <distCodeConcatenated>01-000-2210-0000-000</distCodeConcatenated> <invoiceLineId>77910</invoiceLineId> <lineTypeLookupCode>FREIGHT</lineTypeLookupCode> </ApInvoiceLinesInterface> </apInvoiceLinesInterfaceCollection> </ApInvoicesInterface> </ApInvoicesInterfaceCollection> </part> </PushToEBS_insert_InputVariable>
Warning: You must ensure that for each address in Oracle E-Business Suite, the Country Code is defined. An address passed to Oracle Transportation Management without a Country Code will fail.
| Required/Optional | OTM Element | EBS Table.Column | Description |
|---|---|---|---|
| O | TransOrderHeader.TransOrderGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | |
| O | TransOrderHeader.TransOrderGid.Gid.Xid | Concatenation: In the case of a std PO:PO-<PO_HEADERS_ALL.po_header_id> In the case of a blanket release:PO-<PO_HEADERS_ALL.po_header_id>-REL-<PO_RELEASES_ALL.po_release_id> |
Standard PO:PO-Blanket Release:PO-REL- SPO: PO-12341Release: PO-12345-REL-1 |
| O | TransOrderHeader.OrderTypeGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME | OTM: Domain Name profile option |
| O | TransOrderHeader.OrderTypeGid.Gid.Xid | Constant: PURCHASE_ORDER | PURCHASE_ORDER |
| O | TransOrderHeader.OrderRefNum.OrderRefNumQualifierGid.Gid.Xid | Constant: PO | Order refnum qualifiers and values are used to send reference information, such as the EBS document number, supplier, operating unit, and so on. PO |
| O | TransOrderHeader.OrderRefNum.OrderRefNumValue | PO_HEADERS_ALL.segment1 | PO Number |
| O | TransOrderHeader.OrderRefNum.OrderRefNumQualifierGid.Gid.Xid | Constant: RELEASE_NUMBER | RELEASE_NUMBER |
| O | TransOrderHeader.OrderRefNum.OrderRefNumValue | PO_RELEASES_ALL.release_num | PO Release Number |
| O | TransOrderHeader.OrderRefNum.OrderRefNumQualifierGid.Gid.Xid | Constant: SELL_TOP | SELL_TO |
| O | TransOrderHeader.OrderRefNum.OrderRefNumValue | HR_LOCATIONS_ALL.location_code | Operating Unit Name |
| O | TransOrderHeader.OrderRefNum.OrderRefNumQualifierGid.Gid.Xid | Constant: SUPPLIER | SUPPLIER |
| O | TransOrderHeader.OrderRefNum.OrderRefNumValue | PO_VENDORS.vendor_name | Supplier Name |
| O | TransOrderHeader.OrderRefNum.OrderRefNumQualifierGid.Gid.Xid | Constant: SUPPLIER_SITE | |
| O | TransOrderHeader.OrderRefNum.OrderRefNumValue | PO_VENDOR_SITES_ALL.vendor_site_code | Supplier Site |
| O | TransOrderHeader.OrderRefNum.OrderRefNumQualifierGid.Gid.Xid | Constant: BILL_TO | BILL_TO |
| O | TransOrderHeader.OrderRefNum.OrderRefNumValue | HR_LOCATIONS_ALL.location_code | Bill To Site (Inventory Org) |
| O | TransOrderHeader.TransactionCode | Constant: IU | I or U (Insert or Update) |
| O | TransOrderHeader.CommercialTerms.PaymentMethodCode | If PO_HEADERS_ALL.shipping_control = ‘BUYER’:Constant: COLOtherwise:Constant: SUP | Transportation Arranged By COL . PPD (Codes correspond to Freight Collect or Freight Prepaid) |
| O | TransOrderHeader.InvolvedParty.InvolvedPartyQualifierGid.Gid.Xid | Constant: SUPPLIER | SUPPLIER |
| O | TransOrderHeader.InvolvedParty.LocationRef.LocationGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderHeader.InvolvedParty.LocationRef.LocationGid.Gid.Xid | Concatenation:SUP-<PO_HEADERS_ALL.vendor_id> <PO_HEADERS_ALL.vendor_site_id> |
SUP <SupplierID>-<Site ID> |
| O | TransOrderHeader.InvolvedParty.Contact.ContactGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderHeader.InvolvedParty.Contact.ContactGid.Gid.Xid | In order of preference: 1. PO_VENDOR_CONTACTS.email_address 2. Concatenation: PO_VENDOR_CONTACTS.area_code PO_VENDOR_CONTACTS.phone_number 3. Concatenatino: PO_VENDOR_CONTACTS.first_name PO_VENDOR_CONTACTS.last_name |
Supplier contact ID (mapped to the email address, phone number or name in that order) |
| O | Location.Corporation.CorporationName | Concatenation: SUP-<PO_HEADERS_AL.vendor_id> |
SUP-<Vendor ID> |
| O | Location.LocationName | PO_VENDOR_SITES_ALL.vendor_site_code | Supplier Site Name SAN JOSE-ERS |
| O | Location.LocatioRefNum.Qualifier | Constant: EBS_NAME | EBS_NAME |
| O | Location.LocatioRefNum.Value | PO_VENDOR_SITES_ALL.vendor_site_code | Supplier Site Name |
| O | LocationGid.Gid.Xid | Concatenation: SUP-<PO_HEADERS_AL.vendor_id>-<PO_HEADERS_ALL.vendor_site_id> |
SUP <Supplier ID>-<Site ID> |
| O | Location.LocationRole | Constant: SHIPFROM-SHIPTO | SHIPFROM >SHIPTO |
| O | Location.Address.AddressLine1 to AddressLine3 | Concatenation: PO_VENDOR_SITES_ALL.address_line1 PO_VENDOR_SITES_ALL.address_line2 PO_VENDOR_SITES_ALL.address_line3 | Supplier Site Address Lines |
| O | Location.Address.City | PO_VENDOR_SITES_ALL.city | Supplier Site Address City |
| O | Location.Address.CountryCode3 | PO_VENDOR_SITES_ALL.country | Supplier Site Address Country |
| O | Location.Address.PostalCode | PO_VENDOR_SITE,S_ALL.zip | Supplier Site Address Postal Code |
| O | Location.Address.Province | In order of preference: 1. PO_VENDOR_SITES_ALL.state 2. PO_VENDOR_SITES_ALL.province 3. PO_VENDOR_SITES_ALL.county |
Supplier Site Address Province |
| O | Location.Address.ProvinceCode | In order of preference: 1. PO_VENDOR_SITES_ALL.state 2. PO_VENDOR_SITES_ALL.provincePO_VENDOR_SITES_ALL.county |
Supplier Site Address Province Code |
| O | Contact.ContactGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | Contact.ContactGid.Gid.Xid | In order of preference: 1. PO_VENDOR_CONTACTS.email_address 2. Concatenation: PO_VENDOR_CONTACTS.area_code PO_VENDOR_CONTACTS.phone_number 3. Concatenatino: PO_VENDOR_CONTACTS.first_name PO_VENDOR_CONTACTS.last_name |
Supplier contact ID (mapped to the email address, phone number or name in that order) |
| O | Contact.FirstName | PO_VENDOR_CONTACTS.first_name | Supplier Contact First Name |
| O | Contact.LastName | PO_VENDOR_CONTACTS.last_name | Supplier Contact Last Name |
| O | Contact.Phone1 | Concatenation: PO_VENDOR_CONTACTS.area_code PO_VENDOR_CONTACTS.phone_number | Supplier Contact Phone Number |
| O | Contact.EmailAddress | PO_VENDOR_CONTACTS.email_address | Supplier Contact Email ID |
| O | Contact.Fax | Concatenation: PO_VENDOR_CONTACTS.fax_area_code PO_VENDOR_CONTACTS.fax | Supplier contact fax number |
| O | Contact.CommunicationMethod | Constant. In order of preference: 1. EMAIL 2. FAX 3. MANUAL |
Email/Fax/Manual (Phone) |
| O | TransOrderHeader.InvolvedParty.InvolvedPartyQualifierGid.Gid.Xid | Constant: SELL_TO | This involved party is the purchasing operating unit SELL_TO |
| O | TransOrderHeader.InvolvedParty.LocationRef.LocationGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderHeader.InvolvedParty.LocationRef.LocationGid.Gid.Xid | Concatenation: ORG-<PO_HEADERS_ALL.org_id>-<HR_LOCATIONS_ALL.location_id> |
ORG-<Org ID>-<Location ID> |
| O | TransOrderHeader.InvolvedParty.Contact.ContactGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderHeader.InvolvedParty.Contact.ContactGid.Gid.Xid | In order of preference: 1. PER_ALL_PEOPLE_F.email_address 2. PER_PHONES.phone_number 3. Concatenation: PER_ALL_PEOPLE_F.first_name PER_ALL_PEOPLE_F.last_name |
Buyer contact ID (mapped to the email address, phone number or name in that order) |
| O | Location.CorporationXID | Concatenation: ORG-<PO_HEADERS_ALL.org_id> |
ORG-<Operating Unit ID> ORG-204 |
| O | Location.LocatioRefNum.Qualifier | Constant: EBS_NAME | EBS_NAME |
| O | Location.LocatioRefNum.Value | HR_LOCATIONS_ALL.location_code | Operating Unit Default Location Name |
| O | Location.LocationGid.Gid.Xid | Concatenation: ORG-<PO_HEADERS_ALL.org_id>-<HR_LOCATIONS_ALL.location_id> |
ORG-<Org ID>-<Location ID> ORG-204-4848 |
| O | Location.LocationRole | Constant: SHIPFROM-SHIPTO | SHIPFROM->SHIPTO |
| O | Location.Address.AddressLine1 to AddressLine3 | Concatenation: HR_LOCATIONS_ALL.address_line_1 HR_LOCATIONS_ALL.address_line_2 HR_LOCATIONS_ALL.address_line_3 |
OU Default Location Address Lines |
| O | Location.Address.City | HR_LOCATIONS_ALL.town_or_city | OU Default Location Address City |
| O | Location.Address.CountryCode3 | FND_TERRITORIES.iso_territory_code | OU Default Location Address Country |
| O | Location.Address.PostalCode | HR_LOCATIONS_ALL.postal_code | OU Default Location Address Postal code |
| O | TransOrderHeader.InvolvedParty.LocationRef.Location.Address.Province | Either HR_LOCATIONS_ALL.region_1 or HR_LOCATIONS_ALL.region_2, depending on address style. |
OU Default Location Address Province |
| O | Location.Address.ProvinceCode | Either HR_LOCATIONS_ALL.region_1 or HR_LOCATIONS_ALL.region_2, depending on address style. |
OU Default Location Address Province Code |
| O | Contact.ContactGid.Gid.DomainName | OTM: Domain Name profile option | OTM: Domain Name profile option |
| O | Contact.ContactGid.Gid.Xid | In order of preference: 1. PER_ALL_PEOPLE_F.email_address 2. PER_PHONES.phone_number 3. Concatenation: PER_ALL_PEOPLE_F.first_name PER_ALL_PEOPLE_F.last_name |
Supplier contact ID (mapped to the email address, phone number or name in that order) |
| O | Contact.FirstName | PER_ALL_PEOPLE_F.first_name | Buyer Contact First Name |
| O | Contact.LastName | PER_ALL_PEOPLE_F.last_name | Buyer Contact Last Name |
| O | Contact.Phone1 | PER_PHONES.phone_number | Buyer Contact Phone Number |
| O | Contact.EmailAddress | PER_ALL_PEOPLE_F.email_address | Buyer Contact Email ID |
| O | Contact.Fax | PER_PHONES.phone_number | Buyer Contact Fax Number |
| O | Contact.CommunicationMethod | Constant. In order of preference: 1. EMAIL 2. FAX 3. MANUAL |
Email/Fax/Manual (Phone) |
| O | TransOrderHeader.InvolvedParty.InvolvedPartyQualifierGid.Gid.Xid | Constant: BILL-TO | BILL-TO |
| O | TransOrderHeader.InvolvedParty.InvolvedPartyLocationRef.LocationRef.LocationGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderHeader.InvolvedParty.InvolvedPartyLocationRef.LocationRef.LocationGid.Gid.Xid | Concatenation:ORG-<PO_HEADERS_ALL.org_id>-<PO_HEADERS_ALL.bill_to_location_id> | ORG-<Org ID>-<Bill to Location ID> |
| O | TransOrderHeader.InvolvedParty.Contact.ContactGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderHeader.InvolvedParty.Contact.ContactGid.Gid.Xid | In order of preference: 1. PER_ALL_PEOPLE_F.email_address 2. PER_PHONES.phone_number 3. Concatenation: PER_ALL_PEOPLE_F.first_name PER_ALL_PEOPLE_F.last_name |
Buyer contact ID (mapped to the email address, phone number or name in that order) |
| O | Location.LocationGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | Location.LocationGid.Gid.Xid | Concatenation: ORG-<PO_HEADERS_ALL.org_id>-<PO_HEADERS_ALL.bill_to_location_id> |
ORG-<Org ID>-<Bill to Location ID> |
| O | Location.CorperationXID | Concatenation:ORG-<PO_HEADERS_ALL.org_id> | ORG-<Org ID> |
| O | Location.LocatioRefNum.Qualifier | Constant: EBS_NAME | EBS_NAME |
| O | Location.LocatioRefNum.Value | HR_LOCATIONS_ALL.location_code | Bill To Location Name |
| O | Location.Address.AddressLine1 to AddressLine3 | Concatenation: HR_LOCATIONS_ALL.address_line_1 HR_LOCATIONS_ALL.address_line_2 HR_LOCATIONS_ALL.address_line_3 |
Bill To Location Address Lines |
| O | Location.Address.City | HR_LOCATIONS_ALL.town_or_city | Bill To Location Address City |
| O | Location.Address.CountryCode3 | FND_TERRITORIES.iso_territory_code | Bill To Location Address Country |
| O | Location.Address.PostalCode | HR_LOCATIONS_ALL.postal_code | Bill To Location Address Postal Code |
| O | Location.Address.Province | Either HR_LOCATIONS_ALL.region_1 or HR_LOCATIONS_ALL.region_2, depending on address style. |
Bill To Location Address Province |
| O | Location.Address.ProvinceCode | Either HR_LOCATIONS_ALL.region_1 or HR_LOCATIONS_ALL.region_2, depending on address style. |
Bill To Location Address Province Code |
| O | Location.Contact.ContactGid | In order of preference: 1. PER_ALL_PEOPLE_F.email_address 2. PER_PHONES.phone_number 3. Concatenation: PER_ALL_PEOPLE_F.first_name PER_ALL_PEOPLE_F.last_name |
|
| O | Contact.ContactGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | Contact.ContactGid.Gid.Xid | In order of preference: 1. PER_ALL_PEOPLE_F.email_address 2. PER_PHONES.phone_number 3. Concatenation: PER_ALL_PEOPLE_F.first_name PER_ALL_PEOPLE_F.last_name |
Buyer contact ID (mapped to the email address, phone number or name in that order) |
| O | Contact.FirstName | PER_ALL_PEOPLE_F.first_name | Buyer Contact First Name |
| O | Contact.LastName | PER_ALL_PEOPLE_F.last_name | Buyer Contact Last Name |
| O | Contact.Phone1 | PER_PHONES.phone_number | Buyer Contact Phone Number |
| O | Contact.EmailAddress | PER_ALL_PEOPLE_F.email_address | Buyer Contact Email ID |
| O | Contact.Fax | PER_PHONES.phone_number | Buyer Contact Fax Number |
| O | Contact.CommunicationMethod | Constant. In order of preference: 1. EMAIL 2. FAX 3. MANUAL |
Email/Fax/Manual (Phone) |
| O | TransOrderLineDetail.TransactionCode | Constant: IU | IU (Insert/Update) |
| O | TransOrderLineDetail.TransOrderLineGid.Gid.Xid | Concatenation:LINE-<PO_LINES_ALL.po_line_id>-SCHED-<PO_LINE_LOCATIONS_ALL.line_location_id> | LINE-<Line ID>-SCHED-<Schedule ID> |
| O | TransOrderLineDetail.TransOrderLineGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderLineDetail.ItemQuantity.ItemTag1 | Concatenation: LINE/SCHEDUL: <PO_LINES_ALL.line_num>/<PO_LINE_LOCATIONS_ALL.shipment_num> |
LINE->SCHEDULE: <Line Number>-<Schedule Number> These tags are displayed in the Ready to Ship screen in OTM. |
| O | TransOrderLineDetail.ItemQuantity.ItemTag2 | Concatenation: SUPPLIER ITEM: <PO_LINES_ALL.vendor_product_num> |
SUPPLIER ITEM: <Supplier Item> |
| O | TransOrderLineDetail.ItemQuantity.ItemTag3 | Concatenation: REV: <PO_LINES_ALL.item_revision> |
REV: <Item Revision> |
| O | TransOrderLineDetail.ItemQuantity.PackagedItemCount | PO_LINE_LOCATIONS_ALL.quantity | Shipment or Schedule Quantity Quantity changes in Purchasing will not affect released or shipped quantities in OTM – only the order base line quantity. |
| O | TransOrderLineDetail.PackagedItemRef.PackagedItemGid.XID | Concatenation: If UOM code is same in EBS and OTM:<MTL_SYSTEM_ITEMS_KFV.concatenated_segments>_<MTL_UNITS_OF_MEASURE.uom_code> Otherwise:<MTL_SYSTEM_ITEMS_KFV.concatenated_segments>_<MTL_UNITS_OF_MEASURE.attribute15 > |
<EBS Item ID>_<UOM> For one time items:<Item Description>_<UOM> |
| O | TransOrderLineDetail.PackagedItemRef.ItemGid.XID | Concatenation: If UOM code is same in EBS and OTM:<MTL_SYSTEM_ITEMS_KFV.concatenated_segments>_<MTL_UNITS_OF_MEASURE.uom_code> Otherwise:<MTL_SYSTEM_ITEMS_KFV.concatenated_segments>_<MTL_UNITS_OF_MEASURE.attribute15 > |
<EBS Item ID>_<UOM> For one time items:<Item Description>_<UOM> |
| O | TransOrderLineDetail.ItemQuantity.DeclaredValue.MonetaryAmount | Calculated field:PO_LINE_LOCATIONS_ALL.price_override * CIEL(PO_LINE_LOCATIONS_ALL.quantity) | Line Amount Does not include tax |
| O | TransOrderLineDetail.DeclaredValue.FinancialAmount.GlobalCurrencyCode | PO_HEADERS_ALL.currency_code | PO Currency |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureQualGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option Item Feature Qualifiers are used to display additional item information. |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureQualGid.Gid.Xid | Constant: DESCRIPTION | DESCRIPTION |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureValue | PO_LINES_ALL.item_description | PO Line Description |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureQualGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureQualGid.Gid.Xid | Constant: REVISION | REVISION |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureValue | PO_LINES_ALL.item_revision | Item Revision |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureQualGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureQualGid.Gid.Xid | Constant: SUPPLIER_ITEM | SUPPLIER_ITEM |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureValue | PO_LINES_ALL.vendor_product_num | Supplier Item |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureQualGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureQualGid.Gid.Xid | Constant: SUPPLIER_CONFIG_ID | SUPPLIER_CONFIG_ID |
| O | TransOrderLineDetail.TransOrderLine.ItemAttributes.ItemFeatureValue | PO_LINES_ALL.supplier_ref_number | Supplier Config ID This is the configuration ID got from the supplier. For example, a user configured desktop purchased from Dell Web site via punch-out. |
| O | TransOrderLineDetail.TransOrderLine.TimeWindow.LateDeliveryDate | Calculated field. In order of preference: 1. PO_LINE_LOCATIONS_ALL.promised_date + PO_LINE_LOCATIONS_ALL.days_late_receipt_allowed 2. PO_LINE_LOCATIONS_ALL.need_by_date + PO_LINE_LOCATIONS_ALL.days_late_receipt_allowed |
Promised or Need-by Date + Days late receiving tolerance on PO Promised Date if it exists, else Need-By Date |
| O | TransOrderLineDetail.TransOrderLine.TimeWindow.EarlyDeliveryDate | Calculated field. In order of preference: 1. PO_LINE_LOCATIONS_ALL.promised_date - PO_LINE_LOCATIONS_ALL.days_early_receipt_allowed 2. PO_LINE_LOCATIONS_ALL.need_by_date - PO_LINE_LOCATIONS_ALL.days_early_receipt_allowed |
Promised or Need-by Date - Days early receiving tolerance on PO Promised Date if it exists, or else the Need-By Date |
| O | TransOrderlineDetail.TransOrderLine.InvolvedParty.InvolvedPartyQualifierGid.Gid.Xid | Constant: SHIP TO | SHIP TO |
| O | TransOrderHeader.InvolvedParty.InvolvedPartyLocationRef.LocationRef.LocationGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderHeader.InvolvedParty.InvolvedPartyLocationRef.LocationRef.LocationGid.Gid.Xid | Concatenation: ORG-<PO_HEADERS_ALL.org_id>-<PO_HEADERS_ALL.bill_to_location_id> |
ORG-<Org ID>-<Bill to Location ID> |
| O | TransOrderHeader.InvolvedParty.Contact.ContactGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderHeader.InvolvedParty.Contact.ContactGid.Gid.Xid | In order of preference: 1. PER_ALL_PEOPLE_F.email_address 2. PER_PHONES.phone_number 3. Concatenation: PER_ALL_PEOPLE_F.first_name PER_ALL_PEOPLE_F.last_name |
Requestor contact ID – This is mapped to the email address, phone number or name in that order Requestor, if it exists, or else the buyer |
| O | Location.LocationGid.Gid.Xid | Concatenation. If internal location:ORG-<PO_LINE_LOCATIONS_ALL.ship_to_organization_id>-<PO_LINE_LOCATIONS_ALL.ship_to_location_id> If drop-ship:CUS-000-<PO_LINE_LOCATIONS_ALL.ship_to_location_id> |
ORG-<Org ID>-<Ship-To Location ID> or CUS-000-<Ship-To Location ID> |
| O | Location.LocationRole | Constant: SHIPFROM-SHIPTO | SHIPFROM-SHIPTO |
| O | Location.CorperationXID | Concatenation. If internal location:ORG-<PO_LINE_LOCATIONS_ALL.ship_to_organization_id> If drop-ship:CUS-000 |
ORG-<Org ID> or CUS-000 Use ‘CUS-000’ for drop ship orders. |
| O | Location.LocatioRefNum.Qualifier | Constant: EBS_NAME | EBS_NAME |
| O | Location.LocatioRefNum.Value | HR_LOCATIONS_ALL.location_code | Ship-to Location Name |
| O | Location.Address.AddressLine1 to AddressLine3 | Concatenation: HR_LOCATIONS_ALL.address_line_1 HR_LOCATIONS_ALL.address_line_2 HR_LOCATIONS_ALL.address_line_3 |
Ship-to Location Address Lines |
| O | Location.Address.City | HR_LOCATIONS_ALL.town_or_city | Ship-to Location Address City |
| O | Location.Address.CountryCode3 | FND_TERRITORIES.iso_territory_code | Ship-to Location Address Country |
| O | Location.Address.PostalCode | HR_LOCATIONS_ALL.postal_code | Ship-to Location Address Postal Code |
| O | Location.Address.Province | Either HR_LOCATIONS_ALL.region_1 or HR_LOCATIONS_ALL.region_2, depending on address style. |
Ship-to Location Address Province |
| O | Location.Address.ProvinceCode | Either HR_LOCATIONS_ALL.region_1 or HR_LOCATIONS_ALL.region_2, depending on address style. |
Ship-to Location Address Province Code |
| O | Contact.ContactGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | Contact.ContactGid.Gid.Xid | In order of preference: 1. PER_ALL_PEOPLE_F.email_address 2. PER_PHONES.phone_number 3. Concatenation: PER_ALL_PEOPLE_F.first_name PER_ALL_PEOPLE_F.last_name |
Supplier contact ID (mapped to the email address, phone number or name in that order) |
| O | Contact.FirstName | PER_ALL_PEOPLE_F.first_name | Requestor Contact First Name |
| O | Contact.LastName | PER_ALL_PEOPLE_F.last_name | Requestor Contact Last Name |
| O | Contact.Phone1 | PER_PHONES.phone_number | Requestor Contact Phone Number |
| O | Contact.EmailAddress | PER_ALL_PEOPLE_F.email_address | Requestor Contact Email ID |
| O | Contact.Fax | PER_PHONES.phone_number | Requestor Contact Fax Number |
| O | Contact.CommunicationMethod | Constant. In order of preference: 1. EMAIL 2. FAX 3. MANUAL |
Email/Fax/Manual (Phone) |
| O | TransOrderLineDetail.TransOrderLine.ShipFromLocationRef.LocationRef.LocationGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name profile option |
| O | TransOrderLineDetail.TransOrderLine.ShipFromLocationRef.LocationRef.LocationGid.Gid.Xid | Concatenation:SUP-<PO_HEADERS_ALL.vendor_id>-<PO_HEADERS_ALL.vendor_site_id> | SUP-<Supplier ID>_<Site ID> Note: Although the supplier site need not be the ship-from location, this is a required attribute in OTM and therefore is mapped to supplier site. This can however be updated with the true ship-from location downstream in OTM. EBS PO does not currently carry the ship-from location. |
| O | TransOrderLineDetail.TransOrderLine.ShipToLocationRef.LocationRef.LocationGid.Gid.DomainName | Profile Option: OTM_DOMAIN_NAME (OTM: Domain Name) | OTM: Domain Name |
| O | TransOrderLineDetail.TransOrderLine.ShipToLocationRef.LocationRef.LocationGid.Gid.Xid | Concatenation. If internal location:ORG-<PO_LINE_LOCATIONS_ALL.ship_to_organization_id>-<PO_LINE_LOCATIONS_ALL.ship_to_location_id> If drop-ship:CUS-000-<PO_LINE_LOCATIONS_ALL.ship_to_location_id> |
ORG-<Ship-To Org ID>_<Ship-To Location ID> or CUS-000-<Location ID> |
See Concurrent Programs for details, including XML mapping, of the Location XML transaction for carriers.
The following table shows the XML mapping for the Release XML transaction. Elements that are not mapped are not shown. Item, Customer, Customer Site, and Organization reference data is sent using the Order Release Message. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite.)
| Required/Optional | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| R | ReleaseGid | WSH_NEW_DELIVERIES.DELIVERY_ID | Release Global ID |
| R | TransactionCode | "I" for creation, RC for update, and D for delete. | Internally sent to OTM as 'RC' for both Update/Create |
| O | ReleaseHeader.ReleaseName | WSH_NEW_DELIVERIES.NAME | Release name |
| O | ReleaseHeader.ReleaseMethodGid | PREPACK | EBS dictates the contents of the Shipunits |
| O | ReleaseHeader.CommercialTerms | Commercial terms | |
| O | ReleaseHeader.CommercialTerms.PaymentMethodCodeGid | WSH_NEW_DELIVERIES.FREIGHT_TERMS_CODE | Payment method code global ID |
| O | ReleaseHeader.CommercialTerms.IncoTermGid | WSH_NEW_DELIVERIES.FOB_CODE | FOB code |
| O | ReleaseHeader.TimeWindowEmphasisGid | BOTH | Time window emphasis specification |
| O | ReleaseHeader.RateServiceGid | WSH_NEW_DELIVERIES.SERVICE_LEVEL | Rate service global ID |
| O | ReleaseHeader.A.ServiceProviderGid | WSH_NEW_DELIVERIES.CARRIER_ID | Service provider global ID |
| O | ReleaseHeader.B.TransportModeGid | WSH_NEW_DELIVERIES.MODE_OF_TRANSPORT | Transportation mode global ID |
| R | ShipFromLocationRef.LocationGid | DELIVERY SHIP FROM LOCATION GID as per reference data GID | Location global ID |
| O | ShipToLocationRef | ||
| R | ShipToLocationRef.LocationGid | DELIVERY SHIP TO/ULTIMATE DROP-OFF LOCATION GID | Location ID. If the location is mapped to an internal location (e.g., Internal Sales Order) then the internal location ID will be sent. |
| O | TimeWindow.EarlyPickupDate | WSH_NEW_DELIVERIES.EARLIEST_PICKUP_DATE | Early pickup date |
| O | TimeWindow.LatePickupDate | WSH_NEW_DELIVERIES.LATEST_PICKUP_DATE | Late pickup date |
| O | TimeWindow.EarlyDeliveryDate | WSH_NEW_DELIVERIES.EARLIEST_DROPOFF_DATE | Early delivery date |
| O | TimeWindow.LateDeliveryDate | WSH_NEW_DELIVERIES.LATEST_DROPOFF_DATE | Late delivery date |
| R | TotalWeightVolume.Weight | WSH_NEW_DELIVERIES.GROSS_WEIGHTWSH_NEW_DELIVERIES.WEIGHT_UOM_CODE | Total gross weight and UOM |
| R | TotalWeightVolume.Volume | WSH_NEW_DELIVERIES.VOLUMEWSH_NEW_DELIVERIES.VOLUME_UOM_CODE | Total gross volume and UOM |
| R | TotalNetWeightVolume.Weight | WSH_NEW_DELIVERIES.NET_WEIGHTWSH_NEW_DELIVERIES.WEIGHT_UOM_CODE | Total net weight and UOM |
| O | TotalPackagedItemCount | Total quantities of Item regardless of the UOM. A sum of quantities of the item. | |
| O | ReleaseRefnum | WSH_NEW_DELIVERIES.TMS_VERSION_NUMBER[BL] | Delivery revision number Qualifier: REVNUM |
| O | Remarks | WSH_NEW_DELIVERIES.REASON_OF_TRANSPORTWSH_NEW_DELIVERIES.DESCRIPTIONWSH_NEW_DELIVERIES.ADDITIONAL_SHIPMENT_INFOWSH_NEW_DELIVERIES.ROUTING_INSTRUCTIONS | Qualifiers: TRSP_REASON DEL_DESCRIPTION ADD_INFOS ROUTING_INSTR ACTUALS_RECEIVED ACTUALS_RECEIVED is a qualified value used when sending the release transaction in the actual shipment context. (Value =Y) |
The following table shows the XML mapping for the Release XML transaction for delivery lines. Elements that are not mapped are not shown. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite.)
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| R | ReleaseLineGid | WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID | Release line global ID |
| R | PackagedItemGid | DELIVERY DETAIL ITEM GID as per reference data GID | Item GID as per reference data. |
| O | ItemQuantity.ItemTag1 | WSH_DELIVERY_DETAILS.LOT_NUMBER | Item lot number |
| O | ItemQuantity.ItemTag2 | WSH_DELIVERY_DETAILS.SERIAL_NUMBER | Item serial number |
| O | ItemQuantity.ItemTag3 | WSH_DELIVERY_DETAILS.TO_SERIAL_NUMBER | Item to serial number |
| O | ItemQuantity.IsSplitAllowed | N | |
| R | ItemQuantity.WeightVolume.Weight | WSH_DELIVERY_DETAILS.GROSS_WEIGHTWSH_DELIVERY_DETAILS.WEIGHT_UOM_CODE | Note: UOM codes are PUBLIC data in Oracle Transportation Management. Domain is not mapped in this case. |
| R | ItemQuantity.WeightVolume.Volume | WSH_DELIVERY_DETAILS.VOLUMEWSH_DELIVERY_DETAILS.VOLUME_UOM_CODE | Adjusted to the QUANTITY_SHIPPED when ship confirmation occurs. |
| O | ItemQuantity.PackagedItemCount | Delivery not ship confirmed WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY. Delivery ship confirmed WSH_DELIVERY_DETAILS.SHIPPED_QUANTITY |
Packaged item count |
| O | Refnum | WSH_DELIVERY_DETAILS.CUST_PO_NUMBER WSH_DELIVERY_DETAILS.SOURCE_HEADER_NUMBER | Qualifiers: CUST_PO SO_NUM |
The following table shows the XML mapping for the Release XML transaction for ship units. Elements that are not mapped are not shown. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite.)
Note: Item, customer, customer site, and organization reference data is sent using the Order Release Message.
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| R | ShipUnitGid | Unpacked: WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_IDPacked: WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID of the outer most LPN | Unpacked content: One ship unit per delivery detail is generated, thus ship unit and or line use the same ID |
| O | TransactionCode | I when creating a release RC when updating | Transaction code |
| O | ShipUnit.ShipUnitSpecGid | Unpacked: Not mapped Packed: Type of the LPN container item (example STANDARD PALLET) | |
| O | WeightVolume | Unpacked:Delivery detail content gross weight and volumes. Packed: LPN gross Weights and volume. | UOM codes are PUBLIC data in Oracle Transportation Management. Domain is not mapped in this case. |
| O | UnitNetWeightVolume | Packed: LPN net weights and volume. Unpacked: Delivery detail weights and volumes. | |
| O | LengthWidthHeight | LPN container item dimensions | |
| O | ShipUnitSeal | WSH_DELIVERY_DETAILS.SEAL_CODE | Seal code for the ship unit |
| O | ShipUnitContent.LineNumber | Incremental value | Incremental value per content line : 1,2, 3, etc. |
| O | ShipUnitContent.ReleaseGid | WSH_NEW_DELIVERIES.DELIVERY_ID | ReleaseGID of the DD contained in the ship unit |
| O | ShipUnitContent.ReleaseLineGid | WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID | ReleaselineGID of the DD contained in the ship unit |
| O | ShipUnitCount | 1 | Ship unit count |
The following table shows the XML mapping for the Release XML transaction for packaged items. Elements that are not mapped are not shown. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite.)
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| R | PackagedItemGid | DELIVERY_DETAIL_ITEM_GID | Item comes from reference data as a result only. |
The following table shows the XML mapping for the PlannedShipment XML transaction for trips. Elements that are not mapped are not shown. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite.)
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| R | Shipment.ShipmentHeader.ServiceProviderGid.Gid.Xid | WSH_TRIPS.CARRIER_ID | Carrier ID |
| O | Shipment.ShipmentHeader. RateServiceGid.Gid.Xid | WSH_TRIPS.SERVICE_LEVEL | Rate service ID |
| O | Shipment.ShipmentHeader.TransportModeGid.Xid | WSH_TRIPS.MODE_OF_TRANSPORT | Transportation mode ID |
| O | Shipment.ShipmentHeader.CommercialTerms.PaymentMethodCodeGid | WSH_TRIPS.FREIGHT_TERMS_CODE | Payment method global ID |
| O | Shipment.Sequipment.EquipmentGroupGid | WSH_TRIPS.VEHICLE_ITEM_ID | Equipment type global ID |
| O | Shipment.Sequipment.EquipmentInitial | WSH_TRIPS.VEHICLE_NUMBER_PREFIX | Equipment prefix |
| R | Shipment.Sequipment.EquipmentNumber | WSH_TRIPS.VEHICLE_NUMBER | Equipment number |
| O | Shipment.ShipmentHeader.ShipmentGid.Gid.Xid | WSH_TRIPS.TP_PLAN_NAME | Shipment header global ID |
| O | Shipment.Release.ReleaseAllocationInfo.ReleaseAllocByType.ReleaseAllocShipment.TotalAllocCost (XSL Mapping chooses the Cost for ReleaseAllocByType = ‘PLANNING’) |
WSH_FREIGHT_COSTS.TOTAL_AMOUNT | Freight cost allocated at the release level. |
The following table shows the XML mapping for the PlannedShipment XML transaction for trip stops. Elements that are not mapped are not shown. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite.)
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| R | Shipment.ShipmentStop.LocationRef | WSH_TRIP_STOPS.STOP_LOCATION_ID | Trip stop ID |
| O | Shipment.ShipmentStop.StopSequence | WSH_TRIP_STOPS.STOP_SEQUENCE_NUMBER | Trip stop sequence number |
| O | Shipment.ShipmentStop.ArrivalTime.EventTime.PlannedTime | WSH_TRIP_STOPS.PLANNED_ARRIVAL_DATE | Trip stop planned arrival date |
| O | Shipment.ShipmentStop.DepartureTime.EventTime.PlannedTime | WSH_TRIP_STOPS.PLANNED_DEPARTURE_DATE | Trip stop planned departure date |
| O | Shipment.Location.Address.TimezoneGid | WSH_TRIP_STOPS.TIMEZONE_XID | Trip stop time zone |
The following table shows the XML mapping for the PlannedShipment XML transaction for delivery leg. Elements that are not mapped are not shown. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite.)
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| O | ShipUnit.ShipFromLocationRef | WSH_TRIP_STOPS.STOP_LOCATION_ID corresponding to WSH_DELIVERY_LEGS.PICK_UP_STOP_ID | Delivery leg pick up stop ID |
| O | ShipUnit.ShipToLocationRef | WSH_TRIP_STOPS.STOP_LOCATION_ID corresponding to WSH_DELIVERY_LEGS.DROP_OFF_STOP_ID | Delivery leg drop off stop ID |
The following table shows the XML mapping for the ActualShipment XML transaction. Elements that are not mapped are not shown. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite.)
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| R | ShipmentHeader.ShipmentGid | Include for planning WSH_TRIPS.TP_PLAN_NAME Ignore for planning prefix WSH - WSH_TRIPS.TRIP_ID (GID example EBS.WSH-12345) | Shipment global ID |
| O | ShipmentHeader.ShipmentName | WSH_TRIPS.NAME | Shipment name |
| O | ShipmentHeader.ShipmentRefnum | WSH_TRIPS.TRIP_ID WSH_TRIPS.MASTER_BOL_NUMBER WSH_TRIPS.PLANNED_FLAG Total manually entered Freight Costs. Manually entered freight cost currency WSH_TRIPS.OPERATOR | Shipment reference number Qualifiers: TRIP_ID MBOL_NUMBER PLANNED_TRIP MANUAL_FREIGHT_COSTS MAN_FREIGHT_COST_CUR OPERATOR |
| R | ShipmentHeader.TransactionCode | IU: Ignore for planning. | Transaction code |
| O | ShipmentHeader.ServiceProviderGid | GID of WSH_TRIPS.CARRIER_ID | Service provider global ID |
| O | ShipmentHeader.IsServiceProviderFixed | Y: Ignore for planning | Whether or not service provider is fixed. |
| O | ShipmentHeader.TransportModeGid | WSH_TRIPS.MODE_OF_TRANSPORT | Mode of transportation global ID |
| O | ShipmentHeader.TotalWeightVolume.Weight | Sum of TRIP_STOPS. Departure gross weights. NOTE : Rules for trip's Weights and Volumes UOM Get UOM Class defined in Shipping Parameters (Org-specific) Base UOM defined for this UOM Class in Inventory > Units Of Measure > In the Outbound Concurrent Request, we would have to check that for each trip that gets picked-up, the base UOM exists for the first stop location's organization's UOM Class. And for each trip, it's base UOM would be used to calculate total weight/volume at the trip level for Actuals. > End-Users will have to ensure that both forms are defined at set-up time and that OTM has defined this UOM (or UOMs if it's different across Orgs). Note UOM domain: because UOMs are in the PUBLIC domain in OTM we do not map a DOMAIN for UOM GIDs | Total gross weight |
| O | ShipmentHeader.TotalWeightVolume.Volume | Sum of TRIP_STOPS departure volumes | Total gross volume of the trip |
| O | TotalNetWeight.Volume | Total net volume of the trip | |
| O | ShipmentHeader.TotalNetWeightVolume.Weight | Sum of TRIP_STOPS departure net weights | Total net weight |
| O | ShipmentHeader.TotalShipUnitCount | Count of Ship_units = count of outermost LPNs + 1 unpacked content | Ship unit count. |
| O | ShipmentHeader.TotalPackagedItemCount | Total quantity of items regardless of UOM. | Packaged item count for the trip |
| O | CommercialTerms.PaymentMethodCode | WSH_TRIPS.FREIGHT_TERMS_CODE | Payment method code |
| O | ShipmentHeader.Remark | WSH_TRIPS.ROUTING_INSTRUCTIONS | Miscellaneous remarks |
| O | ShipmentHeader.StopCount | Number of stops within the trip. | |
| O | ShipmentHeader2.ShipmentAsWork | N= Include for Planning Y= for Ignore for Planning | |
| O | SEquipment.IntSavedQuery.IntSavedQueryGID | Include for Planning trips only. S_EQUIPEMENT_1 | Saved query global ID |
| O | SEquipment.IntSavedQuery.IntSavedQueryArg.ArgName | Include for Planning trips only. SHIPMENT_GID | |
| O | SEquipment.IntSavedQuery.IntSavedQueryArg.Value | Include for Planning trips only SHIPMENT_GID = DOMAIN.WSH_TRIPS.TP_PLAN_NAME | Saved query value |
| R | SEquipmentGid | Ignore for Planning trips only. SHIPMENT_GID = DOMAIN.WSH-WSH_TRIP.TRIP_ID (for example EBS.WSH-12345). | Equipment Global ID If Ignore, then populate SEquipmentGid.DomainName and SEquipmentGid.Value If Include, then populate IntSavedQueryGid, IntSavedQueryArgValue, IntSavedQueryArgName |
| O | SEquipment.EquipmentInitial | WSH_TRIP.VEHICLE_NUMBER_PREFIX | Equipment prefix |
| O | SEquipment.EquipmentNumber | WSH_TRIP.VEHICLE_NUMBER | Equipment number |
| O | SEquipment.EquipmentGroupGid | Item name of item WSH_TRIP.VEHICLE_ITEM_ID (for example 40ft trailer) | Equipment group global ID |
| O | SEquipment.SEquipmentSeal | WSH_TRIP.SEAL_CODE | Equipment seal code |
| O | SEquipment.SEquipmentSeal.SequenceNumber | Incremental number, 1 because there is one seal number for the trip. | Equipment sequence number. |
| R | ShipmentStop.StopSequence | WSH_TRIP.STOP_SEQUENCE_NUMBER | Stop sequence number |
| O | ShipmentStop.TransactionCode | I: Ignore for planning trips IU: Include for planning | Transaction code |
| O | ShipmentStop.StopDuration | Pick up stop only Difference between start of loading end of loading | Stop duration |
| ShipmentStop.LocationRef.locationGid | GID of WSH_TRIP_STOPS.LOCATION_ID | Location global ID | |
| O | ShipmentStop.ArrivalTime.EventTime.ActualTime.GLogDate | Ignore for planning - pick up stop WSH_TRIP_STOPS.ACTUAL_ARRIVAL_DATE Ignore for Planning - drop stop WSH_TRIP_STOPS.PLANNED_ARRIVAL_DATE Include for Planning - Pick up stop WSH_TRIP_STOPS.ACTUAL_ARRIVAL_DATE | Arrival time |
| O | ShipmentStop.DepartureTime..EventTime.ActualTime.GLogDate | Ignore for planning - Pick up stop WSH_TRIP_STOPS.ACTUAL_DEPARTURE_DATE Ignore for Planning - Drop stop WSH_TRIP_STOPS.PLANNED_DEPARTURE_DATE Include for Planning - Pick up stop WSH_TRIP_STOPS.ACTUAL_DEPARTURE_DATE | Actual arrival time |
| R | ShipmentStop.ShipmentStopDetail.Activity | P if the ship unit is to be picked up D if the ship unit is to be dropped off. | Activity information |
| R | ShipmentStop.ShipmentStopDetail.ShipUnitGid | Release's ShipUnit Gid picked up or dropped off. | Ship unit Global ID |
| O | ShipmentStop.ShipmentStopDetail.Refnum | WSH_TRIP_STOPS.DEPARTURE_SEAL_CODE | Reference number |
The following table shows the mapping of the referenced XML carrier data between Oracle E-Business Suite and Oracle Transportation Management, whether the data is required or optional, and a brief description. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite)
Note: All null fields must contain a tilde (~) and must not be left blank.
| Optional/Required | OTM Element | EBS Table.Column | Description |
|---|---|---|---|
| Corporation | Structure containing corporate information specific to a corporation headquarters. | ||
| O | CorporationName | AR.HZ_PARTIES.PARTY_NAME (via CARRIER_ID) | Corporation name |
| Location | Structure containing place where transportation related activities occur, for example, a loading and unloading freight location. In addition, a location might represent a corporation, and/or a service provider. | ||
| R | Location.TransactionCode | CONSTANT =IU | Specifies how the XML transaction should be processed.” I” adds new data. “U” updates existing data. “IU” updates if present, insert otherwise. “D” to delete.”RC” to replace children. |
| R | Location.LocationGid | DomainName = Profile Option "OTM: Domain Name" value XID = CAR "-" WSH.WSH_CARRIERS.CARRIER_ID |
A global ID that uniquely identifies a location. |
| O | Location.LocationName | AR.HZ_PARTIES.PARTY_NAME (via CARRIER_ID) | Location name. Truncated to 30 characters because of size limitations in OTM. |
| R | Location.Address.CountryCode3Gid | DomainName = PUBLIC XID = Profile Option "WSH: OTM Corporation Country Code value |
The three character ISO country code global identifier. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = ORIGIN |
Location reference number global ID. |
| R | Location.LocationRefnum.LocationRefnumValue | CONSTANT = CARRIER | Location reference number is used to provide smart EBS values. For example, when Qualifier = ORIGIN, Value is the origin EBS data such as Customer, Carrier, or Organization. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = ORIGIN |
Location reference number qualifier global ID. |
| R | Location.LocationRefnum.LocationRefnumValue | WSH.WSH_CARRIERS.CARRIER_ID | Location reference number is used to provide smart EBS values. For example, when Qualifier = CARID, Value is the origin EBS data is the EBS carrier ID known by EBS users. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = CARNM |
Location reference number qualifier Global ID. |
| R | Location.LocationRefnum.LocationRefnumValue | WSH: AR.WSH_CARRIERS.SCAC_CODE | Location reference number is used to provide smart EBS values. For example, when Qualifier = CARNM, Value is the EBS carrier short name known by EBS users. |
| O | Location.LocationRoleGid | DomainName = PUBLIC XID = CARRIER |
A structure specifying a location role. |
| O | Location.Corporation.CorporationName | AR.HZ_PARTIES.PARTY_NAME (via CARRIER_ID) | Corporation name. Truncated to 30 characters because of size limitations in OTM. |
| ServiceProvider | An optional structure containing a location represents a service provider. | ||
| R | ServiceProvider Alias.ServiceProviderAliasQualifierGid | DomainName = PUBLIC XID = SUPPLIER_ID |
Indicates the type of value in the ServiceProviderAliasValue element. |
| R | ServiceProvider Alias.ServiceProviderAliasValue | DomainName = Profile Option OTM: Domain Name value XID = SUP "-" WSH.WSH_CARRIERS.SUPPLIER_ID |
Service provider alias is used to provide EBS information. For example, when Qualifier = SUPPLIER_ID, Value is the EBS link between carrier and supplier. |
Carrier Site XML Mapping
The following table shows the mapping of the referenced XML carrier site data between Oracle E-Business Suite and Oracle Transportation Management, whether the data is required or optional, and a brief description. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite)
Note: All null fields must contain a tilde (~) and must not be left blank.
| Optional/Required | OTM Element | EBS Table.Column | Description |
|---|---|---|---|
| R | Location | Structure containing a place where transportation related activities occur, such as loading and unloading freight. | |
| R | Location.TransactionCode | CONSTANT =IU | TransactionCode specifies how the XML transaction should be processed. “I” adds new data. “U” updates existing data. “IU” updates if present, inserts otherwise. “D” to delete. “RC” to replace children |
| R | Location.LocationGid | DomainName = Profile Option "OTM: Domain Name" value XID= CAR "-" WSH.WSH_CARRIERS.CARRIER_ID "-" AR.HZ_PARTY_SITES.LOCATION_ID |
Global ID that uniquely identifies a location. |
| O | Location.LocationName | AR.HZ_PARTIES.PARTY_NAME (via CARRIER_ID) "," AR.HZ_LOCATIONS.CITY "," AR.HZ_LOCATIONS.STATE "," AR.HZ_LOCATION.COUNTRY |
The total length for Location.LocationName should be 30 characters. Therefore, 10 charcaters from ten character from NAME + "," + ten characters from CITY + "," + four characters from STATE + "," + three characters from COUNTRY. |
| O | Location.Address.AddressLines | AR.HZ_LOCATIONS.ADDRESS1AR.HZ_LOCATIONS.ADDRESS2AR.HZ_LOCATIONS.ADDRESS3AR.HZ_LOCATIONS.ADDRESS4 | Contains the sequence number and address lines. |
| O | Location.Address.City | AR.HZ_LOCATIONS.CITY | City |
| O | Location.Address.Province | AR.HZ_LOCATIONS.STATE Only if length is not 2 letters or AR.HZ_LOCATIONS.PROVINCE Only if length is not 2 letters or AR.HZ_LOCATIONS_ALL.REGION_2 Only if length is not 2 letters or AR.HZ_LOCATIONS_ALL.REGION_1 Only if length is not 2 letters. | Province is a long description corresponding to ProvinceCode. |
| O | Location.Address.ProvinceCode | AR.WSH_REGIONS.STATE Only if length is 2 letters; if not, then nothing or AR.HZ_LOCATIONS.STATE Only if length is 2 letters; if not, then nothing or AR.HZ_LOCATIONS.PROVINCE Only if length is 2 letters; if not, then nothing or AR.HZ_LOCATIONS_ALL.REGION_2 Only if length is 2 letters; if not, then nothing or AR.HZ_LOCATIONS_ALL.REGION_1 Only if length is 2 letters; if not, then nothing. | ProvinceCode is a two character province code. In the United States, ProviceCode corresponds to a two character state code such as PA. |
| O | Location.Address.PostalCode | AR.HZ_LOCATIONS.POSTAL_CODE | PostalCode is the postal code component of Address. For United States, this is a 3, 5 or 9 character zip code. |
| R | Location.Address.CountryCode3Gid | DomainName = PUBLIC XID = FND_TERRITORIES.ISO_TERRITORY_CODEAR |
CountryCode3Gid is the three-character ISO country code global identifier. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID =ORIGIN |
LocationRefnumQualifierGid is a location reference number qualifier global identifier.( For example: Gid.Xid = DUNS) |
| R | Location.LocationRefnum.LocationRefnumValue | CONSTANT = CARRIER | Used to provide smart EBS values. For example, when Qualifier = ORIGIN, Value is the origin EBS data such as Customer, Carrier, or Organization |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = CARID |
Location reference number qualifier global ID. |
| R | Location.LocationRefnum.LocationRefnumValue | WSH.WSH_CARRIERS.CARRIER_ID | Used to provide smart EBS values. For example, when Qualifier = CARID, Value is the EBS carrier ID known by EBS user. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = LOCID |
Location reference number qualifier global ID. |
| R | Location.LocationRefnum.LocationRefnumValue | AR.HZ_PARTY_SITES.PARTY_SITE_NUMBER | Used to provide smart EBS values. For example, when Qualifier = CARNM, Value is the EBS carrier short name known by EBS user. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = LOCID |
Location reference number qualifier global ID. |
| R | Location.LocationRefnum.LocationRefnumValue | AR.HZ_PARTY_SITES.PARTY_SITE_NUMBER | Used to provide smart EBS values. For example, when Qualifier = LOCID, Value is the EBS carrier location ID known by EBS user. |
| O | Location.LocationRoleGid | DomainName = PUBLIC XID = DISPATCH LOCATION |
LocationRole is a structure specifying a location role. Locations may play multiple roles. Examples of location roles include warehouse, crossdock, loading dock, etc. A calendar may be associated with a location role, to limit the times when a part |
| R | Location.ParentLocationGid | DomainName = Profile Option "OTM: Domain Name" value XID = CAR "-" WSH.WSH_CARRIERS.CARRIER_ID |
Within the Location element, the ParentLocationGid specifies the parent corporation for this location. Within the OprationalLocation element, ParentLocationGid specifies the main location to which the operational location belongs. |
The following table shows the mapping of the referenced data between Oracle E-Business Suite and Oracle Transportation Management, whether the data is required or optional, and a brief description of each. (OTM = Oracle Transportation Management. EBS = Oracle E-Business Suite)
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| R | Item.TransactionCode | CONSTANT =IU | TransactionCode specifies how the XML transaction should be processed. “I” adds new data. “U” updates existing data. “IU” updates if present, inserts otherwise. “D” to delete. “RC” to replace children. |
| R | Item.ItemGid | DomainName = Profile Option "OTM: Domain Name" value XID = MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID “-“ MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID |
Item global ID |
| O | Item.ItemName | MTL_SYSTEM_ITEMS_B.segment1..segment15 | Item name Truncated to 30 characters because of size limitations in OTM. |
| O | Item.Description | MTL_SYSTEM_ITEMS_TL.DESCRIPTION | Item description Truncated to 120 characters because of size limitations in OTM |
| R | Packaging.PackagedItemGid | DomainName = Profile Option "OTM: Domain Name" value XID = MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID “-“ MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID |
Packaged item global ID |
| O | Packaging.Description | MTL_SYSTEM_ITEMS_TL.DESCRIPTION | Packaging description Truncated to 120 characters because of size limitations in OTM. |
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| O | CorporationName | AR.HZ_PARTIES.PARTY_NAME (via CUST_ACCOUNT_ID) | Corporation name |
| R | Location.TransactionCode | CONSTANT =IU | TransactionCode specifies how the XML transaction should be processed. “I” adds new data. “U” updates existing data. “IU” updates if present, inserts otherwise. “D” to delete. “RC” to replace children. |
| R | Location.LocationGid | DomainName = Profile Option "OTM: Domain Name" value XID = CUS "-" AR.HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID |
Location global ID |
| O | Location.LocationName | AR.HZ_PARTIES.PARTY_NAME (via CUST_ACCOUNT_ID) | Location name Truncated to 30 characters because of size limitations in OTM. |
| R | Location.Address.CountryCode3Gid | DomainName = PUBLIC XID = Profile Option "OTM: Corporation Country Code" value |
The three character ISO country code global identifier |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = ORIGIN |
Reference number qualifier global ID |
| R | Location.LocationRefnum.LocationRefnumValue | CONSTANT = CUSTOMER | Used to provide smart EBS values. For example, when Qualifier = ORIGIN, Value is the origin EBS data such as Customer, Carrier, or Organization. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = CUSID |
Reference number qualifier global ID |
| R | Location.LocationRefnum.LocationRefnumValue | AR.HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER | Location reference number is used to provide smart EBS values. For example, when Qualifier = CUSID, Value is the origin EBS data is the EBS customer ID known by EBS users. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = CUSNM |
Location reference number qualifier global ID |
| R | Location.LocationRefnum.LocationRefnumValue | AR.HZ_PARTIES.PARTY_NAME (via CUST_ACCOUNT_ID) | Location reference number is used to provide smart EBS values. For example, when Qualifier = CUSNM, Value is the EBS customer name known by EBS users. |
| R | Location.LocationRole.LocationRoleGid | DomainName = PUBLIC XID = CUSTOMER |
Location role global ID |
| O | Location.Corporation.CorporationName | AR.HZ_PARTIES.PARTY_NAME (via CUST_ACCOUNT_ID) | Corporation name |
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| R | Location.TransactionCode | CONSTANT =IU | TransactionCode specifies how the XML transaction should be processed. “I” adds new data. “U” updates existing data. “IU” updates if present, inserts otherwise. “D” to delete. “RC” to replace children |
| R | Location.LocationGid | Location global ID | |
| O | Location.LocationName | AR.HZ_PARTIES.PARTY_NAME (via CUST_ACCOUNT_ID) "," AR.HZ_LOCATIONS.CITY "," AR.HZ_LOCATIONS.STATE "," AR.HZ_LOCATION.COUNTRY |
The total length for Location.LocationName should be 30 characters; therefore, ten character from NAME + "," + ten characters from CITY + "," + four characters from STATE + "," + three characters from COUNTRY |
| O | Location.Address.AddressLines | AR.HZ_LOCATIONS.ADDRESS1AR.HZ_LOCATIONS.ADDRESS2AR.HZ_LOCATIONS.ADDRESS3AR.HZ_LOCATIONS.ADDRESS4 | Contains the sequence number and address lines. |
| O | Location.Address.City | AR.HZ_LOCATIONS.CITY | City |
| O | Location.Address.Province | AR.HZ_LOCATIONS.STATE Only if length is not 2 letters, or AR.HZ_LOCATIONS.PROVINCE Only if length is not 2 letters, or AR.HZ_LOCATIONS_ALL.REGION_2 Only if length is not 2 letters, or AR.HZ_LOCATIONS_ALL.REGION_1 Only if length is not 2 letters | Province is a long description corresponding to ProvinceCode. |
| O | Location.Address.ProvinceCode | AR.WSH_REGIONS.STATE Only if length is 2 letters; if not, then nothing, or AR.HZ_LOCATIONS.STATE Only if length is 2 letters; if not, then nothing, or AR.HZ_LOCATIONS.PROVINCE Only if length is 2 letters; if not, then nothing, or AR.HZ_LOCATIONS_ALL.REGION_2 Only if length is 2 letters; if not, then nothing, or AR.HZ_LOCATIONS_ALL.REGION_1 Only if length is 2 letters; if not, then nothing | A two character province code. In the United States, ProvinceCode corresponds to a two character state code such as PA. |
| O | Location.Address.PostalCode | AR.HZ_LOCATIONS.POSTAL_CODE | Postal code |
| R | Location.Address.CountryCode3Gid | DomainName = PUBLIC XID = FND_TERRITORIES.ISO_TERRITORY_CODEAR | Three character ISO country code global identifier. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID =ORIGIN |
Location reference number qualifier global ID |
| R | Location.LocationRefnum.LocationRefnumValue | CONSTANT = CUSTOMER | Used to provide smart EBS values. For example, when Qualifier = ORIGIN, Value is the origin EBS data such as Customer, Carrier, or Organization |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID =CUSID |
Location reference number qualifier global ID |
| R | Location.LocationRefnum.LocationRefnumValue | AR.HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER | Used to provide smart EBS values. For example, when Qualifier = CUSID, Value is the EBS customer ID known by EBS user |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID =CUSNM |
Location reference number qualifier global ID |
| R | Location.LocationRefnum.LocationRefnumValue | AR.HZ_PARTIES.PARTY_NAME (via CUST_ACCOUNT_ID) | Location reference number is used to provide smart EBS values. For example, when Qualifier = CUSNM, Value is the EBS customer name known by EBS users. |
| R | Location.Contact.ContactGid | DomainName = Profile Option "OTM: Domain Name" value XID = AR.RA_CONTACTS.CONTACT_ID |
Contact global ID |
| O | Location.Contact.TransactionCode | CONSTANT = IU | ) TransactionCode specifies how the XML transaction should be processed. “I” adds new data. “U” updates existing data. “IU” updates if present, inserts otherwise. “D” to delete. “RC” to replace children |
| O | Location.Contact.EmailAddress | AR.HZ_CONTACT_POINTS.EMAIL_ADDRESS | Contact Email address |
| O | Location.Contact.FirstName | AR.HZ.PARTIES.PERSON_FIRST_NAME | Contact first name |
| O | Location.Contact.LastName | AR.HZ.PARTIES.PERSON_LAST_NAME | Contact last name |
| O | Location.Contact.JobTitle | AR.HZ_ORG_CONTACTS.JOB_TITLE | Contact job title |
| O | Location.Contact.Phone1 | AR.HZ_CONTACT_POINTS.PHONE_COUNTRY_CODEAR.HZ_CONTACT_POINTS.PHONE_AREA_CODEAR.HZ_CONTACT_POINTS.PHONE | Contact phone number |
| R | Location.LocationRole.LocationRoleGid | DomainName = Profile Option "OTM: Domain Name" value XID = SHIPFROM / SHIPTO |
Location role global ID |
| R | Location.ParentLocationGid | DomainName = Profile Option "OTM: Domain Name" value XID = CUS "-" AR.HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID |
Parent location global ID |
| R | Location.SubstituteLocationGid | DomainName = Profile Option "OTM: Domain Name" value XID = CUS "-" AR.HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID "-" AR.HZ_LOCATIONS.LOCATION_ID |
Substitute location global ID |
| Optional/Required | OTM Attribute | EBS Table.Column | Description |
|---|---|---|---|
| O | CorporationName | HR.HR_ALL_ORGANIZATION_UNITS.NAME | Corporation name |
| R | Location.TransactionCode | CONSTANT =IU | TransactionCode specifies how the XML transaction should be processed. “I” adds new data. “U” updates existing data. “IU” updates if present, inserts otherwise. “D” to delete. “RC” to replace children. |
| R | Location.LocationGid | DomainName = Profile Option "OTM: Domain Name" value XID = ORG "-" HR.HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID "-" HR.HR_LOCATIONS_ALL.LOCATION_ID |
Location global ID |
| O | Location.LocationName | HR.HR_ALL_ORGANIZATION_UNITS.NAME | Location name Truncated to 30 characters because of size limitations in OTM. |
| O | Location.Address.AddressLines | HR.HR_LOCATIONS_ALL.ADDRESS_LINE_1HR.HR_LOCATIONS_ALL.ADDRESS_LINE_2HR.HR_LOCATIONS_ALL.ADDRESS_LINE_3 | Contains the sequence number and address lines. |
| O | Location.Address.City | HR.HR_LOCATIONS_ALL.TOWN_OR_CITY | City |
| O | Location.Address.Province | HR.HR_LOCATIONS_ALL.REGION_1 | Province is a long description corresponding to ProvinceCode |
| O | Location.Address.ProvinceCode | HR.HR_LOCATIONS_ALL.REGION_2 Only if length is 2 letters; if not, then nothing. | ProvinceCode is a two character province code. In the United States, ProvinceCode corresponds to a two character state code such as PA. |
| O | Location.Address.PostalCode | HR.HR_LOCATIONS_ALL.POSTAL_CODE | Postal code |
| R | Location.Address.CountryCode3Gid | DomainName = PUBLIC XID= HR.HR_LOCATIONS_ALL.COUNTRY |
CountryCode3Gid is the three character ISO country code global identifier |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = ORIGIN |
Location reference number qualifier global ID |
| R | Location.LocationRefnum.LocationRefnumValue | CONSTANT = ORGANIZATION | Used to provide smart EBS values. For example, when Qualifier = ORIGIN, Value is the origin EBS data such as Customer, Carrier, or Organization. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = ORGID |
Location reference number qualifier global ID |
| R | Location.LocationRefnum.LocationRefnumValue | MTL_PARAMETERS.ORGANIZATION_CODE | Location reference number is used to provide smart EBS values. For example, when Qualifier = ORGID, Value is the EBS organization ID known by EBS users. |
| R | Location.LocationRefnum.LocationRefnumQualifierGid | DomainName = PUBLIC XID = ORGNM |
Location reference number qualifier global ID |
| R | Location.LocationRefnum.LocationRefnumValue | HR.HR_ALL_ORGANIZATION_UNITS.NAME | Location reference number is used to provide smart EBS values. For example, when Qualifier = ORGNM, Value is the EBS organization name known by EBS users. |
| R | Location.Contact.ContactGid | DomainName = Profile Option "OTM: Domain Name" value XID = ORG "-" HR.HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID "-" HR.HR_LOCATIONS_ALL.LOCATION_ID |
Contact global ID |
| O | Location.Contact.TransactionCode | CONSTANT = IU | ) TransactionCode specifies how the XML transaction should be processed. “I” adds new data. “U” updates existing data. “IU” updates if present, inserts otherwise. “D” to delete. “RC” to replace children. |
| O | Location.Contact.Phone1 | HR.HR_LOCATIONS_ALL.TELEPHONE_NUMBER_1 | Contact phone number |
| O | Location.Contact.Phone2 | HR.HR_LOCATIONS_ALL.TELEPHONE_NUMBER_2 | Contact phone number |
| O | Location.Contact.Fax | HR.HR_LOCATIONS_ALL.TELEPHONE_NUMBER_3 | Contact fax number |
| R | Location.LocationRole.LocationRoleGid | DomainName = Profile Option "OTM: Domain Name" value XID = SHIPFROM / SHIPTO |
Location role global ID |
| O | Location.Corporation.CorporationName | HR.HR_ALL_ORGANIZATION_UNITS.NAME | Corporation name |
| R | Location.Corporation.VatRegistration.CountryCode3Gid | DomainName = PUBLIC XID = HR.HR_LOCATIONS_ALL.COUNTRY |
Country code |
| O | Location.IsShipperKnown | CONSTANT = Y |