Pseudo column's In Oracle

Definition:
Pseudocolumns are not actual columns in a table, but they behave like columns. They can be thought of as special-purpose data elements that can be used in SQL statements just as if they were part of the table, but are not actually contained in a table. Essentially, a pseudo-column is an Oracle assigned value used in the same context as an Oracle Database column, but not stored on disk. 
You can select values from a pseudocolumn but you cannot INSERT INTO, UPDATE, or DELETE from a pseudo-column. Pseudocolumns are allowed in SQL statements, but not in procedural statements. SQL and PL/SQL recognize the following SQL pseudocolumns, which return specific data items (this is a partial list):

• ROWID
• ROWNUM
• SYSDATE
• SYSTIMESTAMP
• UID
• USER
• LEVEL
• CURRVAL
• NEXTVAL

ROWID: For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:

• The data object number of the object
• The data block in the data file in which the row resides
• The position of the row in the data block (first row is 0)
• The data file in which the row resides (first file is 1). The file number is relative to the tablespace.

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.
NOTE:

 You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

Fault-policy frame work in SOA 11g

Fault-policy frame work in SOA 11g

In this post we will discuss the fault-policy framework in SOA 11g.
Conceptual wise no changes in this release when compare to 10g. But some changes are there in placing these files and etc.

First of all this framework will work only for the faults at invoke activity.
To implement fault-policy framework we have to have two xml files called fault-bindings.xmland fault-policies.xml.

A fault policy bindings file associates the policies defined in a fault policy file with the SOA composite application or the component.

A fault policy file defines fault conditions and their corresponding fault recovery actions. Each fault condition specifies a particular fault or group of faults, which it attempts to handle, and the corresponding action for it.

And these files should be created based on below XML schema.

http://download.oracle.com/docs/cd/E12839_01/integration.1111/e10224/med_faulthandling.htm#BABJGAEI

We can have these xml files in the same directory as the composite.xml file of the SOA composite application or we can place it in a different location and access those by using below properties in composite.xml

oracle.composite.faultPolicyFile
oracle.composite.faultBindingFile
Ex:
<property name="oracle.composite.faultPolicyFile">oramds:/apps/components/faultpolicies/fault-Policies.xml
</property>

<property

name="oracle.composite.faultBindingFile">oramds://apps/components/faultpolicyfiles/fault-bindings.xml

</property>
Below diagram shows how BPEL process behaves when we use fault-policy frame-work.

So when we are using this framework, it takes precedence over catch and catchAll blocks in BPEL. 

Below are the sample fault-policies and fault-bindings.xml

fault-policies.xml :

<?xml version="1.0" encoding="UTF-8"?>
<faultPolicies xmlns="http://schemas.oracle.com/bpel/faultpolicy"
xmlns:bpelx="http://schemas.oracle.com/bpel/extension"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
<faultPolicy version="2.0.1" id="PannPolicy1">
<Conditions>
<faultName xmlns:bpelx="http://schemas.oracle.com/bpel/extension" name="bpelx:remoteFault">
<condition>
<action ref="ora-terminate"/>
</condition>
</faultName>
<faultName xmlns:bpelx="http://schemas.oracle.com/bpel/extension" name="bpelx:bindingFault">
<condition>
<action ref="ora-retry"/>
</condition>
<condition>
<action ref="ora-human-intervention"/>
</condition>
</faultName>
</Conditions>
<Actions>
<Action id="ora-terminate">
<abort/>
</Action>
<Action id="ora-retry">
<retry>
<retryCount>2</retryCount>
<retryInterval>2</retryInterval>
<exponentialBackoff/>
</retry>
</Action>
<Action id="ora-human-intervention">
<humanIntervention/>
</Action>
</Actions>
</faultPolicy>
</faultPolicies>

 fault-bindings.xml:

<?xml version="1.0" encoding="UTF-8"?>

<faultPolicyBindings version="2.0.1" xmlns="http://schemas.oracle.com/bpel/faultpolicy"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

<composite faultPolicy=" PannPolicy 1"/>

<component faultPolicy=" PannPolicy 1 ">

<name>TestProcess</name>

</component>

<component faultPolicy=" PannPolicy 1 ">

<name>SecondProcess</name>

</component>

</faultPolicyBindings>

Infault-policies.xml condition section, we can have actions based on the evaluation of fault-variable available on the fault. Like below.

<condition>

<test>$fault.code="ABC Error"</test>

<action ref="ora-terminate"/>

</condition>

Some predefind actions were provided by oracle are:

retry

human-intervention

replay

rethrow

terminate

javaAction

We will see how to use custom java code in fault-policy framework in coming posts.

Some Impotent join conditions in Oracle apps

GL AND AP    

GL_CODE_COMBINATIONS    AP_INVOICES_ALL

code_combination_id = acct_pay_code_combination_id

GL_CODE_COMBINATIONS    AP_INVOICES_DISTRIBUTIONS_ALL

code_combination_id = dist_code_combination_id

GL_SETS_OF_BOOKS AP_INVOICES_ALL

set_of_books_id = set_of_books_id

GL AND AR

GL_CODE_COMBINATIONS    RA_CUST_TRX_LINE__GL_DIST_ALL

code_combination_id = code_combination_id

GL AND INV

GL_CODE_COMBINATIONS    MTL_SYSTEM_ITEMS_B

code_combination_id = cost_of_sales_account

GL AND PO

GL_CODE_COMBINATIONS    PO_DISTRIBUTIONS_ALL

code_combination_id = code_combination_id

PO AND AP

PO_DISTRIBUTIONS_ALL      AP_INVOICE_DISTRIBUTIONS_ALL

Po_distribution_id = po_distribution_id

PO_VENDORS           AP_INVOICES_ALL

vendor_id = vendor_id

PO AND SHIPMENTS

PO_HEADERS_ALL     RCV_TRANSACTIONS

Po_header_id = po_header_id

PO_DISTRIBUTIONS_ALL      RCV_TRANSACTIONS

Po_distribution_id = po_distribution_id

SHIPMENTS AND AP INVOICE

RCV_TRANSACTIONS           AP_INVOICE_DISTRIBUTIONS_ALL

RCV_TRANSACTION_ID = RCV_TRANSACTION_ID

PO AND  INV

PO_REQUISITION_LINES_ALL MTL_SYSTEM_ITEMS_B

item_id = inventory_item_id

org_id = organization_id

PO AND HRMS

PO_HEADERS_ALL     HR_EMPLOYEES

Agent_id = employee_id

PO AND REQUISITION

PO_DISTRIBUTIONS_ALL      PO_REQ_DISTRIBUTIONS_ALL

req_distribution_id = distribution_id

SHIPMENTS AND INV

RCV_TRANSACTIONS           MTL_SYSTEM_ITEMS_B

Organization_id         =        organization_id

INV AND HRMS

MTL_SYSTEM_ITEMS_B       HR_EMPLOYEES

buyer_id        =        employee_id

OM  AND  AR

OE_ORDER_HEADERS_ALL              RA_CUSTOMER_TRX_LINES_ALL

TO_CHAR( Order_number)    =        interface_line_attribute1

OE_ORDER_LINES_ALL                   RA_CUSTOMER_TRX_LINES_ALL

TO_CHAR(Line_id)     =        interface_line_attribute6  

OE_ORDER_LINES_ALL                   RA_CUSTOMER_TRX_LINES_ALL

reference_customer_trx_line_id       =        customer_trx_line_id

OM AND SHIPPING

OE_ORDER_HEADERS_ALL               WSH_DELIVARY_DETAILS

HEADER_ID     =        SOURCE_HEADER_ID

OE_ORDER_HEADERS_ALL              WSH_DELIVARY_DETAILS

LINE_ID         =        SOURCE_LINE_ID

AP AND AR (BANKS)

AR_CASH_RECEIPTS_ALL               AP_BANK_ACCOUNTS

REMITTANCE_BANK_ACCOUNT_ID    =        ABA.BANK_ACCOUNT_ID

AP AND AR

HZ_PARTIES                      AP_INVOICES_ALL

PARTY_ID      =        PARTY_ID

OM AND CRM

OE_ORDER_LINES_ALL         CSI_ITEM_INSTANCES(Install Base)

LINE_ID         =        LAST_OE_ORDER_LINE_ID

How to submit a concurrent program from backend

How to submit a concurrent program from backend:

   Using FND_REQUEST.SUBMIT_REQUEST Funtion and by passing the required parameters to it we can submit a concurrent program from backend. 

But before doing so, We have to set the environment of the user submitting the Request.

We have to Initialize the following parameters using FND_GLOBAL.APPS_INITIALIZE Procedure

1). USER_ID  2).RESPONSIBILITY_ID  3). RESPONSIBILITY_APPLICATION_ID

syntax:

FND_GLOBAL.APPS_INITIALIZE:

PROCEDURE APPS_INITIALIZE( user_id in number,

                                         resp_id in number,

                                         resp_appl_id in number);

FND_REQUEST.SUBMIT_REQUEST:

REQ_ID :=FND_REQUEST.SUBMIT_REQUEST(Application=> 'ApplicationName',

                                                                               Program => 'Program Name',

                                                                               Description=>'null',

                                                                              start_time=>'null',

                                                                             sub_request=>false,

                                                                             argument1=>1,

                                                                             argument2=>2,

                                                                             .

                                                                             .

                                                                             .

                                                                             . argument n=>n);

Where ,Req_id is the concurrent request id upon successful completion.And concurrent request ID returns 0 for any submission problems.

Example:-

1st get the user_id and responsibility_id by which we have to submit the program:

SELECT USER_ID,

               RESPONSIBILITY_ID,

               RESPONSIBILITY_APPLICATION_ID,

                SECURITY_GROUP_ID

FROM FND_USER_RESP_GROUPS

WHERE USER_ID =(SELECT USER_ID

                                  FROM FND_USER

                                WHERE USER_NAME='&USER_NAME')

AND RESPONSIBILITY_ID=(SELECT RESPONSIBILITY_ID

                                                   FROM FND_RESPONSIBILITY_VL

                                                   WHERE RESPONSIBILITY_NAME='&RESP_NAME');

Now create this procedure

create or replace procedure apps.call_racust (p_return_code out number,

                                                              p_org_id number,

                                                              p_return_msg  out varchar2)

IS

V_REQUEST_ID VARCHAR2(100);

P_CREATE_RECIPROCAL_FLAG VARCHAR2(1) :='N';

BEGIN

FND_GLOBAL.APPS_INITIALIZE(10081,5559,220);

V_REQUEST_ID:=APPS.FND_REQUEST.SUBMIT_REQUEST('AR',

                                                                            'RACUST',

                                                                            '' ",

                                                                            FALSE,

                                                                           P_CREATE_RECIPROCAL_FLAG,

                                                                            P_ORG_ID,

                                                                             CHAR(0));

P_RETURN_MSG:='Request submitted.ID=' || V_REQUEST_ID;

P_RETURN_CODE:=0; COMMIT;

EXCEPTION

WHEN OTHERS THAN

P_RETURN_MSG:='REQUEST SET SUBMISSION FAILED -UNKNOWN ERROR:'||SQLERRM;

P_RETURN_CODE:=2;

END; 

How to Remove Contents from MDS Repository

How to Remove Contents from MDS Repository

In 11g we can publish artifacts and commonly used schema, wsdl's in a meta data store to acccess across processes.

Often we realize that sometime we need to clean up and remove unnecessary and unwanted files from the repostiory.

There are two ways you can remove contents from MDS offline mode and online mode. I will explain both the options below

Option 1: Offline Mode

Step 1: Execute Wlst.Sh

Goto YOUR_MIDDLEWARE_HOME/Oracle_SOA1/common/bin and execute wlst.sh

Step 2: Run Command

At wlst command prompt (wls:/offline>) execute the following

sca_removeSharedData('http://yourhost:yourport', 'directory', 'adminuser', 'adminpassword')

To remove a folder named "common" folder and all it's sub-directories and files.

sca_removeSharedData('http://localhost:8001', 'common', 'weblogic', 'weblogic1')

Option 2: Online Mode

Step 1: Execute Wlst.Sh

Goto YOUR_MIDDLEWARE_HOME/Oracle_SOA1/common/bin and execute wlst.sh

Step 2: Connect To SOA Server

At wlst command prompt (wls:/offline>) execute the following

connect('adminuser', 'adminpassword', 't3://hostname:port')

ex:
connect('weblogic', 'weblogic1', 't3://localhost:8001')

Step 2: Remove Content From MDS

deleteMetadata(application='soa-infra',server='soa_server1',docs='/apps/common')

MD 50 and MD 70 Documents - Oracle application development

Documentation plays a very  important role in oracle apps implementation projects.Oracle as part of the AIM   Methodology has provided certain jargon's for various kinds of documents at different stages of the project.So here is a blog post detailing the same:

BR Documents: Business Requirement Documents,Which is mostly done by the Functional persons of the implementation Team Like Functional Project Leads/Managers.These documents are the set up Documents,which is 100% based on the BR120-Business Requirement Gatherings as provided by the business.
You can say these are as is process.So BR 100 is the To Be Process after you gather all sorts of info from the Biz and map in the Oracle Systems.

MD Documents:-Modular Designing Documents,which are is mostly done by the Technical persons of the Implementation Team like Technical Project Leads/Project Manager. These document are the Design Documents,which is again based on the BR120-Business Requirement Gathering as provided by the business.
     These MD's are of basically discussed on any customization needs or any special behavior oracle System should work which is not the standard Oracle Functionality.These also discuss about the tables and the interface tables or forms which are going  to be used in the particular modules.It also discusses about the High Level Designs Like Flows of the Business and all.
These MD's are basically made after you all Functional Design and if there is no work around Oracle System provides for a particular Test Scenario and there is no other way other than to go for the Customization. 

Now you can understand the basic difference b/w these two.
Here is the Overall flow with various documents involved in every Stage:
1st Stage:-Analysis
Document used: 
RD 120-Requirement Gathering -As Is To Be Process
BR150 - Fit GAP Analysis (It's been done on the basis of above doc)
2nd Stage: Designing
Document Used:
BR 100: Set up Document for the Functional Consultants
MD 200: Set up Document for the Technical Consultants
3rd Stage: Build -DEMO/ PROTO TYPE
Document Used:
BR 050
4th Stage:Testing
Document Used:
TE 050
5th Stage : GO Live
6th Stage: Post Production.

BPEL Persistence Properties

Ø  BPEL Persistence Properties – 11g

BPEL Persistence properties are used to control, when a process need to dehydrate. Below are the properties which we can use to control it for BPEL Component in a Composite.

InMemoryOptimization

This property indicates to Oracle BPEL Server that this process is a transient process and dehydration of the instance is not required. When set to true, Oracle BPEL Server keeps the instances of this process in memory only during the course of execution. This property can only be set to true for transient processes (process type does not incur any intermediate dehydration points during execution).

• false (default): instances are persisted completely and recorded in the dehydration store database for a synchronous BPEL process.
• true: Oracle BPEL Process Manager keeps instances in memory only.

CompletionPersistPolicy

This property controls if and when to persist instances. If an instance is not saved, it does not appear in Oracle BPEL Console. This property is applicable to transient BPEL processes (process type does not incur any intermediate dehydration points during execution).

This property is only used when inMemoryOptimization is set to true.

This parameter strongly impacts the amount of data stored in the database (in particular, the cube_instance, cube_scope, and work_item tables). It can also impact throughput.

• on (default): The completed instance is saved normally.
• deferred: The completed instance is saved, but with a different thread and in another transaction, If a server fails, some instances may not be saved.
• faulted: Only the faulted instances are saved.
• off: No instances of this process are saved.

<component name="mybpelproc">
...
<property name="bpel.config.completionPersistPolicy">faulted</property>
<property name="bpel.config.inMemoryOptimization">true</property>
...
</component>

OneWayDeliveryPolicy

This property controls database persistence of messages entering Oracle BPEL Server. Its used when we need to have a sync-type call based on a one way operation. This is mainly used when we need to make an adapter synchronous to the BPEL Process.

By default, incoming requests are saved in the following delivery service database tables: dlv_message

• async.persist: Messages are persisted in the database.
• sync.cache: Messages are stored in memory.
• sync: Direct invocation occurs on the same thread.

<component name="UnitOfOrderConsumerBPELProcess">
...
<property name="bpel.config.transaction" >required</property>
<property name="bpel.config.oneWayDeliveryPolicy">sync</property>
...
</component>

General Recommendations:

1. If your Synchronous process exceed, say 1000 instances per hour, then its better to set inMemoryOptimization to true and completionPersistPolicy to faulted, So that we can get better throughput, only faulted instances gets dehydrated in the database, its goes easy on the purge (purging historical instance data from database)

2. Do not include any settings to persist your process such as (Dehydrate, mid process receive, wait or Onmessage)

3. Have good logging on your BPEL Process, so that you can see log messages in the diagnostic log files for troubleshooting.