PN Billing/Payment Term Upload

➜ Back to Library
XMLXML import file

Description

PN Billing/Payment Term Upload creates, updates and copies the billing and payment terms of existing Oracle Property Manager leases from Excel – the recurring or one-time amounts a tenant is billed or a landlord is paid, with their dates, frequency, third party, payment terms and accounts. Each lease’s terms are submitted together through the standard Property Manager lease API, and the schedules and items are generated for the new and changed terms.

When to use it

  • Add billing or payment terms (rent, operating expenses, insurance, cleaning, parking) to many leases in one pass.
  • Mass-edit existing terms: amounts, dates, schedule day, third party sites, payment terms, accounts or comments.
  • Roll a schedule forward by copying existing terms with their dates shifted by a number of days, months or years.
  • Populate the descriptive flexfield attributes of payment terms in bulk (Expense Class Template).

Before you start

  • Your responsibility has access to the lease’s operating unit.
  • The lease already exists in Oracle Property Manager. The upload maintains a lease’s terms; it does not create leases.
  • The customers (billing) or suppliers (payment) with their bill-to or supplier sites, the payment terms and the GL accounts you want to use exist in the operating unit.

Choose a template

TemplateUse it for
Revenue Class Template (default)Billing terms on Revenue leases. Sets Lease Class to Revenue and shows the receivables columns: Third Party Ship Site, Third Party PO Number, Payment Method, Ar Invoice Type, Salesperson, Invoicing Rule and Accounting Rule.
Expense Class TemplatePayment terms on Expense leases. Sets Lease Class to Expense and shows the payables columns: PO Number, Invoice Grouping Rule, Distribution Set, Project, Task, Expenditure Type, Expenditure Item Date, Expenditure Organization, the Rou Asset, Liability, Report From Inception and Intercompany flags, and the descriptive flexfield attributes.

Step 1 – Set the parameters

Open PN Billing/Payment Term Upload in Blitz Report, choose the template and set the parameters:

ParameterMeaning
Upload ModeCreate opens an empty sheet for new terms; Create, Update (default) downloads the existing terms for editing and lets you add new ones; Copy downloads existing terms as new rows, so uploading them creates copies.
Lease ClassRevenue, Expense or Sublease. Set by the template.
Final Draft StatusDownloads leases in this approval status: Final (default) or Draft.
Lease StatusDownloads leases with this lease status. Defaults to Active.
Operating UnitThe operating unit of the leases.
Third Party NameThe lease’s customer or supplier.
Lease TypeRestricts the download to one lease type.
Lease Name, Lease Name From / ToOne lease, or a range of lease names.
Lease Number, Lease Number From / ToOne lease, or a range of lease numbers.
LocationLeases with this location.
Purpose, Type, FrequencyOnly terms with this payment purpose, term type or frequency.
Payment Start Date From / ToOnly terms starting in this date range.
Copy: Date Increment Type / UnitsIn Copy mode, shifts the Start Date, End Date and Target Date of the copied terms by this number of Days, Months or Years.

Unless the Upload Mode is Create, enter at least one selection parameter from Third Party Name onwards.

Blitz Report run screen for PN Billing/Payment Term Upload with the Revenue Class Template, Upload Mode Create, Update, Lease Class Revenue, Final Draft Status Final, Lease Status Active, Operating Unit Vision Operations and Lease Name WCC Aloha Travel

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with one row per payment term of the selected leases, showing the lease, term template, location, purpose, type, frequency, amounts, dates, third party, payment terms and accounts.

Excel file with the three billing terms of lease WCC Aloha Travel: Base Rent, Insurance and Operating Expense

Step 3 – Enter or change the payment terms

Operating Unit, Lease Name and Lease Number identify the lease and cannot be changed on a downloaded row. Change any other column of a downloaded term, for example the Actual Amount, End Date, Schedule Day, Third Party Bill Site or Term Comments.

To add a term, add a row with the Operating Unit, Lease Name and Lease Number, and fill Purpose, Type, Frequency, Actual Amount or Estimated Amount, Currency Code, Rate, Start Date, End Date (within the lease dates), Schedule Day, Third Party Name and Number, Third Party Bill Site and Payment Terms, plus the Term Template, Location and the Receivables Liability, Revenue Expense and Accrual accounts as needed. On a new row also unhide the Final Draft column and enter the lease’s status (Final or Draft); downloaded rows already carry it. The third party is a customer with its bill-to location on Revenue and Sublease leases, and a supplier with its supplier site on Expense leases; Payment Terms are receivables terms on Revenue and Sublease leases and payables terms on Expense leases. Enter accounts as concatenated segments, for example 01-000-4504-0000-000.

Excel file with the term comments of the Base Rent term changed, marked Update, and a new monthly Cleaning term added, marked Create

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 edited 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 validates every row and then submits all terms of a lease together, updates before creates. If any term of a lease fails, none of that lease’s terms are saved. Oracle Property Manager then generates the schedules and items for the new and changed terms.

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 the updated Base Rent term and the created Cleaning term with status Success

What’s produced

  • New, updated or copied billing and payment terms on the selected leases, with their schedules and items.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Can I delete a payment term or create a lease with this upload?
No. The upload creates, updates and copies terms on existing leases. Delete terms and create leases in the Property Manager Leases window.

What is the difference between Create, Update and Copy?
Create, Update downloads the existing terms so you can change them and add new ones. Copy downloads existing terms as new rows, optionally with their dates shifted by the Copy: Date Increment parameters, so uploading them creates new terms and leaves the originals unchanged.

Why did my other terms of the same lease fail with “One or more Payment Terms for this lease have an error”?
All terms of a lease are saved together. When one row of a lease has an error, the lease’s other rows are not saved either. Fix the failing row and upload the lease’s rows again.

Why does a new row fail with “Lease Name/Number does not exist or is not accessible to this responsibility.”?
The lease is looked up by its operating unit, name or number and its Final Draft status. Enter Final or Draft in the hidden Final Draft column of the new row, or create the term by copying an existing one in Copy mode, which fills it from the lease.

Can I update a term I typed in myself?
No. Updates need the term’s identifier, which only a Create, Update download provides. Download the terms first and change the downloaded rows.

How do I maintain Sublease leases?
Set Lease Class to Sublease. Sublease terms use customers and receivables payment terms like Revenue leases, so use the Revenue Class Template.

Troubleshooting

MessageCauseWhat to do
Please specify one or more criteria to determine the payment terms to be updated or copiedUpload Mode Create, Update or Copy without a selection parameter.Enter a lease, third party, location or another selection parameter.
Operating Unit is invalid or not accessible to this Responsibility.The operating unit is not in your responsibility’s security profile.Upload from a responsibility with access to the operating unit.
Lease Name/Number does not exist or is not accessible to this responsibility.No lease with this name or number and Final Draft status in the operating unit; on a new row, usually an empty Final Draft column.Check the lease name and number, and enter Final or Draft in the Final Draft column of new rows.
Lease Status is Invalid (…)The Final Draft column holds a value other than Final or Draft.Enter Final or Draft.
Payment Term ID is null. Cannot update a Payment Term which has not been downloaded.An Update row that was not downloaded.Download the terms in Create, Update mode and change the downloaded rows.
Payment Term is invalid (…)The Payment Terms value is not an active receivables (Revenue, Sublease) or payables (Expense) payment term.Pick the Payment Terms from the list.
Third Party Name and/or Number is requiredThird Party Name and Number are both empty.Enter the customer or supplier.
Third Party Name is invalid. / Third Party is invalid.No customer (Revenue, Sublease) or supplier (Expense) with this name and number.Pick the Third Party Name and Number from the list.
Multiple Third Party Accounts exist with the same name.Several customer accounts or suppliers share the name.Also enter the Third Party Number.
Third Party Bill To Location is required.Third Party Bill Site is empty.Enter the customer’s bill-to location or the supplier site.
Third Party Bill To Location invalid. / Third Party Bill To Location is invalid.The site is not an active bill-to location of the customer, or an active site of the supplier, in the operating unit.Pick the Third Party Bill Site from the list.
Third Party Ship To Location invalid.The ship site is not an active ship-to location of the customer in the operating unit.Pick the Third Party Ship Site from the list or leave it empty.
Location is invalid (…)The location is not one of the lease’s tenancy locations.Pick the Location from the list.
Payment Method is invalid / AR Transaction Type is invalid / Salesrep is invalid / Invoice Rule invalid / Accounting Rule invalid (…)The receivables value is inactive or does not exist in the operating unit.Pick the value from the list.
PO Number is invalid (…) / PO Number is not unique (…)No purchase order, or several, with this number in the operating unit.Check the PO Number.
Invalid Grouping Rule / Invalid Distribution Set / Invalid Project / Invalid Task / Invalid Expenditure Organization (…)The payables or projects value does not exist or is inactive in the operating unit.Pick the value from the list.
One or more Payment Terms for this lease have an errorAnother row of the same lease failed, so none of the lease’s terms were saved.Fix the failing row and upload the lease’s rows again.

SQL 185 lines

Parameters 21