How to find error message comming in oracle API's

After the calling of API give the following
IF (fnd_msg_pub.count_msg > 0)THEN
FOR i IN 1..fnd_msg_pub.count_msg
LOOP

fnd_msg_pub.get
( p_msg_index => i,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => X_msg_count
);

DBMS_OUTPUT.PUT_LINE('API ERROR: ' || x_msg_data);
END LOOP;
dbms_output.put_line(x_jtf_note_id ||'--'|| x_return_status ||'--'|| X_msg_count ||'--'|| x_msg_data);
ELSE
DBMS_OUTPUT.PUT_LINE('Updated sr : ' || to_char(ChildIncId));
End if;

http://programmerslounge.blogspot.com/2009/04/how-to-find-error-message-comming-in.html

INSTALL BASE ALL MAIN TABLES

Instance

Records the Item Instance Details of an IB instance

Table: CSI_ITEM_INSTANCES

Transaction

Install Base Transaction Log

Table: CSI_TRANSACTIONS

Csi_transactions

Transaction_id

Transaction_type_id

Csi_txn_types

Transaction_type_id

Csi_item_instances_h

Instance_history_id

Instance_id

Transaction_id

Csi_item_instances

Instance_id

Inventory_item_id

Serial_number

Instance_status_id

Location_id

Instance Relationship

Defines the instance to instance relationships

Table: CSI_II_RELATIONSHIPS

Csi_item_instances

Instance_id

Csi_ii_relationships

Relationship_id

Subject_id

Relationship_type_code

Object_id

Csi_ii_relation_types

Relationship_type_code

Csi_ii_relationships_h

Relationship_history_id

Relationship_id

Transaction_Id

Csi_transactions

Transaction_id

Instance Party relationship

Defines the relationship between Instance and party

Table: CSI_II_PARTIES

Instance Party accounts

Defines the Instance to account (party account) association

Table: CSI_IP_ACCOUNTS

Csi_item_instances

Instance_id

Csi_ip_accounts_h

Ip_account_history_id

Ip_account_id

Transaction_id

Csi_ip_accounts

Ip_account_id

Instance_party_id

Party_account_id

Csi_i_parties

Instance_party_id

Relationship_type_code

Instance_id

Party_id

Csi_transactions

Transaction_id

Csi_i_parties_h

Instance_party_history_id

Instance_party_id

Transaction_id

API's

CSI_ITEM_INSTANCE_PUB

Create Item Instance

Update Item Instance

Expire Item Instance

Get Item Instance

Get Item Instance Details

Copy Item Instance

CSI_II_RELATIONSHIPS_PUB

Create Relationship

Update Relationship

Expire Relationship

CSI_SYSTEMS_PUB

Create System

Update System

Expire System

Get System

CRM CONTRACTS TABLES INFO

OKC_ASSENTS

Indicates if an Operation is to be performed for a Subclass of Contract while in a Status

Assent works with Operations (OKC_OPERATIONS_B), Status (OKC_STATUSES_B), and Subclass (OKC_SUBCLASSES_B) to allow flexibility as to what operations can be performed on a different types of Contracts in different statuses.

Subclasses define different types of Contracts (see OKC_SUBCLASSES_B for more information).

Statuses define different states a Contract may be in (see OKC_STATUSES_B for more information).

Operations define different operations a user, or the application may perform on or because of the Contract (see OKC_OPERATIONS_B for more information).

Assent is at the intersection of all three, defining if a specific Operation is allowed or disallowed for a Subclass of Contract while it has a specific Status.

For example, assume that for an implementation we have defined the Status 'Bill Hold'. If the ALLOWED_YN column is set to 'N' for the Operation 'Bill' for the Subclass 'Service' for the Status 'Bill Hold', then billing will not to be performed for 'Service' contracts in the 'Bill Hold' status.

ID Unique identifier for assent defined..

STS_CODE Status for which this assent is defined

OPN_CODE Operation for this assent is defined

STE_CODE Status type for which this assent is defined

SCS_CODE Subclass for which this assent is defined.

OKC_OPERATIONS_B

Set of processes performed by the application to, or as a result of, a contract.

OKC_OPERATIONS defines a set of processes performed by the application to, or as a result of, a contract.

Some operations may be performed on the contract, such as update on line or update via change request. Others are performed as the result of a contract line, such as entitle or bill.

Along with OKC_ASSENTS, this provides information to the application as to what it can do and what it should allow the user to do.

CODE CODE for operations defined at contract header/contract line level.

OPN_TYPE Type of operation (contract or line).

OBJECT_VERSION_NUMBER Sequential number set at 1 on insert and incremented on update. Used by APIs to ensure current record is passed.

OKC_STATUSES_B

User defined values that define a contract's status.

STATUS is a user defined value that defines a contract's status.

Each user-defined status must be in one of six STATUS TYPES, which are seeded values in FND_LOOKUPS. The six status types are:

Entered

Cancelled

Active

Hold

Expired

Terminated

Within each status type, users may define as many statuses as they need. Along with OPERATIONS, SUBCLASS, and ASSENT, status helps drive what the system can or cannot do and what the system allows users to do. For example, it may be allowed to delete a contract in a Cancelled status but not in an Active status.

CODE Status code as defined in FND_LOOKUP_VALUES.

STE_CODE Status Type to which this Status Code belongs to. For example, Status Type "ENTERED'' can have ''ENTERED'', ''SUBMITTED FOR APPROVAL'' as status codes.

DEFAULT_YN Indicates if a status code is the default status code for the given status type.

START_DATE The beginning of the active period, one second after midnight on the date indicated.

END_DATE The end of the active period, one second before midnight on the date indicated.

OKC_PROCESS_DEFS_B

This table stores information of PL/SQL processes or workflows within the application which are used as OUTCOME, CONTRACT PROCESS, QA PROCESS, or FUNCTION in a CONDITION LINE.

This table stores information of PL/SQL processes or workflows registered with the application to be used as OUTCOME, CONTRACT PROCESS, QA PROCESS, or FUNCTION in a CONDITION LINE. Along with OKC_PROCESS_DEF_PARMS, this table provides the information necessary for the application to invoke the process or workflow. The usage of these processes is recorded in OKC_OUTCOMES, OKC_K_PROCESSES, OKC_CONDITION_LINES, and OKC_QA_LIST_PROCESSES.

ID System generated Unique Identifier. Generated from sequence 'OKC_PROCESS_DEFS_S1'. Also the Primary key for the table.

PDF_TYPE Process definition type. Valid values are ALERT, SCRIPT, PPS, WPS.

OBJECT_VERSION_NUMBER Sequential number set at 1 on insert and incremented on update. Used by APIs to ensure current record is passed.

CREATED_BY Standard Who column.

USAGE Usage of Process Definition. Valid values are Approve, Auto Numbering, Approve Change Request, Function, Outcome and Quality Assurance. Refers to LOOKUP_CODE in FND_LOOKUPS where LOOKUP_TYPE= 'OKC_PROCESS_USAGE_TYPES'.

NAME

OKC_QA_CHECK_LISTS_B

Associates a list of quality assurance processes with a specific contract or contract template

Provides a "header" that associates a list of quality assurance processes with a specific contract or contract template.

Because it can take a long time to collect and enter the information about a contract, it is not possible to enforce all data integrity rules or business rules during data entry. The purpose of the QA check list is to assemble a set of routines (defined in OKC_PROCESS_DEFS) that will run against the contract to validate that all such rules have been met. Rules about required data integrity are "hardcoded", and are always run. Others are intended to be supplied by the client as independent routines assembled into the check list.

OKS_BILLING_PROFILES_B

Contains profile information for a customer.

This table holds Billing profile information for a customer. Billing profiles include Information about customer account, customer billing address, invoicing rule, accounting rule, billing level, billing type, billing interval, interface offset and invoiving offset. This table is populated through Billing Profile setup UI. Users can use the billing profile to overwrite any existing line level billing schedule information on the contract authoring form by selecting it in the Cascade Attributes form. Upon renewal, users can also default billing profile information onto the contract by associate a billing profile template to a customer in the Global Contract Defaults form.

OKS_SERV_AVAILS

Stores availability information for a service.

This table stores information about general service availability for service item and populated through the header part of Service Availability setup UI. This table is closely related to the table OKS_SERV_AVAIL_EXCEPTS where the exception of service availability is stored. These two tables are combined together to get the information of available services for service programs, warranties, and extended warranties. Service Availability inclusion and exclusion rules are honored while selling services in upstream applications like quoting, Order Management. When user enters a service item, by default generally available check box if checked for party and product and on saving the default entry, two records are saved in this table meaning service is generally available for party and product. User can specify affectivity of this service availability also by entering values in START_DATE_ACTIVE and END_DATE_ACTIVE columns. If generally available check box is unchecked for party or customer, general_yn flag will be set to 'N' meaning service is not available generally for party or product.

ID Internal Unique Identifier.

OBJECT1_ID1 Item id used for service.

OBJECT1_ID2 Not used.

JTOT_OBJECT1_CODE Foreign key to JTF_OBJECTS_B. JTF Object code identifying the OKX view for service item. Identifies the Source Object (Table/View/Object) that contains the item.

OKS_COV_TYPES_B

Contains the coverage type code and importance of each coverage type.

OKS_COV_TYPES_B is populated by Coverage Types setup form. This table contains the coverage type and importance level definition. E.g., G (Gold), S (Silver) and B (Bronze). The Coverage types defined here are used in the Coverage form.

CODE Unique Identifier code for records in OKS_COV_TYPES_B

IMPORTANCE_LEVEL Importance level associated to the Coverage type. Used by Service Application to prioritize their task or service.

ENABLED_FLAG Flag to indicate that the coverage type record is enabled to be used

START_DATE_ACTIVE Specifies the date when the coverage type is allowed to be used

END_DATE_ACTIVE Specifies the date when the coverage type is no longer effective

CRM PROFILE VALUES

OKC: View Contracts By Organization

OKS: Contact Center Date Range

OKS: Intangible Subscription Method

OKS: Transfer Party Relationship

OKS: Update Contract with Install Base Quantity

OKS: Warranty Consolidation

OKS: Full Credit for Product Return

OKS: Transfer Status

OKS: Credit Card Display Privileges

OKS: Intangible Subscription Pricing Method

OKS: Minimum Service Duration

OKS: Reprice Warning Message

OKS: Enable Negative Invoicing

OKS: Usage Billing Calculation

OKS: Payment Method for AR Interface

OKS: Default Order Type for Subscriptions

OKS: Category for Order Management Originated Contracts

OKS: Raise Credit Memo for Install Base Transactions

OKS: Notify User of Install Base Integration Notifications

OKS: Vendor Contact Role

OKS: Credit Card Validation Level

OKS: Credit Card Minimum Authorized Amount

OKS: Use Advanced Pricing for Manual Adjustment

OKS: Notify Contract Administrator

OKS: Notify Sales Administrator

OKS: Revenue Type for Sales Credits

OKS: Revenue Type Distribution for Sales Credit

OKS: Default Sales Person for Renewal

OKS: Use Territories to Default Sales Person

OKS: Wallet Path

IB TABLES

CSI_ITEM_INSTANCES

CSI_ITEM_INSTANCES_H

CSI_TRANSATIONS

CSI_TXN_TYPES

CSI_II_REALATIONSSHIPS

CSI_II_RALATION_TYPES

CSI_II_REALATIONSSHIPS_H

CSI_I_PARTIES

CSI_IP_ACCOUNTS

CSI_IP_ACCOUNTS_H

CSI_I_PARTIES_H

CSI_SYSTEMS_B

CSI_INSTANCE_STATUSES

Core Contracts

OKC_K_HEADERS_B

OKC_K_LINES_B

OKC_K_ITEMS

OKC_K_SALES_CREDITS

OKC_ROLES_B

OKC_PARTY_ROLES

OKC_STATUSES_B

OKC_ITEM_PARTYS_B

OKC_CONTACTS

OKC_RULES_B_AS

OKC_K_PARTY_ROLES_B

OKC_PRICE_ADJUSTMENTS

Service Contracts

OKS_K_HEADERS_B

OKS_K_LINES_B

OKS_K_ORDER_DETAILS

OKS_K_SALES_CREDITS

Service Billing

OKS_BILLRATE_SCHEDULES

OKS_BILL_CONT_LINES

OKS_BILL_SUB_LINES

OKS_BILL_SUB_LINES_DTLS

OKS_BILL_TRANSACTIONS

OKS_BILL_TXN_LINES

TCA

HZ_PARTIES

HZ_LOCATIONS

HZ_PARTY_SITES

HZ_PARTY_SITE_USES

HZ_PARTY_RELATIONSHIPS

HZ_CUST_ACCOUNTS

HZ_CUST_ACCOUNT_ROLES

HZ_ROLE_RESPONSIBILITY

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_SITE_USES_ALL

HZ_CONTACT_POINTS

HZ_CUST_CONTACT_PONTS

HZ_ORG_CONTACTS

HZ_ORG_CONTAC_ROLES

Toad – Update the Select Result in Toad with ROWID

If you want to update your select result in toad, just add the ROWID is your select statement. Once you select the records with ROWID then you can edit/update your result grid.

Update the Select Result in Toad for Oracle

You can also see the Green indicator as shown in the below screenshot that means your records are editable.

Update the Select Result in Toad for Oracle

Purge Obsolete Workflow Runtime Data (FNDWFPR)

Use the standard concurrent program FNDWFPR "Purge Obsolete Workflow Runtime Data" to purge old data from the Oracle Workflow tables regularly.

This program purges obsolete runtime information associated with work items, including status information, any associated notifications, and, if the ECX: Purge ECX data with WF profile option is set to Y, any associated Oracle XML Gateway transactions. By default, it also purges obsolete design information, such as activities that are no longer in use and expired ad hoc users and roles, and obsolete runtime information not associated with work items, such as notifications that were not handled through a workflow process and, if the ECX: Purge ECX data with WF profile option is set to Y, Oracle XML Gateway transactions that were not handled through a workflow process. You can optionally choose to purge only core runtime information associated with work items for performance gain during periods of high activity, and purge all obsolete information as part of your routine maintenance during periods of low activity.

Note: This program does not delete ad hoc users or roles whose expiration date is null. To ensure that ad hoc users and roles are purged in a timely fashion after they are no longer needed, estimate how long they should be active and specify an appropriate expiration date when you call WF_DIRECTORY.CreateAdHocUser(), WF_DIRECTORY.CreateAdHocRole(), or WF_DIRECTORY.CreateAdHocRole2() to create them.

To preserve electronic signature evidence for future reference, this program by default does not delete any notifications that required signatures or their associated signature information. If you do not need to maintain signature evidence, you can choose to delete signature-related information as well.

Navigate to the Submit Requests form in Oracle E-Business Suite to submit the Purge Obsolete Workflow Runtime Data concurrent program. When you install and set up Oracle E-Business Suite and Oracle Workflow, your system administrator needs to add this concurrent program to a request security group for the responsibility that you want to run this program from. See: Overview of Concurrent Programs and Requests, Oracle E-Business Suite System Administrator's Guide - Configuration and Running Reports and Programs, Oracle E-Business Suite User's Guide.

You can supply the following parameters for the Purge Obsolete Workflow Runtime Data concurrent program:

• Item Type - The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.
• Item Key - The item key to purge. Leaving this field blank defaults to purging the runtime data for all item keys.
• Age - Minimum age of data to purge, in days. The default is0.
• Persistence Type - The persistence type to be purged, either Temporary or Permanent. The default is Temporary
• Core Workflow Only - Enter 'Y' to purge only obsolete runtime data associated with work items, or 'N' to purge all obsolete runtime data as well obsolete design data. The default is 'N'.
• Commit Frequency - The number of records to purge before the program commits data. To reduce rollback size and improve performance, set this parameter to commit data after a smaller number of records. The default is 500 records.
  
  Note: After performing a commit, the program resumes purging work items with the next subsequent begin date. In some cases, if additional items have the same begin date as the last item that was purged before a commit, the program may not purge all eligible items. To purge these remaining work items, simply rerun the program.
  
• Signed Notifications - Enter 'N' to preserve signature evidence, including notifications that required electronic signatures and their associated signature information. Enter 'Y' to purge signature-related information. The default is 'N'.
  
  

Useful Information about Workflow Background Process Concurrent Program

What is the purpose of Workflow Background Process

Workflow Background Process is a concurrent program which is run for processing deferred activities, timed out activities, and stuck processes using the parameters specified. The background engine executes all activities that satisfy the given arguments at the time that the background engine is invoked. This procedure does not remain running long term, so you must restart this procedure periodically. Any activities that are newly deferred or timed out or processes that become stuck after the current background engine starts are processed by the next background engine that is invoked.

Workflow Background Process is run with the help of Workflow Background Engine which is PL/SQL Procedure which runs this concurrent program with specified parameters.

Workflow Background Process Parameters:

• Item Type – Specify an item type to restrict this engine to activities associated with that item type. If you do not specify an item type, the engine processes any activity regardless of its item type.
• Minimum Threshold – Specify the minimum cost that an activity must have for this background engine to execute it, in hundredths of a second.
• Maximum Threshold – Specify the maximum cost that an activity can have for this background engine to execute it, in hundredths of a second. By using Minimum Threshold and Maximum Threshold you can create multiple background engines to handle very specific types of activities. The default values for these arguments are null so that the background engine runs activities regardless of cost.
• Process Deferred – Specify whether this background engine checks for deferred activities. Setting this parameter to Yes allows the engine to check for deferred activities.
• Process Timeout – Specify whether this background engine checks for activities that have timed out. Setting this parameter to Yes allows the engine to check for timed out activities.
• Process Stuck – Specify whether this background engine checks for stuck processes. Setting this parameter to Yes allows the engine to check for stuck processes.

 Note: Make sure you have a least one background engine that can check for timed out activities, one that can process deferred activities, and one that can handle stuck processes. At a minimum, you need to set up one background engine that can handle both timed out and deferred activities as well as stuck processes.

How to run Workflow Background Process from Oracle Applications?

1. Responsibility: System Administrator
2. Navigate: Requests > Run
3. Select Workflow Background Process from the list of values.
4. Enter parameters:
Item Type = OM Order Line
Process Deferred = yes
Process Timeout = yes
5. Submit concurrent program.

How to run Workflow Background Process from SQL*Plus?

BEGIN wf_engine.background (itemtype=>NULL , process_deferred=>TRUE , minthreshold=>NULL , maxthreshold=>NULL , process_timeout=>FALSE , process_stuck=>FALSE); END;

How to run Workflow Background Process from Unix?

$ $FND_TOP/Admin/Sql/wfbkg.sql

Performance Tuning of Workflow Background Process

Workflow Background Process picks and executes all eligible workflow items related to Oracle Modules like OM,AP,PO,WSH,OKL,ASN,WIP, etc

Hence if ItemType parameter is not specified the workflow background process runs will first check for all eligible workflow activities and then run for all eligible workflows this will result in poor performance. It is preferable to execute the Workflow Background Process with specified ItemType.