PER Location Upload

➜ Back to Library
Description

PER Location Upload creates, updates and deletes Oracle EBS HR Locations from Excel – the global and business-group locations shared across HR, Purchasing, Inventory and other modules – including country-specific addresses, site-usage flags, designated receiver, time zone and the location descriptive flexfield, via the standard Oracle public API.

When to use it

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

Before you start

  • Blitz Report is installed and you are signed in to a responsibility tied to the intended business group.
  • Address styles, time zones, countries, inventory organizations and employees (for the designated receiver) already exist.
  • Locations download for your current business group plus global locations.

Step 1 – Choose a mode, set the parameters and download

In Blitz Report, open PER Location Upload, choose an Upload Mode (Create, Update – the default – downloads existing locations to edit; Create gives an empty template), and set the parameters:

ParameterPurpose
Upload ModeCreate or Create, Update. Default is Create, Update.
Default Address StyleThe default country address format for new rows (required).
Location Name / Like / Status / Country / Global / site flagsRestrict which existing locations are downloaded.

Run the upload to download and open the Excel file. Each location is automatically handled as a create or an update depending on whether the Location Code already exists.

Step 2 – Enter the locations

On each row enter the Location Code (the unique key) and a Description, choose the Address Style and fill its address fields (Address Line 1, Town or City, Postal Code, Country, etc.), and set the site-usage flags, Inventory Organization, designated receiver (Contact) and Time Zone as needed. For a new location, leave Business Group blank to make it global, or pick one to make it local.

Step 3 – Delete locations (optional)

To remove a location, set Delete Location to Yes on its row. The location must already exist, and legal-address locations cannot be deleted.

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and runs the upload’s validation (including address validation against the chosen style), then saves the file. Correct anything it flags before continuing.

Step 5 – Upload and view the result

Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which creates, updates or deletes each location. When it finishes, a result report opens showing each row as success or error.

What’s produced

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

Common questions

Are these the global locations used by Purchasing and Inventory, or HR-only locations?
They are the standard HR locations used across HR, PO, INV and more. Leave Business Group blank for a global location; assign one for a local location.

Why did my Ship-To / Receiving flags change to something I didn’t enter?
They are auto-corrected. A self-referencing (blank or same) Ship-To Location forces Ship-To = Yes and Receiving = Yes; pointing to another location forces Ship-To = No; whenever Ship-To = Yes, Receiving is forced to Yes.

Can I set the Legal Address flag?
No. It is information only and cannot be set or changed by the upload.

Why can’t I set an Inactive Date on some locations?
Legal-address locations are protected from inactivation (and deletion).

A row says “No changes detected” – did anything happen?
No. The location already matched the uploaded values, so no update was performed.

Troubleshooting

MessageCauseWhat to do
Location Code is requiredThe Location Code cell was left blank.Enter a unique Location Code (it is the required key).
Cannot set Inactive Date on a Legal Address locationTried to inactivate a legal-address location.Remove the Inactive Date; legal-address locations can’t be inactivated.
Location to delete does not existDelete = Yes for a location code that isn’t found.Verify the code; only existing locations can be deleted.
Address validation errorThe address fails the rules of the selected address style.Correct the address fields for that country/style, or change the address style.
Location flexfield validation errorAn invalid location-attribute value.Pick valid flexfield values for the chosen attribute category.
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™