Monday, April 5, 2010
How to find Order types in R12 (Script for Order types)
Below query will be useful in collecting the order type information along with their corresponding operating units and other important data about them. The below script will also gives information about the Workflow process associated with each order type.
I tested this script in R12.1.1 instance
SELECT ott.org_id
,hrorg.name operating_unit_name
,ottl.name order_type_name
,ott.order_category_code
,ott.transaction_type_code
,ott.attribute1 order_type
,ott.attribute2 order_category
,ott.attribute3 adjustment_reason
,ott.def_transaction_phase_code
,ott.default_fulfillment_set
,ott.start_date_active
,ott.invoice_source_id
,ott.non_delivery_invoice_source_id
,ott.tax_calculation_event_code
,ott.price_list_id
,ott.arehouse_id
,ott.shipping_method_code
,ott.scheduling_level_code
,in_type.NAME default_return_line_type
,out_type.NAME default_order_line_type
,(SELECT wa.display_name
FROM wf_activities_tl wa
,oe_workflow_assignments wf
WHERE wf.order_type_id = ott.transaction_type_id
AND wa.NAME = wf.process_name
AND (wf.line_type_id = in_type.transaction_type_id
OR wf.line_type_id = out_type.transaction_type_id)
AND version = (
SELECT MAX(wa.version)
SELECT MAX(wa.version)
FROM wf_activities_tl wa
,oe_workflow_assignments wf
WHERE wa.NAME = wf.process_name
AND wf.order_type_id = ott.transaction_type_id
AND wf.line_type_id IS NOT NULL)
) wf_process
FROM oe_transaction_types_all ott
,oe_transaction_types_tl ottl
,hr_all_organization_units hrorg
,oe_transaction_types_tl in_type,
,oe_transaction_types_tl out_type
WHERE ott.transaction_type_id = ottl.transaction_type_id
AND hrorg.organization_id = ott.org_id
AND ott.DEFAULT_INBOUND_LINE_TYPE_ID = in_type.transaction_type_id(+)
AND ott.DEFAULT_OUTBOUND_LINE_TYPE_ID = out_type.transaction_type_id(+);
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
5 Responses to “How to find Order types in R12 (Script for Order types)”
May 28, 2013 at 9:01 PM
THanks for this very useful
March 28, 2014 at 8:52 AM
Thank you! Very good!
Additionally you can use this AND:
AND ottl.language = 'US' --or other language
May 2, 2014 at 2:26 PM
Thanks! You have an extra comma btw:
,oe_transaction_types_tl in_type,
,oe_transaction_types_tl out_type
January 25, 2018 at 11:45 PM
Its not working!!
Error:
ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:
January 16, 2019 at 12:22 PM
Same issue...
ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
Post a Comment