Oracle-EBS-SQL
Library of Oracle EBS SQLs for operational Excel and ECC reporting, Discoverer replacement, Oracle EBS performance tuning and rapid report development.
CAC Open Purchase Orders
Description:
Report to show open purchase orders and related information. This report will convert any foreign currency purchases into the currency of the general ledger (defaulted from the inventory organization for this session). Use the To and From Transaction Date parameters to create an average receipt cost and use the Comparison Cost Type parameter to show a comparison amounts from another cost type.
Parameters:
Comparison Cost Type: enter the cost type for a comparison against the purchase order prices (optional). Defaulted from your Costing Method. Transaction Date From: starting transaction date for averaging the purchase order receipts (mandatory). Use these averages for comparing to the PO unit prices. Transaction Date To: ending transaction date for averaging the purchase order receipts (mandatory). Use these averages for comparing to the PO unit prices. Currency Conversion Date: enter the currency conversion date to use for converting foreign currency purchases into the currency of the general ledger (mandatory). Currency Conversion Type: enter the currency conversion type to use for converting foreign currency purchases into the currency of hhe general ledger (mandatory). Supplier Name: specific vendor or supplier you wish to report (optional). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Item Number: enter the specific item number(s) you wish to report (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional).
/* +=============================================================================+
– | Copyright 2020 - 2023 Douglas Volz Consulting, Inc.
– | All rights reserved.
– | Permission to use this code is granted provided the original author is
– | acknowledged. No warranties, express or otherwise is included in this
– | permission.
– +=============================================================================+
– |
– | Original Author: Douglas Volz (doug@volzconsulting.com)
– |
– | Program Name: open_po_rept.sql
– |
– | Version Modified on Modified by Description
– | ======= =========== ============== =========================================
– | 1.0 09 Sep 2020 Douglas Volz Initial Coding based on Purchase Price
– | variance report, xxx_ppv_lot_rept.sql
– | 1.1 10 Sep 2020 Douglas Volz Added inspection flag.
– | 1.2 01 Dec 2020 Douglas Volz Added variance and charge accounts
– | 1.3 20 Dec 2020 Douglas Volz Added promise date, Need By Date, project,
– | Expected Receipt Date, Target Price (PO List Price),
– | Customer Name and difference columns.
– | And added Minimum Cost Difference parameter.
– | 1.4 03 Feb 2021 Douglas Volz Merged OSP with stock purchase orders.
– | 1.5 05 Feb 2021 Douglas Volz Added PO averages and item cost information.
– | 1.6 07 Jul 2022 Douglas Volz Add multi-language item status.
– | 1.7 28 Nov 2023 Andy Haack Remove tabs, add org access controls, fix for G/L Daily Rates, outer joins.
– | 1.8 15 Dec 2023 Douglas Volz Setting the comparison cost type as mandatory, to
– | prevent a multiple rows error for the comparison cost type.
– +=============================================================================+*/
Parameters
Comparison Cost Type, Transaction Date From, Transaction Date To, Currency Conversion Date, Currency Conversion Type, Supplier Name, Category Set 1, Category Set 2, Item Number, Organization Code, Operating Unit, Ledger
Used tables
mtl_supply, cst_item_costs, cst_cost_types, mtl_parameters, org_access_view, po_vendors, wip_entities, po_lines_all, po_line_locations_all, po_headers_all, org_organization_definitions, gl_ledgers, hr_all_organization_units_vl, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual, po_releases_all, po_distributions_all, mtl_system_items_vl, mtl_uom_conversions_view, mtl_units_of_measure_vl, pa_projects_all, mtl_item_status_vl, cst_resource_costs, bom_resources, mtl_material_transactions, gl_daily_rates, gl_daily_conversion_types, hr_employees, fnd_common_lookups, po_lookup_codes, gl_code_combinations
Categories
Dependencies
If you would like to try one of these Oracle EBS SQLs without having Blitz Report installed, note that some of the reports require functions from utility package xxen_util.
Example Report
CAC Open Purchase Orders 07-Jul-2022 170534.xlsx
Report SQL
www.enginatics.com/reports/cac-open-purchase-orders/
Blitz Report™ import options
Oracle E-Business Suite Reporting Library
We provide an open source Oracle EBS SQLs as a part of operational and project implementation support toolkits for rapid Excel reports generation.
Blitz Report™ is based on Oracle EBS forms technology, and hence requires minimal training. There are no data or performance limitations since the output files are created directly from the database without going through intermediate file formats such as XML.
Blitz Report can be used as BI Publisher and Oracle Discoverer replacement tool. Standard Oracle BI Publisher and Discoverer reports can also be imported into Blitz Report for immediate output to Excel. Typically, reports can be created and version tracked within hours instead of days. The concurrent request output automatically opens upon completion without the need for re-formatting.
The Filters, Columns, Rows and Values fields are used to create and deliver the data in pivot table format with full drill down to details.
The Excel template upload functionality in Blitz Report allows users to create their own layouts by uploading an Excel template with additional sheets and charts, automatically refreshed when the report runs again. This allows to create custom dashboards and more advanced visualizations of report data.
You can download and use Blitz Report free of charge for your first 30 reports.
The installation and implementation process usually takes less than 1 hour; you can refer to our installation and user guides for specific details.
If you would like to optimize your Oracle EBS implementation and or operational reporting you can visit www.enginatics.com to review great ideas and example usage in blog. Or why not try for yourself in our demo environment.
Useful Links
Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Oracle Discoverer replacement – importing worksheets into Blitz Report™
Supply Chain Hub by Blitz Report™
© 2024 Enginatics