Thursday, May 26, 2016

Key Tables in Inventory

a) Items
Select * from mtl_system_items_b;

b) Categories
Select * from mtl_categories_b;

c)Template
select * from mtl_item_templates;

d)Onhand
Select * from mtl_onhand_quantities;

e) Transactions
select * from mtl_material_transactions;

f) Material transaction interface
select * from mtl_transactions_interface;

g)Pending transactions
select * from mtl_material_transactions_temp;

h) Item Costs
select * from cst_item_costs;

Key tables in Order Management and Shipping Execution


a) Header
select * from oe_order_headers_all;

b) Lines
select * from oe_order_lines_all;

c) Delivery Details
select * from wsh_delivery_details;

d) Deliveries
select * from wsh_delivery_assignments


Key tables in Purchasing


a) Header
select * from po_headers_all

b) Lines
select * from po_lines_all;

c) Shipment
select * from po_line_locations_all;

d) Distributions
select * from po_distributions_all;

e)Account Expense Rules
select * from PO_RULE_EXPENSE_ACCOUNTS;

select * from PO_RULE_EXPENSE_ACCOUNTS_V;

12.2.4 Subscription contract

Subscription contract been created from OM with mapping it's new attributes in R12.2 




iProcurement Categories in Catalog Schema.



The types of categories in the catalog that define the catalog's hierarchy are:

Purchasing Item categories: Every item in the catalog must belong to a Purchasing Item category. Purchasing Item categories are recognized by many integrating Oracle Applications in addition to Oracle iProcurement. The association of Purchasing Item categories with catalog items enables a wide range of functionality in Oracle Applications.

Shopping categories: Used to group similar items for presentation to the iProcurement user. Shopping categories generally have names that are more shopper friendly than Purchasing Item categories, and are displayed prominently in many iProcurement pages to help requesters find what
they are shopping for.



Shopping categories are not mandatory for all items in the catalog, but they are highly recommended for items that are intended for iProcurement requesters. Shopping categories are found at the lowest level of the category hierarchy; therefore a shopping category can never be a parent category to a child category.



Browsing categories, also known as navigation categories (or master or intermediate level categories), define the category hierarchy. The category hierarchy helps requesters browse for catalog items Browsing categories can be either a parent or child to another category, but cannot contain any items.

Ex: 


Friday, May 20, 2016

Uninstalling Demantra

The Demantra software is not listed in the Control Panel > Add/Remove Programs utility. If you plan to reinstall Demantra, you can overwrite your current install.
You can either use a freeware application that can detect and uninstall the Demantra program, or you can do the following to manually remove Demantra from your system:
  1. Make sure that none of the Oracle Demantra software is running, including all background items such as the Analytical Engine.
  2. Remove Oracle software from the system Path variable so shown below.
    • On the Windows Start menu, click Settings > Control Panel > System.
    • Click System.
    • Select the Advanced tab.
    • Click Environment Variables.
    • In the System Variables section, click Path and then click Edit.
    • Carefully remove any Oracle Demantra additions to this variable, leaving other parts unchanged.
  3. Manually delete the Oracle Demantra files from the machine.
  4. In the registry(regedit.exe), search for the HKEY_CURRENT_USER\Software key for Demantra and delete it.
  5. Log in to the database as the database administrator and delete the Oracle Demantra database user.
  6. Delete any Demantra shortcuts from the desktop or program groups.

Monday, March 28, 2016

Oracle Approvals Management (AME)

                                                             AME for Requisitions

Setup:

a) Profile Option

Set up profile option for Application = Purchasing and Profile = AME:Installed.

b) Assign Approval Transaction Type

Purchasing Super User > Setups > Purchasing > Document Types

Assign Approval Transaction Type to ‘PURCHASE_REQ/INTERNAL_REQ’.

Approval transaction type is given a value, then Requisition will use AME for
approvals.
For Purchase Requisition, the value is “PURCHASE_REQ”
For Internal Requisition, the value is “INTERNAL_REQ”
These are seeded values defined in the table “ame_calling_apps”.
If Approval transaction type is Blank, AME will not be used for approvals.


c) Assign AME access to users

Assign AME Roles and Responsibilities

AME responsibilities in 11i.AME.A are assigned directly to the users. However, In R12 or 11i.AME.B and higher, AME responsibilities are assigned indirectly to users through roles. The roles are assigned to the users by the SYSADMIN user using the User Management responsibility. Once the roles are assigned, the AME responsibilities are automatically available to the users without specifically assigning the AME responsibilities to the users. Here are steps to assign the roles:

1. Login as System Administrator user
2. Select the responsibility "User Management". (NOTE: User Management data is stored in the UMX schema)
3. Select "Users" menu option
4. Search for the user to whom you wish to grant AME roles
5. In the results table, click on update icon
6. In the update user page, user details can be seen along with a list of roles available to user.
Click on "Assign Roles"
7. Search for Approval% and Select roles from the resulting LOV. Choose the roles that are applicable (proper authority) for the user, and click the Select button.
8. Specify justification and relevant dates for the newly assigned roles, and click Apply to assign the roles to the user.


d) Grant Transaction Type Access to Users

AME restricts access to transaction types using Data Security.  Grant users access to the transaction types using the Grants page. Set up user access as follows:

1. Navigate to the Personal Home Page.

2. Select Functional Administrator Responsibility

3. From the Grants page, press on the Create Grant button

4. Create a grant with the following information:
  • Name <specify a descriptive name> 
  • Grantee Type = Specific User
  • Grantee = <The user who desires AME access>
  • Object = AME Transaction Types
5. Click Next and select the Object Data Context
  • Data Context Type = All Rows
6. Click Next to define the object parameters and Select Set
  • Set = AME Calling Applications
7. Click Next. Review the changes and then Finish the process.

e) Review and Modify AME Setup

AME is designed to provide approval logic for many transaction types. Transaction types used for Purchasing include: Purchase Requisition Approval, Internal Requisition Approval, Requester Change Order Approval and Purchase Order Approval. 
1. Navigate to the Approvals Management Business Analyst, Standard responsibility
2. Choose the Business Analyst Dashboard menu from the responsibility
3. Use the Transaction Type LOV to search and select the transaction type
4. Use the links on the right in the Approval Process Setup region to set the components (Attributes, Conditions, Action Types, Approver Groups) and rules, or to use the test workbench in AME.

1. Action Types

1. An action type is a collection of actions having similar functionality. Every action belongs to an action type. Action types are enabled or disabled for a particular transaction type. AME may give an error when attempting to enable an action type for a transaction if the transaction is not designed to allow that action type.
2. To disable or enable action types for the transaction, select the Action Types link
3. The Action Types page shows the action types that are currently enabled for the transaction type. Use the Previous and Next links to scroll through the list of enabled action types. Select the Use Existing Action Type button to see other pre-defined action types available in AME. Some of these may or may not be applicable to the currently select transaction type; AME will give an error if the user tries to add a non-relevant action type for the selected transaction type.
4. If you are going to use Employee/Supervisor, assign a level (approval authority) to the jobs. Navigate to HR responsibility -> Work Structures -> Job -> Description - to assign a Level (Approval Authority) to a Job.  Query up the Job and enter the appropriate Job Level in the Approval Authority field.
5. In AME, select any or all of the following Action Types for JOB BASED approvals if applicable for your business requirements:
  • absolute job level / chains of authority based on absolute job level
  • final approver only / chains of authority containing only the final job-level approver
  • manager then final approver / chain of authority includes requestor's manager and then the final approver
  • relative job level / chains of authority based on relative job level
  • supervisory level / chains of authority based on number of supervisory levels
6. In AME, select any or all of the following Action types for APPROVER GROUP approvals if applicable for your business requirements:
  • post-chain-of-authority approvals / group approvals after the chain of authority
  • pre-chain-of-authority approvals / group approvals before the chain of authority
  • approval-group chain of authority / chain of authority includes an approval group
7. In AME, select any of all of the following Action types for POSITION BASED approvals (Only in R12 and higher) if applicable for your business requirements
  • hr position / chains of authority based on a particular HR position
  • hr position level / chains of authority based on HR positions

2. Attributes

1. Attributes are the base element for an AME Rule. Attribute values are retrieved from the Oracle E-Business Suite Applications database or derived from values in the database. AME is seeded with attributes relevant to the transaction type, and the user can create new attributes in AME for use in AME rules.
2. Select the Attributes link to view or add attributes for the selected transaction type
3. Use the Previous and Next links to scroll through the existing attributes. Some of the attributes include ITEM_CATEGORY, ITEM_NUMBER as well as other attributes. When AME approvals is enabled, these values get populated for the purchasing document, and AME uses this information to determine the appropriate AME rule(s) to use.
4. In addition to the seeded attributes, a customized attribute can be created. SRI_CATEGORY_SEGMENT is an example of this. This attribute uses a query to capture SEGMENT1 of the Item Category flexfield. The Item Category flexfield may be setup to use one or more segments; this customized AME attribute captures only SEGMENT1 of the flexfield. This allows the users to setup conditions and rules that are dependent on a certain value in SEGMENT1 of the ITEM Category used on the requisition. NOTE: The new attribute SRI_CATEGORY_SEGMENT1 uses the same sql query as the seeded ITEM_CATEGORY AME attribute, except it selects mck.segment1 rather than mck.concatenated_segments.

3. Conditions

1. Conditions identify values and value ranges for some or all of the attributes available. AME rules refer to these conditions to determine if a particular rule is applicable for the specific document being approved. For example, an AME rule can be setup to require certain approvers if $0 USD <= requisition total < $1000 USD. Since REQUISITION_TOTAL is a seeded attribute, the user can define a condition $0 USD <= requisition total < $1000 USD, and then use this condition in a rule to require certain approvers for the requisition. The rule cannot refer to this condition until it is defined in AME Conditions for the Purchase Requisition Approval transaction type.
2. Select the Conditions link from the AME Business Analyst Dashboard after specifying the transaction type.  Selecting the Conditions link will display the existing conditions defined for the transaction type, and also allow the user to create new conditions for the transaction.
3. Click the Create button to create a new condition
4. To define the new condition, specify whether the condition is ordinary, or an exception condition (which can only be used in an exception rule). Use the Attribute LOV to choose the attribute on which the condition is based. The condition will specify a value or range of values for the attribute, so the attribute must be selected before the value(s) can be defined.
5. Define the allowed value or value range for the selected attribute. Click Apply to complete the condition definition.

4. Approver Groups

1. Approver Groups are optional. Setup Approver Groups if additional approvers are required for particular conditions, or to specify a dynamic sql query for additional approvers. The rules defined for the transaction can be based on Approver Groups, Jobs defined in HR setup, or Positions defined in HR setup (only in R12); the rules may also use a combination of Job, Position, and Approver Group basis.
2. Select the Approver Groups link from the AME Business Analyst Dashboard.
3. View and edit existing approver groups, or Click the Create button to create a new approver group.
4. When creating the approval group specify all the mandatory values.
a. Give a name and description to the approval group.
b. Specify an order number (order number of this approver group relative to other approver groups).
c. Choose a voting regime, only Serial is supported for Purchase Requisition Approval in 11.5.10 and 11.5.9. R12 does allow other voting regimes that use parallel routing.
d. Choose Static if approvers will be selected when defining the approval group, or choose Dynamic if a sql query is used to dynamically find the approvers for this approver group when the approval transaction is being processed.
e. Click the Add Another Row button to add approvers to the approval group now.
f. Click Apply to save the approver group
5. The approval group members can be added as additional approvers to the normal chain of command approvers generated by AME.

5. Rules

1. Define rules to specify approvers that should be included in the approval list under specific conditions for the approval transaction.
2. Select the Rules link from the AME Dashboard after selecting the transaction type
3. Review the list of existing rules already defined for the transaction
4. Select the Create button to create a new rule for the transaction. (Optionally, if there already exists a similar rule choose the Duplicate icon or the Use Existing Rule button).
a. Specify a name for the new rule and choose the rule type and effective dates.  Rule types are explained in the AME online help pages along with examples. The most common types are List Creation, Pre List Approver Group and Post List Approver Group. (NOTE: Some rule types may not be available if the corresponding action types have not been assigned to the transaction type. Use the Action Types feature to add or remove action types for the transaction)
b. Specify one or more conditions that activate the rule. The Conditions are defined in the AME Setup, and they may be seeded conditions or user defined conditions.
c. Choose the Action Type and then choose a specific action. The list of actions available is dependent on the Action Type selected. The actions are related to Jobs, Positions (in R12), or Approver Groups. (Action Types are discussed previously in this whitepaper)
d. Review the rule details and click Finish to complete the rule setup, or click Back to make changes.

6. Test Workbench

Use the Test Workbench to determine which AME Rule(s) apply to a specific document, or to determine which AME Rule(s) apply for an adhoc combination of values specified at the time of the test. Select the Test Workbench link from the AME Dashboard
1. Specific Requisition test
a. Click the Run Real Transaction Test button.
b. Specify the value of REQUISITION_HEADER_ID from PO_REQUISITION_HEADERS_ALL as the Transaction Id value. Click Go to see the AME rules that apply to the requisition.
c. Adjust the rules setup to cause rules to be called differently based on the business requirements.
2. Specific Purchase Order test
a. Click the Run Real Transaction Test button.
b. Specify the value of AME_APPROVAL_ID from PO_HEADERS_ALL as the Transaction Id value. Click Go to see the AME rules that apply to the purchase order.
c. Adjust the rules setup to cause rules to be called differently based on the business requirements.

3. Adhoc test
a. Click the Create button on the Test Workbench page
b. Specify Name and Description for the test, and specify values for pertinent attributes (e.g. Requisition Total = $100 USD)
c. Click the Run Test Case button to see the applicable AME rules, and the resulting AME approval list that will be built based on the conditions specified for the attributes.

Test the Functionality

1. Create a requisition or purchase order and verify that the Approval List is built per the AME rules based on the conditions present on the attributes.

View the AME Setup

1. Click the Setup Report link in the Quick Links section of the Dashboard
2. Select the appropriate transaction type (Example: Purchase Requisition Approval) and click Go.
3. Click the Printable Page button to view the complete setup for the selected transaction type (Attributes, Conditions, Rules, Approval Groups, and etc.)
4. Compare the AME setup to the requisition attributes and approval list generated for a specific requisition, or compare the AME setup to the business requirements.