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_idin 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_codein 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_idin 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_locatorfrom 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_CONVERSIONSor 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_numin WIP_REQUIREMENT_OPERATIONS table.
· Transaction_source_id=Wip_entity_idin 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_VLtable.
· Ship_to_location_id and Bill_to_location_id= Location_id in HR_LOCATIONS_ALLtable.
· 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_idin 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 quantityfrom 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_ALLtable.
· Org_id=Operating unit. (Not inventory Organization).
· Ship_to_organization_id=Organization_idin ORG_ORGANZIATION_DEFINITIONStable.
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 tablefor 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_ALLtable.
· Req_distribution_id=Distribution_idin 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_alltable.
· 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_idin 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_idin 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_idin PER_ALL_PEOPLE_F table.
· Suggested_buyer_id=Agent_idin 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_idin 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_ALLtable.
· 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_NAMEfrom PER_ALL_PEOPLE_F as buyer or agent name.
· Category_id=Category_idin 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_idin PO_HEADERS_ALL table.
· Vendor_site_id=Vendor_site_idin 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_CONTACTStable.
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_idin 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_ALLtable.
· Ship_from_org_id=Inventory_organization (Organization_id in ORG_ORGANZIATION_DEFINITIONSor 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_idin QP_SECU_LIST_HEADERS_V.
· Inventory_item_id=Inventory_item_idin MTL_SYSTEM_ITEMS table or any Inventory table.
· Ship_from_org_Id=Organization_idin 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_IDin 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_Band 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_idin 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_idin 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_LOCATIONStable.(keep outer join on Location_id in HR_LOCATIONStable)
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_idin 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_idin 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_idin 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_idin WIP_DISCRETE_JOBS.
2). Join Parent_object_id in EAM_WO_RELATIONSHIPSwith 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_idin this table with Schedule_group_idin 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_idin 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_idin 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_idin 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_idin FND_FLEX_VALUE_SETS table with Flex_value_set_id in FND_FLEX_VALUES_VLTable.
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 segment2with 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_idin MTL_SECONDARY_INVENTORIES table (Nothing but Inventory Location id), Deliver_to_location_idin 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_idin 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_idin 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_idin 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_idin 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_Ftable.
PER_JOBS (Job_id, Business_group_id, Name)
· Job_id=Job_id in PER_ALL_ASSIGNMENTS_F table.
· Business_group_id=Business_group_idin 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_VLtable 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_idin FND_DOCUMENTS_TL table.
· Seq_num=Sequence number.
· Entity_name=Entity name.
· PK1_value=Requisition_header_idin 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_idin FND_CONCURRENT_PROGRAMS_VL table.
· Category_id=Inventory category.
FND_DOCUMENTS_TL
(Document_id, Description, Media_id, Program_id)
· Document_id=Document_idin 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_idin 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_idin 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_codein 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_FILEor DATA_TEMPLATE or TEMPLATE or XML_SCHEMA or XML_SAMPLEor 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