MOAC- Multi Org Access Control

Multiple Organization Architecture was first introduced in Release 10.6, for data security by Operating Unit. In Release 10.7, we added a column, ORG_ID, to each base table that requires ”partitioning” by Operating Units. All the tables that are partitioned are renamed with suffix, ‘_ALL’, and their corresponding secured views are created in APPS schema. Multi-Org views restrict data access by filtering records for a single Operating Unit set by application responsibility level profile, ““MO: Operating Unit”.” The value for the profile option is cached in Application Context, and is initialized whenever FND initialization routine is called. All Multi-Org views as well as any SQL statements that require Multi-Org security contains FND CLIENT_INFO predicate. FND_CLIENT_INFO function retrieves ORG_ID value stored in the application context. The value is valid during a session unless it is explicitly changed by procedure calls. To retrieve all information regardless of the Operating Unit, the _ALL table should be used in the SQL statement. Cross-Organization reports are good example in which the query statements are performed against _ALL tables rather than Multi-Org secured views. In order to increase flexibility and performance in Multi-Org environment while providing the same level data security, Virtual Private Database (VPD) feature introduced in Oracle 8i RDBMS will replace usage of CLIENT_INFO function in Multi-Org Access Control. Virtual Private Database (VPD) The Virtual Private Database feature allows developers to enforce security by attaching a security policy to tables and views in Oracle8i, and to synonyms in Oracle 9i Release2. It attaches predicates for the security policies to every SQL statement against the database objects where policies are applied. When a user directly or indirectly accesses a table with a security policy, the RDBMS dynamically rewrites user’s SQL statement to include conditions set by security policy transparent to the user. The conditions can be expressed in, or returned by a function. Key benefits of VPD feature are ease of policy maintenance and flexibility. Security policy can be maintained in a PL/SQL file. This eliminates the need to change in CASE repository, and simplify the release mechanism. The VPD feature allows multiple security policies to be applied to a database objects (table, view, synonyms etc).

EDI Basics

EDI 850 --> PO Inbound

HRMS

Oracle HRMS can be used for:

• Store, query and track employment and personal information
• Set up your recruitment procedures exactly as your enterprise requires
• Manage careers and succession using competence management and special information types
• Define and manage all your human resource budgets

Creating a new employee...
hr_employee_api.create_us_employee
(p_validate => FALSE, -- Input Parameter
p_hire_date => SYSDATE, -- Input Parameter
p_business_group_id => 0, -- Input Parameter
p_first_name => 'Jigra', -- Input Parameter
p_last_name => 'Last_Name', -- Input Parameter
p_sex => 'M', -- Input Parameter
p_employee_number => 861, -- Input Parameter
p_email_address => 'aemitarora@gmail.com', -- Input Parameter
p_person_id => l_person_id,
p_assignment_id => l_assignment_id,
p_per_object_version_number => l_per_object_version_number,
p_asg_object_version_number => l_asg_object_version_number,
p_per_effective_start_date => l_per_effective_start_date,
p_per_effective_end_date => l_per_effective_end_date,
p_full_name => l_full_name,
p_per_comment_id => l_per_comment_id,
p_assignment_sequence => l_assignment_sequence,
p_assignment_number => l_assignment_number,
p_name_combination_warning => l_name_combination_warning,
p_assign_payroll_warning => l_assign_payroll_warning
);

You can view the new employee info in Per_All_People_F

Here 'Employee Number' was passed as an input parameter (its an IN OUT parameter). You can pass it as an input parameter if the "Employee Number Generation" option (in your Business Group) is set to 'Manual'; if 'Automatic', then you cannot.

You may view/edit this setup here:

R12 Navigation: HRMS Super User >> Work Structures >> Organization >> Description

Query for your business group and then click on "Business Group" under "Organization Classifications". Then click on Others and select "*Business Group info.".

Things to Remember in Oracle HRMS

• You can create a new Contingent Worker using the HR_CONTINGENT_WORKER_API.create_cwk api. Oracle recommends not to refer the person type id in per_all_people_f table; refer to person_type_id in per_person_type_usages_f table especially for non-regular employees like contingent worker

Trivia

• Self–Service Human Resources (SSHR) enables employees and line managers to maintain and update information themselves.

Interfaces

Inventory

• Item Import
  
  
  
  
  

Legacy System --> Mtl_System_Items_Interface --> Item Import Program --> Mtl_System_Items_B (or Mtl_Interface_Errors)

• Material Transactions

Interface Tables to be used are:
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE

Transaction Type ID should be 2 (for "Subinventory Transfers") and "Material Transactions Manager" will process the records from the Interface tables.

If there are any errors while processing, the details of the errors will be available in MTL_INTERFACE_ERRORS table.

Cost Management

• Item Cost Import

Sample insert statement to populate the Item Cost Interface table

INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
(INVENTORY_ITEM_ID
,ORGANIZATION_ID
,COST_TYPE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,Process_Flag
,Item_Cost
,Cost_Element_ID
,USAGE_RATE_OR_AMOUNT
)
SELECT INVENTORY_ITEM_ID
,ORGANIZATION_ID
,COST_TYPE_ID
,SYSDATE
,1441
,SYSDATE
,1441
,14411441
,1
,5
,1
,5
from cst_item_costs where inventory_item_id = 13433714 --12456335
AND Cost_Type_ID = 3

Payables

• Invoice Import

Interface Tables
AP_INVOICE _INTERFACE is the header info
AP_INVOICES_LINES_INTERFACE is the lines table.

RUN PAYABLES INVOICE IMPORT CONCURRENT PROGRAM

Base Tables
AP_INVOICE_ALL is also the header information is stored.
AP_INVOICES_DISTRIBUTIONS_ALL is the lines information table.
AP_PAYMENTS_SCHEDULES_ALL (AMOUNT_REMAINING stores balance amount to be paid)
AP_INVOICE_PAYMENTS_ALL (INVOICE_ID)
AP_CHECKS_ALL (CHECK_ID)

AP_INTERFACE_REJECTIONS (REJECT_LOOKUP_CODE stores the error occurred during the invoice import ex: account required, invalid supplier site )

SQL

Table within a table

A nested table is nothing more than a normal table that has a column whose datatype is another table.

Tables Info - Inventory

MTL_SYSTEM_ITEM_B - Inventory item definitions

• Inventory_Item_ID - Item ID
• Segment1 - Item Name
• Costing_Enabled_Flag*

*There is a trigger ‘Mtl_System_Items_T1’ on this table which updates Cst_Item_Costs table in case of any update to ‘Costing_Enabled_Flag’

MTL_SYSTEM_ITEMS_TL - Translations table table holding item descriptions in multiple languages

MTL_SYSTEM_ITEMS_INTERFACE - Inventory Items Open Interface table

MTL_PARAMETERS - Inventory control options and defaults

• Organization_ID
• Organization_Code
• Master_Organization_ID

MTL_SECONDARY_INVENTORIES - Subinventory definitions

MTL_CATEGORIES_B - Code combinations table for item categories

MTL_ITEM_CATEGORIES - Define item assignments to categories

• Inventory_Item_ID
• Organization_ID
• Category_Set_ID
• Category_ID

MTL_CATEGORY_SETS_B - Category Sets

• Category_Set_ID
• Structure_ID
• Validate_Flag
• Control_Level
• Category_Set_Name
• Description
• Multi_Item_Cat_Assign_Flag

MTL_CATEGORY_SET_VALID_CATS - Valid categories for category sets

MTL_ITEM_CATALOG_GROUPS_B - Item catalog groups; it's the code combinations table for item catalog groups.
MTL_MATERIAL_TRANSACTIONS - Material transaction table
MTL_MATERIAL_TRANSACTIONS_TEMP - Temporary table for processing material transactions

MTL_TRANSACTIONS_INTERFACE - Gateway for externally generated material transactions
MTL_TRANSACTION_REASONS Inventory Transaction Reasons Table
MTL_TRANSACTION_TYPES Inventory Transaction Types Table
MTL_TRANSACTION_LOTS_INTERFACE Temporary lot storage for transaction processor
MTL_TRANSACTION_LOTS_TEMP Temporary lot numbers holding table
MTL_TRANSACTION_LOT_NUMBERS Transaction lot numbers MTL_CROSS_REFERENCES Assign cross references to items MTL_ABC_ASSGN_GROUP_CLASSES Assigns abc classes to abc groups
MTL_ABC_ASSIGNMENTS Assigns items to abc classes in an abc group
MTL_ABC_ASSIGNMENT_GROUPS Defines abc groups
MTL_ABC_CLASSES Inventory ABC classes
MTL_ABC_COMPILES ABC descending value compile data
MTL_ABC_COMPILES_EFC Euro as a Functional Currency Archive
MTL_ABC_COMPILE_HEADERS ABC compile header data
MTL_ABC_COMPILE_HEADERS_EFC Euro as a Functional Currency Archive
MTL_ACTUAL_COST_SUBELEMENT Stores the material overhead subelements
MTL_ACTUAL_COST_SUBELEMENT_EFC Euro as a Functional Currency Archive
MTL_ATP_RULES ATP computation rules
MTL_ATTR_APPL_DEPENDENCIES Dependencies between item attributes and Oracle products
MTL_BIS_INV_BY_PERIOD BIS table that stores inventory turns data by organization,
accounting period and Inventory item id for open periods and closed periods
MTL_CATALOG_SEARCH_ITEMS Items returned by item searches
MTL_CATEGORIES_TL Categories table holding translated Description column for Item Categories
MTL_CATEGORY_ACCOUNTS Asset and expense accounts by item category, by subinventory MTL_CATEGORY_SETS_TL A table holding translated Name and Description columns
for Category Sets
MTL_CC_ENTRIES_INTERFACE Cycle Count Entries Interface data
MTL_CC_INTERFACE_ERRORS Cycle Count Entries Interface process errors
MTL_CC_SCHEDULE_REQUESTS Defines cycle count schedule requests
MTL_CC_SCHED_COUNT_XREFS Cross references count entry requests
MTL_CC_SERIAL_NUMBERS Serial Numbers for Cycle Count Entries.
MTL_CC_SUBINVENTORIES Defines subinventories to be used in subinventory level cycle count
MTL_CI_INTERFACE Customer Items Interface
MTL_CI_XREFS_INTERFACE Customer Items Cross References Interface
MTL_COMMODITY_CODES Customer item commodity codes
MTL_COPY_ORG_INTERFACE Generate Inventory Organizations
MTL_COUNTRY_ASSIGNMENTS stores information concerning the assignment of countries
to economic zones.
MTL_CROSS_REFERENCES_INTERFACE (not currently used)
MTL_CROSS_REFERENCE_TYPES Define cross reference types
MTL_CST_ACTUAL_COST_DETAILS Actual cost information by element, by level
MTL_CST_ACTUAL_COST_DETAIL_EFC Euro as a Functional Currency Archive
MTL_CST_LAYER_ACT_COST_DETAILS Actual cost information by element, by level
MTL_CST_TXN_COST_DETAILS The cost of a transaction by element, by level
MTL_CST_TXN_COST_DETAILS_EFC Euro as a Functional Currency Archive
MTL_CUSTOMER_ITEMS Customer item Information
MTL_CUSTOMER_ITEM_XREFS Relationships between customer items and inventory items
MTL_CYCLE_COUNT_CLASSES Defines cycle count classes
MTL_CYCLE_COUNT_CLASSES_EFC Euro as a Functional Currency Archive
MTL_CYCLE_COUNT_ENTRIES Defines cycle count entries
MTL_CYCLE_COUNT_ENTRIES_EFC Euro as a Functional Currency Archive
MTL_CYCLE_COUNT_HEADERS Defines cycle count header information
MTL_CYCLE_COUNT_HEADERS_EFC Euro as a Functional Currency Archive
MTL_CYCLE_COUNT_ITEMS Defines items to be used in cycle count
MTL_DEFAULT_CATEGORY_SETS Default category set for a particular functional area
MTL_DEMAND Sales order demand and reservations
MTL_DEMAND_HISTORIES Sales order demand and reservations
MTL_DEMAND_INTERFACE Temporary demand storage for the transaction processor
MTL_DESCRIPTIVE_ELEMENTS Descriptive element definitions for an item catalog group
MTL_DESCR_ELEMENT_VALUES Stores the descriptive element values for a particular item
MTL_FORECAST_RULES Forecast rules for forecast processes
MTL_INTERCOMPANY_PARAMETERS Inter–company relationships
MTL_INTERFACE_ERRORS Item open interface errors
MTL_INTERFACE_PROC_CONTROLS I nterface Processes Control Parameters
MTL_INTERORG_PARAMETERS Inter–organization relationships
MTL_INTERORG_SHIP_METHODS Relationships between shipping networks and shipment methods
MTL_ITEM_ATTRIBUTES I tem attributes table
MTL_ITEM_ATTRIBUTES_TEMP Temporary Table specifically for item attributes copy form
MTL_ITEM_CATEGORIES_INTERFACE Item Category Open Interface table; used to load item assignments to category sets and categories into Oracle Inventory.
MTL_ITEM_LOCATIONS Definitions for stock locators
MTL_ITEM_LOC_DEFAULTS Inventory item receiving and shipping locator default values
MTL_ITEM_REVISIONS Item revisions
MTL_ITEM_REVISIONS_INTERFACE Item revisions interface
MTL_ITEM_STATUS Material status definitions
MTL_ITEM_SUB_DEFAULTS Inventory item receiving and shipping subinventory default values
MTL_ITEM_SUB_INVENTORIES Item–subinventory assignments
MTL_ITEM_TEMPLATES Item template definitions
MTL_ITEM_TEMPL_ATTRIBUTES Item attributes and attribute values for a template
MTL_ITEM_VALUES_TEMP Holds Item Attributes and their values. Used in the Item Attributes Copyform.
MTL_KANBAN_CARDS Kanban Cards
MTL_KANBAN_CARD_ACTIVITY Kanban Card Activity
MTL_KANBAN_CARD_PRINT_TEMP Temporary table used to print kanban cards
MTL_KANBAN_PULL_SEQUENCES Kanban Pull Sequences
MTL_KANBAN_TEMPLATES
MTL_LOT_NUMBERS Lot number definitions
MTL_MANUFACTURERS Define manufacturers
MTL_MATERIAL_STATUSES Inventory status definition – Used by WMS only
MTL_MATERIAL_STATUSES_B Base table for inventory status definitions – Used by WMS
MTL_MATERIAL_TXN_ALLOCATIONS Unit allocations among repetitive schedules
MTL_MFG_PART_NUMBERS Define manufacturer part numbers
MTL_MGD_INVENTORY_POSITIONS Stores the data sets built for on line display or for export
MTL_MOVEMENT_PARAMETERS Movement parameter values
MTL_MOVEMENT_STATISTICS Stores all relevant information for movement statistics transactions.
MTL_ONHAND_QUANTITIES FIFO quantities by control level and receipt
MTL_ONHAND_QUANTITY_TEMP Temporary table for on–hand quantity queries
MTL_ORG_REPORT_TEMP Multi–organization inventory report temporary table
MTL_PAC_TXN_COST_DETAILS Transaction details for a period
MTL_PENDING_ITEM_STATUS History of item–status assignments
MTL_PHYSICAL_ADJUSTMENTS Physical inventory adjustments
MTL_PHYSICAL_INVENTORIES Physical inventory definitions
MTL_PHYSICAL_INVENTORY_TAGS Physical inventory tag definitions
MTL_PHYSICAL_SUBINVENTORIES Physical inventory specific subinventories
MTL_PICKING_RULES Picking Rule Definitions
MTL_PLANNERS Planner code definitions
MTL_PURGE_HEADER Transaction history purges
MTL_RELATED_ITEMS Item relationships
MTL_REPLENISH_HEADERS Replenishment header information
MTL_REPLENISH_HEADERS_INT Replenishment header interface information
MTL_REPLENISH_LINES Replenishment line information
MTL_REPLENISH_LINES_INT Replenishment line interface information
MTL_RESERVATIONS Reservations
MTL_RESERVATIONS_INTERFACE Reservations Interface
MTL_RTG_ITEM_REVISIONS Routing revisions
MTL_RTG_ITEM_REVS_INTERFACE Routing revisions interface table
MTL_SAFETY_STOCKS Safety stocks
MTL_SALES_ORDERS Local definitions of sales orders
MTL_SEASONALITY_INDICES Seasonality indices
MTL_SECONDARY_LOCATORS Item–subinventory–locator assignments
MTL_SERIAL_NUMBERS Serial number definitions
MTL_SERIAL_NUMBERS_INTERFACE Temporary serial number storage for transactions processor
MTL_SERIAL_NUMBERS_TEMP Temporary serial number holding table
MTL_SHORT_CHK_PARAM Shortage Parameters
MTL_STATUS_ATTRIBUTE_VALUES Item status code attribute values
MTL_SUMMARY_TEMP Transaction summaries
MTL_SUPPLY Stores incoming supply information
MTL_SUPPLY_DEMAND_TEMP Temporary table containing ATP details returned from ATP user–exit
MTL_TXN_COST_DET_INTERFACE Stores transaction cost by element and by level for
transactions to be imported through the transaction interface
MTL_TXN_REQUEST_HEADERS Move Order headers table
MTL_TXN_REQUEST_LINES Move order lines table
MTL_TXN_SOURCE_TYPES Valid transaction source types
MTL_UNITS_OF_MEASURE_TL Unit of measure definitions
MTL_UNIT_TRANSACTIONS Serial number transactions
MTL_UOM_CLASSES_TL Unit of measure classes
MTL_UOM_CLASS_CONVERSIONS Conversions between base units of two different classes
MTL_UOM_CONVERSIONS Unit of measure conversion table for both default and item specific conversions

Oracle Inventory

Move orders

are formal requests to move material within (one subinventory to another) the same inventory organization.

There are three types:

Requisition Move Orders - Manually crearted. User may choose to print a pick slip for the material handler to know where to get the material from.

Source Types --> subinventory transfer and account transfer

Replenishment Move Orders - pre-approved move orders that are created automatically from Min-Max Planning, Replenishment Counting, Kanban replenishment.

Source Type --> Subinventory

Pickwave Move Orders -  used in Order Management. The Oracle Shipping Execution pick release process generates a Pick Wave Move Order to move the material from its source location to the Staging subinventory.

Backend Story:

SELECT header_id, h.* FROM apps.MTL_TXN_REQUEST_HEADERS h

WHERE Request_Number IN ('16788419', '16874441');

SELECT line_status, l.* FROM apps.MTL_TXN_REQUEST_LINES l
WHERE header_id IN (16788751, 16870925); -- 5 is Closed
Select *from apps.mfg_lookups
where lookup_type LIKE 'MTL_TXN_REQUEST%'; --_STATUS';
SELECT * FROM apps.MTL_TXN_SOURCE_TYPES;