How to update Workflow Administrator Role in Oracle Applications

If you want to see workflow details (owned by other users) or status diagram in Oracle Applications 11i/R12 then you should belong to one of responsibilities/user listed under WF_ADMIN_ROLE.

By default (in 11i & R12) this role is set to user sysadmin (In old versions 11.5.8 or prior, it used to set to *)

If you wish to change WF_ADMIN_ROLE, as per most of meta link notes either

1. Change it via Workflow Administrator Web Applicationsresponsibility (Login as sysadmin >> Workflow Administrator Web Applications >> Administration)

or update table
2. SQL> update wf_resources set text=’&Enter_Admin_Name’ where name=’WF_ADMIN_ROLE’;

to set it to everyone, use
SQL> update wf_resources set text=’*’ where name=’WF_ADMIN_ROLE’;

.

Problem with above solution
– Execution of Auto config will override above settings. Auto config will pick value against parameter s_wf_admin_role(default value SYSADMIN) from context file $CONTEXT_FILE.

Correct way to set Workflow Administrator Role

1. First identify Workflow Role Name associated with User or Responsibility.

A. For setting Admin Role to specific userSQL> select name from apps.wf_roles where DISPLAY_NAME like ‘&USER_NAME’  and ORIG_SYSTEM=’FND_USR’;

B. For setting it to a responsibility (so that all users with that responsibility can view other user’s Workflow)
SQL> select name from apps.wf_roles where DISPLAY_NAME like ‘&Responsibility_Name’ and ORIG_SYSTEM=’FND_RESP’;

SQL> select name from apps.wf_roles where DISPLAY_NAME like ‘System Administrator‘;  (output for System Administrator responsibility should look like)FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD

SQL> select name from apps.wf_roles where DISPLAY_NAME like ‘Application Developer‘; (output for Application Developer responsibility should look like)
FND_RESP|FND|APPLICATION_DEVELOPER|STANDARD

2. Update context file ($CONTEXT_FILE) variable s_wf_admin_role (If you don’t see this parameter in context file then apply latest Autoconfig Patch) to value from above query

For Sysadmin User – Set it to SYSADMIN

For System Administrator Responsibility – Set it to
FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD

For Application Developer Responsibility – Set it to FND_RESP|FND|APPLICATION_DEVELOPER|STANDARD

3. Run Autoconfig –
$OAD_TOP/admin/scripts/$CONTEXT_NAME/adautocfg.sh(11i)
$ADMIN_SCRIPTS_HOME/adautocfg.sh (R12)

How to check Open Workflow Notification from backend and how to Approve/Reject.

select  wn.notification_id nid,
wn.context,
wn.group_id,
wn.status,
wn.mail_status,
wn.message_type,
wn.message_name,
wn.access_key,
wn.priority,
wn.begin_date,
wn.end_date,
wn.due_date,
wn.callback,
wn.recipient_role,
wn.responder,
wn.original_recipient,
wn.from_user,
wn.to_user,
wn.subject
from    wf_notifications wn, wf_item_activity_statuses wias
where  wn.group_id = wias.notification_id
and  wias.item_type = ‘XXRECNQM’
and  wias.item_key like ‘XXRECNQM%’;

BEGIN
wf_notification.setattrtext(nid => ‘NOTIFICATION_ID’,aname => ‘RESULT’,avalue =>  ‘REJECTED/APPROVED’ );
wf_notification.respond(nid => ‘NOTIFICATION_ID’,respond_comment => ‘Reject from Backend’,responder =>  ‘USER_NAME/RESPONDER_NAME’ );
Commit;
END;

——————————————————-

Close FYI Notification

BEGIN
wf_notification.close(NOTIFICATION_ID,’SYSADMIN’);

END;

Oracle Workflow Notification Query

Oracle Workflow Notification Query

Firstly, retrieve all the messages from the system which have a message attribute which is does not have a type of RESPOND, but which also do not have any RESPOND attributes.

SELECT *
FROM   ( SELECT DISTINCT ‘FYI’          action
,      wfi.display_name        item_type
,      wfma.message_type       item_type_internal
,      wfmt.display_name       message_name
,      wfma.message_name       message_internal
FROM   wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfma.SUBTYPE     != ‘RESPOND’
AND    wfma.message_type = wfi.name
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
AND    (wfma.message_type, wfma.message_name) NOT IN ( SELECT DISTINCT wfma2.message_type
,      wfma2.message_name
FROM   wf_message_attributes   wfma2
WHERE  wfma2.SUBTYPE      = ‘RESPOND’
)
UNION
SELECT DISTINCT ‘Response Required’
,      wfi.display_name        item_type
,      wfma.message_type       item_type_internal
,      wfmt.display_name       message_name
,      wfma.message_name       message_internal
FROM   wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfma.SUBTYPE     = ‘RESPOND’
AND    wfma.message_type = wfi.name
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
) messages
ORDER BY item_type_internal, message_internal

We now need to include the notifications that are open in the system, so include WF_NOTIFICATIONS in the query as well

SELECT *
FROM   ( SELECT DISTINCT ‘FYI’           action
,      wfi.display_name         item_type
,      wfn.message_type         item_type_internal
,      wfmt.display_name        message_name
,      wfn.message_name         message_internal
,      wfn.notification_id
FROM   wf_notifications        wfn
,      wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfn.message_type  = wfma.message_type
AND    wfn.message_name  = wfma.message_name
AND    wfma.message_type = wfi.name
AND    wfma.SUBTYPE     != ‘RESPOND’
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
AND    (wfn.message_type, wfn.message_name) NOT IN ( SELECT DISTINCT wfn2.message_type
,      wfn2.message_name
FROM   wf_notifications        wfn2
,      wf_message_attributes   wfma2
WHERE  wfn2.message_type  = wfma2.message_type
AND    wfn2.message_name  = wfma2.message_name
AND    wfma2.SUBTYPE      = ‘RESPOND’ )
UNION
SELECT DISTINCT ‘Response Required’
,      wfi.display_name         item_type
,      wfn.message_type         item_type_internal
,      wfmt.display_name        message_name
,      wfn.message_name         message_internal
,      wfn.notification_id
FROM   wf_notifications        wfn
,      wf_message_attributes   wfma
,      wf_item_types_tl        wfi
,      wf_messages_tl          wfmt
WHERE  wfn.message_type  = wfma.message_type
AND    wfn.message_name  = wfma.message_name
AND    wfma.message_type = wfi.name
AND    wfma.SUBTYPE      = ‘RESPOND’
AND    wfma.message_type = wfmt.type
AND    wfma.message_name = wfmt.name
) notifications
ORDER BY item_type_internal

Oracle R12 Organization Structure

Oracle R12 Organization Structure

Business Group:

A business group is the highest level classification in the organization hierarchy of the Oracle E-Business Suite.  It may correspond to an entire enterprise or to a major grouping such as a subsidiary or operating division.

For a fresh installation, Oracle Applications provides a predefined business group, Setup Business Group. We can setup additional Business groups as required for the defined Organization Structure.

By Using a Single responsibility security model, more than one business group can be accessed from single responsibility.

Profile option HR User type should be set against each responsibility which will use HRMS Tables and get access to define organization Window.

Profile Options HR Security Profile should be set before creating any Organizations (Other than Business Group) for all responsibilities.

Ledger and Legal Entity:

Legal entities are formally the entities that actually enter into transactions. Individual legal entities own the assets of the enterprise, record sales and pay taxes on those sales, make purchases and incur expenses, and make other transactions.

The Ledger represents an accounting representation for an organization that is accountable in a self-contained way. A ledger provides balanced ledger accounting for the accounting entity and serves as the repository of financial information

Detailed transaction information is captured in the sub ledgers and periodically posted (in summary or detail form) to the ledger.

Accounting Setup Manager is Used for Creating the below mentioned setups.

1. Primary Ledger
2. Legal Entity
3. Reporting Currency
4. Balancing Segment Value
5. Sub ledger Accounting Options
6. Secondary Ledger

Operating Units:

An operating unit organization defines the Purchasing, Order Entry, Accounts Payable and Accounts Receivable level of operation. An operating unit may span multiple manufacturing facilities, distribution points and sales offices, or it may be limited to a single site.

Inventory Organizations:

The Inventory Organization represents an organization for which you track inventory transactions and balances. These organizations might be manufacturing or distribution centers. Several modules and functions in the Oracle Manufacturing and Supply Chain Management suite secure information by Inventory Organization

Locations:

A location code is an address. Each organization must be assigned at least one location code.

Sub Inventories:

A sub inventory is used as a holding point for on-hand inventory. This is a Logical partition of an Inventory .Sub inventories are defined within inventory organizations.

Stock Locators:

Stock locators represents physical locations within a sub inventory.  if locators are defined to represent a shelf within a stockroom, on-hand balances on the system would show the item and quantity down to the physical location within the facility.

Limitations with Stock Locators:

Only one locator flex field definition is allowed per install.

1.If a sub inventory wants to track material by row, bin and shelf, it will likely define a three-segment flex field with segments for row, bin, and shelf. If locators are desired for another sub inventory, even in another organization, the structure will again be 3 segments for row, bin and shelf.

2.Locators must be unique within an organization.

Oracle Apps R12 Sub ledger Accounting Tables and joins

Oracle Apps R12 Sub ledger Accounting Tables and joins

XLA Table joins
GL_JE_BATCHES (je_batch_id)                        => GL_JE_HEADERS (je_batch_id)
GL_JE_HEADERS (je_header_id)                      => GL_JE_LINES (je_header_id)
GL_JE_LINES (je_header_id,  je_line_num)        => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id)        => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id)  => XLA_AE_HEADERS (application_id, ae_header_id)
XLA_AE_HEADERS (application_id, event_id)   => XLA_EVENTS (application_id, event_id)
XLA_EVENTS (application_id, entity_id)            => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)

Transaction Entity Codes and ids
xte.entity_code =
‘TRANSACTIONS’ or
‘RECEIPTS’ or
‘ADJUSTMENTS’ or
‘PURCHASE_ORDER’ or
‘AP_INVOICES’ or
‘AP_PAYMENTS’ or
‘MTL_ACCOUNTING_EVENTS’ or
‘WIP_ACCOUNTING_EVENTS’

xte.source_id_int_1 =
‘INVOICE_ID’ or
‘CHECK_ID’ or
‘CUSTOMER_TRX_ID’ or
‘TRANSACTION_ID’

XLA_EVENTS:
SELECT *  FROM xla_events xe
WHERE xe.application_id = 222
AND xe.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = ‘TRANSACTIONS’
AND xte.source_id_int_1 = 10066)

XLA_AE_HEADERS:
SELECT *  FROM xla_ae_headers xah  WHERE xah.application_id = 222
AND xah.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = ‘TRANSACTIONS’
AND xte.source_id_int_1 = 10066)

XLA_AE_LINES:
SELECT xal.*  FROM xla_ae_lines xal, xla_ae_headers xah  WHERE xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.application_id = 222
AND xah.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = ‘TRANSACTIONS’
AND xte.source_id_int_1 = 10066)

XLA_DISTRIBUTION_LINES:
SELECT xdl.*  FROM xla_distribution_links xdl, xla_ae_headers xah  WHERE xdl.application_id = xah.application_id
AND xdl.ae_header_id = xah.ae_header_id
AND xah.application_id = 222
AND xah.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = ‘TRANSACTIONS’
AND xte.source_id_int_1 = 10066)

XLA_AE_HEADER status Columns
Table stores important status Information
GL Transfer Status Code (GL_TRANSFER_STATUS_CODE)
The GL transfer status code of the sub-ledger journal entry has one of the following values:
Not transferred
Selected to transfer
Transferred
When a sub-ledger journal entry is created and completed, the value of this column is Not transferred. The Transfer to GL process updates this column when the subledger journal entry is transferred to General Ledger.

Subledger Journal Entry Status Code (ACCOUNTING_ENTRY_STATUS_CODE)
A subledger journal entry can have a status of Draft, Final, Incomplete, Invalid or Invalid Related Entry.
Draft: The entry status is set to Draft when the following conditions are met:
Final: The entry status is set to Final when the following conditions are met:
Incomplete: The entry status is set to Incomplete when the following condition is met:
Invalid Related Entry: The entry status is set to Invalid Related Entry when the following condition is met:

The subledger journal entry is valid, but one or more of the other subledger journal entries associated with the same accounting event are invalid.

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types
xdl.source_distribution_type = ‘AP_PMT_DIST ‘
and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id
—————
xdl.source_distribution_type = ‘AP_INV_DIST’
and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id
—————
xdl.source_distribution_type = ‘AR_DISTRIBUTIONS_ALL’
and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id
and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id
—————
xdl.source_distribution_type = ‘RA_CUST_TRX_LINE_GL_DIST_ALL’
and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id
—————
xdl.source_distribution_type = ‘MTL_TRANSACTION_ACCOUNTS’
and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id
—————
xdl.source_distribution_type = ‘WIP_TRANSACTION_ACCOUNTS’
and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id
—————
xdl.source_distribution_type = ‘RCV_RECEIVING_SUB_LEDGER’
and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id

Oracle Apps Inventory Tables

Following are important tables in Oracle Apps Inventory

MTL_SYSTEM_ITEMS_B
This table holds the definitions for inventory items, engineering items, and purchasing items. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID.

MTL_ITEM_STATUS
This is the definition table for material status codes. Status code is a required item attribute. It indicates the status of an item, i.e., Active, Pending, Obsolete.

MTL_UNITS_OF_MEASURE_TL
This is the definition table for both the 25-character and the 3-character units of measure. The base_uom_flag indicates if the unit of measure is the primary unit of measure for the uom_class. Oracle Inventory uses this table to keep track of the units of measure used to transact an item.

MTL_ITEM_LOCATIONS
This is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.

MTL_ITEM_CATEGORIES
This table stores inventory item assignments to categories within a category set.

MTL_CATEGORIES_B 
This is the code combinations table for item categories.

MTL_CATEGORIES_B and MTL_CATEGORIES_TL. MTL_CATEGORIES_TL table holds translated Description for Categories.

MTL_CATEGORY_SETS_B 
It contains the entity definition for category sets.

MTL_DEMAND
This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows,Open Demand Rows, and Reservation Rows.

MTL_SECONDARY_INVENTORIES 
This is the definition table for the subinventory.

MTL_ONHAND_QUANTITIES
It stores quantity on hand information by control level and location.

MTL_TRANSACTION_TYPES
It contains seeded transaction types and the user defined ones.

MTL_MATERIAL_TRANSACTIONS 
This table stores a record of every material transaction or cost update performed in Inventory.

MTL_ITEM_ATTRIBUTES
This table stores information on item attributes.

MTL_ITEM_CATALOG_GROUPS_B 
This is the code combinations table for item catalog groups.

MTL_ITEM_REVISIONS_B 
It stores revision levels for an inventory item.

MTL_CUSTOMER_ITEMS 
It stores customer item information for a specific customer. Each record can be defined at one of the following levels: Customer, Address Category, and Address. The customer item definition is organization independent.

MTL_SYSTEM_ITEMS_INTERFACE 
It temporarily stores the definitions for inventory items, engineering items and purchasing items before loading this information into Oracle Inventory.

MTL_TRANSACTIONS_INTERFACE 
It allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory  transaction module.

MTL_ITEM_REVISIONS_INTERFACE
It temporarily stores revision levels for an inventory item before loading this information into Oracle Inventory.

MTL_ITEM_CATEGORIES_INTERFACE
This table temporarily stores data about inventory item assignments to category sets and categories before loading this information into Oracle Inventory.

MTL_DEMAND_INTERFACE 
It is the interface point between non-Inventory applications and the Inventory demand module. Records inserted into this table are processed by the Demand Manager concurrent program.

MTL_INTERFACE_ERRORS
It stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

MTL_PARAMETERS
It maintains a set of default options like general ledger accounts; locator, lot, and serial controls, inter-organization options, costing method, etc. for each organization defined in Oracle Inventory.