Oracle Distribution Tables

INVENTORY TABLES

A raw material comes under Inventory.

HR_OPERATING_UNITS

(Business_group_id, organization_id, name, date_from, date_to, Short_code)

• Business_group_id->Any organization starts with business group and each organization must have unique business group id.
• Organization_id->Different organization id’s under business group id.
• Name -> Corresponding organization names for defined organization id’s under business group id.
• Short_code->Nothing but Operating Unit.

Date_from and Date_to are used in value sets for comparing sysdate between date_from and nvl(date_to,sysdate).If date_to has some value then no need of comparing with nvl function else must and should use nvl function.

ORG_ORGANIZATION_DEFINITIONS

(Business_group_id, Organization_id, Organization_name, Organization_code, Operating_unit)

• Operating_unit=organization_id in HR_OPERATING_UNITS.
• Organization_id=Inventory organization under operating unit.
• Organization_name=Inventory organization name’s under operating unit.
• Organization_code=Organization code for Inventory organization under Operating unit.

MTL_PARAMETERS

(Master_organization_id, Organization_id, Organization_code)

• It contains Master and child organizations and also includes the organization code.

• Master_organization_id=Organization id’s for master organizations.
• Organization_id=Child Organization id.
• Organizaiton_Code=Organization code for Inventory organization.

MTL_SYSTEM_ITEMS

(Inventory_item_id, Organization_id, Segment1, Primary_unit_of_measure, Primary_uom_code, Planning_make_buy_code)

• Inventory_item_id=Nothing but Item id.
• Organization_id=Inventory organization id.
• Segment1=Item Code.
• Description=Item Description.
• Primary_unit_of_measure=Unit of measure for Item.(full description of uom)
• Primary_uom_code=Just contains the uom code not full description of uom.
• Planning_make_buy_code=lookup_code in MFG_LOOKUPS table.

MTL_SECONDARY_INVENTORIES

(Secondary_inventory_name, Description, Organization_id)

• Secondary_inventory_name=Contains subinventory code.
• Description=Subiventory description.
• Organization_id=Inventory Organization.

MTL_ITEM_SUB_INVENTORIES

(Inventory_item_id, Organization_id, Secondary_inventory)

• Inventory_item_id=Item id.
• Organization_id=Inventory organization.
• Secondary_inventory=Subinventory code.

MTL_SECONDARY_LOCATORS

(Inventory_item_Id, Organization_id, Secondary_locator, Subinventory_Code)

• Locators come under Subinventories only.
• Inventory_item_id=Item id.
• Organization_id=Inventory Organization.
• Secondary_locator=Contains Secondary locator code.
• Subinventory_code=Secondary Inventory code.

MTL_ITEM_LOCATIONS

(Inventory_location_id, Organization_id, Segment1, Segment2, Description, Subinventory_code, Inventory_item_id)

• Inventory_location_id=Location id for Inventory organization.
• Organization_id=Inventory organization.
• Segment1 and Segment2 are nothing but locator names.
• Description=Location name.
• Subinventory_code=Secondary Inventory code.
• Inventory_item_id=Item id.

MTL_ITEM_LOCATIONS_KFV

Same as MTL_ITEM_LOCATIONS but here we have concatenated segments. Instead of concatenating sement1 and segment2 from MTL_ITEM_LOCAITONS, we directly take the column concatenated_segments from MTL_ITEM_LOCATIONS_KFV.

MTL_LOT_NUMBERS

(Inventory_item_id, Organization_id, Lot_number)

• Lot is nothing but group of similar items.
• Lots can be created under either subinventory or locators level.
• Inventory_item_id=Item id.
• Organization_id=Inventory organization.
• Lot_number=lot number for items.

MTL_SERIAL_NUMBERS

(Inventory_item_id, Serial_number)    

• We give serial numbers to items under lots. Each item has individual serial no.
• Inventory_item_id=Item id.
• Serial_number=Serial number of an item.

MTL_CATEGORY_SETS

(Category_set_id, Structure_id, Category_set_name, Description)

• Category set id is “1” for Inventory Category.
• Category_set_id= Id for Particular category set.
• Category_set_name=Name of category set.
• Description=Category set description.

MTL_CATEGORIES

(Category_id, Structure_id, Segment1, Segment2, Description, Structure_id)

• Category_Id=Id for Particular Category under Category set.
• Segment1 and Segment2 are nothing but category names.
• Description=Category description.
• Structure_id=Structure_id in MTL_CATEGORY_SETS table.

      NOTE: - Brand Name can be calculated by using the following formulae.

Brand Name= SUBSTR (description, INSTR (description, '-', 1) + 1, INSTR (description, '-', -1) - INSTR (description, '-', 1)

- 1)

MTL_ITEM_CATEGORIES

(Inventory_item_id, Organization_id, Category_set_id, Category_id)

• Inventory_item_id=Item id.
• Organization_id=Inventory organization.
• Category_set_id=Id for particular Category set.
• Category_id=Category id for particular category under category set.

MTL_ITEM_CATEGORIES_V

It contains all Category_set_name, Category_set_id, Category_id, Inventory_item_id, Organization_id, Segment1, and Segment2.

MTL_ONHAND_QUANTITIES

(Inventory_item_id, Organization_id, Transaction_quantity, Subinventory_code, Locator_id)

• Contains Quantity or Count of items.
• Inventory_item_id=Item id.
• Organization_id=Inventory organization.
• Transaction_quantity=Item quantity.
• Subinventory_code=secondary inventory code.
• Locator_id=Secondary_locator from MTL_SECONDARY_LOCATORS.

CST_ITEM_COSTS

(Inventory_item_id, Organization_id, Cost_type_id, Item_cost)

• Price or Cost of items will be stored in this table.

• Inventory_item_id=Item id.
• Organization_id=Inventory organization.
• Cost_type_id=Id for type of cost.
• Item cost=Cost of an item.

CST_COST_TYPES

(Cost_type_id, Organization_id, Cost_type, Description)

• Cost_type_id=Id for cost type.
• Organization_id=Inventory organization.
• Cost_type=Type of cost.
• Description=Cost type description.

CST_ITEM_COST_TYPE_V

(Inventory_item_id, Item_number, Description, Primary_uom_code, Organization_id, Cost_type_id, Cost_type, Cost_type_description, Item_cost, Category_id)

• Inventory_item_id=Item id.
• Item_number is nothing but item code.
• Description is nothing but item description.
• Primary_uom_code is nothing but uom.
• Organization_id=Inventory organization.
• Cost_type_id=Id for cost type.(Cost_type_id “2” is for average cost of an item)
• Cost_type=Type of cost.
• Cost_type_description=Description for cost type.
• Item_cost=Cost of an item.
• Category_id=Id for a category.

For calculating total cost or extended cost:-

Total or extended cost= (item_cost * transaction_quantity)   

                                            Item_cost (CST_ITEM_COSTS)

                                            Transaction_quantity (MTL_ONHAND_QUANTITIES)

MTL_UOM_CONVERSIONS

(Unit_of_measure, Uom_code, Inventory_item_id)

• Unit_of_measure=Full UOM Description.
• Uom_code=Just contains Uom code not full description.
• Inventory_item_id=Item id.

MTL_UNITS_OF_MEASURE

For UOM we take either MTL_UOM_CONVERSIONS or MTL_UNITS_OF_MEASURE. But MTL_UNITS_OF_MEASURE does not have any organization_id or inventory_item_Id to map with inventory tables.

MTL_ITEM_TEMPLATE

(Template_id, Template_name, Description)

• Contains type of items whether the item is EXPENSE or STOCKED ITEM       or FINISHED GOOD ITEM or PURCHASED ITEM or etc.
• Template_id=Id for template.
• Template_name=Name of a template.
• Description=Description of a template.
• PO can be created on Expensed Items or Stored Items.
• If PO created on Expensed on Items then Item_id will not be appeared.

Note: - Don’t use the following tables MTL_UNITS_OF_MEASURE, MTL_UOM_CONVERSIONS, MTL_ITEM_TEMPLATE in report level.

The main table for Distribution module is MTL_MATERIAL_TRANSACTIONS.

MTL_MATERIAL_TRANSACTIONS

(Transaction_id,Inventory_item_id,Organization_id,Subinventory_code,Locator_id,Transaction_type_id,Transaction_quantity,Transaction_uom,Primary_quantity,Transaction_date,Transaction_cost,Currency_code,Department_id,RCV_transaction_id,Source_code,Shipment_number,Expenditure_type,Transaction_source_id,Operation_seq_num,Actual_cost)

• All the transaction details of an item are updated in MTL_MATERIAL_TRANSACTIONS table.
• Transaction_id=Id for transaction of an item.
• Inventory_item_id=Item id.
• Organizaiton_id=Inventory organization.
• Subinventory_Code=Secondary inventory code.
• Locator_id=Secondary locator id.
• Transaction_Type_id=Type id of transaction of an item.
• Transaction_quantity=How much quantity done for a transaction of an item.
• Primary_quantity=Quantity of an item.
• Transaction_quantity and Primary_quantity both are same; they are different when UOM was changed.
• Original quantity is in Transaction_quantity.
• Changed quantity is in Primary_quantity.
• Primary_quantity:-
• Primary_quantity preceeded with two signs -, +.
• When Purchase or gain an item then ‘+’.
• When Sales or delivers an item then ‘-‘.
• Transaction_date=Date of a Transaction of an item.
• Transaction_cost=Cost for a transaction of an item.
• Currency_code=Currency code of an item to be transacted.
• Actual_cost=Cost of item nothing Item Cost.
• Operation_seq_num=Operation_seq_num in WIP_REQUIREMENT_OPERATIONS table.
• Transaction_source_id=Wip_entity_id in WIP_ENTITIES (Work order tables) table.

MTL_TRANSACTION_TYPES

(Transaction_type_id, Transaction_type_name, Description, Transaction_source_type_id)

• Contains all the transaction types whether it is PO type or Work Order type or etc.
• Transaction_type_id=Id for transaction type.
• Transaction_type_name=Name of a transaction type.
• Description= Transaction type description.
• Transaction_source_type_id=source type of a transaction.
• (Transaction_source_type_id is “1” for PO transactions.

                                                         “4” for OE order transactions.

                                                         “5” for Work order transactions etc.)

PO TABLES

PO_HEADERS_ALL

(Po_header_id,Agent_id,Type_lookup_code,segment1,Creation_date,Vendor_id,Vendor_site_id,Vendor_contact_id,Ship_to_location_id,Bill_to_location_Id,Terms_id,Currency_code,Rate_type,Authorization_status,Comments,Attribute15,Org_id,freight_terms_lookup_code,fob_lookup_code, Quote_vendor_quote_number, Reply_date)

• Po_header_id=Header id for po.
• Agent_id=Buyer id.
• Type_lookup_code=PO type.
• Segment1=PO number.
• Creation_date=PO creation date.
• Vendor_id=Supplier id.
• Vendor_site_id=Supplier site id.
• Vendor_contact_id=Supplier contact id.
• Currency_code=Currency code for po.
• Authorization_status=PO status.
• Comments=Remarks or Description or Subject.
• Attribute15=Order type.
• Org_id=Opearting unit. (Not inventory Organization).
• Frieght_terms_lookup_code=Frieght terms.
• Fob_lookup_code=Meaning in FND_LOOKUP_VALUES_VL table.
• Ship_to_location_id and Bill_to_location_id= Location_id in HR_LOCATIONS_ALL table.
• Quote_vendor_quote_number=Reference Number.
• Reply_date=Reference date.

PO_LINES_ALL

(PO_line_id,PO_header_id,Line_type_id,Line_num,Item_id,Category_id,Item_description,Unit_meas_lookup_code,Unit_price,Quantity,Org_id)

• PO_line_id=Lind id for po.
• PO_header_id=Header id for po.
• Line_type_id=Type id for po line.
• Line_num=Line number.
• Item_id=Item id nothing but Inventory_item_id in MTL_SYSTEM_ITEMS or any Inventory tables.
• Category_id=Category id.
• Item Description=Description of an item.
• Unit_meas_lookup_code=UOM Description.
• Unit_price=Cost of item.
• Quantity=Count of item.
• Org_id=Operating unit. (Not inventory Organization).

PO_LINE_LOCATIONS_ALL

(Line_location_id,PO_Header_id,PO_line_id,Quantity,Quantity_Received,Quantity_Accepted,Quantity_Rejected,Quantit_Billed,Quantity_Cancelled,Unit_meas_lookup_code,PO_release_id,Ship_to_location_id,Need_by_date,Shipment_num,Shipment_type,Org_id,Ship_to_organization_id)

• Always take sum of quantity from this table because it is lower level table for PO_headers_all, PO_lines_all and PO_line_locations_all.
• Line_location_id=Location id.
• PO_Header_id=Header id for po.
• PO_line_id=Line id for po.
• Quantity=Quantity of item.
• Unit_meas_lookup_code=UOM description.
• PO_release_id=Release id nothing but PO_release_id in PO_RELEASES_ALL table.
• Org_id=Operating unit. (Not inventory Organization).
• Ship_to_organization_id=Organization_id in ORG_ORGANZIATION_DEFINITIONS table.

PO_ DISTRIBUTIONS_ALL

(PO_distribution_id,PO_header_id,PO_line_id,Line_location_id,Quantity_ordered,PO_release_id,Req_distribution_id,Destination_subinventory,Destination_organization_id,Org_id,Distribution_type)

• When you are using this table in your query take sum of quantity from this table only, because it is a lower level table for PO_headers_all, po_lines_all, po_line_locations_all, PO_distributions_all.
• PO_distribution_id=Distribution id.
• PO_header_id=Header id.
• PO_line_id=Line id.
• Quantity_ordered=Quantity.
• PO_release_id=Release_id nothing but PO_release_id in PO_RELEASES_ALL table.
• Req_distribution_id=Distribution_id in PO_REQ_DISTRIBUTIONS_ALL.
• Destination_subinventory =Subinventory.
• Destination_organizaiton_id=Destination_organization.
• Org_id=Operating unit. (Not inventory Organization).

For calculating total amount of a particular PO number:

• Total Amount= (sum (quantity) *(Unit_price))                        //When using PO_line_locations_all table.
• Total Amount= (sum (ordered_quantity) *(unit_price))       //When using PO_distributions_all table.
• Unit_price is taken from po_lines_all table.

PO_RELEASES_ALL

(PO_release_id, PO_header_id, Release_num, Agent_id, Release_date, Authorization_status, Release_type, Org_id)

• PO_release_id=Release id (PO_release_id in PO_LINE_LOCATIONS_ALL or PO_DISTRIBUTIONS_ALL tables).
• PO_header_id=Header id.
• Release_num=Release Number.
• Agent_id=Buyer id.
• Release_date=Release date.
• Authorization_status=Release status.
• Release_type=Release type.
• Org_id=Operating unit (Not inventory Organization).

PO_REQUISIITION_HEADERS_ALL

(Requisition_header_id, Preparer_id, Segment1, Authorization_status, Type_lookup_code, Cancel_flag, Closed_code, Org_id, Approved_date)

• Here type_lookup_code is either PURCHASE or INTERNAL.
• INTERNAL means within organization and INTERNAL requisitions does not have suggested buyer.
• PURCHASE means out of organization and PURCHASE requisition have suggested buyer.
• Here Cancel_flag must be ‘N’.(in case of requisitions which are not cancelled)
• Closed_code is null.
• Requisition_header_id=Header id for po requisition.
• Preparer_id=Who prepares the requisition.(Person_id in per_all_people_f ,agent_id in po_agents_v)
• Segment1=Requisition Number.
• Authorization_status=Requisition status.
• Type_lookup_code=Requisition type.
• Org_id=Operating unit (Not inventory organization).
• Approved_date=Requisition approved date.

PO_REQUISITION_LINES_ALL

(Requisition_line_id,Requisition_header_id,Line_num,Line_type_id,Category_id,Blanket_po_header_id,Item_description,Unit_meas_lookup_code,Unit_price,Quantity,Item_id,Suggested_buyer_id,Line_location_id,Note_to_receiver,Suggested_vendor_name,Suggested_vendor_contact,Suggested_vendor_location,Cancel_flag,Source_organization_id,Source_subinventory,Destination_type_code,Destination_organization_id,Destination_subinventory,Closed_code,Org_id,Manufacturer_id,Manufacturer_name,Note_to_vendor,Note_to_agent,Deliver_to_location_id)

• Here Cancel_flag must be ‘N’.(in case of requisitions which are not cancelled)
• Closed_code is null.
• Requisition_line_id=Line id for a requisition.
• Requisition_header_id=Header id for a requisition.
• Line_num=Line number.
• Line_type_id=Line type id.
• Category_id=Category id.
• Blanket_po_header_id=PO_header_id in PO_HEADERS_ALL table.(Make Outer join on PO_HEADERS_ALL table)
• Item_description=Item description.
• Unit_meas_lookup_code=UOM description.
• Unit_price=Item cost.
• Quantity=Count of item.
• Item_id=Inventory item.
• Source_organization_id=to organization id.
• Source_subinventory=to subinventory.
• Destination_organization_id=from organization id (Nothing but Inventory Organization id).
• Destination_subinventory=from subinventory.
• Destination_type_code=Destination type code.
• Org_id=Operating unit (not inventory organization).
• To_person_id=Person_id in PER_ALL_PEOPLE_F table.
• Suggested_buyer_id=Agent_id in PO_AGENTS table, Employee_id in HR_EMPLOYEES table.
• Note_to_vendor=Note to Supplier.
• Note_to_agent=Note to Buyer.
• Deliver_to_location_id=Location_id in HR_LOCATIONS_ALL, HR_LOCATIONS_ALL_TL table.

PO_LINE_TYPES

(Line_type_id, Order_type_lookup_code, Creation_date, Category_id, Unit_of_measure, Unit_price, Line_type, Description, Purchase_basis, Matching_basis)

• Line_type_id=Line_type_id in PO_REQUISITION_LINES_ALL table.
• Unit_of_measure=UOM code.
• Line_type=Line type.
• Description=Line type description.

PO_REQ_DISTRIBUTIONS_ALL

(Distribution_id, Requisition_line_id, Requisition_line_quantity, Set_of_books_id, Code_combination_id, Distribution_num, Org_id)

• Distribution_id=Req_distribution_id in PO_DISTRIBUTIONS_ALL.
• Requisition_line_id=Requisition line id.
• Requisition_line_quantity=Line Quantity for a requisition.
• Distribution_num=Distribution number.
• Org_id=Operating unit (Not inventory organization).

PO_ACTION_HISTORY

(Object_id, Object_type_code, Sequence_num, Action_code, Action_date, Employee_id)

• PO approved details were stored in this table.
• Sequence_num=Sequence number (level wise approval details).
• Action_code=Action code.
• Employee_id=Person_id (PER_ALL_PEOPLE_F).
• Object_id=PO_header_id in PO_HEADERS_ALL table.
• Object_type_code=Object type code.

PO_AGENTS_V

(Agent_id, Agent_name)

• Agent_id=Buyer id.
• Agent_name=Buyer name.

PO_AGENTS

(Agent_id, Location_id, Category_id)

• Agent_id=Buyer_id
• Join Agent_id with Person_id in PER_ALL_PEOPLE_F and get the FULL_NAME from PER_ALL_PEOPLE_F as buyer or agent name.
• Category_id=Category_id in MTL_CATEGORIES table.

SUPPLIER DETAILS

AP_SUPPLIERS

(Vendor_id, Vendor_name, Segment1, Employee_id, Vendor_type_lookup_code, Terms_id, Invoice_currency_code, payment_currency_code, Party_id)

• Vendor_id=Supplier id.
• Vendor_name=Supplier name.
• Segment1=Supplier code or Vendor number or Supplier number.
• Terms_id=Term id.
• Party_id=Party id.

PO_VENDORS

• Same as AP_SUPPLIERS table. We can take one of both tables for Supplier details.

PO_VENDOR_CONTACTS

(Vendor_contact_id, Vendor_site_id, First_name, Middle_name, Last_name, Vendor_id)

• Vendor_contact_id=Vendor_contact_id in PO_HEADERS_ALL table.
• Vendor_site_id=Vendor_site_id in PO_HEADERS_ALL table.
• Vendor_id=Vendor_id in PO_HEADERS_ALL table.
• First_name||’ ‘||Middle_name||’ ‘||Last_name=Contact Person.

AP_SUPPLIER_CONTACTS

• This table is same as of PO_VENDOR_CONTACTS table.

AP_SUPPLIER_SITES_ALL

(Vendor_site_id,Vendor_id,Vendor_site_code,Address_line1,Address_line2,Address_line3,City,State,Country,Area_code,Phone,Fax,Fax_area_code,Terms_id,Invoice_currency_code,Payment_currency_code,Validation_number,Org_id,Bank_number,Email_address,Location_id,Party_site_Id)

• Vendor_site_id=Supplier site id.
• Vendor_id=Supplier id.
• Vendor_site_code=Supplier site code.
• Address_line1, Address_line2, Address_line3, City, State, Country, Area_code, Phone_num = Address of Supplier.
• Fax, Fax_area_code=Fax address of supplier (nothing but Contact Person Fax).
• Phone=Contact Person Phone.
• Terms_id=Term id.
• Org_id=Operating unit (not Inventory organization).
• Email_address=Email address of supplier.
• Location_id=Location id.
• Party_site_id=Party site id.

RCV_TRANSACTIONS

(Transaction_id,Transaction_type,Transaction_date,Quantity,Unit_of_measure,Shipment_header_id,Shipment_line_id,Interface_source_code,Source_document_code,Destination_type_code,Primary_quantity,Primary_unit_of_measure,UOM_Code,Parent_transaction_id,PO_header_id,PO_line_id,PO_release_id,PO_Line_location_id,PO_distribution_id,Requisition_line_id,Currency_code,Currency_conversion_type,Vendor_id,Vendor_site_id,Organization_id,Subinventory,Locator_id,WIP_entity_id,WIP_line_id,Location_id,Inspection_status_code,Comments,OE_order_header_id,OE_order_line_id,Customer_id,Customer_site_id)

• All receiving information stored in RCV_TRANSACTIONS.
• Transaction_id=Transaction id.
• Transaction_type=Type of transaction.
• Transaction_date=Transaction date.
• Quantity=Transaction quantity.
• Unit_of_measure=UOM description.
• Shipment_header_Id=Shipment header id in RCV_SHIPMENT_HEADERS.
• Shipment_line_id=Shipment line id in RCV_SHIPMENT_LINES.
• UOM_code=UOM code.
• PO_header_id=PO header id.
• PO_line_id=PO line id.
• PO_line_location_id=PO line location id.
• PO_distribution_id=PO distribution id.
• Requisition_line_id=Requisition line id.
• Currency_code=Currency code.
• Vendor_id=Supplier id.
• Vendor_site_id=Supplier site id.
• Organization_id=Inventory organization.
• Subinventory=Secondary inventory.
• WIP_entity_id=Work order entity id.
• WIP_line_id=Work order line id.
• OE_order_header_id=Order header id for OE.
• OE_order_line_id=Order line id for OE.
• Customer_id=Customer id or Party_id.
• Customer_site_id=Customer Site id or Party site id.

RCV_SHIPMENT_HEADERS

(Shipment_header_Id, Receipt_source_code, Vendor_id, Vendor_site_id, Organization_id, Shipment_num, Receipt_num, Shipped_date, Employee_id, Waybill_airbill_num)

• Receipt details stored in this table.
• Shipment_header_id=Shipment header id.
• Receipt_num=Receipt number.
• Employee_id=Person id.
• Waybill_airbill_num=Waybill Air bill Number.

RCV_SHIPMENT_LINES

(Shipment_line_id, Shipment_header_id, Line_num, Category_id, Quantity_shipped, Quantity_received, Unit_of_measure,Item_description,Item_id,Source_document_code,PO_Header_id,PO_line_id,PO_release_id,PO_line_location_id,PO_distribution_id,Requisition_line_id,Req_distribution_id,From_organization_id,Employee_id,Destination_type_code,To_organization_id,To_subinventory,Comments,OE_order_header_id,OE_order_line_id,Ship_to_location_id)

• Line_num=line number.
• Quantity_shipped=Shipped quantity.
• Quantity_received=Received quantity.
• Item_description=Item description.
• Item_id=Inventory item.
• From_organization_id=Destination organization id.
• To_organization_id=Source organization id.

• To_subinventory=Source subinventory.
• Commnets=Remarks or description.
• Ship_to_location_id=Location_id in HR_LOCATIONS table.

ORDER MANAGEMENT

OE_ORDER_HEADERS_ALL

(Header_id,Org_id,Order_type_id,Order_number,Version_number,Ordered_date,Pricing_date,Price_list_id,Conversio_type_code,Cust_po_number,Payment_term_id,Freight_terms_code,Sold_from_org_id,Sold_to_org_id,Ship_from_org_id,Ship_to_org_id,Invoice_to_org_id,Salesrep_id,Order_category_code,Flow_status_code)

• Header_id=Order header id.
• Org_id=Operating unit (not inventory organization).
• Order_number=Order number.
• Ordered_date=Order date.
• Price_list_id=List_header_id in QP_SECU_LIST_HEADERS_V.
• Conversion_type_code=Type of Currency code.
• Paymet_term_id=Term id.
• Frieght_terms_code=Ship via.
• Sold_to_org_id=Customer nothing but Cust_account_id in HZ_CUST_ACCOUNTS_ALL table.
• Ship_from_org_id=Inventory_organization (Organization_id in ORG_ORGANZIATION_DEFINITIONS or any inventory table).
• Invoice_to_org_id=Site_use_id from HZ_CUST_SITE_USES_ALL.
• Salesrep_id=Sales representative id.
• Order_category_code=must be order.

OE_ORDER_LINES_ALL

(Line_id,Org_id,Header_id,Line_type_id,Line_number,Ordered_item,Schedule_ship_date,Order_quantity_uom,Pricing_quantity,Pricing_quantity_uom,Shipped_quantity,Cancelled_quantity,Ordered_quantity,Shipping_quantity,Shipping_quantity_uom,Ship_from_org_id,Ship_to_org_id,Invoice_to_org_id,Deliver_to_org_id,Sold_from_org_id,Sold_to_org_id,Cust_po_number,Inventory_item_id,Price_list_id,Shipment_number,Unit_selling_price,Unit_list_price,Cancel_flag,Salesrep_id,Ordered_item_id,Flow_status_code,Inventory_item_id)

• Line_id=Order line id.
• Org_id=Operating unit (not inventory organization).
• Line_type_id=Line type id.
• Line_number=Line number.
• Ordered_item=Item number.
• Order_quantity_uom=Ordered quantity uom (just uom code).
• Price_list_id=List_header_id in QP_SECU_LIST_HEADERS_V.
• Inventory_item_id=Inventory_item_id in MTL_SYSTEM_ITEMS table or any Inventory table.
• Ship_from_org_Id=Organization_id in Mtl_system_items table or any Inventory organization.

WSH_DELIVERABLES_V

(Delivery_detail_id,Delivery_line_id,Delivery_id,Source_code,Source_name,Source_header_id,Source_line_id,Source_header_number,Source_header_type_id,Source_header_type_name,Source_line_number,Src_requested_quantity,Src_requested_quantity_uom,Customer_id,Inventory_item_id,Item_description,Ship_from_location_id,Ship_to_location_id,Deliver_to_location_id,Requested_quantity,Shipped_quantity,Delivered_quantity,Cancelled_quantity,Subinventory,Lot_number,Serial_number,Locator_id,Date_requested,Date_scheduled,Released_status_name,Organization_id,Cust_po_number,Org_id,Unit_price,Currency_code,Ship_to_site_use_id,Deliver_to_site_use_id,Picked_quantity,Vendor_id)

• Source_header_id=Order_header_id.
• Source_line_id=Order_line_id.
• Source_header_number=Order number.
• Source_header_type_id=Order_type_id.
• Source_header_type_name=Order type name.
• Source_line_number=Line number.
• Inventory_item_id=Inventory item.
• Item_description=Item description.
• Requested_quantity=Quantity requested.
• Shipped_quantity=Quantity shipped.
• Delivered_quantity=Quantity delivered.
• Subinventory=Secondary inventory.
• Released_status_name=Released status.
• Organization_id=Inventory organization.
• Cust_po_number=Customer po number.
• Org_id=Operating unit (Not inventory organization).
• Unit_price=Item cost.
• Currency_code=Currency code.
• Picked_quantity=Quantity picked.
• Vendor_id=Supplier id.

WSH_NEW_DELIVERIES

(Delivery_id,Name,Status_code,Customer_id,Confirmed_by,Confirm_date,Delivery_type,Currency_code,Organization_id,Source_header_id,Hash_value)

• Delivery_id=Delivery id.
• Name=Delivery number.
• Customer_id=Customer id.
• Delivery_type=Delivery type.
• Organization_id=Inventory organization.

OE_TRANSACTION_TYPES_TL

(Transaction_type_id, Name, Description)

• Order type name stored in this table.
• Transaction_type_id=Order type id.
• Name=Order type name.
• Description=Order type description.

OE_TRANSACTION_TYPES_ALL

(Transaction_type_id, Transaction_type_code, Order_category_code, Start_date_active, End_date_active, Cust_trx_type_id, Price_list_id, Org_id)

• Transaction_type_id=Order type id.
• Transaction_type_code and Order_category_code must be ORDER.
• Price_list_id=List_header_id in OE_ORDER_HEADERS_ALL.
• Org_id=Operating unit (not Inventory organization).

QP_GRANTS

(Grant_id, Object_id, Instance_type, Instance_id, Grantee_type, Grantee_id, Menu_id, Start_date, End_date)

• Grant_id=Grant id.
• Object_id=Object id.
• Instance_id=Instance id.
• Grantee_type=Type of grantee.
• Sysdate between Start_date and end_date.

QP_LIST_HEADERS_ALL_B

(List_header_id, List_type_code, Start_date_active, End_date_active, Currency_code, Rounding_factor, Frieght_terms_code, Terms_id, Comments, Source_system_code)

• List_header_id=Price_list_id in OE_ORDER_HEADERS_ALL
• List_type_code=Price list type code.
• Frieght_terms_code=Carrying or transporting charges code.
• Terms_id=Payment terms id.
• Comments=Remarks or description.

QP_LIST_HEADERS_TL

(List_header_id, Language, Source_lang, Name, Description, Version_no)

• List_header_id=Price_list_id in OE_ORDER_HEADERS_ALL.
• Language=Language.
• Name=Price list name.
• Description=Price list description.
• Version_no=Price list version number.

NOTE :- 1). Join LIST_HEADER_ID in QP_LIST_HEADERS_ALL_B with INSTANCE_ID in QP_GRANTS.

              2). Join LIST_HEADER_ID in QP_LIST_HEADERS_ALL_B with LIST_HEADER_ID in QP_LIST_HEADERS_TL.

               3).LANGUAGE in QP_LIST_HEADERS_TL=userenv (‘LANG’)

               4).LIST_TYPE_CODE in QP_LIST_HEADERS_ALL_B=’PRL’.

QP_POLICY_LIST_HEADERS_V

• This view includes QP_LIST_HEADERS_ALL_B and QP_LIST_HEADERS_TL tables.

QP_SECU_LIST_HEADERS_V

(Currency_code,Freight_terms_code,List_header_id,Start_date_active,End_date_active,List_type_code,Terms_id,Name,Description,Comments,Source_system_code,Version_no)

• List_header_id=Price_list_id in OE_ORDER_HEADERS_ALL.
• List_type_code=Price list type code.
• Name=Price list name.
• Description=Price list description.
• Comments=Remarks or description.
• Version_no=Price list version number.

JTF_RS_DEFRESOURCES_V

(Resource_id,Category,Catg_meaning,Resource_number,Source_id,Address_id,Start_date_active,End_date_active,User_id,Resource_name,Source_name,Source_number,Source_job_title,Source_email,Source_address1,Source_address2,Source_city,Source_postal_code,Source_country,Source_mgr_name,Source_business_grp_id,Source_business_grp_name,User_name,Source_first_name,Source_last_name)

RA_SALESREPS_ALL

(Salesrep_id, Resource_id, Sales_credit_type_id, Name, Start_date_active, End_date_active, Salesrep_number, Org_id, Email_address, Person_id)

• Salesrep_id=Sales person id.
• Resource_id=Resource id.
• Name=Sales person name.
• Salesrep_number=Sales person number.
• Org_id=Operating unit (not inventory organization).
• Person_id=Person_id in PER_ALL_PEOPLE_F.
• Sysdate between Start_date_active and end_date_active.

AP_TERMS

(Term_id, Type, Start_date_active, End_date_active, Name, Description)

• Term_id=Term id.
• Type=Term type.
• Name=Payment term name.
• Description=Payment term description.

HZ_PARTIES

(Party_id,Party_number,Party_name,Party_type,Country,Address1,Address2,Address3,Address4,City,Country)

• Take Customer names from this table only.
• Party_id=party id.
• Party_name=Customer name.
• Party_number=party number.
• Party_type=party type.

HZ_PARTY_SITES

(Party_site_id, Party_id, Location_id, Party_site_number)

• Party_site_id=Party site id.
• Party_id=Party id.
• Location_id=Site address id.
• Party_site_number=Party site number.

HZ_CUST_ACCOUNTS_ALL

(Cust_account_id, Party_id, Account_number, Paymet_term_id, Order_type_id, Price_list_id)

• Cust_account_id=Customer account id.
• Party_id=Party id.
• Account_number=Customer number.

HZ_CUST_ACCT_SITES_ALL

(Cust_acct_site_id, Cust_acct_id, Party_site_id, Org_id, Language)

• Customer site information stored in this table.
• Cust_acct_site_id=Customer account site id.
• Cust_acct_id=Customer account id.
• Party_site_id=Party site id.
• Org_id=Operating unit (not inventory organization).

HZ_CUST_SITE_USES_ALL

(Site_use_id, Cust_acct_site_id, Site_use_code, Location, Order_type_id, Price_list_id, Territory_id, Org_id)

• Used sites details stored in this table.
• Site_use_id=site use id (Invoice_to_org_id in OE_ORDER_HEADERS_ALL).
• Cust_acct_site_id=Customer account site id.
• Site_use_code=Site use code.

HZ_LOCATIONS

(Location_id, Country, Address1, Address2, Address3, City, Postal_code, State)

• Location_id=Location id in HZ_PARTY_SITES.
• Site addresses stored in this table.

NOTE: - Instead of taking all the above tables for customer details you can use XXNC_CUSTOMER_MASTER_V or XXGF_CUSTOMER_MASTER_V view. It contains all details like customer name, address and customer number etc.

WORK ORDER TABLES

WIP_ENTITIES

(Wip_entity_id, Organization_id, Wip_entity_name, Entity_type, Description)

• Wip_entity_id=Work order entity id.
• Organization_id=Inventory organization.
• Wip_entity_name=Work order number.
• Entity_type=work order type.
• Description=Work order description.

WIP_DISCRETE_JOBS

(Wip_entity_Id,Organization_Id,Description,Status_type,Job_type,Scheduled_start_date,Scheduled_completion_date,Date_released,Date_completed,Date_closed,Start_quantity,Quantity_completed,Net_quantity,PO_creation_time,Asset_number,Asset_group_id,Work_order_type,Owning_department,Acitvity_type,Maintenance_object_id,Created_by)

• Wip_entity_id=Work order entity id.
• Organization_id=Inventory organization.
• Description=Work order description.
• Status_type=Work order status.
• Scheduled_start_date=Start date time.
• Scheduled_completion_date=Comp date time.
• Total duration can be calculated as:

Total duration= ((scheduled_completion_date) – (scheduled_start_date))* 24

• PO_creation_time=PO created time.
• Asset_number=Instance number.
• Work_order_type=Work order type.
• Owning_department=Department id.
• Activity_type=work order activity type.
• Maintenance_object_id=Instance_id in CSI_ITEM_INSTANCES.
• Created_by= User_id in FND_USER table.

EAM_WORK_ORDERS_V

(Wip_entity_id,Wip_entity_name,Entity_type,Organization_id,Description,Asset_number,Asset_description,Status_type,Work_order_type,Work_order_type_disp,Scheduled_start_date,Scheduled_completion_date,Owning_department,Owning_department_Code,Activity_type,Activity_type_disp,Priority,priority_disp,Instance_id,Instance_number,Maintainance_object_id,Work_order_status,Maintenance_object_type,Source)

• Wip_entity_id=Work order entity id.
• Wip_entity_name=Work order entity name.
• Entity_type=Work order type.
• Organization_id=Inventory organization.
• Description=Work order description.
• Asset_number=Instance number.
• Asset_description=Instance description.
• Status_type=Work order status type.
• Work_order_staus=Work order status.
• Work_order_type=Work order type.
• Work_order_type_disp=Work order type description.
• Owning_department=Department_id.
• Owning_department_code=Department code.
• Activity_type=Work order activity type.
• Activity_type_disp=Work order activity type description.
• Instance_number=Asset_number.
• Instance_id=Asset id.
• Maintenance_object_id=Instance_id in CSI_ITEM_INSTANCES.
• Maintenance_object_type=Type of maintenance object type.
• Source=Maintenance object source type.

BOM_DEPARTMENTS

(Department_id, Department_code, Organization_id, Description, Location_id)

• Department_id=Work order department id.
• Department_code=Work order department code.
• Organization_id=Inventory organization.
• Description=Department description.
• Location_id=Inventory location and Location_id in HR_LOCATIONS table.(keep outer join on Location_id in HR_LOCATIONS table)

CSI_ITEM_INSTANCES

(Instance_id,Instance_number,Inventory_item_Id,Inv_master_organization_id,Quantity,Unit_of_measure,Acitve_start_date,Active_end_date,Location_type_code,Location_id,Inv_organization_id,Inv_loactor_id,Inv_subinventory_name,Last_vld_organization_id)

1. Instance_id=Asset id.
2. Instance_number=Asset number.
3. Inventory_item_id=Inventory item id.
4. Inv_master_organization_id=Inventory master organization id or Operating_unit.
5. Unit_of_measure=UOM code.
6. Inv_organization_id=Inventory organization id.
7. Inv_locator_id=Inventory location id.
8. Inv_subinventory_name=Secondary inventory.
9. Last_vld_organization_id=Organization_id in MTL_PARAMETERS (take outer join to mtl_parameters table) nothing but inventory organization id.

WIP_EAM_WORK_REQUESTS

(Work_request_id, Work_request_number, Asset_number, Organization_id, Wip_entity_id, Description, Maintenance_object_id)

10. Work_request_id=Work order request id.
11. Work_request_number=Work order request number.
12. Asset_number=Instance number.
13. Organization_id=Inventory organization.
14. Wip_entity_id=Work order entity id.
15. Description=Work order request description.
16. Maintenance_object_id=Instance_id in CSI_ITEM_INSTANCES.

WIP_OPERATIONS

(Wip_entity_id, Operation_seq_num, Organization_id, Department_id, Description, Scheduled_quantity, Quantity_running)

17. Wip_entity_id=Work order entity id.
18. Operation_seq_num=Operation sequence number.
19. Organization_id=Inventory organization.
20. Department_id=Work order department id.
21. Description=Department description.

WIP_OPERATION_RESOURCES

(Wip_entity_id,Operation_seq_num,Resource_seq_num,Organization_id,Resource_id,UOM_code,Applied_resource_value,Applied_resource_units,Department_id,Usage_rate_or_amount,Assigned_units)

22. Wip_entity_id=Work order entity id.
23. Operation_seq_num=Operation sequence number.
24. Resource_seq_num=Resource sequence number.
25. Organization_id=Inventory organization.
26. Resource_id=Resource id.
27. UOM_code=UOM code.
28. Applied_resource_units=Total actual hours.
29. Department_id=Work order department id.
30. Usage_rate_or_amount=Total planned hours.
31. Assigned_units=No of units.

BOM_RESOURCES

(Resource_id, Resource_code, Organization_id, Description, Unit_of_measure, Resource_type)

32. Resource_id=Resource id.
33. Resource_code=Resource code.
34. Organization_id=Inventory organization.
35. Description=Resource description.
36. Unit_of_measure=UOM code.
37. Resource_type=Resource type.

WIP_REQUIREMENT_OPERATIONS

(Inventory_item_id, Organization_id, Wip_entity_id, Operation_seq_num, Department_id, Required_quantity, Quantity_issued, Segment1, Unit_price, Comments)

38. Inventory_item_id=Inventory item id.
39. Organization_id=Inventory organization.
40. Wip_entity_id=Work order entity id.
41. Operation_seq_num=Operation number.
42. Department_id=Work order Department id.
43. Segment1=Requirement number.
44. Required_quantity=Quantity Required.
45. Quantity_issued=Issued Quantity.

     NOTE:-Balance quantity can be calculated by using the following formulae.

            Balance Quantity= (Required_quantity-Quantity_issued)

MTL_EAM_NETWORK_ASSETS_V

(Network_association_id,Network_item_id,Network_serial_number,Network_asset_number,Network_object_id,Maintenance_object_id,Inventory_item_id,Serial_number,Asset_number,Start_date_active,End_date_active,Organization_id)

46. Network_association_id=Network association id.
47. Network_item_id=Network item id.
48. Network_serial_number=Asset route.
49. Network_assset_number=Network asset number.
50. Maintenance_object_id=Maintenance_object_id in EAM_WORK_ORDERS_V or Instance_id in CSI_ITEM_INSTANCES.
51. Inventory_item_id=Inventory item.
52. Serial_number=Asset Serial number.
53. Asset_number=Instance number.
54. Organization_id=Inventory organization.

MTL_EAM_ASSET_NUMBERS_ALL_V

(Inventory_item_id,Serial_number,Descriptive_text,Network_asset_flag,Inv_organization_code,Concatenated_segments,Asset_group_description,Category_id,Category_name,Category_description,Owning_department_id,Owning_department,Parent_item_id,Parent_serial_number,Parent_instance_number,Instance_number,Inv_organization_id,Maintenance_object_id,Location_id)

55. Inventory_item_id=Inventory item.
56. Serial_number=Asset serial number.
57. Descriptive_text=Serial number description.
58. Inv_organization_code=Inventory organization code.
59. Category_id=Inventory category id.
60. Category_name=Inventory category name.
61. Category_description=Inventory category description.
62. Owning_department_id=Work order department id.
63. Owning_department=Work order department code.
64. Instance_number=Asset number.
65. Inv_organization_id=Inventory organization id.

EAM_JOB_COMPLETION_TXNS

(Transaction_id, Transaction_date, Transaction_type, Wip_entity_id, Organization_id, Asset_group_id, Asset_number, Actual_start_date, Actual_end_date, Actual_duration)

66. Transaction_id=Job transaction id.
67. Transaction_date=Job transaction date.
68. Transaction_type=Job transaction type.
69. Wip_entity_id=Work order entity id.
70. Organization_id=Inventory organization.
71. Asset_number=Instance number.

EAM_WO_RELATIONSHIPS

(WO_relationship_id, Parent_object_id, Child_object_id, Parent_relationship_type, Relation_status)

72. WO_relationship_id=Work order relationship id.
73. Parent_object_id=Parent object id.
74. Child_object_id=Child object id.
75. Parent_relationship_type=Parent relationship type.
76. Relation_status=Relationship status.

    NOTE: - 1). Join Child_object_id in EAM_WO_RELATIONSHIPS with Wip_entity_id in WIP_DISCRETE_JOBS.

                    2). Join Parent_object_id in EAM_WO_RELATIONSHIPS with Wip_entity_id in WIP_ENTITIES.

WIP_SCHEDULE_GROUPS

(Schedule_group_name, Schedule_group_id, Organization_id, Description)

77. Schedule_group_name=Schedule group name.
78. Schedule_group_id=Schedule group id.
79. Organization_id=Inventory organization.
80. Description=Schedule group description.

NOTE: - Join Schedule_group_id in this table with Schedule_group_id in WIP_DISCRETE_JOBS.

EAM_WO_SERVICE_ASSOCIATION

(WO_service_entity_assoc_id, Wip_entity_id, Service_request_id, Enable_flag)

81. WO_service_entity_assoc_id=Service entity association id.
82. Wip_entity_id=Work order entity id.
83. Service_request_id=Service request id.

NOTE: - Here Wip_entity_id is join column and Enable_flag set to ‘Y’.

EAM_ASSET_FAILURES

(Failure_id, Failure_entry_id, Failure_date, Source_type, Source_id, Object_type, Object_id, Department_id)

84. Failure_id=Asset failure id.
85. Failure_date=Asset failure date.
86. Source_type=Asset failure source type.
87. Source_id=Asset failure source id.
88. Department_id=Asset work order department id.

      NOTE: - Join Source_id in EAM_ASSET_FAILURES table with Wip_entity_id in WIP_DISCRETE_JOBS table.

EAM_ASSET_FAILURE_CODES

(Failure_id, Failure_entry_id, Failure_code, Combination_id, Cause_code, Resolution_code, Commnets)

89. Failure_id=Asset failure id.
90. Failure_code=Asset failure code.
91. Cause_code=Asset failure cause code.
92. Resolution_code=Asset resolution code.
93. Comments=Description.

        NOTE: - Join Failure_id in EAM_ASSET_FAILURE_CODES table with Failure_id in EAM_ASSET_FAILURES table.

EAM_WORK_ORDER_DETAILS_V

(Wip_entity_id, Organization_id, User_defined_status_id, Work_order_status)

94. Wip_entity_id=Work order entity id.
95. Organization_id=Inventory organization.
96. Work_order_status=Work order status.

EAM_WORK_ORDER_DETAILS

(Wip_entity_id, Organization_id, User_defined_status_id, Estimate_id)

97. Wip_entity_id=Work order entity id.
98. Organization_id=Inventory organization.
99. Estimate_id=Estimate id in EAM_CONSTRUCTION_ESTIMATES.

EAM_CONSTRUCTION_ESTIMATES

(Estimate_id, Organization_id, Estimate_number, Estimate_description)

100. Estimate_id=Work order estimation id.
101. Organization_id=Inventory organization.
102. Estimate_number=Estimation number.
103. Estimate_description=Estimation description.

        NOTE: - Join Estimate_id in this table with Estimate_id in EAM_WORK_ORDER_DETAILS table.

EAM_WORK_ORDER_STATUSES_V

(Status_id, System_status, Work_order_status, System_status_desc, Enabled_flag)

104. Status_id=Work order status id.
105. System_status=System status.
106. Work_order_status=Work order status description.
107. System_status_desc=System status description.

EAM_WORK_ORDER_STATUSES_B

(Status_id, System_status, Enabled_flag)

108. Status_id=Work order status id.
109. System_status=System status.

EAM_WORK_ORDER_STATUSES_TL

(Status_id, Language, User_defined_status)

110. Status_id=Work order status id.
111. Language=Language.
112. User_defined_status=User defined work order status.

NOTE: - Join Status_id in EAM_WORK_ORDER_STATUSES_TL, EAM_WORK_ORDER_STATUSES_B tables with User_defined_status_id in EAM_WORK_ORDER_DETAILS table.

FND_FLEX_VALUES_VL

(Flex_value_set_id, Flex_value_id, Flex_value, Parent_flex_value_low, Flex_value_meaning, Description)

113. Flex_value_set_id=Flex value set id.
114. Flex_value_id=Flex value id.
115. Flex_value=Flex value.

FND_FLEX_VALUE_SETS

(Flex_value_set_id, Flex_value_set_name, Description)

116. Flex_value_set_id=Flex value set id.
117. Flex_value_set_name=Flex value set name.
118. Description=Flex value set description.

    NOTE: - 1). Join Flex_value_set_id in FND_FLEX_VALUE_SETS table with Flex_value_set_id in           FND_FLEX_VALUES_VL Table.

                       2). Specify Flex_value_set_name for segment1 and segment2.

                       3). Flex_value in FND_FLEX_VALUES_VL table is equals to segment1 and segment2 in MTL_CATEGORIES                   table.       

                       4). When segment2 is dependent on segment1 then join Flex_value for segment2 with Parent_flex_value_low for segment1.

HR_LOCATIONS_ALL         

(Location_id,Location_code,Address_line_1,Address_line_2,Address_line_3,Country,Description,Inventory_organization_id,Postal_code,Region_1,Region_2,Ship_to_location_id,Town_or_city)

119. Location_id=Location_id in MTL_SECONDARY_INVENTORIES table (Nothing but Inventory Location id), Deliver_to_location_id in PO_REQUISITION_LINES_ALL table and Ship_to_location_id in RCV_SHIPMENT_LINES table and Ship_to_location_id or Bill_to_location_id in PO_HEADERS_ALL table.
120. Location_code=Location code.
121. Address_line_1, Address_line_2, Address_line_3, Country Comes under Address.
122. Description=Location description.
123. Inventory_organization_id=Inventory organization id.
124. Postal_code=Postal code.

NOTE: - PO_AGENTS table does not contain Agent Name. To retrieve Agent name you must use PER_ALL_PEOPLE_F or HR_EMPLOYEES tables.  

HR_EMPLOYEES

(Employee_id, Employee_num, Full_name, Prefix)

125. Employee_id=Agent_id in PO_AGENTS table.
126. Employee_num=Agent or Employee Number.
127. Full_name=Agent or Buyer Name.
128. Prefix=Prefix contains either MR. or MS. or MRS. or etc.

PER_ALL_PEOPLE_F

(Person_id,Effective_start_date,Effective_end_date,Business_group_id,Person_type_id,Last_name,Applicant_number,Date_of_birth,Email_address,Employee_number,First_name,Full_name,Marital_status,Middle_names,Nationality,National_identifier,Sex,Title,Date_of_death,Town_of_birth,Country_of_birth,Party_id,Global_name,Local_name,Known_as,Title)

129. Contains all the information about Person.
130. Known_as=Employee Short Name.
131. Title=Contains Person Identification like either MR. or MRS. or MISS or MS.
132. Full_name=Requestor or Buyer Name or Agent Name.
133. Person_id=Employee_id in HR_EMPLOYEES table, Agent_id in PO_AGENTS table.

NOTE: - Trunc (Sysdate) must between Trunc (effective_start_date) and Trunc (effective_end_date)

FND_USER

(User_id, User_name, Session_number, Employee_id)

134. Employee_id=Person_id in PER_ALL_PEOPLE_F table.
135. User_id=Created_by in WIP_DISCSRETE_JOBS table.
136. User_name=User Name.

PER_ALL_ASSIGNMENTS_F

(Assignment_id,Effective_start_date,Effective_end_date,Business_group_id,Job_id,Location_id,Person_id,Organization_id,Assignment_number,Title,Vendor_id,Vendor_site_id,PO_Header_id,PO_line_id)

137. Assignment_id=Assignment id.
138. Effective_start_date=Effective Start date.
139. Effective_end_date=Effective end date.
140. Business_group_id=Business_group_id in PER_BUSINESS_GROUPS, ORG_ORGANIZATION_DEFINITIONS, HR_OPERATING_UNITS tables.
141. Job_id=Job_id in PER_JOBS table.
142. Location_id=Inventory Location.
143. Person_id=Employee_id in PER_ALL_PEOPLE_F table.
144. Organization_id=Inventory organization.
145. Assignment_number=Assignment Number.
146. Title=Assignment title.
147. Vendor_id=Vendor id.
148. Vendor_site_id=Vendor site id.
149. PO_header_id=PO Header id.
150. PO_line_id=PO Line id.

NOTE: - Trunc (Sysdate) must between Trunc (Effective_start_date) and Trunc (Effective_end_date).

PER_ASSIGNMENTS_F

151. This table is same as PER_ALL_ASSIGNMENTS_F table.

PER_JOBS     (Job_id, Business_group_id, Name)

152. Job_id=Job_id in PER_ALL_ASSIGNMENTS_F table.
153. Business_group_id=Business_group_id in HR_OPERATING_UNITS, ORG_ORGANIZATION_DEFINITIONS, PER_ALL_ASSIGNMENTS_F tables.
154. Name=Job Name nothing but designation of employee.

NOTE: - We can find which concurrent program is defined under which responsibility and request group by using the following tables.

FND_CONCURRENT_PROGRAMS_VL

(Concurrent_program_id, Concurrent_program_name, Executable_id, User_concurrent_program_name, Description)

155. Concurrent_program_id=Concurrent program id.
156. Concurrent_program_name=Short code for a concurrent program.
157. Executable_id=Executable id.
158. User_concurrent_program_name=Program name for a concurrent program.
159. Description=Concurrent program name description.

FND_EXECUTABLES_VL

(Executable_id, Executable_name, Description)

160. Executable_id=Executable id.
161. Executable_name=Executable name.
162. Description=Executable Name description.

NOTE: -  Join FND_CONCURRENT_PROGRAMS_VL, FND_EXECUTABLES_CL tables with Executable_id.

FND_REQUEST_GROUPS

(Request_group_id, Request_group_name, Description)

163. Request_group_id=Request group id.

164. Request_group_name=Request group name.
165. Description=Request Group name description.

FND_RESPONSIBILITY

(Responsibility_id, Request_group_id, Responsibility_key, Data_group_id)

166. Responsibility_id=Responsibility id.
167. Request_group_id=Request group id.
168. Responsibility_key=Responsibility key.
169. Data_group_id=Data group id.

     NOTE: - Join FND_RESPONSIBILITY and FND_REQUEST_GROUPS tables with Request_group_id.

FND_RESPONSIBILITY_VL

(Responsibility_id, Responsibility_key, Data_group_id, Request_group_id, Responsibility_name, Description)

170. Responsibility_id=Responsibility id.
171. Responsibility_key=Responsibility key.
172. Data_group_id=Data group id.
173. Responsibility_name=Responsibility name.
174. Description=Responsibility Description.

      NOTE: - 1) Join FND_RESPONSIBILITY_VL table and FND_RESPONSIBILITY table with Responsibility_id.

                      2) Join FND_RESPONSIBILITY_VL table and FND_REQUEST_GROUPS tables with Request_group_id.

FND_CONCURRENT_REQUESTS

(Request_id, Concurrent_program_id, Responsibility_id, Org_id)

175. Request_id=Request id.
176. Concurrent_program_id=Concurrent program id.
177. Responsibility_id=Responsibility id.
178. Org_id=Operating Unit (not Inventory Organization).

      NOTE: - 1) Join FND_CONCURRENT_REQUESTS table and FND_RESPONSIBILITY table with Responsibility_id.

                       2) Join FND_CONCURRENT_REQUESTS table and FND_RESPONSIBILITY_VL table with Responsibility_id.

                       3) Join FND_CONCURRENT_REQUESTS table and FND_CONCURRENT_PORGRAMS_VL with Concurrent_program_id.

FND_ATTACHED_DOCUMENTS

(Attached_document_id, Document_id, Seq_num, Entity_name, PK1_value, Program_id, Category_id)

179. Attached_document_id=Attached document id.
180. Document_id=Document_id in FND_DOCUMENTS_TL table.
181. Seq_num=Sequence number.
182. Entity_name=Entity name.
183. PK1_value=Requisition_header_id in PO_REQUISITION_HEADERS_ALL table, Requisition_line_id in PO_REQUISITION_LINES_ALL, PO_header_id in PO_HEADERS_ALL table, PO_line_id in PO_LINES_ALL table.
184. Program_id=Concurrent_program_id in FND_CONCURRENT_PROGRAMS_VL table.
185. Category_id=Inventory category.

FND_DOCUMENTS_TL

(Document_id, Description, Media_id, Program_id)

186. Document_id=Document_id in FND_ATTACHED_DOCUMENTS table.
187. Description=Document description.
188. Media_id=Media_id in FND_DOCUMENTS_SHORT_TEXT, FND_DOCUMENTS_LONG_TEXT tables.
189. Program_id=Concurrent_program_id in FND_CONCURRENT_PROGRAMS_VL table.

FND_DOCUMENTS_SHORT_TEXT

(Media_id, Short_text)

190. Media_id=Media_id in FND_DOCUMENTS_TL table, Media_id in FND_DOCUMENTS_LONG_TEXT table.
191. Short_text=Short text of a document.

FND_DOCUMENTS_LONG_TEXT

(Media_id, Long_text)

192. Media_id=Media_id in FND_DOCUMENTS_TL, FND_DOCUMENTS_SHORT_TEXT tables.
193. Long_text=Long text of a document.

FND_DOCUMENT_CATEGORIES_TL

(Category_id, Language, Name, User_name)

194. Category_id=Category_id in FND_ATTACHED_DOCUMENTS table.
195. Name=Category Description or Category Name.

MFG_LOOKUPS

(Lookup_type, Lookup_code, Meaning, Description, Start_date_active, End_date_active, Created_by)

196. Lookup_type=Look up type.
197. Lookup_code=Look up code and Planning_make_buy_code in MTL_SYSTEM_ITEMS table.
198. Meaning=Look up meaning.
199. Description=Look up description.

Data Definition and Template Tables:

XDO_LOBS

(Lob_type,Application_short_name,Lob_code,Language,Territory,XDO_file_type,File_name)

200. Lob_type=Lob_type is whether BURSTING_FILE or DATA_TEMPLATE or TEMPLATE or XML_SCHEMA or XML_SAMPLE or etc.
201. Application_short_name=Application short name.
202. Lob_code=Short code.
203. File_name=File name.

XDO_DS_DEFINITIONS_VL

(Application_short_name,Data_source_code,Data_source_name,Description)

204. Application_short_name=Application short name.
205. Data_source_code=Data definition code.
206. Data_source_name=Data definition name.
207. Description=Data definition description.

XDO_TEMPLATES_VL

(Template_id,Application_id,Application_short_name,Template_code,DS_app_short_name,Data_source_code,Template_type_code,Template_name,Description)

208. Template_id=Template id.
209. Application_id=Application id.
210. Application_short_name=Application short name.
211. Template_code=Template code.
212. DS_app_short_name=Data definition short name.
213. Data_source_code=Data definition code.
214. Template_type_code=Template code.
215. Template_name=Template name.
216. Description=Template description.