Import Addresses into Order Management

Import Order Header Attributes

Populate bill-to attributes on the order header.

Bill-to Customer
Bill-to Account

Populating bill-to attributes on the order header.

Use attributes on the DOO_ORDER_ADDRESSES_INT tab.

Attribute	Value
Address Use Type	BILL_TO
Customer Name	Name of customer you're importing, such as Computer Service and Rentals. Use this attribute only when Address Use Type contains BILL_TO.
Account Site Identifier	Value that uniquely identifies the account, such as 300000001469016. Use this attribute only when Address Use Type contains BILL_TO.
Source Transaction Line Identifier	Leave empty.

Populate ship-to attributes on the order header.

Ship-to Customer
Ship-to Address

Populating ship-to attributes on the order header.

Use attributes on the DOO_ORDER_ADDRESSES_INT tab.

Attribute	Value
Address Use Type	SHIP_TO
Party Name	Name of the party you're importing, such as Computer Service and Rentals. Use this attribute only when Address Use Type contains SHIP_TO.
Party Site Identifier	Value that uniquely identifies the party site address, such as 300000001469004. Use this attribute only when Address Use Type contains SHIP_TO.
Source Transaction Line Identifier	Leave empty.

Import Order Line Attributes

Populate Bill-To Attributes on the Order Line

Bill-to Customer
Bill-to Address

Populating bill-to attributes on the order line.

Set the same values that you use to populate order header attributes, except set this attribute.

Attribute	Description
Source Transaction Line Identifier	Value that uniquely identifies the transaction line in the source system, such as 1.

Note

Make sure you set Source Transaction Line Identifier on DOO_ORDER_LINES_ALL_INT and on DOO_ORDER_ADDRESSES_INT to the same value.
The import uses Source Transaction Line Identifier to create a relationship between the address on DOO_ORDER_ADDRESSES_INT with the order line on DOO_ORDER_LINES_ALL_INT.

Set a value for Source Transaction Line Identifier only if your Ship-to Address on the order line must be different than the Ship-to Address on the order header.

If the Ship-to Address is the same for the order header and all order lines, then leave these attributes empty on the DOO_ORDER_ADDRESSES_INT tab.

Source Transaction Line Identifier
Source Transaction Schedule Identifier

Set the Address Use Type

If you set the Address Use Type attribute to BILL_TO or SHIP_TO, then the template uses these values to determine which row to use when it populates the Bill_to and Ship_to attributes on the order header. For example, if you set Address Use Type to SHIP_TO on row 5 of DOO_ORDER_ADDRESSES_INT, and if the Address Line1 attribute on row 5 contains 123 Main Street, then the import sets the Ship-to Address address on the order header to 123 Main Street, and it cascades this value from the order header to the Ship-to Address on all order lines.

If you import a sales order and find that attributes on the order header are empty, its possible that you set the values for Source Transaction Line Identifier and Source Transaction Schedule Identifier on DOO_ORDER_ADDRESSES_INT but not for Address Use Type and other attributes that the import uses to populate values on the order header, such as Party Site Identifier and Address Line1.

You can also set Address Use Type to:

DESTINATION_SHIPPING_TO. If you use this value, then the template uses the value in the Location Identifier attribute on the DOO_ORDER_ADDRESSES_INT worksheet to determine the shipping destination to use.
FINAL_DISCHARGE_TO. If you use this value, then the template uses the value in the Location Identifier attribute on the DOO_ORDER_ADDRESSES_INT worksheet to determine the discharge location to use. Use this value to specify the location that you want to use when you calculate tax for the item on the invoice. For details, see the Manage Location of Final Discharge section in Oracle Financials Cloud, Implementing Tax.

Populate Ship-To Attributes on the Order Line

Ship-to Customer
Ship-to Address

Populating ship-to attributes on the order line.

Set Bill-to and Ship-to On the Same Order Line

Setting Bill-to and Ship-to On the Same Order Line.

Get Values for Identifier Attributes

Make sure you use the correct value for the Account Site Identifier attribute and the Party Site Identifier attribute. Use SQL to query the Order Management database to get these values.

Here's the SQL to run.

SELECT  hzp.party_name
        ||
        ' '
        ||
        hzp.party_number                                ,
        hzp.party_id                                    ,
        HZA.account_number                              ,
        HZA.account_name                                ,
        hza.CUST_ACCOUNT_ID                             ,
        HZA.status "Account Status"                     ,
        hzp.status "Party Status"                       ,
        hzps.status "Party Site Status"                 ,
        hzps.party_site_id "PARTY SITE ID - for SHIP_TO",
        hzcasa.status "Account Site Status"             ,
        hzcsua.site_use_id "SITE USE ID - for BILL_TO"  ,
        hzcasa.start_Date                               ,
        hzcasa.end_Date                                 ,
        hzcsua.SITE_USE_CODE                            ,
        hzcasa.BILL_TO_FLAG                             ,
        hzcasa.SHIP_TO_FLAG                             ,
        hzcsua.PRIMARY_FLAG                             ,
        hzcsua.STATUS "Account Site USE Status"         ,
        hzcsua.LOCATION                                 ,
        hzl.ADDRESS1                                    ,
        hzl.ADDRESS2                                    ,
        hzl.ADDRESS3                                    ,
        hzl.ADDRESS4                                    ,
        hzl.CITY                                        ,
        hzl.POSTAL_CODE                                 ,
        hzl.STATE                                       ,
        hzl.COUNTRY
FROM    fusion.HZ_PARTIES HZP               ,
        fusion.HZ_PARTY_SITES hzps          ,
        fusion.HZ_CUST_ACCOUNTS HZA         ,
        fusion.HZ_CUST_ACCT_SITES_ALL hzcasa,
        fusion.HZ_CUST_SITE_USES_ALL hzcsua ,
        fusion.hz_locations HZL
WHERE   hzP.party_id                 = HZA.party_id (+)
        AND hza.CUST_ACCOUNT_ID      = hzcasa.CUST_ACCOUNT_ID (+)
        AND hzcasa.party_site_id     = hzps.party_site_id (+)
        AND hzcasa.cust_acct_site_id = hzcsua.cust_acct_site_id (+)
        AND hzps.location_id         = hzl.location_id (+)
        --         and       (
        --                      hzcasa.start_Date    <= sysdate
        --                        OR hzcasa.start_Date IS NULL
        --                )
        --                AND
        --                (
        --                        hzcasa.end_date    >= sysdate
        --                        OR hzcasa.end_date IS NULL
        --                )
        AND upper(hzP.party_name) LIKE upper('%GOO%IND%')
ORDER BY hzp.party_number ,
        hza.account_number,
        hzl.LOCATION_ID

Note

Examine the SITE_USE_CODE column.
For ship_to, use the value that PARTY SITE ID - for SHIP_TO returns.
For bill_to, use the value that SITE USE ID - for BILL_TO returns.
This SQL also returns inactive account details.

In your SQL, replace the placeholder with a value from your order import template.

Placeholder	Attribute on the DOO_ORDER_ADDRESSES_INT Tab
`party_name`	Party Name
`party_number`	Party Number
`party_id`	Party Identifier
`account_number`	Not applicable
`account_name`	Not applicable