Friday, June 27, 2014

Sql query for relation between Business Group, Legal Entity, Operating Unit and Balancing segment

select HRL.COUNTRY,
  HROUTL_BG.name BG,
  HROUTL_BG.ORGANIZATION_ID,
  LEP.LEGAL_ENTITY_ID,
  LEP.name LEGAL_ENTITY,
  HROUTL_OU.name OU_NAME,
  HROUTL_OU.ORGANIZATION_ID ORG_ID,
  HRL.LOCATION_ID,
  HRL.LOCATION_CODE,
  GLEV.FLEX_SEGMENT_VALUE
from XLE_ENTITY_PROFILES LEP,
  XLE_REGISTRATIONS REG,
  HR_LOCATIONS_ALL HRL,
  HZ_PARTIES HZP,
  FND_TERRITORIES_VL TER,
  HR_OPERATING_UNITS HRO,
  HR_ALL_ORGANIZATION_UNITS_TL HROUTL_BG,
  HR_ALL_ORGANIZATION_UNITS_TL HROUTL_OU,
  HR_ORGANIZATION_UNITS GLOPERATINGUNITSEO,
  GL_LEGAL_ENTITIES_BSVS GLEV
where LEP.TRANSACTING_ENTITY_FLAG      = 'Y'
and LEP.PARTY_ID                       = HZP.PARTY_ID
and LEP.LEGAL_ENTITY_ID                = REG.SOURCE_ID
and REG.SOURCE_TABLE                   = 'XLE_ENTITY_PROFILES'
and HRL.LOCATION_ID                    = REG.LOCATION_ID
and REG.IDENTIFYING_FLAG               = 'Y'
and TER.TERRITORY_CODE                 = HRL.COUNTRY
and LEP.LEGAL_ENTITY_ID                = HRO.DEFAULT_LEGAL_CONTEXT_ID
and GLOPERATINGUNITSEO.ORGANIZATION_ID = HRO.ORGANIZATION_ID
and HROUTL_BG.ORGANIZATION_ID          = HRO.BUSINESS_GROUP_ID
and HROUTL_OU.ORGANIZATION_ID          = HRO.ORGANIZATION_ID
and GLEV.LEGAL_ENTITY_ID               = LEP.LEGAL_ENTITY_ID;

SQL Query To Check Form Personalizations

SELECT   ffcr.SEQUENCE "Seq", ffcr.description "Description",
         DECODE (ffcr.rule_type,
                 'F', 'Form',
                 'A', 'Function',
                 'Other'
                ) "Level",
         ffcr.enabled "Enabled", ffcr.trigger_event "Trigger Event",
         ffcr.trigger_object "Trigger Object", ffcr.condition "Condition",
         DECODE (ffcr.fire_in_enter_query,
                 'Y', 'Both',
                 'N', 'Not in Enter-Query Mode',
                 'O', 'Only in Enter-Query Mode',
                 'Other'
                ) "Processing Mode"
    FROM apps.fnd_form_custom_rules ffcr
   WHERE ffcr.function_name = 'PO_POXPOEPO'
     AND ffcr.form_name = 'POXPOEPO'
ORDER BY ffcr.SEQUENCE;

Key Tables in Oracle Inventory

TableDescription
MTL_PARAMETERSIt 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. Each organization’s item master organization (MASTER_ORGANIZATION_ID) and costing organization (COST_ORGANIZATION_ID) are maintained here.
MTL_SYSTEM_ITEMS_BThis is the definition table for items. 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. Therefore, the same item can be defined in more than one organization. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item Description and Long Description in multiple languages.
MTL_ITEM_STATUSThis 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_TLThis 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_LOCATIONSThis 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_CATEGORIESThis table stores inventory item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items always may be assigned to multiple category sets. However, depending on the Multiple Assignments Allowed attribute value in a given category set definition, an item can be assigned to either many or only one category in that category set.
MTL_CATEGORIES_BThis is the code combinations table for item categories. Items are grouped into categories within the context of a category set to provide flexible grouping schemes. Item categories now support multilingual category description. MLS is implemented with a pair of tables: MTL_CATEGORIES_B and MTL_CATEGORIES_TL. MTL_CATEGORIES_TL table holds translated Description for Categories.
MTL_CATEGORY_SETS_BIt contains the entity definition for category sets. A category set is a categorization scheme for a group of items. Items may be assigned to different categories in different category sets to represent the different groupings of items used for different
purposes. An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with the category set. Category Sets now support multilingual category set name and description. MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL. MTL_CATEGORY_SETS_TL table holds translated Name and Description for Category Sets.
MTL_DEMANDThis 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_INVENTORIESThis is the definition table for the subinventory. A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this item will physically exist in inventory.
MTL_ONHAND_QUANTITIESIt stores quantity on hand information by control level and location. It is maintained as a stack of receipt records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria.
MTL_TRANSACTION_TYPESIt contains seeded transaction types and the user defined ones. USER_DEFINED_FLAG will distinguish the two. The table also stores the TRANSACTION_ACTION_ID and TRANSACTION_SOURCE_TYPE_ID that is associated with each transaction type.
MTL_MATERIAL_TRANSACTIONSThis table stores a record of every material transaction or cost update performed in Inventory. Records are inserted into this table either through the transaction processor or by the standard cost update program. The columns TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID and TRANSACTION_SOURCE_NAME describe what the transaction is and against what entity it was performed.
MTL_ITEM_ATTRIBUTESThis table stores information on item attributes. Each
row in the table corresponds to an attribute. The table stores the attribute name, the corresponding user-friendly name seen by the users, and the kind of validation enforced on the attribute.
MTL_ITEM_CATALOG_GROUPS_BThis is the code combinations table for item catalog groups. An item catalog group consists of items that can be described by the same set of descriptive elements or item properties. When an item is associated with an item catalog group, the item inherits the descriptive elements for that group which then behave like additional item attributes.
MTL_ITEM_REVISIONS_BIt stores revision levels for an inventory item. When an item is defined a starting revision record is written out to this table, so every item will at least have one starting revision.
MTL_ITEM_TEMPLATES_BThis is the definition table for item templates. It
contains the user-defined name (TEMPLATE_NAME) and description (DESCRIPTION) ONLY for backward compatibility. You can use a template to set certain item attributes.
MTL_DESCRIPTIVE_ELEMENTSIt stores the descriptive element definitions for an item catalog group. Descriptive elements are defining properties used to describe in the catalog group.
MTL_DESCR_ELEMENT_VALUESIt stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.
ORG_ACCT_PERIODSIt holds the open and closed financial periods for organizations.
MTL_CUSTOMER_ITEMSIt 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_INTERFACEIt temporarily stores the definitions for inventory items, engineering items and purchasing items before loading this information into Oracle Inventory.
MTL_TRANSACTIONS_INTERFACEIt allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory  transaction module.
MTL_ITEM_REVISIONS_INTERFACEIt temporarily stores revision levels for an inventory item before loading this information into Oracle Inventory.
MTL_ITEM_CATEGORIES_INTERFACEThis table temporarily stores data about inventory item assignments to category sets and categories before loading this information into Oracle Inventory.
MTL_DESC_ELEM_VAL_INTERFACEThis table temporarily stores descriptive element values for an item that is associated with an item catalog group before loading this information into Oracle Inventory.
MTL_DEMAND_INTERFACEIt 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_ERRORSIt stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

FND Tables

FND_APPLICATION:  Stores applications registered with Oracle Application Object Library.

FND_APPLICATION_TL: Stores translated information about all the applications registered with Oracle Application Object Library.

FND_APP_SERVERS: This table will track the servers used by the E-Business Suite system.

FND_ATTACHED_DOCUMENTS: Stores information relating a document to an application entity.

FND_CONCURRENT_PROCESSES: Stores information about concurrent managers.

FND_CONCURRENT_PROCESSORS: Stores information about immediate (subroutine) concurrent program libraries.

FND_CONCURRENT_PROGRAMS: Stores information about concurrent programs. Each row includes a name and description of the concurrent program.

FND_CONCURRENT_PROGRAMS_TL: Stores translated information about concurrent programs in each of the installed languages.

FND_CONCURRENT_QUEUES: Stores information about concurrent managers.

FND_CONCURRENT_QUEUE_SIZE: Stores information about the number of requests a concurrent manager can process at once, according to its work shift.

FND_CONCURRENT_REQUESTS: Stores information about individual concurrent requests.

FND_CONCURRENT_REQUEST_CLASS: Stores information about concurrent request types.

FND_CONC_REQ_OUTPUTS: This table stores output files created by Concurrent Request.

FND_CURRENCIES: Stores information about currencies.

FND_DATABASES: It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.

FND_DATABASE_INSTANCES: Stores instance specific information. Every database has one or more instance.

FND_DESCRIPTIVE_FLEXS: Stores setup information about descriptive flexfields.

FND_DESCRIPTIVE_FLEXS_TL: Stores translated setup information about descriptive flexfields.

FND_DOCUMENTS: Stores language-independent information about a document.

FND_EXECUTABLES: Stores information about concurrent program executable.

FND_FLEX_VALUES: Stores valid values for key and descriptive flexfield segments.

FND_FLEX_VALUE_SETS: Stores information about the value sets used by both key and descriptive flexfields.

FND_LANGUAGES: Stores information regarding languages and dialects.

FND_MENUS: It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.

FND_MENUS_TL: Stores translated information about the menus in FND_MENUS.

FND_MENU_ENTRIES: Stores information about individual entries in the menus in FND_MENUS.

FND_PROFILE_OPTIONS: Stores information about user profile options.

FND_REQUEST_GROUPS: Stores information about report security groups.

FND_REQUEST_SETS: Stores information about report sets.

FND_RESPONSIBILITY: Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.

FND_RESPONSIBILITY_TL: Stores translated information about responsibilities.

FND_RESP_FUNCTIONS: Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.

FND_SECURITY_GROUPS: Stores information about security groups used to partition data in a Service Bureau architecture.

FND_SEQUENCES: Stores information about the registered sequences in your applications.

FND_TABLES: Stores information about the registered tables in your applications.

FND_TERRITORIES: Stores information for countries, alternatively known as territories.

FND_USER: Stores information about application users.

FND_VIEWS: Stores information about the registered views in your applications.


Important Account Payables Tables

AP_SUPPLIERS:
  • This table replaces the old PO_VENDORS table.
  • It stores information about your supplier level attributes.
  • Each row includes the purchasing, receiving, invoice, tax, classification, and general information.
  • Oracle Purchasing uses this information to determine active suppliers.
  • The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.


AP_SUPPLIER_SITES_ALL:
  • This table replaces the old PO_VENDOR_SITES_ALL table.
  • It stores information about your supplier site level attributes.
  • There is a row for unique combination of supplier address, operating unit and the business relationship that you have with the supplier.
  • The supplier address information is not maintained in this table and is maintained in TCA. The reference to the internal identifier of address in TCA will be stored in AP_SUPPLIER_SITES_ALL.LOCATION_ID, to link the address record in TCA.
  • Each row includes the supplier reference, purchasing, invoice, and general information.

AP_INVOICES_ALL:
  • It contains records for invoices you enter.
  • There is one row for each invoice you enter.
  • An invoice can have one or more invoice distribution lines and can have one or more scheduled payments.  


AP_INVOICE_LINES_ALL:
  • It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
  • An invoice can have one or more invoice lines.
  • An invoice line represents goods (direct or indirect materials), service(s), and/or associated tax/freight/miscellaneous charges invoiced from a supplier.
  • An invoice line should contain all the attributes that are present on the physical or electronic invoice presented by the supplier.


AP_INVOICE_DISTRIBUTIONS_ALL:
  • It holds the distribution information that is manually entered or system-generated.
  • There is one row for each invoice distribution and a distribution must be associated with an invoice.
  • An invoice can have multiple distributions.


AP_INVOICE_PAYMENTS_ALL:
  • It contains records of invoice payments that you made to suppliers.
  • There is one row for each payment you make for each invoice and there is one payment and one invoice for each payment in this table.
  • Oracle Payables application updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick payment.
  • When you void a payment, your Oracle Payables inserts an additional payment line that is the negative of the original payment line.


AP_PAYMENT_SCHEDULES_ALL:
  • This table stores information about scheduled payment information on invoices.


AP_PAYMENT_HISTORY_ALL:
  • It stores the clearing/unclearing history for payments.
  • It also stores the maturity history for future dated payments.
  • The table contains a row for each future dated payment, once the future dated payment matures, i.e. becomes negotiable.
  • Any time a payment is cleared or uncleared, a row is inserted into this table for the payment.


AP_BATCHES_ALL:
  • It contains summary information about invoices you enter in batches if you enable the Batch Control Payables option.
  • There is one row for each batch of invoices you enter.
  • If you enable Batch Control, each invoice must correspond to a record in this table.
  • Your Oracle Payables application uses this information to group together invoices that one person entered in a batch.


AP_CHECKS_ALL:
  • It stores information about payments issued to suppliers or refunds received from suppliers. 
  • There is one row for each payment you issue to a supplier or refund received from a supplier.
  • Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers.
  • Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. Oracle Payables application also stores address information for all payments.


AP_HOLDS_ALL:
  • It contains information about holds that you or your Oracle Payables application place on an invoice.
  • For non-matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice-shipment match.
  • An invoice may have one or more corresponding rows in this table.
  • Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table.


AP_BANK_ACCOUNTS_ALL:
  • It contains information about your bank accounts.
  • There is one row for each bank account you define and each bank account must be affiliated with one bank branch.


AP_BANK_ACCOUNT_USES_ALL:
  • It stores information for the internal and external bank accounts you define in Oracle Payables and Oracle Receivables applications.


AP_CARDS_ALL:
  • It stores information about the corporate credit cards issued to your employees by your corporate credit card providers.


AP_TRIAL_BALANCE:
  • It contains denormalized information about invoices and payments posted to the accrual set of books.



HZ tables in Oracle Receivables

I found this post very useful and found listed on many blogs so wanted to include here also for quick reference. This is not my original work.


HZ_PARTIES:
The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. The primary key for this table is PARTY_ID.
\Few Important Columns are
PARTY_ID: Party identifier
PARTY_NUMBER: Unique identification number for this party
PARTY_NAME: Name of the party
PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.

HZ_PARTY_SITES:
The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.
Few Important Columns are
PARTY_SITE_ID: Party site identifier.
PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
PARTY_SITE_NUMBER: Party site number.
PARTY_SITE_NAME: User-defined name for the site.
ADDRESSEE: Addressee information.

HZ_LOCATIONS:
The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. The primary key for this table is LOCATION_ID.

Few Important Columns are
LOCATION_ID: Unique identifier for this location
COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
ADDRESS1: First line for address
ADDRESS2: Second line for address
ADDRESS3: Third line for address
ADDRESS4: Fourth line for address
CITY: City
POSTAL_CODE: Postal Code
STATE: State
ADDRESS_KEY: Derived key that facilitates fuzzy searches

HZ_CUST_ACCOUNTS:
The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship. Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. The primary key for this table is CUST_ACCOUNT_ID.
Few Important Columns are
CUST_ACCOUNT_ID: Customer account identifier
PARTY_ID: A foreign key to the HZ_PARTY table.
ACCOUNT_NUMBER: Account Number
CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal customers, R for revenue generating external customers.
CUSTOMER_CLASS_CODE: Customer class identifier

HZ_CUST_ACCT_SITES_ALL:
The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.
Few Important Columns are
CUST_ACCT_SITE_ID: Customer site identifier
CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS table
PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
BILL_TO_FLAG: Indicates if this is a Bill-To site.
SHIP_TO_FLAG: Indicates if this is a Ship-To site.
MARKET_FLAG: Indicates if this is a Marketing site.

HZ_CUST_SITE_USES_ALL:
The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary key for this table is SITE_USE_ID.
Few Important Columns areSITE_USE_ID: Site use identifier
CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the HZ_CUST_ACCT_SITES_ALL table
SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and Statements.
PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary customer account site. N for other customer account sites.

HZ_CUSTOMER_PROFILES:
The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.
Few Important Columns are

CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the HZ_CUST_ACCOUNTS table.
STATUS: Indicates whether the profile is active or inactive

HZ_CUST_PROFILE_CLASSES:
The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common across a group of customer accounts. The characteristics specified in this table can be used as default characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.

HZ_PARTY_RELATIONSHIPS:
The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.



Tuesday, June 24, 2014

Top Metalink Notes for Inventory

General
  • Note 123456.1: Latest 11i Applications Recommended Patch List
  • Note 568012.1: FAQ: Inventory Standard Datafixes
  • Note 438787.1: What are the events passed using Custom library and when do they fire?
  • Note 438920.1:  How To Customize Standard Oracle Application Using Custom Library
  • Note 402245.1: "Receiving Transactions Data Collection Script rcv11i_sa.sql".
  • Note 729265.1: How To Process Move Orders Using INV_MOVE_ORDER_PUB.
  • Note 29012.1:   TK Prof Different Useages
  • Note 141278.1: Raw Trace and TKPROF
  • Note 295179.1 - To create MTI records
  • Note 338291.1 - Howto Enable WMS / MSCA Logging
  • Note 191477.1 - INV: Genealogy Prefix or Suffix
  • Note.357535.1 - Sample WMS Open Interface Insert Scripts / Examples howto Insert into Transaction Open Interface
  • Note.259673.1 - "Q: Physical inventory Open Interface or APIs? No.
  • Note.286755.1 - Debug Issues with Inventory Supply/Demand form
  • Note.141149.1 - Important INV profile option explainations
  • Note 398699.1 - Inventory Pending Transactions in MMTT Data Collection Test
  • Note 402574.1 - MOQD MSN mismatch
  • Note 279205.1 - MOQD MMT mismatch
  • Note:401220.1 - Unsuccessful row construction in MSCA for Subinventory field
  • Note 168083.1 - Simple Drop Shipment Flow
  • NOTE:406390.1,364738.1,365454.1,387882.1,420999.1 - Consigned Inventory
  • NOTE:190028.1 - Flow for ATO Configurations
  • Note 402574.1 - Onhand Quantity Mismatch MTL_SERIAL_NUMBERS And 
  • MTL_ONHAND_QUANTITIES_DETAIL
  • Note 223702.1 - Inventory Item Setup Data Collection Tool
  • Note 279205.1 -  Find Mismatch Between MTL_MATERIAL_TRANSACTIONS (MMT) and 
  • MTL_ONHAND_QUANTITIES_DETAIL (MOQD)
  • Note 132629.1 - How to know which row is locked by what user.
  • Note 166650.1 - Working Effectively With Global Customer Support
  • Note 268973.1-To Find Duplicate Records in MMT , MMTT and MTI (Sales Orders/Internal Orders)
  • Note.737442.1-Int/Arc FAQ Work in Process (WIP) Standard Datafixes
  • Note 268974.1-To Review The Stock Picture For An Item in a Given Organization / Subinventory
  • Note 737157.1- FAQ: Costing Standard Datafixes
  • Note 601623.1 - How To Diagnose Issues Related To Inter-Organization Transfers
  • Note 726226.1-INV/WMS/RCV Family Patch Release History / Patchsets / RUPs
Inventory Data Collection
  • Note 205049.1 - 11i : Oracle Inventory General Setup Test
  • Note 223702.1 - Item Diagnostics(Inventory Item Setup Data Collection Tool)
  • Note 148651.1-INV DEBUG: How to get Debug Information for Inventory 
  • Note 299497.1 - RCV debug
  • Note 402245.1 - RCV11i
  • Note 316142.1-Discrete Job Diagnostic script wipjob11i.sql
  • Note 207398.1  - Duplicate WIP Component Issue Check Diagnostic Script
  • Note 276207.1 - Diagnostics : Apps Check
  • Note.133464.1 - OMSE11i
  • Note 150168.1 - FRD Trace
  • Note 373548.1 - FRD Trace in 
  • Note 148651.1,Note 210188.1 - INV/Debug log file
  • Note 290432.1 - Shipping Log File
  • Note 277655.1 - Mobile Trace
  • Note 276211.1 - Shipping Transactions Diagnostic Report (wshord11i.sql)
  • Note 463601.1 - Inventory Organization Data Collection Setup Test
  • Note 378348.1 - Data Collection Scripts for Cost Management
  • Note 206576.1 - Oracle Inventory Period Closing Activity Test
  • Note 398700.1 -Oracle Inventory Open Interface Transactions (MTI) Data
  • Note 463579.1  - Inventory Serial Number Information Setup Test
Cycle Count
  • Note 732843.1 - R12 and 11i Troubleshooting Guide for All Cycle Count  issues: No Data Returned or Cycle Count Not Picking Up Enough Items
  • Note 458727.1 Cycle Count ITAR Template  Note 66634.1 - Cycle Count - How To" to create a new ABC Compile, ABC Class, ABC Assignment Group, and cycle count.
  •  Note 199620.1 - Schedule and Perform Full Cycle Count
  •  Note 76805.1 - Steps to Compile an ABC to Use for Cycle Counting
  •  Note 76804.1 - Steps to Define and Complete a Cycle Count
  •  Note 199639.1 - Approving Cycle Count Corrections
  •  Note 199636.1 - Entering Cycle Counts in Release 11i INV-115
  •  Note 199619.1 - Define Cycle Count and Set Cycle Count Classes
  •  Note 199638.1 - Approving Cycle Counts Held for Approval and Approving Cycle Count Adjustments
  •  Note 76807.1 - How the Cycle Count Process Determines which Items are Due for Cycle Counting
  •  Note 1043388.6 - CYCLE COUNTING COUNTS ITEM MORE THE REQUESTED TIMES
  •  Note 102488.1 - Inventory Current Issues: Cycle Count
  •  Note 416149.1 - 11i FAQ How Cycle Counting Works and how to ensure that cycle counts are scheduled based on setup class and frequency to meet audit requirements
  • Note 342741.1 11i Cycle Counting: Items That Should Be Counted In The Quarter Have Not Come Up For Count 
ROI Documentation
  • Note 559956.1: ROI Features + Transactions supported in various releases
  • Note 437587.1: ROI in R12
  • Note 290489.1- 11510 ROI transactions supported
ROI transactions against PO
  • Note:467037.1 - How Do We Create A Roi Receipt For Po Line That Is Fixed Price Or Rate Type? 
  • Note:368811.1 - 11.5.10 ROI How To Perform Purchase Order Receipt For Lot and Serial Controlled Item ?
  • Note:461928.1 - How Is ASN_TYPE Field In RCV_HEADERS_INTERFACE Used?
  • Note:549609.1 - 11.5.10 ROI How To Create ASN / Perform Receipt For ASN Already Created Via ROI?
  • Note 301624.1 - How to Receive and Deliver Against ASBN by ROI (partially)
  • Note 445274.1 - 11.5.10: Which Interface Tables are Used by ROI to Process ASNs With LPNs?
  • Note 374162.1 - PO Receipt & Deliver for Expense/Inventory destinations, Plain/Lot/Serial/Lot-Serial Items.
ROI transactions against RMA
  • Note 435275.1, 270760.1 & 339835.1 - RMA thru ROI (not supported before 11510)
  • Note 302987.1 - How to create DELIVER transaction against RMA with lot/serial controlled item by ROI
ROI transactions against Internal shipments: ISO & IOT
  • Note 336924.1 - How to Receive Intransit Shipment (Inter-organization Transfer) using ROI - 11.5.10
  • Note:342610.1 - Receive Intransit Shipment (Inter-org transfer) for Lot / Serial Controlled Items via Receiving
  • Note:365200.1 - 11.5.10 ROI How To Perform Receipt On Internal Requisition / Internal Sales Order Via Receiving Open Interface
Generic scripts for various receiving transactions
  • Note:335699.1 - 11.5.10 ROI How to Correct Receiving Transaction with Receiving Open Interface?
  • Note:360340.1 - 11.5.10 ROI How to Return Receiving Transaction with Receiving Open Interface?
  • Note 60340.1  - Return txns thru ROI
  • Note 301281.1: 11.5.10/R12: Which Interface Tables are Used by ROI to Process Serial and Lot Controlled Items?
  • Note 29012.1 - TK Prof Different Useages
  • Note 295179.1 - To create MTI records
  • Note 148651.1 - INV/Debug log file
  • Note 290432.1 - How to Create a Debug File in Shipping Execution
  • Note 150168.1 - FRD Trace
  • Note 438652.1 - FRD Trace R12
  • Note 373548.1 - FRD Trace R12
  • Note 338291.1 - Howto Enable WMS / MSCA Logging?
  • Note 277655.1 - SQL*Trace with WMS-MWA: Mobile Applications (Tracing SQL via Database Trace to allow for TKPROF of results of user actions)
  • Note 190893.1 - Oracle Inventory Support Service Request Instrumentation for Organization Setup
  • Note 251415.1 - SQLTXPLAIN.SQL - Enhanced Explain Plan and related diagnostic info for one SQL statement
  • Note 305404.1 - Oracle Inventory Item Information Data Collection Test
  • Note 223702.1 - Inventory Item Setup Data Collection Tool
  • Note 191477.1 - INV: Genealogy Prefix or Suffix
  • Note.357535.1 - Sample WMS Open Interface Insert Scripts / Examples howto Insert into Transaction Open Interface:
  • Note.259673.1 - "Q: Physical inventory Open Interface or APIs? No.
  • Note.286755.1 - Debug Issues with Inventory Supply/Demand form
  • Note.141149.1 - Important INV profile option explainations
  • Note.395981.1 - Cost Manager Not Processing Because of Error CST_INVALID_INTERORG
  • Note.356771.1 - Cost Manager Not Processing Because of Error CST_INVALID_INTERORG
  • Note.273922.1 - INCTPC -Cannot Close Accounting Period Due to Status = Processing
  • Note.133464.1 - Sales Order Diagnostics patch (omse11i)
  • Note:415537.1 - Physical Inventory Adjustments ends in error
  • Note:221211.1 - Inventory Move Orders diagnostics patch
  • Note:60647.1 - About Physical Inentory
  • Note:135389.1 - Using profile option 'Initialization SQL Statement - Custom'
  • Note:301524.1 - Receiving Transactions Fails With RVTTH-115F / RVTTH-115D Error (Missing Move Order Datafix)
  • Note 387838.1 - Serial Number &TOKEN Does Not Exist For The Given Item Leading/Trailing Spaces
  • Note 413174.1 - Miscellaneous Receipt Getting App-Inv-05011: Cannot Convert Quantities To Ea In Routine UE:XACT_QTY0
  • Note 283740.1 - Inventory Java Versions Data Collection Script
  • Note 282480.1 - Cannot Issue Locator Controlled Item - Locator and Onhand Subinventory Do Not Match
  • Note 279205.1 - Find Mismatch Between MTL_MATERIAL_TRANSACTIONS (MMT) and MTL_ONHAND_QUANTITIES_DETAIL (MOQD)
  • Note 298250.1 - Troubleshooting WMS / MSCA Label Printing
  • Note 377205.1 - Cst_match_date_ Period The Txn Date Is Not Within The Acct Period When Costing Transactions
  • Note 306173.1 - Subinventory Priority While Allocating A Move Order In A Move Order Requisition
  • Note 200066.1 - Transactions are Stuck in MTL_TRANSACTIONS_INTERFACE Table With Invalid Serial Number Status
  • Note 398700.1 - Inventory Open Interface Transactions (MTI) Data Collection Test
  • Note 239627.1 - How To Obtain the CSE/CSI Log and Debug Files For Your Oracle Support Analyst
  • Note 186472.1 - Diagnostic Scripts: 11i - Hanging SQL - Find the Statement Causing Process to Hang
  • Note 249669.1 - How To Collect Apache and Jserv Debugging Details For Applications 11i
  • Note 461611.1 - Tag Counts In Physical Inventory Do Not Allow Duplicate Serials Across Items
  • Note 317412.1 - How To Override Cross Docking Suggestion
  • Note 375282.1 - There Must Be A Valid Blanket Purchase Agreement In Effect For The Item You Wish To Transact
  • Note 336778.1 - Consignment Transactions Error With Must Be A Valid Blanket Purchase Agreement
  • Note 416678.1 - R12 : Deferred COGS account
  • Note 120183.1 - How To Investigate Mtl_demand_interface Errors
  • Note 249738.1 - 11i - Intercompany Invoicing Does Not Work Properly
  • Note 316365.1 - Oracle Applications Release 11.5.10.2 Maintenance Pack Installation Instructions
  • Note 462174.1 - Problems With View On-Hand Quantities Form
  • Note 402245.1 - Receiving Transactions Data Collection Script rcv11i_sa.sql
  • Note 277655.1 - SQL*Trace with WMS-MWA: Mobile Applications (Tracing SQL via Database Trace to allow for TKPROF of results of user actions)
  • Note 398700.1 - Inventory Open Interface Transactions (MTI) Data Collection Test.
  • Note 398699.1 - Inventory Pending Transactions in MMTT Data Collection Test
  • NOTE:406390.1,364738.1,365454.1,387882.1,420999.1 - Consigned Inventory
  • NOTE:190028.1 - Flow for ATO Configurations
  • Note 402245.1 - RCV11i
  • Note 136098.1 - User_id, responsibility_id, RESPONSIBILITY_APPLICATION_ID
  • Note 400088.1 - Distribution_account_id
  • Note 353345.1 - Consolidate inventory onhand
  • Note 223702.1 - Item Diagnostics
  • Note 166923.1 - COMMON ISSUES FOR RECORDS STUCK IN INVENTORY PENDING TRANSACTIONS
  • Note 404336.1 - How Many Processes Should Be Set For The Receiving Transaction Manager (RTM)?
  • Note 168083.1 - Simple Drop Shipment Flow
  • Note 276207.1 - Diagnostics : Apps Check
  • Note 402574.1 - MOQD MSN mismatch
  • Note 279205.1 - MOQD MMT mismatch
  • Note:401220.1 - Unsuccessful row construction in MSCA for Subinventory field
  • Note 337325.1 - Intercompany Transactions
  • Note 336129.1 - Intercompany Invoicing Overview
  • Note 417875.1 - Serial Number FAQ
  • Note 332697.1 - 11.5.10 RCVRCERC - Internal Sales Order Receipts Fail with 'rvtptcontrol failed' and 'WMSINB-22192:Lot/Serial
  • Note 376505.1 - Unable to Receive Inter-Organization Transfers "rvtptcontrol failed"
  • Note 141118.1 - How To Relink Forms Library Files Using Adadmin
  • Note 414943.1 - R: Xml Output Getting Emsg: Signal 11-Glibcdetected Corrupted Double-Linked List
  • Note 315741.1 - Basic Setup To Achieve Skip Lot Inspection Functionality In Receiving Quality Inspection
  • Note 405632.1 - Item Definition with Chargeable Subcontracting
  • Note 273044.1 - FLEXFIELD: Validate on Server Profile Option
  • Note 363377.1 - WMSINB-23178 - Lot/Serial Validation Fail Error For Receipt
  • Note 303365.1 - POXRQSDD: Internal Requisitions/Deliveries Discrepancy Report Not Displaying Correct Information
  • Note 303088.1 - 11.5.10 - Only First Line is Split When Performing Partial Receipt for Multiple Line RMA
  • Note 391554.1 - Oracle Application Framework Documentation Resources
  • Note 11.1 - "Demo It To Oracle" (DITO) - CamStudio Help
  • Note 203238.1 - Using Parallel Execution
  • Note 605248.1 - Inventory Suite Patching Strategy For One-Off Requests and RUPs
  • Note 294670.1 - Oracle Mobile Applications (MSCA/WMS) Graphical User Interface
  • Note 277332.1 - Receipts For Drop Ship Purchase Orders Fail With Rvtii-060 - Subroutine inltpu() returned error
  • Note 372432.1 - How to Trace the Receiving Transactions
  • Note 66634.1 - Cycle Count - How To" to create a new ABC Compile, ABC Class, ABC Assignment Group, and cycle count.
  • Note 199620.1 - Schedule and Perform Full Cycle Count
  • Note 76805.1 - Steps to Compile an ABC to Use for Cycle Counting
  • Note 76804.1 - Steps to Define and Complete a Cycle Count
  • Note 199639.1 - Approving Cycle Count Corrections
  • Note 199636.1 - Entering Cycle Counts in Release 11i INV-115
  • Note 199619.1 - Define Cycle Count and Set Cycle Count Classes
  • Note 199638.1 - Approving Cycle Counts Held for Approval and Approving Cycle Count Adjustments
  • Note 76807.1 - How the Cycle Count Process Determines which Items are Due for Cycle Counting
  • Note 1043388.6 - CYCLE COUNTING COUNTS ITEM MORE THE REQUESTED TIMES
  • Note 102488.1 - Inventory Current Issues: Cycle Count
  • Note 416149.1 - 11i FAQ How Cycle Counting Works and how to ensure that cycle counts are scheduled based on setup class and frequency to meet audit requirements
  • Note 406312.1 - ISO cycle
  • Note 301372.1 - Concurrent program database trace with Binds and waits
  • Note 420787.1 - Oracle Applications Multiple Organizations Access Control for Custom Code
Autoconfig
  • 218089.1  Autoconfig FAQ
  • 165195.1  Using AutoConfig to Manage System Configurations with Oracle Applications 11i
  • 387859.1  Using AutoConfig to Manage System Configurations in Oracle Applications Release 12 
  • 270519.1  Customizing an AutoConfig Environment
  • 364927.1 How To Run Autoconfig On Database Tier (DB-Tier)
  • 341322.1 How to change the hostname of an Applications Tier using AutoConfig
  • 338003.1 How to change the hostname and/or port of the Database Tier using AutoConfig
  • 315674.1 How To Verify if Autoconfig is Enabled on 11.5.x
  • 391406.1 How to get a clean Autoconfig Environment

Monday, June 23, 2014

Item Orderability Rules in Order Management

Item orderability is a new feature that Oracle introduced in order management release 12.1. Based on these rules, a user can restrict items that can be entered on sales order by manual or EDI, XML, Etc.

Setups

  • Define an Orderability Rule 
  • Set the Profile OM: Use Materialized View for Items LoV 
  • Run Refresh Order Management Materialized Views 

Step 1) Define an Orderability Rule

Navigate to OM>Setup>Rules>Item Orderability

Item Orderability Rules in Order Management
Item Orderability Rules in Order Management

















# Select an Operating Unit
# you can set this rule at item level or Category Level
# Check Enabled to enable this rule
# If Generally available is ticked, then item is available to all except the criteria mentioned in rule.
and if Generally Available is not ticked then item is restricted to all, except the criteria mentioned in the rule.

Here in above example, the item is available to all customers except to "w_Enterprise Builders"

# Rule Level

There are 11 Levels at which you can set the item Orderability Rule

These are

  1. CUSTOMER 
  2. CUSTOMER CLASS
  3. CUSTOMER CATEGORY 
  4. END CUSTOMER 
  5. ORDER TYPE 
  6. SALES CHANNEL 
  7. SALES PERSON 
  8. SHIP TO LOCATION 
  9. BILL TO LOCATION 
  10. DELIVER TO LOCATION 
  11. REGIONS


Step 2) Set the Profile OM: Use Materialized View for Items LoV 

If this profile option is set to "Yes", then sales order form will display the items which order not restricted by Item Orderability Rules

If this is set to NO, then system display all items but give a note when saving the sales order.

Its better to set this profile option as Yes

Step 3) Run "Refresh Order Management Materialized Views"

If the profile OM: Use Materialized View for Items LoV is set to Yes, the concurrent request "Refresh Order Management Materialized Views" needs to be run in order to refresh OM materialized views OE_ITEMS_ORD_MV, OE_ITEMS_MV.

The concurrent request needs to be run after changes are made to the item master, cross reference, Customer Items or to the item orderability rules, to ensure that the item list of  values displays the latest items on the order entry form, based on orderability rules.

Test Case

Let try to enter a order for item Steel123 based on above rule it should restrict us



































Thanks
Mandeep

------------------------------------------------------------------------------------------------------------------------------------------------------------------
Please share this knowledge with your friends and subscribe at our blog for more updates. If you have any queries, please participate in our Forum or by leaving your comments below.
------------------------------------------------------------------------------------------------------------------------------------------------------------------


Friday, June 20, 2014

Kanban

Definition : Kanban literally means signboard or billboard in Japanese. It is first implemented at Toyota in 1953 and as per Wikipedia "Kanban maintain inventory levels; a signal is sent to produce and deliver a new shipment as material is consumed. These signals are tracked through the replenishment cycle and bring extraordinary visibility to suppliers and buyers"

As per Oracle White Paper on Kanban, "Kanban Cards are used in a Pull based System. A pull based system is one where inventory items for a particular part or assembly area are replenished as soon as they are needed. When the items are needed, Kanban cards in Oracle Inventory change their Supply Status to Empty, and Inventory generates the Kanban replenishment request automatically."

Kanban Source Type
  • Inter Org:  This triggers an internal organization and item is sourced from other organization. 
  • Supplier :  This triggers a Purchase requisition and insert records into PO_REQUISITIONS_INTERFACE_ALL
  • Intra Org : This triggers a Replenishment Move order for Subinventory Transfer within an Organization. Value of REFERENCE_ID in MTL_TXN_REQUEST_LINES gives the KANBAN_CARD_ID in MTL_KANBAN_CARDS
  • Production : This triggers creation of a WIP Job for make item and records are inserted into WIP_JOB_SCHEDULE_INTERFACE
Cycle of a Kanban Card

New > Empty>Wait>In Process> Full


Setups and Test Cycle


Step1) Create a item "Kanban Item" using Purchased Item template













Step2) Navigate to Inventory > Kanban> Pull Sequences >New





Source Tab
# Specify the item
# Take source type as Supplier

Kanban Tab
# Take Calculate as Kanban Size
# Specify a Kanban size
# Take Number of cards as 2

















Click on Open will show you all the information


















Save your work and click on Generate Cards


















Click on Yes when Prompted for printing. Now click on Cards button









You can see the Card Status is Active and Supply Status is "New". Click on "Replenish" button.



















That will change the Supply Status as Empty and also inserts the records in PO_REQUISITIONS_INTERFACE_ALL table.

Step 3) To Import this record, run Requisition import from Purchasing responsibility














You can check the output of this request so see number of records created

Now navigate to Requisition summary form and find the requisition with item number





Card status here still be empty

Step 4) Now AutoCreate Requisition in PO and approve the PO











Check the card status now. It will be In Process









Step 5) Now create a receipt and deliver products in Stores subinventory














Now check Kanban Cards










Supply Status is Full Now

Thanks
Mandeep


Disclaimer

All content provided on this blog is for informational purposes only. The owners of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. All trademarks, trade names, service marks, copyrighted work and logos referenced here - belong to their respective owners.If you want to report any content that is violating copyright law and you want us to remove it, please contact us.

Featured Post

Complete Oracle P2P cycle