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
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
