Examples of Custom Query used in PO Wizard
Examples where the Custom Query would be beneficial to users are described below:
Data Conversion
For conversion purposes, the custom query functionality is ideal as you can select all outstanding Purchase Orders to identify those that are yet to be billed or have only been partially billed.
The query criteria can also include those that have been partially receipted or yet to be receipted. Then you can use the Close PO functionality to close off these lines and create new lines in the new E-Business instance for the outstanding amounts.
You may have other criteria that you wish to use to identify Purchase Orders to be converted to your new E-Business instance.
Open Purchase Orders
The custom query functionality is handy for finding Purchase Orders that failed to close due to receipting and invoice tolerances that are negligible in an environment that has zero tolerance.
It also enables you to write a query to find all Purchase Orders that are either open for invoicing or receiving where the quantity is receipted and the quantity billed is not equal to the quantity ordered, or some variation of these conditions.
You can then download the list of the Purchase Orders into the Close PO template and close all of them off in one action.
The example below provides SQL queries and a step-by-step guide for creating a simple query set for downloading Blanket Header’s and associated Lines:
Blanket Header Query
SELECT DISTINCT h.po_header_id Header_ID
,h.segment1 PO_Number
,h.vendor_name Supplier
,h.vendor_site_code Site
,h.blanket_total_amount Amount_Agreed
,h.start_date Header_Effective_Date
,h.end_date Header_Expiration_Date
,h.amount_limit Amount_Limit
,h.GLOBAL_AGREEMENT_FLAG Global
FROM po_headers_v h
WHERE type_lookup_code = 'BLANKET'
Blanket Line Query
SELECT l.po_header_id HeaderID
,l.po_line_id LineID
,l.item_description Line_Description
,l.unit_meas_lookup_code UOM
,l.line_num Line_Number
,l.line_type Line_type
,mtl.concatenated_segments Item
,cat.concatenated_segments Category
,l.unit_price Price
,l.quantity Line_Quantity
FROM po_lines_v l
,mtl_system_items_kfv mtl
,mtl_categories_kfv cat
where
mtl.inventory_item_id(+) = l.item_id
AND mtl.organization_id(+) = 204
AND cat.category_id(+) = l.category_id
Refer to section:
