OPM Inventory

Oracle® Process Manufacturing Inventory Technical Reference Manual RELEASE 11i December 1999 OPM Inventory Technical Reference Manual Release 11i To order this book, ask for Part No. A81213-01 Copyright © 1994, 1999. Oracle Corporation. All rights reserved. Major Contributor: Michele-Andrea Fields Contributors: Christine McLean, Joe DiIorio This Technical Reference Manual (TRM) in any form, software or printed matter, contains proprietary information of Oracle Corporation; it is provided under an Oracle Corporation agreement containing restrictions on use and disclosure and is also protected by copyright, patent, and other intellectual property law. Restrictions applicable to this TRM include, but are not limited to: (a) exercising either the same degree of care to safeguard the confidentiality of this TRM as you exercise to safeguard the confidentiality of your own most important Confidential Information or a reasonable degree of care, whichever is greater; (b) maintaining agreements with your employees and agents that protect the Confidential Information of third parties such as Oracle Corporation and instructing such employees and agents of these requirements for this TRM; (c) restricting disclosure of this TRM to those of your employees who have a ”need to know” consistent with the purposes for which this TRM was disclosed to you; (d) maintaining this TRM at all times on your premises; (e) not removing or destroying any proprietary or confidential legends or markings placed upon this TRM in whatever form, software or printed matter; and (f) not reproducing or transmitting this TRM in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation. You should not use this TRM in any form, software or printed matter, to create software that performs the same or similar functions as any Oracle Corporation products. Program Documentation is licensed for use solely to support the deployment of the Programs and not for any other purpose. The information in this TRM is subject to change without notice. If you find any problems in the TRM in any form, software or printed matter, please report them to us in writing. Oracle Corporation does not warrant that this TRM is error-free. This TRM is provided to customer ”as-is” with no warranty of any kind. This TRM does not constitute Documentation as that term is defined in Oracle’s agreements. Reverse engineering of the Programs (software and documentation) associated with this TRM are prohibited. The Programs associated with this TRM are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be licensee’s responsibility to take all appropriate fail-safe, back-up, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracle disclaims liability for any damages caused by such use of the Programs. Restricted Rights Legend This TRM and the Programs associated with this TRM delivered subject to the DOD FAR Supplement are ’commercial computer software’ and use, duplication and disclosure of the TRM and the Programs associated with this TRM shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, this TRM and the Programs associated with this TRM delivered subject to the Federal Acquisition Regulations are ’restricted computer software’ and use, duplication and disclosure of the TRM and the Programs associated with this TRM shall be subject to the restrictions in FAR 52.227-14, Rights in Data -- General, including Alternate III (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065. Oracle is a registered trademark, and CASE*Exchange, Enabling the Information Age, Hyper*SQL, NLS*Workbench, Oracle7, Oracle8, Oracle 8i, Oracle Access, Oracle Application Object Library, Oracle Discoverer, Oracle Financials, Oracle Quality, Oracle Web Customers, Oracle Web Employees, Oracle Work in Process, Oracle Workflow, PL/SQL, Pro*Ada, Pro*C, Pro*COBOL, Pro*FORTRAN, Pro*Pascal, Pro*PL/I, SmartClient, SQL*Connect, SQL*Forms, SQL*Loader, SQL*Menu, SQL*Net, SQL*Plus, and SQL*Report are trademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of their respective owners. CAUTION This Technical Reference Manual in any form -- software or printed matter -- contains proprietary, confidential information that is the exclusive property of Oracle Corporation. If you do not have a valid contract with Oracle for the use of this Technical Reference Manual or have not signed a non-disclosure agreement with Oracle covering this Technical Reference Manual, then you received this document in an unauthorized manner and are not legally entitled to possess or read it. Use, duplication, and disclosure are subject to restrictions stated in your contract with Oracle Corporation. Contents 1 Introduction Overview .............................................................................................................................................. Finding the Latest Information ........................................................................................................ Audience............................................................................................................................................... How This Manual is Organized....................................................................................................... How to Use This Manual................................................................................................................... How Not To Use This Manual.......................................................................................................... About Oracle Application Object Library ..................................................................................... A Few Words About Terminology ................................................................................................... Other Information Sources ............................................................................................................... About Oracle...................................................................................................................................... Thank You .......................................................................................................................................... 1-2 1-2 1-3 1-3 1-3 1-4 1-5 1-5 1-7 1-11 1-11 2 High-Level Design Overview of High-Level Design...................................................................................................... Summary Database Diagram ...................................................................................................... Database Diagrams....................................................................................................................... Table Lists ...................................................................................................................................... View Lists ...................................................................................................................................... Module List.................................................................................................................................... Summary Database Diagram ........................................................................................................... Database Diagramming Conventions ...................................................................................... Database Diagrams............................................................................................................................. How to Use These Database Diagrams ..................................................................................... 2-2 2-2 2-2 2-2 2-3 2-4 2-5 2-6 2-7 2-7 Oracle Proprietary, Confidential Information--Use Restricted by Contract v Inventory Close ................................................................................................................................... Inventory Items ................................................................................................................................. Inventory Transactions..................................................................................................................... Physical Inventory ............................................................................................................................ Public Table List................................................................................................................................ Public View List ................................................................................................................................ Inquiry View List .............................................................................................................................. Multilingual View List .................................................................................................................... Module List ............................................................................................................................... ......... Forms ............................................................................................................................................ Reports.......................................................................................................................................... 2-9 2-10 2-11 2-12 2-13 2-18 2-20 2-20 2-21 2-22 2-24 3 Detailed Design Overview of Detailed Design........................................................................................................... Table and View Definitions......................................................................................................... Table and View Definitions ........................................................................................................... Foreign Keys .................................................................................................................................. QuickCodes Columns .................................................................................................................. Column Descriptions.................................................................................................................... Indexes............................................................................................................................................ Sequences ....................................................................................................................................... Database Triggers ......................................................................................................................... View Derivation ............................................................................................................................ BISOPM_ONHANDSALE_SUM................................................................................................ BIS_OPM_PROD_SUM................................................................................................................ CM_CLDR_DTL............................................................................................................................ CM_CLDR_HDR......................................................................................................................... CM_MTHD_MST........................................................................................................................ CM_WHSE_ASC......................................................................................................................... CR_SQDT_CLS............................................................................................................................ FM_FORM_EFF........................................................................................................................... FM_FORM_MST ......................................................................................................................... FM_MATL_DTL ......................................................................................................................... FND_DUAL ................................................................................................................................. GL_CLDR_HDR.......................................................................................................................... Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-2 3-2 3-2 3-3 3-3 3-3 3-5 3-6 3-6 3-6 3-7 3-8 3-9 3-10 3-12 3-13 3-14 3-15 3-17 3-19 3-21 3-22 vi GL_ITEM_CST ............................................................................................................................ GL_PLCY_MST........................................................................................................................... IC_ADJS_JNL .............................................................................................................................. IC_ALLC_CLS............................................................................................................................. IC_ALOT_PRM ........................................................................................................................... IC_CLDR_DTL............................................................................................................................ IC_CLDR_HDR........................................................................................................................... IC_COMD_CDS .......................................................................................................................... IC_COST_CLS ............................................................................................................................. IC_CRUL_CLS ............................................................................................................................ IC_CTMS_CLS ............................................................................................................................ IC_CYCL_ADT ........................................................................................................................... IC_CYCL_DTL ............................................................................................................................ IC_CYCL_ERR ............................................................................................................................ IC_CYCL_HDR ........................................................................................................................... IC_CYCL_MSC ........................................................................................................................... IC_FRGT_CLS ............................................................................................................................. IC_GLED_CLS ............................................................................................................................ IC_INVN_CLS............................................................................................................................. IC_INVN_TYP ............................................................................................................................ IC_ITEM_CDT ............................................................................................................................ IC_ITEM_CNV............................................................................................................................ IC_ITEM_CPG............................................................................................................................. IC_ITEM_HIERARCHY ............................................................................................................ IC_ITEM_MST............................................................................................................................. IC_ITEM_WHS ........................................................................................................................... IC_JRNL_MST............................................................................................................................. IC_LOCT_INV ............................................................................................................................ IC_LOCT_MST............................................................................................................................ IC_LOTS_CPG ............................................................................................................................ IC_LOTS_MST ............................................................................................................................ IC_LOTS_STS .............................................................................................................................. IC_PERD_BAL ............................................................................................................................ IC_PHYS_CNT............................................................................................................................ IC_PHYS_ERR............................................................................................................................. 3-23 3-25 3-28 3-31 3-32 3-33 3-34 3-35 3-36 3-38 3-39 3-41 3-43 3-44 3-45 3-47 3-49 3-51 3-52 3-54 3-56 3-57 3-58 3-59 3-60 3-64 3-65 3-66 3-68 3-70 3-71 3-73 3-74 3-76 3-78 Oracle Proprietary, Confidential Information--Use Restricted by Contract vii IC_PKGS_MST ............................................................................................................................ IC_PLNT_INV ............................................................................................................................. IC_PRCE_CLS ............................................................................................................................. IC_PRCH_CLS ............................................................................................................................ IC_PURG_PRM ........................................................................................................................... IC_RANK_MST........................................................................................................................... IC_SALE_CLS.............................................................................................................................. IC_SHIP_CLS............................................................................................................................... IC_STAT_RPT ............................................................................................................................. IC_STOR_CLS ............................................................................................................................. IC_SUMM_INV........................................................................................................................... IC_TAXN_ASC ........................................................................................................................... IC_TAXN_CLS ............................................................................................................................ IC_TEXT_HDR .......................................................................................................................... IC_TEXT_TBL_TL ..................................................................................................................... IC_TRAN_ARC ......................................................................................................................... IC_TRAN_CMP......................................................................................................................... IC_TRAN_PND......................................................................................................................... IC_WHSE_INV.......................................................................................................................... IC_WHSE_MST......................................................................................................................... IC_WHSE_REL.......................................................................................................................... IC_WHSE_STS........................................................................................................................... IN_ADDR_MST ........................................................................................................................ IN_CTRY_MST.......................................................................................................................... IN_ITEM_MST .......................................................................................................................... IN_REGN_MST......................................................................................................................... IN_STAT_CTL........................................................................................................................... IN_STAT_DTL........................................................................................................................... IN_STAT_HDR ......................................................................................................................... IN_STAT_MSG.......................................................................................................................... IN_TEXT_HDR ......................................................................................................................... IN_TEXT_TBL_TL .................................................................................................................... IN_TOFD_MST ......................................................................................................................... OP_BILL_LAD .......................................................................................................................... OP_CUST_MST......................................................................................................................... 3-79 3-80 3-82 3-84 3-86 3-88 3-89 3-91 3-93 3-94 3-96 3-98 3-99 3-100 3-101 3-102 3-104 3-106 3-109 3-112 3-114 3-116 3-117 3-118 3-119 3-120 3-121 3-122 3-124 3-125 3-126 3-127 3-128 3-129 3-132 Oracle Proprietary, Confidential Information--Use Restricted by Contract viii OP_ORDR_DTL ........................................................................................................................ OP_ORDR_HDR....................................................................................................................... OP_PORT_MST ........................................................................................................................ PM_BTCH_HDR....................................................................................................................... PM_MATL_DTL ....................................................................................................................... PO_ORDR_DTL ........................................................................................................................ PO_ORDR_HDR....................................................................................................................... PO_RECV_DTL......................................................................................................................... PO_RECV_HDR........................................................................................................................ PO_RTRN_DTL ........................................................................................................................ PO_VEND_CLS ........................................................................................................................ PO_VEND_MST ....................................................................................................................... PS_PLNG_CLS .......................................................................................................................... QC_ACTN_MST ....................................................................................................................... QC_GRAD_MST....................................................................................................................... QC_HRES_MST ........................................................................................................................ SY_ADDR_MST ........................................................................................................................ SY_DOCS_MST......................................................................................................................... SY_DOCS_SEQ ......................................................................................................................... SY_ORGN_MST........................................................................................................................ SY_ORGN_USR ........................................................................................................................ SY_REAS_CDS .......................................................................................................................... SY_UOMS_MST........................................................................................................................ SY_UOMS_TYP......................................................................................................................... SY_WF_ITEM_ROLES ............................................................................................................. TX_TLOC_CDS ......................................................................................................................... 3-137 3-145 3-150 3-151 3-154 3-157 3-160 3-163 3-166 3-169 3-172 3-173 3-177 3-179 3-181 3-183 3-185 3-186 3-187 3-189 3-191 3-192 3-193 3-194 3-195 3-196 Oracle Proprietary, Confidential Information--Use Restricted by Contract ix Oracle Proprietary, Confidential Information--Use Restricted by Contract x Send Us Your Comments Oracle Process Manufacturing Inventory Technical Reference Manual Part No. A81213-01 Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this publication. Your input is an important part of the information used for revision. Did you find any errors? Is the information clearly presented? Do you need more information? If so, where? Are the examples correct? Do you need more examples? What features did you like most about this manual?






If you find any errors or have any other suggestions for improvement, please indicate the chapter, section, and page number (if available). You can send comments to us in the following ways: FAX: 650-506-7200 Attn: Oracle Process Manufacturing Postal service: Oracle Corporation Oracle Process Manufacturing 500 Oracle Parkway Redwood City, CA 94065 U.S.A. If you would like a reply, please give your name, address, and telephone number below. If you have problems with the software, please contact your local Oracle Support Services. xi xii 1 Introduction The Oracle Process Manufacturing Inventory Technical Reference Manual provides the information you need to understand the underlying structure of Oracle Process Manufacturing (OPM). After reading this manual, you should be able to convert your existing applications data, integrate your existing applications with OPM, and write custom reports for OPM, as well as read data that you need to perform other tasks. This chapter introduces you to the Oracle Process Manufacturing Inventory Technical Reference Manual, and explains how to use it. Oracle Proprietary, Confidential Information--Use Restricted by Contract Introduction 1-1 Overview Overview At Oracle, we design and build applications using Oracle Designer, our systems design technology that provides a complete environment to support developers through all stages of a systems life cycle. Because we use a repository-based design toolset, all the information regarding the underlying structure and processing of our applications is available to us online. Using Oracle Designer, we can present this information to you in the form of a technical reference manual. This Oracle Process Manufacturing Inventory Technical Reference Manual contains detailed, up-to-date information about the underlying structure of OPM. As we design and build new releases of OPM, we update our Oracle Designer repository to reflect our enhancements. As a result, we can always provide you with an Oracle Process Manufacturing Inventory Technical Reference Manual that contains the latest technical information as of the publication date. Note that after the publication date we may have added new indexes to OPM to improve performance. About this Manual This manual describes the Oracle Applications Release 11i data model, as used by OPM; it discusses the database we include with a fresh install of Oracle Applications Release 11i. If you have not yet upgraded to Release 11, your database may differ from the database we document in this book. If you have upgraded from a previous release, you might find it helpful to use this manual with the appropriate Oracle Applications Product Update Notes manual. The product update notes list database changes and seed data changes in OPM between releases. The Oracle Applications Product Update Notes Release 11 manual describes the changes between release 10.7 and release 11, and the Oracle Applications Product Update Notes Release 11i manual describes the changes between release 11 and release 11i. You can contact your Oracle representative to confirm that you have the latest technical information for OPM. You can also use OracleMetaLink which is accessible through Oracle’s Support Web Center ( http://www.oracle.com/support/elec_sup ). Finding the Latest Information The Oracle Process Manufacturing Inventory Technical Reference Manual contains the latest information as of the publication date. For the latest information we encourage you to use OracleMetaLink which is accessible through Oracle’s Support Web Center ( http://www.oracle.com/support/elec_sup ). Oracle Proprietary, Confidential Information--Use Restricted by Contract 1-2 OPM Inventory Technical Reference Manual How to Use This Manual Audience The Oracle Process Manufacturing Inventory Technical Reference Manual provides useful guidance and assistance to: Technical End Users Consultants Systems Analysts System Administrators Other MIS professionals






This manual assumes that you have a basic understanding of structured analysis and design, and of relational databases. It also assumes that you are familiar with Oracle Application Object Library and OPM. If you are not familiar with the above products, we suggest that you attend one or more of the training classes available through Oracle Education (see: Other Information Sources). How This Manual is Organized This manual contains two major sections, High-Level Design and Detailed Design. High-Level Design This section, Chapter 2, contains database diagrams, and lists each database table and view that OPM uses. This chapter also has a list of modules. Detailed Design This section, Chapter 3, contains a detailed description of the OPM database design, including information about each database table and view you might need for your custom reporting or other data requirements. How to Use This Manual The Oracle Process Manufacturing Inventory Technical Reference Manual is a single, centralized source for all the information you need to know about the underlying structure and processing of OPM. For example, you can use this manual when you need to: Convert existing application data Integrate OPM with your other applications systems Oracle Proprietary, Confidential Information--Use Restricted by Contract Introduction 1-3 How Not To Use This Manual Write custom reports Define alerts against Oracle Applications tables Configure your Oracle Self-Service Web Applications Create views for decision support queries using query tools Create business views for Oracle Discoverer You need not read this manual cover to cover. Use the table of contents and index to quickly locate the information you need. How Not To Use This Manual Do not use this manual to plan modifications You should not use this manual to plan modifications to OPM. Modifying OPM limits your ability to upgrade to future releases of OPM. In addition, it interferes with our ability to give you the high-quality support you deserve. We have constructed OPM so that you can customize it to fit your needs without programming, and you can integrate it with your existing applications through interface tables. However, should you require program modifications, you should contact our support team (see: Other Information Sources). They can put you in touch with Oracle Services, the professional consulting organization of Oracle. Their team of experienced applications professionals can make the modifications you need while ensuring upward compatibility with future product releases. Oracle Proprietary, Confidential Information--Use Restricted by Contract 1-4 OPM Inventory Technical Reference Manual




Do not write data into non-interface tables Oracle reserves the right to change the structure of Oracle Applications tables, and to change the meaning of, add, or delete lookup codes and data in future releases. Do not write data directly into or change data in non-interface tables using SQL*Plus or other programming tools because you risk corrupting your database and interfering with our ability to support you. Moreover, this version of the Oracle Process Manufacturing Inventory Technical Reference Manual does not contain complete information about the dependencies between OPM tables. Therefore, you should write data into only those tables we identify as interface tables. If you write data into other non-interface tables, you risk violating your data integrity since you might not fulfill all the data dependencies in OPM. A Few Words About Terminology You are responsible for the support and upgrade of the logic within the procedures that you write, which may be affected by changes between releases of Oracle Applications. Do not rely on upward compatibility of the data model Oracle reserves the right to change the structure of OPM tables, and to change the meaning of, add, or delete lookup codes and other data in future releases. We do not guarantee the upward compatibility of the OPM data model. For example, if you write a report that identifies concurrent requests that end in Error status by selecting directly from Oracle Application Object Library tables, we do not guarantee that your report will work properly after an upgrade. About Oracle Application Object Library The Oracle Process Manufacturing Inventory Technical Reference Manual may contain references to tables that belong to Oracle Application Object Library. Oracle Application Object Library is a collection of pre-built application components and facilities for building Oracle Applications and extensions to Oracle Applications. Oracle Application Coding Standards use the Oracle Application Object Library and contains shared components including but not limited to -- forms, subroutines, concurrent programs and reports, database tables and objects, messages, menus, responsibilities, flexfield definitions and online help. Attention: Oracle does not support any customization of Oracle Application Object Library tables or modules, not even by Oracle consultants. (Oracle Application Object Library tables generally have names beginning with FND_%.) Accordingly, this manual does not contain detailed information about most Oracle Application Object Library tables used by OPM. A Few Words About Terminology The following list provides you with definitions for terms that we use throughout this manual: Relationship A relationship describes any significant way in which two tables may be associated. For example, rows in the Journal Headers table may have a one-to-many relationship with rows in the Journal Lines table. Oracle Proprietary, Confidential Information--Use Restricted by Contract Introduction 1-5 A Few Words About Terminology Database Diagram A database diagram is a graphic representation of application tables and the relationships between them. Summary Database Diagram A summary database diagram shows the most important application tables and the relationships between them. It omits tables and relationships that contribute little to the understanding of the application data model. Typically, a summary database diagram shows tables that contain key reference and transaction data. Module A module is a program or procedure that implements one or more business functions, or parts of a business function, within an application. Modules include forms, concurrent programs and reports, and subroutines. Application Building Block An application building block is a set of tables and modules (forms, reports, and concurrent programs) that implement closely-related database objects and their associated processing. Said another way, an application building block is a logical unit of an application. QuickCodes QuickCodes let you define general purpose, static lists of values for window fields. QuickCodes allow you to base your program logic on lookup codes while displaying user-friendly names in a list of values window. QuickCodes simplify name and language changes by letting you change the names your end users see, while the codes in your underlying programs remain the same. Form A form is a module comprised of closely related windows that are used together to perform a task. For example, the Enter Journals form in Oracle General Ledger includes the Enter Journals window, the Batch window, and the More Actions window among others. The Enter Journals window is the main window, and from it, you can use buttons to navigate to other windows in the form. The form name usually corresponds to the main window in the form, and is frequently a window you open directly from the Navigator. Oracle Proprietary, Confidential Information--Use Restricted by Contract 1-6 OPM Inventory Technical Reference Manual Other Information Sources Other Information Sources There are additional information sources, including other documentation, training and support services, that you can use to increase your knowledge and understanding of Oracle Designer, Oracle Application Object Library, and OPM. We want to make these products easy for you and your staff to understand and use. Oracle Designer Online Documentation The online help for Oracle Designer describes how you can use Oracle Designer for your development needs. Oracle Applications Developer’s Guide This guide contains the coding standards followed by the Oracle Applications development staff. It describes the Oracle Application Object Library components needed to implement the Oracle Applications user interface described in the Oracle Applications User Interface Standards. It also provides information to help you build your custom Developer forms so that they integrate with Oracle Applications. Oracle Applications User Interface Standards This manual contains the user interface (UI) standards followed by the Oracle Applications development staff. It describes the UI for the Oracle Applications products and how to apply this UI to the design of an application built using Oracle Forms 6. Oracle Process Manufacturing System Administration User’s Guide Your user guide provides you with all the information you need to use your Release 11i OPM application. Each user guide is organized for fast, easy access to detailed information in a function- and task-oriented organization. Oracle Self-Service Web Applications Online Documentation This documentation describes how Oracle Self-Service Web Applications enable companies to provide a self-service and secure Web interface for employees, customers, and suppliers. Employees can change their personal status, submit expense reports, or request supplies. Customers can check on their orders, and suppliers can share production schedules with their trading partners. This documentation is available in HTML only. Oracle Proprietary, Confidential Information--Use Restricted by Contract Introduction 1-7 Other Information Sources Oracle Applications Flexfields Guide This guide provides flexfields planning, setup and reference information for the OPM implementation team, as well as for users responsible for the ongoing maintenance of Oracle Applications product data. This manual also provides information on creating custom reports on flexfields data. Oracle Workflow Guide This manual explains how to define new workflow business processes as well as customize existing Oracle Applications-embedded workflow processes. You also use this guide to complete the setup steps necessary for any Oracle Applications product that includes workflow-enabled processes. Oracle Alert User Guide This manual explains how to define periodic and event alerts to monitor the status of your Oracle Applications data. Multiple Reporting Currencies in Oracle Applications If you use the Multiple Reporting Currencies feature to report and maintain accounting records in more than one currency, use this manual before implementing OPM. This manual details additional steps and setup considerations for implementing OPM with this feature. Multiple Organizations in Oracle Applications If you use the Oracle Applications Multiple Organization Support feature to use multiple sets of books for one OPM installation, use this guide to learn about setting up and using OPM with this feature. This book describes the Oracle Applications organization model, which defines business units and the relationships between them in an arbitrarily complex enterprise. Functional and technical overviews of multiple organizations are presented, as well as information about how to set up and implement this feature set in the relevant Oracle Applications products. Oracle Applications Messages Manual The Oracle Applications Messages Manual contains the text of numbered error messages in Oracle Applications. (Oracle Applications messages begin with the prefix ”APP-”.) It also provides information on the actions you take if you get a message. Note: This manual is available only in HTML format. Oracle Proprietary, Confidential Information--Use Restricted by Contract 1-8 OPM Inventory Technical Reference Manual Other Information Sources Installation and System Administration Oracle Applications Installation Release Notes This manual contains a road map to the components of the release, including instructions about where to access the Release 11i documentation set. Oracle Applications Concepts Designed to be the first book the user reads to prepare for an installation of Oracle Applications. It explains the technology stack, architecture, features and terminology for Oracle Applications Release 11i. This book also introduces the concepts behind and major uses of Applications-wide features such as MRC, BIS, languages and character sets (NLS, MLS), BIS, Self-Service Web Applications and so on. Installing Oracle Applications Describes the One-Hour Install process, the method by which Release 11i will be installed. This manual includes all how-to steps, screen shots and information about Applications-wide post-install tasks. Using the AD Utilities This manual contains how-to steps, screen shots and other information required to run the various AD utilities such as AutoInstall, AutoPatch, AD Administration, AD Controller, Relink and so on. It also contains information about when and why you should use these utilities. Upgrading Oracle Applications This manual contains all the product specific pre and post-upgrade steps that are required to upgrade products from Release 10.7 (NCA, SC and character-mode) or Release 11 of Oracle Applications. This manual also contains an overview chapter that describes all the tasks necessary to prepare and complete a upgrade of Oracle Applications. Oracle Applications System Administrator’s Guide This manual provides planning and reference information for the Oracle Applications System Administrator. It contains information on how to define security, customize menus and manage concurrent processing. Oracle Proprietary, Confidential Information--Use Restricted by Contract Introduction 1-9 Other Information Sources Oracle Applications Product Update Notes This book contains a summary of each new feature we added since Release 11, as well as information about database changes and seed data changes that may affect your operations or any custom reports you have written. If you are upgrading from Release 10.7 you also need to read Oracle Applications Product Update Notes Release 11. Oracle Self-Service Web Applications Implementation Manual This manual describes the setup steps for Oracle Self-Service Web Applications and the Web Applications Dictionary. Oracle Applications Implementation Wizard User Guide If you are implementing more than one Oracle product, you can use the Oracle Applications Implementation Wizard to coordinate your setup activities. This guide describes how to use the wizard. Other Information Training Oracle Education offers a complete set of training courses to help you and your staff master Oracle Applications. We can help you develop a training plan that provides thorough training for both your project team and your end users. We will work with you to organize courses appropriate to your job or area of responsibility. Training professionals can show you how to plan your training throughout the implementation process so that the right amount of information is delivered to key people when they need it the most. You can attend courses at any one of our many Educational Centers, or you can arrange for our trainers to teach at your facility. In addition, we can tailor standard courses or develop custom courses to meet your needs. Support From on-site support to central support, our team of experienced professionals provides the help and information you need to keep OPM working for you. This team includes your Technical Representative, Account Manager, and Oracle’s large staff of consultants and support specialists with expertise in your business area, managing an Oracle server, and your hardware and software environment. Oracle Proprietary, Confidential Information--Use Restricted by Contract 1-10 OPM Inventory Technical Reference Manual Thank You About Oracle Oracle Corporation develops and markets an integrated line of software products for database management, applications development, decision support, and office automation, as well as Oracle Applications, an integrated suite of more than 75 software modules for financial management, supply chain management, manufacturing, project systems, human resources, and sales and service management. Oracle products are available for mainframes, minicomputers, personal computers, network computers, and personal digital assistants, allowing organizations to integrate different computers, different operating systems, different networks, and even different database management systems, into a single, unified computing and information resource. Oracle is the world’s leading supplier of software for information management, and the world’s second largest software company. Oracle offers its database, tools, and applications products, along with related consulting, education, and support services, in over 145 countries around the world. Thank You Thanks for using OPM and this technical reference manual! We appreciate your comments and feedback. At the back of this manual is a Reader’s Comment Form that you can use to explain what you like or dislike about OPM or this technical reference manual. Mail your comments to the following address or call us directly at (650) 506-7000. Oracle Applications Documentation Manager Oracle Corporation 500 Oracle Parkway Redwood Shores, California 94065 U.S.A. Or, send electronic mail to appsdoc@us.oracle.com. Oracle Proprietary, Confidential Information--Use Restricted by Contract Introduction 1-11 Thank You Oracle Proprietary, Confidential Information--Use Restricted by Contract 1-12 OPM Inventory Technical Reference Manual 2 High-Level Design This chapter presents a high-level design for Oracle Process Manufacturing (OPM) that satisfies the business needs we specify during Strategy and Analysis. It contains database diagrams for OPM Inventory building blocks, lists of database tables and views, and a list of modules. Oracle Proprietary, Confidential Information--Use Restricted by Contract High-Level Design 2-1 Overview of High-Level Design Overview of High-Level Design During High-Level Design, we define the application components (tables, views, and modules) we need to build our application. We specify what application components should do without specifying the details of how they should do it. You can refer to this High-Level Design chapter to quickly acquaint yourself with the tables, views, and modules that comprise OPM Inventory. And, you can prepare yourself to understand the detailed design and implementation of OPM. Summary Database Diagram The Summary Database Diagram section graphically represents the most important application tables and the relationships between them. It omits tables and relationships that contribute little to the understanding of the application data model. Typically, a summary database diagram shows tables that contain key reference and transaction data. We prepare a summary database diagram to describe, at a conceptual level, the key information on which our business depends. Later, we refine this summary database diagram, breaking it into multiple database diagrams (generally, one per application building block) to represent all the tables and relationships we need to implement our application in the database. Review the Summary Database Diagram section to see at a glance the major tables and relationships on which your application depends. Database Diagrams The Database Diagrams section graphically represents all OPM Inventory tables and the relationships between them, organized by building block. Use this section to quickly learn what tables each OPM Inventory building block uses, and how those tables interrelate. Then, you can refer to the Table and View Definitions sections of Chapter 2 for more detailed information about each of those tables. Table Lists The Table List sections list the OPM Inventory tables. Because a product might not include at least one table for each type, this Technical Reference Manual might not include each of the following sections. Oracle Proprietary, Confidential Information--Use Restricted by Contract 2-2 OPM Inventory Technical Reference Manual Overview of High-Level Design Public Tables Use the Public Table List section to quickly identify the tables you are most interested in. Then, you can refer to the Table and View Definitions sections of Chapter 2 for more detailed information about those tables. In addition, this manual may contain full documentation for one or more of the following Application Object Library tables: FND_DUAL, FND_CURRENCIES, and FND_COMMON_LOOKUPS. Internal Tables This section includes a list of private, internal tables used by OPM Inventory; we do not provide additional documentation for these tables. View Lists The View List sections list the OPM Inventory views, with one section for each type of view. Because a product might not include at least one view for each type, this Technical Reference Manual might not include each of the following sections. Use this section to quickly identify the views you are most interested in. Then, you can refer to the Table and View Definitions sections of Chapter 2 for more detailed information about those views. Public Views This section lists views that may be useful for your custom reporting or other data requirements. The list includes a description of the view, and the page in Chapter 3 that gives detailed information about the public view. Web Views This section lists views that you may need to configure your Self-Service Web applications. The list includes a description of the view, and the page in Chapter 3 that gives detailed information about the web view. Forms and Table Views This section lists supplementary views that are not essential to the Release 11i data model, but simplify coding or improve performance for Oracle Developer. Internal Views This section includes each private, internal view that OPM Inventory uses. Oracle Proprietary, Confidential Information--Use Restricted by Contract High-Level Design 2-3 Overview of High-Level Design Single-Organization Views This section lists the views that we added to take the place of various tables that are now partitioned by operating unit, to support multiple sets of books within a single installation of OPM. Multiple Reporting Currency Views This list includes views that were created to support the Multiple Reporting Currencies feature. MultiLingual Views This section lists views that were created to allow certain seed data to be available in multiple national languages simultaneously. Module List The Module List section briefly describes each of the OPM Inventory modules. This section lists forms, reports, and concurrent programs. A form is a module comprised of closely related windows that are used together to perform a task. For example, the Enter Journals form in Oracle General Ledger includes the Enter Journals window, the Batch window, and the More Actions window. The Enter Journals window is the main window, and from it, you can use buttons to navigate to other windows in the form. The form name usually corresponds to the main window in the form, and is frequently a window you can open directly from the Navigator. The Reports and Concurrent Programs lists include processes you can submit from the Submit Requests window or other windows, as well as processes that are submitted automatically by OPM Inventory. Use your user’s guide to learn more about reports and concurrent processes. Oracle Proprietary, Confidential Information--Use Restricted by Contract 2-4 OPM Inventory Technical Reference Manual Summary Database Diagram Summary Database Diagram This Summary Database Diagram graphically represents the most important OPM Inventory tables and the relationships between them. It describes, at a conceptual level, the key information on which OPM depends. This diagram does not represent the complete database implementation of OPM Inventory tables. It shows tables that contain key reference and transaction data, and omits tables and relationships that contribute little to the understanding of the OPM data model. For example, a foreign key relationship shown between two tables may actually be implemented by an intervening table, not shown in this diagram. For more detailed graphical representations of OPM Inventory tables and the relationships between them, see the Database DIagrams section in this chapter. Oracle Proprietary, Confidential Information--Use Restricted by Contract High-Level Design 2-5 Summary Database Diagram Database Diagramming Conventions We use the following notational conventions in our database diagrams: Server Model Diagram Elements and Notation Recursive foreign key (Pig's Ear) Title Bar Column/attribute details section Relational Table Foreign key constraint Arc EMP Primary key column (#*) Mandatory column (*) # * EMPNO * DEPTNO Delete rule indicator DEPT Values in the foreign key must match the primary key # * DEPTNO * DNAME Mandatory and Optional Foreign Keys A Value entered in the column in the foreign key must match a value in the primary key column. A Value entered in the column in the foreign key must match either a value in the primary key column, or else it must be null. Oracle Proprietary, Confidential Information--Use Restricted by Contract 2-6 OPM Inventory Technical Reference Manual Database Diagrams Tables - are the basic unit of storage in the database. A hand symbol preceding the title in the table’s title bar indicates that the table is not owned by this application but shared with another. Foreign key constraint - is a type of referential integrity constraint for checking the integrity of data entered in a specific column or set of columns. This specified column or set of columns is known as the foreign key. Delete rule indicator - determines the action to be taken when an attempt is made to delete a related row in a join table. A line through the foreign key constraint, as shown on the above diagram, indicates that this action is restricted. Arcs - specify that, for any given row in a table, a value must be entered in one of the arc columns. The remaining columns within the arc must be null. Database Diagrams This section graphically represents all OPM Inventory tables and the relationships between them, organized by building block. Use this section to quickly learn what tables each OPM Inventory building block uses, and how these tables interrelate. Then, you can refer to the Table and View Definitions sections of Chapter 2 for more detailed information about each of those tables. This section contains a database diagram for each of the following OPM Inventory building blocks: Diagram 1:Inventory Close Diagram 2:Inventory Items Diagram 3:Inventory Transactions Diagram 4:Physical Inventory



Some tables, especially important reference tables, appear in more than one database diagram. When several building blocks use a table, we show that table in each appropriate database diagram. How to Use These Database Diagrams Here is an example of how you might use these database diagrams: Suppose you want to write a custom application to interface your Oracle Payables application with your non-Oracle purchasing system. You want to see how your Oracle Proprietary, Confidential Information--Use Restricted by Contract High-Level Design 2-7 Database Diagrams Oracle Payables application matches an invoice to a purchase order. You turn to Diagram 3 to see the table structure for the Matching Invoices to Purchase Orders building block. You learn that each purchase order distribution line in PO_ DISTRIBUTIONS may be used to create one or more invoice distribution lines in AP_INVOICE_DISTRIBUTIONS when you match an invoice to a purchase order. You can also see that your Oracle Payables application associates a matching hold on an invoice with a purchase order shipment through the PO_LINE_LOCATIONS table. Finally, you know that your Oracle Payables application matches invoices to purchase orders at the purchase order shipment level. Next, you turn to the Table and View Definitions section in Chapter 2 to learn about the columns in each of these tables and determine which columns are required for matching. Using this information, you write an application that allows your Oracle Payables application to match invoices to purchase orders from your purchasing system. Oracle Proprietary, Confidential Information--Use Restricted by Contract 2-8 OPM Inventory Technical Reference Manual Inventory Close B5B 52B 27B 6B2 2721 70B 70B 27B B276B067 B1 - Lot_id for the item Used for multiple ctry of origin or destination which make up 1 inventory transaction. Valid county destination as found in sy_geog_ mst.geog_code. Valid county destination as found in sy_geog_ mst.geog_code. Standard who column Standard who column Standard who column Standard who column Not currently used Standard who column TRANS_ID ITEM_ID LOT_ID ISTAT_QTY CTRY_ORIGIN CTRY_DEST CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY TRANS_CNT LAST_UPDATE_LOGIN NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NUMBER(10) NUMBER NUMBER NUMBER VARCHAR2(4) VARCHAR2(4) DATE NUMBER(15) DATE NUMBER(15) NUMBER NUMBER(15) Indexes Index Name IC_STAT_RPTI0 Index Type NOT UNIQUE Sequence 1 2 3 4 Column Name SEQ_NO ITEM_ID LOT_ID TRANS_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-93 Table and View Definitions IC_STOR_CLS This table contains the item storage class definition for use throughout OPM. Foreign Keys Primary Key Table IC_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name ICSTORAGE_CLASS (PK) ICSTORAGE_CLASS_DESC CREATION_DATE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY TRANS_CNT DELETE_MARK TEXT_CODE LAST_UPDATE_LOGIN ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 Null NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Type VARCHAR2(8) VARCHAR2(70) DATE DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(15) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) Description Code for the storage class. Description of the storage class. Standard who column Standard who column Standard who column Standard who column Not currently used Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Standard who column Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-94 OPM Inventory Technical Reference Manual Table and View Definitions ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Descriptive Descriptive Descriptive Descriptive Descriptive Flexfield Flexfield Flexfield Flexfield Flexfield Indexes Index Name IC_STOR_CLS_PK Index Type UNIQUE Sequence 10 Column Name ICSTORAGE_CLASS Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-95 Table and View Definitions IC_SUMM_INV Inventory summary table. This table contains inventory summary balances by item/warehouse/grade. Foreign Keys Primary Key Table IC_ITEM_MST IC_WHSE_MST QC_GRAD_MST Primary Key Column ITEM_ID WHSE_CODE QC_GRADE Foreign Key Column ITEM_ID WHSE_CODE QC_GRADE Column Descriptions Name SUMM_INV_ID ITEM_ID WHSE_CODE QC_GRADE ONHAND_QTY (PK) Null NOT NULL NOT NULL NOT NULL NULL NOT NULL Type NUMBER(15) NUMBER(10) VARCHAR2(4) VARCHAR2(4) NUMBER Description Primary Key for IC_SUMM_INV. Surrogate key for the item. Warehouse in which the inventory is located. Grade of the inventory. NULL value denotes that QC Grade does not apply to item. Total nettable qty of this item/grade in this warehouse, expressed in the primary unit of measure of an item. Total nettable qty of this item/grade in this warehouse, expressed in the secondary unit of measure of an item, if dual UOM controlled. Total qty available for use as a production ingredient, expressed in the primary UOM of an item. Total qty available for use as a production ingredient, expressed in the secondary UOM of an itemif dual UOM controlled. Total qty available for ordering, expressed in the primary UOM of an item. Total qty available for ordering, expressed in the secondary UOM of an item, if dual UOM controlled. Total qty available for shipping, expressed in the primary UOM of an item. Total qty available for shipping, expressed in the secondary UOM of an item, if dual UOM controlled. Total qty pending from purchase orders, expressed in the primary UOM of an item. Total qty pending from purchase orders, expressed in the secondary UOM of an item if dual UOM controlled. Total qty of product pending from production, expressed in the primary UOM of an item. Total qty of product pending from production, expressed in the secondary UOM of an item. Total qty committed to sales orders and ship- ONHAND_QTY2 NULL NUMBER ONHAND_PROD_QTY NOT NULL NUMBER ONHAND_PROD_QTY2 NULL NUMBER ONHAND_ORDER_QTY ONHAND_ORDER_QTY2 NOT NULL NULL NUMBER NUMBER ONHAND_SHIP_QTY ONHAND_SHIP_QTY2 NOT NULL NULL NUMBER NUMBER ONPURCH_QTY ONPURCH_QTY2 NOT NULL NULL NUMBER NUMBER ONPROD_QTY ONPROD_QTY2 COMMITTEDSALES_QTY NOT NULL NULL NOT NULL NUMBER NUMBER NUMBER Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-96 OPM Inventory Technical Reference Manual Table and View Definitions COMMITTEDSALES_QTY2 NULL NUMBER COMMITTEDPROD_QTY COMMITTEDPROD_QTY2 NOT NULL NULL NUMBER NUMBER INTRANSIT_QTY INTRANSIT_QTY2 LAST_UPDATED_BY CREATED_BY LAST_UPDATE_DATE CREATION_DATE LAST_UPDATE_LOGIN PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE REQUEST_ID NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NUMBER NUMBER NUMBER(15) NUMBER(15) DATE DATE NUMBER(15) NUMBER(15) NUMBER(15) DATE NUMBER(15) ments, expressed in the primary UOM of an item. Total qty committed to sales orders and shipments, expressed in the secondary UOM of an item if dual UOM controlled. Total qty of ingredient committed to batches, expressed in the primary UOM of an item. Total qty of ingredient committed to batches, expressed in the secondary UOM of an item if dual UOM controlled. Not currently used. Not currently used. Standard who column Standard who column Standard who column Standard who column Standard who column Concurrent who column Concurrent who column Concurrent who column Concurrent who column Indexes Index Name IC_SUMM_INV_PK IC_SUMM_INV_U1 Index Type UNIQUE UNIQUE Sequence 10 10 20 30 Column Name SUMM_INV_ID ITEM_ID WHSE_CODE QC_GRADE Sequences Sequence GEM5_SUMM_INV_ID_S Derived Column SUMM_INV_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-97 Table and View Definitions IC_TAXN_ASC Item tax class association. This table associates items to item tax classes. Foreign Keys Primary Key Table IC_ITEM_MST IC_TAXN_CLS IC_TEXT_HDR Primary Key Column ITEM_ID ICTAX_CLASS TEXT_CODE Foreign Key Column ITEM_ID ICTAX_CLASS TEXT_CODE Column Descriptions Name ICTAX_CLASS (PK) ITEM_ID (PK) CREATION_DATE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY TRANS_CNT DELETE_MARK TEXT_CODE LAST_UPDATE_LOGIN Null NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL NULL Type VARCHAR2(8) NUMBER(10) DATE DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(15) Description Code for the tax class. Surrogate key for the item. Standard who column Standard who column Standard who column Standard who column Not currently used Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Standard who column Indexes Index Name IC_TAXN_ASC_PK Index Type UNIQUE Sequence 10 20 Column Name ICTAX_CLASS ITEM_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-98 OPM Inventory Technical Reference Manual Table and View Definitions IC_TAXN_CLS Item tax class table. Groups items into classes for taxation purposes. Foreign Keys Primary Key Table IC_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name ICTAX_CLASS (PK) ICTAX_CLASS_DESC CREATION_DATE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY IN_USE DELETE_MARK TEXT_CODE LAST_UPDATE_LOGIN Null NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL NULL Type VARCHAR2(8) VARCHAR2(70) DATE DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(15) Description Code for the tax class. Description of the tax class. Standard who column Standard who column Standard who column Standard who column Not currently used Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Standard who column Indexes Index Name IC_TAXN_CLS_PK Index Type UNIQUE Sequence 10 Column Name ICTAX_CLASS Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-99 Table and View Definitions IC_TEXT_HDR Master table for creation and storage of text codes associated with GMI tables. Column Descriptions Name TEXT_CODE (PK) Null NOT NULL Type NUMBER(10) Description Surrogate key for the record. ID which joins any rows of text in this table to the Text Table for this Module Standard who column Standard who column Standard who column Standard who column Standard who column LAST_UPDATED_BY CREATED_BY LAST_UPDATE_DATE CREATION_DATE LAST_UPDATE_LOGIN NOT NOT NOT NOT NULL NULL NULL NULL NULL NUMBER(15) NUMBER(15) DATE DATE NUMBER(15) Indexes Index Name IC_TEXT_HDR_PK Index Type UNIQUE Sequence 10 Column Name TEXT_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-100 OPM Inventory Technical Reference Manual Table and View Definitions IC_TEXT_TBL_TL IC Module Text Lines. Descriptive text for all tables in this module. Column Descriptions Name TEXT_CODE (PK) Null NOT NULL Type NUMBER(10) Description Surrogate key for the record. ID which joins any rows of text in this table to the Text Table for this Module No longer used. Defined in sy_para_cds. Defined in sy_para_cds. Line number which allows multiple lines of descripitive text for underlying record. -1 = line is used to recreate key of underlying record; 1 TEXTNULLVARCHAR2(70) Descriptive text. LANG_CODE (PK) PARAGRAPH_CODE (PK) SUB_PARACODE (PK) LINE_NO (PK) NOT NOT NOT NOT NULL NULL NULL NULL VARCHAR2(4) VARCHAR2(4) NUMBER(5) NUMBER(5) LANGUAGE (PK) SOURCE_LANG LAST_UPDATED_BY CREATED_BY LAST_UPDATE_DATE CREATION_DATE LAST_UPDATE_LOGIN NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL VARCHAR2(4) VARCHAR2(4) NUMBER(15) NUMBER(15) DATE DATE NUMBER(15) Standard Standard Standard Standard Standard who who who who who column column column column column Indexes Index Name IC_TEXT_TBL_TL_PK Index Type UNIQUE Sequence 10 20 30 40 50 60 Column Name TEXT_CODE LANG_CODE PARAGRAPH_CODE SUB_PARACODE LINE_NO LANGUAGE Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-101 Table and View Definitions IC_TRAN_ARC Inventory transaction archive table. Stores rows from ic_tran_cmp after purge. Foreign Keys Primary Key Table IC_ITEM_MST IC_LOCT_MST IC_LOTS_MST IC_LOTS_STS IC_TEXT_HDR IC_WHSE_MST QC_GRAD_MST SY_DOCS_MST SY_ORGN_MST SY_ORGN_MST SY_REAS_CDS SY_UOMS_MST SY_UOMS_MST Primary Key Column ITEM_ID WHSE_CODE LOT_ID LOT_STATUS TEXT_CODE WHSE_CODE QC_GRADE DOC_TYPE ORGN_CODE ORGN_CODE REASON_CODE UM_CODE UM_CODE Foreign Key Column ITEM_ID WHSE_CODE LOCATIONLOCATION LOT_ID ITEM_IDITEM_ID LOT_STATUS TEXT_CODE WHSE_CODE QC_GRADE DOC_TYPE ORGN_CODE CO_CODE REASON_CODE TRANS_UM TRANS_UM2 Column Descriptions Name TRANS_ID ITEM_ID LINE_ID CO_CODE (PK) Null NOT NULL NOT NULL NOT NULL NOT NULL Type NUMBER(10) NUMBER(10) NUMBER(10) VARCHAR2(4) Description Unique key for the transaction. Surrogate key for the item. Surrogate key for the document line that produced the transaction. Company affected by the transaction (company which owns the organization which created the transaction). Organization which created the transaction. Warehouse affected by the transaction. Surrogate key of the lot. Location affected by the transaction. Surrogate key of the document that created the transaction. Type of document which created the transaction. Not currently used. Line type. Reason code associated with the transaction. Standard who column Transaction date. Quantity of the transaction in the primary UOM of an item. ORGN_CODE WHSE_CODE LOT_ID LOCATION DOC_ID DOC_TYPE DOC_LINE LINE_TYPE REASON_CODE CREATION_DATE TRANS_DATE TRANS_QTY NOT NULL NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL VARCHAR2(4) VARCHAR2(4) NUMBER(10) VARCHAR2(16) NUMBER(10) VARCHAR2(4) NUMBER(10) NUMBER(5) VARCHAR2(4) DATE DATE NUMBER Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-102 OPM Inventory Technical Reference Manual Table and View Definitions TRANS_QTY2 QC_GRADE LOT_STATUS TRANS_STAT TRANS_UM TRANS_UM2 ARCHIVE_DATE OP_CODE GL_POSTED_IND EVENT_ID TEXT_CODE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY PURGE_ID LAST_UPDATE_LOGIN NULL NULL NULL NULL NULL NULL NULL NULL NULL NUMBER VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) DATE NUMBER(15) NUMBER(10) NUMBER(10) NUMBER(10) DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(15) NOT NOT NOT NOT NOT NULL NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL Quantity of the transaction in the secodary UOM of an item, if dual UOM controlled. Grade associated with the transaction. Lot status associated with the transaction. Not currently used. UOM in which trans_qty is expressed. UOM in which trans_qty2 is expressed. Date the transaction was archived. Operator who created the transaction. GL posted indicator. 0=Not posted to GL, 1=Posted to GL. Event ID. ID which joins any rows of text in this table to the Text Table for this Module Standard who column Standard who column Standard who column Purge ID. Standard who column Indexes Index Name IC_TRAN_ARCI0 Index Type NOT UNIQUE Sequence 1 2 Column Name DOC_TYPE DOC_ID TRANS_ID IC_TRAN_ARC_PK UNIQUE 10 Sequences Sequence GEM5_PURGE_ID_S Derived Column PURGE_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-103 Table and View Definitions IC_TRAN_CMP Completed inventory transactions. Transactions created on the Inventory Quantities form and through Physical Inventory are stored here. Also, completed transactions associated with closed documents can be purged from ic_tran_pnd into this table. Foreign Keys Primary Key Table IC_ITEM_MST IC_LOTS_MST IC_LOTS_STS IC_TEXT_HDR IC_WHSE_MST QC_GRAD_MST SY_DOCS_MST SY_ORGN_MST SY_ORGN_MST SY_REAS_CDS SY_UOMS_MST SY_UOMS_MST Primary Key Column ITEM_ID LOT_ID LOT_STATUS TEXT_CODE WHSE_CODE QC_GRADE DOC_TYPE ORGN_CODE ORGN_CODE REASON_CODE UM_CODE UM_CODE Foreign Key Column ITEM_ID LOT_ID ITEM_IDITEM_ID LOT_STATUS TEXT_CODE WHSE_CODE QC_GRADE DOC_TYPE ORGN_CODE CO_CODE REASON_CODE TRANS_UM TRANS_UM2 Column Descriptions Name TRANS_ID ITEM_ID LINE_ID CO_CODE (PK) Null NOT NULL NOT NULL NOT NULL NOT NULL Type NUMBER(10) NUMBER(10) NUMBER(10) VARCHAR2(4) Description Unique key for the transaction. Surrogate key for the item. Surrogate key for the document line that produced the transaction. Company affected by the transaction (company which owns the organization which created the transaction). Organization which created the transaction. Warehouse affected by the transaction. Surrogate key of the lot. Location affected by the transaction. Surrogate key of the document that created the transaction. Type of document which created the transaction. Not currently used. Line type. Reason code associated with the transaction. Standard who column Transaction date. ORGN_CODE WHSE_CODE LOT_ID LOCATION DOC_ID DOC_TYPE DOC_LINE LINE_TYPE REASON_CODE CREATION_DATE TRANS_DATE NOT NULL NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL VARCHAR2(4) VARCHAR2(4) NUMBER(10) VARCHAR2(16) NUMBER(10) VARCHAR2(4) NUMBER(10) NUMBER(5) VARCHAR2(4) DATE DATE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-104 OPM Inventory Technical Reference Manual Table and View Definitions TRANS_QTY TRANS_QTY2 QC_GRADE LOT_STATUS TRANS_STAT TRANS_UM TRANS_UM2 OP_CODE GL_POSTED_IND EVENT_ID TEXT_CODE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY LAST_UPDATE_LOGIN PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE REQUEST_ID NULL NULL NULL NULL NULL NULL NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NUMBER NUMBER VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) NUMBER(15) NUMBER(10) NUMBER(10) NUMBER(10) DATE NUMBER(15) NUMBER(15) NUMBER(15) NUMBER(15) NUMBER(15) DATE NUMBER(15) Quantity of the transaction in the primary UOM of an item. Quantity of the transaction in the secodary UOM of an item, if dual UOM controlled. Grade associated with the transaction. Lot status associated with the transaction. Not currently used. UOM in which trans_qty is expressed. UOM in which trans_qty2 is expressed. Operator who created the transaction. GL posted indicator. 0=Not posted to GL, 1=Posted to GL. Event ID. ID which joins any rows of text in this table to the Text Table for this Module Standard who column Standard who column Standard who column Standard who column Concurrent who column Concurrent who column Concurrent who column Concurrent who column Indexes Index Name IC_TRAN_CMPI1 Index Type NOT UNIQUE Sequence 1 2 3 4 Column Name ITEM_ID LOT_ID WHSE_CODE LOCATION DOC_TYPE DOC_ID LINE_ID TRANS_ID IC_TRAN_CMPI2 NOT UNIQUE 1 2 3 IC_TRAN_CMP_PK UNIQUE 10 Sequences Sequence GEM5_TRANS_ID_S Derived Column TRANS_ID GEM5_LINE_ID_S LINE_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-105 Table and View Definitions IC_TRAN_PND Pending inventory transaction table. Note that this table stores all pending transactions as well as many completed transactions. Foreign Keys Primary Key Table IC_ITEM_MST IC_LOTS_MST IC_LOTS_STS IC_TEXT_HDR IC_WHSE_MST QC_GRAD_MST SY_DOCS_MST SY_ORGN_MST SY_ORGN_MST SY_REAS_CDS SY_UOMS_MST SY_UOMS_MST Primary Key Column ITEM_ID LOT_ID LOT_STATUS TEXT_CODE WHSE_CODE QC_GRADE DOC_TYPE ORGN_CODE ORGN_CODE REASON_CODE UM_CODE UM_CODE Foreign Key Column ITEM_ID LOT_ID ITEM_IDITEM_ID LOT_STATUS TEXT_CODE WHSE_CODE QC_GRADE DOC_TYPE ORGN_CODE CO_CODE REASON_CODE TRANS_UM TRANS_UM2 Column Descriptions Name TRANS_ID ITEM_ID LINE_ID CO_CODE (PK) Null NOT NULL NOT NULL NOT NULL NOT NULL Type NUMBER(10) NUMBER(10) NUMBER(10) VARCHAR2(4) Description Unique key for the transaction. Surrogate key for the item. Surrogate key for the document line that produced the transaction. Company affected by the transaction (company which owns the organization which created the transaction). Organization which created the transaction. Warehouse affected by the transaction. Surrogate key of the lot. Location affected by the transaction. Surrogate key of the document that created the transaction. Type of document which created the transaction. Not currently used. Line type. Reason code associated with the transaction. Standard who column Transaction date. Quantity of the transaction in the primary UOM of an item. ORGN_CODE WHSE_CODE LOT_ID LOCATION DOC_ID DOC_TYPE DOC_LINE LINE_TYPE REASON_CODE CREATION_DATE TRANS_DATE TRANS_QTY NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL VARCHAR2(4) VARCHAR2(4) NUMBER(10) VARCHAR2(16) NUMBER(10) VARCHAR2(4) NUMBER(10) NUMBER(5) VARCHAR2(4) DATE DATE NUMBER NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-106 OPM Inventory Technical Reference Manual Table and View Definitions TRANS_QTY2 QC_GRADE LOT_STATUS TRANS_STAT TRANS_UM TRANS_UM2 OP_CODE COMPLETED_IND STAGED_IND GL_POSTED_IND EVENT_ID DELETE_MARK TEXT_CODE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY LAST_UPDATE_LOGIN PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE REQUEST_ID REVERSE_ID NULL NULL NULL NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NULL NUMBER VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) NUMBER(15) NUMBER(5) NUMBER(5) NUMBER(10) NUMBER(10) NUMBER(5) NUMBER(10) DATE NUMBER(15) NUMBER(15) NUMBER(15) NUMBER(15) NUMBER(15) DATE NUMBER(15) NUMBER(10) Quantity of the transaction in the secodary UOM of an item, if dual UOM controlled. Grade associated with the transaction. Lot status associated with the transaction. Not currently used. UOM in which trans_qty is expressed. UOM in which trans_qty2 is expressed. Operator who created the transaction. Completed indicator. 0=Pending transaction, 1=Completed transaction. Not currently used. GL posted indicator. 0=Not posted to GL, 1=Posted to GL. Event ID. This column should an optional column; NULL. Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Standard who column Standard who column Standard who column Standard who column Concurrent who column Concurrent who column Concurrent who column Concurrent who column Indicator of whether this is a reversing transaction. Indexes Index Name IC_TRAN_PNDI1 Index Type NOT UNIQUE Sequence 1 2 3 4 Column Name ITEM_ID LOT_ID WHSE_CODE LOCATION DOC_TYPE DOC_ID IC_TRAN_PNDI2 2 NOT UNIQUE 1 3 IC_TRAN_PNDI3 IC_TRAN_PNDI4 NOT UNIQUE NOT UNIQUE 1 1 2 3 4 IC_TRAN_PND_PK UNIQUE 10 LINE_ID LINE_ID ITEM_ID COMPLETED_IND DOC_TYPE WHSE_CODE TRANS_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-107 Table and View Definitions Sequences Sequence GEM5_TRANS_ID_S Derived Column TRANS_ID GEM5_LINE_ID_S LINE_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-108 OPM Inventory Technical Reference Manual Table and View Definitions IC_WHSE_INV Warehouse rules table. Contains item/warehouse rules used by MRP and Physical Inventory. Foreign Keys Primary Key Table IC_CRUL_CLS IC_FRGT_CLS IC_GLED_CLS IC_ITEM_MST IC_RANK_MST IC_SHIP_CLS IC_TEXT_HDR IC_WHSE_MST Primary Key Column COUNT_CLASS ICFRT_CLASS ICGL_CLASS ITEM_ID ABC_CODE ICSHIP_CLASS TEXT_CODE WHSE_CODE Foreign Key Column COUNT_CLASS FRT_CLASS GL_CLASS ITEM_ID WHSE_ABCCODE WHSE_CODEWHSE_CODE SHIP_CLASS TEXT_CODE WHSE_CODE Column Descriptions Name WHSEINV_ID ITEM_ID WHSE_CODE SAFETY_STOCK REORDER_POINT FIXED_LEADTIME VARIABLE_LEADTIME (PK) Null NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL Type NUMBER(15) NUMBER(10) VARCHAR2(4) NUMBER NUMBER NUMBER NUMBER Description Used for Warehouse ID rules Surrogate key for the item for which warehouse rules are defined. Warehouse for which the rules are defined. Contains NULLs, denotes ”ALL” condition. Safety stock of the item in the warehouse. MRP will plan to maintain this stock level. If inventory drops below this level, the item will appear on the Reorder Point report. Fixed component of leadtime for purchases, in days. Variable component of leadtime for purchases, in days. This is added for each multiple of the std_ qty. Minimum qty that can be suggested in one MRP purchase suggestion. Macimum qty that can be suggested in one MRP purchase suggestion. Standard purchase qty. Inner fence in days. MRP will not make purchase suggestions within this period. Outer fence in days. MRP will not make purchase suggestions after this point. When MRP comes to the first net safety stock requirement, it sum all demand out this many days from the day of the safety stock requirement and make suggestions to cover that demand Not currently used. Purchase indicator. 0=Item is not purchased, MIN_QTY MAX_QTY STD_QTY INNER_TIMEFENCE OUTER_TIMEFENCE DAYS_SUPPLY NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER SCRAP_FACTOR PURCHASE_IND NOT NULL NOT NULL NUMBER NUMBER(5) Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-109 Table and View Definitions TRANSFER_IND NOT NULL NUMBER(5) PRODUCTION_IND RESCHEDULE_PERIOD RULE_TYPE WHSE_ABCCODE CYCLECOUNT_DAYS NOT NULL NOT NULL NOT NULL NULL NOT NULL NUMBER(5) NUMBER NUMBER(5) VARCHAR2(4) NUMBER(5) LASTCOUNT_DATE COUNT_CLASS GL_CLASS SHIP_CLASS FRT_CLASS CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY DELETE_MARK TRANS_CNT TEXT_CODE ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL DATE VARCHAR2(8) VARCHAR2(8) VARCHAR2(8) VARCHAR2(8) DATE NUMBER(15) DATE NUMBER(15) NUMBER(5) NUMBER(10) NUMBER(10) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) NOT NOT NOT NOT NOT 1=Item is replenished in this warehouse through purchases. Transfer indicator. 0=Item is not transferred, 1=Item is replenished in this warehouse through warehouse transfers. 0=Item is not produced, 1=Item is replenished in this warehouse through production. Number of days purchase orders can be rescheduled. Reorder rule for purchases. 0=Manual planning, 1=Lot for lot, 2=Economic order quantity. ABC code for the item in this warehouse. Number of days defining how often the item should be counted in this warehouse for cycle counts. Last date the item was counted in this warehouse. Count class for the item in this warehouse. GL class. Ship class. Freight class. Standard who column Standard who column Standard who column Standard who column Standard: 0=Active record (default); 1=Marked for (logical) deletion. Not currently used ID which joins any rows of text in this table to the Text Table for this Module Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-110 OPM Inventory Technical Reference Manual Table and View Definitions ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY LAST_UPDATE_LOGIN NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) NUMBER(15) Descriptive flexfield Descriptive flexfield Descriptive flexfield Descriptive flexfield Descriptive flexfield Descriptive flexfield Descriptive flexfield Descriptive flexfield Descriptive flexfield Standard who column segment segment segment segment segment segment segment segment segment Indexes Index Name IC_WHSE_INV_PK IC_WHSE_INV_U1 Index Type UNIQUE UNIQUE Sequence 10 10 20 Column Name WHSEINV_ID ITEM_ID WHSE_CODE Sequences Sequence GEM5_WHSEINV_ID_S Derived Column WHSEINV_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-111 Table and View Definitions IC_WHSE_MST Warehouse master. This table contains warehouse definition information. Foreign Keys Primary Key Table IC_TEXT_HDR SY_ADDR_MST SY_ORGN_MST TX_TLOC_CDS Primary Key Column TEXT_CODE ADDR_ID ORGN_CODE TAXLOC_CODE Foreign Key Column TEXT_CODE ADDR_ID ORGN_CODE TAXLOC_CODE Column Descriptions Name WHSE_CODE WHSE_NAME ORGN_CODE LOCT_CTL (PK) Null NOT NOT NOT NOT NULL NULL NULL NULL Type VARCHAR2(4) VARCHAR2(40) VARCHAR2(4) NUMBER(5) Description ADDR_ID CONSIGN_IND NONNET_IND REGION_CODE WHSE_CLASS WHSE_CONTACT WHSE_PHONE TAXLOC_CODE LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY TRANS_CNT DELETE_MARK TEXT_CODE LAST_UPDATE_LOGIN INGREDIENT_STAGE PRODUCT_STAGE RECV_STAGE SHIP_STAGE ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Code for the warehouse. Name of the warehouse. Organization that owns the warehouse. Location control indicator. 0=Warehouse is not location controlled, 1=Validated location control, 2=Non-validated location control. NUMBER(10) Surrogate key to the address of the warehouse. NUMBER(5) Consignment indicator. 0=Not a consigment warehouse, 1=Consignment warehouse. NUMBER(5) Not currently used. VARCHAR2(8) Region code. VARCHAR2(8) Warehouse class. Not currently validated. VARCHAR2(40) Contact person at the warehouse. VARCHAR2(20) Phone number. VARCHAR2(10) Tax location of warehouse. DATE Standard who column NUMBER(15) Standard who column DATE Standard who column NUMBER(15) Standard who column NUMBER(10) Not currently used NUMBER(5) Standard: 0=Active record (default); 1=Marked for (logical) deletion. NUMBER(10) ID which joins any rows of text in this table to the Text Table for this Module NUMBER(15) Standard who column VARCHAR2(16) Staging location for ingredients. VARCHAR2(16) Staging location for products. VARCHAR2(16) Staging location for receiving. Acts as default location for receiving allocations. VARCHAR2(16) Staging location for shipping. Acts as default location for shipment allocations. VARCHAR2(240) Descriptive flexfield segment VARCHAR2(240) Descriptive flexfield segment VARCHAR2(240) Descriptive flexfield segment Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-112 OPM Inventory Technical Reference Manual Table and View Definitions ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment Indexes Index Name IC_WHSE_MST_PK Index Type UNIQUE Sequence 10 Column Name WHSE_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-113 Table and View Definitions IC_WHSE_REL Warehouse transfer rules table. This table contains information on transfers between warehouses, it is used by MRP. Foreign Keys Primary Key Table IC_ITEM_MST IC_TEXT_HDR IC_WHSE_MST IC_WHSE_MST Primary Key Column ITEM_ID TEXT_CODE WHSE_CODE WHSE_CODE Foreign Key Column ITEM_ID TEXT_CODE DEST_WHSE SOURCE_WHSE Column Descriptions Name WHSEREL_ID (PK) SOURCE_WHSE DEST_WHSE ITEM_ID FIXED_LEADTIME VARIABLE_LEADTIME Null NOT NULL NULL NULL NULL NOT NULL NOT NULL Type NUMBER(15) VARCHAR2(4) VARCHAR2(4) NUMBER(10) NUMBER NUMBER Description Surrogate ID for this record Warehouse from which the item is transferred. Warehouse to which the item is transferred. Surrogate key for the item transferred. Fixed component of transfer leadtime, in days. Variable component of transfer leadtime, in days. This is added for each multiple of the std_ qty. Inner fence in days. MRP will not make transfer suggestions within this period. Outer fence in days. MRP will not make transfer suggestions after this point. Minimum qty that can be suggested in one MRP transfer suggestion. Macimum qty that can be suggested in one MRP transfer suggestion. Standard transfer qty. Reorder rule type. 0=Manual planning, 1=Lot for lot, 2=Economic order quantity. Preference indicator for selecting among different warehouse transfer rules for the item when it is required in the dest_whse. Standard who column Standard who column Standard who column Standard who column Standard: 0=Active record (default); 1=Marked for (logical) deletion. Not currently used ID which joins any rows of text in this table to the Text Table for this Module Transfer type. 0=Transfer suggested only if entire required qty available in source_whse, 1=Forced transfer, 2=Partial transfer allowed. INNER_TIMEFENCE OUTER_TIMEFENCE MIN_QTY MAX_QTY STD_QTY RULE_TYPE PREFERENCE NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER(5) NUMBER(5) CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY DELETE_MARK TRANS_CNT TEXT_CODE TRANSFER_TYPE NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL DATE NUMBER(15) DATE NUMBER(15) NUMBER(5) NUMBER(10) NUMBER(10) NUMBER(5) NOT NULL Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-114 OPM Inventory Technical Reference Manual Table and View Definitions LAST_UPDATE_LOGIN NULL NUMBER(15) Standard who column Indexes Index Name IC_WHSE_REL_PK IC_WHSE_REL_U1 Index Type UNIQUE UNIQUE Sequence 10 10 20 30 Column Name WHSEREL_ID SOURCE_WHSE DEST_WHSE ITEM_ID Sequences Sequence GEM5_WHSEREL_ID_S Derived Column WHSEREL_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-115 Table and View Definitions IC_WHSE_STS Warehouse close status. Stores the period end close info at warehouse level. Foreign Keys Primary Key Table IC_CLDR_DTL IC_WHSE_MST Primary Key Column PERIOD_ID WHSE_CODE Foreign Key Column PERIOD_ID WHSE_CODE Column Descriptions Name WHSE_CODE (PK) FISCAL_YEAR PERIOD_ID (PK) PERIOD LOG_END_DATE CLOSE_WHSE_IND LAST_UPDATE_LOGIN LAST_UPDATED_BY CREATED_BY LAST_UPDATE_DATE CREATION_DATE Null NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Type VARCHAR2(4) VARCHAR2(4) NUMBER(10) NUMBER(10) DATE NUMBER(5) NUMBER(15) NUMBER(15) NUMBER(15) DATE DATE Description Warehouse in the organization of the calendar. Code of inventory calendar. Surrogate key for the Period. Number of the period in the year. Last time a close done on the warehouse. Close status of the warehouse. 1=Open; 2=Preliminary close, 3=Final close. Standard who column Standard who column Standard who column Standard who column Standard who column NOT NOT NOT NOT Indexes Index Name IC_WHSE_STS_PK 20 IC_WHSE_STS_U1 Index Type UNIQUE Sequence 10 Column Name WHSE_CODE PERIOD_ID UNIQUE 10 20 30 WHSE_CODE FISCAL_YEAR PERIOD Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-116 OPM Inventory Technical Reference Manual Table and View Definitions IN_ADDR_MST EC Address Supplemental Details. Intrastat details relevant to ship-to address. Column Descriptions Name ADDR_ID (PK) VAT_REGN BRANCH_NO EC_DELIVERY_CODE MOT_CODE EC_COUNTRY_CODE EC_REGION_CODE PORT_CODE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN TRANS_CNT DELETE_MARK TEXT_CODE Null NOT NULL NOT NULL NULL NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Type NUMBER(10) VARCHAR2(20) NUMBER(3) VARCHAR2(3) NUMBER VARCHAR2(3) VARCHAR2(4) VARCHAR2(8) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) Description FK - as found in sy_addr_mst VAT registration associated with this address Branch identifier Default delivery code used with this address Default mode of transport code used with this address Country code as known in EC member state. May be 2 alpha or 3 numeric EC standard region code of this address code of port associated with this address Standard who column Standard who column Standard who column Standard who column Standard who column Not currently used used to logically delete row ID which joins any rows of text in this table to the Text Table for this Module NOT NOT NOT NOT NOT Indexes Index Name IN_ADDR_MST_PK Index Type UNIQUE Sequence 10 Column Name ADDR_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-117 Table and View Definitions IN_CTRY_MST Country Codes. This table contains Intrastat specific country codes. Column Descriptions Name EC_COUNTRY_CODE (PK) COUNTRY_NAME EC_MEMBER_STATE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN TRANS_CNT DELETE_MARK TEXT_CODE Null NOT NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL Type VARCHAR2(3) VARCHAR2(40) NUMBER(5) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) Description Country code Country name indicates if country is a EC member state Standard who column Standard who column Standard who column Standard who column Standard who column Not currently used used to logically delete row ID which joins any rows of text in this table to the Text Table for this Module Indexes Index Name IN_CTRY_MST_PK Index Type UNIQUE Sequence 10 Column Name EC_COUNTRY_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-118 OPM Inventory Technical Reference Manual Table and View Definitions IN_ITEM_MST EC Item Supplemental Details. This table contains Item related Intrastat details. Column Descriptions Name ITEM_ID (PK) PROCESSED_IND CTRY_OF_ORIGIN SUPPLEMENTAL_UM CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN TRANS_CNT DELETE_MARK TEXT_CODE Null NOT NULL NOT NULL NULL NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NOT NULL NULL Type NUMBER(10) VARCHAR2(1) VARCHAR2(3) VARCHAR2(4) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) Description FK - as used in ic_item_mst Indicates if item is for process / repair Valid country code as found in in_ctry_mst Supplemental unit of measure code as found in sy_uoms_mst (or blank) Standard who column Standard who column Standard who column Standard who column Standard who column Not currently used used to logically delete row ID which joins any rows of text in this table to the Text Table for this Module Indexes Index Name IN_ITEM_MST_PK Index Type UNIQUE Sequence 10 Column Name ITEM_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-119 Table and View Definitions IN_REGN_MST EC Regions Code. This table contains Intrastat region codes Column Descriptions Name EC_REGION_CODE (PK) EC_REGION_DESC CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN TRANS_CNT DELETE_MARK TEXT_CODE Null NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL Type VARCHAR2(4) VARCHAR2(40) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) Description Standard EC region code EC region code description Standard who column Standard who column Standard who column Standard who column Standard who column Not currently used used to logically delete row ID which joins any rows of text in this table to the Text Table for this Module Indexes Index Name IN_REGN_MST_PK Index Type UNIQUE Sequence 10 Column Name EC_REGION_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-120 OPM Inventory Technical Reference Manual Table and View Definitions IN_STAT_CTL EC Statistics Control Table. Intrastat build run status by company code. Column Descriptions Name CO_CODE (PK) RUN_STATUS FROM_DATE TO_DATE STARTED_ON STARTED_BY ENDED_ON TOTAL_ROWS ERROR_ROWS CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN TRANS_CNT DELETE_MARK TEXT_CODE Null NOT NULL NOT NULL NOT NULL NOT NULL NOT NOT NOT NOT NULL NULL NULL NULL Type VARCHAR2(4) NUMBER(1) DATE DATE DATE VARCHAR2(4) DATE NUMBER(10) NUMBER(10) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) Description Company code for which build is being run 0 = statistics build not running1 = statistics build running start date from which transactions should be selected for build run end date to which transactions should be selected for build run date and time run was initiated operator_code of initiator of run date and time run was completed total number of rows inserted into statistics detail table number of error mesages inserted into ec_stat_ msg Standard who column Standard who column Standard who column Standard who column Standard who column Not currently used used to logically delete row ID which joins any rows of text in this table to the Text Table for this Module NOT NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NOT NULL NULL Indexes Index Name IN_STAT_CTL_PK Index Type UNIQUE Sequence 10 Column Name CO_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-121 Table and View Definitions IN_STAT_DTL EC Statistics Detail. Intrastat statistics detail file. Column Descriptions Name STAT_ID (PK) STATLINE_ID (PK) TRANS_ID PND_TRANS_ID CMP_TRANS_ID LINE_ID PORECV_LINE_ID OPORDR_LINE_ID ICADJS_LINE_ID ITEM_ID LOT_ID COMMODITY_CODE STAT_QTY STAT_UM STAT_VALUE CURRENCY_CODE TRANS_DATE STAT_SUPP_QTY STAT_SUPP_UM FROM_COUNTRY TO_COUNTRY CTRY_OF_ORIGIN FROM_REGION_CODE TO_REGION_CODE TRANS_NATURE EC_DELIVERY_CODE MOT_CODE VAT_REGN BRANCH_NO PORT_CODE ORIGINATION_IND CREATION_DATE CREATED_BY LAST_UPDATE_DATE Null NOT NULL NOT NULL NOT NULL NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Type NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) VARCHAR2(9) NUMBER(10) VARCHAR2(4) NUMBER(10) VARCHAR2(4) DATE NUMBER(10) VARCHAR2(4) VARCHAR2(3) VARCHAR2(3) VARCHAR2(3) VARCHAR2(4) VARCHAR2(4) NUMBER(2) VARCHAR2(4) NUMBER(1) VARCHAR2(20) NUMBER(3) VARCHAR2(8) NUMBER(1) DATE NUMBER(15) DATE Description FK as found in in_stat_hdr surrogate key to this table FK to ic_tran_pnd or ic_tran_cmp FK as found in ic_tran_pnd FK as found in ic_tran_cmp FK as found in po_recv_dtl op_ordr_dtl, ic_adjs_ jnl FK as found in po_recv_dtl FK as found in op_ordr_dtl FK as found in ic_adjs_jnl FK as found in ic_item_mst FK as found in ic_lots_mst valid commodity code as defined in ic_comd_cds quantity of goods moved in SY$INTRASTAT_UM value of SY$INTRASTAT_UM value of goods in base currency base currency code date of movement quantity of goods in supplementary unit of measure supplementary unit of measure country code of member state where goods dispatched from. country code of member state where goods are moved to. valid country code as defined in in_ctry_mst region code as defined in in_regn_mst where goods dispatched from. valid region code as defined in in_regn_mst where goods moved to Standard 2 digit code identifying nature of transaction. valid delivery code as defined in in_tofd_mst Standard 1 digit code identifying mode of transport. VAT registration of customer / supplier or warehouse Branch identifier code of port 0 = manual creation / amendment1 = auto created by build routine. Standard who column Standard who column Standard who column NOT NOT NOT NOT NOT NOT NOT NOT NULL NOT NULL NOT NULL NULL NULL NULL NOT NULL NULL NULL NOT NULL NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-122 OPM Inventory Technical Reference Manual Table and View Definitions LAST_UPDATED_BY LAST_UPDATE_LOGIN TRANS_CNT DELETE_MARK TEXT_CODE NOT NULL NULL NULL NOT NULL NULL NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) Standard who column Standard who column Not currently used used to logically delete row ID which joins any rows of text in this table to the Text Table for this Module Indexes Index Name IN_STAT_DTL_PK Index Type UNIQUE Sequence 10 20 Column Name STAT_ID STATLINE_ID Sequences Sequence GMZ_STATLINE_ID_S Derived Column STATLINE_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-123 Table and View Definitions IN_STAT_HDR EC Statistics Header. Intrastat statistics header file. Column Descriptions Name DOC_TYPE (PK) DOC_ID (PK) ARRIVAL_DISPATCH_IND RECV_ID ORDER_ID JOURNAL_ID STAT_ID CO_CODE ORGN_CODE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN IN_USE DELETE_MARK TEXT_CODE Null NOT NULL NOT NULL (PK) NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Type VARCHAR2(4) NUMBER(10) NUMBER(1) NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) VARCHAR2(4) VARCHAR2(4) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) Description Document type which is source of movement FK - as found in source document Indicates whether this document pertains to arrivals or dispatches FK - as used in po_recv_hdr FK - as used in op_ordr_hdr FK - as used in ic_jrnl_mst surrogate key for this table company code organisation code Standard who column Standard who column Standard who column Standard who column Standard who column Not currently used used to logically delete row ID which joins any rows of text in this table to the Text Table for this Module NOT NOT NOT NOT NOT NOT NOT NOT Indexes Index Name IN_STAT_HDR_PK Index Type UNIQUE Sequence 10 20 30 Column Name DOC_TYPE DOC_ID ARRIVAL_DISPATCH_IND Sequences Sequence OPM_STAT_ID_S Derived Column STAT_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-124 OPM Inventory Technical Reference Manual Table and View Definitions IN_STAT_MSG EC Statistics message table. Intrastat build error messages by company code. Column Descriptions Name CO_CODE (PK) LINE_NO (PK) MESSAGE_COMMENT CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN DELETE_MARK Null NOT NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL Type VARCHAR2(4) NUMBER(10) VARCHAR2(240) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(5) Description Company code for which build is being run error message line number text narrative of error message Standard who column Standard who column Standard who column Standard who column Standard who column used to logically delete row Indexes Index Name IN_STAT_MSG_PK Index Type UNIQUE Sequence 10 20 Column Name CO_CODE LINE_NO Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-125 Table and View Definitions IN_TEXT_HDR Master table for creation and storage of text codes associated with GMA tables. Column Descriptions Name TEXT_CODE (PK) Null NOT NULL Type NUMBER(10) Description Surrogate key for the record. ID which joins any rows of text in this table to the Text Table for this Module Standard who column Standard who column Standard who column Standard who column Standard who column CREATED_BY CREATION_DATE LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN NOT NOT NOT NOT NULL NULL NULL NULL NULL NUMBER(15) DATE DATE NUMBER(15) NUMBER(15) Indexes Index Name IN_TEXT_HDR_PK Index Type UNIQUE Sequence 10 Column Name TEXT_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-126 OPM Inventory Technical Reference Manual Table and View Definitions IN_TEXT_TBL_TL IN Module Text Lines. Descriptive text for all tables in this module. Column Descriptions Name TEXT_CODE (PK) Null NOT NULL Type NUMBER(10) Description Surrogate key for the record. ID which joins any rows of text in this table to the Text Table for this Module Indicates language used in text. Defined in sy_para_cds. Defined in sy_para_cds. Line number which allows multiple lines of descripitive text for underlying record. -1 = line is used to recreate key of underlying record; 1 TEXTNULLVARCHAR2(70) Descriptive text. LANG_CODE (PK) PARAGRAPH_CODE (PK) SUB_PARACODE (PK) LINE_NO (PK) NOT NOT NOT NOT NULL NULL NULL NULL VARCHAR2(4) VARCHAR2(4) NUMBER(5) NUMBER(5) LANGUAGE (PK) SOURCE_LANG CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN LAST_UPDATED_BY NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NOT NULL VARCHAR2(4) VARCHAR2(4) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) Standard Standard Standard Standard Standard who who who who who column column column column column Indexes Index Name IN_TEXT_TBL_TL_PK Index Type UNIQUE Sequence 1 2 3 4 5 6 Column Name TEXT_CODE LANG_CODE PARAGRAPH_CODE SUB_PARACODE LINE_NO LANGUAGE Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-127 Table and View Definitions IN_TOFD_MST EC Terms of Delivery Table. Intrastat specific terms of delivery code Column Descriptions Name EC_DELIVERY_CODE (PK) EC_DELIVERY_DESC CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN TRANS_CNT DELETE_MARK TEXT_CODE Null NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL Type VARCHAR2(3) VARCHAR2(40) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) Description EC terms of delivery code EC terms of delivery description Standard who column Standard who column Standard who column Standard who column Standard who column Not currently used used to logically delete row ID which joins any rows of text in this table to the Text Table for this Module Indexes Index Name IN_TOFD_MST_PK Index Type UNIQUE Sequence 10 Column Name EC_DELIVERY_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-128 OPM Inventory Technical Reference Manual Table and View Definitions OP_BILL_LAD This table contains header information for a shipment. You can find general information for the organization where the shipment belongs, total shipping weight, and descriptive information concerning this shipment. Shipment lines aremaintained in the table OP_ORDR_DTL. Foreign Keys Primary Key Table GL_CURR_MST OP_PORT_MST OP_PORT_MST OP_SHIP_MST OP_TEXT_HDR SY_ADDR_MST SY_ORGN_MST SY_UOMS_MST Primary Key Column CURRENCY_CODE PORT_CODE PORT_CODE SHIPPER_CODE TEXT_CODE ADDR_ID ORGN_CODE UM_CODE Foreign Key Column DEMURRAGE_CURRENCY EMBARKATION_PORT DEBARKATION_PORT SHIPPER_CODE TEXT_CODE BOLADDR_ID ORGN_CODE BOL_UM Column Descriptions Name BOL_ID (PK) ORGN_CODE MSTBOL_ID BOL_NO SHIPPER_CODE BOLADDR_ID WAYBILL_NO BOLSHIP_WT BOL_UM Null NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL Type NUMBER(10) VARCHAR2(4) NUMBER(10) VARCHAR2(32) VARCHAR2(4) NUMBER(10) VARCHAR2(32) NUMBER VARCHAR2(4) Description Surrogate id to uniquely identify this shipment. Organization code to which this shipment belongs. Fk to sy_orgn_mst. For master bills of lading, manifests, etc. (for future use). Bill of lading number - unique within an organization. Fk to op_ship_mst. Pointer to address, if modified, contained in sy_addr_mst. Waybill number, freeform, unvalidated column. User-modifiable total shipping weight. Defaults to system-calculated ship weight. Bol unit of measure to bol shipping weight. Defaults to system variable shipping wt uom. Fk to sy_uoms_mst. For future use - presently only one type of bol. Defaults to 0. Number of times a bol is printed. Incremented by 1 when the bol is printed to a printer. Carrier trailer number or lock tag id, freeform, unvalidated column. Port of loading/port of discharge. Fk to op_ port_mst. Port shipment is unloaded/port of entry. Fk to op_port_mst. BOL_TYPE PRINT_COUNT TRAILER_NO EMBARKATION_PORT DEBARKATION_PORT NOT NULL NOT NULL NULL NULL NULL NUMBER(5) NUMBER(5) VARCHAR2(40) VARCHAR2(8) VARCHAR2(8) Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-129 Table and View Definitions REGISTERED_IND DROPOFF_TIME PICKUP_TIME DEMURRAGE_AMT DEMURRAGE_CURRENCY BOL_COMMENT DATE_PRINTED CREATION_DATE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY LAST_UPDATE_LOGIN DELETE_MARK IN_USE TEXT_CODE EDI_TRANS_COUNT ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 NOT NULL NOT NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NUMBER(5) DATE DATE NUMBER VARCHAR2(4) VARCHAR2(70) DATE DATE DATE NUMBER(15) NUMBER(15) NUMBER(15) NUMBER(5) NUMBER(10) NUMBER(10) NUMBER(5) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) NOT NOT NOT NOT NOT Indicates if shipment is registered. Not currently used. Defaults to 0. For demurrage determination. Date and time of dropoff. For demurrage determination. Date and time of pickup. Amount of demurrage penalty. Demurrage is the amount charged for detainment of a shipment. Currency in which demurrage was charged. Fk to gl_curr_mst. Freeform text. Date that the bill of lading was printed Standard who column Standard who column Standard who column Standard who column Standard who column Standard: 0=Active record (default); 1=Marked for (logical) deletion. Not currently used ID which joins any rows of text in this table to the Text Table for this Module Used with advanced ship notice EDI transactions Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-130 OPM Inventory Technical Reference Manual Table and View Definitions ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NULL NULL VARCHAR2(240) Descriptive Flexfield Segment VARCHAR2(240) Descriptive Flexfield Segment VARCHAR2(30) Attribute Category for Flexfield attributes Indexes Index Name OP_BILL_LAD_PK OP_BILL_LAD_U1 Index Type UNIQUE UNIQUE Sequence 10 10 20 Column Name BOL_ID ORGN_CODE BOL_NO Sequences Sequence GMO_BOL_ID_S Derived Column BOL_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-131 Table and View Definitions OP_CUST_MST This table is the customer master for OPM. All customer information must be entered in Oracle Receivables. A data synchronization program then transfers the information and creates records in this table. There are several fields whichare not updated by Oracle Receivables and these may be modified in OPM using the Customer screen. Since information in these fields has no equivalent in Oracle Receivables, the data does not exist in that system. These fields include: CUSTSORT_NO, ALTCUST_NO, TO_WHSE, SPLC_CODE, PRICECUST_NO, CUSTTRADE_CLASS, TEXT_CODE, RESTRICT_TO_CUSTITEMS, LOWEST_ PRICE_IND. All other information is taken from similar columns in Oracle Receivables tables. See the table description for the OPM table OP_CUST_MST_INT for additional information. Foreign Keys Primary Key Table GL_CURR_MST IC_WHSE_MST IC_WHSE_MST OP_CGLD_CLS OP_COMMISSION OP_CPRC_CLS OP_CTRD_CLS OP_CUST_CLS OP_CUST_MST OP_FOBC_MST OP_FRGT_MTH OP_LKBX_MST OP_SLSR_MST OP_TERM_MST OP_TEXT_HDR SY_ADDR_MST SY_ADDR_MST SY_ORGN_MST TX_CALC_MST TX_TLOC_CDS Primary Key Column CURRENCY_CODE WHSE_CODE WHSE_CODE CUSTGL_CLASS COMMISSION_CODE CUSTPRICE_CLASS CUSTTRADE_CLASS CUST_CLASS CUST_ID FOB_CODE FRTBILL_MTHD LOCKBOX_CODE SLSREP_CODE TERMS_CODE TEXT_CODE ADDR_ID ADDR_ID ORGN_CODE TAXCALC_CODE TAXLOC_CODE Foreign Key Column CUST_CURRENCY FROM_WHSE TO_WHSE CUSTGL_CLASS COMMISSION_CODE CUSTPRICE_CLASS CUSTTRADE_CLASS CUST_CLASS PRICECUST_ID FOB_CODE FRTBILL_MTHD LOCKBOX_CODE SLSREP_CODE TERMS_CODE TEXT_CODE ADDR_ID MAILADDR_ID CO_CODE TAXCALC_CODE TAXLOC_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-132 OPM Inventory Technical Reference Manual Table and View Definitions Column Descriptions Name CUST_ID CUST_NO ADDR_ID (PK) Null NOT NULL NOT NULL NULL Type NUMBER(10) VARCHAR2(32) NUMBER(10) Description Surrogate key for customer master record. Surrogate cust_vend in sy_surg_ctl. Unique identifier of a customer. Pointer to address for customer. Fk to sy_addr_ mst. For order entry and shipping, this is the shipping address. Customer sort number. Used to sort customers for lookups and other purposes. Defaults from customer name. User can override for user-defined sort order. Customer name Customer phone number. Customer fax number. Customer telex number. Alternate customer number. Not validated, for customer use. Company for which the customer number is defined. A NULL value indicates the customer is defined for all companies. Pointer to sy_addr_mst which holds the mailing address. This address is used for invoicing and billing purposes. Default payment terms code for customer; fk to op_term_mst. Indicates if terms code can vary for this customer on an order. 0 means that the terms code can not be changed on an order; 1 means that the terms code can be changed. Default fob code for this customer; fk to op_ fobc_mst. Default sales rep for this customer; fk to op_ slsr_mst. Default commission code for this customer; fk to op_comm_cds. Indicates commission group. Indicates if the customer accepts backorders. Value of 0 means that the customer does not accept backorders (i.e., if the entire order qty can not be shipped, then the remaining qty is not backordered). Value of 1 means that thecustomer For future use: indicates if the customer is sent a statement. Value of 0 means that the customer is not sent a statement; value of 1 means that the customer is sent a monthly statement. For future use; indicates if the customer is charged a service charge for overdue payments. Value of 0 means that the customer is not charged a service charge; value of 1 means that the customer is charged a service charge for overdue pay Indicates whether a customer is active or not. Value of 0 means that the customer is active; a value of 1 means that the customer is inactive. Defaults to 0. Indicates whether the customer is a bill-to. CUSTSORT_NO NOT NULL VARCHAR2(16) CUST_NAME PHONE_NO FAX_NO TELEX_NO ALTCUST_NO CO_CODE NOT NULL NULL NULL NULL NULL NULL VARCHAR2(40) VARCHAR2(20) VARCHAR2(20) VARCHAR2(20) VARCHAR2(32) VARCHAR2(4) MAILADDR_ID NULL NUMBER(10) TERMS_CODE TERMS_VARY NULL NULL VARCHAR2(4) NUMBER(5) FOB_CODE SLSREP_CODE COMMISSION_CODE BACKORDER_IND NULL NULL NULL NOT NULL VARCHAR2(4) VARCHAR2(8) VARCHAR2(8) NUMBER(5) STMT_IND NOT NULL NUMBER(5) SVCCHG_IND NOT NULL NUMBER(5) INACTIVE_IND NOT NULL NUMBER(5) BILL_IND NOT NULL NUMBER(5) Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-133 Table and View Definitions SHIP_IND NOT NULL NUMBER(5) CUST_TYPE NOT NULL NUMBER(5) SIC_CODE FROM_WHSE TO_WHSE NULL NULL NULL VARCHAR2(8) VARCHAR2(4) VARCHAR2(4) SPLC_CODE NULL VARCHAR2(16) FRTBILL_MTHD CREDIT_LIMIT NULL NOT NULL VARCHAR2(4) NUMBER PRICECUST_ID CUST_TERRITORY CUST_CURRENCY CUST_CLASS CUSTTRADE_CLASS CUSTGL_CLASS TAXLOC_CODE TAXCALC_CODE NOT NULL NULL NULL NULL NULL NULL NULL NULL NUMBER(10) VARCHAR2(8) VARCHAR2(4) VARCHAR2(8) VARCHAR2(8) VARCHAR2(8) VARCHAR2(10) VARCHAR2(4) OF_CUST_ID OF_SHIP_TO_ADDRESS_ID OF_SHIP_TO_SITE_USE_ID OF_BILL_TO_ADDRESS_ID OF_BILL_TO_SITE_USE_ID CREATION_DATE LAST_UPDATE_LOGIN LAST_UPDATE_DATE NULL NULL NULL NULL NULL NOT NULL NULL NOT NULL NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) DATE NUMBER(15) DATE Value of 0 means that the customer is not a bill-to customer; value of 1 means that the customer is a bill-to customer. Indicates whether a customer is a ship-to customer. A value of 0 means that the customer is not a ship-to customer; a value of 1 means that the customer is a ship-to customer. Designates the type of customer. Valid values are: 0 means ”normal” customer 1 means consignment customer 2 means transfer/interplant customer. Unvalidated field which can hold the u.s. standard industry code. Default warehouse where shipments will originate for this customer; fk to ic_whse_mst. Default receiving warehouse for this customer. For use with consignment and transfer customers only. Fk to ic_whse_mst. Unvalidated field which can contain the standard point location code used in the u.s. for freight and shipping purposes. Default freight bill method for this customer; fk to op_frgt_mth Unvalidated field which can contain a credit limit amount. Will be used as an option for order level credit checking; complete credit checking is available by maintaining credit limits in oracle financials and using the gemms-of a/r integ Contains the customer id of the customer to be used for customer-specific pricing. Customer territory; unvalidated field. Billing currency of the customer; fk to gl_curr_ mst. Customer class, fk to op_cust_cls. Customer trade class; fk to op_ctrd_cls. Customer gl class used for gl mapping; fk to op_ cgld_cls. Tax location code of customer; fk to tx_tloc_ cds. Tax calculation code for customer to specify special tax calculation flags for this customer; fk to tx_calc_mst. Surrogate from Oracle Financials identifying the customer. Surrogate from Oracle Financials identifying the customer address. Surrogate from Oracle Financials identifying the customer location. Surrogate from Oracle Financials identifying the customer bill to address. Surrogate from Oracle Financials identifying the customer bill to site use id. Standard who column Standard who column Standard who column Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-134 OPM Inventory Technical Reference Manual Table and View Definitions CREATED_BY LAST_UPDATED_BY TRANS_CNT DELETE_MARK TEXT_CODE OPEN_BALANCE NOT NULL NOT NULL NULL NOT NULL NULL NULL NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) NUMBER CUSTPRICE_CLASS EMAIL_ADDRESS LOCKBOX_CODE EDI_FLAG USE_PROFILE NULL NULL NULL NOT NULL NOT NULL VARCHAR2(8) VARCHAR2(70) VARCHAR2(8) NUMBER(5) NUMBER(5) RESTRICT_TO_CUSTITEMS NOT NULL NUMBER(5) SOCONFIRM_IND NOT NULL NUMBER(5) PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE REQUEST_ID ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NUMBER(15) NUMBER(15) DATE NUMBER(15) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) Standard who column Standard who column Not currently used Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Contains current open order balance (value of unshipped orders). This amount is updated during order entry and shipping for use in credit checking under oracle financials. Balance is incremented for new open orders and decremented when an Class to group customers for pricing purposes. Used in pricing tables. Fk to op_cprc_cls. Freeform text to store email address for customer. Default lockbox code where customer remits payment. Fk to op_lkbx_mst. For future use; identifies if customer uses edi. Indicates if order profile should be used to create orders for customer. Values are: 0 means that order profile is not used automatically when an order is taken for the customer 1 means that the order profile is automatically copied inwh Indicates if customer can order items which are not defined in the customer-item table. Value of 1 means that only items defined in the customer-item table can be ordered by this customer. Value of 0 means that any items canbe ordered b Indicates if customer requires a sales order confirmation. Value of 0 means that no confirmation is required; value of 1 means that a sales order confirmation must be sent. Indicator will be used in the future. Concurrent who column Concurrent who column Concurrent who column Concurrent who column Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-135 Table and View Definitions ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY LOWEST_PRICE_IND NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) NUMBER(5) Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Value of 0 (zero) means that normal pricing rules are in effect; value of 1 (one) means that pricing of an order for this customer will select the lowest price from effective pricelists established for this customer. Indexes Index Name OP_CUST_MSTI2 Index Type NOT UNIQUE Sequence 1 2 3 4 5 6 Column Name CUSTSORT_NO CUST_NO CUST_NAME DELETE_MARK INACTIVE_IND CUST_ID CUST_ID CO_CODE CUST_NO OP_CUST_MST_PK OP_CUST_MST_UC1 UNIQUE UNIQUE 10 1 2 Sequences Sequence GEM5_CUST_ID_S Derived Column CUST_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-136 OPM Inventory Technical Reference Manual Table and View Definitions OP_ORDR_DTL This table contains the lines for Orders and Shipments. This is the main detail table in OPM Order Fulfillment. When an order is entered (or copied from either a profile, existing sales order or blanket order), order line information is obtained from the source (either user, profile, etc.) and shipping and invoicing information is left blank. Neworder lines contain the ORDER_QTY (and unit of measure and secondary quantity, if applicable). The SHIP_QTY’s default to the ORDER_QTY’s. When the order line is placed on a shipment, then the corresponding shipping information iscompleted, such as BOL_ID, BOLLINE_NO, et, and the SHIP_QTY’s are updated for the quantity shipped. If the SHIP_QTY’s are less than the ORDER_QTY’s, then a backorder is created. A backorder line is inserted as another open order line, with the same ORDER_ID and LINE_NO, but a different LINE_ID. In addition, the BACKORDER_IND is set to1. The ORDER_QTY’s and SHIP_QTY’s equal the quantity which was not shipped (still open). Also, the BOL_ID, BOLLINE_NO do not exist until the backorder is placed on another shipment. At any point in time, only ONE open (LINE_STATUS = 0) order line will exist for any combination of ORDER_ID and LINE_NO. Once an order line is placed on a shipment, the LINE_STATUS becomes 10 (picked) and after shipment, it becomes 20 (shipped). Information on the order header (in table OP_ORDR_HDR) is used initially as a default for the order lines. Once an order is saved, each order line becomes, in effect, its own order, with all information used from the line. Foreign Keys Primary Key Table GL_CURR_MST GL_CURR_MST IC_ITEM_MST IC_WHSE_MST IC_WHSE_MST OP_BILL_LAD OP_COMMISSION OP_CUST_CON OP_CUST_MST OP_CUST_MST OP_CUST_MST Primary Key Column CURRENCY_CODE CURRENCY_CODE ITEM_ID WHSE_CODE WHSE_CODE BOL_ID COMMISSION_CODE CONTACT_ID CUST_ID CUST_ID CUST_ID Foreign Key Column BILLING_CURRENCY BASE_CURRENCY ITEM_ID FROM_WHSE TO_WHSE BOL_ID COMMISSION_CODE CONTACT_ID SHIPCUST_ID CONSIGNEE_ID ULTIMATE_SHIPCUST_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-137 Table and View Definitions OP_CUST_MST OP_FOBC_MST OP_FRGT_MTH OP_GNRC_ITM OP_HOLD_CDS OP_LKBX_MST OP_ORDR_HDR OP_ORDR_STS_B OP_PKGD_ITM OP_PORT_MST OP_PORT_MST OP_PRCE_CDS OP_PRCE_EFF OP_PRSL_DTL OP_SHIP_MST OP_SHIP_MTH OP_SLSR_MST OP_TERM_MST OP_TEXT_HDR QC_GRAD_MST SY_ADDR_MST SY_REAS_CDS SY_UOMS_MST SY_UOMS_MST SY_UOMS_MST SY_UOMS_MST SY_UOMS_MST CUST_ID FOB_CODE FRTBILL_MTHD GENERIC_ID HOLDREAS_CODE LOCKBOX_CODE ORDER_ID ORDER_STATUS SALESPKG_ID PORT_CODE PORT_CODE PRICEREAS_CODE PRICEFF_ID PRESALES_ORDLINE_ID SHIPPER_CODE SHIP_MTHD SLSREP_CODE TERMS_CODE TEXT_CODE QC_GRADE ADDR_ID REASON_CODE UM_CODE UM_CODE UM_CODE UM_CODE UM_CODE SOLDTOCUST_ID FOB_CODE FRTBILL_MTHD GENERIC_ID HOLDREAS_CODE LOCKBOX_CODE ORDER_ID LINE_STATUS SALESPKG_ID EMBARKATION_PORT DEBARKATION_PORT PRICEREAS_CODE PRICEFF_ID PRESALES_ORDLINE_ID SHIPPER_CODE SHIP_MTHD SLSREP_CODE TERMS_CODE TEXT_CODE QC_GRADE_WANTED SHIPADDR_ID CHANGEREAS_CODE PRICE_UM ORDER_UM1 ORDER_UM2 SHIPWT_UM SHIPVOLUME_UM Column Descriptions Name PRICE_ID Null NULL Type NUMBER(10) Description The specific item price that was used to arrive at the net_price. fk to op_prce_itm or op_cntr_ dtl. Indicates if user selected price from list of offerred pricelists/contracts. 0 means no user selection done; 1 means user selected pricelist. Reference to order which contains this line; fk to op_ordr_hdr. PRICE_SELECTED_IND NULL NUMBER(5) ORDER_ID NOT NULL NUMBER(10) Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-138 OPM Inventory Technical Reference Manual Table and View Definitions LINE_ID LINE_NO (PK) NOT NULL NOT NULL NULL NULL NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(5) INVOICE_ID INVOICELINE_NO BOL_ID BOLLINE_NO RETURN_ID ITEM_ID GENERIC_ID NULL NULL NULL NOT NULL NULL NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(10) NUMBER(10) LINE_DESC NOT NULL VARCHAR2(70) SALESPKG_ID LINE_COMMENT ORDER_QTY1 ORDER_QTY2 NULL NULL NOT NULL NULL NUMBER(10) VARCHAR2(70) NUMBER NUMBER ORDER_UM1 NOT NULL VARCHAR2(4) ORDER_UM2 SHIPADDR_ID NULL NULL VARCHAR2(4) NUMBER(10) SHIPCUST_ID NOT NULL NUMBER(10) SHIPPER_CODE CONSIGNEE_ID NULL NULL VARCHAR2(4) NUMBER(10) SHIP_MTHD TO_WHSE NULL NULL VARCHAR2(4) VARCHAR2(4) FROM_WHSE FOB_CODE NULL NULL VARCHAR2(4) VARCHAR2(4) Surrogate id for table; uniquely identifies each row. Line number assigned by system for this order line. Reference to invoice which contains this line. This functionality is no longer supported. Line number assigned by the system for the invoice; this functionality is no longer supported. Reference to shipment which contains this line; fk to op_bill_lad. Line number assigned by the system for the shipment on which this order line is contained. Reference to the return; not currently supported; for future use Ordered item; fk to ic_item_mst. Generic or customer item number reference. If o then no generic or customer item entered. Fk to op_gnrc_itm. Description of item ordered; defaults from op_ gnrc_itm, op_pkgd_itm or ic_item_mst; user can modify. Packaged item id; fk to op_pkgd_itm. Freeform text/comments. Quantity ordered in unit of measure indicated in column order_um1. Quantity ordered in unit of measure indicated in column order_um2. This uom is always equivalent to the item_um2 (from ic_item_mst) of an item. User can edit order_qty2 within tolerances established in ic_item_mst for item. Order unit of measure. Defaults to item_um1 of an item, but can be modified by user. Fk to sy_ uoms_mst. Second unit of measure. Defaults to item_um2 of an item and can not be changed by user. Shipping destination. Defaults to addr_id from op_cust_mst for shipcust and and can be modified by user. Default shipping (destination) customer from header. Fk to op_cust_mst. Can be overridden by user. Defaults from header but may be overridden; fk to op_ship_mst. Ultimate customer when consignment inventory is shipped to a gemms-managed warehouse; defaults from header; fk to op_cust_mst. Defaults from header but can be overridden; fk to op_ship_mth. Destination warehouse for intra-enterprise shipments and consignments; defaults from header and required on interplant or consignment orders; fk to ic_whse_mst. Shipping warehouse; defaults from header; fk to ic_whse_mst. Defaults from header but may be overridden; fk Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-139 Table and View Definitions NET_WT NOT NULL NUMBER DEBARKATION_PORT SOLDTOCUST_ID NULL NULL VARCHAR2(8) NUMBER(10) TARE_WT NOT NULL NUMBER PALLET_WT NOT NULL NUMBER FRTBILL_WT NOT NULL NUMBER SHIP_STATUS NOT NULL NUMBER(5) SHIPWT_UM NOT NULL VARCHAR2(4) BASE_PRICE NOT NULL NUMBER NET_PRICE NOT NULL NUMBER EXTENDED_PRICE PRICE_UM NOT NULL NOT NULL NUMBER VARCHAR2(4) BILLING_CURRENCY NOT NULL VARCHAR2(4) BASE_CURRENCY NOT NULL VARCHAR2(4) EXCHANGE_RATE NOT NULL NUMBER TERMS_CODE BILL_QTY NOT NULL NULL VARCHAR2(4) NUMBER to op_fobc_mst. Weight of item in shipwt_um unit of measure not including shipping container. Calculated by system. Port of arrival for overseas shipments (where goods are unloaded); fk to op_port_mst. Third-party customer to the sales transaction may be a buying cooperative, distributor, etc. Defaults from header using customer association, if established Weight of packaging materials for item in shipwt_um unit of measure. Defaults from packaged item, if entered. Can be entered/overridden by user. Pallet weight for item being shipped in shipwt_ um. Defaults from packaged item, if entered, otherwise entered/overridden by user. Freight bill weight equals the net_wt + tare_wt + pallet_wt. In shipwt_um and can be modified by user in shipping. Weight for freight billing purposes. Indicates status of order line re: shipping. Valid values are: 0 - line has not been shipped, 10 - shipment has been picked (shipment created for line), 20 - shipment has been released (shipped). Defaults from system variable for ship weight uom. Can not be changed by user. Unit of measure for shipping weights. Fk to sy_uoms_mst. Base price retrieved from the pricing tables (from the column base_price) for the item/customer/whse, etc. on this order line. Price is converted to price_um and billing_currency for this line. Defaults from system_price, but can be overridden by user. This is the price that the user will be charged for this order line. In price_um and billing_currency. Total price for line which equals: order_qty1 multiplied by net_price. Unit of measure retrieved from pricing tables. This is the uom in which the price was established in the pricelist and which may have necessitated a conversion of the price to the order_ um1. Fk to sy_uoms_mst. This uom corresponds to bo Currency that customer will be billed and currency that order is denominated. Defaults from header. Fk to gl_curr_mst. Currency of organization identified with order. Defaults from gl_plcy_mst and can not be modified by user. Exchange rate between billing_currency and base_ currency. Defaults to 1 if the two currencies are the same, otherwise retrieved from gl_xchg_ rte. Code to uniquely identify payment terms Invoice qty used in previous versions; column is Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-140 OPM Inventory Technical Reference Manual Table and View Definitions REQUESTED_SHIPDATE PROMISED_SHIPDATE LIST_PRICE NOT NULL NOT NULL NOT NULL DATE DATE NUMBER SCHED_SHIPDATE NOT NULL DATE ACTUAL_SHIPDATE REQUIRED_DLVDATE NULL NOT NULL DATE DATE ACTUAL_DLVDATE BASE_COST QC_GRADE_WANTED NULL NULL NULL DATE NUMBER VARCHAR2(4) COMPLETED_IND NOT NULL NUMBER(5) REPRICE_SHIPMENT NULL NUMBER(5) SLSREP_CODE COMMISSION_CODE NET_COMMISSION NULL NULL NULL VARCHAR2(8) VARCHAR2(8) NUMBER MUL_DIV_SIGN NOT NULL NUMBER(5) NET_TAX NOT NULL NUMBER BACKORDREAS_CODE BATCH_ID LOCKBOX_CODE HOLDREAS_CODE HOLD_EXPIRATION NULL NULL NULL NOT NULL NOT NULL VARCHAR2(4) NUMBER(10) VARCHAR2(8) VARCHAR2(4) DATE not supported or used. Requested shipping date, defaults from header. Promised ship date, defaults from header. Price obtained from pricing tables designated as ”list prices”. Price is converted to order_um1 and billing_currency on this order line. Scheduled ship date, defaults from header. This date becomes the transaction date for pending inventory transactions for this order line. Actual ship date. Date is required in shipping before a shipment is released. Required delivery date, defaults from header. This date can be used to calculate the sched_ shipdate. Actual delivery date to customer. For future use. Not used. Qc grade requested by user for this item. Used to assign lots to this order line during automatic lot allocation, otherwise, a guide to the user for manual allocation. Only applicable for grade-controlled items. Indicates ”completed” status of order line. Valid values are: 0 - line has not been shipped; 1 = line has been shipped (released), -1=line has been cancelled. Indicator to reprice the order line based on the shipped qty ( during shipping). A value of 0 means that the line will not be repriced during shipping, a value of 1 means that the line will be repriced during shipping. For future use. Defaults from header but may be overridden; fk to op_slsr_mst. Commission code defaulted from header; fk to op_ commission. Overrides code in op_ordr_hdr. Net commission. Defaults to 0. For future use. Intended to contain commission calculated for this line. Defaults to 0. Retrieved from gl_xchg_rte table if exchange_rate needs to be retrieved. Indicates if exchange rate is multiplied or divided into billing currency to arrive at base currency. Value of 0 means multiply; value of 1 meansdi Net tax. Defaults to 0. If automatic tax calculation in use, then field contains tax amount calculated for this line. Reason code to indicate why a line was backordered. Fk to sy_reas_cds. Reference to production batch that will fulfill this order line - for future use. Remit-to lockbox code. Defaults from header. Defaults from header but may be overridden; hold reason code; fk to op_hold_cds. Hold expiration date. Date when hold indicated by hold reason code will expire. Defaults to system ”max date”. Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-141 Table and View Definitions ALLOC_IND NOT NULL GL_POSTED_IND NOT NULL CREATION_DATE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY DELETE_MARK TRANS_CNT TEXT_CODE CUSTPO_NO EXPORTED_DATE COA_REQUIRED NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL SYSTEM_PRICE NOT NULL PRESALES_ORDLINE_ID NULL PRICEREAS_CODE NULL LINE_STATUS PRICEFF_ID LINE_TYPE ALLOC_QTY INVENTORY_QTY BACKORDER_FLAG NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL IN_USE LAST_UPDATE_LOGIN ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 NULL NULL NULL NULL NULL Indicates if inventory has been allocated for the line. Valid values are: 0 - inventory has either been partially or not allocated to the line; 1 means that the line has been fully allocated. NUMBER(10) Indicates if this shipment line has been posted to the mfg controller subsidiary ledger. 0 means that the shipment line has been posted; 1 means that the shipment line has not been posted. DATE Standard who column DATE Standard who column NUMBER(15) Standard who column NUMBER(15) Standard who column NUMBER(5) Standard: 0=Active record (default); 1=Marked for (logical) deletion. NUMBER(10) Not currently used NUMBER(10) ID which joins any rows of text in this table to the Text Table for this Module VARCHAR2(40) Customer purchase order number; defaults from header. DATE Date when the shipment line was exported to oracle financials a/r system for invoicing. NUMBER(5) Flag to indicate if certificate of analysis is required for this line item. Defaults from header. Value of 0 means that no coa is required; value of 1 means that a coa must be created and sent to customer. For future use. NUMBER The price obtained for this line item from the pricing tables, taking into account, the item, customer, whse, qc grade, price classes and order quantity. Converted to order_um1 and billing_ currency on the line. NUMBER(10) References origin of line, if created from a presales order, i.e., an order profile or blanket order. VARCHAR2(4) Reason code for price change (changing of price to one that is different from the system-generated price). Fk to op_prce_cds. NUMBER(5) Indicates status of line. Fk to op_ordr_status. NUMBER(10) Reference to price effectivity used to compute system- generated price. Fk to op_prce_eff. NUMBER(5) Not used. NUMBER Quantity that has been allocated for this line in order_um1. NUMBER Order_qty1 converted to item_um1 (the first inventory unit of measure). NUMBER(5) Indicates if this line is a backorder line. Value of 0 means that this is not a backorder line; value of 1 means that this is a backorder line. NUMBER(10) Not currently used NUMBER(15) Standard who column VARCHAR2(240) Descriptive flexfield segment VARCHAR2(240) Descriptive flexfield segment VARCHAR2(240) Descriptive flexfield segment NUMBER(5) Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-142 OPM Inventory Technical Reference Manual Table and View Definitions ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE REQUEST_ID SHIP_QTY1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) NUMBER(15) NUMBER(15) DATE NUMBER(15) NUMBER EMBARKATION_PORT NULL VARCHAR2(8) SHIP_QTY2 NULL NUMBER SHIP_VOLUME NOT NULL NUMBER ULTIMATE_SHIPCUST_ID NULL NUMBER(10) CONTACT_ID NULL NUMBER(10) Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Concurrent who column Concurrent who column Concurrent who column Concurrent who column Quantity shipped on this order line in order_um1 unit of measure. Defaults in order entry to order_qty1 and is updated in shipping to the actual ship quantity. Port of leave for overseas shipments (port where goods are loaded). Defaults from header; fk to op_port_mst. Quantity shipped in order_um2 unit of measure. Defaults in order entry to order_qty2 and is updated in shipping to the actual ship quantity. Volume computed by system using order_qty1 and converting it to shipvolume_um unit of measure or by using defaults established for the packaged item. This value can not be updated by the user in shipping or oe. Ultimate shipping (destination) customer when shipcust is a freight forwarder or distribution center; defaults from header; fk to op_cust_mst. Surrogate from customer contact table. Contact for order line, defaults from header and can be Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-143 Table and View Definitions MINSHIP_PCT NOT NULL NUMBER SHIPVOLUME_UM NOT NULL VARCHAR2(4) FRTBILL_MTHD MAXSHIP_PCT NULL NOT NULL VARCHAR2(4) NUMBER NET_FREIGHT NULL NUMBER FRT_COST CHANGEREAS_CODE NULL NULL NUMBER VARCHAR2(4) changed for a line. Minimum percentage below the order qty that is required to ship to the customer and close the order line. This number defaults from either the order profile, blanket order or customer-item table and can not be modified by the user. Store Defaults from system variable for shipvolume_um. Unit of measure for shipping volume; can not be changed by user. Fk to sy_uoms_mst. Defaults from header but may be overridden; fk to op_frgt_mth. Maximum percentage over the order qty that can be shipped to the customer. This number defaults from either the order profile, blanket order or customer-item tables and can not be changed by the user. Stored as decimal. Net freight amount; not currently used. Intended to contain the amount of freight billed/allocated for this line. For future use Reason code for line changes; fk to sy_reas_cds Indexes Index Name OP_ORDR_DTLI1 OP_ORDR_DTLI2 OP_ORDR_DTLI3 OP_ORDR_DTLI4 Index Type NOT UNIQUE NOT UNIQUE NOT UNIQUE NOT UNIQUE Sequence 1 1 1 1 2 Column Name BOL_ID ORDER_ID SHIPCUST_ID ORDER_ID LINE_NO LINE_ID ORDER_ID LINE_ID OP_ORDR_DTL_PK OP_ORDR_DTL_UK UNIQUE UNIQUE 10 10 20 Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-144 OPM Inventory Technical Reference Manual Table and View Definitions OP_ORDR_HDR This table is the Sales Order Header table and contains default information for the order. The only data which does not exist on each order line is the BILLCUST_ID, ORDER_DATE and ORDER_TYPE. There is only one Bill-To customer for an order. Most of the remaining information is contained on each sales order line. The ORDER_TYPE controls the processing of the order and is used to designate different types of orders for various purposes. Foreign Keys Primary Key Table GL_CURR_MST IC_WHSE_MST IC_WHSE_MST OP_COMMISSION OP_CUST_CON OP_CUST_MST OP_CUST_MST OP_CUST_MST OP_CUST_MST OP_CUST_MST OP_FOBC_MST OP_FRGT_MTH OP_HOLD_CDS OP_LKBX_MST OP_ORDR_STS_B OP_ORDR_TYP_B OP_PORT_MST OP_PORT_MST OP_PRSL_HDR OP_SHIP_MST OP_SHIP_MTH OP_SLSR_MST OP_TERM_MST OP_TEXT_HDR SY_ADDR_MST Primary Key Column CURRENCY_CODE WHSE_CODE WHSE_CODE COMMISSION_CODE CONTACT_ID CUST_ID CUST_ID CUST_ID CUST_ID CUST_ID FOB_CODE FRTBILL_MTHD HOLDREAS_CODE LOCKBOX_CODE ORDER_STATUS ORDER_TYPE PORT_CODE PORT_CODE PRESALES_ORD_ID SHIPPER_CODE SHIP_MTHD SLSREP_CODE TERMS_CODE TEXT_CODE ADDR_ID Foreign Key Column BILLING_CURRENCY FROM_WHSE TO_WHSE COMMISSION_CODE CONTACT_ID SOLDTOCUST_ID CONSIGNEE_ID BILLCUST_ID SHIPCUST_ID ULTIMATE_SHIPCUST_ID FOB_CODE FRTBILL_MTHD HOLDREAS_CODE LOCKBOX_CODE ORDER_STATUS ORDER_TYPE EMBARKATION_PORT DEBARKATION_PORT PRESALES_ORD_ID SHIPPER_CODE SHIP_MTHD SLSREP_CODE TERMS_CODE TEXT_CODE SHIPADDR_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-145 Table and View Definitions SY_ORGN_MST SY_REAS_CDS ORGN_CODE REASON_CODE ORGN_CODE CHANGEREAS_CODE Column Descriptions Name ORDER_ID (PK) Null NOT NULL NOT NULL NULL NULL NOT NULL NOT NULL NULL Type NUMBER(10) NUMBER(5) NUMBER(10) VARCHAR2(8) NUMBER(10) NUMBER(10) NUMBER(10) Description Surrogate key for order. Alternate primary key is orgn_code + order_no + release_no + order_type Order type; fk to op_ordr_type Not currently used Port of entry/port shipment is unloaded; fk to op_port_mst. Default third party to be invoiced; fk to op_ cust_mst. Default shipping (destination) customer; fk to op_cust_mst. Shipping destination; defaults from op_cust_ mst.addr_id for shipcust_id of this record; fk to sy_addr_mst. Fk to op_ship_mst; sets defaults for lines. Third-party customer to the sales transaction. Defaults from customer association, if established. Customer purchase order number Customer contact reference. Fk to op_cust_con. Fk to op_slsr_mst. Sales rep for order. Sets defaults for lines. Fk to op_commission Commission code default. Currency that customer is billed in and currency of order. Fk to gl_curr_mst. Shipping warehouse; fk to ic_whse_mst. Destination warehouse for intra-enterprise shipments and consignments. Fk to ic_whse_mst. Fk to op_fobc_mst. Free-on-board code default. Payment terms code; fk to op_term_mst. Organization code; all sales orders are organization specific; fk to sy_orgn_mst. Freeform text/comments. Indicates ”completed” status of order; valid values are: 0 means that order is open and has not been completely shipped; 1 means that the order has been completely shipped; -1 means that the order has been cancelled. Registered indicator - not currently used. Print count is incremented when sales order confirmation is printed to a printer. Shows number of times that this report has been printed for the order. Remit-to lockbox; not modified in sales order, but carried through to invoicing. Fk to op_lkbx_ mst. Status of order. Fk to op_ordr_status. Indicates if certificate of analysis is required ORDER_TYPE IN_USE DEBARKATION_PORT BILLCUST_ID SHIPCUST_ID SHIPADDR_ID SHIPPER_CODE SOLDTOCUST_ID NULL NULL VARCHAR2(4) NUMBER(10) CUSTPO_NO CONTACT_ID SLSREP_CODE COMMISSION_CODE BILLING_CURRENCY FROM_WHSE TO_WHSE FOB_CODE TERMS_CODE ORGN_CODE ORDER_COMMENT COMPLETED_IND NULL NULL NULL NULL NOT NULL NULL NULL NULL NULL NOT NULL NULL NOT NULL VARCHAR2(40) NUMBER(10) VARCHAR2(8) VARCHAR2(8) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(70) NUMBER(5) REGISTERED_IND PRINT_COUNT NULL NOT NULL NUMBER(5) NUMBER(5) LOCKBOX_CODE NULL VARCHAR2(8) ORDER_STATUS COA_REQUIRED NOT NULL NULL NUMBER(5) NUMBER(5) Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-146 OPM Inventory Technical Reference Manual Table and View Definitions DATE_PRINTED ORDER_NO HOLDREAS_CODE EMBARKATION_PORT HOLD_EXPIRATION ORDER_DATE REQUESTED_SHIPDATE PROMISED_SHIPDATE SCHED_SHIPDATE NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL DATE VARCHAR2(32) VARCHAR2(4) VARCHAR2(8) DATE DATE DATE DATE DATE REQUIRED_DLVDATE NOT NULL DATE RELEASE_NO CREATION_DATE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY LAST_UPDATE_LOGIN TEXT_CODE DELETE_MARK PRESALES_ORD_ID NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NUMBER(5) DATE DATE NUMBER(15) NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) NOT NULL NULL TOTAL_OPEN_AMOUNT NOT NULL NUMBER SOURCE_IND NULL NUMBER(5) EDI_TRANS_COUNT CONSIGNEE_ID ULTIMATE_SHIPCUST_ID NULL NULL NULL NUMBER(5) NUMBER(10) NUMBER(10) for order. Value of 0 means that no coa is required; value of 1 means that coa is required; defaults to 0. Sets defaults for lines. For future use. Contains last date that the sales order confirmation was printed. Order number assigned by either system or user. Unique within an organization. Hold reason code default; fk to op_hold_cds. Port of loading/port of discharge for shipments; fk to op_port_mst. Hold expiration date; defaults to system maximum date and can be modified by user. Date that order was placed. Date requested for shipment. Default for order lines. Promised ship date. Default for all order lines. Scheduled ship date. Default for all order lines. Date used as transaction date for pending inventory transactions for the order (unless changed on one or more order lines). Required delivery date. Date required by customer. Default for all order lines. Date can be used to calculate scheduled ship date. Release number (for future use). Standard who column Standard who column Standard who column Standard who column Standard who column ID which joins any rows of text in this table to the Text Table for this Module Logical delete. 0 means active record; 1 means ”deleted”. Link to origin of sales order if created from a presales order, i.e., an order profile or blanket order. Total unshipped amount of the order. Updated by order entry and shipping. For a new order, this total equals the total order amount. As lines are shipped, they are decremented from this total. Field used for credit checking withOracle AR. Indicates the source of the order. Normal value is 0, however, if order originates from a Blanket Sales Order, then the value of this column is 2. If the order originates from an EDI Inbound PO, then the value is 3. For future use with EDI orders Ultimate customer when consignment inventory is shipped to a gemms-managed warehouse. Ultimate shipping (destination) customer when shipcust is a freight forwarder or distribution center; fk to op_cust_mst. Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-147 Table and View Definitions SHIP_MTHD ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY ATTRIBUTE6 CHANGEREAS_CODE FRTBILL_MTHD NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VARCHAR2(4) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) VARCHAR2(240) VARCHAR2(4) VARCHAR2(4) Fk to op_ship_mth; sets defaults for lines. Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Reason code for changes to an order; fk to sy_ reas_cds. Freight bill method default; fk to op_frgt_mth. Indexes Index Name OP_ORDR_HDR_I1 OP_ORDR_HDR_PK OP_ORDR_HDR_U1 Index Type NOT UNIQUE UNIQUE UNIQUE Sequence 5 10 10 20 30 40 Column Name ORDER_NO ORDER_ID ORDER_TYPE ORGN_CODE ORDER_NO RELEASE_NO Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-148 OPM Inventory Technical Reference Manual Table and View Definitions Sequences Sequence GMO_ORDER_ID_S Derived Column ORDER_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-149 Table and View Definitions OP_PORT_MST Shipping/receiving ports are established in this table. Port names are used in both Order Entry/Shipping and Receiving. Foreign Keys Primary Key Table OP_TEXT_HDR SY_ADDR_MST Primary Key Column TEXT_CODE ADDR_ID Foreign Key Column TEXT_CODE ADDR_ID Column Descriptions Name PORT_CODE (PK) PORT_DESC ADDR_ID PORT_COMMENT CREATION_DATE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY LAST_UPDATE_LOGIN TRANS_CNT TEXT_CODE DELETE_MARK Null NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NULL NULL NOT NULL Type VARCHAR2(8) VARCHAR2(70) NUMBER(10) VARCHAR2(70) DATE DATE NUMBER(15) NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(10) NUMBER(5) Description Uniquely identifies port. Description of port. Port address; fk to sy_addr_mst. Freeform text/comments. Standard who column Standard who column Standard who column Standard who column Standard who column Not currently used ID which joins any rows of text in this table to the Text Table for this Module Standard: 0=Active record (default); 1=Marked for (logical) deletion. Indexes Index Name OP_PORT_MST_PK Index Type UNIQUE Sequence 10 Column Name PORT_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-150 OPM Inventory Technical Reference Manual Table and View Definitions PM_BTCH_HDR Batch/FPO header. This table stores the header information for both Firm Planned Orders (FPO’s) and Batches, including the plant, the effectivity, formula, and routing on which the batch was based, and the planned and actual start and completion dates. Foreign Keys Primary Key Table FM_FORM_EFF FM_FORM_MST FM_ROUT_HDR IC_WHSE_MST PM_MATL_DTL PM_TEXT_HDR SY_ORGN_MST Primary Key Column FMEFF_ID FORMULA_ID ROUTING_ID WHSE_CODE LINE_ID TEXT_CODE ORGN_CODE Foreign Key Column FMEFF_ID FORMULA_ID ROUTING_ID WIP_WHSE_CODE PARENTLINE_ID TEXT_CODE PLANT_CODE Column Descriptions Name BATCH_ID (PK) PLANT_CODE BATCH_NO BATCH_TYPE PROD_ID Null NOT NULL NOT NULL NOT NULL NOT NULL NULL Type NUMBER(10) VARCHAR2(4) VARCHAR2(32) NUMBER(5) NUMBER(10) Description Surrogate key to FPO or batch. Production organization in which the batch or FPO is/was/will be run. Batch or FPO number. Differentiates between Firm Planned Order(10) and batches (0). Reserved for future use. Used for tying multiple batches to a campaign/prod ord. When used, validate against PM_PROD_MST. Reserved for future use. Batch sequence in campaign/prod order. Surrogate key to formula on which batch or FPO is based. Surrogate key to routing used. Planned release date/time for batch. Actual start date/time for batch. Required batch completion date/time, notational. Planned batch completion date/time. Actual completion date. Batch or FPO status. -3=Converted FPO, -1=Cancelled,1=Pending; 2=WIP; 3=Certified; 4=Closed. Not currently used. Not currently used. Number of times batch ticket was printed. Not currently used Surrogate key to the formula effectivity the batch or FPO was based on. PROD_SEQUENCE FORMULA_ID ROUTING_ID PLAN_START_DATE ACTUAL_START_DATE DUE_DATE EXPCT_CMPLT_DATE ACTUAL_CMPLT_DATE BATCH_STATUS PRIORITY_VALUE PRIORITY_CODE PRINT_COUNT IN_USE FMEFF_ID NOT NULL NOT NULL NULL NOT NULL NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NUMBER(5) NUMBER(10) NUMBER(10) DATE DATE DATE DATE DATE NUMBER(5) NUMBER VARCHAR2(4) NUMBER(5) NUMBER(10) NUMBER(10) Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-151 Table and View Definitions FMCONTROL_CLASS WIP_WHSE_CODE BATCH_CLOSE_DATE LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE LAST_UPDATE_LOGIN CREATED_BY DELETE_MARK TEXT_CODE PARENTLINE_ID NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NULL NULL VARCHAR2(8) VARCHAR2(4) DATE DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(5) NUMBER(10) NUMBER(10) ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Not currently used. Warehouse used to cost production activity. Date/time the batch was closed. Standard who column Standard who column Standard who column Standard who column Standard who column Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module For phantom batches. Surrogate key to the batch ingredient line for which this phantom batch produces product. Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Indexes Index Name PM_BTCH_HDRI2 Index Type NOT UNIQUE Sequence 1 Column Name PLANT_CODE 2BATCH_STATUS 3 BATCH_ Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-152 OPM Inventory Technical Reference Manual Table and View Definitions NO PM_BTCH_HDRI3 PM_BTCH_HDR_PK PM_BTCH_HDR_U1 NOT UNIQUE UNIQUE UNIQUE 1 10 10 20 30 PARENTLINE_ID BATCH_ID PLANT_CODE BATCH_NO BATCH_TYPE Sequences Sequence GEM5_BATCH_ID_S Derived Column BATCH_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-153 Table and View Definitions PM_MATL_DTL Batch or FPO material details. This table contains data on the products, ingredients, and by products of an FPO or batch, including the planned and actual quantity. Foreign Keys Primary Key Table IC_ITEM_MST PM_BTCH_HDR PM_TEXT_HDR SY_UOMS_MST SY_UOMS_MST Primary Key Column ITEM_ID BATCH_ID TEXT_CODE UM_CODE UM_CODE Foreign Key Column ITEM_ID BATCH_ID TEXT_CODE ITEM_UM ITEM_UM2 Column Descriptions Name BATCH_ID (PK) LINE_ID (PK) FORMULALINE_ID LINE_NO ITEM_ID LINE_TYPE PLAN_QTY Null NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL Type NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(5) NUMBER Description Surrogate key to batch. Key that uniquely identifies a batch or FPO line. Surrogate key to the formula line on which the batch line was based, if any. Sequential line number for each line type in a batch or FPO. Surrogate key to the item that is a product, ingredient, or byproduct. -1=Ingredient; 1=Product; 2=Byproduct. The quantity that this line was planned to produce or consume, in the batch UOM (item_um column). The quantity that this line was planned to produce or consume, in the secondary UOM of an item(for dual UOM items). UOM in which planned and actual qty is entered on the batch. Secondary UOM of item. Sum (trans_qty) of completed transactions for line, in the batch UOM. Sum (trans_qty) of completed transactions for line, in the secondary UOM of an item(for dual UOM items). Release (or certification) type. 0=Automatic release (certification), 1=Manual release (certification). Scrap factor decimal used to a trans_qty that allows for scrap. 0=Fixed qty (non-scalable), 1=Linear scaling. Phantom indicator. 0=not a phantom, 1=automatic phantom replacement, 2=manual phantom. For products, fraction of cost allocated to this product. PLAN_QTY2 NULL NUMBER ITEM_UM ITEM_UM2 ACTUAL_QTY ACTUAL_QTY2 NOT NULL NULL NOT NULL NULL VARCHAR2(4) VARCHAR2(4) NUMBER NUMBER RELEASE_TYPE NOT NULL NUMBER(5) SCRAP_FACTOR SCALE_TYPE PHANTOM_TYPE COST_ALLOC NOT NULL NOT NULL NOT NULL NULL NUMBER NUMBER(5) NUMBER(5) NUMBER Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-154 OPM Inventory Technical Reference Manual Table and View Definitions ALLOC_IND COST TEXT_CODE PHANTOM_ID CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY IN_USE ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY LAST_UPDATE_LOGIN NOT NULL NULL NULL NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NUMBER(5) NUMBER NUMBER(10) NUMBER(10) DATE NUMBER(15) DATE NUMBER(15) NUMBER(10) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) NUMBER(15) Allocation indicator. 0=Not fully allocated, 1=Fully allocated. Not currently used. ID which joins any rows of text in this table to the Text Table for this Module For phantoms. Surrogate key of the phantom batch which produces the ingredient. Standard who column Standard who column Standard who column Standard who column Not currently used Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Standard who column Indexes Index Name PM_MATL_DTLI1 PM_MATL_DTLI2 Index Type UNIQUE NOT UNIQUE Sequence 10 1 Column Name LINE_ID BATCH_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-155 Table and View Definitions 2 3 PM_MATL_DTL_PK UNIQUE 10 20 LINE_TYPE LINE_NO BATCH_ID LINE_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-156 OPM Inventory Technical Reference Manual Table and View Definitions PO_ORDR_DTL PO_ORDR_DTL holds the details of your purchase order document. It stores various information such as ordered quantity, received quantity, the dual uoms of the item, freight details, terms and conditions of the PO, the promised delivery dates and requested delivery dates. Purchase orders are entered in the Oracle Purchasing application and transferred to this table via the CPG_PURCHASING_ INTERFACE table and a synchronization program run under the concurrent manager. Foreign Keys Primary Key Table IC_ITEM_MST IC_PRCH_CLS IC_WHSE_MST IC_WHSE_MST OP_FOBC_MST OP_FRGT_MTH OP_GNRC_ITM OP_SHIP_MST OP_SHIP_MTH OP_TERM_MST PO_BPOS_DTL PO_ORDR_HDR PO_TEXT_HDR PO_VEND_MST QC_GRAD_MST SY_ADDR_MST SY_REAS_CDS SY_REAS_CDS SY_UOMS_MST SY_UOMS_MST SY_UOMS_MST Primary Key Column ITEM_ID ICPURCH_CLASS WHSE_CODE WHSE_CODE FOB_CODE FRTBILL_MTHD GENERIC_ID SHIPPER_CODE SHIP_MTHD TERMS_CODE LINE_ID PO_ID TEXT_CODE VENDOR_ID QC_GRADE ADDR_ID REASON_CODE REASON_CODE UM_CODE UM_CODE UM_CODE Foreign Key Column ITEM_ID ICPURCH_CLASS FROM_WHSE TO_WHSE FOB_CODE FRTBILL_MTHD GENERIC_ID SHIPPER_CODE SHIP_MTHD TERMS_CODE BPO_LINE_ID PO_ID TEXT_CODE SHIPVEND_ID QC_GRADE_WANTED RECVADDR_ID CANCELLATION_CODE POHOLD_CODE ORDER_UM1 ORDER_UM2 PRICE_UM Column Descriptions Name PO_ID Null NOT NULL Type NUMBER(10) Description In po_ordr_hdr. Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-157 Table and View Definitions ACCT_MAP_IND NULL NUMBER(5) LINE_ID (PK) LINE_NO BPO_LINE_ID APINV_LINE_ID ITEM_ID GENERIC_ID ITEM_DESC ICPURCH_CLASS PO_STATUS ORDER_QTY1 ORDER_QTY2 ORDER_UM1 ORDER_UM2 RECEIVED_QTY1 RECEIVED_QTY2 NET_PRICE EXTENDED_PRICE PRICE_UM FROM_WHSE TO_WHSE RECV_LOCT RECVADDR_ID RECV_DESC SHIP_MTHD SHIPPER_CODE SHIPVEND_ID QC_GRADE_WANTED FRTBILL_MTHD TERMS_CODE POHOLD_CODE CANCELLATION_CODE FOB_CODE VENDSO_NO BUYER_CODE PROJECT_NO AGREED_DLVDATE EXPEDITE_DATE REQUESTED_DLVDATE REQUIRED_DLVDATE SCHED_SHIPDATE MATCH_TYPE EXPORTED_DATE CREATION_DATE CREATED_BY LAST_UPDATE_DATE NOT NULL NOT NULL NULL NULL NOT NULL NULL NOT NULL NULL NOT NULL NOT NULL NULL NOT NULL NULL NULL NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NULL NULL NULL NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) VARCHAR2(70) VARCHAR2(8) NUMBER(5) NUMBER NUMBER VARCHAR2(4) VARCHAR2(4) NUMBER NUMBER NUMBER NUMBER VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(16) NUMBER(10) VARCHAR2(40) VARCHAR2(4) VARCHAR2(4) NUMBER(10) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(32) VARCHAR2(35) VARCHAR2(16) DATE DATE DATE DATE DATE NUMBER DATE DATE NUMBER(15) DATE 0=gl. acct dist are not populated in po_dist_ dist, 1=gl acct. dist are populated in po_dist_ dtl Surrogate for access from other tables. Duplicates on partial recvs allowed. Fk to po_bpos_dtl. Reserved for future use. In ic_item_mst. If >0, in op_gnrc_item. Free form item description. Fk to ic_prch_cls. Determines the status of a purchase order: 0 open; 1 - cancelled; 2 - closed Quantity ordered. Original line only. Quantity ordered. Original line only. Uom of order_qty1. Used if order_qty2 >0. Total of receipts in order_um1. Total of receipts in order_um2. Price per price_um. Total price for line. Uom for pricing purposes. For intra-company transfers. Anticipated receiving warehouse. Anticipated receiving location. In sy_addr_mst. Usually warehouse address. Free form shipment destination. Anticipated ship method (op_ship_mth). Anticipated shipper (op_ship_mst). Shipping vendor id. Necessary quality grade. Fk to op_frgt_mth. Fk to op_term_mst. Fk to sy_reas_cds. Fk to sy_reas_cds. Fk to op_fobc_mst. Vendor sales order number. Buyer on PO Not currently supported Agreed delivery date. Date expedited. Requested delivery date. Required delivery date. Scheduled ship date. Reserved for future use Date that the transaction was exported to oracle finanacials. Defaults to sy$mindate. Standard who column Standard who column Standard who column Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-158 OPM Inventory Technical Reference Manual Table and View Definitions LAST_UPDATED_BY LAST_UPDATE_LOGIN TEXT_CODE TRANS_CNT ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(10) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Standard who column Standard who column ID which joins any rows of text in this table to the Text Table for this Module Not currently used Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Indexes Index Name PO_ORDR_DTL PO_ORDR_DTL_U1 Index Type UNIQUE UNIQUE Sequence 1 1 2 Column Name LINE_ID PO_ID LINE_NO Sequences Sequence GEM5_PO_LINE_ID_S GEM5_APINV_LINE_ID_S Derived Column LINE_ID APINV_LINE_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-159 Table and View Definitions PO_ORDR_HDR PO_ORDR_HDR holds the main header information of your Purchase order documents. This is the header block of the Purchase order transaction in OPM. Every purchasing document except blanket purchase orders has a row in this table. Purchase orders are entered in the Oracle Purchasing application and transferred to this table via the CPG_PURCHASING_INTERFACE table and a synchronization program run under the concurrent manager. Foreign Keys Primary Key Table GL_CURR_MST IC_PRCH_CLS IC_WHSE_MST IC_WHSE_MST OP_FOBC_MST OP_FRGT_MTH OP_SHIP_MST OP_SHIP_MTH OP_TERM_MST PO_BPOS_HDR PO_TEXT_HDR PO_VEND_MST PO_VEND_MST SY_ADDR_MST SY_ORGN_MST SY_REAS_CDS SY_REAS_CDS Primary Key Column CURRENCY_CODE ICPURCH_CLASS WHSE_CODE WHSE_CODE FOB_CODE FRTBILL_MTHD SHIPPER_CODE SHIP_MTHD TERMS_CODE BPO_ID TEXT_CODE VENDOR_ID VENDOR_ID ADDR_ID ORGN_CODE REASON_CODE REASON_CODE Foreign Key Column BILLING_CURRENCY ICPURCH_CLASS FROM_WHSE TO_WHSE FOB_CODE FRTBILL_MTHD SHIPPER_CODE SHIP_MTHD TERMS_CODE BPO_ID TEXT_CODE PAYVEND_ID SHIPVEND_ID RECVADDR_ID ORGN_CODE POHOLD_CODE CANCELLATION_CODE Column Descriptions Name ACCT_MAP_DATE PO_ID (PK) ORGN_CODE BPO_ID FRTBILL_MTHD BPO_RELEASE_NO PO_TYPE PAYVEND_ID Null NULL NOT NULL NOT NULL NULL NULL NULL NOT NULL NOT NULL Type DATE NUMBER(10) VARCHAR2(4) NUMBER(10) VARCHAR2(4) NUMBER(5) NUMBER(5) NUMBER(10) Description Date the gl acct dist was created Physical key. In sy_orgn_mst. Fk to po_bpos_hdr. In op_frgt_mth. Optional. User relevant bpo release number. In sy_type_mst. Billing vendor id in po_vend_mst. Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-160 OPM Inventory Technical Reference Manual Table and View Definitions SHIPVEND_ID RECVADDR_ID SHIPPER_CODE RECV_DESC FROM_WHSE TO_WHSE RECV_LOCT SHIP_MTHD PO_NO PURCHASE_EXCHANGE_RATE MUL_DIV_SIGN BILLING_CURRENCY CURRENCY_BGHT_FWD TERMS_CODE PO_STATUS POHOLD_CODE CANCELLATION_CODE FOB_CODE BUYER_CODE ICPURCH_CLASS VENDSO_NO PROJECT_NO PO_DATE REQUESTED_DLVDATE SCHED_SHIPDATE REQUIRED_DLVDATE AGREED_DLVDATE DATE_PRINTED EXPEDITE_DATE REVISION_COUNT IN_USE PRINT_COUNT EXPORTED_DATE LAST_UPDATE_DATE CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN DELETE_MARK TEXT_CODE ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 NOT NULL NULL NULL NULL NULL NOT NULL NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NUMBER(10) NUMBER(10) VARCHAR2(4) VARCHAR2(40) VARCHAR2(4) VARCHAR2(4) VARCHAR2(16) VARCHAR2(4) VARCHAR2(32) NUMBER NUMBER(5) VARCHAR2(4) NUMBER(5) VARCHAR2(4) NUMBER(5) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(35) VARCHAR2(8) VARCHAR2(32) VARCHAR2(16) DATE DATE DATE DATE DATE DATE DATE NUMBER(5) NUMBER(10) NUMBER(5) DATE DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(5) NUMBER(10) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) Shipping vendor. Default for line item. Receive location address. Default for line. Default anticipated shipper (op_ship_mst). Free form ship to destination. For intra-company transfers. Default. Anticipated receiving warehouse. Default. Anticipated receiving location. Default. Anticipated ship method. Logical key. May have orgn_code pre-pend. Home/billing_currency exchange rate. 0 - multiply exhange rate; 1 - divide exchange rate Currency in which to pay invoice. Currency brought forward flag. Default from bill vendor in op_term_mst. 0=not closed; 20=closed. Fk to sy_reas_mst. Fk to sy_reas_mst. In op_fobc_mst. Default from ship vendor. Buyer on PO Fk to ic_prch_cls. Vendor sales order number. Not currently supported User defined date of purchase order. Requested delivery date. Scheduled ship date. Required delivery date. Agreed delivery date. Date printed onto form for vendor. Not recvd by date/tickler date for rpt. Number of times revised. Not currently used Number of times printed. Date the transaction was exported to oracle financials. Standard who column Standard who column Standard who column Standard who column Standard who column Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-161 Table and View Definitions ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE30 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE12 ATTRIBUTE_CATEGORY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment Indexes Index Name PO_ORDR_HDR_PK PO_ORDR_HDR_U1 Index Type UNIQUE UNIQUE Sequence 1 1 2 Column Name PO_ID ORGN_CODE PO_NO Sequences Sequence GEM5_PO_ID_S Derived Column PO_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-162 OPM Inventory Technical Reference Manual Table and View Definitions PO_RECV_DTL PO_RECV_DTL table tracks the receipts made against a purchase order. This table has entries for every line that is received against the line in a PO along with the received quantity, receive location, shipping method and the shipper code. Information in this table is transferred to Oracle Purchasing so that detailed receiving information is available in that application, and specifically, for matching invoices entered in Oracle Payables (and generating automatic invoices). The Receiving Open Interface in Oracle Purchasing is used to create receiving transactions in Oracle Purchasing when receipts are saved in OPM. Foreign Keys Primary Key Table IC_ITEM_MST IC_WHSE_MST IC_WHSE_MST OP_FOBC_MST OP_FRGT_MTH OP_SHIP_MST OP_SHIP_MTH PO_ORDR_DTL PO_ORDR_HDR PO_RECV_HDR PO_TEXT_HDR PO_VEND_MST SY_ADDR_MST SY_UOMS_MST SY_UOMS_MST SY_UOMS_MST Primary Key Column ITEM_ID WHSE_CODE WHSE_CODE FOB_CODE FRTBILL_MTHD SHIPPER_CODE SHIP_MTHD LINE_ID PO_ID RECV_ID TEXT_CODE VENDOR_ID ADDR_ID UM_CODE UM_CODE UM_CODE Foreign Key Column ITEM_ID FROM_WHSE TO_WHSE FOB_CODE FRTBILL_MTHD SHIPPER_CODE SHIP_MTHD POLINE_ID PO_ID RECV_ID TEXT_CODE SHIPVEND_ID RECVADDR_ID RECV_UM1 RECV_UM2 PRICE_UM Column Descriptions Name LINE_ID RECV_ID LINE_NO PO_ID POLINE_ID (PK) Null NOT NULL NOT NULL NOT NULL NULL NULL Type NUMBER(10) NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(10) Description Surrogate key to the receipt line number. Surrogate key to the receipt. Receipt line number. Surrogate key to the purchase order received against. Surrogate key to the purchase order linereceived against. Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-163 Table and View Definitions RECV_QTY1 RECV_QTY2 RECV_UM1 RECV_UM2 PORECV_QTY1 PORECV_QTY2 RECVADDR_ID SHIPPER_CODE SHIP_MTHD TO_WHSE FROM_WHSE RECV_LOCT RECV_DESC FRTBILL_MTHD FOB_CODE RECV_DATE RECV_STATUS ALLOC_IND NOT NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL NULL NULL NULL NULL NOT NULL NOT NULL NOT NULL NUMBER NUMBER VARCHAR2(4) VARCHAR2(4) NUMBER NUMBER NUMBER(10) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(16) VARCHAR2(40) VARCHAR2(4) VARCHAR2(4) DATE NUMBER(5) NUMBER(5) EXTENDED_PRICE ITEM_ID NET_PRICE PRICE_UM RETURN_IND SHIPVEND_ID VENDSO_NO GL_POSTED_IND CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN TRANS_CNT ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NUMBER NUMBER(10) NUMBER VARCHAR2(4) NUMBER(5) NUMBER(10) VARCHAR2(32) NUMBER(5) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(10) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) NOT NOT NOT NOT Quantity received (in uom1). Quantity received (in uom 2). Unit of measure 1. Unit of measure 2. Quantity received (in po uom 1). Quantity received (in po uom 2). Fk to sy_addr_mst Fk to op_ship_mst. Fk to op_ship_mth. Fk to ic_whse_mst. Fk to ic_whse_mst. Fk to ic_loct_mst. User entered description. Fk to op_frgt_mth. Fk to op_fobc_mst. Date of the receipt. Status of the receipt: 0 - pending; 20 - completed Indicates whether the lots have been fully allocated to the line: 0 - not allocated; 1 - fully allocated Price x received quantity Surrogate key to the item received. Price in price_um Price uom. 1=a return has been issued against thisreceipt. Fk to po_vend_mst. The vendor sales order number. Indicates whether the information has been posted to the gl. 0 - not posted; 1 - posted Standard who column Standard who column Standard who column Standard who column Standard who column Not currently used Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-164 OPM Inventory Technical Reference Manual Table and View Definitions ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY TEXT_CODE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) NUMBER(10) Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment ID which joins any rows of text in this table to the Text Table for this Module Indexes Index Name PO_RECV_DTLI1 Index Type UNIQUE Sequence 1 2 Column Name RECV_ID LINE_NO POLINE_ID RECV_DATE LINE_ID PO_RECV_DTLI2 PO_RECV_DTLI3 PO_RECV_DTL_PK NOT UNIQUE NOT UNIQUE UNIQUE 1 1 1 Sequences Sequence GEM5_RECV_LINE_ID_S Derived Column LINE_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-165 Table and View Definitions PO_RECV_HDR PO_RECV_HDR stores the header information for the receipts made against a purchase order. This table has one entry for every receipt that is created. RECV_ID is the unique system generated key which is not visible to the user. ORGN_NOand RECV_NO is the unique key seen by the user. RECV_TYPE indicates the type of receipt, either from a Purchase Order or a Stock Receipt. Information in this table is transferred to Oracle Purchasing so that detailed receiving information is available in that application, and specifically, for matching invoices entered in Oracle Payables (and generating automatic invoices). The Receiving Open Interface in Oracle Purchasing is used to create receiving transactions in Oracle Purchasing when receipts are saved in OPM. Receipt numbers in OPM and Oracle Purchasing are the same since the same source is used for document numbering. Stock receipts are maintained only in OPM, so they have a different document numbering sequence. Foreign Keys Primary Key Table GL_CURR_MST GL_CURR_MST IC_WHSE_MST OP_PORT_MST OP_PORT_MST OP_SHIP_MST OP_SHIP_MTH PO_TEXT_HDR PO_VEND_MST PO_VEND_MST SY_ORGN_MST SY_REAS_CDS SY_UOMS_MST Primary Key Column CURRENCY_CODE CURRENCY_CODE WHSE_CODE PORT_CODE PORT_CODE SHIPPER_CODE SHIP_MTHD TEXT_CODE VENDOR_ID VENDOR_ID ORGN_CODE REASON_CODE UM_CODE Foreign Key Column BILLING_CURRENCY DEMURRAGE_CURRENCY TO_WHSE EMBARKATION_PORT DEBARKATION_PORT SHIPPER_CODE SHIP_MTHD TEXT_CODE PAYVEND_ID SHIPVEND_ID ORGN_CODE REASON_CODE BOL_UM Column Descriptions Name RECV_ID (PK) RECV_LOCT RECV_NO Null NOT NULL NULL NOT NULL Type NUMBER(10) VARCHAR2(16) VARCHAR2(32) Description Physical key. Default actual receiving location. Document number. Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-166 OPM Inventory Technical Reference Manual Table and View Definitions RECV_TYPE PAYVEND_ID SHIPVEND_ID SHIPPER_CODE SHIP_MTHD TO_WHSE ORGN_CODE RECV_DATE REASON_CODE TRAILER_NO WAYBILL_NO RECEIVED_BY APPROVED_BY PRINT_COUNT DEBARKATION_PORT EMBARKATION_PORT ARRIVAL_DATE DEPARTURE_DATE REGISTERED_IND DEMURRAGE_AMT DEMURRAGE_CURRENCY RECEIPT_COMMENT GROSS_WT NET_WT TARE_WT BOL_UM DATE_PRINTED VOID_IND IN_USE RECEIPT_EXCHANGE_RATE MUL_DIV_SIGN BILLING_CURRENCY TEXT_CODE CREATION_DATE LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN CREATED_BY DELETE_MARK ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NUMBER(5) NUMBER(10) NUMBER(10) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) DATE VARCHAR2(4) VARCHAR2(40) VARCHAR2(32) NUMBER(15) NUMBER(15) NUMBER(5) VARCHAR2(8) VARCHAR2(8) DATE DATE NUMBER(5) NUMBER VARCHAR2(4) VARCHAR2(70) NUMBER NUMBER NUMBER VARCHAR2(4) DATE NUMBER(5) NUMBER(10) NUMBER NUMBER(5) VARCHAR2(4) NUMBER(10) DATE DATE NUMBER(15) NUMBER(15) NUMBER(15) NUMBER(5) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) NOT NOT NOT NOT NOT NOT NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL Indicates document type which created the receipt: 1 - po receipt; 2 - stock receipt Remit to vendor. Fk to po_vend_mst. Vendor shipping the order. Fk to po_vend_mst. Default actual shipper (op_ship_mst). Default actual ship method (op_ship_mth). Default actual receiveing warehouse. Fk to sy_orgn_mst. Receipt date and default for po_ordr_dtl. Fk to sy_reas_cds. Number of trailer making delivery. Shippers shipment identification number. Fk to sy_oper_mst. Fk to sy_oper_mst. Number of times printed. Fk to op_port_mst. Fk to op_port_mst. Date & time trucker arrived. Date & time trucker departed. 0 - not registered; 1=registered. Actual demurrage penalty. Fk to gl_curr_cds. Prints on receipt ticket. Weight of truck/rail car before unload. Net weight of entire receipt. Weight of truck/rail car after unload. Fk to sy_uoms_mst. Date receipt ticket printed. 0=no void; 1=void. Not currently used Exchange rate used to convert the amount into base currency. 0 - multiply; 1 - divide Fk to gl_curr_mst. ID which joins any rows of text in this table to the Text Table for this Module Standard who column Standard who column Standard who column Standard who column Standard who column Standard: 0=Active record (default); 1=Marked for (logical) deletion. Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-167 Table and View Definitions ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment Indexes Index Name PO_RECV_HDR_PK PO_RECV_HDR_U1 Index Type UNIQUE UNIQUE Sequence 1 1 2 Column Name RECV_ID ORGN_CODE RECV_NO Sequences Sequence GEM5_RECV_ID_S Derived Column RECV_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-168 OPM Inventory Technical Reference Manual Table and View Definitions PO_RTRN_DTL PO_RTRN_DTL table tracks the returns made against a purchase order or in a stock receipt. This table has entries for every line that is returned against a specific receipt line (stock receipt or purchase order receipt). Information in this table is transferred to Oracle Purchasing so that detailed return information is available in that application, and specifically, for matching invoices entered in Oracle Payables (and generating automatic invoices). Thetable RCV_ TRANSACTIONS is updated to reflect returns entered in OPM. Foreign Keys Primary Key Table IC_ITEM_MST PO_ORDR_DTL PO_ORDR_HDR PO_RECV_DTL PO_RECV_HDR PO_RTRN_HDR PO_TEXT_HDR SY_REAS_CDS SY_UOMS_MST SY_UOMS_MST Primary Key Column ITEM_ID LINE_ID PO_ID LINE_ID RECV_ID RETURN_ID TEXT_CODE REASON_CODE UM_CODE UM_CODE Foreign Key Column ITEM_ID POLINE_ID PO_ID RECVLINE_ID RECV_ID RETURN_ID TEXT_CODE RETURN_CODE RETURN_UM1 RETURN_UM2 Column Descriptions Name LINE_ID RETURN_ID LINE_NO PO_ID (PK) Null NOT NULL NOT NULL NOT NULL NULL Type NUMBER(10) NUMBER(10) NUMBER(5) NUMBER(10) Description Surrogate key for the line number in this return. Surrotate key for the return. Line number. Surrogate key for the purchase order, ifapplicable, under which the material was originally received. Surrogate key for the po line, if applicable, under which the material was originally received. Surrogate key for the receipt against which the material was originally received. Surrogate key for the receipt line against which the material was originally received. Surrogate key for the item being returned. Return material authorization code, obtained from the vendor, acknowledging thereturn. Reason code for returning the material. Quantity return (in uom 1). POLINE_ID RECV_ID RECVLINE_ID ITEM_ID RMA RETURN_CODE RETURN_QTY1 NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NUMBER(10) NUMBER(10) NUMBER(10) NUMBER(10) VARCHAR2(16) VARCHAR2(4) NUMBER Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-169 Table and View Definitions RETURN_QTY2 RETURN_UM1 RETURN_UM2 REORDER_IND TRANS_CNT TEXT_CODE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NOT NULL NULL NOT NULL NULL NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NUMBER VARCHAR2(4) VARCHAR2(4) NUMBER(5) NUMBER(10) NUMBER(10) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Quantity returned (in uom 2). Unit of measure for quantity 1. Unit of measure for quantity 2. 1=reorder replacements for the materialreturned by creating new pending transaction. Not currently used ID which joins any rows of text in this table to the Text Table for this Module Standard who column Standard who column Standard who column Standard who column Standard who column Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Descriptive flexfield segment Indexes Index Name PO_RTRN_DTLI1 Index Type NOT UNIQUE Sequence 1 2 Column Name RETURN_ID LINE_NO Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-170 OPM Inventory Technical Reference Manual Table and View Definitions PO_RTRN_DTLI2 PO_RTRN_DTL_PK PO_RTRN_DTL_UK NOT UNIQUE UNIQUE UNIQUE 1 1 1 2 RECVLINE_ID LINE_ID LINE_ID RETURN_ID Sequences Sequence GEM5_RETURN_LINE_ID_S Derived Column LINE_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-171 Table and View Definitions PO_VEND_CLS Vendor classes are defined in this table for the general grouping of vendors for reporting or other purposes. Vendor classes defined in OPM can be entered on the Supplier in Oracle Purchasing via specific descriptive flexfields which aredefined for this information. Foreign Keys Primary Key Table PO_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name VEND_CLASS (PK) VEND_CLASS_DESC CREATION_DATE LAST_UPDATE_LOGIN LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY TRANS_CNT DELETE_MARK TEXT_CODE Null NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NULL Type VARCHAR2(8) VARCHAR2(70) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) Description Unique vendor class entered by the user. User entered description Standard who column Standard who column Standard who column Standard who column Standard who column Not currently used Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Indexes Index Name PO_VEND_CLS_PK Index Type UNIQUE Sequence 1 Column Name VEND_CLASS Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-172 OPM Inventory Technical Reference Manual Table and View Definitions PO_VEND_MST The vendor master for OPM is updated via a database trigger from the Supplier entered in Oracle Purchasing. Vendor information can not be edited or viewed in OPM. Purchase orders transferred to OPM will reference the vendor number fromOPM and stock receipts must contain a vendor number which can be looked up from the OPM vendor master. In addition to updating this table, the vendor relationships maintained in PO_VEND_ASC are also created when vendors are transferred from Oracle Purchasing. Foreign Keys Primary Key Table GL_CURR_MST OP_FRGT_MTH OP_TERM_MST PO_TEXT_HDR PO_VEND_CLS PO_VGLD_CLS PO_VTRD_CLS SY_ADDR_MST SY_ADDR_MST SY_ORGN_MST Primary Key Column CURRENCY_CODE FRTBILL_MTHD TERMS_CODE TEXT_CODE VEND_CLASS VENDGL_CLASS VENDTRADE_CLASS ADDR_ID ADDR_ID ORGN_CODE Foreign Key Column DEFAULT_CURRENCY FRTBILL_MTHD TERMS_CODE TEXT_CODE VEND_CLASS VENDGL_CLASS VENDTRADE_CLASS ADDR_ID MAILADDR_ID CO_CODE Column Descriptions Name VENDOR_ID PHONE_NO VENDOR_NO ALTVEND_NO VENDSORT_NO VENDOR_NAME ADDR_ID REMIT_NAME MAILADDR_ID CO_CODE Null NOT NULL NULL NOT NULL NULL NULL NOT NULL NOT NULL NULL NULL NULL Type NUMBER(10) VARCHAR2(20) VARCHAR2(32) VARCHAR2(32) VARCHAR2(16) VARCHAR2(40) NUMBER(10) VARCHAR2(40) NUMBER(10) VARCHAR2(4) Description Surrogate key for vendor. Phone number. Unique vendor number entered by user. User entered code Short name of the vendor Vendor name Fk to sy_addr_mst. Remit to name of the vendor Fk to sy_addr_mst. Company for which the vendor number is defined. A blank value indicates the vendor is defined for all companies. Fax number. Telex number. Default curreny for a transaction. Fk to gl_ curr_mst. Indicates whether the currency can be changed: 0 - default currency only; 1- other currencies FAX_NO TELEX_NO DEFAULT_CURRENCY CHANGE_CURRENCY NULL NULL NOT NULL NOT NULL VARCHAR2(20) VARCHAR2(20) VARCHAR2(4) NUMBER(5) Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-173 Table and View Definitions TIMELINESS_RATING PRICE_RATING MISC_RATING BUS_START_DATE BUS_END_DATE BUS_AMOUNT MIN_PURCHASE APPROVED_IND NULL NULL NULL NULL NULL NULL NULL NOT NULL NUMBER(5) NUMBER(5) NUMBER(5) DATE DATE NUMBER NUMBER NUMBER(5) INACTIVE_IND NOT NULL NUMBER(5) SHIP_IND PAYMENT_IND INFORM_GOVT TAXABLE_IND TERMS_CODE TERMS_VARY VENDOR_TYPE SIC_CODE SPLC_CODE FRTBILL_MTHD CREDIT_LIMIT VEND_CLASS VENDTRADE_CLASS VENDGL_CLASS OF_VENDOR_ID OF_VENDOR_SITE_ID CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY TRANS_CNT TEXT_CODE DELETE_MARK LAST_UPDATE_LOGIN PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE REQUEST_ID NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NUMBER(5) NUMBER(5) NUMBER(5) NUMBER(5) VARCHAR2(4) NUMBER(5) NUMBER(5) VARCHAR2(8) VARCHAR2(8) VARCHAR2(4) NUMBER VARCHAR2(8) VARCHAR2(8) VARCHAR2(8) NUMBER(10) NUMBER(10) DATE NUMBER(15) DATE NUMBER(15) NUMBER(10) NUMBER(10) NUMBER(5) NUMBER(15) NUMBER(15) NUMBER(15) DATE NUMBER(15) NOT NULL NULL NULL NULL NULL NULL are permited. User entered timeliness of delivery rating. How the user rates the vendor prices. Miscellaneous rating. Date when the first busineess activity took place. Date when the final business actity took place. Amount of business done with the vendor. The minimum amount that can be purchased on a single po. Indicates whether the vendor has been approved for purchases: 0 - orders not allowed; 1- order allowed. Restricts or permits entry of the vendor in po. Indicates if the vendor is inactive: 0 active; 1 - not active. If the vendor is not active a po cannot be created. Indicates whether the vendor ships product: 0 no; 1 - yes Does this vendor receive payments for merchandice: 0 - no; 1 - yes Is a 1099 issued: 0 - no; 1- yes Is the vendor taxable: 0 - tax exempt; 1 taxable Fk to op_term_mst Indicates whether the terms can vary on an order by order basis: 0 - no; 1 - yes 0 - regular vendor; 1 - consignment vendor User entered sic code - not validated User enterd splc code - not validated Fk to op_ship_mth Maximum credit limit granted by the vendor. Fk to po_vend_cls. Fk to po_vtrd_cls. Fk to po_vgld_cls. Surrogate from Oracle Financials identifying the vendor. Surrogate from Oracle Financials identifying the vendor. Standard who column Standard who column Standard who column Standard who column Not currently used ID which joins any rows of text in this table to the Text Table for this Module Standard: 0=Active record (default); 1=Marked for (logical) deletion. Standard who column Concurrent who column Concurrent who column Concurrent who column Concurrent who column Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-174 OPM Inventory Technical Reference Manual Table and View Definitions ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield flexfield segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment segment Indexes Index Name PO_VEND_MSTI1 Index Type UNIQUE Sequence 1 2 Column Name CO_CODE VENDOR_NO VENDOR_NO VENDSORT_NO VENDOR_NAME CO_CODE INACTIVE_IND DELETE_MARK VENDOR_ID PO_VEND_MSTI2 UNIQUE 1 2 3 4 5 6 PO_VEND_MST_PK UNIQUE 1 Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-175 Table and View Definitions Sequences Sequence GEM5_VENDOR_ID_S Derived Column VENDOR_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-176 OPM Inventory Technical Reference Manual Table and View Definitions PS_PLNG_CLS Planning class definitions. Items and operators can be associated to these to support report groupings for item activity by planning class. Foreign Keys Primary Key Table IC_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name PLANNING_CLASS (PK) PLANNING_CLASS_DESC TEXT_CODE TRANS_CNT DELETE_MARK CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 Null NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Type VARCHAR2(8) VARCHAR2(40) NUMBER(10) NUMBER(10) NUMBER(5) NUMBER(15) DATE NUMBER(15) DATE NUMBER(15) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) Description Code for the planning class. Description of the planning class. ID which joins any rows of text in this table to the Text Table for this Module Not currently used - Retrofitted Standard who column Standard who column Standard who column Standard who column Standard who column Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Flexfield Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive FlexfieldDescriptive Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield NOT NOT NOT NOT NOT Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-177 Table and View Definitions ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Flexfield Flexfield Flexfield Flexfield Flexfield Flexfield Indexes Index Name PS_PLNG_CLS_PK Index Type UNIQUE Sequence 1 Column Name PLANNING_CLASS Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-178 OPM Inventory Technical Reference Manual Table and View Definitions QC_ACTN_MST QC action codes. Expiration, out-of-spec, out-of-grade actions. Foreign Keys Primary Key Table QC_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name ACTION_CODE (PK) ACTION_DESC ACTION_INTERVAL DELETE_MARK TEXT_CODE TRANS_CNT CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 Null NOT NULL NOT NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Type VARCHAR2(4) VARCHAR2(40) NUMBER NUMBER(5) NUMBER(10) NUMBER(10) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) Description Code for the action to be performed. Full description of the action code. Days between failure to meet spec and performance of action. Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Not currently used Standard who column Standard who column Standard who column Standard who column Standard who column Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment NOT NOT NOT NOT Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-179 Table and View Definitions ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NULL NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Descriptive Flexfields Flexfields Flexfields Flexfields Flexfields Flexfields Flexfields Segment Segment Segment Segment Segment Segment Segment Indexes Index Name QC_ACTN_MST_PK Index Type UNIQUE Sequence 1 Column Name ACTION_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-180 OPM Inventory Technical Reference Manual Table and View Definitions QC_GRAD_MST QC grade code master. Definitions of QC grade codes. Foreign Keys Primary Key Table QC_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name QC_GRADE (PK) QC_GRADE_DESC DELETE_MARK TEXT_CODE TRANS_CNT CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 Null NOT NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Type VARCHAR2(4) VARCHAR2(40) NUMBER(5) NUMBER(10) NUMBER(10) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) Description Code for the QC grade. Description of the QC grade. Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Not currently used Standard who column Standard who column Standard who column Standard who column Standard who column Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment Descriptive Flexfield Segment NOT NOT NOT NOT Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-181 Table and View Definitions ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Descriptive Descriptive Descriptive Descriptive Descriptive Flexfield Flexfield Flexfield Flexfield Flexfield Segment Segment Segment Segment Segment Indexes Index Name QC_GRAD_MST_PK Index Type UNIQUE Sequence 1 Column Name QC_GRADE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-182 OPM Inventory Technical Reference Manual Table and View Definitions QC_HRES_MST QC hold reason code master. Definition of QC hold reason codes. Foreign Keys Primary Key Table QC_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name QCHOLD_RES_CODE (PK) QCHOLD_RES_DESC CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY DELETE_MARK TEXT_CODE TRANS_CNT LAST_UPDATE_LOGIN ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 Null NOT NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Type VARCHAR2(4) VARCHAR2(40) DATE NUMBER(15) DATE NUMBER(15) NUMBER(5) NUMBER(10) NUMBER(10) NUMBER(15) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) Description Code for the QC hold reason. Description of the QC hold reason. Standard who column Standard who column Standard who column Standard who column Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Not currently used Standard who column Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Descriptive Flexfields Segment Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-183 Table and View Definitions ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_CATEGORY NULL NULL NULL NULL NULL VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(240) VARCHAR2(30) Descriptive Descriptive Descriptive Descriptive Descriptive Flexfields Flexfields Flexfields Flexfields Flexfields Segment Segment Segment Segment Segment Indexes Index Name QC_HRES_MST_PK Index Type UNIQUE Sequence 1 Column Name QCHOLD_RES_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-184 OPM Inventory Technical Reference Manual Table and View Definitions SY_ADDR_MST OPM Address master. Table which stores addresses for all Oracle Process Manufacturing applications entities. Column Descriptions Name ADDR_ID ADDR1 ADDR2 ADDR3 ADDR4 STATE_CODE COUNTRY_CODE POSTAL_CODE PSEUDO_KEY LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY COMMENTS DELETE_MARK TRANS_CNT ORA_ADDR4 PROVINCE COUNTY LAST_UPDATE_LOGIN PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE REQUEST_ID Null NOT NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Type NUMBER(10) VARCHAR2(70) VARCHAR2(70) VARCHAR2(70) VARCHAR2(70) VARCHAR2(4) VARCHAR2(4) VARCHAR2(16) VARCHAR2(70) DATE NUMBER(15) DATE NUMBER(15) VARCHAR2(40) NUMBER(5) NUMBER(10) VARCHAR2(70) VARCHAR2(70) VARCHAR2(70) NUMBER(15) NUMBER(15) NUMBER(15) DATE NUMBER(15) Description Surrogate id uniquely identifying an address. First line of the address. Second line of the address. Third line of the address. Fourth line of the address. State code of the address. Country code of the address. Postal code of the address. Allows recreation of link to originating record in case of table corruption. Standard who column Standard who column Standard who column Standard who column Comment Field - unused Standard: 0=Active record (default); 1=Marked for (logical) deletion. Not currently used Address Line used for Oracle Financials Integration Province code County code Standard who column Concurrent who column Concurrent who column Concurrent who column Concurrent who column Indexes Index Name SY_ADDR_MST_PK Index Type UNIQUE Sequence 10 Column Name ADDR_ID Sequences Sequence GEM5_ADDRESS_ID_S Derived Column ADDR_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-185 Table and View Definitions SY_DOCS_MST Oracle Process Manufacturing Document master. This table stores document definitionsused by inventory, resource and costing transactions in OPM. Foreign Keys Primary Key Table SY_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name DOC_TYPE DOC_DESC (PK) Null NOT NULL NOT NULL NOT NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL Type VARCHAR2(4) VARCHAR2(40) VARCHAR2(40) NUMBER(5) NUMBER(10) NUMBER(10) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) Description Document type. Description of the document in the default language. Description of the document in english. Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Not currently used Standard who column Standard who column Standard who column Standard who column Standard who column DESC_ENGLISH DELETE_MARK TEXT_CODE TRANS_CNT CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN NOT NOT NOT NOT Indexes Index Name SY_DOCS_MST_PK Index Type UNIQUE Sequence 10 Column Name DOC_TYPE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-186 OPM Inventory Technical Reference Manual Table and View Definitions SY_DOCS_SEQ Document sequencing by OPM organization. Maintains document number assignment rules including auto assignment. Does not support blank paddingcharacters. Foreign Keys Primary Key Table SY_DOCS_MST SY_ORGN_MST SY_ORGN_MST SY_ORGN_MST SY_ORGN_MST SY_TEXT_HDR Primary Key Column DOC_TYPE ORGN_CODE ORGN_CODE ORGN_CODE ORGN_CODE TEXT_CODE Foreign Key Column DOC_TYPE ORGN_CODE SY_DOCS_SEQ_ORGN_CODE SY_DOCS_SEQ_ORGN_CODE2 SY_DOCS_SEQ_ORGN_CODE3 TEXT_CODE Column Descriptions Name DOC_TYPE ORGN_CODE (PK) (PK) Null NOT NULL NOT NULL NOT NULL NULL NULL NULL NOT NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NOT NULL Type VARCHAR2(4) Description ASSIGNMENT_TYPE LAST_ASSIGNED FORMAT_SIZE PAD_CHAR DELETE_MARK CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY TEXT_CODE TRANS_CNT DUMMY_FILL LAST_UPDATE_LOGIN SY_DOCS_SEQ_ORGN_CODE SY_DOCS_SEQ_ORGN_CODE2 SY_DOCS_SEQ_ORGN_CODE3 Document type for which the document sequencing rules are defined. VARCHAR2(4) Organization for which document sequencing rules are defined. NUMBER(5) 1= manual assignment; 2=auto integer sequencing. NUMBER(10) Last integer assigned to the document inthe organization. NUMBER(5) Size of the document number. Must be greater than 0, less than 11. VARCHAR2(1) Padding character used for padding leading spaces in the document number. Either blank or zero. NUMBER(5) Standard: 0=Active record (default); 1=Marked for (logical) deletion. DATE Standard who column NUMBER(15) Standard who column DATE Standard who column NUMBER(15) Standard who column NUMBER(10) ID which joins any rows of text in this table to the Text Table for this Module NUMBER(10) Not currently used VARCHAR2(255) Used to enlarge record so it fills more of a logical database page. NUMBER(15) Standard who column VARCHAR2(4) VARCHAR2(4) VARCHAR2(4) Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-187 Table and View Definitions Indexes Index Name SY_DOCS_SEQ_PK Index Type UNIQUE Sequence 10 20 Column Name DOC_TYPE ORGN_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-188 OPM Inventory Technical Reference Manual Table and View Definitions SY_ORGN_MST Organization code (business unit) master for Oracle Process Manufacturing.Define organization codes and addresses here. This table is linked to HR_ ORGANIZATION_UNITS by ORGANIZATION_ID, where a one to one relationship exists.It also stores Resource Warehouse as used in the APS integration. Foreign Keys Primary Key Table SY_ADDR_MST SY_DOCS_SEQ Primary Key Column ADDR_ID ORGN_CODE DOC_TYPE Foreign Key Column ADDR_ID SY_ORGN_MST_ORGN_CODE SY_ORGN_MST_DOC_TYPE CO_CODE PARENT_ORGN_CODE TEXT_CODE TAXLOC_CODE SY_ORGN_MST SY_ORGN_MST SY_TEXT_HDR TX_TLOC_CDS ORGN_CODE ORGN_CODE TEXT_CODE TAXLOC_CODE Column Descriptions Name ORGN_CODE (PK) ORGN_NAME PARENT_ORGN_CODE CO_CODE PLANT_IND ADDR_ID ORGANIZATION_ID TAXLOC_CODE TEXT_CODE DELETE_MARK TRANS_CNT POC_IND Null NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NULL NULL NULL NOT NULL NOT NULL NOT NULL Type VARCHAR2(4) VARCHAR2(40) VARCHAR2(4) VARCHAR2(4) NUMBER(5) NUMBER(10) NUMBER(15) VARCHAR2(10) NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(5) Description Organization code. Organization name. Orgn to which orgn_code reports. Must already exist in the table. Company code of the organization. 0=no the organization is not a plant; 1=yes the organization is a plant. Surrogate of the organization address. Foreign reference to HR_ORGANIZATION_UNITS Tax Location Code for this organization (FK to TX_TLOC_CDS) ID which joins any rows of text in this table to the Text Table for this Module Standard: 0=Active record (default); 1=Marked for (logical) deletion. Not currently used 0=Do not generate POC transactions for organization; 1=Generate POC transactions for organization Standard who column Standard who column Standard who column Standard who column Standard who column Resource warehouse code associated to Plants CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN RESOURCE_WHSE_CODE SY_ORGN_MST_ORGN_CODE NULL NULL NULL NULL NULL NULL NOT NULL NOT NOT NOT NOT DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) VARCHAR2(4) VARCHAR2(4) Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-189 Table and View Definitions SY_ORGN_MST_DOC_TYPE NOT NULL VARCHAR2(4) Indexes Index Name SY_ORGN_MST_PK SY_ORGN_MST_U1 Index Type UNIQUE UNIQUE Sequence 10 10 Column Name ORGN_CODE ORGANIZATION_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-190 OPM Inventory Technical Reference Manual Table and View Definitions SY_ORGN_USR Associates OPM Organizations with FND users. This dictates what Organizationa User has access to, limiting the ability to process transactions and view organization specific data where necessary. Foreign Keys Primary Key Table SY_ORGN_MST Primary Key Column ORGN_CODE Foreign Key Column ORGN_CODE Column Descriptions Name USER_ID (PK) ORGN_CODE (PK) LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN Null NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL Type NUMBER(15) VARCHAR2(4) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) Description Foreign Key to FND_USER Organization Code (Foreign key to SY_ORGN_MST) Standard who column Standard who column Standard who column Standard who column Standard who column Indexes Index Name SY_ORGN_USR_PK Index Type UNIQUE Sequence 10 20 Column Name USER_ID ORGN_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-191 Table and View Definitions SY_REAS_CDS The Reason Code table is used to store Reason Code definitions which provide information on the increase or decrease in inventory. Reason codes are used to flag transactions and attach reasons to them. Foreign Keys Primary Key Table SY_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name REASON_CODE (PK) REASON_DESC1 REASON_DESC2 REASON_TYPE FLOW_TYPE AUTH_STRING DELETE_MARK TEXT_CODE TRANS_CNT CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN Null NOT NULL NOT NULL NULL NOT NULL NOT NULL NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL Type VARCHAR2(4) VARCHAR2(40) VARCHAR2(70) NUMBER(5) NUMBER(5) VARCHAR2(90) NUMBER(5) NUMBER(10) NUMBER(10) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) Description Reason code. Description of the reason code. Description of the reason code (not used). Indicates change type for the reason. Indicates inventory flow. -1=inflow; 1=outflow; 0=usage; 2=exclude. Unused. Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Not currently used Standard who column Standard who column Standard who column Standard who column Standard who column NOT NOT NOT NOT Indexes Index Name SY_REAS_CDS_PK Index Type UNIQUE Sequence 10 Column Name REASON_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-192 OPM Inventory Technical Reference Manual Table and View Definitions SY_UOMS_MST Unit of measure master for Oracle Process Manufacturing. Synchronized with MTL_UNITS_OF_MEASURE and associated tables when Oracle Financials is used. Foreign Keys Primary Key Table SY_TEXT_HDR SY_UOMS_TYP Primary Key Column TEXT_CODE UM_TYPE Foreign Key Column TEXT_CODE UM_TYPE Column Descriptions Name UM_CODE UM_DESC UM_TYPE STD_FACTOR RND_FACTOR REF_UM LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY TEXT_CODE DELETE_MARK TRANS_CNT LAST_UPDATE_LOGIN (PK) Null NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL Type VARCHAR2(4) VARCHAR2(40) VARCHAR2(10) NUMBER NUMBER VARCHAR2(4) DATE NUMBER(15) DATE NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(15) Description Unit of Measure code for which the conversion is defined. Description of the Unit of Measure The Unit of Measure Type associated with each Unit of Measure Conversion factor to the reference UOM of the same Type. Unused, defaulted to 0. Reference Unit of Measure for the same Unit of Measure Type. Standard who column Standard who column Standard who column Standard who column ID which joins any rows of text in this table to the Text Table for this Module Standard: 0=Active record (default); 1=Marked for (logical) deletion. Not currently used Standard who column NOT NULL NULL NULL Indexes Index Name SY_UOMS_MST_PK Index Type UNIQUE Sequence 10 Column Name UM_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-193 Table and View Definitions SY_UOMS_TYP Unit of measure type master for Oracle Process Manufacturing products. Define unit of measure types here, which categorize Units of Measure. Synchronized with MTL_UOM_CLASSES when Oracle Financials is used. Foreign Keys Primary Key Table SY_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name UM_TYPE (PK) TYPE_DESC LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY TEXT_CODE DELETE_MARK TRANS_CNT LAST_UPDATE_LOGIN STD_UM Null NOT NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL Type VARCHAR2(10) VARCHAR2(40) DATE NUMBER(15) DATE NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(15) VARCHAR2(4) Description Unit of Measure Type Unit of Measure Type description. Standard who column Standard who column Standard who column Standard who column ID which joins any rows of text in this table to the Text Table for this Module Standard: 0=Active record (default); 1=Marked for (logical) deletion. Not currently used Standard who column The Standard, or reference, UOM for this UOM Type NOT NULL NULL NULL NULL Indexes Index Name SY_UOMS_TYP_PK Index Type UNIQUE Sequence 10 Column Name UM_TYPE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-194 OPM Inventory Technical Reference Manual Table and View Definitions SY_WF_ITEM_ROLES Column Descriptions Name ITEM_ID ITEM_NO EXPIRY_ROLE_NAME EXPIRY_ROLE_DISPLAY_NAME RETEST_ROLE_NAME RETEST_ROLE_DISPLAY_NAME LOT_EXPIRY_NOTE LOT_RETEST_NOTE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN WHSE_ITEM_ID WHSE_ITEM_NO Null NULL NULL NULL NULL NULL NULL NULL NULL NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NULL Type NUMBER(10) VARCHAR2(32) VARCHAR2(100) VARCHAR2(240) VARCHAR2(100) VARCHAR2(240) NUMBER(3) NUMBER(3) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) NUMBER(10) VARCHAR2(32) Description Item ID from IC_ITEM_MST Item Number from IC_ITEM_MST Roles for Lot Expiry Notification Display Name for the Expiry_Role_Name Roles for Lot Retest Notification Display Name for the Retest_Role_Name Number of days before lot expiry date, the notification will be sent Number of days before lot retest date, the notification will be sent Standard who column Standard who column Standard who column Standard who column Standard who column Warehouse Item ID from IC_ITEM_MST Warehouse Item Number from IC_ITEM_MST Indexes Index Name SY_WF_ITEM_ROLES_UK Index Type UNIQUE Sequence 10 20 Column Name ITEM_ID WHSE_ITEM_ID Oracle Proprietary, Confidential Information--Use Restricted by Contract Detailed Design 3-195 Table and View Definitions TX_TLOC_CDS This table holds the definition of Tax Locations. A Tax Location is assigned to a customer and warehouse to determine which tax authorities pertain to the ship-from warehouse/customer combination. The pairing of Tax Locations is done inthe table TX_TOFR_ASC. Foreign Keys Primary Key Table TX_TEXT_HDR Primary Key Column TEXT_CODE Foreign Key Column TEXT_CODE Column Descriptions Name TAXLOC_CODE (PK) Null NOT NULL NOT NOT NOT NOT NOT NULL NULL NULL NULL NULL NULL NOT NULL NULL NULL Type VARCHAR2(10) VARCHAR2(70) DATE DATE NUMBER(15) NUMBER(15) NUMBER(10) NUMBER(5) NUMBER(10) NUMBER(15) Description Tax location code. Indicates tax jurisdiction/geographic location. User entered description for the tax location Standard who column Standard who column Standard who column Standard who column Not currently used Standard: 0=Active record (default); 1=Marked for (logical) deletion. ID which joins any rows of text in this table to the Text Table for this Module Standard who column TAXLOC_DESC CREATION_DATE LAST_UPDATE_DATE CREATED_BY LAST_UPDATED_BY TRANS_CNT DELETE_MARK TEXT_CODE LAST_UPDATE_LOGIN Indexes Index Name TX_TLOC_CDS_PK Index Type UNIQUE Sequence 1 Column Name TAXLOC_CODE Oracle Proprietary, Confidential Information--Use Restricted by Contract 3-196 OPM Inventory Technical Reference Manual Index A Application Building Block, 1-6 L Lookup types See QuickCodes, 3-3 C Column descriptions, 3-3 Columns Who, 3-4 Concurrent Program List See Concurrent Program Definitions, 2-4 M Module List, 2-21 Module List See Module Definitions, 2-4 Modules, 1-6 Multilingual View List, 2-20 D Database Diagram, 1-6 Summary Database Diagram, database diagrams conventions, 2-6 summary, 2-5 Database triggers, 3-6 1-6 O Oracle8 sequences See Sequences, 3-6 P Public Table List, 2-13 Public View List, 2-18 Q F Foreign keys, 3-3 Form, 1-6 Form List, 2-22 Form List See Form Definitions, QuickCodes, 1-6 Columns that contain, 3-3 2-4 R Relationship, 1-5 Report List, 2-24 Report List See Report Definitions, 2-4 I Indexes, 3-5 important note about, 3-5 S Sequences, 3-6 summary database diagram See database Index-1 diagrams, 2-5 T Table and View Definitions BIS_OPM_PROD_SUM, 3-8 BISOPM_ONHANDSALE_SUM, CM_CLDR_DTL, 3-9 CM_CLDR_HDR, 3-10 CM_MTHD_MST, 3-12 CM_WHSE_ASC, 3-13 CR_SQDT_CLS, 3-14 FM_FORM_EFF, 3-15 FM_FORM_MST, 3-17 FM_MATL_DTL, 3-19 FND_DUAL, 3-21 FND_PRINTER_VL, 2-20 GL_CLDR_HDR, 3-22 GL_ITEM_CST, 3-23 GL_PLCY_MST, 3-25 IC_ADJS_JNL, 3-28 IC_ALLC_CLS, 3-31 IC_ALOT_PRM, 3-32 IC_CLDR_DTL, 3-33 IC_CLDR_HDR, 3-34 IC_COMD_CDS, 3-35 IC_COST_CLS, 3-36 IC_CRUL_CLS, 3-38 IC_CTMS_CLS, 3-39 IC_CYCL_ADT, 3-41 IC_CYCL_DTL, 3-43 IC_CYCL_ERR, 3-44 IC_CYCL_HDR, 3-45 IC_CYCL_MSC, 3-47 IC_FRGT_CLS, 3-49 IC_GLED_CLS, 3-51 IC_INVN_CLS, 3-52 IC_INVN_TYP, 3-54 IC_ITEM_CDT, 3-56 IC_ITEM_CNV, 3-57 IC_ITEM_CPG, 3-58 IC_ITEM_HIERARCHY, 3-59 IC_ITEM_MST, 3-60 IC_ITEM_WHS, 3-64 IC_JRNL_MST, 3-65 3-7 IC_LOCT_INV, 3-66 IC_LOCT_MST, 3-68 IC_LOTS_CPG, 3-70 IC_LOTS_MST, 3-71 IC_LOTS_STS, 3-73 IC_PERD_BAL, 3-74 IC_PHYS_CNT, 3-76 IC_PHYS_ERR, 3-78 IC_PKGS_MST, 3-79 IC_PLNT_INV, 3-80 IC_PRCE_CLS, 3-82 IC_PRCH_CLS, 3-84 IC_PURG_PRM, 3-86 IC_RANK_MST, 3-88 IC_SALE_CLS, 3-89 IC_SHIP_CLS, 3-91 IC_STAT_RPT, 3-93 IC_STOR_CLS, 3-94 IC_SUMM_INV, 3-96 IC_TAXN_ASC, 3-98 IC_TAXN_CLS, 3-99 IC_TEXT_HDR, 3-100 IC_TEXT_TBL_TL, 3-101 IC_TRAN_ARC, 3-102 IC_TRAN_CMP, 3-104 IC_TRAN_PND, 3-106 IC_WHSE_INV, 3-109 IC_WHSE_MST, 3-112 IC_WHSE_REL, 3-114 IC_WHSE_STS, 3-116 IN_ADDR_MST, 3-117 IN_CTRY_MST, 3-118 IN_ITEM_MST, 3-119 IN_REGN_MST, 3-120 IN_STAT_CTL, 3-121 IN_STAT_DTL, 3-122 IN_STAT_HDR, 3-124 IN_STAT_MSG, 3-125 IN_TEXT_HDR, 3-126 IN_TEXT_TBL_TL, 3-127 IN_TOFD_MST, 3-128 OP_BILL_LAD, 3-129 OP_CUST_MST, 3-132 OP_ORDR_DTL, 3-137 OP_ORDR_HDR, 3-145 Index-2 OP_PORT_MST, 3-150 PM_BTCH_HDR, 3-151 PM_MATL_DTL, 3-154 PO_ORDR_DTL, 3-157 PO_ORDR_HDR, 3-160 PO_RECV_DTL, 3-163 PO_RECV_HDR, 3-166 PO_RTRN_DTL, 3-169 PO_VEND_CLS, 3-172 PO_VEND_MST, 3-173 PS_PLNG_CLS, 3-177 QC_ACTN_MST, 3-179 QC_GRAD_MST, 3-181 QC_HRES_MST, 3-183 SY_ADDR_MST, 3-185 SY_DOCS_MST, 3-186 SY_DOCS_SEQ, 3-187 SY_ORGN_MST, 3-189 SY_ORGN_USR, 3-191 SY_REAS_CDS, 3-192 SY_UOMS_MST, 3-193 SY_UOMS_TYP, 3-194 SY_WF_ITEM_ROLES, 3-195 TX_TLOC_CDS, 3-196 Tables Column descriptions, 3-3 Foreign keys, 3-3 Indexes See Indexes, 3-5 Primary Keys, 3-3 QuickCodes Columns, 3-3 Who columns, 3-4, 3-5 Tables See Table and View Definitions, 3-2 V View Definitions See Table and View Definitions, 3-6 Views Derivation, 3-6 Views See Table and View Definitions, Views See View List, 3-6 3-6 Index-3 Index-4