Oracle Payroll table Name

Oracle Payroll table Names

Oracle Payroll Table Name	Table Description
PAY_ACCRUAL_BANDS	Length of service bands used in calculating accrual of paid time off.
PAY_ACCRUAL_PLANS	PTO accrual plan definitions, (Paid time off).
PAY_ACTION_CLASSIFICATIONS	Payroll Action Type classifications.
PAY_ACTION_CONTEXTS	Assignment Action Contexts.
PAY_ACTION_INFORMATION	Archived data stored by legislation
PAY_ACTION_INTERLOCKS	Assignment action interlock definitions to control rollback processing.
PAY_ACTION_PARAMETERS	Global parameters to control process execution.
PAY_ACTION_PARAMETER_GROUPS	Groups of Pay Action Parameters
PAY_ACTION_PARAMETER_VALUES	Values for the specified action parameters
PAY_AC_VENDOR_MAPPINGS	North American Table to control the mapping of internal Values to External Vendor Values
PAY_ALL_PAYROLLS_F	Payroll group definitions.
PAY_ASSIGNMENT_ACTIONS	Action or process results, showing which assignments have been processed by a specific payroll action, or process.
PAY_ASSIGNMENT_LATEST_BALANCES	Denormalised assignment level latest balances.
PAY_ASSIGNMENT_LINK_USAGES_F	Intersection between PAY_ELEMENT_LINKS_F and PER_ALL_ASSIGNMENTS_F.
PAY_AU_MODULES	Defines the processes that can be executed by the generic code caller.
PAY_AU_MODULE_PARAMETERS	Defines the parameters associated with the module used in the generic code
PAY_AU_MODULE_TYPES	Defines the module types used in the generic code caller
PAY_AU_PROCESSES	This table defines the processes that can be executed by the generic code caller.
PAY_AU_PROCESS_MODULES	Defines the intersection between processes and modules used by the generic code caller.
PAY_AU_PROCESS_PARAMETERS	Defines the parameters for a process.
PAY_BACKPAY_RULES	Balances to be recalculated by a RetroPay process.
PAY_BACKPAY_SETS	Identifies backpay, or RetroPay sets.

PAY_BALANCE_ATTRIBUTES	Holds mappings between attributes and defined balances.
PAY_BALANCE_BATCH_HEADERS	Batch header information for balance upload batch.
PAY_BALANCE_BATCH_LINES	Individual batch lines for the balance upload process.
PAY_BALANCE_CATEGORIES_F	Holds seeded categories for balances.
PAY_BALANCE_CLASSIFICATIONS	Information on which element classifications feed a balance.
PAY_BALANCE_CONTEXT_VALUES	Localization balance contexts.
PAY_BALANCE_DIMENSIONS	Information allowing the summation of a balance.
PAY_BALANCE_FEEDS_F	Controls which input values can feed a balance type.
PAY_BALANCE_SETS	Allows related balances to be grouped for reporting purposes.
PAY_BALANCE_SET_MEMBERS	Individual members of the balance set
PAY_BALANCE_TYPES	Balance information.
PAY_BALANCE_TYPES_EFC	This is a copy of the PAY_BALANCE_TYPES table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_BALANCE_TYPES_TL	Translated balance type definitions
PAY_BALANCE_VALIDATION	Balance Validity information
PAY_BAL_ATTRIBUTE_DEFAULTS	Balance attribution defaulted according to values in this table.
PAY_BAL_ATTRIBUTE_DEFINITIONS	Balance attributes help to identify which balances should be usedin which reports.
PAY_BANK_BRANCHES	Stores bank branch information to enable entry of bank account details with the correct branch information (e.g. GB bank, sort code, branch).
PAY_BATCH_CONTROL_TOTALS	Holds user defined control totals for the Batch Element Entry process.
PAY_BATCH_HEADERS	Header information for a Batch Element Entry batch.
PAY_BATCH_LINES	Batch lines for a Batch Element Entry batch.
PAY_CALENDARS	Details of user defined budgetary calendars.
PAY_CA_EMP_FED_TAX_INFO_F	Canadian federal tax information
PAY_CA_EMP_PROV_TAX_INFO_F	Canadian provincial tax information
PAY_CA_FILE_CREATION_NUMBERS	Used by Canadian direct deposit
PAY_CA_LEGISLATION_INFO	Canadian legislation specific data
PAY_CA_PMED_ACCOUNTS	Canadian Provincial Medical account information

PAY_CE_RECONCILED_PAYMENTS	Holds reconciliation information for payments processed through Oracle Cash Management.
PAY_COIN_ANAL_ELEMENTS	Monetary unit quantities for automatic make-up of cash payments.
PAY_COMPARISON_ROWS
PAY_CONSOLIDATION_SETS	Consolidation set of results of payroll processing.
PAY_COSTS	Cost details and values for run results.
PAY_COSTS_EFC	This is a copy of the PAY_COSTS table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_COST_ALLOCATIONS_F	Cost allocation details for an assignment.
PAY_COST_ALLOCATION_KEYFLEX	Cost Allocation key flexfield combinations table.
PAY_CUSTOMIZED_RESTRICTIONS	CustomForm restrictions for specific forms.
PAY_CUSTOM_RESTRICTIONS_TL	Translated data for the table PAY_CUSTOMIZED_RESTRICTIONS
PAY_DATED_TABLES	Holds details of datetracked columns
PAY_DATETRACKED_EVENTS	Stores details of events to track on HRMS Datetrack tables
PAY_DEFINED_BALANCES	Intersection between PAY_BALANCE_TYPES and PAY_BALANCE_DIMENSIONS.
PAY_DIMENSION_ROUTES	Stores balance dimension relationships.
PAY_ELEMENT_CLASSIFICATIONS	Element classifications for legislation and information needs.
PAY_ELEMENT_CLASSIFICATIONS_TL	Translated element classification definitions
PAY_ELEMENT_ENTRIES_F	Element entry list for each assignment.
PAY_ELEMENT_ENTRY_VALUES_F	Actual input values for specific element entries.
PAY_ELEMENT_ENTRY_VALUES_F_EFC	This is a copy of the PAY_ELEMENT_ENTRY_VALUES_F table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_ELEMENT_LINKS_F	Eligibility rules for an element type.
PAY_ELEMENT_SETS	Element sets.  Used to restrict payroll runs, customize windows, or as a distribution set for costs.
PAY_ELEMENT_SPAN_USAGES
PAY_ELEMENT_TEMPLATES	Element Templates
PAY_ELEMENT_TYPES_F	Element definitions.
PAY_ELEMENT_TYPES_F_EFC	This is a copy of the PAY_ELEMENT_TYPES_F table which is populated by the EFC (Euro as a Functional Currency) process.

PAY_ELEMENT_TYPES_F_TL	Translated element definitions
PAY_ELEMENT_TYPE_EXTRA_INFO	Stores extra information for an element
PAY_ELEMENT_TYPE_INFO_TYPES	Types of extra information that may be held against an element.
PAY_ELEMENT_TYPE_RULES	Include and exclude rules for specific elements in an element set.
PAY_ELEMENT_TYPE_USAGES_F	Used to store elements included or excluded from a defined run type.
PAY_ELE_CLASSIFICATION_RULES	Intersection table for PAY_ELEMENT_SETS and PAY_ELEMENT_CLASSIFICATIONS.
PAY_ELE_PAYROLL_FREQ_RULES	Frequency rules for a deduction/payroll combination.
PAY_ENTRY_PROCESS_DETAILS	Internal processing details for certain element entries
PAY_EVENT_GROUPS	Provides grouping for user control of event monitoring
PAY_EVENT_PROCEDURES	Code to execute if event detected.
PAY_EVENT_QUALIFIERS_F	Event Qualification definitions
PAY_EVENT_UPDATES	Process event update transactions
PAY_EVENT_VALUE_CHANGES_F	Values changes that cause an event
PAY_EXTERNAL_ACCOUNTS	Bank account details that enable payments to be made.
PAY_FILE_DETAILS	Report file details that have been saved in the system
PAY_FORMULA_RESULT_RULES_F	Rules for specific formula results.
PAY_FREQ_RULE_PERIODS	Stores frequency rule for a deduction/payroll combination.
PAY_FR_CONTRIBUTION_USAGES	PAY_FR_CONTRIBUTION_USAGES holds the definition of statutory payroll contributions in the French legislation.
PAY_FUNCTIONAL_AREAS	Holds definitions of functional areas
PAY_FUNCTIONAL_TRIGGERS	Defines the triggers contained in a functional area
PAY_FUNCTIONAL_USAGES	Enables functional areas for specific legislations, business groups and payrolls
PAY_GB_SOY_OUTPUTS	Temporary table for GB Start of Year process outputs.
PAY_GB_TAX_CODE_INTERFACE	Interface table for the UK Start of Year process.
PAY_GB_YEAR_END_ASSIGNMENTS	Extraction table for UK End of Year processing, which holds information about assignments.
PAY_GB_YEAR_END_PAYROLLS	Payroll information for the UK EOY process.
PAY_GB_YEAR_END_VALUES	Extraction table for the UK End of Year process that holds information about the NI balances at the year end.
PAY_GL_INTERFACE	Costed details to be passed to the General Ledger
PAY_GRADE_RULES_F	Stores the values for grade or progression point rates.
PAY_GRADE_RULES_F_EFC	This is a copy of the PAY_GRADE_RULES_F table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_GROSSUP_BAL_EXCLUSIONS	Stores balances which will be excluded for gross up by the net to gross process
PAY_IE_PAYE_DETAILS_F	PAY_IE_PAYE_DETAILS_F holds the PAYE Tax  Details for an assignment. It is a Date Tracked table.
PAY_IE_PRSI_DETAILS_F	PAY_IE_PRSI_DETAILS_F holds the PRSI Details for an assignment. It is a Date Tracked table.
PAY_IE_SOCIAL_BENEFITS_F	PAY_IE_SOCIAL_BENEFITS_F holds the social benefit details for an assignment.This is a date tracked table
PAY_IE_TAX_BODY_INTERFACE	PAY_IE_TAX_BODY_INTERFACE,Interface table used for uploading data into PAYE tables from a flat file.
PAY_IE_TAX_ERROR	PAY_IE_TAX_ERROR,Table used  to populate errors occured during uploading PAYE details.
PAY_IE_TAX_HEADER_INTERFACE	PAY_IE_TAX_HEADER_INTERFACE,Interface table used for uploading data into PAYE tables from a flat file.
PAY_IE_TAX_TRAILER_INTERFACE	PAY_IE_TAX_TRAILER_INTERFACE,Interface table used for uploading data into PAYE tables from a flat file.
PAY_INPUT_VALUES_F	Input value definitions for specific elements.
PAY_INPUT_VALUES_F_EFC	This is a copy of the PAY_INPUT_VALUES_F table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_INPUT_VALUES_F_TL	Translated input value definitions
PAY_ITERATIVE_RULES_F	Holds the processing rules of iterative elements.
PAY_JOB_WC_CODE_USAGES	Workers Compensation codes for specific job and state combinations.
PAY_JP_BANKS	This table is used for Japanese bank information.
PAY_JP_BANK_BRANCHES	This table is used for Japanese bank branch information.
PAY_JP_PRE_TAX	This table is a temporary table for Japanese legislative reports.
PAY_JP_SWOT_NUMBERS	Holds Japanese Tax Special Withholding Obligation Taxpayer Numbers.
PAY_LEGISLATION_CONTEXTS	Maps core contexts to legislative names
PAY_LEGISLATION_RULES	Legislation specific rules and structure identifiers.
PAY_LEGISLATIVE_FIELD_INFO	Controls legislative rules on individual form fields
PAY_LINK_INPUT_VALUES_F	Input value overrides for a specific element link.
PAY_LINK_INPUT_VALUES_F_EFC	This is a copy of the PAY_LINK_INPUT_VALUES_F table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_MAGNETIC_BLOCKS	Driving table for fixed format version of the magnetic tape process.
PAY_MAGNETIC_RECORDS	Controls the detailed formatting of the fixed format version of the magnetic tape process.
PAY_MESSAGE_LINES	Error messages from running a process.
PAY_MONETARY_UNITS	Valid denominations for currencies.
PAY_MONETARY_UNITS_TL	Translated data for the table PAY_MONETARY_UNITS_TL
PAY_MONITOR_BALANCE_RETRIEVALS	Monitors the source of balance retrievals
PAY_MX_EARN_EXEMPTION_RULES_F	Used to hold the Earnings exemption rules for Mexico
PAY_MX_LEGISLATION_INFO_F	Mexican legislation specific data
PAY_NET_CALCULATION_RULES	Element entry values which contribute to the net value of Paid Time Off.
PAY_NL_IZA_UPLD_STATUS	Holds the Status of the Data Records in the Processed IZA File
PAY_ORG_PAYMENT_METHODS_F	Payment methods used by a Business Group.
PAY_ORG_PAYMENT_METHODS_F_EFC	This is a copy of the PAY_ORG_PAYMENT_METHODS_F table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_ORG_PAYMENT_METHODS_F_TL	Translated payment method information
PAY_ORG_PAY_METHOD_USAGES_F	Payment methods available to assignments on a specific payroll.
PAY_PATCH_STATUS	Used to track the application of patches.
PAY_PAYMENT_TYPES	Types of payment that can be processed by the system.
PAY_PAYMENT_TYPES_TL	Translated payment type details
PAY_PAYROLL_ACTIONS	Holds information about a payroll process.
PAY_PAYROLL_ACTIONS_EFC	This is a copy of the PAY_PAYROLL_ACTIONS table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_PAYROLL_GL_FLEX_MAPS	Payroll to GL key flexfield segment mappings.
PAY_PAYROLL_LIST	List of payrolls that a secure user can access.
PAY_PEOPLE_GROUPS	People group flexfield information.
PAY_PERSONAL_PAYMENT_METHODS_F	Personal payment method details for an employee.
PAY_PERSONAL_PAYMENT_METHO_EFC	This is a copy of the PAY_PERSONAL_PAYMENT_METHODS_F table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_PERSON_LATEST_BALANCES	Latest balance values for a person.
PAY_POPULATION_RANGES	PERSON_ID ranges for parallel processing.
PAY_PRE_PAYMENTS	Pre-Payment details for an assignment, including the currency, the amount and the specific payment method.
PAY_PRE_PAYMENTS_EFC	This is a copy of the PAY_PRE_PAYMENTS table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_PROCESS_EVENTS	Process event capture table.
PAY_PROCESS_GROUPS	Defines groups of processes
PAY_PROCESS_GROUP_ACTIONS	Processes within the Process Group
PAY_PSS_TRANSACTION_STEPS	Table holding (denormalised) work-in-progress for Payroll Payments self-service.
PAY_PURGE_ACTION_TYPES	Details of the processing order required to purge action types.
PAY_PURGE_ROLLUP_BALANCES	Populated during Purge.  Stores details of the balance values being removed.
PAY_QUICKPAY_EXCLUSIONS	List of element entries that are to be excluded from a QuickPay run.
PAY_QUICKPAY_INCLUSIONS	List of element entries that can be included in a QuickPay run.
PAY_RATES	Definitions of pay rates, or pay scales that may be applied to grades.
PAY_RECORDED_REQUESTS	Dated process information.
PAY_REPORT_FORMAT_ITEMS_F	Individual items for the report mapping.
PAY_REPORT_FORMAT_MAPPINGS_F	Maps a report for a given jurisdiction to the fixed format defined for the magnetic tape.
PAY_REPORT_TOTALS
PAY_RESTRICTION_PARAMETERS	Restrictions to the rows retrieved by a customized form.
PAY_RESTRICTION_VALUES	The specific values to be used to customize a form.
PAY_RETRO_ASSIGNMENTS	Identifies assignment for reprocessing
PAY_RETRO_COMPONENTS
PAY_RETRO_COMPONENT_USAGES
PAY_RETRO_DEFINITIONS
PAY_RETRO_DEFN_COMPONENTS
PAY_RETRO_ENTRIES	Identifies the Entries required for re-processing.
PAY_RETRO_NOTIF_REPORTS	Populated and used in the RetroNotification Report
PAY_ROUTE_TO_DESCR_FLEXS	Store of routes to Descriptive Flexfields
PAY_RUN_BALANCES	Store of run level balances.
PAY_RUN_RESULTS	Result of processing a single element entry.
PAY_RUN_RESULT_VALUES	Result values from processing a single element entry.
PAY_RUN_RESULT_VALUES_EFC	This is a copy of the PAY_RUN_RESULT_VALUES table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_RUN_TYPES_F	The different types of Payroll Run processing
PAY_RUN_TYPES_F_TL	Translated run type descriptions
PAY_RUN_TYPE_ORG_METHODS_F	Organisation level payment methods associated with a particular run type.
PAY_RUN_TYPE_ORG_METHODS_F_EFC	This is a copy of the PAY_RUN_TYPE_ORG_METHODS_F table which is populated by the EFC (Euro as a Functional Currency) process.
PAY_RUN_TYPE_USAGES_F	Holds child run types where the run type parent is of type Cumulative.
PAY_SECURITY_PAYROLLS	List of payrolls and security profile access rules.
PAY_SHADOW_BALANCE_CLASSI	Element Template Shadow Balance Classifications
PAY_SHADOW_BALANCE_FEEDS	Element Template Shadow Balance Feeds
PAY_SHADOW_BALANCE_TYPES	Element Template Shadow Balance Types
PAY_SHADOW_BAL_ATTRIBUTES
PAY_SHADOW_DEFINED_BALANCES	Element Template Shadow Defined Balances
PAY_SHADOW_ELEMENT_TYPES	Element Template Shadow Element Type
PAY_SHADOW_ELE_TYPE_USAGES	Element Template Shadow Element Type Usages
PAY_SHADOW_FORMULAS	Element Template Shadow Formulas
PAY_SHADOW_FORMULA_RULES	Element Template Shadow Formula Result Rules
PAY_SHADOW_GU_BAL_EXCLUSIONS	Element Template Grossup Balance Exclusions
PAY_SHADOW_INPUT_VALUES	Element Template Shadow Input Values
PAY_SHADOW_ITERATIVE_RULES	Element Template Shadow Iterative Rules
PAY_SHADOW_SUB_CLASSI_RULES	Element Template Shadow Sub-Classification Rules
PAY_STATE_RULES	US state tax information.
PAY_STATUS_PROCESSING_RULES_F	Assignment status rules for processing specific elements.

Oracle Order to Cash tables: O2C tables

Oracle Apps Order to Cash important tables:

Order entry

OE_ORDER_HEADERS_ALL 1 record created in header table

OE_ORDER_LINES_ALL Lines for particular records

OE_PRICE_ADJUSTMENTS When discount gets applied

OE_ORDER_PRICE_ATTRIBS If line has price attributes then populated

OE_ORDER_HOLDS_ALL If any hold applied for order like credit check etc

Order Booked

OE_ORDER_HEADERS_ALL Booked_Flag=Y, Order booked.

WSH_DELIVERY_DETAILS Status Opened

WSH_DELIVERY_ASSIGNMENTS WSH_DELIVERY_ASSIGNMENTS.delivery_id will be NULL as still pick release operation is not performed as final delivery is not yet created.

MTL_DEMAND. ‘Demand interface program’ is triggered in the background and demand of the item with specified quantity is created 

Order Scheduled/Reserved This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background(if scheduled) and quantities are reserved. 

OE_ORDER_LINES_ALL Awaiting Shipping

MTL_RESERVATIONS This is only soft reservations. No physical movement of stock

WSH_DELIVERY_DETAILS R: Ready to Release: Line is ready to be released

Pick Released Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.

OE_ORDER_LINES_ALL

WSH_DELIVERY_DETAILS S: Released to Warehouse

WSH_NEW_DELIVERIES A new record is created in WSH_NEW_DELIVERIES with status_code = ‘OP’ (Open). WSH_NEW_DELIVERIES has the delivery records.

WSH_DELIVERY_ASSIGNMENTS Deliveries get assigned

WSH_PICKING_BATCHES After batch is created for pick release

MTL_TXN_REQUEST_HEADERS A move order is created in Pick Release process which is used to pick and move the goods to staging area (here move order is just created but not transacted). MTL_TXN_REQUEST_HEADERS, MTL_TXN_REQUEST_LINES  are move order tables

MTL_TXN_REQUEST_LINES move order line

Pick Confirm Pick Confirm is to transact the move order created in Pick Release process

OE_ORDER_LINES_ALL flow_status_code =’PICKED’

MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)

MTL_MATERIAL_TRANSACTIONS MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Pick Transaciton

MTL_TRANSACTION_ACCOUNTS updated with accounting information for mtl_material Transactions

WSH_DELIVERY_DETAILS Y: Staged- Line has been picked and staged by Inventory

MTL_ONHAND_QUANTITIES

Ship Confirmed The goods are picked from staging area and given to shipping. “Interface Trip Stop” program runs in the backend.

OE_ORDER_LINES_ALL .flow_status_code =‘SHIPPED’ Shipped_Quantity get populated

WSH_DELIVERY_DETAILS Released_Status=C ;Shipped ;Delivery Note get printed Delivery assigned to trip stop quantity will be decreased 

MTL_TRANSACTIONS_INTERFACE Data from MTL_TRANSACTIONS_INTERFACE is moved to MTL_MATERIAL_TRANACTIONS

MTL_MATERIAL_TRANSACTIONS updated with Sales Order Issue transaction

WSH_NEW_DELIVERIES If Defer Interface is checked then OM & inventory not updated. If Defer Interface is not checked: Shipped

OE_ORDER_LINES_ALL

WSH_DELIVERY_LEGS 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.

OE_ORDER_HEADERS_ALL If all the lines get shipped then only flag N

WSH_NEW_DELIVERIES Data Deleted

MTL_RESERVATIONS Data Deleted

MTL_DEMAND Data Deleted

MTL_ONHAND_QUANTITIES Item deducted from MTL_ONHAND_QUANTITIES

MTL_TRANSACTION_ACCOUNTS updated with accounting information.

WSH_TRIPS

WSH_TRIP_STOPS

Auto Invoice After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. 

OE_ORDER_LINES_ALL invoice_interface_status_code = ‘YES’

WSH_DELIVERY_DETAILS Released_Status=I 

RA_INTERFACE_LINES_ALL Data will be populated after work flow process.

RA_CUSTOMER_TRX_ALL After running Auto Invoice Master Program for

RA_CUSTOMER_TRX_LINES_ALL Specific batch transaction tables get populated

Close Order Last step of the process is to close the order which happens automatically once the goods are shipped

OE_ORDER_LINES_ALL flow_status_code =’CLOSED’ and open_flag = ‘N’

Order Management Status and Tables of O2C

Order Management Status

Order Management Tables.

Entered
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.

Booked
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release

Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock

Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines

wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned

Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock


Ship Confirmed

wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped

oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N


Autoinvoice

wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program for
ra_customer_trx_lines_all specific batch transaction tables get populated

Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines

Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms

AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.

Customer Information
hz_parties Get Customer information include name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers

Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments

Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes

End User Details
csi_t_party_details To capture End user Details

Sales Credit Sales Credit Information(How much credit can get)
oe_sales_credits

Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text

Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all

Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions

Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all

Hold Relaese
oe_hold_releases_all Hold released Sales Order.

Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.

Cancel Orders
oe_order_lines_all Cancel Order Details.

Debugging the Error Workflow

This post describes various methods of debugging oracle workflow. Oracle workflow is generally used to integrate the ERP business process into Oracle applications

Initial Level Checks:

1. Oracle workflow requires the Workflow Background process to be scheduled for every 10 minutes in the system with the following parameters:

• Y,N,N
• N,Y,N
• N,N,Y

1. First get the concurrent program ID:

   SELECT concurrent_program_id  FROM fnd_concurrent_programs_tl

  WHERE user_concurrent_program_name  = 'Workflow Background Process';

2. Check for the programs frequency of execution:

   SELECT request_date,actual_date,actual_completion_date,phase_code,status_code

   FROM fnd_concurrent_requests

   Where concurrent_program_id = <concurrent_program_id>

   AND argument_text = ', , , N, Y, N'

   AND resubmit_interval = 10

   AND resubmit_interval_unit_code = ‘MINUTES’

   Order By 1 Desc

3. Check whether the program is scheduled and running for every 10 mins

4. Similarly repeat step 2 for other arguments.

Check whether all the agent listeners are up and running as shown below:

     Navigation Path:

a.      Go to ‘Workflow Administrator Web Applications’ responsibility and click on ‘Workflow Manager’ as shown below.

After undergoing the initial level checks we need to categorize the workflow issue to any one of the below category:

1. Workflow not running
2. Notifications not being fired.

Workflow not running:

We assume that the workflow has been initiated and it’s not running further. We need to get the workflow name and itemkey for the workflow not running. Itemkey is a key to identify the workflow instances below are some of the examples for Itemkey

OM Header workflow   

SELECT header_id FROM oe_order_headers_all

WHERE order_number = <order_number>

AND   org_id = <Organization of the order>;

OM Line level workflow

SELECT line_id FROM oe_order_lines_all

WHERE header_id = <order header_id>

AND   org_id = <Organization of the order>;

PO Approval Workflow

SELECT wf_item_key FROM po_headers_all
WHERE segment1 = <PO Number>

AND   org_id = <Organization of the order>;


PO Approval Workflow
SELECT wf_item_key FROM po_headers_all
WHERE segment1 = <PO Number>

AND   org_id = <Organization of the order>;

Requisition Workflow  

SELECT wf_item_key FROM po_requisition_headers_all
WHERE segment1 = <REQ Number>

AND   org_id = <Organization of the order>;

After getting the workflow name and itemkey for the workflow which is not running follow the below steps:

·         Go to workflow status monitor.

 Enter the Workflow type and Itemkey of the workflow

·         Workflow may be in Active/Error/Complete/Defferred status

Ø       Complete - Workflow has successfully completed.

Ø       Error       - Workflow has error out. 

Ø       Active      - Workflow is still active.

Ø       Defferred - Workflow is waiting to be picked up by workflow

                       background engine.

        

      Active:

·         Select the workflow which we need to troubleshoot and click on the activity history.

·         Ensure that the recent activity is not in deferred status if so run the workflow background process.

·         Click on the activity to get the details

     If the function type is PL/SQL then debug the package mentioned in the Function column (e.g OE_STANDARD_WF.STANDARD_BLOCK).

Retry

·         Select the recent activity and click on the retry button if the activity shows as error to restart the workflow.

·         If the error still exists then click on the error to debug the package as done for the active status.

Deferred

·         If the recent status is deferred then check the workflow background engine status. Also check the deferred queue in the wf_deferred_table_m for the specific workflow

Select * from wf_deferred_table_m where corrid = ‘APPS’ + <item_type>;

Notifications not getting fired:

  All the workflow notifications are stored in the WF_NOTIFICATIONS table.

Select * from WF_Notifications where subject = <Subject of the notification>;

          

Column Descriptions:

• Mail_status:

ü       Sent: - Mails are sent to the recipients.

ü       Error: - Mails are not delivered to the recipient may be due to invalid email address.

• Status:

ü       Open: - Mails have been sent to the recipient and not yet viewed by the user.

ü       Closed: - Mail has been viewed by the recipient.

ü       Error: - Mail server is unable to deliver the message.

ü       Cancelled :- Workflow got cancelled

ü       Timeout :- Notification got timed out

• Context: It has the itemtype and itemkey separated by Colons.
• Recipient Role : It has the recipient roles
• 
  

Select * from WF_roles where name = <recipient_role>;

Select * from wf_user_roles where role_name = <recipient_role>

Common Reasons:

• Recipient has left the company so the role has expired.
• Invalid email address.
• Email address may be different from the HR tables in such cases run the concurrent program “WF Synchronize Local Tables”
• If emails got struck in the mail server contact the DBA team.
• If emails are not sent to the correct recipients then check the setups.
• Notification preference of the recipients may be Query/Summary which will not send email notification but can be viewed from the recipient notification worklist.

Other workflow Tips:

• Always clear the cache if you are not able to open the notifications listed in the notification worklist.
• If you are not able to view the workflow status diagram of the workflows owned by other users then set the workflow Administrator privilege to “*”

Workflow Configuration Page 

• “Purge Obsolete Workflow runtime data” concurrent program should be running every day to improve the performance of the workflow.
• Use the Set Override address in Test instances to check the email notifications.

Process of Debugging the Error Workflow :- 
=====================================

First Step get the item_key which got error out for the workflow

From Front end Applications you can get it from the
Workflow Administrator :-> Status Monitor =>  Give Internal name , Workflow Status, Workflow Started and
Click on Go button. It will list out the Workflow items based on the Criteria Selected.

From Back end :-

select * from wf_items;
WF_Items Table List out all the item_keys and item_types of the workflow.

Second Step :-

Once You get the Item_key, From Front End Applications, when we click on the Item key error , the error message would be something like this

Error message : 3205: 'xxxx' Is Not A Valid Role Or User Name

we should not consider this error as a main error , which is causing the problem for the workflow.

How to debug more on this to get the exact error message is as follows :-

select * from wf_items -> will get the item_keys

select * from WF_ITEM_ACTIVITY_STATUSES where item_key=<enter errored item key>;

select * from WF_ITEM_ATTRIBUTE_VALUES where item_key=<enter errored item key>;

WF_ITEM_ATTRIBUTE_VALUES table will give you the exact error message occurred value in the column 'TEXT_VALUE'

How to get details about patch applied in Oracle Applications

There are some tables in oracle apps (AD tables especially) involved when applying patches.
Some of them are very useful when we need specific information about patch already applied.

I will show the main tables and afterwards some handy related SQL’s to retrieve patch applied details and how we can also get all this information via OAM.

AD_APPLIED_PATCHES – The main table when we are talking about patches that applied in Oracle Apps.
This table holds information about the "distinct" Oracle Applications patches that have been applied.
If 2 patches happen to have the same name but are different in content (e.g. "merged" patches), then they are considered distinct and this table will therefore hold 2 records (eTRM).
I also found that if the applications tier node is separate from the concurrent manager node, and the patch applied on both nodes, this table will hold 2 records, one for each node.

AD_PATCH_DRIVERS – This table holds information about all patch drivers included in specific patch.
For example if patch contain only one unified driver like u[patch_name].drv then ad_patch_drivers will hold 1 record.
On the other hand, if patch contain more than 1 driver, for example d[patch_name].drv and c[patch_name].drv, this table will hold 2 records.

AD_PATCH_RUNS – holds information about each execution of adpatch for a specific patch driver.
In case a patch contains more than one driver, this table will hold a record for each driver.
This table also holds one record for each node the patch driver has been applied on (column APPL_TOP_ID).

AD_PATCH_RUN_BUGS – holds information about all the bugs fixed as a part of specific run of adpatch.

AD_BUGS – this table holds information about all bug fixes that have been applied.


We have 2 options to view applied patch information:1) via OAM – Oracle Applications Manager
2) Via SQL queries


With OAM it’s easy and very intuitive, from OAM site map -> “Maintenance” tab -> “Applied Patches” under Patching and Utilities.

Search by Patch ID will get all information about this patch; In addition, drill down by clicking on details will show the driver details.


For each driver we can use the buttons (Timing Details, Files Copied, etc.) to get more detailed information.

With SQL we can retrieve all the above information, sometimes more easily. 

For example: How to know which modules affected by specific patch? 

With OAM:
1) search patch by Patch ID
2) click on Details
3) For each driver click on “Bug Fixes” and look on product column.

With SQL:
Run the following query, it will show you all modules affected by specific patch in one click…

select distinct aprb.application_short_name as "Affected Modules"
from ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_patch_run_bugs aprb
where aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and apr.patch_run_id = aprb.patch_run_id
and aprb.applied_flag = 'Y'
and aap.patch_name = '&PatchName';
Another SQL will retrieve basic information regarding patch applied, useful when you need to know when and where (node) you applied specific patch:

select aap.patch_name, aat.name, apr.end_date
from ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_appl_tops aat
where aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and aat.appl_top_id = apr.appl_top_id
and aap.patch_name = '&PatchName';
To check if specific bug fix is applied, you need to query the AD_BUGS table only.
This table contains all patches and all superseded patches ever applied:

select ab.bug_number, ab.creation_date
from ad_bugs ab
where ab.bug_number = '&BugNumber';