BOM Tables

Following are the BOM main tables.

•  bom_bill_of_materials
•  bom_inventory_components
•  bom_reference_designators
•  bom_substitute_components
•  mtl_item_revisions
•  bom_operational_routings
•  bom_operation_sequences
•  bom_operation_resources
•  BOM_BILL_OF_MTLS_INTERFACE
•  BOM_INVENTORY_COMPS_INTERFACE
•  BOM_REF_DESGS_INTERFACE
•  BOM_SUB_COMPS_INTERFACE
•  MTL_ITEM_REVISIONS_INTERFACE
•  BOM_OP_ROUTINGS_INTERFACE
•  BOM_OP_SEQUENCES_INTERFACE
•  BOM_OP_RESOURCES_INTERFACE
•  MTL_RTG_ITEM_REVS_INTERFACE

 =======================

Following are all tables of BOM.

•  BOM_ALTERNATE_DESIGNATORS
•  BOM_ALTERNATE_DESIGNATORS_TL
•  BOM_ATO_CONFIGS_TEMP
•  BOM_ATO_CONFIGURATIONS
•  BOM_BILLS_HEADER_TEMP
•  BOM_BILL_OF_MATERIALS
•  BOM_BILL_OF_MATERIALS_ARCHIVE
•  BOM_BILL_OF_MATERIALS_TEMP
•  BOM_BILL_OF_MTLS_INTERFACE
•  BOM_BILL_REVISIONS_B
•  BOM_BILL_REVISIONS_TL
•  BOM_CALENDARS
•  BOM_CALENDAR_DATES
•  BOM_CALENDAR_EXCEPTIONS
•  BOM_CALENDAR_SHIFTS
•  BOM_CAL_WEEK_START_DATES
•  BOM_CAL_YEAR_START_DATES
•  BOM_CMP_USR_ATTR_INTERFACE
•  BOM_COMPARISON_TEMP
•  BOM_COMPONENTS_B
•  BOM_COMPONENTS_EXT_B
•  BOM_COMPONENTS_EXT_TL
•  BOM_COMPONENT_COLUMNS
•  BOM_COMPONENT_OPERATIONS
•  BOM_COMPONENT_OPS_INTERFACE
•  BOM_COMPONENT_TYPES
•  BOM_COMP_OPTCLASS_TEMP
•  BOM_COMP_OPTIONS_TEMP
•  BOM_CONFIG_EXPLOSIONS
•  BOM_COPY_ORGANIZATION_LIST
•  BOM_COPY_STRUCTURE_ACTIONS
•  BOM_COPY_STRUCTURE_REQUEST
•  BOM_CTO_MLSUPPLY_MAIN_TEMP
•  BOM_CTO_MODEL_ORGS
•  BOM_CTO_ORDER_DEMAND
•  BOM_CTO_ORDER_LINES
•  BOM_CTO_ORDER_LINES_UPG
•  BOM_CTO_OSS_COMPONENTS
•  BOM_CTO_OSS_ORGS_LIST
•  BOM_CTO_SRC_ORGS
•  BOM_CTO_SRC_ORGS_B
•  BOM_DELETE_ENTITIES
•  BOM_DELETE_ERRORS
•  BOM_DELETE_GROUPS
•  BOM_DELETE_SQL_STATEMENTS
•  BOM_DELETE_SUB_ENTITIES
•  BOM_DEPARTMENTS
•  BOM_DEPARTMENT_CLASSES
•  BOM_DEPARTMENT_RESOURCES
•  BOM_DEPENDENT_DESC_ELEMENTS
•  BOM_DEPEND_DESC_ELEM_ARCHIVE
•  BOM_DEPT_RES_INSTANCES
•  BOM_EAM_DEPT_APPROVERS
•  BOM_EXCEPTION_SETS
•  BOM_EXCEPTION_SET_DATES
•  BOM_EXCLUSION_RULE_ARCHIVE
•  BOM_EXCLUSION_RULE_DEF
•  BOM_EXPLOSIONS
•  BOM_EXPLOSIONS_ALL
•  BOM_EXPLOSION_TEMP
•  BOM_FORM_QUERY
•  BOM_IMPLOSION_TEMP
•  BOM_INTERFACE_DELETE_GROUPS
•  BOM_INVENTORY_COMPONENTS
•  BOM_INVENTORY_COMPONENTS_TEMP
•  BOM_INVENTORY_COMPS_ARCHIVE
•  BOM_INVENTORY_COMPS_INTERFACE
•  BOM_INV_COMPONENTS_TEMP
•  BOM_ITEM_ATTACH_CATEGORY_ASSOC
•  BOM_ITEM_BACKFLUSH_SUBINV
•  BOM_LISTS
•  BOM_LOW_LEVEL_CODES
•  BOM_MIXED_MODEL_MAP_CELLS
•  BOM_MIXED_MODEL_MAP_HEADER
•  BOM_MIXED_MODEL_MAP_PROCESSES
•  BOM_MIXED_MODEL_MAP_RES
•  BOM_OPERATIONAL_ROUTINGS
•  BOM_OPERATION_NETWORKS
•  BOM_OPERATION_RESOURCES
•  BOM_OPERATION_RESOURCES_EFC
•  BOM_OPERATION_SEQUENCES
•  BOM_OPERATION_SEQUENCES_TEMP
•  BOM_OPERATION_SKILLS
•  BOM_OP_NETWORKS_INTERFACE
•  BOM_OP_RESOURCES_ARCHIVE
•  BOM_OP_RESOURCES_INTERFACE
•  BOM_OP_ROUTINGS_ARCHIVE
•  BOM_OP_ROUTINGS_INTERFACE
•  BOM_OP_SEQUENCES_ARCHIVE
•  BOM_OP_SEQUENCES_INTERFACE
•  BOM_PARAMETERS
•  BOM_PERIOD_START_DATES
•  BOM_REFERENCE_DESGS_ARCHIVE
•  BOM_REFERENCE_DESIGNATORS
•  BOM_REF_DESGS_INTERFACE
•  BOM_RESOURCES
•  BOM_RESOURCES_EFC
•  BOM_RESOURCE_CHANGES
•  BOM_RESOURCE_DOWNCODES
•  BOM_RESOURCE_EMPLOYEES
•  BOM_RESOURCE_EQUIPMENTS
•  BOM_RESOURCE_FLEX_FENCES
•  BOM_RESOURCE_SETUPS
•  BOM_RESOURCE_SHIFTS
•  BOM_RES_INSTANCE_CHANGES
•  BOM_RTGHEADER_TEMP
•  BOM_RTG_ITEM_REVISIONS_ARCHIVE
•  BOM_RTG_NETWORK_TEMP
•  BOM_RTG_OPERATION_TEMP
•  BOM_RTG_OPER_RES_TEMP
•  BOM_RTG_RESOURCE_TEMP
•  BOM_RTG_SUB_RESOURCE_TEMP
•  BOM_RULES_B
•  BOM_SETUP_TRANSITIONS
•  BOM_SETUP_TYPES
•  BOM_SHIFT_DATES
•  BOM_SHIFT_EXCEPTIONS
•  BOM_SHIFT_TIMES
•  BOM_SMALL_EXPL_TEMP
•  BOM_SMALL_IMPL_TEMP
•  BOM_STANDARD_OPERATIONS
•  BOM_STD_OP_BONUS_CODES
•  BOM_STD_OP_RESOURCES
•  BOM_STD_OP_RESP_EXCLUSIONS
•  BOM_STD_OP_SCRAP_CODES
•  BOM_STD_SUB_OP_RESOURCES
•  BOM_STRUCTURES_B
•  BOM_STRUCTURES_EXT_B
•  BOM_STRUCTURES_EXT_TL
•  BOM_STRUCTURE_EXT_B
•  BOM_STRUCTURE_REVISIONS_B
•  BOM_STRUCTURE_REVISIONS_TL
•  BOM_STRUCTURE_TYPES_B
•  BOM_STRUCTURE_TYPES_TL
•  BOM_SUBSTITUTE_COMPONENTS
•  BOM_SUBSTITUTE_COMPS_ARCHIVE
•  BOM_SUB_COMPS_INTERFACE
•  BOM_SUB_OPERATION_RESOURCES
•  BOM_SUB_OPERATION_RESOURCE_EFC
•  BOM_SUB_OP_RESOURCES_INTERFACE
•  BOM_WORKDAY_PATTERNS

Read More»

FND Concurrent Request Status Codes and Phase codes Meaning

 

Read More»

SQL Query to find out Parameters and Value Sets associated with a Concurrent Program

SELECT
fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name",
fdfcuv.column_seq_num "Column Seq Number",
fdfcuv.end_user_column_name "Parameter Name",
fdfcuv.form_left_prompt "Prompt",
fdfcuv.enabled_flag " Enabled Flag",
fdfcuv.required_flag "Required Flag",
fdfcuv.display_flag "Display Flag",
fdfcuv.flex_value_set_id "Value Set Id",
ffvs.flex_value_set_name "Value Set Name",
flv.meaning "Default Type",
fdfcuv.DEFAULT_VALUE "Default Value"
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE
fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name like 'XX%'
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.LANGUAGE = USERENV ('LANG')
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY fdfcuv.column_seq_num;

Read More»

How to make a Oracle form 'Query Only'

There are many ways in which we can make a form Query only. We are going to discuss two
1) Using parameter QUERY_ONLY=YES
2) using Form Personalization
Method 1: Using parameter QUERY_ONLY=YES
Lets take an example of Purchase order enter form

Step 1) Using Sysadmin Responsibility find out the Menu attached to Purchasing Super User responsibility for which you want to make Purchase Order form as read only.

Step 2) Now Navigate to Application>Menu and query for this form and find out the form function associate with enter purchase order form. In our case it is "Purchase Orders"

Step 3) Create a new form function name having logically similar name as that of original function and in the form tab make QUERY_ONLY=YES

Step 4) Attach this new function in the place of old function to the same menu

Save and let Compile Security Request complete

Test ResultNavigate to Purchasing Super User> Purchase Order

See now this form is read only

Method 2 : Using Form Personalization

Step 1) Navigate to PO form>Help>Diagnostic>custom Code>Personalize

Step 2) Now here you can disable Update, Delete and Insert

Test Case : Check PO form now

Thanks

Mandeep

Read More»

Important WF tables

WF_USER_ROLE_ASSIGNMENTS stores information about the direct and inherited assignments of users to roles in Oracle .

WF_USER_ROLES stores associations of users with the roles of which they are members.

WF_ROLES stores Roles, which can have one or more users as members.

WF_LOCAL_ROLES stores role information, including a user flag to mark those roles that also represent individual users. This table contains columns similar to those required in the WF_USERS and WF_ROLES views.

WF_LOCAL_USER_ROLES stores information about the associations of users with roles. This table contains columns similar to those required in the WF_USER_ROLES view.

WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modeled workflow process, which can be included as an activity in other processes to represent a sub process. A notification activity sends a message to a performer. A functions activity performs an automated function that is written as a PL/SQL stored procedure. A folder activity is not part of a process, it provides a means of grouping activities.

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.

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 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_MESSAGES contains the definitions of messages which may be sent out as notifications.

WF_MESSAGE_ATTRIBUTES contains message attribute definitions.

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system.

WF_ITEM_TYPES: defines an item that is transitioning through a workflow process.

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.

WF_ITEM_ACTIVITY_STATUSES 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.

WF_ITEM_ACTIVITY_STATUSES_H  stores the history of the WF_ITEM_ACTIVITY_STATUSES table.

WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process.

WF_ACTIVITY_TRANSITIONS 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.

WF_ACTIVITY_ATTR_VALUES contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute.

Thanks
Mandeep

Read More»

R12 SQL Query to differentiate Customer and Suppliers Data

select * from (
select hp.party_number,hp.party_name,hp.status,decode(nvl(hpu.party_usage_code,hp.party_type),'ORGANIZATION','CUSTOMER',nvl(hpu.party_usage_code,hp.party_type)) party_type 
from  apps.HZ_PARTY_USG_ASSIGNMENTS hpu, hz_parties hp
where hp.party_id = hpu.party_id(+))
order by party_type

Read More»

Important HRMS Tables

• per_all_people_f 

This table is used to store employee related data.

• per_all_assignments_f

This table store employee assignment related information and connected to per_all_people_f with person_id and assignment number.

• per_addresses

This table store  information related to employee address form and  connected to per_all_people_f with person_id.

• per_pay_proposals

This table will store information related to employee salary form.

• per_person_types_tl

This table store type of the employee information and connected to per_all_people_f with the person_type_id.

• per_jobs_tl

This table is for JOBS in Oracle and can be linked to per_all_assignments_f.

• per_grades_tl

This table stores various types of GRADES and can be linked to per_all_assignments_f.

• hr_locations_all

Location table

• pay_all_payrolls_f

PAYROLLS Table and can be linked to per_all_assignments_f table

• per_pay_bases

Contain the PAY BASES can be linked to  per_all_assignments_f table.

• per_assignment_status_types_tl

Contain the assignment types and linked to per_all_assignments_f table

• per_person_type_usages_f

This table store the person type of the employee. 

Read More»

Sql query for relation between Business Group, Legal Entity, Operating Unit and Balancing segment

select HRL.COUNTRY,
  HROUTL_BG.name BG,
  HROUTL_BG.ORGANIZATION_ID,
  LEP.LEGAL_ENTITY_ID,
  LEP.name LEGAL_ENTITY,
  HROUTL_OU.name OU_NAME,
  HROUTL_OU.ORGANIZATION_ID ORG_ID,
  HRL.LOCATION_ID,
  HRL.LOCATION_CODE,
  GLEV.FLEX_SEGMENT_VALUE
from XLE_ENTITY_PROFILES LEP,
  XLE_REGISTRATIONS REG,
  HR_LOCATIONS_ALL HRL,
  HZ_PARTIES HZP,
  FND_TERRITORIES_VL TER,
  HR_OPERATING_UNITS HRO,
  HR_ALL_ORGANIZATION_UNITS_TL HROUTL_BG,
  HR_ALL_ORGANIZATION_UNITS_TL HROUTL_OU,
  HR_ORGANIZATION_UNITS GLOPERATINGUNITSEO,
  GL_LEGAL_ENTITIES_BSVS GLEV
where LEP.TRANSACTING_ENTITY_FLAG      = 'Y'
and LEP.PARTY_ID                       = HZP.PARTY_ID
and LEP.LEGAL_ENTITY_ID                = REG.SOURCE_ID
and REG.SOURCE_TABLE                   = 'XLE_ENTITY_PROFILES'
and HRL.LOCATION_ID                    = REG.LOCATION_ID
and REG.IDENTIFYING_FLAG               = 'Y'
and TER.TERRITORY_CODE                 = HRL.COUNTRY
and LEP.LEGAL_ENTITY_ID                = HRO.DEFAULT_LEGAL_CONTEXT_ID
and GLOPERATINGUNITSEO.ORGANIZATION_ID = HRO.ORGANIZATION_ID
and HROUTL_BG.ORGANIZATION_ID          = HRO.BUSINESS_GROUP_ID
and HROUTL_OU.ORGANIZATION_ID          = HRO.ORGANIZATION_ID
and GLEV.LEGAL_ENTITY_ID               = LEP.LEGAL_ENTITY_ID;

Read More»

SQL Query To Check Form Personalizations

SELECT   ffcr.SEQUENCE "Seq", ffcr.description "Description",
         DECODE (ffcr.rule_type,
                 'F', 'Form',
                 'A', 'Function',
                 'Other'
                ) "Level",
         ffcr.enabled "Enabled", ffcr.trigger_event "Trigger Event",
         ffcr.trigger_object "Trigger Object", ffcr.condition "Condition",
         DECODE (ffcr.fire_in_enter_query,
                 'Y', 'Both',
                 'N', 'Not in Enter-Query Mode',
                 'O', 'Only in Enter-Query Mode',
                 'Other'
                ) "Processing Mode"
    FROM apps.fnd_form_custom_rules ffcr
   WHERE ffcr.function_name = 'PO_POXPOEPO'
     AND ffcr.form_name = 'POXPOEPO'
ORDER BY ffcr.SEQUENCE;

Read More»

PO Interface Tables

PO_HEADERS_INTERFACE is used to import Purchase Order's Header Level Information

PO_LINES_INTERFACE is used to import Purchase order's line level information.

PO_DISTRIBUTIONS_INTERFACE is used to import Distribution Level Information

PO_REQUISITIONS_INTERFACE_ALL used to import requisition's information from other applications/Modules.

PO_REQ_DIST_INTERFACE_ALL: used to import requisition's Distribution  information from other applications/Modules.

RCV_HEADERS_INTERFACE is the interface table that stores receiving header information.

RCV_LOTS_INTERFACE : this table stores temporary lot number transaction records for a parent record in the RCV_TRANSACTIONS_INTERFACE table.

RCV_TRANSACTIONS_INTERFACE : stores information about receiving transactions that are waiting to be processed by the receiving transaction processor.

Thanks
Mandeep

Read More»

How to Find the Table name on OAF Page

Step 1) Set the profile option "FND: Diagnostics" at User Level to "Yes"

set FND : Diagnostics

Step 2) Navigate to respective OAF page and Click "About this Page" link at the bottom as shown in below screenshot

Click About this Page

Step 3) Navigate to Page > View Object and click on this. You can see the table name from there.

Thanks
Mandeep
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Please share this knowledge with your friends and subscribe at our blog for more updates. If you have any queries, please participate in our Forum or by leaving your comments below.
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Read More»