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)
                       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(+);

5 Responses to “How to find Order types in R12 (Script for Order types)”

Wes Baker said...
May 28, 2013 at 9:01 PM

THanks for this very useful


Anonymous said...
March 28, 2014 at 8:52 AM

Thank you! Very good!

Additionally you can use this AND:
AND ottl.language = 'US' --or other language


Matt K said...
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


Anonymous said...
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:


Marcelo said...
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

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.