Sales Order Hold Report

Post date: Aug 23, 2013 5:31:38 PM

Oracle eBS, at the time of this posting, did not have a great means of being able to get all sales orders which had an unreleased hold associated to them. It is because of this which is why I created the below report which will list all sales orders which have an unreleased hold associated to either the header or a line. This also includes closed or cancelled sales orders as well so that data cleanup could be performed from the result.

SELECT

haou.name AS "Operating Unit"

, hca.account_number AS "Cust Acct #"

, hp.party_name AS "Customer"

, ooha.order_number AS "Order Number"

, ottt.name AS "Order Type"

, flv.meaning AS "SO Status"

, fu_ooha.user_name AS "SO Created By"

, DECODE(oohold.line_id, NULL, NULL, oola.line_number||'.'||oola.shipment_number) AS "Line Number"

, oola.ordered_item AS "Ordered Item"

, ohd.name AS "Hold Name"

, ohd.description AS "Hold Description"

, ohsa.hold_comment AS "Hold Comment"

, oohold.creation_date AS "Hold Created"

, fu_hold.user_name AS "Hold Created By"

FROM

apps.oe_order_holds_all oohold

INNER JOIN apps.fnd_user fu_hold

ON oohold.created_by = fu_hold.user_id

INNER JOIN apps.oe_hold_sources_all ohsa

ON ohsa.hold_source_id = oohold.hold_source_id

INNER JOIN apps.oe_hold_definitions ohd

ON ohd.hold_id = ohsa.hold_id

INNER JOIN apps.hr_all_organization_units_tl haou

ON haou.organization_id = oohold.org_id

INNER JOIN apps.oe_order_headers_all ooha

ON oohold.header_id = ooha.header_id

INNER JOIN apps.fnd_user fu_ooha

ON ooha.created_by = fu_ooha.user_id

INNER JOIN apps.fnd_lookup_values flv

ON ooha.flow_status_code = flv.lookup_code

AND flv.lookup_type = 'FLOW_STATUS'

INNER JOIN apps.oe_transaction_types_all otta

ON ooha.order_type_id = otta.transaction_type_id

INNER JOIN apps.oe_transaction_types_tl ottt

ON otta.transaction_type_id = ottt.transaction_type_id

AND ottt.language = 'US'

INNER JOIN apps.hz_cust_accounts hca

ON ooha.sold_to_org_id = hca.cust_account_id

INNER JOIN apps.hz_parties hp

ON hca.party_id = hp.party_id

LEFT JOIN apps.oe_order_lines_all oola

ON oohold.line_id = oola.line_id

WHERE oohold.released_flag = 'N'

AND UPPER(haou.name) LIKE UPPER(&OperatingUnit)||'%'