Query to get the Defaulting rules defined in Order Managment

SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       DECODE (
          src_type,
          'API', src_api_pkg || '.' || src_api_fn,
          'CONSTANT', src_constant_value,
          'SYSTEM', src_system_variable_expr,
          'SEQUENCE', src_sequence_name,
          'DATABASE', src_database_object_name || '.' || src_attribute_code,
          'WAD_ATTR', ' ',
          'WAD_OBJATTR', ' ',
          src_constant_value)
          source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d, oe_lookups l, oe_def_attr_rules_v k
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND d.attribute_code = k.attribute_code
       AND k.src_type NOT IN
              ('RELATED_RECORD', 'SAME_RECORD', 'PROFILE_OPTION')
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND (k.src_type = l.lookup_code
            OR (k.src_type = k.attribute_code AND l.lookup_code = 'CONSTANT'))
UNION
SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       a.object_name || '.' || a.name source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d,
       oe_lookups l,
       oe_def_attr_rules_v k,
       ak_object_attributes_vl a
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND d.attribute_code = k.attribute_code
       AND a.database_object_name(+) = k.src_database_object_name
       AND a.attribute_code(+) = k.src_attribute_code
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND k.src_type = l.lookup_code
       AND k.src_type = 'RELATED_RECORD'
UNION
SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       a.name source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d,
       oe_lookups l,
       oe_def_attr_rules_v k,
       ak_object_attributes_vl a
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND d.attribute_code = k.attribute_code
       AND a.database_object_name(+) = k.database_object_name
       AND a.attribute_code(+) = k.src_attribute_code
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND k.src_type = l.lookup_code
       AND k.src_type = 'SAME_RECORD'
UNION
SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       a.user_profile_option_name source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d,
       oe_lookups l,
       oe_def_attr_rules_v k,
       fnd_profile_options_vl a
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND a.profile_option_name(+) = k.src_profile_option
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND k.src_type = l.lookup_code
       AND k.src_type = 'PROFILE_OPTION';

References:
http://oracleappsscriptsandtips.blogspot.ca/2012/09/query-to-get-defaulting-rules-defined.html

Accessing Approval Management Engine (AME) - Roles and Grants

Accessing Approval Management Engine (AME) is not possible as we access other responsibility / functions in Oracle Applications like Order Management / Application Developer etc.

AME is controlled by RBAC (Role Based Access Control). So in this case even you assign responsibility to the user but if user don't have the appropriate roles then you will not be able to access Approval Management functions.

There are two seeded responsibility for AME :-

• Approvals Management Administrator
• Approvals Management Business Analyst

Let's assign the responsibility directly to user and see what happens.

 Login with respective user.

Chose Approval Management Responsibility

You will receive below error.

With the screen shots above we can see that even though the responsibility is assigned to user while trying to access it, It says There is no valid navigation for this responsibility.

Accessing AME is two step process :-

• Assign the required roles to the user : This will give user access to use Approval Management functions.
• Grant access to respective AME transaction type : This will provide access to AME transaction type. If we don't perform this step then we will be able to access AME responsibility but will not be able to access any transaction types.

We have following seeded roles provided by Oracle that can be used to access AME.

• Approvals Management Administrator
• Approvals Management Business Analyst
• Approvals Management Process Owner
• Approvals Management System Administrator
• Approvals Management System Viewer

Step -1 : Assign the Roles

• Login with administrator / sysadmin user.
  
• Navigate to User Management -> User.
  
• Enter User Name and click on Go to find the user.
• Click on Update button
  
• Click on Assign Roles button.
  
• Search the role 'Approval Management Administrator' from LOV and select it.
  
  
• Based on the role chosen you will see number of functions added under users roles.
  

Step -2 : Grant Access to Transaction Types

• Login with administrator / sysadmin user.
• Navigate to Functional Administrator -> Grants -> Create Grants.
  
  
• Enter a name and description for the Grant
• Enter effective from date and optionally effective to date.
• Select Grantee Type from Security Context region
• All Users : The grant will be applied to all the users and all users will be given same right / privileges that is part of this grant. 
• Group Of Users : The grant will be applied to users those are part of the group.
• Specific User : Grant will be applicable of specific set of users that you select.
• I have opted for specific user.
• In addition to this you can also choose Operating Unit and Responsibility to make is more secure. Say you want to provide a user to AME access to US operating Unit only. In that case you can choose operating unit. If there is not such restriction required then leave it blank.
• From Data Security region select an object. In our case it will be 'AME Transaction Types'
  
• Click Next
• In the Data Context Type select 'All Rows'.
  
• Select 'AME Calling Applications' as permission set and click Next to review the setup.
  
• Finish to complete.
  

Now you are good to use AME and setup approvals. Happy Setups...

References:
http://www.oracleappsnfusion.com/2014/01/accessing-ame.html
http://mtalmasri.blogspot.com/2012/04/oracle-ebs-repairing-xxx-is-not-valid.html

How to search within Long data type column in Oracle (this is one way of doing it)

select at.trigger_name, (at.trigger_body) trig_body, at.*
from all_triggers at
where (trigger_name like 'XX%'
OR table_name like 'XX%')

create xx_triggers
as
select at.trigger_name, to_lob(at.trigger_body) trig_body
from all_triggers at
where (trigger_name like 'XX%'
OR table_name like 'XX%');

select xt.trigger_name,  upper(xt.trig_body)--, at.*
from xx_triggers xt
where upper(xt.trig_body) like '%SEGMENT1%';

Patch history tables in Oracle Apps

AD_APPL_TOPS - Stores information about all APPL_TOPS utilizing this instance.
AD_APPLIED_PATCHES - Stores information about all distinct updates applied.
AD_BUGS - Stores information about all distinct bug fixes applied.
AD_DRIVERS - Stores information about the drivers that comprise an update.
AD_FILE_VERSIONS - Stores version information for files.
AD_FILES - Stores information about files in the system.
AD_PATCH_COMMON_ACTIONS - Stores information about actions in update driver files.
AD_PATCH_DRIVER_LANGS - Stores information about the languages included in an update driver file.
AD_PATCH_DRIVER_MINIPKS - Stores information about minipacks.
AD_PATCH_RUN_BUG_ACTIONS - Stores detailed information about actions applied by an update.
AD_PATCH_RUN_BUGS - Stores information about all bug fixes included in an update , applied or not.
AD_PATCH_RUNS - Stores information about all incvocations of AutoPatch for all of the various updates applied.
AD_RELEASES - Stores release information about the Oracle Applications system.

References:
http://appsoracle.blogspot.ca/2012/03/patch-history-tables-in-oracle-apps.html

Oracle Application URL from the Apps database

SELECT profile_option_value
  FROM apps.fnd_profile_option_values
 WHERE profile_option_id =
                         (SELECT profile_option_id
                            FROM apps.fnd_profile_options
                           WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')
   AND level_value = 0

OAF Basic Part 1

Index What is OA Framework Why OA Framework OAF Vs FORMS OA Framework Architecture MVC Architecture and its Components Profile Options JDeveloper Structure Naming Standards What is OA Framework? Oracle Application Framework(OA Framework) is a proprietary framework developed by Oracle Corporation for application development within the Oracle E-Business Suite. Available to customers for personalization, customizations and custom-application development. The OA Framework is a Model-view-controller (MVC) framework built using J2EE (Java 2 Platform, Enterprise Edition)technologies. Why OA Framework? Advantages : Enterprise-Grade Performance and Scalability Improved End User Productivity Highly extensible architecture Browser Look and Feel (BLAF terminology) for all applications Open industry Standards such as XML, HTML, Java, JSP, SQL and Web Services.  Using Oracle Forms OA Framework  With Oracle Forms I can create custom Apps modules and modify seeded Apps Forms. JDeveloper is the development tool for creating custom Apps web pages and modifying seeded Apps web pages. I use SQL and PL/SQL in Oracle Forms We will still use SQL statements, specifically in View Objects.  We can execute PL/SQL, but within Java code.  If your Java skills are nil or weak, now might be a good time to plan a training program.  See What To Do Next for more information. An Oracle Forms file (.fmb) contains screen design and custom procedures and functions. Reusable code, that is the mantra for JDeveloper and OA Framework.  In Oracle Forms a data block references a table or view, and that definition is contained within the .fmb.  With JDeveloper, we create view objects or entity objects that can be reused by other web pages. In Forms, I can reuse forms, libraries, and menus by referencing. OA Framework is an assembly of reusable objects. I use TEMPLATE.fmb as the starting point for any custom Apps forms.  TEMPLATE.fmb contains references to libraries, property settings, and menus that will help make custom forms look-and-feel like Oracle-provided forms. OA Framework is a development and deployment platform presented through JDeveloper.  The seeded objects and settings give custom web pages the same look-and-feel as Oracle-provided web pages In Forms I create and use canvases: Forms requires a root canvas I use stacked canvases for controlling access to groups of items Fields and buttons appear on a canvas Properties control the appearance of canvases and items In OA Framework we use the concept of a region: *A web page requires a root region *Regions can be nested *Widgets (fields, buttons, images, etc) appear in a region * Properties control the appearance of widgets

Components of MVC Architecture

Interaction between model, view and controller

Entity Object (EO)

Entity Object is based on database table or other data source. Entity Object contains attributes which represent database columns. All insert/update/delete (DML Operations) transactions go through EO to database.

View Object (VO)

Two types      1. SQL based      2. EO based Represents a query result. Is used for joining, filtering, projecting, and sorting your business      data. Can be based on any number of entity objects (EOs) and provides      access to EOs.

Application Module (AM)

Every page must have a root application module. Handles transactions. Application Modules serve as containers for related BC4J Objects. The objects are related by participating in the same task (for example, a multi-page UI for creating purchase orders) Application Modules provide transaction context Application Modules establish database connections Application Modules may be nested to provide more complex application modules

Controller (CO) in MVC Architecture

Responds to user actions and directs application flow. Model objects like EO and VO can't be accessed directly from the Controller Class, except AM. Contains methods such as            1. ProcessRequest: Fires when OAF page loads for the first time.            2. ProcessFormRequest: Fires when user submits the page.                                            (Ex: clicking GO button)

MVC Architecture

Model-View-Controller (MVC) Design Pattern

Profile Options

To Enable the “about this page” link on the page set the below profile option as “yes” FND: Diagnostics : Yes To Enable the “personalize page” link on the page set the below profile options as “yes” Personalize Self-Service Defn : Yes FND: Personalization Region Link Enabled : Yes

Jdeveloper Structure

Naming Standards

Page Name Ends with PG/PAGE Region name ends with RN Entity Object Name ends with EO View Object ends with VO Application Module Name Ends with AM Controller Name ends with CO

References:

http://oafadfmahi.com/oaf/oafbasic1.html

Data Quality Management(DQM) in TCA

In this article I will touch base upon the data quality management feature of TCA. This article also contains a link to a DQM presentation that will compliment the Q & A in this article. A combination of this article and thepowerpoint presentation should help you understand the basics of DQM [ Data Quality Management ] in Oracle TCA. 
Is DQM a part of TCA?
Yes it is, the setup can be done using responsibility named "Trading Community Manager".

What are the setup steps for DQM?
Note: These steps will get clearer once you finish reading this article + PowerPoint Presentation
Here are the steps :-
1. Identify the Attributes that you wish to use for Search and Scoring.
2. Define custom attributes for those columns on which you would like to either search or score, but those columns are not in TCA tables.
3. Assign transformation to each column. Define custom transformation rules if need be, by using PL/SQL 
4. Decide which transformed values are used for Searching and which transformed values are to be used for [both] Searching and/or just Scoring
The attribute transformations that you wish to search upon, will have "Acquisition" checkbox set to Yes.
5. Define your match rules. You can define as many match rules
6. In Match rules, you specify how the users search criteria[values being used by users] are transformed prior to acquisition.
7. Define scoring/ranking rules
8. Run the staging process.


Give me some examples where DQM is used?
Lets say your organization has 50000 employees and a person named john smith gets recruited for a job. HRMS team must know whether this John Smith has had any kind of interaction with the organization in past. Ideally if John Smith has had any kind of interaction with the company in past, then there must exist an entry for this record in TCA. In this case, HR must ensure that HRMS record can be made to point to existing TCA record. By doing so a 360 degree view of the employee be maintained in your organization.


So DQM is about being able to identify a duplicate entry in your system.
Correct, and this I made possible by applying some transformations as you will see in the presentation.


Is DQM only applicable at party level in TCA?
No way, DQM can be made to work at party address level & contact level too.


Isn't finding duplicates as simple as matching the social security number or national insurance number?
Correct, but say if your payables department wants to pay expenses to a third party for their interview travel expenses, then payables team will not have access to SSN or "NI Number". In this case, they rely upon other attributes of the person to find duplicates.


Ah, it means DQM will not be used merely by HRMS department or the Receivables department?
Certainly not, in an efficient organization, any department that registers a party into oracle apps must have access to DQM functionality.


But what if Payables team wants to use DQM, but with different set of search and ranking criteria altogether?
This is a very common requirement. Hence in this case Oracle lets you define multiple "Match Rules" in DQM.


Fine, we can define different Match Rules, but how will we enforce Payables users to use say "Match Rule 1" and HRMS users to use say "Match Rule 2"?
You can differentiate such requirements by creating different DQM search responsibilities. For each such responsibility you will assign profile option "DQM Match Rule for Search" to respective value.
In case you do not wish to have different responsibilities, though recommended, you can set this profile option at user level too.


But DQM searches for duplication in TCA tables, however "NI Number" and SSN [Social Security] are stored in HRMS tables. How will DQM search data in other modules?
You can define custom attributes in DQM. Custom attribute is nothing but a pseudo column which uses pl/sql to return the value,in this case from per_people_f


Does DQM search data within TCA tables?
Well, after defining rules for DQM setup, you run a concurrent program named "DQM Staging Program". This process de-normalises TCA data into staging tables. Transformed values for columns are stored in staging table. This will be clear when you go through the PowerPoint presentation.


What are the staging tables?
There are a list staging tables where DQM Transformed data is stored. Each transformed value resides in a different column. The transformed values used for Acquisition are indexed by Oracle.
The list of tables are
HZ_STAGED_CONTACTS
HZ_STAGED_CONTACT_POINTS
HZ_STAGED_PARTIES
HZ_STAGED_PARTY_SITES



What is event "oracle.apps.ar.hz.DQM.realtimesync" which keeps on firing when employee records or the TCA records are modified?
As you know, the TCA data is transformed and de-normalized into staging tables. Any changes to the TCA data must update the DQM staging tables, so that changes can be searched upon in DQM. The subscription of this event reads the pending entries in staging interface table HZ_DQM_SYNC_INTERFACE and transfers them to DQM staging tables after having applied the transformations.

NOTE: Although the event name is realtimesync, Oracle has disabled the realtime option for this event subscription. Hence synchronization with DQM staging tables happens in deferred mode only.


Here is the link to DQM presentation
Link for DQM and TCA presentation 

References:
http://oracle.anilpassi.com/dqm-tca.html

BI Publisher Bursting: Data Definition sample XML file

<?xml version="1.0"?>
<dataTemplate name="XXCOMPSOL" description="Concurrent Program Name" version="1.0" defaultPackage="XXCOMP_CUSTOMER_TOOL_PKG" >
    <!--
    Modification Log
    =================================================================================================================================================
    Version  Date          Modified By             Ticket    Comments
    =======  ============  ======================  =======   ========================================================================================
    1.0      24-DEC-2013   First Last Name             Initial Development
    =================================================================================================================================================
    -->
    <properties>
        <property name="xml_tag_case" value="upper"/>
        <property name="db_fetch_size"/>
        <property name="scalable_mode"/>
        <property name="include_rowsettag"/>
        <property name="debug_mode"/>
    </properties>
 
    <parameters>
        <parameter name="P_ORDER_HEADER_ID"       dataType="number"    />
        <parameter name="P_SEND_EMAIL"            dataType="character" />
        <parameter name="P_EMAIL_BCC_01"          dataType="character" />
        <parameter name="P_CTL_TBL_RETAIN_DAYS"   dataType="number"    />
    </parameters>
 
    <dataTrigger name="beforeReport" source="XXCOMP_CUSTOMER_TOOL_PKG.BEFORE_REPORT"/>
    <dataTrigger name="afterReport"  source="XXCOMP_CUSTOMER_TOOL_PKG.AFTER_REPORT"/>
 
    <dataQuery>
        <sqlStatement name="Q_MAIN">
            <![CDATA[ select *
           from XXCOMP_CUSTOMER_TOOL_STG
  where CREATED_BY_REQ_ID = :LP_REQUEST_ID
]]>
        </sqlStatement>
    </dataQuery>
 
    <dataStructure>
        <group name="Q_MAIN_HDR" source="Q_MAIN" groupFilter="XXCOMP_CUSTOMER_TOOL_PKG.FLAG_AS_NOTIFIED(:HEADER_ID)">
            <element name="HEADER_ID"          value="HEADER_ID"          />
            <element name="SHIP_TO_CONT_EMAIL" value="SHIP_TO_CONT_EMAIL" />
            <element name="EMAIL_TO"           value="EMAIL_TO"           />
            <element name="EMAIL_SUBJ"         value="EMAIL_SUBJ"         />
            <element name="EMAIL_BCC"          value="EMAIL_BCC"          />
            <element name="IMG_URL_PORT"       value="IMG_URL_PORT"       />
<element name="EMAIL_BODY_1"         value="EMAIL_BODY_1"        />
<element name="EMAIL_BODY_2"         value="EMAIL_BODY_2"        />
<element name="EMAIL_BODY_3"         value="EMAIL_BODY_3"        />
<element name="EMAIL_BODY_4"         value="EMAIL_BODY_4"        />
            <group name="Q_MAIN" source="Q_MAIN">
                <element name="SOLD_TO_CUST"          value="SOLD_TO_CUST"          />
                <element name="END_CUST"              value="END_CUST"              />
                <element name="CUST_PO_NUMBER"        value="CUST_PO_NUMBER"        />
                <element name="ORDER_NUMBER"          value="ORDER_NUMBER"          />
                <element name="ORDERED_DATE"          value="ORDERED_DATE"          />
                <element name="SEGMENT1"              value="SEGMENT1"              />
                <element name="DESCRIPTION"           value="DESCRIPTION"           />
                <element name="ORDERED_QUANTITY"      value="ORDERED_QUANTITY"      />
                <element name="SOLUTION_NAME"         value="SOLUTION_NAME"         />
<element name="SOLUTION_TYPE"         value="SOLUTION_TYPE"         />
                <element name="CONTRACT_DURATION"     value="CONTRACT_DURATION"     />
                <element name="CONTRACT_DURATION_WORDS"     value="CONTRACT_DURATION_WORDS"     />
                <element name="CONTRACT_DURATION_UOM" value="CONTRACT_DURATION_UOM" />
<element name="SERVICE_START_DATE" value="SERVICE_START_DATE" />
<element name="SERVICE_END_DATE" value="SERVICE_END_DATE" />
            </group>
        </group>
    </dataStructure>
 
</dataTemplate>

Sales Order Line Status Flow

OM = Order Management Sales Order Form

SE = Shipping Transactions or Shipping Execution Form

Entered [OM]: Order is saved but not booked

Booked [OM]: Order is Booked.

Awaiting Shipping [OM]: Order is booked but lines are not yet picked.

Navigating to Shipping Execution, the delivery line status flow is:Not Ready to Release [SE]: A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used.

Released to Warehouse [SE]: Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.
Ready to Release [SE]: Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.
Backordered [SE]: The status of Backorderd is assigned to a line under the following circumstances.● The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity.
● At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.
● The user manually Backorders the entire delivery.
Shipped [SE]: The delivery line is shipped confirmed.
Confirmed [SE]: The delivery line is shipped or backordered and the trip stops are open.
Picked [OM]: Pick release is complete, both allocations and pick confirm
Picked Partial [OM]: This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred
Interfaced [SE]: The delivery line is shipped and Inventory interface concurrent process is complete.
Awaiting Fulfillment [OM]: When fulfillment set is used, Not all shippable lines in a fulfillment set or a configuration are fulfilled
Fulfilled [OM]: All lines in a fulfillment set are fulfilled.
Interfaced to Receivables [OM]: The order is linked with Receivables and the invoice is created.
Partially Interfaced to Receivables [OM]: This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.
Closed [OM]: Closed indicates that the line is closed.

Canceled [OM]: Indicates that the line has been completely canceled. No further processing will occur for this line.

WSH_DELIVERY_DETAILS.Release_Status can have any of the below valid values

Execute the below query to see lookup values

SELECT lookup_type, lookup_code, meaning, description FROM fnd_lookup_values WHERE lookup_type = 'PICK_STATUS' AND LANGUAGE = 'US';

LOOKUP_TYPE	LOOKUP_CODE	MEANING	DESCRIPTION
PICK_STATUS	B	Backordered	Line failed to be allocated in Inventory
PICK_STATUS	C	Shipped	Line has been shipped
PICK_STATUS	D	Cancelled	Line is Cancelled
PICK_STATUS	E	Replenishment Requested	Line has been replenishment requested
PICK_STATUS	F	Replenishment Completed	Line has been replenishment completed
PICK_STATUS	I	Interfaced	Line has been shipped and interfaced to Order Management and Inventory
PICK_STATUS	K	Planned for Crossdocking	Line has been Planned for X-dock
PICK_STATUS	L	Closed	Line has been Received
PICK_STATUS	N	Not Ready to Release	Line is not ready to be released
PICK_STATUS	P	Purged	Line has been purged from source system
PICK_STATUS	R	Ready to Release	Line is ready to be released
PICK_STATUS	S	Released to Warehouse	Line has been released to Inventory for processing
PICK_STATUS	X	Not Applicable	Line is not applicable for Pick Release
PICK_STATUS	Y	Staged/Pick Confirmed	Line has been picked and staged by Inventory

WSH_NEW_DELIVERIES.status_code column values

SELECT lookup_type, lookup_code, meaning, description FROM fnd_lookup_values WHERE lookup_type = 'TRIP_STATUS' AND LANGUAGE = 'US';

LOOKUP_TYPE	LOOKUP_CODE	MEANING	DESCRIPTION
TRIP_STATUS	CL	Closed	Trip has completed
TRIP_STATUS	IT	In-Transit	Trip is in-transit and has begun
TRIP_STATUS	OP	Open	Trip is Open and has not begun

References:

http://oracleappssharing.blogspot.ca/2013/11/sales-order-line-status-flow.html