Wednesday, October 15, 2014

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)
  1. Work_request_id=Work order request id.
  2. Work_request_number=Work order request number.
  3. Asset_number=Instance number.
  4. Organization_id=Inventory organization.
  5. Wip_entity_id=Work order entity id.
  6. Description=Work order request description.
  7. 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)
  1. Wip_entity_id=Work order entity id.
  2. Operation_seq_num=Operation sequence number.
  3. Organization_id=Inventory organization.
  4. Department_id=Work order department id.
  5. 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)
  1. Wip_entity_id=Work order entity id.
  2. Operation_seq_num=Operation sequence number.
  3. Resource_seq_num=Resource sequence number.
  4. Organization_id=Inventory organization.
  5. Resource_id=Resource id.
  6. UOM_code=UOM code.
  7. Applied_resource_units=Total actual hours.
  8. Department_id=Work order department id.
  9. Usage_rate_or_amount=Total planned hours.
  10. Assigned_units=No of units.
BOM_RESOURCES
(Resource_id, Resource_code, Organization_id, Description, Unit_of_measure, Resource_type)
  1. Resource_id=Resource id.
  2. Resource_code=Resource code.
  3. Organization_id=Inventory organization.
  4. Description=Resource description.
  5. Unit_of_measure=UOM code.
  6. 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)
  1. Inventory_item_id=Inventory item id.
  2. Organization_id=Inventory organization.
  3. Wip_entity_id=Work order entity id.
  4. Operation_seq_num=Operation number.
  5. Department_id=Work order Department id.
  6. Segment1=Requirement number.
  7. Required_quantity=Quantity Required.
  8. 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)
  1. Network_association_id=Network association id.
  2. Network_item_id=Network item id.
  3. Network_serial_number=Asset route.
  4. Network_assset_number=Network asset number.
  5. Maintenance_object_id=Maintenance_object_id in EAM_WORK_ORDERS_V or Instance_id in CSI_ITEM_INSTANCES.
  6. Inventory_item_id=Inventory item.
  7. Serial_number=Asset Serial number.
  8. Asset_number=Instance number.
  9. 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)
  1. Inventory_item_id=Inventory item.
  2. Serial_number=Asset serial number.
  3. Descriptive_text=Serial number description.
  4. Inv_organization_code=Inventory organization code.
  5. Category_id=Inventory category id.
  6. Category_name=Inventory category name.
  7. Category_description=Inventory category description.
  8. Owning_department_id=Work order department id.
  9. Owning_department=Work order department code.
  10. Instance_number=Asset number.
  11. 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)
  1. Transaction_id=Job transaction id.
  2. Transaction_date=Job transaction date.
  3. Transaction_type=Job transaction type.
  4. Wip_entity_id=Work order entity id.
  5. Organization_id=Inventory organization.
  6. Asset_number=Instance number.
EAM_WO_RELATIONSHIPS
(WO_relationship_id, Parent_object_id, Child_object_id, Parent_relationship_type, Relation_status)
  1. WO_relationship_id=Work order relationship id.
  2. Parent_object_id=Parent object id.
  3. Child_object_id=Child object id.
  4. Parent_relationship_type=Parent relationship type.
  5. 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)
  1. Schedule_group_name=Schedule group name.
  2. Schedule_group_id=Schedule group id.
  3. Organization_id=Inventory organization.
  4. 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)
  1. WO_service_entity_assoc_id=Service entity association id.
  2. Wip_entity_id=Work order entity id.
  3. 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)
  1. Failure_id=Asset failure id.
  2. Failure_date=Asset failure date.
  3. Source_type=Asset failure source type.
  4. Source_id=Asset failure source id.
  5. 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)
  1. Failure_id=Asset failure id.
  2. Failure_code=Asset failure code.
  3. Cause_code=Asset failure cause code.
  4. Resolution_code=Asset resolution code.
  5. 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)
  1. Wip_entity_id=Work order entity id.
  2. Organization_id=Inventory organization.
  3. Work_order_status=Work order status.
EAM_WORK_ORDER_DETAILS
(Wip_entity_id, Organization_id, User_defined_status_id, Estimate_id)
  1. Wip_entity_id=Work order entity id.
  2. Organization_id=Inventory organization.
  3. Estimate_id=Estimate id in EAM_CONSTRUCTION_ESTIMATES.
EAM_CONSTRUCTION_ESTIMATES
(Estimate_id, Organization_id, Estimate_number, Estimate_description)
  1. Estimate_id=Work order estimation id.
  2. Organization_id=Inventory organization.
  3. Estimate_number=Estimation number.
  4. 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)
  1. Status_id=Work order status id.
  2. System_status=System status.
  3. Work_order_status=Work order status description.
  4. System_status_desc=System status description.
EAM_WORK_ORDER_STATUSES_B
(Status_id, System_status, Enabled_flag)
  1. Status_id=Work order status id.
  2. System_status=System status.
EAM_WORK_ORDER_STATUSES_TL
(Status_id, Language, User_defined_status)
  1. Status_id=Work order status id.
  2. Language=Language.
  3. 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)
  1. Flex_value_set_id=Flex value set id.
  2. Flex_value_id=Flex value id.
  3. Flex_value=Flex value.
FND_FLEX_VALUE_SETS
(Flex_value_set_id, Flex_value_set_name, Description)
  1. Flex_value_set_id=Flex value set id.
  2. Flex_value_set_name=Flex value set name.
  3. 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)
  1. 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.
  2. Location_code=Location code.
  3. Address_line_1, Address_line_2, Address_line_3, Country Comes under Address.
  4. Description=Location description.
  5. Inventory_organization_id=Inventory organization id.
  6. 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)
  1. Employee_id=Agent_id in PO_AGENTS table.
  2. Employee_num=Agent or Employee Number.
  3. Full_name=Agent or Buyer Name.
  4. 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)
  1. Contains all the information about Person.
  2. Known_as=Employee Short Name.
  3. Title=Contains Person Identification like either MR. or MRS. or MISS or MS.
  4. Full_name=Requestor or Buyer Name or Agent Name.
  5. 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)
  1. Employee_id=Person_id in PER_ALL_PEOPLE_F table.
  2. User_id=Created_by in WIP_DISCSRETE_JOBS table.
  3. 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)
  1. Assignment_id=Assignment id.
  2. Effective_start_date=Effective Start date.
  3. Effective_end_date=Effective end date.
  4. Business_group_id=Business_group_id in PER_BUSINESS_GROUPS, ORG_ORGANIZATION_DEFINITIONS, HR_OPERATING_UNITS tables.
  5. Job_id=Job_id in PER_JOBS table.
  6. Location_id=Inventory Location.
  7. Person_id=Employee_id in PER_ALL_PEOPLE_F table.
  8. Organization_id=Inventory organization.
  9. Assignment_number=Assignment Number.
  10. Title=Assignment title.
  11. Vendor_id=Vendor id.
  12. Vendor_site_id=Vendor site id.
  13. PO_header_id=PO Header id.
  14. PO_line_id=PO Line id.
NOTE: - Trunc (Sysdate) must between Trunc (Effective_start_date) and Trunc (Effective_end_date).
PER_ASSIGNMENTS_F
  1. This table is same as PER_ALL_ASSIGNMENTS_F table.
PER_JOBS     (Job_id, Business_group_id, Name)
  1. Job_id=Job_id in PER_ALL_ASSIGNMENTS_F table.
  2. Business_group_id=Business_group_id in HR_OPERATING_UNITS, ORG_ORGANIZATION_DEFINITIONS, PER_ALL_ASSIGNMENTS_F tables.
  3. 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)
  1. Concurrent_program_id=Concurrent program id.
  2. Concurrent_program_name=Short code for a concurrent program.
  3. Executable_id=Executable id.
  4. User_concurrent_program_name=Program name for a concurrent program.
  5. Description=Concurrent program name description.
FND_EXECUTABLES_VL
(Executable_id, Executable_name, Description)
  1. Executable_id=Executable id.
  2. Executable_name=Executable name.
  3. 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)
  1. Request_group_id=Request group id.
  1. Request_group_name=Request group name.
  2. Description=Request Group name description.
FND_RESPONSIBILITY
(Responsibility_id, Request_group_id, Responsibility_key, Data_group_id)
  1. Responsibility_id=Responsibility id.
  2. Request_group_id=Request group id.
  3. Responsibility_key=Responsibility key.
  4. 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)
  1. Responsibility_id=Responsibility id.
  2. Responsibility_key=Responsibility key.
  3. Data_group_id=Data group id.
  4. Responsibility_name=Responsibility name.
  5. 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)
  1. Request_id=Request id.
  2. Concurrent_program_id=Concurrent program id.
  3. Responsibility_id=Responsibility id.
  4. 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)
  1. Attached_document_id=Attached document id.
  2. Document_id=Document_id in FND_DOCUMENTS_TL table.
  3. Seq_num=Sequence number.
  4. Entity_name=Entity name.
  5. 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.
  6. Program_id=Concurrent_program_id in FND_CONCURRENT_PROGRAMS_VL table.
  7. Category_id=Inventory category.
FND_DOCUMENTS_TL
(Document_id, Description, Media_id, Program_id)
  1. Document_id=Document_id in FND_ATTACHED_DOCUMENTS table.
  2. Description=Document description.
  3. Media_id=Media_id in FND_DOCUMENTS_SHORT_TEXT, FND_DOCUMENTS_LONG_TEXT tables.
  4. Program_id=Concurrent_program_id in FND_CONCURRENT_PROGRAMS_VL table.
FND_DOCUMENTS_SHORT_TEXT
(Media_id, Short_text)
  1. Media_id=Media_id in FND_DOCUMENTS_TL table, Media_id in FND_DOCUMENTS_LONG_TEXT table.
  2. Short_text=Short text of a document.
FND_DOCUMENTS_LONG_TEXT
(Media_id, Long_text)
  1. Media_id=Media_id in FND_DOCUMENTS_TL, FND_DOCUMENTS_SHORT_TEXT tables.
  2. Long_text=Long text of a document.
FND_DOCUMENT_CATEGORIES_TL
(Category_id, Language, Name, User_name)
  1. Category_id=Category_id in FND_ATTACHED_DOCUMENTS table.
  2. Name=Category Description or Category Name.
MFG_LOOKUPS
(Lookup_type, Lookup_code, Meaning, Description, Start_date_active, End_date_active, Created_by)
  1. Lookup_type=Look up type.
  2. Lookup_code=Look up code and Planning_make_buy_code in MTL_SYSTEM_ITEMS table.
  3. Meaning=Look up meaning.
  4. 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)
  1. Lob_type=Lob_type is whether BURSTING_FILE or DATA_TEMPLATE or TEMPLATE or XML_SCHEMA or XML_SAMPLE or etc.
  2. Application_short_name=Application short name.
  3. Lob_code=Short code.
  4. File_name=File name.
XDO_DS_DEFINITIONS_VL
(Application_short_name,Data_source_code,Data_source_name,Description)
  1. Application_short_name=Application short name.
  2. Data_source_code=Data definition code.
  3. Data_source_name=Data definition name.
  4. 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)
  1. Template_id=Template id.
  2. Application_id=Application id.
  3. Application_short_name=Application short name.
  4. Template_code=Template code.
  5. DS_app_short_name=Data definition short name.
  6. Data_source_code=Data definition code.
  7. Template_type_code=Template code.
  8. Template_name=Template name.
  9. Description=Template description.

1 comment:

Anonymous said...

Thank you so much.
you saved me hours of effort.
bless you man.
10/10 for the info.

Post a Comment

Best Blogger TipsGet Flower Effect