Apps Tables

Inventory Tables

Inventory Parameter Setup Table

1.      MTL_PARAMETERS – Inventory Control Options and Default

2. MTL_PICKING_RULES
3. MTL_ATP_RULES

4.      BOM_CALANDARS – Workday Calandars

 

Inventory Setup Tables

1. MTL_SYSTEM_ITEMS_B – Inventory Item Definitions

• INVENTORY_ITEM_ID             
• ORGANIZATION_ID
• DESCRIPTION                           
• BUYER_ID                              
• ACCOUNTING_RULE_ID                    
• INVOICING_RULE_ID                     
• PURCHASING_ITEM_FLAG  
• SHIPPABLE_ITEM_FLAG  
• CUSTOMER_ORDER_FLAG            
• BOM_ENABLED_FLAG            
• INVENTORY_ITEM_FLAG           
• PURCHASING_ENABLED_FLAG        
• CUSTOMER_ORDER_ENABLED_FLAG  
• LOT_CONTROL_CODE 
• INVENTORY_PLANNING_CODE
• MRP_PLANNING_CODE
• ATP_FLAG

2. MTL_LOT_NUMBERS  -- Lot Number Definitions

·         Inventory_Item_Id

·        Organization_id

·        Lot_Number

3. MTL_MATERIAL_TRANSACTION
• Transaction_id

4. MTL_ITEM_TEMPLATES – Item Template Definitions
• TEMPLATE_ID   
• TEMPLATE_NAME 
• DESCRIPTION   

5. MTL_ITEM_TEMPL_ATTRIBUTES – Item Attributes and Attribute Values to a Template.

• TEMPLATE_ID   
• ATTRIBUTE_NAME
• ATTRIBUTE_VALUE
• SEQUENCE              
• ATTRIBUTE_GROUP_ID    
• REPORT_USER_VALUE     

6. MTL_ITEM_STATUS – Material Status Definitions
• INVENTORY_ITEM_STATUS_CODE

7. MTL_PENDING_ITEM_STATUS
8. MTL_INTERORG_PARAMETERS
9. MTL_UOM_CONVERSIONS

10. MTL_ITEM_LOCATIONS

Definitions for stock locators.

11. MTL_ITEM_SUB_INVENTORIES

• INVENTORY_ITEM_ID     
• ORGANIZATION_ID       
• SECONDARY_INVENTORY   
• MIN_MINMAX_QUANTITY           
• MAX_MINMAX_QUANTITY           
• INVENTORY_PLANNING_CODE       

12. MTL_SERIAL_NUMBERS

• INVENTORY_ITEM_ID
• SERIAL_NUMBER         

13. MTL_TXN_REQUEST_HEADERS_V (Move Order Header Screen)

• REQUEST_NUMBER
• HEADER_STATUS_DISPLAY

14. MTL_TXN_REQUEST_LINES_V
• INVENTORY_ITEM
• TO_SUBINVENTORY_CODE
• TRANSACTION_TYPE_NAME
• QUANTITY

PO Requisition Headers Entry

Table - PO_REQUISITION_HEADERS_V

            Base Table for PO Requisition Header

            Columns

·        REQUISITION_HEADER_ID         

·        PREPARER_ID                   

·        DESCRIPTION                   

·        NOTE_TO_AUTHORIZER             

·        VENDOR_ID             

Table - PO_REQUISITION_LINES_V

            Base Table for PO Requisition Lines

            Columns

·        ROW_ID                

·        REQUISITION_HEADER_ID 

·        REQUISITION_LINE_ID   

·        LINE_NUM              

·        LINE_TYPE             

·        LINE_TYPE_ID          

·        ITEM_ID               

·        ITEM_REVISION         

·        CATEGORY_ID           

·        ITEM_DESCRIPTION      

·        QUANTITY              

·        QUANTITY_RECEIVED     

·        QUANTITY_DELIVERED    

·        QUANTITY_CANCELLED              

PO Entry

Table – PO_HEADERS_V

            Base Table for PO Header

            Columns

·        PO_HEADER_ID

·        VENDOR_ID             

·        VENDOR_SITE_ID        

·        VENDOR_CONTACT_ID     

·        SHIP_TO_LOCATION_ID   

·        BILL_TO_LOCATION_ID   

Table - HR_LOCATIONS_ALL

            Columns

·        LOCATION_ID

·        INVENTORY_ORGANIZATION_ID

Table - PO_LINES  

             Base Table/View for PO Lines

            Columns

·        PO_LINE_ID            

·        PO_HEADER_ID          

Table - PO_LINE_LOCATIONS_V

            Base table/view for PO Shipments

            Columns

·        ROW_ID                        

·        PO_HEADER_ID                  

·        PO_LINE_ID                    

·        LINE_LOCATION_ID              

·        SHIPMENT_NUM                  

·        SHIPMENT_TYPE                 

·        SHIP_TO_ORGANIZATION_ID       

·        SHIP_TO_ORGANIZATION_CODE     

·        SHIP_TO_LOCATION_ID           

·        SHIP_TO_LOCATION_CODE         

·        QUANTITY                      

·        QUANTITY_ACCEPTED             

·        QUANTITY_BILLED               

·        QUANTITY_CANCELLED            

·        QUANTITY_RECEIVED             

·        QUANTITY_REJECTED             

Suppliers

Table – AP_VENDORS_V

            Base table/view for Supplier

            Columns

·        VENDOR_ID                     

·        VENDOR_NAME                   

·        VENDOR_NUMBER                 

·        EMPLOYEE_ID                   

·        VENDOR_TYPE_DISP              

·        CUSTOMER_NUM                  

·        PARENT_VENDOR_ID              

·        PARENT_VENDOR_NAME            

·        PARENT_VENDOR_NUMBER          

·        SHIP_TO_LOCATION_ID           

·        SHIP_TO_LOCATION_CODE         

·        SHIP_TO_LOCATION              

·        BILL_TO_LOCATION_ID           

·        BILL_TO_LOCATION_CODE         

·        BILL_TO_LOCATION              

Table – AP_VENDOR_SITES_V

            Base Table for Supplier Sites

            Columns

·        ROW_ID                

·        VENDOR_SITE_ID        

·        VENDOR_ID             

·        VENDOR_SITE_CODE      

Table – PO_VENDOR_CONTACTS_V

            Base Table for Supplier Contacts

            Columns

·        VENDOR_CONTACT_ID             

·        VENDOR_SITE_ID                

·        INACTIVE_DATE                 

·        FIRST_NAME                    

·        MIDDLE_NAME                   

Request For Quotation (RFQ)

Table – PO_HEADERS_RFQQT_V

            Base Table for RFQ Header

            Columns

·        ROW_ID                        

·        PO_HEADER_ID                  

·        STATUS                        

·        REVISION_NUM                  

·        VENDOR_ID                      

·        VENDOR_NAME                   

·        VENDOR_SITE_ID                

·        VENDOR_SITE_CODE              

·        VENDOR_CONTACT_ID                           

Table – PO_LINES_RFQQT_V

            Base Table for RFQ Header

            Columns

·        PO_HEADER_ID                  

·        PO_LINE_ID                    

·        ITEM_ID                       

·        ITEM_REVISION                 

·        CATEGORY_ID                   

·        ITEM_DESCRIPTION              

·        UNIT_MEAS_LOOKUP_CODE         

·        PRIMARY_UNIT_CLASS            

·        UNIT_PRICE                    

·        VENDOR_PRODUCT_NUM            

·        MIN_ORDER_QUANTITY            

·        MAX_ORDER_QUANTITY            

·        NOTE_TO_VENDOR                

Table – PO_RFQ_VENDORS_V

            Base Table for RFQ Suppliers 

            Columns

·        ROW_ID                 

·        PO_HEADER_ID          

·        SEQUENCE_NUM          

·        VENDOR_ID             

·        VENDOR_NAME           

·        VENDOR_SITE_ID        

·        VENDOR_SITE_CODE      

·        VENDOR_CONTACT_ID     

·        VENDOR_CONTACT        

Oracle Order Management (OM) Tables

1. Orders and Returns

Diagram 1 shows the tables and relationships that store order header

and line information. This diagram also shows the tables and

relationships that link directly to order and line tables.

     Tables

      OE_ORDER_HEARDS_ALL

      (HEADER_ID)

      

       OE_AGREEMENTS_B

        (AGREEMENT_ID)

        OE_ORDER_SOURCES

        (ORDER_SOURCE_ID)

        OE_LINE_ACKS

        OE_HEADER_ACKS

        OE_WORKFLOW_ASSIGNMENTS

        (ASSIGNMENT_ID)

        OE_PRICE_ADJUSTMENTS

        (PRICE_ADJUSTMENT_ID)

        FND_CURRENCIES

        (CURRENCY_CODE)

       OE_CREDIT_CHECK_RULES

       (CREDIT_CHECK_RULES_ID)

       OE_TRANSACTION_TYPES_ALL

        (TRANSACTION_TYPE_ID)

       RA_CUST_TRX_TYPES_ALL

        (CUST_TRX_TYPE_ID)

       MTL_SYSTEM_ITEMS_B

       (INVENTORY_ITEM_ID, ORGANIZATION_ID)

       OE_ORDER_LINES_ALL

       (LINE_ID)

       OE_SETS

        (SET_ID)

2. Customers

Diagram 2 shows the tables and relationships that store customers,

customer addressesk, site uses, contacts, and phone numbers. This

 

Tables

RA_SALESREPS_ALL

(SALESREP_ID)

RA_TERMS_B

(TERM_ID)

RA_CONTACTS_HZ

(CONTACT_ID)

RA_RULES

(RULE_ID)

HR_ALL_ORGANIZATION_UNITS

(ORGANIZTION_ID)

3. Items and Models

Diagram 3 shows the tables and relationships that store inventory items

and configurations (bills of material, or BOMs). this diagram also

shows the tables and relationships that link directly to item and BOM

tables.

           Tables

           BOM_INVENTORY_COMPONENTS

           (COMPONENT_SEQUENCE_ID)

           MTL_CUSTOMER_ITEMS

          (CUSTOMER_ITEM_ID)

          MTL_LOT_NUMBERS

          (INVENTORY_ITEM_ID, ORGANIZATION_ID, LOT_NUMBER)

          MTL_CUSTOMER_ITEM_XREFS

          (CUSTOMER_ITEM_ID, INVENTORY_ITEM_ID,

       MASTER_ORGNIZATION_ID)

        MTL_CROSS_REFERENCE_TYPES

        (CROSS_REFERENCE_TYPE)

       MTL_CROSS_REFERENCES

       (INVENTORY_ITEM_ID, ORGANIZATION_ID, CROSS_REFERENCE_TYPE,

         CROSS_REFERENCE)

4. Price Adjustments

Diagram 4 shows the tables and relationships that store price

adjustments, price adjustment associations, and price adjustment

attributes. This diagram also shows the tables and relationships that

link directly to pricing tables.

           Tables

          QP_LIST_HEADERS_B

          (LIST_HEADER_ID)

          QP_LIST_LINES

          (LIST_LINES_ID)

          OE_ORDER_PRICE_ATTRIBS

         (ORDER_PRICE_ATTRIB_ID)

         OE_PRICE_ADJUSTMENTS

         (PRICE_ADJUSTMENTS_ID)

         OE_PRICE_ADJ_ASSOCS

         (PRICE_ADJ_ASSOC_ID)

        OE_PRICE_ADJ_ATTRIBS

        (PRICE_ADJ_ATTRIB_ID)

5. Transaction Types

Diagram 5 shows the tables and relationships that store transaction

types along with the tables and relationships that link directly to

transaction type tables.

6. Order Import

Diagram 10 shows the tables and relationships that store imported

order information.

            Tables

            OE_HEADERS_IFACE_ALL

           OE_CREDITS_IFACE_ALL

           OE_PRICE_ATTS_IFACE_ALL

           OE_LINES_IFACE_ALL

            OE_PRICE_ADJS_IFACE_ALL

            OE_RESERVTNS_IFACE_ALL

            OE_LOTSERIALS_IFACE_ALL

7. Drop Shipments

Diagram 11 shows the tables and relationships that store drop shipped

sales order lines and their corresponding requisition and purchase

order linke. This diagram also shows the tables and relationships that

link directly to sales order and purchase order tables.

            Tables    

          RCV_SHIPMENT_LINES

          (SHIPMENT_LINE_ID)

          PO_LINE_LOCATIONS_ALL

         (LINE_LOCATION_ID)

          PO_LINES_ALL

         (PO_LINE_ID)

          PO_HEADERS_ALL

          (PO_HEADER_ID)

         PO_DISTRIBUTIONS_ALL

         (PO_DISTRIBUTION_ID)

      OE_ORDER_LINES_ALL

      (LINE_ID)

     OE_DROP_SHIP_SOURCES

     PO_REQUISITION_HEADERS_ALL

     (REQUISITION_HEADER_ID)

     PO_REQUISITION_LINES_ALL

      (REQUISITION_LINE_ID)

     PO_REQ_DISTRIBUTIONS_ALL

     (DISTRIBUTION_ID)

    PO_REQUISTIONS_INTERFACE_ALL

    (TRANSACTION_ID)

   PO_INTERFACE_ERRORS

   (INTERFACE_TYPE, INTERFACE_TRANSACTION_ID)

What is SQL Loader with Examples

What is SQL Loader

SQL LOADER is an Oracle utility used to load data into table given a datafile which has the records that need to be loaded. SQL*Loader takes data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called a

 log file, bad file or reject file, discard file.

• Log file tells you the state of the tables and indexes and the number of logical records already read from the input datafile. This information can be used to resume the load where it left off.
• Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle errors.
• Discard file specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records.

What is Structure of the data file:

The data file can be in fixed record format or variable record format.

Fixed Record Format would look like the below. In this case you give a specific position where the Control file can expect a data field:

7369 SMITH      CLERK        7902  12/17/1980         800                  

7499 ALLEN      SALESMAN  7698  2/20/1981           1600    

7521 WARD      SALESMAN  7698  2/22/1981           1250    

7566 JONES      MANAGER   7839  4/2/1981             2975             

7654 MARTIN    SALESMAN  7698  9/28/1981           1250    

7698 BLAKE      MANAGER   7839  5/1/1981             2850             

7782 CLARK      MANAGER   7839  6/9/1981             2450             

7788 SCOTT      ANALYST    7566  12/9/1982           3000             

7839 KING        PRESIDENT          11/17/1981         5000             

7844 TURNER    SALESMAN  7698  9/8/1981            1500           

7876 ADAMS     CLERK         7788  1/12/1983          1100             

7900 JAMES      CLERK         7698  12/3/1981          950              

7902 FORD        ANALYST     7566  12/3/1981          3000            

7934 MILLER     CLERK         7782  1/23/1982          1300            

Variable Record Format would like below where the data fields are separated by a delimiter.

Note: The Delimiter can be anything you like. In this case it is "|"

1196700|9|0|692.64

1378901|2|3900|488.62

1418700|2|2320|467.92

1418702|14|8740|4056.36

1499100|1|0|3.68

1632800|3|0|1866.66

1632900|1|70|12.64

1637600|50|0|755.5

Structure of a Control file:

Sample CTL file for loading a Variable record data file:

OPTIONS (SKIP = 1)   --The first row in the data file is skipped without loading

LOAD DATA

INFILE '$FILE'             -- Specify the data file path and name

APPEND                       -- type of loading (INSERT, APPEND, REPLACE, TRUNCATE

INTO TABLE "APPS"."BUDGET"   -- the table to be loaded into

FIELDS TERMINATED BY '|'           -- Specify the delimiter if variable format datafile

 OPTIONALLY ENCLOSED BY '"'   --the values of the data fields may be enclosed in "

TRAILING NULLCOLS     -- columns that are not present in the record treated as null

  (ITEM_NUMBER    "TRIM(:ITEM_NUMBER)", -- Can use all SQL functions on columns

  QTY                 DECIMAL EXTERNAL,

  REVENUE             DECIMAL EXTERNAL,

  EXT_COST            DECIMAL EXTERNAL TERMINATED BY WHITESPACE "(TRIM(:EXT_COST))"  ,

  MONTH           "to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD-MON-YY')" ,

DIVISION_CODE    CONSTANT "AUD"  -- Can specify constant value instead of

                                                                          Getting value from datafile

   )

OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.

SKIP = n -- Number of logical records to skip (Default 0)

LOAD = n -- Number of logical records to load (Default all)

ERRORS = n -- Number of errors to allow (Default 50)

ROWS = n   -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)

BINDSIZE = n -- Size of conventional path bind array in bytes (System-dependent default)

SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} -- Suppress messages during run

                (header, feedback, errors, discards, partitions, all)

DIRECT = {TRUE | FALSE} --Use direct path (Default FALSE)

PARALLEL = {TRUE | FALSE} -- Perform parallel load (Default FALSE)

LOAD DATA statement is required at the beginning of the control file.

INFILE: INFILE keyword is used to specify location of the datafile or datafiles.

INFILE * specifies that the data is found in the control file and not in an external file. INFILE '$FILE', can be used to send the filepath and filename as a parameter when registered as a concurrent program.

INFILE   '/home/vision/kap/import2.csv' specifies the filepath and the filename.

Example where datafile is an external file:

LOAD DATA

INFILE   '/home/vision/kap/import2.csv'

INTO TABLE kap_emp

FIELDS TERMINATED BY ","

( emp_num, emp_name, department_num, department_name )

Example where datafile is in the Control file:

LOAD DATA

INFILE *

INTO TABLE kap_emp

FIELDS TERMINATED BY ","              

( emp_num, emp_name, department_num, department_name )

BEGINDATA

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

Example where file name and path is sent as a parameter when registered as a concurrent program

LOAD DATA

INFILE '$FILE'

INTO TABLE kap_emp

FIELDS TERMINATED BY ","              

( emp_num, emp_name, department_num, department_name )

TYPE OF LOADING:

INSERT   -- If the table you are loading is empty, INSERT can be used.

APPEND  -- If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded.

REPLACE -- All rows in the table are deleted and the new data is loaded

TRUNCATE -- SQL*Loader uses the SQL TRUNCATE command.

INTO TABLE is required to identify the table to be loaded into. In the above example INTO TABLE "APPS"."BUDGET", APPS refers to the Schema and BUDGET is the Table name.

FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)

OPTIONALLY ENCLOSED BY '"' specifies that data fields may also be enclosed by quotation marks.

TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

How to Loading a fixed format data file:

LOAD DATA

INFILE 'sample.dat'

INTO TABLE emp

(      empno         POSITION(01:04)   INTEGER EXTERNAL,

       ename          POSITION(06:15)   CHAR,

       job            POSITION(17:25)   CHAR,

       mgr            POSITION(27:30)   INTEGER EXTERNAL,

       sal            POSITION(32:39)   DECIMAL EXTERNAL,

       comm           POSITION(41:48)   DECIMAL EXTERNAL,

       deptno         POSITION(50:51)   INTEGER EXTERNAL)

What are Steps to Run the SQL* LOADER from UNIX:

At the prompt, invoke SQL*Loader as follows:

      sqlldr USERID=scott/tiger CONTROL= LOG=

      name>

SQL*Loader loads the tables, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.

How to Register as concurrent Program:

Place the Control file in $CUSTOM_TOP/bin.

Define the Executable. Give the Execution Method as SQL*LOADER.

Define the Program. Add the Parameter for FILENAME.

 How to Skip columns:

You can skip columns using the 'FILLER' option.

Load Data
--
--
--
TRAILING  NULLCOLS
(
name Filler,
Empno ,
sal
)

here the column name will be skipped.