Tuesday, May 7, 2013

Oracle Applications INV,PO,SUPPLIERS,OM and Work Order 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)
·         Instance_id=Asset id.
·         Instance_number=Asset number.
·         Inventory_item_id=Inventory item id.
·         Inv_master_organization_id=Inventory master organization id or Operating_unit.
·         Unit_of_measure=UOM code.
·         Inv_organization_id=Inventory organization id.
·         Inv_locator_id=Inventory location id.
·         Inv_subinventory_name=Secondary inventory.
·         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)
·         Work_request_id=Work order request id.
·         Work_request_number=Work order request number.
·         Asset_number=Instance number.
·         Organization_id=Inventory organization.
·         Wip_entity_id=Work order entity id.
·         Description=Work order request description.
·         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)
·         Wip_entity_id=Work order entity id.
·         Operation_seq_num=Operation sequence number.
·         Organization_id=Inventory organization.
·         Department_id=Work order department id.
·         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)
·         Wip_entity_id=Work order entity id.
·         Operation_seq_num=Operation sequence number.
·         Resource_seq_num=Resource sequence number.
·         Organization_id=Inventory organization.
·         Resource_id=Resource id.
·         UOM_code=UOM code.
·         Applied_resource_units=Total actual hours.
·         Department_id=Work order department id.
·         Usage_rate_or_amount=Total planned hours.
·         Assigned_units=No of units.
BOM_RESOURCES
(Resource_id, Resource_code, Organization_id, Description, Unit_of_measure, Resource_type)
·         Resource_id=Resource id.
·         Resource_code=Resource code.
·         Organization_id=Inventory organization.
·         Description=Resource description.
·         Unit_of_measure=UOM code.
·         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)
·         Inventory_item_id=Inventory item id.
·         Organization_id=Inventory organization.
·         Wip_entity_id=Work order entity id.
·         Operation_seq_num=Operation number.
·         Department_id=Work order Department id.
·         Segment1=Requirement number.
·         Required_quantity=Quantity Required.
·         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)
·         Network_association_id=Network association id.
·         Network_item_id=Network item id.
·         Network_serial_number=Asset route.
·         Network_assset_number=Network asset number.
·         Maintenance_object_id=Maintenance_object_id in EAM_WORK_ORDERS_V or Instance_id in CSI_ITEM_INSTANCES.
·         Inventory_item_id=Inventory item.
·         Serial_number=Asset Serial number.
·         Asset_number=Instance number.
·         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)
·         Inventory_item_id=Inventory item.
·         Serial_number=Asset serial number.
·         Descriptive_text=Serial number description.
·         Inv_organization_code=Inventory organization code.
·         Category_id=Inventory category id.
·         Category_name=Inventory category name.
·         Category_description=Inventory category description.
·         Owning_department_id=Work order department id.
·         Owning_department=Work order department code.
·         Instance_number=Asset number.
·         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)
·         Transaction_id=Job transaction id.
·         Transaction_date=Job transaction date.
·         Transaction_type=Job transaction type.
·         Wip_entity_id=Work order entity id.
·         Organization_id=Inventory organization.
·         Asset_number=Instance number.
EAM_WO_RELATIONSHIPS
(WO_relationship_id, Parent_object_id, Child_object_id, Parent_relationship_type, Relation_status)
·         WO_relationship_id=Work order relationship id.
·         Parent_object_id=Parent object id.
·         Child_object_id=Child object id.
·         Parent_relationship_type=Parent relationship type.
·         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)
·         Schedule_group_name=Schedule group name.
·         Schedule_group_id=Schedule group id.
·         Organization_id=Inventory organization.
·         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)
·         WO_service_entity_assoc_id=Service entity association id.
·         Wip_entity_id=Work order entity id.
·         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)
·         Failure_id=Asset failure id.
·         Failure_date=Asset failure date.
·         Source_type=Asset failure source type.
·         Source_id=Asset failure source id.
·         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)
·         Failure_id=Asset failure id.
·         Failure_code=Asset failure code.
·         Cause_code=Asset failure cause code.
·         Resolution_code=Asset resolution code.
·         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)
·         Wip_entity_id=Work order entity id.
·         Organization_id=Inventory organization.
·         Work_order_status=Work order status.
EAM_WORK_ORDER_DETAILS
(Wip_entity_id, Organization_id, User_defined_status_id, Estimate_id)
·         Wip_entity_id=Work order entity id.
·         Organization_id=Inventory organization.
·         Estimate_id=Estimate id in EAM_CONSTRUCTION_ESTIMATES.
EAM_CONSTRUCTION_ESTIMATES
(Estimate_id, Organization_id, Estimate_number, Estimate_description)
·         Estimate_id=Work order estimation id.
·         Organization_id=Inventory organization.
·         Estimate_number=Estimation number.
·         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)
·         Status_id=Work order status id.
·         System_status=System status.
·         Work_order_status=Work order status description.
·         System_status_desc=System status description.
EAM_WORK_ORDER_STATUSES_B
(Status_id, System_status, Enabled_flag)
·         Status_id=Work order status id.
·         System_status=System status.
EAM_WORK_ORDER_STATUSES_TL
(Status_id, Language, User_defined_status)
·         Status_id=Work order status id.
·         Language=Language.
·         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)
·         Flex_value_set_id=Flex value set id.
·         Flex_value_id=Flex value id.
·         Flex_value=Flex value.
FND_FLEX_VALUE_SETS
(Flex_value_set_id, Flex_value_set_name, Description)
·         Flex_value_set_id=Flex value set id.
·         Flex_value_set_name=Flex value set name.
·         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)
·         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.
·         Location_code=Location code.
·         Address_line_1, Address_line_2, Address_line_3, Country Comes under Address.
·         Description=Location description.
·         Inventory_organization_id=Inventory organization id.
·         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)
·         Employee_id=Agent_id in PO_AGENTS table.
·         Employee_num=Agent or Employee Number.
·         Full_name=Agent or Buyer Name.
·         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)
·         Contains all the information about Person.
·         Known_as=Employee Short Name.
·         Title=Contains Person Identification like either MR. or MRS. or MISS or MS.
·         Full_name=Requestor or Buyer Name or Agent Name.
·         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)
·         Employee_id=Person_id in PER_ALL_PEOPLE_F table.
·         User_id=Created_by in WIP_DISCSRETE_JOBS table.
·         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)
·         Assignment_id=Assignment id.
·         Effective_start_date=Effective Start date.
·         Effective_end_date=Effective end date.
·         Business_group_id=Business_group_id in PER_BUSINESS_GROUPS, ORG_ORGANIZATION_DEFINITIONS, HR_OPERATING_UNITS tables.
·         Job_id=Job_id in PER_JOBS table.
·         Location_id=Inventory Location.
·         Person_id=Employee_id in PER_ALL_PEOPLE_F table.
·         Organization_id=Inventory organization.
·         Assignment_number=Assignment Number.
·         Title=Assignment title.
·         Vendor_id=Vendor id.
·         Vendor_site_id=Vendor site id.
·         PO_header_id=PO Header id.
·         PO_line_id=PO Line id.
NOTE: - Trunc (Sysdate) must between Trunc (Effective_start_date) and Trunc (Effective_end_date).

PER_ASSIGNMENTS_F
·         This table is same as PER_ALL_ASSIGNMENTS_F table.
PER_JOBS     (Job_id, Business_group_id, Name)
·         Job_id=Job_id in PER_ALL_ASSIGNMENTS_F table.
·         Business_group_id=Business_group_id in HR_OPERATING_UNITS, ORG_ORGANIZATION_DEFINITIONS, PER_ALL_ASSIGNMENTS_F tables.
·         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)
·         Concurrent_program_id=Concurrent program id.
·         Concurrent_program_name=Short code for a concurrent program.
·         Executable_id=Executable id.
·         User_concurrent_program_name=Program name for a concurrent program.
·         Description=Concurrent program name description.
FND_EXECUTABLES_VL
(Executable_id, Executable_name, Description)
·         Executable_id=Executable id.
·         Executable_name=Executable name.
·         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)
·         Request_group_id=Request group id.
·         Request_group_name=Request group name.
·         Description=Request Group name description.
FND_RESPONSIBILITY
(Responsibility_id, Request_group_id, Responsibility_key, Data_group_id)
·         Responsibility_id=Responsibility id.
·         Request_group_id=Request group id.
·         Responsibility_key=Responsibility key.
·         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)
·         Responsibility_id=Responsibility id.
·         Responsibility_key=Responsibility key.
·         Data_group_id=Data group id.
·         Responsibility_name=Responsibility name.
·         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)
·         Request_id=Request id.
·         Concurrent_program_id=Concurrent program id.
·         Responsibility_id=Responsibility id.
·         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)

·         Attached_document_id=Attached document id.
·         Document_id=Document_id in FND_DOCUMENTS_TL table.
·         Seq_num=Sequence number.
·         Entity_name=Entity name.
·         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.
·         Program_id=Concurrent_program_id in FND_CONCURRENT_PROGRAMS_VL table.
·         Category_id=Inventory category.

FND_DOCUMENTS_TL
(Document_id, Description, Media_id, Program_id)
·         Document_id=Document_id in FND_ATTACHED_DOCUMENTS table.
·         Description=Document description.
·         Media_id=Media_id in FND_DOCUMENTS_SHORT_TEXT, FND_DOCUMENTS_LONG_TEXT tables.
·         Program_id=Concurrent_program_id in FND_CONCURRENT_PROGRAMS_VL table.

FND_DOCUMENTS_SHORT_TEXT
(Media_id, Short_text)
·         Media_id=Media_id in FND_DOCUMENTS_TL table, Media_id in FND_DOCUMENTS_LONG_TEXT table.
·         Short_text=Short text of a document.
FND_DOCUMENTS_LONG_TEXT
(Media_id, Long_text)
·         Media_id=Media_id in FND_DOCUMENTS_TL, FND_DOCUMENTS_SHORT_TEXT tables.
·         Long_text=Long text of a document.
FND_DOCUMENT_CATEGORIES_TL
(Category_id, Language, Name, User_name)
·         Category_id=Category_id in FND_ATTACHED_DOCUMENTS table.
·         Name=Category Description or Category Name.


MFG_LOOKUPS
(Lookup_type, Lookup_code, Meaning, Description, Start_date_active, End_date_active, Created_by)
·         Lookup_type=Look up type.
·         Lookup_code=Look up code and Planning_make_buy_code in MTL_SYSTEM_ITEMS table.
·         Meaning=Look up meaning.
·         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)
·         Lob_type=Lob_type is whether BURSTING_FILE or DATA_TEMPLATE or TEMPLATE or XML_SCHEMA or XML_SAMPLE or etc.
·         Application_short_name=Application short name.
·         Lob_code=Short code.
·         File_name=File name.
XDO_DS_DEFINITIONS_VL
(Application_short_name,Data_source_code,Data_source_name,Description)
·         Application_short_name=Application short name.
·         Data_source_code=Data definition code.
·         Data_source_name=Data definition name.
·         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)
·         Template_id=Template id.
·         Application_id=Application id.
·         Application_short_name=Application short name.
·         Template_code=Template code.
·         DS_app_short_name=Data definition short name.
·         Data_source_code=Data definition code.
·         Template_type_code=Template code.
·         Template_name=Template name.
·         Description=Template description.

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect