Wednesday, November 19, 2008

SMSing using PL/SQL

1. Compile the below procedure on sql*plus

CREATE OR REPLACE PROCEDURE send_sms (
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2
)
AS
mailcon UTL_SMTP.connection;
BEGIN
mailcon := UTL_SMTP.open_connection ('localhost');
UTL_SMTP.helo (mailcon, 'localhost');
UTL_SMTP.mail (mailcon, p_sender);
UTL_SMTP.rcpt (mailcon, p_recipient);
UTL_SMTP.DATA (mailcon,
'From: '
|| p_sender
|| UTL_TCP.crlf
|| 'To: '
|| p_recipient
|| UTL_TCP.crlf
|| 'Subject: SMS From Database'
|| UTL_TCP.crlf
|| p_message
);
UTL_SMTP.quit (mailcon);
END;

2. We need to execute the above procedure so as to send SMS. The systax is as follows:

exec SEND_SMS('arun@yahoo.com', '984812345@ideacellular.net','This is my first SMS');

Note: In place of the '984812345@ideacellular.net' you need to provide your mobile number. Also please refer to the below list of mobile operator and the area of the service.

Andhra Pradesh AirTel-----------Mobile No@airtelap.com
Andhra Pradesh Idea Cellular-----Mobile No@ideacellular.net
Chennai Skycell/Airtel------------Mobile No@airtelchennai.com
Chennai RPG Cellular-------------Mobile No@rpgmail.net
Delhi Airtel-----------------------Mobile No@airtelmail.com
Delhi Hutch-----------------------Mobile No@delhi.hutch.co.in
Gujarat Airtel---------------------Mobile No@airtelmail.com
Gujarat Idea Cellular--------------Mobile No@ideacellular.net
Gujarat Celforce/Fascel-----------Mobile No@celforce.com
Goa Airtel-------------------------Mobile No@airtelmail.com
Goa BPL Mobile-------------------Mobile No@bplmobile.com
Goa Idea Cellular------------------Mobile No@ideacellular.net
Haryana Airtel--------------------Mobile No@airtelmail.com
Haryana Escotel-------------------Mobile No@escotelmobile.com
Himachal Pradesh Airtel-----------Mobile No@airtelmail.com
Karnataka Airtel-------------------Mobile No@airtelkk.com
Kerala Airtel-----------------------Mobile No@airtelkerala.com
Kerala Escotel---------------------Mobile No@escotelmobile.com
Kerala BPL Mobile-----------------Mobile No@bplmobile.com
Kolkata Airtel----------------------Mobile No @airtelkol.com
Madhya Pradesh Airtel-------------Mobile No@airtelmail.com
Maharashtra Airtel-----------------Mobile No@airtelmail.com
Maharashtra BPL Mobile-----------Mobile No@bplmobile.com
Maharashtra Idea Cellular----------Mobile No@ideacellular.net
Mumbai Airtel----------------------Mobile No@airtelmail.com
Mumbai BPL Mobile----------------Mobile No@bplmobile.com
Punjab Airtel-----------------------Mobile No@airtelmail.com
Pondicherry BPL Mobile------------Mobile No @bplmobile.com
Tamil Nadu Airtel-------------------Mobile No@airtelmail.com
Tamil Nadu BPL Mobile-------------Mobile No@bplmobile.com
Tamil Nadu Aircel-------------------Mobile No@airsms.com
UP (West) Escotel-------------------Mobile No@escotelmobile.com

How to add comments on a table or a column.

If we want to give a comment on a table :

COMMENT ON TABLE
IS 'the comments should go here';

If we want to give a comment on a column :

COMMENT ON TABLE | COLUMN table.column
IS 'the comments should go here';

The entries of these comments on the table goes in the following tables:

For tables:
select * from all_tab_comments
where table_name ='table_name'

select * from user_tab_comments
where table_name = 'table_name'

For Columns:
select * from all_col_comments
where table_name = 'table_name'

select * from user_col_comments
where table_name = 'table_name'

Tuesday, November 18, 2008

How to Compile and port a Form (fmb) in Oracle

1. Port the attached form to unix directory to any top using FTP (Preferable we port the form to the AU_TOP)

NOTE: Copy .fmb files (form files) in Binary mode.

2. Connect to the telnet and change the directory to the top where we have ported the fmd.

Run the following commands in the above directory in Telnet:

a) FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US
b) f60gen module=formname.fmb userid=apps/apps output_file=formname.fmx module_type=form batch=no compile_all=special
example:
f60gen module=XXTRRBT.fmb userid=apps/apps output_file=XXTRRBT.fmx module_type=form batch=no compile_all=special

Monday, November 17, 2008

Excellent information on all Oracle Application module topics

Please visit the following website to find excellent information on all Oracle Application Modules:

http://download-uk.oracle.com/docs/cd/A60725_05/html/comnls/us/index.htm

Script to exclude the control M(^M) from unix files

The simple way to replace the control M character in UNIX format is as follows.

1. dos2unix filename newfilename
or
2. Open the file which contains the control M character and type the following
:%s/Control v control M//g

Workflow Table Information

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

Sunday, November 16, 2008

Approved Supplier List (ASL)

Approved Supplier Lists in Oracle is the term used to describe a list of items and commodities that have approved sources from a list of suppliers.

All procurement organisations maintain lists that associate the items and services they buy with the companies who supply them, either formally or informally. Data stored in a controlled, global repository containing relevant details about each ship-from/ship-to/item relationship, is known as an Approved Supplier List (ASL).

Navigation for Approved Supplier List
PO Super User -> Supply base -> Approved Supplier List

Select the organization, then define the supplier for category or Item for which you want to define, and status to approved.

API's used to insert the Approved Supplier List's are as follows:
po_asl_ths.insert_row
po_asl_attributes_ths.insert_row

Friday, November 14, 2008

Index on Clob datatype

CREATE INDEX ON () INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('DATASTORE CTXSYS.DEFAULT_DATASTORE');

The index creation includes a clause for setting the INDEXTYPE. I specified CTXSYS.CONTEXT as the type of index to create. CTXSYS is the Oracle Text schema, and this schema owns Oracle Text built-in features. The CTXAPP role granted to my user (TEXT_USER) provides permissions to use this index, but it must still be fully qualified with the schema name. CONTEXT is the type of index I am creating.