PO Requisition Upload

➜ Back to Library
Description

PO Requisition Upload mass-creates Oracle Purchasing requisitions – Purchase or Internal, with header, lines and distributions – from Excel, through the standard Requisition Import program, and can make limited updates to key fields on existing requisitions.

When to use it

  • Load many new purchase requisitions from a spreadsheet (for example bulk indirect/MRO buys).
  • Load many internal (inter-organization transfer) requisitions.
  • Create requisitions with line- and distribution-level detail, project accounting and charge-account distributions in one pass.
  • Attach documents or notes to imported requisitions (use a “(with attachment)” template).
  • Optionally initiate the requisition approval workflow automatically as part of the import.
  • Make limited edits to existing not-yet-finalized requisitions.

Before you start

  • Blitz Report is installed and you are signed in to a responsibility with access to the operating unit and the right to run Requisition Import.
  • Items exist in the destination organization (non-item lines use a description and category instead).
  • Deliver-to locations, requestors and a valid charge account are set up.

Choose a template

There is a template for each requisition type, in a plain version and a “(with attachment)” version that adds the attachment columns:

TemplateUse it for
Create Purchase RequisitionPurchase requisitions (buyer/supplier sourcing).
Create Internal RequisitionInternal (inter-organization transfer) requisitions.
… (with attachment)Either of the above plus the columns to attach a document.

Pick the template that matches the requisition type; each pre-sets its Requisition Type.

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

In Blitz Report, open PO Requisition Upload, choose your template, and set the parameters:

ParameterPurpose
Operating UnitThe operating unit the requisition belongs to (required).
Upload ModeCreate (empty template) or Create, Update (download existing requisitions to edit). Default is Create.
Requisition TypePurchase or Internal (set by the template).
Initiate Approval on ImportWhether to start the approval workflow after import (default Yes).
Group ByHow imported lines are grouped into requisitions (default All).

Run the upload to download and open the Excel file.

Step 2 – Enter the requisitions

Use the Group Code column to group lines into a requisition. On each line enter the Line Type, Item (or Category for a non-item line), Unit of Measure, Quantity, Need By Date, the Destination Type and Organization, the Deliver To Location and Requestor, the source columns, and the Charge Account segments.

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 stages the lines and runs the standard Requisition Import program to build the requisitions. When it finishes, a result report opens showing each row as success or error, with the assigned requisition numbers.

What’s produced

  • New requisitions (and limited edits to existing ones), with their lines and distributions.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Does it use the standard Oracle import?
Yes – new requisitions are staged into the Purchasing open interface and built by the standard Requisition Import program; the upload submits it for you.

Purchase or internal requisitions?
Both – controlled by the Requisition Type parameter (the templates default it to Purchase or Internal).

How are lines grouped into requisitions?
By the Group Code column and the Group By parameter (the same grouping options as standard Requisition Import).

Will it approve the requisition?
Only if Initiate Approval on Import = Yes; otherwise the requisition is left at the imported status.

Can I delete a requisition?
No – there is no delete capability.

Troubleshooting

MessageCauseWhat to do
Operating Unit is invalidThe operating unit isn’t accessible, or the name doesn’t match.Use the Operating Unit value from the dropdown; run under a responsibility with access to it.
Invalid ItemThe item isn’t defined/assigned in the destination organization.Pick an item from the dropdown for that destination org, or use a description-based line.
Charge Account is invalidThe charge-account segments don’t form a valid combination.Correct the segments, or set Allow Dynamic CCID = Yes to allow dynamic creation during import.
Requisition Import completed in Error / WarningThe standard import program flagged the staged rows.Open the named Requisition Import request log, fix the data, and re-upload.
Flexfield validation errorAn attribute value failed validation for its context.Set the matching attribute category and pick valid attribute values.
select
--process--
null action_,
null status_,
null message_,
null request_id_,
null modified_columns_,
to_char(null) row_id,
--header--
null group_code,
prha.creation_date,
prha.requisition_header_id,
prha.segment1 requisition_number,
papf3.full_name preparer,
prha.note_to_authorizer,
--prh.type_lookup_disp requisition_type,
prha.description header_description,
xxen_util.display_flexfield_context(201,'PO_REQUISITION_HEADERS',prha.attribute_category) header_attribute_category,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE1',prha.rowid,prha.attribute1) header_attribute1,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE2',prha.rowid,prha.attribute2) header_attribute2,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE3',prha.rowid,prha.attribute3) header_attribute3,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE4',prha.rowid,prha.attribute4) header_attribute4,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE5',prha.rowid,prha.attribute5) header_attribute5,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE6',prha.rowid,prha.attribute6) header_attribute6,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE7',prha.rowid,prha.attribute7) header_attribute7,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE8',prha.rowid,prha.attribute8) header_attribute8,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE9',prha.rowid,prha.attribute9) header_attribute9,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE10',prha.rowid,prha.attribute10) header_attribute10,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE11',prha.rowid,prha.attribute11) header_attribute11,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE12',prha.rowid,prha.attribute12) header_attribute12,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE13',prha.rowid,prha.attribute13) header_attribute13,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE14',prha.rowid,prha.attribute14) header_attribute14,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_HEADERS',prha.attribute_category,'ATTRIBUTE15',prha.rowid,prha.attribute15) header_attribute15,
prla.requisition_line_id,
prla.line_num,
pltt.line_type,
plc2.displayed_field destination_type,
mpd.organization_code destination_organization,
prla.destination_subinventory,
hlat.location_code deliver_to_location,
papf2.full_name deliver_to_requestor,
plc1.displayed_field source_type,
mps.organization_code source_organization,
prla.source_subinventory,
msiv.concatenated_segments item,
prla.item_description,
prla.item_revision,
mcv.category_concat_segs category,
prla.unit_meas_lookup_code unit_of_measure,
prla.quantity,
prla.unit_price,
prla.need_by_date,
prla.reference_num reference_number,
--source--
prla.suggested_vendor_name supplier,
prla.suggested_vendor_location supplier_site,
prla.suggested_vendor_contact supplier_contact,
prla.suggested_vendor_product_code supplier_item,
--source details--
papf1.full_name buyer,
prla.note_to_agent note_to_buyer,
--currency--
prla.currency_code currency,
prla.rate_type conversion_type,
prla.rate_date conversion_date,
prla.rate conversion_rate,
prla.currency_unit_price,
--
xxen_util.display_flexfield_context(201,'PO_REQUISITION_LINES',prla.attribute_category) line_attribute_category,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE1',prla.rowid,prla.attribute1) line_attribute1,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE2',prla.rowid,prla.attribute2) line_attribute2,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE3',prla.rowid,prla.attribute3) line_attribute3,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE4',prla.rowid,prla.attribute4) line_attribute4,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE5',prla.rowid,prla.attribute5) line_attribute5,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE6',prla.rowid,prla.attribute6) line_attribute6,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE7',prla.rowid,prla.attribute7) line_attribute7,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE8',prla.rowid,prla.attribute8) line_attribute8,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE9',prla.rowid,prla.attribute9) line_attribute9,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE10',prla.rowid,prla.attribute10) line_attribute10,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE11',prla.rowid,prla.attribute11) line_attribute11,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE12',prla.rowid,prla.attribute12) line_attribute12,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE13',prla.rowid,prla.attribute13) line_attribute13,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE14',prla.rowid,prla.attribute14) line_attribute14,
xxen_util.display_flexfield_value(201,'PO_REQUISITION_LINES',prla.attribute_category,'ATTRIBUTE15',prla.rowid,prla.attribute15) line_attribute15,
--distribution accounts--
prda.distribution_id,
prda.distribution_num,
gcc.segment1 charge_account_segment1,
gcc.segment2 charge_account_segment2,
gcc.segment3 charge_account_segment3,
gcc.segment4 charge_account_segment4,
gcc.segment5 charge_account_segment5,
gcc.segment6 charge_account_segment6,
gcc.segment7 charge_account_segment7,
gcc.segment8 charge_account_segment8,
gcc.segment9 charge_account_segment9,
gcc.segment10 charge_account_segment10,
--distribution projects--
prda.project_accounting_context project_accounting,
ppv.segment1 project_number,
ptv.task_number,
prda.expenditure_type,
haouve.name expenditure_organization,
prda.expenditure_item_date,
xxen_util.display_flexfield_context(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category) dist_attribute_category,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE1',prda.rowid,prda.attribute1) dist_attribute1,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE2',prda.rowid,prda.attribute2) dist_attribute2,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE3',prda.rowid,prda.attribute3) dist_attribute3,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE4',prda.rowid,prda.attribute4) dist_attribute4,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE5',prda.rowid,prda.attribute5) dist_attribute5,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE6',prda.rowid,prda.attribute6) dist_attribute6,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE7',prda.rowid,prda.attribute7) dist_attribute7,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE8',prda.rowid,prda.attribute8) dist_attribute8,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE9',prda.rowid,prda.attribute9) dist_attribute9,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE10',prda.rowid,prda.attribute10) dist_attribute10,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE11',prda.rowid,prda.attribute11) dist_attribute11,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE12',prda.rowid,prda.attribute12) dist_attribute12,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE13',prda.rowid,prda.attribute13) dist_attribute13,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE14',prda.rowid,prda.attribute14) dist_attribute14,
xxen_util.display_flexfield_value(201,'PO_REQ_DISTRIBUTIONS',prda.attribute_category,'ATTRIBUTE15',prda.rowid,prda.attribute15) dist_attribute15,
-- defaults
to_char(null) group_by,
to_char(null) initiate_reqappr_after_imp,
to_char(null) interface_source_code,
to_char(null) multi_distributions,
to_char(null) allow_dynamic_ccid,
to_char(null) authorization_status,
to_char(null) preparer_id,
prha.type_lookup_code requisition_type,
--attachments
null attachment_category_,
null attachment_title_,
null attachment_description_,
null attachment_type_,
null attachment_content_,
null attachment_file_id_
from
po_requisition_headers_all prha,
po_requisition_lines_all prla,
po_req_distributions_all prda,
mtl_categories_v mcv,
gl_code_combinations gcc,
pa_projects_all ppv,
pa_tasks_v ptv,
hr_all_organization_units_vl haouve,
mtl_parameters mps,
mtl_parameters mpd,
mtl_system_items_vl msiv,
hr_all_organization_units_vl haouv,
per_all_people_f papf1,
per_all_people_f papf2,
per_all_people_f papf3,
po_lookup_codes plc1,
po_lookup_codes plc2,
hr_locations_all_tl hlat,
po_line_types_tl pltt,
po_document_types_all_tl podt,
po_document_types_all_b podb
where
1=1 and
:p_group_by=:p_group_by and
prha.org_id=haouv.organization_id and
prha.requisition_header_id=prla.requisition_header_id and
prla.requisition_line_id=prda.requisition_line_id and
papf1.person_id(+) = prla.suggested_buyer_id and
trunc (sysdate) between papf1.effective_start_date (+) and papf1.effective_end_date (+) and
plc1.lookup_code = prla.source_type_code and
plc1.lookup_type = 'REQUISITION SOURCE TYPE' and
plc2.lookup_code = prla.destination_type_code and
plc2.lookup_type = 'DESTINATION TYPE' and
podb.document_type_code = 'REQUISITION' and
podb.document_subtype = prha.type_lookup_code and
podb.document_type_code = podt.document_type_code and
podb.document_subtype = podt.document_subtype and
podt.language = userenv ('LANG') and
podt.org_id = podb.org_id and
podb.org_id = prha.org_id and
papf2.person_id = prla.to_person_id and
trunc (sysdate) between papf2.effective_start_date and papf2.effective_end_date and
papf3.person_id = prha.preparer_id and
trunc (sysdate) between papf3.effective_start_date and papf3.effective_end_date and
pltt.line_type_id=prla.line_type_id and
pltt.language=userenv ('LANG') and
hlat.location_id(+) = prla.deliver_to_location_id and
hlat.language(+) = userenv ('LANG') and
nvl(prla.transferred_to_oe_flag,'N')<>'Y' and
nvl(prha.closed_code,'!')<>'FINALLY CLOSED' and
prha.authorization_status not in ('CANCELLED','PRE-APPROVED','IN PROCESS','SYSTEM_SAVED')  and
not exists (
select
1
from
po_distributions_all pda,
po_req_distributions_all prda
where pda.req_distribution_id = prda.distribution_id and
prda.requisition_line_id = prla.requisition_line_id
) and
prla.item_id=msiv.inventory_item_id(+) and
xxen_po_upload.get_inv_org_id(prla.org_id)=msiv.organization_id(+) and
prla.destination_organization_id=mpd.organization_id(+) and
prla.source_organization_id=mps.organization_id(+) and
prla.category_id=mcv.category_id(+) and
prda.code_combination_id=gcc.code_combination_id(+) and
prda.project_id=ppv.project_id(+) and
prda.task_id=ptv.task_id(+) and
prda.expenditure_organization_id=haouve.organization_id(+)
Parameter NameSQL textValidation
Operating Unit
haouv.name=:operating_unit
LOV
Upload Mode
:upload_mode like '%' || xxen_upload.action_update
LOV
Initiate Approval on Import
:p_init_approval=:p_init_approval
LOV Oracle
Requisition Type
podt.document_subtype=:requisition_type
LOV
Group By
 
LOV
Requisition Number From
prha.segment1>=:requisition_number_from
LOV
Requisition Number To
prha.segment1<=:requisition_number_to
LOV
Creation Date From
prha.creation_date>=:creation_date_from
Date
Creation Date To
prha.creation_date<:creation_date_to+1
Date
Need By Date From
prla.need_by_date>=:need_by_date_from
Date
Need By Date To
prla.need_by_date<:need_by_date_to+1
Date
Requestor Name
papf2.full_name=:requestor_name
LOV
Buyer Name
papf1.full_name=:buyer
LOV
Created By
prha.created_by in (select user_id from fnd_user where user_name=:created_by)
LOV
Authorization Status
prha.authorization_status=:authorization_status
LOV
Download
Blitz Report™