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