9/23/2014
Document 1916690.1
Introduction to PO_REQUISITION_UPDATE_PUB API (Doc ID 1916690.1)
In this Document
Abstract
History
Details
Patch and Source Files
Columns of Requisition that can be updated by PO_REQUISITION_UPDATE_PUB API
Flow Charts
How to Launch the PO_REQUISITION_UPDATE_PUB API?
Data preparation
How to Launch the PO_REQUISITION_UPDATE_PUB API?
Procedure call
Debug Information Collection
APPLIES TO:
Oracle Purchasing - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
ABSTRACT
Public API PO_REQUISITION_UPDATE_PUB is the implementation of the enhancement request 17261133 which
allowing user to perform update operation on Requisition Header, Line and Distribution. It allows update on requisition
that is in INCOMPLETE or APPROVED status and it hasn't attached to Purchase Order.
HISTORY
Author : TC Cheng
Create Date : 12-AUG-2014
Update Date : 24-AUG-2014
Expire Date :
DETAILS
Patch and Source Files
Functionality of public API PO_REQUISITION_UPDATE_PUB can be obtained via application of patch
17261133:R12.PO.B and the API is currently available to 12.1.3 ONLY.
Patch 17261133:R12.PO.B will deliver following source files and packages
POXRQUPBS.pls and POXRQUPB.pls (Package PO_REQUISITION_UPDATE_PUB)
POXRQUPVTS.pls and POXRQUPVTB.pls (Package PO_REQUISITION_UPDATE_PVT)
POXRQVLS.pls and POXRQVLB.pls (Package PO_REQUISITION_VALIDATE_PVT)
Only Package PO_REQUISITION_UPDATE_PUB is classified as PUBLIC API, and rest of packages are classified as
PRIVATE API and they are reserved for Oracle Internal usage.
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
1/16
9/23/2014
Document 1916690.1
Columns of Requisition that can be updated by PO_REQUISITION_UPDATE_PUB API
Table
Column
PO_REQUISITION_HEADERS_ALL summary_flag
Table
Column
PO_REQUISITION_LINES_ALL reference_num
enabled_flag
rfq_required_flag
end_date_active
to_person_id
description
line_type_id
note_to_authorizer
item_description
attribute_category
unit_meas_lookup_code
attribute1
unit_price
attribute2
base_unit_price
attribute3
quantity
attribute4
amount
attribute5
source_type_code
attribute6
suggested_buyer_id
attribute7
document_type_code
attribute8
blanket_po_header_id
attribute9
blanket_po_line_num
attribute10
currency_code
attribute11
rate_type
attribute12
rate_date
attribute13
rate
attribute14
currency_unit_price
attribute15
currency_amount
government_context
un_number_id
authorization_status
hazard_class_id
source_organization_id
source_subinventory
destination_type_code
destination_organization_id
destination_subinventory
secondary_quantity
vendor_id
vendor_site_id
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
2/16
9/23/2014
Document 1916690.1
vendor_contact_id
preferred_grade
secondary_unit_of_measure
transaction_reason_code
order_type_lookup_code
org_id
justification
note_to_agent
need_by_date
note_to_receiver
urgent_flag
suggested_vendor_product_code
deliver_to_location_id
oke_contract_header_id
attribute1
attribute2
attribute3
attribute4
attribute5
attribute6
attribute7
attribute8
attribute9
attribute10
attribute11
attribute12
attribute13
attribute14
attribute15
category_id
Flow Charts
(see Attachments section below)
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
3/16
9/23/2014
Document 1916690.1
How to Launch the PO_REQUISITION_UPDATE_PUB API?
Data preparation
PO_REQUISIITON_UPDATE_PUB API is using "Record" type variables as input parameters, so PL/SQL need to be
constructed for loading Requisition data including "To be updated" data into these input parameters before API
was launched. For the usage of "Record" type variables, please reference to Oracle Database PL/SQL Language
Reference.
Detailed definition of these "Record" type variables can be found in source file POXRQUPS.pls which are
req_hdr -- Stores all the requisition header data
req_hdr_tbl -- Table of req_hdr
req_line_rec_type -- Stores all requisition line data.
req_line_tbl -- Table of req_line_rec_type
req_dist -- Store all the requisition distribution data
req_dist_tbl -- Table of req_dist
Here below are usage explanation for variable req_hdr, req_line_rec_type and req_dist:
PO_REQUISITION_UPDATE_PUB.
Column / Element
Description
req_hdr
Mantatory Columns (not
updatable)
Updatable Columns
requisition_header_id requisition_header_id
OR segment1 is
Mandatory
segment1
requisition_header_id
OR segment1 is
Mandatory
org_id
operating unit id
summary_flag
Summary flag of
Requisition Header
enabled_flag
Enable flag of
Requisition Header
segment2
<Reserved for future
use>
segment3
<Reserved for future
use>
segment4
<Reserved for future
use>
segment5
<Reserved for future
use>
start_date_active
<Reserved for future
use>
end_date_active
End date active of
Requisition Header
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
4/16
9/23/2014
Document 1916690.1
Elements for internal usage
PO_REQUISITION_UPDATE_PUB.
description
Description of
Requisition Header
note_to_authorizer
Note to approver
attribute_category
DFF Attribute Values
attribute1
DFF Attribute Values
attribute2
DFF Attribute Values
attribute3
DFF Attribute Values
attribute4
DFF Attribute Values
attribute5
DFF Attribute Values
attribute6
DFF Attribute Values
attribute7
DFF Attribute Values
attribute8
DFF Attribute Values
attribute9
DFF Attribute Values
attribute10
DFF Attribute Values
attribute11
DFF Attribute Values
attribute12
DFF Attribute Values
attribute13
DFF Attribute Values
attribute14
DFF Attribute Values
attribute15
DFF Attribute Values
government_context
USSGL descriptive
flexfield context
column
authorization_status
Authorization status
of the Requisition
submit_for_approval
<Reserved for Oracle
internal usage>
note_to_approver
To be Clarified with
Development
error_message
<Reserved for Oracle
internal usage>
Column / Element
Description
req_line_rec_type
Mantatory Columns (not
requisition_number
Either requisition_header_id or
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
5/16
9/23/2014
Document 1916690.1
updatable)
Optional Columns
requisition_number is mandatory
requisition_header_id
Either requisition_header_id or
requisition_number is mandatory
requisition_line_num
Either requisition_line_id or
requisition_line_num is mandatory
requisition_line_id
Either requisition_line_id or
requisition_line_num is mandatory
org_id
Operating Unit ID
suggested_buyer_name
Will be used to derive value for
suggested_buyer_id from view
PO_BUYERS_VAL_V
blanket_po_number
Will be used to derive value
for blanket_po_header_id from table
PO_HEADERS_ALL
un_number
Will be used to derive value for
un_number_id from view
PO_UN_NUMBERS_VAL_V
hazard_class
Will be used to derive value
for hazard_class_id from view
PO_HAZARD_CLASSES_VAL_V
source_organization_name
Will be used to derive value for
source_organization_id from view
ORG_ORGANIZATION_DEFINITIONS
suggested_vendor_name
Will be used to derive value for
vendor_id from view PO_VENDORS
suggested_vendor_location
Will be used to derive value for
vendor_site_id from view
PO_VENDORS_SITES_ALL
suggested_vendor_contact
Will be used to derive value for
vendor_contact_id from view
PO_VENDOR_CONTACTS
suggested_vendor_phone
Suggested Vendor Phone number
requestor_name
Will be used to derive value for
to_person_id from view
PER_WORKFORCE_CURRENT_X
deliver_to_location_code
Will be used to derive value for
deliver_to_location_id from view
HR_LOCATIONS
line_type
Will be used to derive value for
line_type_id from table
PO_LINE_TYPES_B
contract_number
Will be used to derive value for
oke_contract_header_id from view
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
6/16
9/23/2014
Document 1916690.1
OKC_K_HEADERS_B
destination_organization
Will be used to derive value for
destination_organization_id from
view
ORG_ORGANIZATION_DEFINITIONS
item_id
If null, will be derived value from
item_number
item_revision
Item Revision (will be validated from
MTL_ITEM_REVISIONS)
item_number
Will be used to derive vallue for
item_id from view
MTL_SYSTEM_ITEM_KFV
reference_num
Reference Number
rfq_required_flag
Indicates whether an RFQ is
required prior to placement on a PO
to_person_id
If null, will be derived value
from requestor_name
line_type_id
If null, will be derived value from
line_type
item_description
IDescription of line item
unit_meas_lookup_code
UOM of item at requisition line
unit_price
Unit Price in functional currency
base_unit_price
Base Unit Price
quantity
Ordered quantity
amount
Amount on requisition line
source_type_code
Requisition source type of item
suggested_buyer_id
If null, will be derived value from
suggested_buyer_name
document_type_code
Source document type
blanket_po_header_id
If null, will be derived value from
blanket_po_number
blanket_po_line_num
Suggested source blanket
agreement or catalog quotation line
number
currency_code
Unique identifier for the currency
rate_type
Currency conversion rate type
rate_date
Currency conversion rate date
rate
Currency conversion rate
currency_unit_price
Foreign currency unit price
currency_amount
Foreign currency amount on the
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
7/16
9/23/2014
Document 1916690.1
Requisition line
un_number_id
If null, will be derived value from
un_number
hazard_class_id
If null, will be derived value from
hazard_class
source_organizaiton_id
If null, will be derived value from
source_organization_name
source_subinventory
Inventory source subinventory
destination_type_code
Destination type of requisition line
destination_organizaiton_id
If null, will be derived value from
destination_organization
destination_subinventory
Destination subinventory of
requisition line
secondary_quantity
Secondary quantity
vendor_id
If null, will be derived value
from suggested_vendor_name
vendor_site_id
If null, will be derived value from
suggested_vendor_location
vendor_contact_id
If null, will be derived value from
research_agent_id
<Reserved for future usage>
on_line_flag
<Reserved for future usage>
preferred_grade
Preferred quality grade of the item
ordered
secondary_uom_code
Secondary UOM
transaction_reason_code
Transaction Reason
order_type_lookup_code
Order type
justification
Justification
note_to_agent
Note to Buyer
need_by_date
Need by date of requisition line
note_to_receiver
Note to Deliverer
urgent_flag
Urgent flag
suggested_vendor_product_code
Suggested Vendor Product
deliver_to_location_id
If null, will be derived value
from deliver_to_location_code
oke_contract_header_id
If null, will be derived value
from contract_number
attribute1
DFF attribute columns at line level
attribute2
DFF attribute columns at line level
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
8/16
9/23/2014
Elements for internal usage
Document 1916690.1
attribute3
DFF attribute columns at line level
attribute4
DFF attribute columns at line level
attribute5
DFF attribute columns at line level
attribute6
DFF attribute columns at line level
attribute7
DFF attribute columns at line level
attribute8
DFF attribute columns at line level
attribute9
DFF attribute columns at line level
attribute10
DFF attribute columns at line level
attribute11
DFF attribute columns at line level
attribute12
DFF attribute columns at line level
attribute13
DFF attribute columns at line level
attribute14
DFF attribute columns at line level
attribute15
DFF attribute columns at line level
category_id
If null, will be derived value from
item_category
rebuild_accounts
Default 'N', indicate whether to
rebuild accounts on distributions
item_category
Will be used to derive value for
category_id from view
MTL_CATEGORIES_KFV
manufacturer_part_number
If null, will be derived value from
view
MTL_MFG_PART_NUMBERS_ALL_V
for the item_id
manufacturer_name
If null, will be derived value from
view
MTL_MFG_PART_NUMBERS_ALL_V
for the item_id
manufacturer_id
If null, will be derived value from
view
MTL_MFG_PART_NUMBERS_ALL_V
for the item_id
destination_type
<Reserved for future use>
deliver_to_location
<Reserved for future use>
source_type
<Reserved for future use>
negotiated_by_preparer
<Reserved for future use>
error_message
<Reserved for Oracle internal
usage>
submit_for_approval
<Reserved for Oracle internal
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
9/16
9/23/2014
Document 1916690.1
usage>
note_to_approver
<Reserved for Oracle internal
usage>
authorization_status
<Reserved for Oracle internal
usage>
action_flag
Default 'Update' <Reserved for
Oracle internal usage>
How to Launch the PO_REQUISITION_UPDATE_PUB API?
Procedure call
PO_REQUISIITON_UPDATE_PUB.update_requisition is the main procedure of the
PO_REQUISIITON_UPDATE_PUB API, it will update Header, Line(s) and Distribution(s) of ONE requisition at one
time, if multiple requisitions need to be updated by API then PL/SQL need to be constructed to repeat "Data
preparation" and "Procedure call" cycle for every single requisition.
PO_REQUISIITON_UPDATE_PUB.update_requisition has 6 IN parameters and 3 OUT parameters
Parameter Type
Parameter
Remark
IN
p_init_msg_list
T(Ture) OR F (False).
Default is T. Debug
messages will be logged
if the parameter was set
to T.
IN
p_commit
Y or N. System will save
the change if the
parameter was set to Y.
IN
p_submit_approval
Y or N. System will
submit the Requisition for
approval if the parameter
was set to Y.
IN
p_req_hdr
"Collection Type"
variables for storing
Requisition Header data
IN
p_req_line_tbl
"Collection Type"
variables for storing
Requisition Line data
IN
p_req_dist_tbl
"Collection Type"
variables for storing
Requisition Distribution
data
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
10/16
9/23/2014
Document 1916690.1
OUT
x_return_status
Return S (Sucess) OR E
(Error). Indicating the
API completed with
success or error.
OUT
x_msg_count
Return number of error
detected
OUT
x_msg_data
Return error message if
any
Example
Example of using PO_REQUISIITON_UPDATE_PUB.update_requisition to update Requisition's Item Description,
Quantity and Deliver-to Location via PL/SQL script.
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
11/16
9/23/2014
Document 1916690.1
Before PO_REQUISIITON_UPDATE_PUB.update_requisition was launched
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
12/16
9/23/2014
Document 1916690.1
Run the PL/SQL script via SQL*PLUS
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
13/16
9/23/2014
Document 1916690.1
After PO_REQUISIITON_UPDATE_PUB.update_requisition was launched
Debug Information Collection
When PO_REQUISIITON_UPDATE_PUB.update_requisition was launched with parameter p_init_msg_list set to T,
debug message will be logged. The debug message as well as API complete status and error message will also be
returned via following OUT parameters of PO_REQUISIITON_UPDATE_PUB.update_requisition
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
14/16
9/23/2014
Document 1916690.1
- x_return_status
- x_msg_count
- x_msg_data
API will also record further debug detail in PO_INTERFACE_ERROS table with
-
interface_type = REQ_UPDATE
table_name = PO_REQUISITION_HEADERS or PO_REQUISITION_LINES or PO_REQ_DISTRIBUTIONS
interface_header_id = PO_REQUISITION_HEADERS_ALL.requisition_header_id
interface_line_id = PO_REQUISITION_LINES_ALL.requisition_line_id
interface_distribution_id = PO_REQ_DISTRIBUTIONS_ALL.distribution_id
column_name = Indicates column OR process that failed in validation
error_message = Stores the validation failure message if any
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
15/16
9/23/2014
Document 1916690.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=14ua1wy23f_115&id=1916690.1
16/16
