PO Requisition Template Upload
Description
PO Requisition Template Upload creates and updates Oracle Purchasing requisition templates from Excel, the template header together with its lines, as in the Purchasing Requisition Templates window. Download existing templates to change their description, lines, prices and suggested sources, add new lines or delete lines, or create new templates from scratch.
When to use it
- Create many new requisition templates at once instead of keying them in the Requisition Templates window.
- Add, change or delete lines on existing templates in bulk.
- Mass-maintain prices, buyers and suggested sources (supplier, site and contact, or source organization and subinventory) on template lines.
- Rename templates or change their description and inactive date.
- Copy a template’s lines into a new template by pasting them as new rows with a new Template Name.
Before you start
- Access to the upload in Blitz Report, in a responsibility with access to the operating unit.
- Items are purchasing or internal order enabled in the inventory organization of the operating unit.
- For supplier-sourced lines the supplier site exists in the operating unit; for inventory-sourced lines you know the source organization.
Step 1 – Set the parameters
Open PO Requisition Template Upload in Blitz Report and set the parameters:
| Parameter | Meaning |
|---|---|
| Upload Mode | Create (default) opens an empty sheet for new templates; Create, Update downloads existing templates for editing and lets you add new ones. |
| Operating Unit | The operating unit of the templates to download. Defaults to your current operating unit. |
| Template Type | Downloads only Purchase Requisition or Internal Requisition templates. |
| Template Name | The template(s) to download. |
| Template Name Contains | Downloads all templates whose name contains this text, ignoring case. |
Step 2 – Run to download the Excel file
Click Run. The Excel file downloads and opens with one row per template line, or an empty sheet in Create mode.
Step 3 – Enter or change the templates
Each row is one template line, with the header values Operating Unit, Template Name, Template Description, Type, Inactive Date and Reserve PO Number repeated on every line. The header is saved from each row, so keep these values the same on all rows of a template; to change the description, change it on every row. Operating Unit and Type cannot be changed on downloaded rows.
For a line enter the Line Number (unique within the template), Line Type, Item or, for a line without an item, a Category, the Line Description, UOM, Unit Price and Source Type. For an item line the category is taken from the item’s purchasing category, and the line description from the item unless the item allows description updates. Supplier lines can suggest a Supplier, Supplier Site, Supplier Contact and Supplier Item; Inventory lines need a Source Organization and can name a Source Subinventory. Set Delete This Line to Yes to delete a downloaded line.
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.
Step 5 – Upload the file
In Blitz Report click Upload and select the saved file. This submits the upload request, which creates or updates each template and line, and deletes the lines marked for deletion.
Step 6 – Review the result report
When the request completes, a result report opens listing every uploaded row with its status and message.
What’s produced
- Requisition templates and template lines created or updated, and lines deleted where Delete This Line is Yes.
- 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 of an existing template?
No. Operating Unit and Type are read-only on downloaded rows. Create a new template in the other operating unit instead.
Can I rename a template?
Yes. Download it in Create, Update mode and change the Template Name on all its rows. The new name must not exist in the operating unit and can have at most 25 characters.
How do I delete a line?
Download the template, set Delete This Line to Yes on that line and upload. Only downloaded lines can be deleted; to drop a line you have just added, remove the row from the sheet.
Why did the category of my item line change?
For lines with an item, the category is always taken from the item’s purchasing category, as in the Requisition Templates window. Enter a category yourself only for lines without an item.
Why is Buyer required?
Reserve PO Number is Yes or Optional on the template, which requires a Buyer on every line.
Troubleshooting
| Message | Cause | What to do |
|---|---|---|
| You cannot update existing Templates in Create Mode | A template with this name already exists in the operating unit and Upload Mode is Create. | Use a new template name, or download the template in Create, Update mode. |
| A template with this name already exists | A renamed template uses a name that is taken. | Choose a name not used in the operating unit. |
| Template Name exceeds maximum length (25) | The template name is longer than 25 characters. | Shorten the name. |
| Duplicate Line Number | The template already has a line with this number. | Use a line number not yet used in the template. |
| Item is invalid | The item does not exist in the inventory organization of the operating unit, or has no purchasing category. | Pick the item from the list. |
| Item is not purchasing or internal order enabled | The item cannot be requisitioned. | Enable the item for purchasing or internal orders, or use a line without an item. |
| Unit Price is required for this line type | No Unit Price on a quantity or amount based line. | Enter the unit price. |
| Unit Price does not match the Unit Price for this Line Type ( … ) | An amount based line type has a fixed unit price. | Enter the line type’s unit price, usually 1. |
| Supplier/Supplier Site is invalid | The supplier site does not exist for the supplier in this operating unit. | Pick the Supplier Site from the list. |
| Source Organization is required for Inventory Sourced Template Lines | Source Type is Inventory without a Source Organization. | Enter the source organization. |
| Buyer is required for Category lines when Reserve PO Number is enabled | Reserve PO Number is Yes or Optional and the line has no Buyer. | Enter a Buyer, or set Reserve PO Number to No. |
| Cannot delete a line which has not yet been created | Delete This Line is Yes on a new line. | Set Delete This Line only on downloaded lines. |
| Cannot update a template which has not been downloaded first | A row marked Update was not downloaded from the template. | Download the template in Create, Update mode and edit the downloaded rows. |
SQL 104 lines
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, pdtav.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_vl pdtav, 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') and prha.org_id = haouv.organization_id and prha.type_lookup_code=pdtav.document_subtype and prha.org_id=pdtav.org_id and pdtav.document_type_code='REQUISITION' 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 ) |
Parameters 5
| Parameter | Type | List of values SQL | |
|---|---|---|---|
| Upload Mode | LOV |
| |
| Operating Unit | LOV |
| |
| Template Type | LOV |
| |
| Template Name | LOV |
| |
| Template Name Contains | Char |
|