Useful pointers in OAF

Profile options in OAF:

• Personalize Self-Service Defn
           – Yes to allow ‘Personalize’ button to appear at the top of the page
• FND: Personalization Region Link Enabled
           – Yes to display all the regional links above each region
           – Minimal to display key regional links
• Disable Self-Service Personal
           – Yes will disable all personalizations at any level
• FND: Diagnostics
           – Turns on ‘About this Page’
• FND: Personalization Document Root Path (new in 11.5.10)
           – Required to migrate personalizations
           – Set this profile option to a tmp directory with open (777) permissions

********************************************************************************
JDR API


jdr_utils.listcustomizations();
jdr_utils.printdocument();
jdr_utils.deletedocument();


Command list  for deleting substitution-
SQL> exec jdr_utils.listcustomizations('/oracle/apps/pos/changeorder/server/PosRevisionHistoryVO');
/oracle/apps/pos/changeorder/server/customizations/site/0/PosRevisionHistoryVO
PL/SQL procedure successfully completed.

SQL> exec jdr_utils.deletedocument('/oracle/apps/pos/changeorder/server/customizations/site/0/PosRevisionHistoryVO');
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

SQL> exec jdr_utils.listcustomizations('/oracle/apps/pos/changeorder/server/PosRevisionHistoryVO');
PL/SQL procedure successfully completed.

Similarly you can also list all the customization done on a particular page or region or even VO using below commands:
SQL>exec jdr_utils.listcustomizations('/oracle/apps/asn/lead/webui/ASNLeadQryRN');
..............
..............

SQL>exec jdr_utils.listcustomizations('/oracle/apps/asn/dashboard/webui/DashboardPG ');
..............
..............
SQL>exec jdr_utils.listcustomizations('/oracle/apps/ar/hz/components/contact/server/HzPuiContRelTableVO');
...............
...............

JDR Utility Tables which store the personalization details:

JDR_ATTRIBUTES
JDR_ATTRIBUTES_TRANS
JDR_COMPONENTS
JDR_PATHS

********************************************************************************
XML Import and Export Commands for OAF pages:

1) Command to import the XML page into the database
adjava -mx128m -nojit oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/oracle/apps/xxvm/iExpensePolicyPage/webui/iExpensePolicyPG.xml -username apps -password appspwd -dbconnection "(description=(address=(protocol=tcp)(host=<host name>)(port=1521))(connect_data=(sid=<SID>)))" -rootdir $JAVA_TOP

OR

adjava \

oracle.jrad.tools.xml.importer.XMLImporter \

$HOME/CHR11/XXDOMI_POS_SUMMARY_VIEW/oracle/apps/pos/planning/webui/customizations/site/0/PosHorizSchdCVPG.xml \

-rootdir $HOME/CHR11/XXDOMI_POS_SUMMARY_VIEW \

-rootPackage / \

-username apps \

-password pwd \

-dbconnection "<Host Name>:<Port Number>:<SID>"

2) Command to Export the XML page from the database:
java oracle.jrad.tools.xml.exporter.XMLExporter oracle/apps/ap/oie/audit/webui/OIE_AUD_AUDIT_PAGE -rootdir /export/home/applvmd -username apps -password appspwd -dbconnection "(description=(address=(protocol=tcp)(host=<host name>)(port=1521))(connect_data=(sid=<SID>)))"

3) Command for VO substitution in an instance.

adjava \
oracle.jrad.tools.xml.importer.JPXImporter $HOME/CHR11/XXDOMI_POS_SUMMARY_VIEW/iSupplierPortal.jpx \

-username apps \

-password pwd \

-dbconnection "<Host Name>:<Port Number>:<SID>"

********************************************************************************

How to Find the Correct Version of JDeveloper to Use with E-Business Suite 11i or Release 12.x (Doc ID 416708.1)

1) First when you log in oracle applications, type in the below in the URL
http:///OA_HTML/OAInfo.jsp

2) Then you get the Oracle Apps version you working on like below

OA Framework Version Information
OA Framework Version 11.5.10.2CU.
MDS Version 9.0.5.4.81 (build 481)
UIX Version 2.2.18
BC4J Version 9.0.3.13.51

3) so my version of apps (OAF) is 11.5.10.2CU

4) go to METALINK and search for Doc ID: Note:416708.1

there you find the below in form of a TABLE.

https://metalink.oracle.com/metalink/plsql/f? p=130:14:7711961564797583862::::p14_database_id,p14_doc id,p14_show_header,p14_show_help,p14_black_frame,p14_fo nt:NOT,416708.1,1,1,1,helvetica

click the above link

there you can select the PATCH as per your version of OAF in u r Oracle Apps.

MINE is PATCH 4573517 for 11.5.10.2CU

Another alternative approach to find the OA Version

Identify the OA Framework version in your instance by activating diagnostics and click the "About This Page" from any OAF page. Click the "Technology Components" tab. The OA Framework version in the top row of the table can then be matched to the JDeveloper Patch.

Release 11i

OA Framework 5.10 patch	Oracle JDeveloper 9i Patch
ATG.PF.H (patch 3438354 or Oracle Applications 11.5.10)	Patch 4045639 9IJDEVELOPER WITH OA EXTENSION ARU FOR FWK.H
ATG PF CU1 (patch 4017300)	Patch 4141787 9IJDEVELOPER WITH OA EXTENSION ARU FOR CU1
ATG PF CU2 (patch 4125550)	Patch 4573517 Oracle9i JDeveloper with OA Extension for 11.5.10 CU2
11i.ATG_PF.H RUP3 (patch 4334965)	Patch 4725670 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP3
11i.ATG_PF.H RUP4 (patch 4676589)	Patch 5455514 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP4
11i.ATG_PF.H RUP5 (patch 5473858)	Patch 6012619 9IJDeveloper With OA Extension ARU FOR 11i10 RUP5
11i.ATG_PF.H.RUP6 (patch 5903765)	Patch 6739235 9IJDeveloper With OA Extension ARU FOR 11i10 RUP6
11i.ATG_PF.H.delta.7 (patch 6241631)	Patch 8751878 9I JDEVELOPER WITH OA EXTENSION ARU FOR 11I RUP7

Release 12.0

ATG Release 12 Version	Oracle JDeveloper 10g Patch
12.0.0	Patch 5856648 10g Jdev with OA Extension
12.0.1  (patch 5907545)	Patch 5856648 10g Jdev with OA Extension
12.0.2  (patch 5484000 or 5917344)	Patch 6491398 10g Jdev with OA Extension ARU for R12 RUP2 (replaces 6197418)
12.0.3  (patch 6141000 or 6077669)	Patch 6509325 10g Jdev with OA Extension ARU for R12 RUP3
12.0.4 (patch 6435000 or 6272680)	Patch 6908968 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12 RUP4
12.0.5 (No new ATG code released)	No new JDev patch required
12.0.6  (patch 6728000 or patch 7237006)	Patch 7523554 10G Jdeveloper With OA Extension ARU for R12 RUP6

Release 12.1

ATG Release 12.1 Version	Oracle JDeveloper 10g Patch
12.1 (Controlled Release - only included for completeness)	Patch 7315332 10G Jdev with OA Extension ARU for R12.1 (Controlled Release)
12.1.1 (rapidInstall or patch 7303030)	Patch 8431482 10G Jdeveloper with OA Extension ARU for R12.1.1
12.1.2 (patch 7303033 or patch 7651091)	Patch 9172975 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12.1.2
12.1.3 (patch 9239090 or patch 8919491)	Patch 9879989 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12.1.3
12.1.3.1 (patch 11894708)	Patch 9879989 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12.1.3
12.1.3.2 (patch 15880118)	Patch 9879989 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12.1.3

Release 12.2

ATG Release 12.2 Version	Oracle JDeveloper 10g Patch
12.2	Patch 17513160 10G JDeveloper with OA Extension ARU for R12.2, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 6.0 or above.
12.2.3	Patch 17888411 10G JDeveloper with OA Extension ARU for R12.2.3, certfied on Windows 7, Windows XP-SP2, and Linux. Preferred web browser is Microsoft Internet Explorer 8.0 or above for the Skyros Look-and-Feel and Internet Explorer 6.0 or above for the Swan Look-and-Feel.

Sales Order Flow Statuses

OE_ORDER_LINES_ALL.flow_status_code column values

execute the below query to see the values.
SELECT *
FROM fnd_lookup_values
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND language = 'US'


WSH_DELIVERY_DETAILS.Release_Status can have any of the below valide values

Pick Status   Meaning                Description
-----------      --------                   -----------------
  B        Backordered                 Line failed to be allocated in Inventory
  D        Cancelled                     Line is Cancelled
  L        Closed                          Line has been Received
  I         Interfaced                     Line has been shipped and interfaced to Order Management and Inventory
  X        Not Applicable              Line is not applicable for Pick Release
  N        Not Ready to Release     Line is not ready to be released
  P        Purged                          Line has been purged from source system
  R        Ready to Release           Line is ready to be released
  S        Released to Warehouse   Line has been released to Inventory for processing
  C        Shipped                         Line has been shipped
  Y        Staged/Pick Confirmed   Line has been picked and staged by Inventory


WSH_NEW_DELIVERIES.status_code column values

SELECT *
FROM fnd_lookup_values
WHERE lookup_type = 'TRIP_STATUS'
AND language = 'US'

Trip Status   Meaning      Description
-----------      --------         -----------------
  OP          Open         Trip is Open and has not begun
  IT           In-Transit   Trip is in-transit and has begun
  CL          Closed       Trip has completed   

Value Set types in Oracle Applications

Value Sets

Validation Types in Value Set:

1. Dependent (In order to create a dependent values set you need to create one Independent value set first)
2. Independent
3. None
4. Pair
5. Special
6. Table
7. Translatable Independent
8. Translatable Dependent

• None: This type allows users to enter any value as long as the value meets value set formatting rules. As this value set is not validated, a segment using this value set does not provide list of values. Ex: If the value set does not allow alphabetic characters, we cannot enter “ABC” but can enter “123”.
• Independent: This type provides a predefined list of values associated with descriptions. Ex: The meaning of value in this value set does not depend on any other value of any other segment.
• Table: This type provides a predefined list of values like an independent set but its values are stored in an application table. We define which table to use along with a “where” clause to limit the values to be used.
• Dependent: This type is similar to independent value set except that the available values and their meanings depend on the independent value that was selected in the prior segment of the flex field. Ex: The Item type depends on what type is chosen in the Item Code field.
• Special and Pair Value sets: These provide a mechanism to allow “Flex field within a Flex Field” structure. These value sets are primarily used for Standard Request Submission (SRS) parameters, and are not used for normal flex fields. For a Pair value set, two flex fields provide a range of valid values.
• Translatable Dependent and Translatable Independent: These value types are similar to Dependent and Independent value sets except that they provide a predefined list of values for the segment, also these values can be translated into different languages

Questions on Oracle Value sets

1. Use of Special type of value set.

>> Special type is used for flexifields. Below are some examples for reference:

NOTE: Validate type of Pair will also have similar structure.

Value Set Name: AR_ARZACS_VAL_GLDATE_STANDARD
Validation Type: Special
Click on Edit Information button.
Event = Validate
Funtion =
AR VAL_GLDATE
GL_DATE=:!VALUE
SET_OF_BOOKS_ID=:$PROFILES$.GL_SET_OF_BKS_ID
VGL_DATE_FORMAT="DD-MON-YYYY"

Value Set Name: AP_GL_SRS_FLEXFIELD
Validation Type: Special
Click on Edit Information button.
Event = Edit
Funtion =
FND POPID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=:$FLEX$.GL_SRS_CHART_OF_ACCOUNTS_ID
REQUIRED=Y
VALIDATE=FULL
ID=:!ID
SEG=:!VALUE
DESC=:!MEANING
NAVIGATE=!DIR
DINSERT=N

Event = Load
Funtion =
FND LOADID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=:$FLEX$.GL_SRS_CHART_OF_ACCOUNTS_ID
REQUIRED=Y
VALIDATE=FULL
ID=:!ID
SEG=:!VALUE
DESC=:!MEANING
NAVIGATE=!DIR
DINSERT=N


Value Set Name: AP_SRS_APXINREV_COMPANY_SEGMENT
Validation Type: Special
Click on Edit Information button.
Event = Edit
Funtion =
FND POPID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=:$FLEX$.AP_APXINREV_CHART_OF_ACCOUNTS_NO_VALIDATION
REQUIRED=Y
VALIDATE=NONE
ID=:!VALUE
SEG=:!VALUE
DESC=:!MEANING
DINSERT=N
DISPLAY=GL_BALANCING
ALLOWNULLS="Y"
BOX=Y

Event = Validate
Funtion =
FND VALID
APPL_SHORT_NAME=SQLGL
CODE="GL#"
NUM=:$FLEX$.AP_APXINREV_CHART_OF_ACCOUNTS_NO_VALIDATION
REQUIRED=N
VALIDATE=NONE
ID=:!VALUE
SEG=:!VALUE
DESC=:!MEANING
DINSERT=N
BOX=Y
DISPLAY=GL_BALANCING


2. Use of $FLEX$ in value set

>> :$FLEX$ is used for dependent condition of the parent value set which is of type table.

This example is from flexfield user guide.

Example of $FLEX$ Syntax
Here is an example of using :$FLEX$.Value_Set_Name to set up value sets where one segment depends on a prior segment that itself depends on a prior segment ("Cascading Dependencies")

Assume you have a three-segment flexfield where the first segment is car manufacturer, the second segment is car model, and the third segment is car color. You could limit your third segment’s values to only include car colors that are available for the car specified in the first two segments. Your three value sets might be defined as follows:

Segment Name Manufacturer
Value Set Name Car_Maker_Name_Value_Set
Validation Table CAR_MAKERS
Value Column MANUFACTURER_NAME
Description Column MANUFACTURER_DESCRIPTION
Hidden ID Column MANUFACTURER_ID
SQL Where Clause (none)

Segment Name Model
Value Set Name Car_Model_Name_Value_Set
Validation Table CAR_MODELS
Value Column MODEL_NAME
Description Column MODEL_DESCRIPTION
Hidden ID Column MODEL_ID
SQL Where Clause WHERE MANUFACTURER_ID =
:$FLEX$.Car_Maker_Name_Value_Set

Segment Name Color
Value Set Name Car_Color_Name_Value_Set
Validation Table CAR_COLORS
Value Column COLOR_NAME
Description Column COLOR_DESCRIPTION
Hidden ID Column COLOR_ID
SQL Where Clause WHERE MANUFACTURER_ID =
:$FLEX$.Car_Maker_Name_Value_Set AND
MODEL_ID = :$FLEX$.Car_Model_Name_Value_Set

In this example, MANUFACTURER_ID is the hidden ID column and MANUFACTURER_NAME is the value column of the car_Maker_Name_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Car_Maker_Name_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example.

Another example found on the internet, which talks about using PL/SQL code or calling database functions:

Oracle Special Value Set

Special value set configuration and uses.

I have observed that many times we need to restrict users to a limited, conditional value entries either in DFF or when submitting concurrent request. Normally we can use dependent value set, but when dynamic or some specific check is required, then special value set is better and only choice. Below I have described all the steps for configuring value set. Assign this value set to DFF or concurrent req. parameter as required.

1. Go to Application Developer --> Application --> Validation --> Set.
2. Create a new value set - Enter value set name, description, List type = List of values.
3. Select Validation Type = Special from left bottom of the screen.
4. Click on Edit information.
5. Select Event = Validate.
6. Now in function, you can write pl/sql code or call any function from database.
7. The logic of validation will be as per requirement.
8. The code syntax will be FND PLSQL " entire function "
9. To read the value which user has entered in DFF or as conc. request parameter, use lc_in := :!value;
10. To raise error if the enter value is not correct, use fnd_message.raise_error;
11. Use Application message. Display appropriate message.
12. Raising error makes sure that user cannot continue with the invalid value and he will be forced to correct entry.
13. The character size is limited, but by calling database function, one can put complex validation.

-- Below is the code to validate that user can select a date which is 3 months before sysdate. This is a parementer in report in which user should be allowed to see data for 3 months or before, but he cannot see recent quater data.

FND PLSQL "declare
  l_value varchar2( 20 ) := :!value ;
  l_valid NUMBER := 0 ;
BEGIN
  SELECT (sysdate - to_date(l_value,'dd-mon-yyyy'))
  INTO l_valid
  FROM dual;
  IF (l_valid <= 91) THEN
      fnd_message.set_name( 'FND', 'FND_GENERIC_MESSAGE' ) ;
      fnd_message.set_token( 'MESSAGE', 'Date must be atleast 3 calendar months prior to current date' );
      fnd_message.raise_error ;
  END IF ;
END;
"


3. How to create a value set which is dependent of another value set. (let us consider a value set which displays me the country name, state, in this the country value set is the parent and state value set is child . pls do explain me how to create these two value sets.)

>> You can create dependent value sets in 2 ways.
1) Create one independent then create one dependent and mention the independent one.
2) Create on table type, create another table type and specify the where clause as mentioned above.


4. How to create a value set using 3 tables?

>> You can create value set using multiple table. Give the table names seperated by , in the table name field.
Go through any seeded value set which is based on multiple tables.
Example:

Data flow of Standard Sales Order in OM

1. Order Entry

This is the first stage when Order in entered in the system. When order is entered it basically create a record in order headers and Order Lines tables.

•OE_ORDER_HEADERS_ALL (Here the flow_status_code = Entered)
•OE_ORDER_LINES_ALL (flow_status_code = Entered) ( order number is generated)
•OE_ORDER_HOLDS_ALL - If any hold is applied for order like credit check etc

2.Order Booking

This is next stage , when Order is booked, the Flow status changes from Entered to Booked. At this stage , these table get affected.

•OE_ORDER_HEADERS_ALL (flow_status_code = Booked, booked_flag updated)
•OE_ORDER_LINES_ALL (flow_status_code = Awaiting Shipping, booked_flag updated)
•WSH_NEW_DELIVERIES (status_code OP open)
•WSH_DELIVERY_DETAILS (released_status = 'R' - Ready to Release)
Same time, Demand interface program runs in background and inserts into inventory tables MTL_DEMAND

3. Reservation

This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once this program get successfully completed, the MTL_RESERVATIONS table get updated.

4. Pick Release

Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory into the staging area.

Normally pick release SRS program runs in background. Once the program get completed these are the table get affected:

•OE_ORDER_LINES_ALL (flow_status_code = PICKED)
•WSH_DELIVERY_DETAILS (released_status = 'S' - Released to Warehouse means "Line has been released to Inventory for processing")
•MTL_TXN_REQUEST_HEADERS
•MTL_TXN_REQUEST_LINES
(move order tables. Here request is generated to move item from saleble to staging sub inventory)
•MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through move_order_header_id/line_id

5.Pick Confirm

Items are transferred from saleble to staging Subinventory.

•MTL_MATERIAL_TRANSACTIONS
•MTL_TRANSACTION_ACCOUNTS
•WSH_DELIVERY_DETAILS (released_status = 'Y' - Staged/Pick Confirmed means "Line has been picked and staged by Inventory")
•WSH_DELIVERY_ASSIGNMENTS

6.Ship Confirm

Here ship confirm interface program runs in background. Data removed from WSH_NEW_DELIVERIES

•OE_ORDER_LINES_ALL (flow_status_code "SHIPPED")
•WSH_DELIVERY_DETAILS (released_status 'C' - Shipped means "Line has been shipped")
•MTL_TRANSACTION_INTERFACE
•MTL_MATERIAL_TRANSACTIONS(linked through Transaction source header id)
•MTL_TRANSACTION_ACCOUNTS
•Data deleted from MTL_DEMAND,MTL_RESERVATIONS
•Item deducted from MTL_ONHAND_QUANTITIES

7.Enter Invoice

This is also called Receivables interface, that mean information moved to accounting area for invoicing details.

•Invoicing workflow activity transfers shipped item information to Oracle Receivables.
•RA_INTERFACE_LINES_ALL (interface table into which the data is transferred from order management)T
•Then AutoInvoice Master Program imports data from this
•Table which get affected into this stage are recievables base table.
◦RA_CUSTOMER_TRX_ALL (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)
◦RA_CUSTOMER_TRX_LINES_ALL (line_attribute_1 and line_attribute_6 are linked to header_id (or order number) and line_id of the orders)

8.Complete Line

In this stage order line leval table get updated with Flow status and open flag.

•OE_ORDER_LINES_ALL (flow_status_code = 'CLOSED', open_flag 'N')

9.Close Order

This is last step of Order Processing . In this stage only OE_ORDER_HEADERS_ALL table get updated.

These are the table get affected in this step.

•OE_ORDER_HEADRES_ALL (flow_status_code = CLOSED, open_flag 'N')
These are the typically data flow of a order to cash model for a standard order.

Drop Shipment Order Flow

More details can be found in the below website:
Order Management Functional



1.Order Entry

Here the activity is entering process where oe_order_headers_all (flow_status_code as entered) oe_order_lines_all . The order is booked as DROP SHIP

2. Order Booking

3. The Purchase Release program passes information about eligible drop-ship order lines to Oracle Purchasing.The interface table which gets populated is
po_requisitions_interface_all & po_req_dist_interface_all

4. After Purchase Release has completed successfully, run Requisition Import in Oracle Purchasing to generate purchase requisitions for the processed order lines. The Requisition Import program reads the above interface tables, validates your data, derives or defaults additional information and writes an error message for every validation that fails into the po_interface_errors table.The validated data is then inserted into the requisition base tables
po_requisition_headers_all,
po_requisition_lines_all,
po_req_distributions_all.
Then use autocreate PO fuctionality to create purchase orders and then perform receipts against these purchase orders

7. After the goods are successfully received invoices for vendors are created in accounts payables as in normal purchase orders.

8. Invoices are generated for customers In account receivables.

9. oe_order_lines_all (flow_status_code 'shipped', open_flag "N")

10. oe_order_lines_all (flow_status_code 'closed', open_flag "N")

More details can be found at http://www.oracleug.com/user-guide/order-management/back-back-orders

Oracle Workflow Table details with relationship diagram

A) Some comments on the graphical representation:

The columns belonging to the primary key are indicated in red.

Only the columns that either belong to a primary key or a foreign key are shown on this diagram.
The columns belonging to a foreign key may also belong to a primary key.

This model is split into 2 diagrams. Due to this, sometimes the start or the end point of a foreign key arrow is not on the current diagram.

Due to the graphical representation of foreign key structure, a column name may appear more than once inside a table. Of course this does not mean the column is duplicated within the table.

For a complete description of each of the tables, please refer to:

Oracle eTRM Technical Reference web site: http://etrm.oracle.com or note 150230.1 (ETRM DIRECT ACCESS DOCUMENT).


B) Some comments on workflow tables:

1) Difference between WF_ACTIVITIES and WF_PROCESS_ACTIVITIES

WF_ACTIVITIES contains only the activities that belong to the item type
Does NOT contain activities that belong to other item types (ie pre-defined activities).

WF_PROCESS_ACTIVITIES contains the activities of the item type AND the pre-defined activities that are referenced in the item type.

WF_PROCESS_ACTIVITIES, in addition to its primary key instance_id, has a unique key defined on the following columns:

1.Instance_label
2.Process_item_type
3.Process_name
4.Process_version


2) The following tables have a corresponding translation ( _TL) table:


- WF_ITEM_TYPES
- WF_ITEM_ATTRIBUTES
- WF_ACTIVITIES
- WF_ACTIVITY_ATTRIBUTES
- WF_MESSAGES
- WF_MESSAGE_ATTRIBUTES

The workflow lookup tables only exist as translation (_TL) tables:

WF_LOOKUP_TYPES_TL
WF_LOOKUPS_TL


(Translation tables are not shown in the above diagrams)



3) Difference between WF_ITEM_ACTIVITY_STATUSES and WF_ITEM_ACTIVITY_STATUSES_H

WF_ITEM_ACTIVITY_STATUSES and WF_ITEM_ACTIVITY_STATUSES_H have the same structure.

When the same activity is executed multiple times within a workflow (ie looping activity),
WF_ITEM_ACTIVITY_STATUSES will only contain information on the last execution of the activity, whereas the information on all the previous executions will be stored in WF_ITEM_ACTIVITY_STATUSES_H.

When an activity is executed only once within a workflow, only WF_ITEM_ACTIVITY_STATUSES will be populated.

Oracle Applications : Descriptive Flexfields (DFF) Overview and the SQL to find the Columns & Details.

Descriptive Flexfields

Descriptive flexfields let you satisfy different groups of users without having to reprogram your application, by letting you provide customizable “expansion space”? on your forms.

For example, suppose you have a retail application that keeps track of customers. Your Customers form would normally include fields such as Name, Address, State, Customer Number, and so on. However, your form might not include extra fields to keep track of customer clothing size and color preferences, or regular salesperson, since these are attributes of the customer entity that depend on how your users use your application.

For example, if your retail application is used for a tool company, a field for clothing size would be undesirable. Even if you initially provide all the fields your users need, your users might later identify even more customer attributes that they want to keep track of. You add a descriptive flexfield to your form so that your users have the desired expansion space. Your users can also take advantage of the fact that descriptive flexfields can be context sensitive, where the information your application stores depends on other values your users enter in other parts of the form.

A descriptive flexfield describes an application entity, providing form and database expansion space that you can customize. Each descriptive segment has a name you assign. You can specify valid segment values or set up criteria to validate the entry of any value.

Oracle General Ledger includes a descriptive flexfield in its journal entry form to allow end users to add information of their own choosing. For example, end users might want to capture additional information about each journal entry, such as source document number or the name of the person who prepared the entry. You could use a descriptive flexfield in a fixed assets application you build to allow further description of a fixed asset. You could let the structure of your assets flexfield depend on the value of an asset type field. For example, if asset type were “desk”, your descriptive flexfield could prompt for style, size and wood type. If asset type were “computer”, your descriptive flexfield could prompt for CPU chip and memory size.

SELECT fdfv.title
,fdfv.application_table_name
,fdfv.context_column_name
,fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num
,fdfcu.application_column_name
,fdfcu.end_user_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu
fnd_descriptive_flexs_vl fdfv
WHERE fdfv.title = 'Line Transaction Flexfield'
AND fdfcu.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
AND fdfcu.application_id = fdfv.application_id
ORDER BY fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num ;

SELECT fdfv.title,
,fdfv.application_table_name
,fdfv.context_column_name
,fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num
,fdfcu.application_column_name
,fdfcu.end_user_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu
,fnd_descriptive_flexs_vl fdfv
WHERE fdfv.title = 'Invoice Transaction Flexfield'
AND fdfcu.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
AND fdfcu.application_id = fdfv.application_id
ORDER BY fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num ;


SELECT fdfv.title,
,fdfv.application_table_name
,fdfv.context_column_name
,fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num
,fdfcu.application_column_name
,fdfcu.end_user_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu
,fnd_descriptive_flexs_vl fdfv
WHERE fdfv.title = 'Reference Transaction Flexfield'
AND fdfcu.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
AND fdfcu.application_id = fdfv.application_id
ORDER BY fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num ;


SELECT fdfv.title,
,fdfv.application_table_name
,fdfv.context_column_name
,fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num
,fdfcu.application_column_name
,fdfcu.end_user_column_name
FROM fnd_descr_flex_col_usage_vl fdfcu
,fnd_descriptive_flexs_vl fdfv
WHERE fdfv.title = 'Link-to Transaction Flexfield'
AND fdfcu.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
AND fdfcu.application_id = fdfv.application_id
ORDER BY fdfcu.descriptive_flexfield_name
,fdfcu.descriptive_flex_context_code
,fdfcu.column_seq_num ;