PO Requisition Template Upload

➜ Back to Library
Description

PO Requisition Template Upload creates, updates and deletes Oracle Purchasing requisition templates from Excel – the template header plus its lines – mirroring the Purchasing Requisition Templates setup form.

When to use it

  • Create many new requisition templates at once instead of keying them one by one in the form.
  • Add, change or remove template lines on existing templates in bulk.
  • Fully replace a template’s lines and reload a new set.
  • Mass-maintain sourcing defaults (supplier/site/contact or source organization/subinventory) across templates.
  • Standardize templates across multiple operating units in one spreadsheet.
  • Copy or clone a template’s lines.

Before you start

  • Blitz Report is installed and you are signed in to a responsibility with access to the operating unit.
  • Items exist and are purchasing- or internal-order-enabled in the operating unit; revisions, categories and units of measure are valid.
  • For supplier-sourced lines the supplier, site and contact exist; for inventory-sourced lines a valid source organization exists.

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

In Blitz Report, open PO Requisition Template Upload, choose an Upload Mode (Create for new templates only, or Create, Update to download existing templates to edit), and set the parameters:

ParameterPurpose
Upload ModeCreate or Create, Update. Default is Create.
Operating UnitRestrict downloaded templates to this operating unit.
Template Type / Template Name / Template Name ContainsRestrict which existing templates are downloaded.

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

Step 2 – Enter the templates

Use one row per template line; repeat the header values on each line of the same template. On the header enter the Operating Unit, Template Name and Type. On each line enter the Line Number, Line Type, Item (or Category for a category line), Line Description, UOM, Unit Price and Source Type.

Step 3 – Delete lines (optional)

Set Delete this Template Line to Yes to remove a downloaded existing line. To clear and reload all lines of a template, use the Pre Delete Template Lines option.

Step 4 – 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 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 or updates each template and its lines. When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Created and updated requisition templates with their lines; deleted lines where flagged.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Can I change the Operating Unit or Type on an existing template?
No – Operating Unit and Type are read-only on existing rows; the operating unit of an existing template cannot be changed.

How do I delete a single line vs all lines?
Set Delete this Template Line = Yes on that line (it must be a downloaded existing line). To clear and reload all lines, use the Pre Delete Template Lines option.

What’s the difference between an item line and a category line?
Leave Item blank to create a category-based line (Category required); enter an Item to default Category and Description from the item.

Why is Buyer suddenly required?
Because Reserve PO Number is set to Yes or Optional – those require a Buyer.

Can I rename a template?
Yes, in Create, Update mode – change the Template Name; the new name must not already exist in the operating unit and must be 25 characters or fewer.

Troubleshooting

MessageCauseWhat to do
You cannot create new templates in Update mode / update existing templates in Create modeThe action doesn’t match the selected Upload Mode.Set Upload Mode to Create, Update, or use the correct mode for the row.
A template with this name already exists / Template Name exceeds maximum lengthDuplicate or too-long template name.Use a unique name within the operating unit, 25 characters or fewer.
Item is not purchasing or internal order enabledThe item isn’t set up correctly in the operating unit.Enable the item for purchasing/internal orders, pick a valid item, or use a category line.
Source Organization is required for inventory-sourced linesSource Type = Inventory with no source organization.Enter a valid source organization (and optional subinventory).
Cannot delete a line which has not yet been createdDelete = Yes on a line that doesn’t exist yet.Only set Delete = Yes on a downloaded existing line.
select
null action_,
null status_,
null message_,
null request_id_,
null modified_columns_,
rowidtochar(prha.rowid) header_row_id,
rowidtochar(prla.line_row_id) line_row_id,
to_char(null) delete_this_line,
to_char(null) pre_delete_template_lines,
:p_upload_mode upload_mode,
to_char(null) publish_template_to_icx,
-- header
haouv.name operating_unit,
prha.express_name template_name,
prha.description template_description,
podt.type_name type,
prha.inactive_date,
xxen_util.meaning(prha.reserve_po_number,'RESERVE PO NUM',201) reserve_po_number,
-- lines
prla.sequence_num line_number,
plt.line_type,
nvl2(prla.item_id,msiv.concatenated_segments,null) item,
prla.item_revision,
mck.concatenated_segments category,
prla.item_description line_description,
muomv.unit_of_measure_tl uom,
prla.suggested_quantity,
prla.unit_price,
prla.amount,
xxen_util.meaning(prla.source_type_code,'REQUISITION SOURCE TYPE',201) source_type,
po_inq_sv.get_person_name(prla.suggested_buyer_id) buyer,
asu.vendor_name supplier,
assa.vendor_site_code supplier_site,
decode(asco.last_name,null,null, asco.last_name||', '|| asco.first_name) supplier_contact,
prla.suggested_vendor_product_code supplier_item,
ood.organization_code source_organization,
prla.source_subinventory,
decode(prla.negotiated_by_preparer_flag,'Y',xxen_util.meaning(prla.rfq_required_flag,'YES_NO',0),null) negotiated,
decode(prla.rfq_required_flag,'Y',xxen_util.meaning(prla.rfq_required_flag,'YES_NO',0),null) rfq_required,
--
xxen_util.user_name(prha.last_updated_by) template_last_updated_by,
prha.last_update_date template_last_updated_on,
xxen_util.user_name(prla.last_updated_by) line_last_updated_by,
prla.last_update_date line_last_updated_on,
xxen_util.user_name(prha.created_by) template_created_by,
prha.creation_date template_created_on,
xxen_util.user_name(prla.created_by) line_created_by,
prla.creation_date line_created_on,
--
to_number(null) upload_row
from
po_reqexpress_headers_all prha,
(select
  fspa.inventory_organization_id,
  prla.rowid line_row_id,
  prla.*
 from
  po_reqexpress_lines_all prla,
  financials_system_params_all fspa
 where
  prla.org_id = fspa.org_id
) prla,
hr_all_organization_units_vl haouv,
po_line_types plt,
po_document_types_all_tl podt,
mtl_system_items_vl msiv,
mtl_categories_kfv mck,
mtl_units_of_measure_vl muomv,
org_organization_definitions ood,
ap_suppliers asu,
ap_supplier_sites_all assa,
ap_supplier_contacts asco
where
1=1 and
nvl(:p_publish_template_to_icx,'N')=nvl(:p_publish_template_to_icx,'N') and
nvl(:p_pre_delete_template_lines,'N')=nvl(:p_pre_delete_template_lines,'N') and
prha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and
prha.org_id = haouv.organization_id and
prha.type_lookup_code = podt.document_subtype and
prha.org_id = podt.org_id and
podt.document_type_code = 'REQUISITION' and
podt.language = userenv ('LANG') and
prha.express_name = prla.express_name (+) and
prha.org_id = prla.org_id (+) and
prla.line_type_id = plt.line_type_id (+) and
prla.item_id = msiv.inventory_item_id (+) and
prla.inventory_organization_id = msiv.organization_id (+) and
prla.category_id = mck.category_id (+) and
prla.unit_meas_lookup_code = muomv.unit_of_measure (+) and
prla.source_organization_id = ood.organization_id (+) and
prla.suggested_vendor_id = asu.vendor_id (+) and
prla.suggested_vendor_site_id = assa.vendor_site_id (+) and
prla.suggested_vendor_contact_id = asco.vendor_contact_id (+) and
prla.suggested_vendor_site_id = asco.vendor_site_id (+) and
not exists -- exclude templates copied from a Blanket PO
(select 
  null
 from 
  po_reqexpress_lines_all prla2
 where
  prla2.org_id = prha.org_id and
  prla2.express_name = prha.express_name and
  prla2.po_line_id is not null
)
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode  like '%' || xxen_upload.action_update
LOV
Operating Unit
haouv.name=:p_operating_unit
LOV
Template Type
prha.type_lookup_code=:p_template_type
LOV
Template Name
prha.express_name = :p_template_name
LOV
Template Name Contains
lower(prha.express_name) like '%' || lower(:p_template_name_like) || '%'
Char
Download
Related Blitz Reports
SAM: GL Account Analysis (Drilldown) (with inventory and WIP) COPY OF: TB GL Account Analysis (Drilldown)v3 COPY OF: PO Headers and Lines GL Account Distribution Analysis TB GL Account Analysis (Drilldown)v3 GL Account Analysis PO Headers and Lines ECC Sourcing, Negotiations, SQL2 ECC Sourcing, Negotiations, SQL1
Blitz Report™