Building Reports From Oracle EBS

Hi,
 This  time I would like to share some basic queries to build reports on Oracle EBS.


Query to analyze Supplier Performance:-

 SELECT DISTINCT
aps.vendor_name AS vendor,
 poh.po_header_id AS po_numbers,
mtl.segment1 AS item_code, pol.promised_date AS promise_date,
rcv.transaction_date AS receipt_date,
pol.quantity AS ordered_qty,
pol.quantity_accepted AS accepted_qty,
ROUND (rcv.transaction_date - pol.promised_date) AS variance_days,
pol.quantity - pol.quantity_accepted AS variance_qty
FROM
ap_suppliers aps,
po_headers_all poh,
po_line_locations_all pol,
rcv_transactions rcv,
po_lines_all pll,
mtl_system_items_b mtl

WHERE
          aps.vendor_id = poh.vendor_id
AND pol.line_location_id = rcv.po_line_location_id
AND pll.po_line_id = pol.line_location_id
AND pll.item_id = mtl.inventory_item_id
ORDER BY 1, 2, 3


Query to Find Variance in Cost per Meterial Over a Period:-


select CST.creation_Date as PERIOD,
MTL.SEGMENT1 as Item,
MTL.ITEM_TYPE,
avg(CST.MATERIAL_COST) as Cost
from
CST_ITEM_COSTS CST,
MTL_SYSTEM_ITEMS_B MTL
WHERE
CST.INVENTORY_ITEM_ID=MTL.INVENTORY_ITEM_ID
and MTL.item_type='P'
group by MTL. SEGMENT1,CST.creation_Date,MTL.ITEM_TYPE
order by 1

Shipments made vs Orders booked in a period


select
REQUEST_DATE PERIOD,
NVL(sum(ORDERED_QUANTITY),0) ORDERED,
NVL(SUM(SHIPPED_QUANTITY),0) SHIPMENTS
from OE_ORDER_LINES_ALL
GROUP BY REQUEST_DATE
ORDER BY 1

Order lines comparison between Schedule date and Shipment date


select DISTINCT
LINE_ID ORDER_LINES,
SCHEDULE_SHIP_DATE SCHEDULED_DATE,
ACTUAL_SHIPMENT_DATE SHIPMENT_DATE
from
OE_ORDER_LINES_ALL
Order Lines comparison between promise date and Shipment date


select
DISTINCT LINE_ID ORDER_LINE,
PROMISE_DATE,
ACTUAL_SHIPMENT_DATE SHIPMENT_DATE
FROM
OE_ORDER_LINES_ALL

Performance Analysis

SELECT unit_price, amount, job_id, purchase_basis, line_num, line_type,


item_revision, item_description, unit_meas_lookup_code, quantity,

base_unit_price, vendor_product_num, supplier_ref_number, base_uom,

base_qty, secondary_uom, secondary_qty, qc_grade,

secondary_unit_of_measure, secondary_quantity, preferred_grade,

list_price_per_unit, market_price, price_type,

allow_price_override_flag, not_to_exceed_price,

negotiated_by_preparer_flag, contract_id, oke_contract_version_id,

note_to_vendor, un_number, hazard_class, capital_expense_flag,

transaction_reason, contractor_first_name, contractor_last_name,

start_date, quantity_committed, committed_amount, row_id,

transaction_reason_code, price_type_lookup_code, cancel_flag,

cancel_date, expiration_date, price_break_lookup_code,

firm_status_lookup_code, po_header_id, creation_date,

hazard_class_id, program_id, program_update_date, request_id,

closed_by, closed_date, closed_reason, attribute2, attribute4,

attribute7, attribute9, attribute11, attribute14, category_id,

qty_rcv_tolerance, type_1099, attribute_category, attribute1,

attribute3, attribute5, attribute6, attribute8, attribute10,

reference_num, attribute12, attribute13, attribute15,

min_release_amount, closed_code, government_context,

program_application_id, min_order_quantity, max_order_quantity,

over_tolerance_error_flag, unordered_flag, closed_flag,

user_hold_flag, cancelled_by, cancel_reason, firm_date, po_line_id,

last_update_date, last_updated_by, line_type_id, last_update_login,

created_by, item_id, un_number_id, from_header_id, from_line_id,

from_line_location_id, order_type_lookup_code, matching_basis,

outside_operation_flag, allow_item_desc_update_flag,

planned_item_flag, allowed_units_lookup_code,

outside_operation_uom_type, primary_unit_class,

global_attribute_category, global_attribute1, global_attribute2,

global_attribute3, global_attribute4, global_attribute5,

global_attribute6, global_attribute7, global_attribute8,

global_attribute9, global_attribute10, global_attribute11,

global_attribute12, global_attribute13, global_attribute14,

global_attribute15, global_attribute16, global_attribute17,

global_attribute18, global_attribute19, global_attribute20,

oke_contract_header_id, manual_price_change_flag,

tracking_secondary_default_ind, grade_control_flag,

secondary_uom_code

FROM po_lines_v

WHERE NVL (cancel_flag, 'N') = 'N'

AND NVL (closed_code, 'OPEN') != 'FINALLY CLOSED'

AND (po_header_id = 64034)

ORDER BY line_num