Thursday, May 16

Important tables for workflows and usages

important tables for workflows

wf_notifications_tl

wf_item_types_tl

WF_ACTIVITIES

WF_PROCESS_ACTIVITIES ( used to dig the linking between process and sub process/function)

WF_ITEM_TYPES
The WF_ITEM_TYPES table defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE
WF_ITEM_ATTRIBUTES
The WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
WF_ACTIVITIES
WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders.ITEM_TYPE (PK), NAME (PK), VERSION(PK), TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG
WF_ACTIVITY_ATTRIBUTES
The WF_ACTIVITY_ATTRIBUTES table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.
ACTIVITY_ITEM_TYPE (PK), ACTIVITY_NAME (PK), ACTIVITY_VERSION (PK), NAME (PK), SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
WF_MESSAGES
WF_MESSAGES contains the definitions of messages which may be sent out as notifications. TYPE (PK), NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL
WF_MESSAGE_ATTRIBUTES
WF_MESSAGE_ATTRIBUTES contains message attribute definitions.
WF_NOTIFICATIONS
WF_NOTIFICATIONS holds the runtime information about a specific instance of a sent message. A new row is created in the table each time a message is sent.
WF_NOTIFICATION_ATTRIBUTES
WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.
WF_ITEMS
WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system. ITEM_TYPE (PK), ITEM_KEY (PK), ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, BEGIN_DATE
WF_ITEM_ACTIVITY_STATUSES
The WF_ITEM_ACTIVITY_STATUSES TABLE is the runtime table for a work item. Each row includes the start and end date, result code, and any error information an activity generates. ITEM_TYPE (PK), ITEM_KEY (PK), PROCESS_ACTIVITY (PK)
WF_ITEM_ACTIVITY_STATUSES_H
The WF_ITEM_ACTIVITY_STATUSES_H table stores the history of the WF_ITEM_ACTIVITY_STATUSES table. ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
WF_PROCESS_ACTIVITIES
WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID (PK), INSTANCE_LABEL, PERFORM_ROLE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL
WF_ACTIVITY_TRANSITIONS
The WF_ACTIVITY_TRANSITIONS table defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window. FROM_PROCESS_ACTIVITY (PK), RESULT_CODE (PK), TO_PROCESS_ACTIVITY (PK), PROTECT_LEVEL,CUSTOM_LEVEL

WF_ACTIVITY_ATTR_VALUES
The WF_ACTIVITY_ATTR_VALUES table contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute. PROCESS_ACTIVITY_ID (PK), NAME (PK), VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

Tuesday, May 14

Oracle Apps Interview Questions

1. Why do we call FND SRWINIT from Before Report Trigger

A. FND SRWINIT fetches concurrent request information and sets up the profile options. It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing)

2. Why do we call FND SRWEXIT from After Report Trigger

A. FND SRWEXIT frees all the memory allocations done in other Oracle Applications user exits. It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing)

3. Why do we call FND FLEXSQL from the Before Report Trigger?

A. One need to pass the concatenated segment values from the underlying code combinations table to the user exit so that it can display appropriate data and derive any description and values from switched value sets as needed. One gets this information by calling the AOL user exit FND FLEXSQL from the before report Trigger.

4. If u call the user exit FND FLEXSQL with MODE = “ WHERE” from the Before Report Trigger. What will it do?

A. This user exit populates a lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the WHERE clause of the report query. This user exit is called once for each lexical to be changed.

5. If u call the user exit FND FLEXSQL with MODE = “ ORDER BY” from the Before Report Trigger. What will it do?

A. This user Exit populates the lexical parameter that one specifies with the appropriate SQL fragment at run time. One includes this lexical parameter in the ORDER BY clause of the report query. This user exit is called once for each lexical to be changed.

6. How can we display flexfield segment values, descriptions, and prompts on the report?

A. Create a formula Column. Call the user exit FND FLEXIDVAL as the formula for this column. This user exit automatically fetches more complicated information such as descriptions and prompts so that one does not has to use complicated table joins to the flex field tables.

7. Name some options of the FND FLEXSQL user exit

A CODE, APP_SHORT_NAME, OUTPUT, MODE, DISPLAY, SHOWDEPSEG, NUM or MULTINUM, TABLEALIAS, OPERATOR, OPERAND1, OPERAND2.

8. Describe CODE option of the FND FLEXSQL user exit

A. Specify the flex field code for the report (for example, GL#, MCAT).

9. Describe the APP_SHORT_NAME option of the FND FLEXSQL user exit

A. Specifies the short name of the application that owns the flex field (for example: SQLGL, INV)

10. Describe the OUTPUT option of the FND FLEXSQL user exit

A. Specify the name of the lexical parameter to store the SQl fragment. One uses this lexical later in the report when defining the SQL statement that selects the flexfield values. the datatype of this parameter should be character.

11. Describe the MODE option of the FND FLEXSQL user exit

A. Specify the mode to use to generate the SQL fragment . valid mode are :

SELECT: Retrieves all segments values in an internal (non- displayable format).

WHERE: Restrict the query by specifying constraints on flexfield columns. The fragment returned includes the correct decode statement if one specifies MULTINUM. One must also specify an OPERATOR and OPERANDS.

HAVING: Same calling procedures and functionality as WHERE.

ORDER BY: Order required information by flexfield columns. The fragment Orders your flexfield columns and separates them with a comma. The fragment returned includes the correct decode statement, one specifies in MULTINUM.

12. Describe the DISPLAY option of the FND FLEXSQL user exit

A. One uses the DISPLAY token with the MODE token . the DISPLAY parameter allows you to specify segments that represent specified flexfield qualifiers or specified segments numbers , where the segment numbers are the order in that the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form.

Eg. If your MODE is SELECT and you specify DISPLAY = “ALL” then the SELECT statement includes all the segments of the flexfield. . Similarly, if your MODE is WHERE and you specify DISPLAY = “ALL”, then your WHERE clause includes all segments.

13. Describe the SHOWDEPSEG option of the FND FLEXSQL user exit

A. SHOWDEPSEG = “N” disables automatic addition of depended upon segments to the order criteria. The default is “Y”. This token is valid only for MODE = “ODER BY” In FLEXSQL.

14. Describe the NUM option of the FND FLEXSQL user exit

A. Specify the name or lexical or source column that contains the flexfield structure information. If the flexfield uses just one structure, specify NUM only and use a lexical parameter to hold the value. If the flexfield uses multiple structures, specify MULTINUM only and use a source column to hold the value. The default value is 101.

15. Describe the TABLE ALIAS option of the FND FLEXSQL user exit

A. You use TABLE ALIAS if your SELECT joins to other flexfield tables or uses a self – join.

16. Describe the OPERATOR option of the FND FLEXSQL user exit

A. Specify an operator to use in the WHERE clause.

17. Describe the OPERAND1 option of the FND FLEXSQL user exit

A. Specify an operand to use in the WHERE clause,

18. Describe the OPERAND2 option of the FND FLEXSQL user exit

A. Specify a second operand to use with OPERATOR = “BETWEEN”

19. Where is FND FLEXIDVAL user exit used

A. Call this user exit to populate fields for display. You pass the key flex fields data retrieved by the query into this user exit from the formula column. With this exit you can display values, descriptions and prompts by passing appropriate token (any one of VALUE, DECRIPTION<APROMPT or LPROMPT).

21. Name the interface tables used for the customer interface?

A. 1. RA_CUSTOMERS_INTERFACE_ALL

2. RA_CUSTOMER_BANKS_INT_ALL

3. RA_CUST_PAY_METHOD_INT_ALL

4. RA_CUSTOMER_PROFILES_INT_ALL

5. RA_CONTACT_PHONES_INT_ALL

22 What is the name of the column in CUSTOMER_INTERFACE_TABLE that indicates whether you are inserting new or updating existing information?

A: When importing data into the interface tables, the column INSERT_UPDATE_FLAG indicates whether you are inserting new or updating existing information. This column is required in RA_CUSTOMERS_INTERFACE.

23 If the INSERT_UPDATE_FLAG is not set correctly or the required column is missing the value, will CUSTOMER INTERFACE reject the entire record or just the attributes u want to update?

A Reject the entire record.

24 List some of the required columns for the RA_CUSTOMERS_INTERFACE?

A. ORIG_SYSTEM_CUSTOMER_REF

INSERT_UPDATE_FLAG

CUSTOMER_NAME

CUSTOMER_NUMBER (if you are not using Automatic Customer Numbering)

CUSTOMER_STATUS

LAST_UPDATED_BY

LAST_UPDATE_DATE

CREATED_BY

CREATION_DATE

If you are importing an address and a business purpose, you must also populate the following columns:

PRIMARY_SITE_USE_FLAG (if you are inserting an address)

LOCATION (if you are not using Automatic Site Numbering)

SITE_USE_CODE (if you are inserting an address)

ADDRESS1

25 List some of the production tables that Customer Interface transfers customer data from the interface tables into?

A. AR_CUSTOMER_PROFILES

AR_CUSTOMER_PROFILE_AMOUNTS

RA_ADDRESSES

RA_CONTACTS

RA_CUSTOMERS

RA_CUSTOMER_RELATIONSHIPS

RA_CUST_RECEIPT_METHODS

RA_PHONES

RA_SITE_USES

AP_BANK_ACCOUNT_USES

AP_BANK_ACCOUNTS

AP_BANK_BRANCHES

26. What validation must be given on the customer_number?

A Must be null if you are using Automatic Customer Numbering. Must exist if you are not using Automatic Customer Numbering. This value must be unique within RA_CUSTOMERS.

27 What validation must be given on the CUSTOMER_STATUS?

A Must equal ’A’ for Active or ’I’ for Inactive.

28 Name some of the Oracle receivables Interfaces?

A a) Auto Invoice

b) Auto Lockbox

c) Customer Interface

d) Sales Tax rate Interface

e) Tax Vendor Extension

29 Give some of the Oracle Payables interface?

A. a) Credit Card Transaction Interface

b) Invoice Import Interface

c) Payables Open Interface

d) Purchase Order Matching

30. Name some of the oracle general ledger Interface?

A a) Budget Upload

b) Importing Journals

c) Loading Daily rates

31. What are the names of the parameters u pass to the Procedure which u register in the apps?

A. 1) retcode in varchar2

2) errbuf in varchar2

32. What is the use of Auto lock Box?

A Auto Lockbox (or Lockbox) is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing.

33. Auto Lockbox is a three-step process, what are those?

A. a) Import

b) Validation

c) PostQuickCash

34. What is the order in which Autolock box searches for the types of the matching number?

A. 1. Transaction Number

2. Sales Order Number

3. Purchase Order Number

4. Consolidated Billing Invoice Number

5. Other, user-defined number.

35. What is application short name for General Ledger you specify in FND FLEXSQL user exit?

A. SQLGL

36 . What are validations to be done in Journal Import interface.

A. Batch level: Set of Books, Period Name, and Batch Name

Journal Level: Set of books, Period name, Source name, Journal entry name, Currency code, Category name, Actual flag, Encumbrance type ID, User conversion type, Accounting date, Budget version ID

37. What subclass in forms6i

A Specifies module, storage & name information about the source object and source module for a referenced objects.

38. What is the clause in SQL * Loader to program to override data into table

A. REPLACE

39. How do you set profile in oracle applications In Application Developer responsibility?

A Open ‘Profile’ Function

40. What is the syntax for loading data through SQL * Loader from multiple files simultaneously

A. Sqlldr scott/tiger@orcl control = ctlfile

parfile -- parameter file: name of file that contains parameter specifications

parallel -- do parallel load (Default FALSE)

41 What is the table name for items in Oracle Inventory

A MTL_SYSTEM_ITEMS, MTL_CATEGORIES

42. Tell me names of important production tables & their purpose AP, AR, GL, PO

A AP: AP_INVOICES_ALL, AP_INVOICE_LINES_ALL

To store invoices

AR: RA_SHIPMENT_HEADERS/ _LINES, RA_CUSTOMERS, RA_CONTACTS

PO: PO_VENDORS, PO_VENDOR_SITES - For storing vendor data.

43. Name the interface tables used for the LockBox Interface

A Interface table : AR_PAYMENTS_INTERFACE_ALL

Lockbox transfers the receipts that pass validation to the Receivables interim tables AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL

When you run Post QuickCash, the receipt data is transferred from the QuickCash tables to the following Receipt tables:

AR_CASH_RECEIPTS_ALL

AR_RECEIVABLES_APPLICATIONS_ALL

AR_CASH_RECEIPT_HISTORY_ALL

44 Name the interface tables used for the AutoInvoice Interface.

A AutoInvoice transfers transaction data from the interface tables

RA_INTERFACE_LINES_ALL,

RA_INTERFACE_SALESCREDITS_ALL, and

RA_INTERFACE_DISTRIBUTIONS_ALL

into the following Receivables tables:

• RA_BATCHES_ALL

• RA_CUSTOMER_TRX _ALL

• RA_CUSTOMER_TRX_LINES _ALL

• RA_CUST_TRX_LINE_GL_DIST_ALL

• RA_CUST_TRX_LINE_SALESREPS_ALL

• AR_PAYMENT_SCHEDULES_ALL

• AR_RECEIVABLE_APPLICATIONS_ALL

• AR_ADJUSTMENTS_ALL

Thursday, May 9

Oracle Puchase Order Types

Tuesday, May 7

Oracle Applications INV,PO,SUPPLIERS,OM and Work Order tables

INVENTORY TABLES

A raw material comes under Inventory.

HR_OPERATING_UNITS

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

· Business_group_id->Any organization starts with business group and each organization must have unique business group id.

· Organization_id->Different organization id’s under business group id.

· Name -> Corresponding organization names for defined organization id’s under business group id.

· Short_code->Nothing but Operating Unit.

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

ORG_ORGANIZATION_DEFINITIONS

(Business_group_id, Organization_id, Organization_name, Organization_code, Operating_unit)

· Operating_unit=organization_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.

add

About Me

Thursday, May 16

Important tables for workflows and usages

Tuesday, May 14

Oracle Apps Interview Questions

A. This user exit populates a lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the WHERE clause of the report query. This user exit is called once for each lexical to be changed.

21. Name the interface tables used for the customer interface?

Thursday, May 9

Oracle Puchase Order Types

Tuesday, May 7

Oracle Applications INV,PO,SUPPLIERS,OM and Work Order tables

Topics

Page Rank

Restricted to Copy