AP Distribution Set Upload

➜ Back to Library
Description

AP Distribution Set Upload creates and updates Oracle Payables distribution sets and their lines in bulk from Excel – the equivalent of the Distribution Sets window (Setup > Invoice > Distribution Sets). Oracle provides no interface or API for distribution sets, so this upload applies the same rules as the window: the line percentages of a set must add up to 100 (Full) or 0 (Skeleton), and each set is saved completely or not at all.

When to use it

  • Set up distribution sets for a new operating unit or during a data migration.
  • Rebalance the percentages or change the accounts of existing sets, for example after a department restructuring.
  • Copy a set to another operating unit, or build similar sets from an existing one.
  • Deactivate many sets at once by setting an inactive date.

Before you start

  • You are signed in to a Payables responsibility with access to the operating units you load, and the Payables options are defined for them.
  • For project related lines, Oracle Projects is installed and the project, task, expenditure type and expenditure organization are valid for Payables.

Step 1 – Set the parameters and download

In Blitz Report, open AP Distribution Set Upload and set the parameters:

ParameterPurpose
Upload ModeCreate (empty template for new sets) or Create, Update (existing sets downloaded for editing). Default is Create, Update.
Operating UnitRestrict the download to one operating unit.
Distribution SetRestrict the download to one distribution set.

Run the upload to download and open the Excel file. There is one row per distribution set line, and the set’s header columns (Operating Unit, Distribution Set, Description, Inactive On) are repeated on each of its lines.

Step 2 – Enter or edit the sets

  • Header – enter the Operating Unit and Distribution Set name, and optionally the Description, Inactive On date and descriptive flexfield values. The header values are taken from the first row of each set, so enter header changes there.
  • Lines – on each row enter the Line Number, Percentage and Account, and optionally the Line Description, Income Tax Type and descriptive flexfield values. The line number identifies the line within its set.
  • Project related lines – enter Project, Task, Expenditure Type and Expenditure Organization together and leave the Account blank. The account is derived by the Projects account generator, as in the Distribution Sets window.
  • Delete Line – set this column to Yes to delete a line of an existing set.
  • Header only – leave all line columns blank to create or update a set header without lines.

Each set must end up with line percentages adding up to 100 or 0. The check includes existing lines that are not in your file.

Step 3 – Validate and Save

Click Validate and Save. This checks for missing required values and invalid list values, then saves the file. Correct anything it flags before continuing.

Step 4 – Upload and view the result

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

What’s produced

  • Created or updated distribution sets and lines, and deleted lines, in the chosen operating units. Each set’s type (Full or Skeleton) is set from its line total.
  • A result report listing every row with a status and a message, for example “Distribution set created. Line 1 created.”, “Line 2 updated.”, “Line 3 deleted.” or “No change.”.

Common questions

Why were rows with no errors of their own not saved?
A distribution set is saved as a whole. If any of its rows fails, or its line percentages do not add up to 100 or 0, none of its rows are saved, and the other rows show “Not saved because of errors in other rows of Distribution Set …”. Correct the failing row and upload the set again.

Can I delete a distribution set?
No – as in the Distribution Sets window, a set cannot be deleted. Enter an Inactive On date to stop it being used. Individual lines can be deleted with the Delete Line column.

How do I rename a set?
Download it in Create, Update mode and change the Distribution Set name on all of its rows. The downloaded rows identify the existing set, so it is renamed rather than a new one created.

How do I copy a set?
Copy its downloaded rows, give them a new Distribution Set name (or another Operating Unit) and set the Action to Create. A new set is created and the original is not changed.

Can I enter my own account on a project related line?
No. The account of a project related line is always derived from the project details, as in the Distribution Sets window.

My row says “No change.” – did anything happen?
No update was needed: none of the values differed from what is already stored. This is normal, not an error.

Troubleshooting

MessageCauseWhat to do
The line percentages of Distribution Set … add up to … They must add up to 100 or 0.The resulting line total of the set, including existing lines not in the file, is neither 100 nor 0.Adjust the percentages, or delete or add lines, so the set totals 100 or 0.
Distribution Set … already exists in Operating Unit …A new or renamed set has the name of another set in the operating unit. Names are compared regardless of upper and lower case.Choose a different name, or download the existing set to update it.
Line Number is required.A row has line values but no line number.Enter the line number.
Line Number … occurs more than once for this distribution set.Two rows of the same set have the same line number.Give each line its own number.
Percentage is required.A new line has no percentage.Enter the percentage.
Account is required.A line that is not project related has no account.Enter the account, or the project details for a project related line.
Account … is invalidThe account combination fails the accounting flexfield validation.Correct the account segments.
Account … does not allow detail posting.The account is a summary or parent account.Use a detail account.
Project, Task, Expenditure Type and Expenditure Organization are required for a project related line.Only some of the project details were entered.Enter all four, or clear all four for an account-based line.
The account of a project related line is derived from the project details: …An account different from the derived one was entered on a project related line.Clear the Account column.
Inactive On cannot be in the past.The inactive date entered is before today.Enter today or a future date.
Line … does not exist.Delete Line was set for a line number that the set does not have.Check the line number, or download the set again.
Header values are taken from the first row of the distribution set. Enter header changes on its first row.A header column was changed on a later row of the set.Make the change on the set’s first row.
select
null action_,
null status_,
null message_,
null modified_columns_,
haouv.name operating_unit,
adsa.distribution_set_name distribution_set,
(select alc.displayed_field from ap_lookup_codes alc where alc.lookup_type='NLS TRANSLATION' and alc.lookup_code='PERCENT DIST '||adsa.total_percent_distribution) set_type,
adsa.description,
adsa.inactive_date inactive_on,
xxen_util.display_flexfield_context(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category) set_attribute_category,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE1',adsa.rowid,adsa.attribute1) set_attribute1,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE2',adsa.rowid,adsa.attribute2) set_attribute2,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE3',adsa.rowid,adsa.attribute3) set_attribute3,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE4',adsa.rowid,adsa.attribute4) set_attribute4,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE5',adsa.rowid,adsa.attribute5) set_attribute5,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE6',adsa.rowid,adsa.attribute6) set_attribute6,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE7',adsa.rowid,adsa.attribute7) set_attribute7,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE8',adsa.rowid,adsa.attribute8) set_attribute8,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE9',adsa.rowid,adsa.attribute9) set_attribute9,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE10',adsa.rowid,adsa.attribute10) set_attribute10,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE11',adsa.rowid,adsa.attribute11) set_attribute11,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE12',adsa.rowid,adsa.attribute12) set_attribute12,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE13',adsa.rowid,adsa.attribute13) set_attribute13,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE14',adsa.rowid,adsa.attribute14) set_attribute14,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SETS',adsa.attribute_category,'ATTRIBUTE15',adsa.rowid,adsa.attribute15) set_attribute15,
adsla.distribution_set_line_number line_number,
adsla.percent_distribution percentage,
gcck.concatenated_segments account,
xxen_util.segments_description(gcck.code_combination_id) account_description,
adsla.description line_description,
adsla.type_1099 income_tax_type,
ppa.segment1 project,
pt.task_number task,
adsla.expenditure_type,
haouv_exp.name expenditure_organization,
xxen_util.display_flexfield_context(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category) line_attribute_category,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE1',adsla.rowid,adsla.attribute1) line_attribute1,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE2',adsla.rowid,adsla.attribute2) line_attribute2,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE3',adsla.rowid,adsla.attribute3) line_attribute3,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE4',adsla.rowid,adsla.attribute4) line_attribute4,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE5',adsla.rowid,adsla.attribute5) line_attribute5,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE6',adsla.rowid,adsla.attribute6) line_attribute6,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE7',adsla.rowid,adsla.attribute7) line_attribute7,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE8',adsla.rowid,adsla.attribute8) line_attribute8,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE9',adsla.rowid,adsla.attribute9) line_attribute9,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE10',adsla.rowid,adsla.attribute10) line_attribute10,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE11',adsla.rowid,adsla.attribute11) line_attribute11,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE12',adsla.rowid,adsla.attribute12) line_attribute12,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE13',adsla.rowid,adsla.attribute13) line_attribute13,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE14',adsla.rowid,adsla.attribute14) line_attribute14,
xxen_util.display_flexfield_value(200,'AP_DISTRIBUTION_SET_LINES',adsla.attribute_category,'ATTRIBUTE15',adsla.rowid,adsla.attribute15) line_attribute15,
null delete_line,
adsa.distribution_set_id,
null upload_row
from
ap_distribution_sets_all adsa,
hr_all_organization_units_vl haouv,
ap_distribution_set_lines_all adsla,
gl_code_combinations_kfv gcck,
pa_projects_all ppa,
pa_tasks pt,
hr_all_organization_units_vl haouv_exp
where
1=1 and
adsa.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and
adsa.org_id=haouv.organization_id and
adsa.distribution_set_id=adsla.distribution_set_id(+) and
adsla.dist_code_combination_id=gcck.code_combination_id(+) and
adsla.project_id=ppa.project_id(+) and
adsla.task_id=pt.task_id(+) and
adsla.expenditure_organization_id=haouv_exp.organization_id(+)
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode like '%' || xxen_upload.action_update
LOV
Operating Unit
haouv.name=:operating_unit
LOV
Distribution Set
adsa.distribution_set_name=:distribution_set
LOV
Download
Related Blitz Reports
AP Distribution Set Listing AP Invoice Upload CAC Open Purchase Orders COPY OF: AP Invoice Upload PA Supplier Invoice Audit AP Invoices and Lines APL: AP Invoices and Lines AP Invoices and Lines - Test CAC Receiving Expense Value (Period-End)
Blitz Report™