PER Location Upload

➜ Back to Library
Description

PER Location Upload creates, updates and deletes Oracle EBS HR locations from Excel. These are the global and business-group locations shared across HR, Purchasing, Inventory and other modules. The upload covers country-specific addresses, site usage flags, inventory organization, designated receiver, time zone and the location descriptive flexfield, and uses the standard Oracle HR location API.

When to use it

  • Mass-create new HR locations, for example when you open a new set of sites or start operating in a new country.
  • Bulk-update the descriptions, addresses, telephone numbers or time zones of existing locations.
  • Enable or disable site usages (Bill-To, Ship-To, Receiving, Office, Internal) across many locations.
  • Re-point Ship-To relationships between locations.
  • Inactivate locations, or delete unused ones.
  • Maintain the location descriptive flexfield values in bulk.

Before you start

  • Access to the upload in Blitz Report, from a responsibility whose HR:Business Group profile is the business group you work in. The download shows that business group’s locations plus all global locations.
  • Address styles, countries, time zones, inventory organizations and employees (for the designated receiver) already exist.
  • For addresses with validated fields (for example City, County and State of the United States address style), the values exist in the address validation data of your system.

Step 1 – Set the parameters

Open PER Location Upload in Blitz Report and set the parameters:

ParameterMeaning
Upload ModeCreate opens an empty sheet for new locations; Create, Update (default) downloads existing locations for editing and lets you add new ones. Each row is created or updated depending on whether its Location Code already exists.
Default Address StyleThe address style used for new rows. It also sets the address column headings, which take the prompts of this style (for example City, County, State and Zip Code for United States). Defaults from the country of your business group.
Location NameDownloads the selected location(s).
Location Name LikeDownloads all locations whose code matches this text, for example H1-%.
Location StatusActive or Inactive locations only.
CountryLocations in this country only.
GlobalYes downloads only global locations, No only the locations of your business group.
Legal AddressYes downloads only legal address locations, No excludes them.
Bill To Site, Ship To Site, Receiving Site, Internal Site, Office SiteSet to Yes to download only locations with that site usage.
Blitz Report run screen for PER Location Upload with Upload Mode Create, Update, Default Address Style United States, Location Name Like H1-% and Country United States

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with one row per location, or an empty sheet in Create mode.

Excel file with the existing H1 locations, their descriptions, addresses and site usage flags

Step 3 – Enter or change the locations

Location Code identifies the location. Change any other column of a downloaded row, or add a row with a new Location Code, a Description, the Address Style (defaults from the Default Address Style parameter) and its address fields, for example Address Line 1, City, State, County, Zip Code and Country for United States. Address fields are validated against the address style when you create a location or change an address field. Fill the State before the County, as the County list depends on it.

Set the site usages with Bill To Site Flag, Office Site Flag, In Organization Flag (internal site) and Receiving Site Flag, and optionally the Inventory Organization, the designated receiver in Contact and the Timezone Code. A blank flag is stored as No. Leave Ship To Location blank to make the location its own ship-to location; this sets Ship To Site Flag and Receiving Site Flag to Yes. Entering another location as Ship To Location sets Ship To Site Flag to No.

Business Group is read-only on downloaded rows. On a new row leave it blank to create a global location, or select your business group to make the location local to it. Legal Address Flag is for information only. To inactivate a location enter an Inactive Date; to delete an unused location set Delete Location to Yes.

The default template leaves out the location descriptive flexfield columns. To maintain them, add them to your template with Edit Template.

In the example below, the description and telephone of H1-Redwood City and the telephone of H1-Vintage Oaks are changed, so both rows are marked Update.

Excel file with the description and telephone of H1-Redwood City and the telephone of H1-Vintage Oaks changed, both rows marked Update

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and saves the file. Correct any rows it flags before uploading.

Both changed rows show status Valid after Validate and Save

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which creates, updates or deletes each location.

File Upload page with the saved Excel file selected for upload

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status and message.

Result report showing locations H1-Redwood City and H1-Vintage Oaks updated with status Success

What’s produced

  • HR locations created, updated, inactivated or deleted, with their addresses, site usages and flexfield values.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Are these the locations used by Purchasing and Inventory, or HR-only locations?
They are the standard HR locations used across HR, Purchasing, Inventory and other modules. Leave Business Group blank on a new row for a global location, or select your business group for a local one.

Why did my Ship To Site Flag or Receiving Site Flag change to something I didn’t enter?
They follow the Ship To Location. A blank Ship To Location, or the location itself, sets both flags to Yes. Another location as Ship To Location sets Ship To Site Flag to No, and Receiving Site Flag then keeps the value you entered.

Why are some address columns named City, County or State instead of Town Or City and Region?
The address column headings take the prompts of the Default Address Style. Rows can still use other address styles; each row’s fields are validated against its own Address Style.

Can I set the Legal Address Flag?
No. It is set when the location is used as a legal entity address, and is shown for information only.

Why can’t I set an Inactive Date on some locations?
Legal address locations cannot be inactivated.

Can I delete any location?
Only locations that are not used anywhere, for example by assignments, organizations or positions. Legal address locations cannot be deleted.

A row says “No changes detected.” – did anything happen?
No. The location already had the uploaded values, so it was not updated.

Troubleshooting

MessageCauseWhat to do
Location Code is required.The Location Code is blank.Enter a unique Location Code.
Cannot set Inactive Date on a Legal Address location.An Inactive Date on a legal address location.Remove the Inactive Date; legal address locations cannot be inactivated.
Location ‘…’ to delete does not exist.Delete Location is Yes for a Location Code that does not exist.Check the Location Code; only existing locations can be deleted.
Address validation error: …An address field is missing or not valid for the row’s Address Style, for example a City, County or State that is not in the address validation data.Correct the address fields, choosing validated values from the column lists, or change the Address Style.
Location DFF validation error: …A descriptive flexfield value is not valid.Pick valid values for the chosen context.
ORA-29273: HTTP request failedWith iRecruitment installed and the IRC: Geocode Host profile set, Oracle geocodes every new or changed address through an external web service, which could not be reached.Ask your system administrator to correct the IRC: Geocode Host profile or the server’s network and certificate setup.
select
null action_,
null status_,
null message_,
null request_id_,
null modified_columns_,
hla.location_id,
hla.location_code,
hlat.description,
hla.inactive_date,
to_char(null) delete_location,
pbg.name business_group,
xxen_util.meaning(case when hla.legal_address_flag='Y' then 'Y' end,'YES_NO',0) legal_address_flag,
xxen_util.display_flexfield_context(800,'Address Location',hla.style) address_style,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'ADDRESS_LINE_1',hla.rowid,hla.address_line_1) address_line_1,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'ADDRESS_LINE_2',hla.rowid,hla.address_line_2) address_line_2,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'ADDRESS_LINE_3',hla.rowid,hla.address_line_3) address_line_3,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'TOWN_OR_CITY',hla.rowid,hla.town_or_city) town_or_city,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'REGION_1',hla.rowid,hla.region_1) region_1,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'REGION_2',hla.rowid,hla.region_2) region_2,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'REGION_3',hla.rowid,hla.region_3) region_3,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'POSTAL_CODE',hla.rowid,hla.postal_code) postal_code,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'COUNTRY',hla.rowid,hla.country) country,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'TELEPHONE_NUMBER_1',hla.rowid,hla.telephone_number_1) telephone_number_1,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'TELEPHONE_NUMBER_2',hla.rowid,hla.telephone_number_2) telephone_number_2,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'TELEPHONE_NUMBER_3',hla.rowid,hla.telephone_number_3) telephone_number_3,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'LOC_INFORMATION13',hla.rowid,hla.loc_information13) loc_information13,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'LOC_INFORMATION14',hla.rowid,hla.loc_information14) loc_information14,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'LOC_INFORMATION15',hla.rowid,hla.loc_information15) loc_information15,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'LOC_INFORMATION16',hla.rowid,hla.loc_information16) loc_information16,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'LOC_INFORMATION17',hla.rowid,hla.loc_information17) loc_information17,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'LOC_INFORMATION18',hla.rowid,hla.loc_information18) loc_information18,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'LOC_INFORMATION19',hla.rowid,hla.loc_information19) loc_information19,
xxen_util.display_flexfield_value(800,'Address Location',hla.style,'LOC_INFORMATION20',hla.rowid,hla.loc_information20) loc_information20,
hla2.location_code ship_to_location,
xxen_util.meaning(case when hla.ship_to_site_flag='Y' then 'Y' end,'YES_NO',0) ship_to_site_flag,
xxen_util.meaning(case when hla.receiving_site_flag='Y' then 'Y' end,'YES_NO',0) receiving_site_flag,
xxen_util.meaning(case when hla.bill_to_site_flag='Y' then 'Y' end,'YES_NO',0) bill_to_site_flag,
xxen_util.meaning(case when hla.office_site_flag='Y' then 'Y' end,'YES_NO',0) office_site_flag,
xxen_util.meaning(case when hla.in_organization_flag='Y' then 'Y' end,'YES_NO',0) in_organization_flag,
mp.organization_code inventory_organization,
ppf.full_name contact,
(select ftv.name from fnd_timezones_vl ftv where ftv.timezone_code=hla.timezone_code) timezone_code,
hla.ece_tp_location_code,
xxen_util.display_flexfield_context(800,'HR_LOCATIONS',hla.attribute_category) loc_attribute_category,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE1',hla.rowid,hla.attribute1) loc_attribute1,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE2',hla.rowid,hla.attribute2) loc_attribute2,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE3',hla.rowid,hla.attribute3) loc_attribute3,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE4',hla.rowid,hla.attribute4) loc_attribute4,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE5',hla.rowid,hla.attribute5) loc_attribute5,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE6',hla.rowid,hla.attribute6) loc_attribute6,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE7',hla.rowid,hla.attribute7) loc_attribute7,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE8',hla.rowid,hla.attribute8) loc_attribute8,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE9',hla.rowid,hla.attribute9) loc_attribute9,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE10',hla.rowid,hla.attribute10) loc_attribute10,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE11',hla.rowid,hla.attribute11) loc_attribute11,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE12',hla.rowid,hla.attribute12) loc_attribute12,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE13',hla.rowid,hla.attribute13) loc_attribute13,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE14',hla.rowid,hla.attribute14) loc_attribute14,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE15',hla.rowid,hla.attribute15) loc_attribute15,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE16',hla.rowid,hla.attribute16) loc_attribute16,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE17',hla.rowid,hla.attribute17) loc_attribute17,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE18',hla.rowid,hla.attribute18) loc_attribute18,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE19',hla.rowid,hla.attribute19) loc_attribute19,
xxen_util.display_flexfield_value(800,'HR_LOCATIONS',hla.attribute_category,'ATTRIBUTE20',hla.rowid,hla.attribute20) loc_attribute20,
to_number(null) upload_row
from
hr_locations_all hla,
hr_locations_all_tl hlat,
hr_locations_all hla2,
mtl_parameters mp,
per_all_people_f ppf,
per_business_groups pbg
where
1=1 and
nvl(hla.business_group_id,nvl(hr_general.get_business_group_id,-99))=nvl(hr_general.get_business_group_id,nvl(hla.business_group_id,-99)) and
hla.location_id=hlat.location_id and
hlat.language=userenv('lang') and
hla.ship_to_location_id=hla2.location_id(+) and
hla.inventory_organization_id=mp.organization_id(+) and
hla.designated_receiver_id=ppf.person_id(+) and
hla.business_group_id=pbg.business_group_id(+)
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode like '%' || xxen_upload.action_update
LOV
Default Address Style
:default_address_style=:default_address_style
LOV
Location Name
hla.location_code=:location_name
LOV
Location Name Like
lower(hla.location_code) like lower(:location_name_like)
Char
Location Status
nvl(hla.inactive_date,trunc(sysdate))>=trunc(sysdate)
LOV
Country
hla.country=(select ftv.territory_code from fnd_territories_vl ftv where ftv.territory_short_name=:country)
LOV
Global
hla.business_group_id is null
LOV Oracle
Legal Address
hla.legal_address_flag='Y'
LOV Oracle
Bill To Site
hla.bill_to_site_flag=:bill_to_site
LOV
Ship To Site
hla.ship_to_site_flag=:ship_to_site
LOV
Receiving Site
hla.receiving_site_flag=:receiving_site
LOV
Internal Site
hla.in_organization_flag=:internal_site
LOV
Office Site
hla.office_site_flag=:office_site
LOV
Download
Related Blitz Reports
CSD Depot Repair Traveler FND Attached Documents PO Savings Analysis Report(by Category) PO Receiving Transactions Register PO Receipt Adjustments PO Substitute Receipts PO Receiving Exceptions PO Overshipments PO Purchase Requisition Status
Blitz Report™