PN Billing/Payment Term Upload

➜ Back to Library
Description

PN Billing/Payment Term Upload creates, updates and copies the billing and payment terms on existing Oracle Property Manager leases from Excel – the scheduled, time-phased amounts a tenant is billed or a landlord pays – via the standard Oracle Property Manager API.

When to use it

  • Add many new billing or payment terms to existing leases in one pass (rent, CAM, insurance lines).
  • Mass-edit existing terms – change amounts, dates, schedule day, accounts or third-party sites.
  • Roll forward / renew a schedule by copying last year’s terms shifted by a date increment.
  • Standardize billing setup across a portfolio of leases sharing the same term template.
  • Populate descriptive flexfield attributes on terms in bulk.

Before you start

  • Blitz Report is installed and you are signed in to a responsibility with access to the operating unit.
  • The lease already exists in Oracle Property Manager and is in the status you target (typically Final, Active) – this upload edits a lease’s terms, it does not create leases.
  • A suitable term template exists for the lease’s class, since it drives the term defaults.

Choose a template

TemplateUse it for
Revenue Class Template (default)Billing leases (lease class Revenue) – exposes the billing-side columns (AR invoice type, salesperson, invoicing/accounting rules).
Expense Class TemplatePayment leases (lease class Expense) – exposes the payables-side columns (PO number, distribution set, project/task).

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

In Blitz Report, open PN Billing/Payment Term Upload, choose your template, and set the parameters:

ParameterPurpose
Upload ModeCreate (new terms), Create, Update (download existing to edit – the default), or Copy (duplicate existing terms as new).
Lease ClassRevenue (billing) or Expense (payment) – set by the template.
Copy: Date Increment Type / UnitsIn Copy mode, shift the copied terms’ dates by Days/Months/Years.
Operating Unit / Lease Name / Number / Location / datesRestrict which existing terms are downloaded.

Run the upload to download and open the Excel file. Each term is automatically handled as a create or an update.

Step 2 – Enter the terms

On each row the Lease identifies which lease the term belongs to (read-only on downloaded rows). Enter the Term Template, Purpose, Type, Frequency, the Start Date, End Date and Schedule Day, the amount, the Third Party Name and Payment Terms, and the accounts as needed.

Step 3 – Validate and Save

Click Validate and Save. This checks for missing required values and runs the upload’s validation, 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, which creates or updates each term. When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Created and updated (or copied) billing/payment terms on the selected leases.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Can I delete a payment term with this upload?
No – it only creates, updates and copies. Remove terms in the Property Manager Leases form.

What’s the difference between “Create, Update” and “Copy”?
Create, Update downloads existing terms so you can edit them in place; Copy downloads existing terms but treats every row as new, creating duplicates – optionally with dates shifted by your increment.

Why does my update fail asking for a Payment Term ID?
Updates require the hidden term id that only comes from a Create, Update (or Copy) download – don’t type update rows from scratch.

Why are Revenue vs Expense columns different?
The Revenue Class Template exposes billing fields; the Expense Class Template exposes payables fields. Pick the template matching the lease class.

Where do Purpose, Type, Currency and Schedule Day defaults come from?
From the selected Term Template for the lease; the dropdowns cascade from it.

Troubleshooting

MessageCauseWhat to do
Operating Unit is invalid or not accessibleThe operating unit isn’t granted to your responsibility.Run under a responsibility with access to that operating unit.
Lease Name/Number does not exist or is not accessibleThe lease can’t be found under the chosen operating unit/status, or you lack access.Verify the lease name/number, status and operating unit.
Cannot update a Payment Term which has not been downloadedAn update row has no term id (typed manually instead of downloaded).Download existing terms in Create, Update mode and edit those rows.
Payment Term is invalidThe Payment Terms value doesn’t match an AR/AP term for the lease class.Choose Payment Terms from the dropdown (AR terms for billing, AP terms for payment).
Third Party Name is required / invalidThe customer/supplier is missing or doesn’t resolve in the operating unit.Select the third party (customer for billing, supplier for payment) from the dropdown.
select
decode(:p_upload_mode,'Copy',xxen_upload.action_meaning(xxen_upload.action_create),null) action_,
decode(:p_upload_mode,'Copy',xxen_upload.status_meaning(xxen_upload.status_new),null) status_,
decode(:p_upload_mode,'Copy',xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING', 'XXEN_REPORT_TRANSLATIONS', 0),null) message_,
null request_id_,
null modified_columns_,
null row_id,
haouv.name operating_unit,
plv.lease_name,
plv.lease_number,
xxen_util.meaning(plv.status,'PN_LEASE_STATUS_TYPE',0) final_draft,
plv.lease_class,
plv.lease_type,
nvl(plv.customer_name,plv.supplier_name) lease_third_party_name,
nvl(
 plv.customer_number,
 (select asu.segment1 from ap_suppliers asu where asu.vendor_id = plv.supplier_id)
) lease_third_party_number,
plv.location_code_disp primary_location,
plv.property_name_disp property,
plv.user_responsible,
--
pptv.term_template,
pptv.payment_purpose purpose,
pptv.payment_term_type type,
pptv.frequency_type frequency,
pptv.estimated_amount,
pptv.actual_amount,
pptv.currency_code,
pptv.rate,
--
case when not(:p_upload_mode = 'Copy' and :p_date_incr_type is not null and :p_date_incr_unit is not null)
then pptv.start_date
else case upper(:p_date_incr_type)
     when 'DAYS'   then pptv.start_date + :p_date_incr_unit
     when 'MONTHS' then add_months(pptv.start_date,:p_date_incr_unit)
     when 'YEARS'  then add_months(pptv.start_date,:p_date_incr_unit*12)
     end
end start_date,
case when not(:p_upload_mode = 'Copy' and :p_date_incr_type is not null and :p_date_incr_unit is not null)
then pptv.end_date
else case upper(:p_date_incr_type)
     when 'DAYS'   then pptv.end_date + :p_date_incr_unit
     when 'MONTHS' then add_months(pptv.end_date,:p_date_incr_unit)
     when 'YEARS'  then add_months(pptv.end_date,:p_date_incr_unit*12)
     end
end end_date,
case when not(:p_upload_mode = 'Copy' and :p_date_incr_type is not null and :p_date_incr_unit is not null)
then pptv.target_date
else case upper(:p_date_incr_type)
     when 'DAYS'   then pptv.target_date + :p_date_incr_unit
     when 'MONTHS' then add_months(pptv.target_date,:p_date_incr_unit)
     when 'YEARS'  then add_months(pptv.target_date,:p_date_incr_unit*12)
     end
end target_date,
--
pptv.schedule_day,
--
pla.location_code location,
xxen_util.meaning(pptv.area_type_code,'PN_AREA_TYPE_CODE',0) area_type,
pptv.area,
pptv.actual_amount * 12 annual_amount,
case when nvl(pptv.area,0) = 0
then to_number(null)
else round((pptv.actual_amount * 12)/pptv.area,2)
end annual_area_amount,
--
nvl(pptv.customer_name,pptv.vendor_name) third_party_name,
nvl(pptv.customer_number,pptv.vendor_number) third_party_number,
nvl(pptv.customer_site_use,pptv.vendor_site) third_party_bill_site,
pptv.cust_ship_site_use third_party_ship_site,
pptv.cust_po_number third_party_po_number,
(select poha.segment1 po_number from po_headers_all poha where poha.po_header_id = pptv.po_header_id) po_number,
--
case when plv.lease_class_code in ('THIRD_PARTY','SUB_LEASE','REC')
then (select rtv.name from ra_terms_vl rtv where rtv.term_id = pptv.ap_ar_term_id)
else (select atv.name from ap_terms_vl atv where atv.term_id = pptv.ap_ar_term_id)
end payment_terms,
-- billing
(select arm.name from ar_receipt_methods arm where arm.receipt_method_id = pptv.receipt_method_id) payment_method,
(select rctta.name from ra_cust_trx_types_all rctta where rctta.org_id = pptv.org_id and rctta.cust_trx_type_id = pptv.cust_trx_type_id) ar_invoice_type,
(select rsa.name from ra_salesreps_all rsa where rsa.org_id = pptv.org_id and rsa.salesrep_id = pptv.salesrep_id) salesperson,
(select rr.name from ra_rules rr where rr.rule_id = pptv.inv_rule_id) invoicing_rule,
(select rr.name from ra_rules rr where rr.rule_id = pptv.account_rule_id) accounting_rule,
-- payments
pptv.inv_group_name invoice_grouping_rule,
(select distribution_set_name from ap_distribution_sets_all adsa where adsa.distribution_set_id = pptv.distribution_set_id) distribution_set,
(select ppa.name from pa_projects_all ppa where ppa.project_id = pptv.project_id and ppa.org_id = pptv.org_id) project,
(select ptev.task_name from pa_tasks_expend_v ptev where ptev.task_id = pptv.task_id) task,
--pptv.agreement_name,
--pptv.agreement_number,
pptv.expenditure_type,
pptv.expenditure_item_date,
(select poev.name from pa_organizations_expend_v poev where poev.organization_id = pptv.organization_id) expenditure_organization,
-- tax
xxen_util.meaning(decode(pptv.tax_included,'A','Y',pptv.tax_included),'YES_NO',0) tax_included,
pptv.tax_classification_code,
--
(select
 gcck.concatenated_segments
 from
 pn_distributions_v pdv,
 gl_code_combinations_kfv gcck
 where
 pdv.payment_term_id = pptv.payment_term_id and
 gcck.code_combination_id = pdv.account_id and
 pdv.account_class in ('LIA','REC') and
 pdv.percentage = 100
) receivables_liability_account,
(select
 gcck.concatenated_segments
 from
 pn_distributions_v pdv,
 gl_code_combinations_kfv gcck
 where
 pdv.payment_term_id = pptv.payment_term_id and
 gcck.code_combination_id = pdv.account_id and
 pdv.account_class in ('EXP','REV') and
 pdv.percentage = 100
) revenue_expense_account,
(select
 gcck.concatenated_segments
 from
 pn_distributions_v pdv,
 gl_code_combinations_kfv gcck
 where
 pdv.payment_term_id = pptv.payment_term_id and
 gcck.code_combination_id = pdv.account_id and
 pdv.account_class in ('ACC','UNEARN') and
 pdv.percentage = 100
) accrual_account,
--
pptv.term_comments,
--
xxen_util.meaning(pptv.recoverable_flag,'YES_NO',0) recoverable,
xxen_util.meaning(pptv.normalize,'YES_NO',0) normalize,
null rou_asset,
null liability,
null report_from_inception,
null intercompany,
xxen_util.meaning(pptv.changed_flag,'YES_NO',0) changed,
xxen_util.meaning(pptv.include_in_var_rent,'PN_PAYMENT_BKPT_BASIS_TYPE',0) natural_breakpoint_basis,
xxen_util.meaning(pptv.source_module,'PN_TERM_SOURCE_MODULE',0) source,
--
pptv.attribute_category,
pptv.attribute1,
pptv.attribute2,
pptv.attribute3,
pptv.attribute4,
pptv.attribute5,
pptv.attribute6,
pptv.attribute7,
pptv.attribute8,
pptv.attribute9,
pptv.attribute10,
pptv.attribute11,
pptv.attribute12,
pptv.attribute13,
pptv.attribute14,
pptv.attribute15,
--
decode(:p_upload_mode,'Copy',to_number(null),pptv.payment_term_id) payment_term_id,
pptv.term_template_id,
pptv.lease_id,
pptv.lease_change_id,
nvl(pptv.customer_id,pptv.vendor_id) third_party_id,
nvl(pptv.customer_site_use_id,pptv.vendor_site_id) third_party_bill_to_site_id,
pptv.cust_ship_site_id third_party_ship_to_site_id,
to_number(to_char(pptv.start_date,'j')) start_date_j,
to_number(to_char(pptv.end_date,'j')) end_date_j,
to_number(null) upload_row
from
pn_leases_v plv,
pn_payment_terms_v pptv,
pn_locations_all pla,
hr_all_organization_units_vl haouv
where
1=1 and
:p_upload_mode in (xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update),'Copy') and
plv.lease_id = pptv.lease_id and
plv.org_id = haouv.organization_id and
--
pptv.location_id = pla.location_id (+) and
plv.status = nvl(xxen_util.lookup_code(:p_final_draft_status,'PN_LEASE_STATUS_TYPE',0),'F') and
plv.lease_class = :p_lease_class
Parameter NameSQL textValidation
Upload Mode
 
LOV
Lease Class
 
LOV
Final Draft Status
 
LOV
Lease Status
plv.lease_status = xxen_util.lookup_code(:p_lease_status,'PN_LEASESTATUS_TYPE',0)
LOV
Operating Unit
haouv.name=:p_operating_unit
LOV
Third Party Name
nvl(plv.customer_name,plv.supplier_name) = :p_third_party_name
LOV
Lease Type
plv.lease_type = :p_lease_type
LOV
Lease Name
plv.lease_name = :p_lease_name
LOV
Lease Name From
plv.lease_name >= :p_lease_name_fr
LOV
Lease Name To
plv.lease_name <= :p_lease_name_to
LOV
Lease Number
plv.lease_number = :p_lease_number
LOV
Lease Number From
plv.lease_number >= :p_lease_number_fr
LOV
Lease Number To
plv.lease_number <= :p_lease_number_to
LOV
Location
pla.location_code = :p_location_code
LOV
Purpose
pptv.payment_purpose = :p_payment_purpose
LOV
Type
pptv.payment_term_type = :p_payment_type
LOV
Frequency
pptv.frequency_type = :p_frequency
LOV
Payment Start Date From
pptv.start_date >= :p_pay_start_date_fr
Date
Payment Start Date To
pptv.start_date < :p_pay_start_date_to + 1
Date
Copy: Date Increment Type
 
LOV
Copy: Date Increment Units
 
Number
Download
Related Blitz Reports
AR Customer Upload SAM: GL Account Analysis (Drilldown) (with inventory and WIP) EOS GL Account Analysis with Tax TB GL Account Analysis (Drilldown)v2 GL Account Analysis (Drilldown) (with inventory and WIP) COPY OF: TB GL Account Analysis (Drilldown)v3 GL Account Distribution Analysis TB GL Account Analysis (Drilldown)v3 GL Account Analysis
Blitz Report™