To Retriew the records from Org dependent Views

To Retriew the records from Org dependent Views on Sql Developer/Toad/Sql * Plus

begin
fnd_client_info.set_org_context(Org_id);
end;

R12 AR Fax Contact Point Creation

DECLARE
l_contact_point_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
l_email_rec hz_contact_point_v2pub.email_rec_type;
l_fax_rec hz_contact_point_v2pub.phone_rec_type;
l_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
l_web_rec_type hz_contact_point_v2pub.web_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_contact_point_rec.contact_point_type := 'PHONE';
l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
l_contact_point_rec.created_by_module := 'TCA_MIGRATION';
l_contact_point_rec.owner_table_id := 308013;
l_fax_rec.phone_area_code := '104';
l_fax_rec.phone_number := '234-565';
l_fax_rec.phone_line_type := 'FAX';
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => 'T',
p_contact_point_rec => l_contact_point_rec,
p_edi_rec => l_edi_rec_type,
p_email_rec => l_email_rec,
p_phone_rec => l_fax_rec,
p_telex_rec => l_telex_rec_type,
p_web_rec => l_web_rec_type,
x_contact_point_id => l_contact_point_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_contact_point_id ' || l_contact_point_id);
END IF;
END;

R12 AR Email Contact Point Creation

DECLARE
l_contact_point_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
l_email_rec hz_contact_point_v2pub.email_rec_type;
l_phone_rec hz_contact_point_v2pub.phone_rec_type;
l_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
l_web_rec_type hz_contact_point_v2pub.web_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_contact_point_rec.contact_point_type := 'EMAIL';
l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
l_contact_point_rec.created_by_module := 'TCA_MIGRATION';
l_contact_point_rec.owner_table_id := 308013;
l_email_rec.email_address := 'test@xxx.com';
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => 'T',
p_contact_point_rec => l_contact_point_rec,
p_edi_rec => l_edi_rec_type,
p_email_rec => l_email_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec_type,
p_web_rec => l_web_rec_type,
x_contact_point_id => l_contact_point_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_contact_point_id ' || l_contact_point_id);
END IF;
END;

R12 AR Phone Contact Point Creation

DECLARE
l_contact_point_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
l_email_rec hz_contact_point_v2pub.email_rec_type;
l_phone_rec hz_contact_point_v2pub.phone_rec_type;
l_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
l_web_rec_type hz_contact_point_v2pub.web_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_contact_point_rec.contact_point_type := 'PHONE';
l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
l_contact_point_rec.created_by_module := 'TCA_MIGRATION';
l_contact_point_rec.owner_table_id := 308013;
l_phone_rec.phone_area_code := '703';
l_phone_rec.phone_number := '8441213';
l_phone_rec.phone_line_type := 'GEN';
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => 'T',
p_contact_point_rec => l_contact_point_rec,
p_edi_rec => l_edi_rec_type,
p_email_rec => l_email_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec_type,
p_web_rec => l_web_rec_type,
x_contact_point_id => l_contact_point_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_contact_point_id ' || l_contact_point_id);
END IF;
END;

R12 AR Org Contact Creation

DECLARE
l_org_contact_id NUMBER;
l_party_rel_id_contactrel NUMBER;
l_party_id NUMBER;
l_party_number VARCHAR2 (20);
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
---THIS IS THE SUBJECT ID (PERSON INFO)
l_org_contact_rec.party_rel_rec.subject_id := 308026;
l_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
l_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
---THIS IS THE OBJECT ID (ORGANIZATION INFO)
l_org_contact_rec.party_rel_rec.object_id := 308013;
l_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
l_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
l_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
l_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
l_org_contact_rec.created_by_module := 'TCA_MIGRATION';
hz_party_contact_v2pub.create_org_contact
(p_init_msg_list => 'T',
p_org_contact_rec => l_org_contact_rec,
x_org_contact_id => l_org_contact_id,
x_party_rel_id => l_party_rel_id_contactrel,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_org_contact_id ' || l_org_contact_id);
DBMS_OUTPUT.put_line ( 'l_party_rel_id_contactrel '
|| l_party_rel_id_contactrel
);
DBMS_OUTPUT.put_line ('l_party_id ' || l_party_id);
DBMS_OUTPUT.put_line ('l_party_number ' || l_party_number);
END IF;
END;

R12 AR Person Creation

DECLARE
l_party_id NUMBER;
l_party_number VARCHAR2 (20);
l_profile_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_create_person_rec hz_party_v2pub.person_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_create_person_rec.person_last_name := 'Rohith';
l_create_person_rec.person_first_name := 'Kannur';
l_create_person_rec.created_by_module := 'TCA_MIGRATION';
hz_party_v2pub.create_person
(p_init_msg_list => 'T',
p_person_rec => l_create_person_rec,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_profile_id => l_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' || l_return_status);

IF l_return_status <> 'S'
THEN
DBMS_OUTPUT.put_line ('l_msg_count ' || l_msg_count);
DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);

FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_id ' || l_party_id);
DBMS_OUTPUT.put_line ('l_party_number ' || l_party_number);
DBMS_OUTPUT.put_line ('l_profile_id ' || l_profile_id);
END IF;
END;

R12 AR Customer Account Site Creation

DECLARE
l_cust_acct_site_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
mo_global.init ('AR');
mo_global.set_policy_context ('S', '85');
l_cust_acct_site_rec.cust_account_id := 96682;
l_cust_acct_site_rec.party_site_id := 179420;
l_cust_acct_site_rec.created_by_module := 'TCA_MIGRATION';
hz_cust_account_site_v2pub.create_cust_acct_site
(p_init_msg_list => 'T',
p_cust_acct_site_rec => l_cust_acct_site_rec,
x_cust_acct_site_id => l_cust_acct_site_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_cust_acct_site_id ' || l_cust_acct_site_id);
END IF;
END;

R12 AR customer Party Site Creation

DECLARE
l_party_site_id NUMBER;
l_party_site_number NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_party_site_rec.party_id := 308013;
l_party_site_rec.location_id := 25254;
l_party_site_rec.created_by_module := 'TCA_MIGRATION';
hz_party_site_v2pub.create_party_site
(p_init_msg_list => 'T',
p_party_site_rec => l_party_site_rec,
x_party_site_id => l_party_site_id,
x_party_site_number => l_party_site_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_site_id ' || l_party_site_id);
DBMS_OUTPUT.put_line ('l_party_site_number ' || l_party_site_number);
END IF;
END;

Triggers Concepts - 2

Row Level Trigger With Referencing Clause
CREATE TABLE person (
fname VARCHAR2(15),
lname VARCHAR2(15));

CREATE TABLE audit_log (
o_fname VARCHAR2(15),
o_lname VARCHAR2(15),
n_fname VARCHAR2(15),
n_lname VARCHAR2(15),
chng_by VARCHAR2(10),
chng_when DATE);

CREATE OR REPLACE TRIGGER referencing_clause
AFTER UPDATE
ON person
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE
act VARCHAR2(1);
BEGIN
INSERT INTO audit_log
(o_fname, o_lname, n_fname, n_lname, chng_by, chng_when)
VALUES
(:OLD.fname, :OLD.lname, :NEW.fname, :NEW.lname, USER, SYSDATE);
END referencing_clause;
/

INSERT INTO person (fname, lname) VALUES ('Dan', 'Morgan');

SELECT * FROM person;
SELECT * FROM audit_log;

UPDATE person
SET lname = 'Cline';

SELECT * FROM person;
SELECT * FROM audit_log;

UPDATE person
SET fname = 'Mark', lname = 'Townsend';

SELECT * FROM person;
SELECT * FROM audit_log;

Follows Clause CREATE TABLE test (
testcol VARCHAR2(15));

INSERT INTO test VALUES ('dummy');

CREATE OR REPLACE TRIGGER follows_a
AFTER UPDATE
ON test
FOR EACH ROW
BEGIN
dbms_output.put_line('A');
END follows_a;
/

CREATE OR REPLACE TRIGGER follows_b
AFTER UPDATE
ON test
FOR EACH ROW
BEGIN
dbms_output.put_line('B');
END follows_b;
/

set serveroutput on

UPDATE test SET testcol = 'a';

CREATE OR REPLACE TRIGGER follows_b
AFTER UPDATE
ON test
FOR EACH ROW
FOLLOWS uwclass.follows_a
BEGIN
dbms_output.put_line('B');
END follows_b;
/

UPDATE test SET testcol = 'a';

Compound Triggers (new 11g)

Compound triggers allow for writing a single trigger incorporating STATEMENT and ROW LEVEL and BEFORE and AFTER CREATE TRIGGER
FOR ON
COMPOUND TRIGGER

BEFORE STATEMENT IS
BEGIN
...
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
...
END BEFORE EACH ROW;

AFTER STATEMENT IS
BEGIN
...
END AFTER STATEMENT;

AFTER EACH ROW IS
BEGIN
...
END AFTER EACH ROW;
END compound_trigger;
/
CREATE TABLE test AS
SELECT table_name, tablespace_name
FROM user_tables;

set serveroutput on

CREATE OR REPLACE TRIGGER compound_trig
FOR INSERT ON test
COMPOUND TRIGGER
-------------------------------
BEFORE STATEMENT IS
BEGIN
dbms_output.put_line('BEFORE STATEMENT LEVEL');
END BEFORE STATEMENT;
-------------------------------
BEFORE EACH ROW IS
BEGIN
dbms_output.put_line('BEFORE ROW LEVEL');
END BEFORE EACH ROW;
-------------------------------
AFTER STATEMENT IS
BEGIN
dbms_output.put_line('AFTER STATEMENT LEVEL');
END AFTER STATEMENT;
-------------------------------
AFTER EACH ROW IS
BEGIN
dbms_output.put_line('AFTER ROW LEVEL');
END AFTER EACH ROW;
END compound_trig;
/

SELECT trigger_name, trigger_type
FROM user_triggers;

INSERT INTO test
(table_name, tablespace_name)
VALUES
('MORGAN', 'UWDATA');

Triggers concepts

Data Dictionary Views Related
To DDL Triggers trigger$

dba_triggers all_triggers user_triggers

System Privileges Related To Table Triggers create trigger
create any trigger
administer database trigger
alter any trigger
drop any trigger
Table Trigger Firing Options -- before constraints are applied
BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE

-- after constraints are applied
AFTER INSERT
AFTER UPDATE
AFTER DELETE
Transaction Model Oracle transactions are atomic. No commit or rollback are allowed in a trigger.
Maximum trigger size 32K - but you can call procedures and function in triggers to perform processing

Create Statement Level Triggers (the default)

Statement Level Trigger With A Single Action CREATE OR REPLACE TRIGGER
[]
[OR OR ]
ON
[FOLLOWS ]

DECLARE

BEGIN

EXCEPTION

END ;
/
CREATE TABLE orders (
somecolumn VARCHAR2(20),
numbercol NUMBER(10),
datecol DATE);

CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
ON orders

DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders SET somecolumn = 'XYZ';

Statement Level Trigger With Multiple Actions
CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders

DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' When Inserting');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' When Updating');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' When Deleting');
END IF;
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders SET somecolumn = 'DEF' WHERE ROWNUM = 1;

DELETE FROM orders WHERE ROWNUM = 1;

Create Row Level Triggers
Note: AFTER row triggers create less UNDO than BEFORE row triggers so use AFTER when possible.

Row Level Trigger ... most common usage to provide a surrogate key from a sequence CREATE OR REPLACE TRIGGER
[FOLLOWS ]
[]
OR OR
[OF ]
ON
REFERENCING NEW AS OLD AS PARENT AS
FOR EACH ROW

DECLARE

BEGIN

EXCEPTION

END ;
/
CREATE TABLE t (
rid NUMBER(5),
col VARCHAR2(3));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid)
USING INDEX;

CREATE SEQUENCE seq_t;

CREATE OR REPLACE TRIGGER row_level
BEFORE INSERT
ON t
FOR EACH ROW

BEGIN
SELECT seq_t.NEXTVAL
INTO:NEW.rid
FROM dual;
dbms_output.put_line(:NEW.rid);
END row_level;
/

INSERT INTO t (col) VALUES ('A');
INSERT INTO t (col) VALUES ('B');
INSERT INTO t (col) VALUES ('C');

SELECT * FROM t;

Row Level Trigger With A Single Action
CREATE OR REPLACE TRIGGER row_level
BEFORE UPDATE
ON orders
FOR EACH ROW

DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END row_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');

SELECT * FROM orders;

UPDATE orders SET somecolumn = 'XYZ';

Row Level Trigger With Multiple Actions
CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW

DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' On Insert');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' On Update');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' On Delete');
END IF;
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders
SET somecolumn = 'ZZT';

DELETE FROM orders WHERE rownum < 4;

Row Level Trigger With OF Clause
CREATE OR REPLACE TRIGGER of_clause
BEFORE UPDATE
OF numbercol
ON orders
FOR EACH ROW

DECLARE
vMsg VARCHAR2(40) := 'Update Will Change numbercol Column';
BEGIN
dbms_output.put_line(vMsg);
END of_clause;
/

set serveroutput on

UPDATE orders
SET numbercol = 8;

Oracle Built-in Functions

Below is the useful link for Oracle Built in Functions
http://www.psoug.org/reference/builtin_functions.html

String Functions

ASCII
Get The ASCII Value Of A Character
ASCII(ch VARCHAR2 CHARACTER SET ANY_CS) RETURN PLS_INTEGER;
SELECT ASCII('A') FROM dual;
SELECT ASCII('Z') FROM dual;
SELECT ASCII('a') FROM dual;
SELECT ASCII('z') FROM dual;
SELECT ASCII(' ') FROM dual;

CASE Related Functions
Upper Case UPPER(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT UPPER('Dan Morgan') FROM dual;
Lower Case LOWER(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT LOWER('Dan Morgan') FROM dual;
Initial Letter Upper Case INITCAP(ch VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT INITCAP('DAN MORGAN') FROM dual;
NLS Upper Case NLS_UPPER()
SELECT NLS_UPPER('Dan Morgan', 'NLS_SORT = XDanish')
FROM dual;
NLS Lower Case NLS_LOWER()
SELECT NLS_LOWER('Dan Morgan', 'NLS_SORT = XFrench')
FROM dual;
NLS Initial Letter Upper Case NLS_INITCAP()
SELECT NLS_INITCAP('DAN MORGAN', 'NLS_SORT = XGerman')
FROM dual;

CHR
Character CHR(n PLS_INTEGER) RETURN VARCHAR2;
SELECT(CHR(68) CHR(65) CHR(78)) FROM dual;

SELECT(CHR(68) CHR(97) CHR(110)) FROM dual;

COALESCE

Returns the first non-null occurrence COALESCE(, , , ...)
CREATE TABLE test (
col1 VARCHAR2(1),
col2 VARCHAR2(1),
col3 VARCHAR2(1));

INSERT INTO test VALUES (NULL, 'B', 'C');
INSERT INTO test VALUES ('A', NULL, 'C');
INSERT INTO test VALUES (NULL, NULL, 'C');
INSERT INTO test VALUES ('A', 'B', 'C');

SELECT COALESCE(col1, col2, col3) FROM test;

CONCAT
Concatenate
Overload 1
standard.CONCAT(
lef VARCHAR2 CHARACTER SET ANY_CS,
right VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET LEFT%CHARSET;
SELECT CONCAT('Dan ', 'Morgan') FROM dual;

Concatenate

Overload 2 CONCAT(left IN CLOB, right IN CLOB) RETURN CLOB
set serveroutput on

DECLARE
c1 CLOB := TO_CLOB('Dan ');
c2 CLOB := TO_CLOB('Morgan');
c3 CLOB;
BEGIN
SELECT CONCAT('Dan ', 'Morgan')
INTO c3
FROM dual;

dbms_output.put_line(c3);
END;
/

CONVERT
Converts From One Character Set To Another CONVERT(,,
)
SELECT CONVERT('Ġʠ͠ՠؠA B C D E','US7ASCII','WE8ISO8859P1')
FROM dual;

DUMP

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value DUMP( [,[,[,]]])

8 Octal
10 Decimal
16 Hexidecimal
17 Single Characters
1008 octal notation with the character set name
1010 decimal notation with the character set name
1016 hexadecimal notation with the character set name
1017 single characters with the character set name

set linesize 121
col dmp format a50

SELECT table_name, DUMP(table_name) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16) DMP FROM user_tables;

SELECT table_name, DUMP(table_name, 16, 7, 4) DMP FROM user_tables;

INSTR
See the post in my Blog on this

INSTRB
Location of a string, within another string, in bytes INSTRB(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTRB('Dan Morgan', ' ', 1, 1) FROM dual;

INSTRC
Location of a string, within another string, in Unicode complete characters INSTRC(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTRC('Dan Morgan', ' ', 1, 1) FROM dual;

INSTR2
Location of a string, within another string, in UCS2 code points INSTR2(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTR2('Dan Morgan', ' ', 1, 1) FROM dual;

INSTR4
Location of a string, within another string, in UCS4 code points INSTR4(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;
SELECT INSTR4('Dan Morgan', ' ', 1, 1) FROM dual;

LENGTH
String Length LENGTH()
SELECT LENGTH('Dan Morgan') FROM dual;

LENGTHB
Returns length in bytes LENGTHB()
SELECT table_name, LENGTHB(table_name) FROM user_tables;
Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available.

LPAD
Left Pad

Overload 1 LPAD(
str1 VARCHAR2 CHARACTER SET ANY_CS,
len PLS_INTEGER,
PAD VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT LPAD('Dan Morgan', 25, 'x') FROM dual;
Overload 2 LPAD(
str1 VARCHAR2 CHARACTER SET ANY_CS,
len PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT LPAD('Dan Morgan', 25) FROM dual;
Overload 3 LPAD(
str1 CLOB CHARACTER SET ANY_CS,
len NUMBER,
PAD CLOB CHARACTER SET STR1%CHARSET)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD
Overload 4 LPAD(
str1 CLOB CHARACTER SET ANY_CS,
len INTEGER)
RETURN CLOB CHARACTER SET STR1%CHARSET;
TBD

LTRIM
Left Trim

Overload 1 LTRIM(
str1 VARCHAR2 CHARACTER SET ANY_CS := ' ',
tset VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT '->' LTRIM(' Dan Morgan ') '<-' FROM dual; Overload 2 LTRIM( STR1 VARCHAR2 CHARACTER SET ANY_CS := ' ') RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SELECT '->' LTRIM('xxx Dan Morgan ') '<-' FROM dual; SELECT '->' LTRIM('xxxDan Morgan ', 'x') '<-' FROM dual; MAX The Maximum String based on the current sort parameter MAX()
SELECT MAX(table_name)
FROM user_tables;

MIN
The Minimum String based on the current sort parameter MIN()
SELECT MIN(table_name)
FROM user_tables;

NLSSORT

Returns the string of bytes used to sort a string.

The string returned is of RAW data type NLSSORT(, 'NLS_SORT = );
CREATE TABLE test (name VARCHAR2(15));
INSERT INTO test VALUES ('Gaardiner');
INSERT INTO test VALUES ('Gaberd');
INSERT INTO test VALUES ('G⢥rd');
COMMIT;

SELECT * FROM test ORDER BY name;

SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');

SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = BINARY_CI');

Quote Delimiters

q used to define a quote delimiter for PL/SQL q'';
set serveroutput on

DECLARE
s1 VARCHAR2(20);
s2 VARCHAR2(20);
s3 VARCHAR2(20);
BEGIN
s1 := q'[Isn't this cool]';
s2 := q'"Isn't this cool"';
s3 := q'Isn't this cool';

dbms_output.put_line(s1);
dbms_output.put_line(s2);
dbms_output.put_line(s3);
END;
/

REVERSE
Reverse REVERSE()
SELECT REVERSE('Dan Morgan') FROM dual;

SELECT DUMP('Dan Morgan') FROM dual;
SELECT DUMP(REVERSE('Dan Morgan')) FROM dual;

RPAD
Right Pad

Overload 1 RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER,
pad VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT RPAD('Dan Morgan', 25, 'x') FROM dual;
Overload 2 RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SELECT RPAD('Dan Morgan', 25) '<-' FROM dual; RTRIM Right Trim Overload 1 RTRIM( str1 VARCHAR2 CHARACTER SET ANY_CS := ' ', tset VARCHAR2 CHARACTER SET STR1%CHARSET) RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SELECT '->' RTRIM(' Dan Morganxxx') '<-' FROM dual; SELECT '->' RTRIM(' Dan Morganxxx', 'xxx') '<-' FROM dual; Overload 2 RTRIM( str1 VARCHAR2 CHARACTER SET ANY_CS := ' ') RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SELECT '->' RTRIM(' Dan Morgan ') '<-' FROM dual; SOUNDEX Returns Character String Containing The Phonetic Representation Of Another String Rules: Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y Assign numbers to the remaining letters (after the first) as follows: b, f, p, v = 1 c, g, j, k, q, s, x, z = 2 d, t = 3 l = 4 m, n = 5 r = 6 If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first. Return the first four bytes padded with 0. SOUNDEX(ch VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET ch%CHARSET; CREATE TABLE test ( namecol VARCHAR2(15)); INSERT INTO test (namecol) VALUES ('Smith'); INSERT INTO test (namecol) VALUES ('Smyth'); INSERT INTO test (namecol) VALUES ('Smythe'); INSERT INTO test (namecol) VALUES ('Smither'); INSERT INTO test (namecol) VALUES ('Smidt'); INSERT INTO test (namecol) VALUES ('Smick'); INSERT INTO test (namecol) VALUES ('Smiff'); COMMIT; SELECT name, SOUNDEX(namecol) FROM test; SELECT * FROM test WHERE SOUNDEX(namecol) = SOUNDEX('SMITH'); SUBSTR See links at page bottom SUBSTRB Returns a substring counting bytes rather than characters SUBSTRB( STR1 VARCHAR2 CHARACTER SET ANY_CS, POS PLS_INTEGER, -- starting position LEN PLS_INTEGER := 2147483647) -- number of characters RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SUBSTRC( STR1 VARCHAR2 CHARACTER SET ANY_CS, POS PLS_INTEGER, -- starting position LEN PLS_INTEGER := 2147483647) -- number of characters RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SUBSTR2( STR1 VARCHAR2 CHARACTER SET ANY_CS, POS PLS_INTEGER, -- starting position LEN PLS_INTEGER := 2147483647) -- number of characters RETURN VARCHAR2 CHARACTER SET STR1%CHARSET; SUBSTR4( STR1 VARCHAR2 CHARACTER SET ANY_CS, POS PLS_INTEGER, -- starting position LEN PLS_INTEGER := 2147483647) -- number of characters RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
TREAT Changes The Declared Type Of An Expression TREAT ( AS REF schema.type))
SELECT name, TREAT(VALUE(p) AS employee_t).salary SALARY
FROM persons p;

TRIM (variations are LTRIM and RTRIM)
Trim Spaces TRIM()
SELECT ' Dan Morgan ' FROM dual;

SELECT TRIM(' Dan Morgan ') FROM dual;
Trim Other Characters TRIM( FROM )
SELECT TRIM('D' FROM 'Dan Morgan') FROM dual;
Trim By CHR value TRIM()
SELECT ASCII(SUBSTR('Dan Morgan',1,1)) FROM dual;

SELECT TRIM(CHR(68) FROM 'Dan Morgan') FROM dual;

Vertical Bars
Also known as Pipes
SELECT 'Dan' ' ' 'Morgan' FROM dual;

with alias

SELECT 'Dan' ' ' 'Morgan' NAME FROM dual;
or
SELECT 'Dan' ' ' 'Morgan' AS NAME FROM dual;

VSIZE
Byte Size VSIZE(e IN VARCHAR2) RETURN NUMBER
SELECT VSIZE('Dan Morgan') FROM dual;

SUBSTR And INSTR String Functions

SUBSTR (Substring) Built-in String Function

SUBSTR (overload 1)
SUBSTR(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;

SUBSTR (overload 2)
SUBSTR(
STR1 CLOB CHARACTER SET ANY_CS,
POS NUMBER, -- starting position
LEN NUMBER := 2147483647) -- number of characters
RETURN CLOB CHARACTER SET STR1%CHARSET;

Substring Beginning Of String
SELECT SUBSTR(, 1, )
FROM dual;
SELECT SUBSTR('Take the first four characters', 1, 4) FIRST_FOUR
FROM dual;

Substring Middle Of String
SELECT SUBSTR(, , )
FROM dual.
SELECT SUBSTR('Take the first four characters', 16, 4) MIDDLE_FOUR
FROM dual;

Substring End of String
SELECT SUBSTR(, )
FROM dual;

SELECT SUBSTR('Take the first four characters', 16) SIXTEEN_TO_END
FROM dual;

SELECT SUBSTR('Take the first four characters', -4) FINAL_FOUR
FROM dual;

INSTR (Instring) Built-in String Function

INSTR (overload 1)
INSTR(
STR1 VARCHAR2 CHARACTER SET ANY_CS, -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET, -- string to locate
POS PLS_INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN PLS_INTEGER;

INSTR (overload 2)
INSTR(
STR1 CLOB CHARACTER SET ANY_CS, -- test string
STR2 CLOB CHARACTER SET STR1%CHARSET, -- string to locate
POS INTEGER := 1, -- position
NTH POSITIVE := 1) -- occurrence number
RETURN INTEGER;

Instring For Matching First Value Found
SELECT INSTR(, , ,
FROM dual;
SELECT INSTR('Take the first four characters', 'a', 1, 1) FOUND_1
FROM dual;

Instring If No Matching Second Value Found
SELECT INSTR('Take the first four characters', 'a', 1, 2) FOUND_2
FROM dual;

Instring For Multiple Characters
SELECT INSTR('Take the first four characters', 'four', 1, 1) MCHARS
FROM dual;

Reverse Direction Search
SELECT INSTR('Take the first four characters', 'a', -1, 1) REV_SRCH
FROM dual;

Reverse Direction Search Second Match
SELECT INSTR('Take the first four characters', 'a', -1, 2) REV_TWO
FROM dual;

String Parsing By Combining SUBSTR And INSTR Built-in String Functions

List parsing first value

Take up to the character before the first comma
SELECT SUBSTR('abc,def,ghi', 1 ,INSTR('abc,def,ghi', ',', 1, 1)-1)
FROM dual;

List parsing center value

Take the value between the commas
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 1)+1,
INSTR('abc,def,ghi',',',1,2)-INSTR('abc,def,ghi',',',1,1)-1)
FROM dual;

List parsing last value

Take the value after the last comma
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 2)+1)
FROM dual;

GL Tables which stores parent and child segments information

FND_FLEX_VALUES and
FND_FLEX_VALUE_HIERARCHIES tables.

Below link gives the information on parent and child segments in General Ledger

http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/gl/acctgf06.htm

Pl-Sql: Null Values in Comparisons and Conditional Statements

When working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

Comparisons involving nulls always yield NULL

Applying the logical operator NOT to a null yields NULL

In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed

If the expression in a simple CASE statement or CASE expression yields NULL, it cannot be matched by using WHEN NULL. In this case, you would need to use the searched case syntax and test WHEN expression IS NULL.

In the example below, you might expect the sequence of statements to execute because x and y seem unequal. But, nulls are indeterminate. Whether or not x is equal to y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
x NUMBER := 5;
y NUMBER := NULL;
BEGIN
IF x != y THEN -- yields NULL, not TRUE
dbms_output.put_line('x != y'); -- not executed
ELSIF x = y THEN -- also yields NULL
dbms_output.put_line('x = y');
ELSE
dbms_output.put_line('Can''t tell if x and y are equal or not...');
END IF;
END;
/
In the next example, you might expect the sequence of statements to execute because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
a NUMBER := NULL;
b NUMBER := NULL;
BEGIN
IF a = b THEN -- yields NULL, not TRUE
dbms_output.put_line('a = b'); -- not executed
ELSIF a != b THEN -- yields NULL, not TRUE
dbms_output.put_line('a != b'); -- not executed
ELSE
dbms_output.put_line('Can''t tell if two NULLs are equal');
END IF;
END;
/
NULLs and the NOT Operator
Recall that applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent:

IF x > y THEN | IF NOT x > y THEN
high := x; | high := y;
ELSE | ELSE
high := y; | high := x;
END IF; | END IF;

The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. If neither x nor y is null, both IF statements assign the same value to high. However, if either x or y is null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high.


NULLs and Zero-Length Strings

PL/SQL treats any zero-length string like a null. This includes values returned by character functions and Boolean expressions. For example, the following statements assign nulls to the target variables:

DECLARE
null_string VARCHAR2(80) := TO_CHAR('');
address VARCHAR2(80);
zip_code VARCHAR2(80) := SUBSTR(address, 25, 0);
name VARCHAR2(80);
valid BOOLEAN := (name != '');
BEGIN
NULL;
END;
/
Use the IS NULL operator to test for null strings, as follows:

IF my_string IS NULL THEN ...

NULLs and the Concatenation Operator

The concatenation operator ignores null operands. For example, the expression

'apple' || NULL || NULL || 'sauce'

returns the following value:

'applesauce'

NULLs as Arguments to Built-In Functions

If a null argument is passed to a built-in function, a null is returned except in the following cases.

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000:

DECLARE
the_manager VARCHAR2(40);
name employees.last_name%TYPE;
BEGIN
-- NULL is a valid argument to DECODE. In this case, manager_id is null
-- and the DECODE function returns 'nobody'.
SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ' is managed by ' || the_manager);
END;
/
The function NVL returns the value of its second argument if its first argument is null. In the following example, if the column specified in the query is null, the function returns the value -1 to signify a non-existent employee in the output:

DECLARE
the_manager employees.manager_id%TYPE;
name employees.last_name%TYPE;
BEGIN
-- NULL is a valid argument to NVL. In this case, manager_id is null
-- and the NVL function returns -1.
SELECT NVL(manager_id, -1), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ' is managed by employee #' || the_manager);
END;
/
The function REPLACE returns the value of its first argument if its second argument is null, whether the optional third argument is present or not. For example, the following call to REPLACE does not make any change to the value of OLD_STRING:

DECLARE
string_type VARCHAR2(60);
old_string string_type%TYPE := 'Apples and oranges';
my_string string_type%TYPE := 'more apples';
-- NULL is a valid argument to REPLACE, but does not match
-- anything so no replacement is done.
new_string string_type%TYPE := REPLACE(old_string, NULL, my_string);
BEGIN
dbms_output.put_line('Old string = ' || old_string);
dbms_output.put_line('New string = ' || new_string);
END;
/
If its third argument is null, REPLACE returns its first argument with every occurrence of its second argument removed. For example, the following call to REPLACE removes all the dashes from DASHED_STRING, instead of changing them to another character:

DECLARE
string_type VARCHAR2(60);
dashed string_type%TYPE := 'Gold-i-locks';
-- When the substitution text for REPLACE is NULL,
-- the text being replaced is deleted.
name string_type%TYPE := REPLACE(dashed, '-', NULL);
BEGIN
dbms_output.put_line('Dashed name = ' || dashed);
dbms_output.put_line('Dashes removed = ' || name);
END;
/
If its second and third arguments are null, REPLACE just returns its first argument.

PL/SQL -Advantages

Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

Support for SQL
Support for object-oriented programming
Better performance
Higher productivity
Full portability
Tight integration with Oracle
Tight security
Tight Integration with SQL

The PL/SQL language is tightly integrated with SQL. You do not have to translate between SQL and PL/SQL datatypes: a NUMBER or VARCHAR2 column in the database is stored in a NUMBER or VARCHAR2 variable in PL/SQL. This integration saves you both learning time and processing time. Special PL/SQL language features let you work with table columns and rows without specifying the datatypes, saving on maintenance work when the table definitions change.

Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages.

Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation.

Many database features, such as triggers and object types, make use of PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.

Support for SQL
SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as SELECT, INSERT, UPDATE, and DELETE make it easy to manipulate the data stored in a relational database.

PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. This extensive SQL support lets you manipulate Oracle data flexibly and safely. Also, PL/SQL fully supports SQL datatypes, reducing the need to convert data passed between your applications and the database.

PL/SQL also supports dynamic SQL, a programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements at run time, without knowing details such as table names and WHERE clauses in advance.

Better Performance
Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead.

With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application. PL/SQL even has language features to further speed up SQL statements that are issued inside a loop.

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient. Because stored procedures execute in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.

Higher Productivity
PL/SQL extends tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits.

PL/SQL is the same in all environments. Once you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.

Full Portability
Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.

Tight Security
PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement.

Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.

Support for Object-Oriented Programming
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.

By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs.

In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. This direct mapping helps your programs better reflect the world they are trying to simulate.

What Is Dynamic SQL?

What Is Dynamic SQL?
Some programs must build and process SQL statements where some information is not known in advance. A reporting application might build different SELECT statements for the various reports it generates, substituting new table and column names and ordering or grouping by different columns. Database management applications might issue statements such as CREATE, DROP, and GRANT that cannot be coded directly in a PL/SQL program. These statements are called dynamic SQL statements.

Dynamic SQL statements built as character strings built at run time. The strings contain the text of a SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. Placeholder names are prefixed by a colon, and the names themselves do not matter. For example, PL/SQL makes no distinction between the following strings:

'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'
To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.

How to Fetch Data Using Cursors in Packages and Procedures

Fetching Data

The following package defines a PL/SQL cursor variable type Emp_val_cv_type, and two procedures. The first procedure, Open_emp_cv, opens the cursor variable using a bind variable in the WHERE clause. The second procedure, Fetch_emp_data, fetches rows from the Emp_tab table using the cursor variable.

CREATE OR REPLACE PACKAGE Emp_data AS
TYPE Emp_val_cv_type IS REF CURSOR RETURN Emp_tab%ROWTYPE;
PROCEDURE Open_emp_cv (Emp_cv IN OUT Emp_val_cv_type,
Dept_number IN INTEGER);
PROCEDURE Fetch_emp_data (emp_cv IN Emp_val_cv_type,
emp_row OUT Emp_tab%ROWTYPE);
END Emp_data;

CREATE OR REPLACE PACKAGE BODY Emp_data AS
PROCEDURE Open_emp_cv (Emp_cv IN OUT Emp_val_cv_type,
Dept_number IN INTEGER) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM Emp_tab WHERE deptno = dept_number;
END open_emp_cv;
PROCEDURE Fetch_emp_data (Emp_cv IN Emp_val_cv_type,
Emp_row OUT Emp_tab%ROWTYPE) IS
BEGIN
FETCH Emp_cv INTO Emp_row;
END Fetch_emp_data;
END Emp_data;


The following example shows how to call the Emp_data package procedures from a PL/SQL block:

DECLARE
-- declare a cursor variable
Emp_curs Emp_data.Emp_val_cv_type;
Dept_number Dept_tab.Deptno%TYPE;
Emp_row Emp_tab%ROWTYPE;

BEGIN
Dept_number := 20;
-- open the cursor using a variable
Emp_data.Open_emp_cv(Emp_curs, Dept_number);
-- fetch the data and display it
LOOP
Emp_data.Fetch_emp_data(Emp_curs, Emp_row);
EXIT WHEN Emp_curs%NOTFOUND;
DBMS_OUTPUT.PUT(Emp_row.Ename || ' ');
DBMS_OUTPUT.PUT_LINE(Emp_row.Sal);
END LOOP;
END;

Implementing Variant Records

The power of cursor variables comes from their ability to point to different cursors. In the following package example, a discriminant is used to open a cursor variable to point to one of two different cursors:

CREATE OR REPLACE PACKAGE Emp_dept_data AS
TYPE Cv_type IS REF CURSOR;
PROCEDURE Open_cv (Cv IN OUT cv_type,
Discrim IN POSITIVE);
END Emp_dept_data;

CREATE OR REPLACE PACKAGE BODY Emp_dept_data AS
PROCEDURE Open_cv (Cv IN OUT cv_type,
Discrim IN POSITIVE) IS
BEGIN
IF Discrim = 1 THEN
OPEN Cv FOR SELECT * FROM Emp_tab WHERE Sal > 2000;
ELSIF Discrim = 2 THEN
OPEN Cv FOR SELECT * FROM Dept_tab;
END IF;
END Open_cv;
END Emp_dept_data;


You can call the Open_cv procedure to open the cursor variable and point it to either a query on the Emp_tab table or the Dept_tab table. The following PL/SQL block shows how to fetch using the cursor variable, and then use the ROWTYPE_MISMATCH predefined exception to handle either fetch:

DECLARE
Emp_rec Emp_tab%ROWTYPE;
Dept_rec Dept_tab%ROWTYPE;
Cv Emp_dept_data.CV_TYPE;

BEGIN
Emp_dept_data.open_cv(Cv, 1); -- Open Cv For Emp_tab Fetch
Fetch cv INTO Dept_rec; -- but fetch into Dept_tab record
-- which raises ROWTYPE_MISMATCH
DBMS_OUTPUT.PUT(Dept_rec.Deptno);
DBMS_OUTPUT.PUT_LINE(' ' || Dept_rec.Loc);

EXCEPTION
WHEN ROWTYPE_MISMATCH THEN
BEGIN
DBMS_OUTPUT.PUT_LINE
('Row type mismatch, fetching Emp_tab data...');
FETCH Cv INTO Emp_rec;
DBMS_OUTPUT.PUT(Emp_rec.Deptno);
DBMS_OUTPUT.PUT_LINE(' ' || Emp_rec.Ename);
END;

PL/SQL Packages

PL/SQL Packages
A package is an encapsulated collection of related program objects (for example, procedures, functions, variables, constants, cursors, and exceptions) stored together in the database.

Using packages is an alternative to creating procedures and functions as standalone schema objects. Packages have many advantages over standalone procedures and functions. For example, they:

Let you organize your application development more efficiently.
Let you grant privileges more efficiently.
Let you modify package objects without recompiling dependent schema objects.
Enable Oracle Database to read multiple package objects into memory at once.
Can contain global variables and cursors that are available to all procedures and functions in the package.
Let you overload procedures or functions. Overloading a procedure means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.

Example of a PL/SQL Package Specification and Body
The following example shows a package specification for a package named Employee_management. The package contains one stored function and two stored procedures. The body for this package defines the function and the procedures:

CREATE PACKAGE BODY Employee_management AS
FUNCTION Hire_emp (Name VARCHAR2, Job VARCHAR2,
Mgr NUMBER, Hiredate DATE, Sal NUMBER, Comm NUMBER,
Deptno NUMBER) RETURN NUMBER IS
New_empno NUMBER(10);

-- This function accepts all arguments for the fields in
-- the employee table except for the employee number.
-- A value for this field is supplied by a sequence.
-- The function returns the sequence number generated
-- by the call to this function.

BEGIN
SELECT Emp_sequence.NEXTVAL INTO New_empno FROM dual;
INSERT INTO Emp_tab VALUES (New_empno, Name, Job, Mgr,
Hiredate, Sal, Comm, Deptno);
RETURN (New_empno);
END Hire_emp;

PROCEDURE fire_emp(emp_id IN NUMBER) AS

-- This procedure deletes the employee with an employee
-- number that corresponds to the argument Emp_id. If
-- no employee is found, then an exception is raised.

BEGIN
DELETE FROM Emp_tab WHERE Empno = Emp_id;
IF SQL%NOTFOUND THEN
Raise_application_error(-20011, 'Invalid Employee
Number: ' || TO_CHAR(Emp_id));
END IF;
END fire_emp;

PROCEDURE Sal_raise (Emp_id IN NUMBER, Sal_incr IN NUMBER) AS

-- This procedure accepts two arguments. Emp_id is a
-- number that corresponds to an employee number.
-- SAL_INCR is the amount by which to increase the
-- employee's salary. If employee exists, then update
-- salary with increase.

BEGIN
UPDATE Emp_tab
SET Sal = Sal + Sal_incr
WHERE Empno = Emp_id;
IF SQL%NOTFOUND THEN
Raise_application_error(-20011, 'Invalid Employee
Number: ' || TO_CHAR(Emp_id));
END IF;
END Sal_raise;
END Employee_management;


--------------------------------------------------------------------------------
Note:
If you want to try this example, then first create the sequence number Emp_sequence. Do this with the following SQL*Plus statement:

SQL> CREATE SEQUENCE Emp_sequence
> START WITH 8000 INCREMENT BY 10;


Creating Packages
Each part of a package is created with a different statement. Create the package specification using the CREATE PACKAGE statement. The CREATE PACKAGE statement declares public package objects.

To create a package body, use the CREATE PACKAGE BODY statement. The CREATE PACKAGE BODY statement defines the procedural code of the public procedures and functions declared in the package specification.

You can also define private, or local, package procedures, functions, and variables in a package body. These objects can only be accessed by other procedures and functions in the body of the same package. They are not visible to external users, regardless of the privileges they hold.

It is often more convenient to add the OR REPLACE clause in the CREATE PACKAGE or CREATE PACKAGE BODY statements when you are first developing your application. The effect of this option is to drop the package or the package body without warning. The CREATE statements would then be the following:

CREATE OR REPLACE PACKAGE Package_name AS ...


and

CREATE OR REPLACE PACKAGE BODY Package_name AS ...


Creating Packaged Objects
The body of a package can contain include:

Procedures and functions declared in the package specification.
Definitions of cursors declared in the package specification.
Local procedures and functions, not declared in the package specification.
Local variables.
Procedures, functions, cursors, and variables that are declared in the package specification are global. They can be called, or used, by external users that have EXECUTE permission for the package or that have EXECUTE ANY PROCEDURE privileges.

When you create the package body, make sure that each procedure that you define in the body has the same parameters, by name, datatype, and mode, as the declaration in the package specification. For functions in the package body, the parameters and the return type must agree in name and type.

Privileges to Create or Drop Packages
The privileges required to create or drop a package specification or package body are the same as those required to create or drop a standalone procedure or function.

Procedures %TYPE and %ROWTYPE Attributes Usage

Procedures %TYPE and %ROWTYPE Attributes Usage

Use the type attributes %TYPE and %ROWTYPE to constrain the parameter. For example, the Get_emp_names procedure specification in "Parameters for Procedures and Functions" could be written as the following:

PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)


This has the Dept_num parameter take the same datatype as the Deptno column in the Emp_tab table. The column and table must be available when a declaration using %TYPE (or %ROWTYPE) is elaborated.

Using %TYPE is recommended, because if the type of the column in the table changes, then it is not necessary to change the application code.

If the Get_emp_names procedure is part of a package, then you can use previously-declared public (package) variables to constrain a parameter datatype. For example:

Dept_number number(2);
...
PROCEDURE Get_emp_names(Dept_num IN Dept_number%TYPE);


Use the %ROWTYPE attribute to create a record that contains all the columns of the specified table. The following example defines the Get_emp_rec procedure, which returns all the columns of the Emp_tab table in a PL/SQL record for the given empno:


--------------------------------------------------------------------------------
Caution:
To execute the following, use CREATE OR REPLACE PROCEDURE...

--------------------------------------------------------------------------------


PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE,
Emp_ret OUT Emp_tab%ROWTYPE) IS
BEGIN
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
INTO Emp_ret
FROM Emp_tab
WHERE Empno = Emp_number;
END;


You could call this procedure from a PL/SQL block as follows:

DECLARE
Emp_row Emp_tab%ROWTYPE; -- declare a record matching a
-- row in the Emp_tab table
BEGIN
Get_emp_rec(7499, Emp_row); -- call for Emp_tab# 7499
DBMS_OUTPUT.PUT(Emp_row.Ename || ' ' || Emp_row.Empno);
DBMS_OUTPUT.PUT(' ' || Emp_row.Job || ' ' || Emp_row.Mgr);
DBMS_OUTPUT.PUT(' ' || Emp_row.Hiredate || ' ' || Emp_row.Sal);
DBMS_OUTPUT.PUT(' ' || Emp_row.Comm || ' '|| Emp_row.Deptno);
DBMS_OUTPUT.NEW_LINE;
END;


Stored functions can also return values that are declared using %ROWTYPE. For example:

FUNCTION Get_emp_rec (Dept_num IN Emp_tab.Deptno%TYPE)
RETURN Emp_tab%ROWTYPE IS ...

Stored Program Units (Procedures )

Stored Program Units (Procedures )

A stored procedure, function, or package is a PL/SQL program unit that:

Has a name.
Can take parameters, and can return values.
Is stored in the data dictionary.
Can be called by many users.

Naming Procedures and Functions

Because a procedure or function is stored in the database, it must be named. This distinguishes it from other stored procedures and makes it possible for applications to call it. Each publicly-visible procedure or function in a schema must have a unique name, and the name must be a legal PL/SQL identifier.

Parameters for Procedures and Functions
Stored procedures and functions can take parameters. The following example shows a stored procedure that is similar to the anonymous block

Caution:
To execute the following, use CREATE OR REPLACE PROCEDURE...

PROCEDURE Get_emp_names (Dept_num IN NUMBER) IS
Emp_name VARCHAR2(10);
CURSOR c1 (Depno NUMBER) IS
SELECT Ename FROM Emp_tab
WHERE deptno = Depno;
BEGIN
OPEN c1(Dept_num);
LOOP
FETCH c1 INTO Emp_name;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Emp_name);
END LOOP;
CLOSE c1;
END;


In this stored procedure example, the department number is an input parameter which is used when the parameterized cursor c1 is opened

Sample Script 2 for Anonymous Blocks

Exceptions let you handle Oracle Database error conditions within PL/SQL program logic. This allows your application to prevent the server from issuing an error that could cause the client application to end. The following anonymous block handles the predefined Oracle Database exception NO_DATA_FOUND (which would result in an ORA-01403 error if not handled):

DECLARE
Emp_number INTEGER := 9999;
Emp_name VARCHAR2(10);
BEGIN
SELECT Ename INTO Emp_name FROM Emp_tab
WHERE Empno = Emp_number; -- no such number
DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number);
END;


You can also define your own exceptions, declare them in the declaration part of a block, and define them in the exception part of the block. An example follows:

DECLARE
Emp_name VARCHAR2(10);
Emp_number INTEGER;
Empno_out_of_range EXCEPTION;
BEGIN
Emp_number := 10001;
IF Emp_number > 9999 OR Emp_number < 1000 THEN
RAISE Empno_out_of_range;
ELSE
SELECT Ename INTO Emp_name FROM Emp_tab
WHERE Empno = Emp_number;
DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
END IF;
EXCEPTION
WHEN Empno_out_of_range THEN
DBMS_OUTPUT.PUT_LINE('Employee number ' || Emp_number ||
' is out of range.');
END;

Sample Script Anonymous Blocks In PL-SQL

Anonymous Blocks
An anonymous block is a PL/SQL program unit that has no name and it does not require the explicit presence of the BEGIN and END keywords to enclose the executable statements. An anonymous block consists of an optional declarative part, an executable part, and one or more optional exception handlers.

The declarative part declares PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks. Exception handlers contain code that is called when the exception is raised, either as a predefined PL/SQL exception (such as NO_DATA_FOUND or ZERO_DIVIDE) or as an exception that you define.

The following short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the Emp_tab table, using the DBMS_OUTPUT package:

DECLARE
Emp_name VARCHAR2(10);
Cursor c1 IS SELECT Ename FROM Emp_tab
WHERE Deptno = 20;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO Emp_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Emp_name);
END LOOP;
END;

Overview of PL/SQL Program Units

Overview of PL/SQL Program Units
PL/SQL is a modern, block-structured programming language. It provides several features that make developing powerful database applications very convenient. For example, PL/SQL provides procedural constructs, such as loops and conditional statements, that are not available in standard SQL.

You can directly enter SQL data manipulation language (DML) statements inside PL/SQL blocks, and you can use procedures supplied by Oracle to perform data definition language (DDL) statements.

PL/SQL code runs on the server, so using PL/SQL lets you centralize significant parts of your database applications for increased maintainability and security. It also enables you to achieve a significant reduction of network overhead in client/server applications.

PL/SQL program units include:

Anonymous Blocks
Stored Program Units (Procedures, Functions, and Packages)
Triggers

R12 Location Creation

DECLARE
l_location_rec hz_location_v2pub.location_rec_type;
l_location_id NUMBER;
l_addr_val_status VARCHAR2 (240);
l_addr_warn_msg VARCHAR2 (1000);
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_location_rec.country := 'US';
l_location_rec.address1 := 'Line 1';
l_location_rec.city := 'New Jersey';
l_location_rec.postal_code := 08830;
l_location_rec.state := 'NJ';
l_location_rec.created_by_module := 'HR API';
l_location_rec.county := 'Middlesex';
hz_location_v2pub.create_location
(p_init_msg_list => 'T',
p_location_rec => l_location_rec,
p_do_addr_val => 'Y',
x_location_id => l_location_id,
x_addr_val_status => l_addr_val_status,
x_addr_warn_msg => l_addr_warn_msg,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_addr_val_status ' || l_addr_val_status);
DBMS_OUTPUT.put_line ('l_addr_warn_msg ' || l_addr_warn_msg);
DBMS_OUTPUT.put_line ('l_location_id ' || l_location_id);
END IF;
END;

R12 Customer Account and Party Creation

DECLARE
l_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
l_organization_rec hz_party_v2pub.organization_rec_type;
l_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
l_party_id NUMBER;
l_party_number VARCHAR2 (240);
l_profile_id NUMBER;
l_cust_account_id NUMBER;
l_account_number VARCHAR2 (240);
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_cust_account_rec.created_by_module := 'TCA_MIGRATION';
l_organization_rec.organization_name := 'Test 2';
hz_cust_account_v2pub.create_cust_account
(p_init_msg_list => 'T',
p_cust_account_rec => l_cust_account_rec,
p_organization_rec => l_organization_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile_amt => 'T',
x_cust_account_id => l_cust_account_id,
x_account_number => l_account_number,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_profile_id => l_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_id ' || l_party_id);
DBMS_OUTPUT.put_line ('l_cust_account_id ' || l_cust_account_id);
DBMS_OUTPUT.put_line ('l_profile_id ' || l_profile_id);
END IF;
END;

R12 hz_party_v2pub.create_organization in Receivables

DECLARE
l_organization_rec hz_party_v2pub.organization_rec_type;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_party_id NUMBER;
l_party_number VARCHAR2 (240);
l_profile_id NUMBER;
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_organization_rec.organization_name := 'Test Org';
l_organization_rec.created_by_module := 'TCA_MIGRATION';
hz_party_v2pub.create_organization
(p_init_msg_list => 'T',
p_organization_rec => l_organization_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_profile_id => l_profile_id
);

IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) || ': ' || l_msg_data);
END LOOP;

DBMS_OUTPUT.put_line ('Error mesg' || l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_id ' || l_party_id);
DBMS_OUTPUT.put_line ('l_party_number ' || l_party_number);
DBMS_OUTPUT.put_line ('l_profile_id ' || l_profile_id);
END IF;
END;

R12 AR Credit Memo Creation Single Insert

INSERT INTO RA_INTERFACE_LINES_ALL
(batch_source_name,
line_type,
description,
conversion_type,
currency_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
amount, trx_date,
primary_salesrep_id,
cust_trx_type_id,
orig_system_bill_customer_id,
orig_system_bill_address_id,
orig_system_sold_customer_id,
conversion_rate,
uom_code,
line_number,
interface_line_attribute1,
interface_line_attribute2,
interface_line_context,
org_id,
reference_line_id,
reference_line_context,
gl_date
)
VALUES
('LEGACY', 'LINE', 'Maintenance', 'User',
'USD', 1012015, SYSDATE, 1012015,
SYSDATE, -1000, SYSDATE, 10068,
2, 1288,
1338, 1288,
1, 'Ea', 1,
'631152680', '1',
'VISION BUILD', 204, 806412,
'VISION BUILD', SYSDATE
);

R12 AR Invoice creation Script

INSERT INTO RA_INTERFACE_LINES_ALL
(batch_source_name,
line_type,
description,
conversion_type,
currency_code,
inventory_item_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
amount,
term_name,
term_id,
trx_date, primary_salesrep_id,
cust_trx_type_id,
orig_system_bill_customer_id,
orig_system_bill_address_id,
orig_system_sold_customer_id,
conversion_rate, uom_code,
line_number,
interface_line_attribute1,
interface_line_attribute2,
interface_line_context,
quantity,org_id
)
VALUES ('LEGACY', 'LINE', 'Maintenance', 'User',
'USD',
8451, 1012015,
SYSDATE,1012015,
SYSDATE,
10000, '30 NET', 4,
SYSDATE, 10068,
1,
1288,
1338,
1288,
1, 'Ea',
1,
'631152600',
'1',
'VISION BUILD',
2, 204
);

_ALL tables in Oracle Apps

_ALL tables in Oracle Apps?
_ALL tables in oracle applications give the info about multiple organizations info about these tables.

Difference between data conversion and data migration

What is the difference between data conversion and data migration?
Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to the target module using the standard import program.

Procedure,functions and Packages

Procedure,functions and Packages
* Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
* Procedures do not Return values while Functions return one One Value
*Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

Key Words Used in Oracle

Key Words Used in Oracle
The Key words that are used in Oracle are ::
a) Commiting :: A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transactino into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explcit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache :: Databese Buffer of SGA stores the most recently used blocks of datatbase data.The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechansim in Operating System that executes series of steps.

what is ADI and its features?

ADI means application desktop integrator. It is a excel file which allows you to transfer the data pertaining to General Ledger, Fixed Assets and Budget to oracle apps and allows to run a request. ADI functionality provides an alternative to users who prefer to load information directly from Microsoft Excel rather than using the Oracle user interface. It should read Oracle Interface Programs (batch jobs) rather than Oracle User Interfaces. Broadly following are the feature / elements of ADI
1. Journal Wizard
2. Budget Wizard
3. Report Wizard.
4. Account Hierarchy Editor.
5. Analysis Wizard.
6. Request Center
ADI allows users take advantage of many of the data-entry shortcuts of a spreadsheet, such as copying and pasting cells, dragging and dropping ranges of cells and using formulas to calculate journal line amounts. ADI validates the data entered against the accounts, security rules and reference information that are defined in the General Ledger (GL).

Script to load or convert Notes/Attachments associtaed with items

Script to load or convert Notes/Attachments associtaed with items

-- Script to load or convert Notes/Attachments associated with items.
DECLARE
l_doc_category_id NUMBER;
l_document_id NUMBER;
l_attached_document_id NUMBER;
ll_media_id NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
BEGIN
-- Select User_id
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE user_name ='XXX';
-- Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE name ='SHORT_TEXT';
-- Select Category id for "Vendor/To Supplier" Attachments
SELECT category_id
INTO l_doc_category_id
FROM apps.fnd_document_categories
WHERE name = 'Vendor';
-- Select nexvalues of document id, attached document id and
-- l_media_id
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL,
apps.fnd_documents_short_text_s.NEXTVAL
INTO l_document_id,
l_attached_document_id,
l_media_id
FROM DUAL;
INSERT INTO apps.fnd_documents
(document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
datatype_id,
category_id,
security_type,
security_id,
publish_flag,
usage_type
)
VALUES
(l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
l_short_datatype_id, -- Datatype for 'SHORT_TEXT'
l_doc_category_id, -- Category_id
1, -- 'Organization' Level Security
352, -- Organization id for Inventory Item Master Org
'Y', -- Publish_flag
'O' -- Usage_type of 'One Time'
);
INSERT INTO apps.fnd_documents_tl
(document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
language,
description,
media_id,
translated
)
VALUES
(l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
'AMERICAN', -- language
'EXTENDED DESCRIPTION', -- description
l_media_id, -- media_id
'Y' -- translated
);
INSERT INTO apps.fnd_attached_documents
(attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
seq_num,
entity_name,
pk1_value,
pk2_value,
automatically_added_flag
)
VALUES
(l_attached_document_id,
l_document_id,
SYSDATE,
lcl_fnd_user_id,
SYSDATE,
lcl_fnd_user_id,
20, -- Sequence Number of attachment.
'MTS_SYSTEM_ITEMS', -- Entity_name Table Name assoicated with attachment
352, -- Organization id for Inventory Item Master Org
567, -- Inventory Item Id
'N' -- Automatically_added_flag
);

INSERT INTO apps.fnd_documents_short_text
(media_id,
short_text
)
VALUES
(lcl_media_id,
'Write your Short Text Here' -- Notes/Attachments text
);
COMMIT;
END;
/

AutoInvoice Overview in Receivables

AutoInvoice is a program that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data. This fits well with in Oracle ERP or to integrate with any third party application.

Top 10 reasons for using Auto Invoice

1. Powerful Interface Tool
2. Supports Oracle & Non-Oracle Systems
3. Import Large Amount of Data
4. Calculate or Import Tax
5. Group Lines & Invoices
6. Online Error Correction
7 .Lines Validation
8. Derive GL Date
9 .Import Flex fields
10.Import or Derive Accounting Info

What is inside AutoInvoice
AutoInvoice is a program set consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.
Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)

1. Auto Invoice Master program RAXMTR
Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.
•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
•Marks interface records for processing by marking request_id
•Submits multiple workers for Parallel Processing by creating instances for request.

2. Auto Invoice Import Program
Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.Working of Auto invoice , Validates data, Inserts records, Deletes interface data Only when system option purge set to ‘Y’

3. Auto Invoice Purge Program
Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.Deletes all rows where interface_status =‘P’
• Ra_interface_lines
• Ra_interface_distributions
• Ra_interface_salescredits

Oracle Receivable’s Auto Invoice program will be used to import and validate Invoices. A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table. When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report. Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

How Autoinvoice Execution works
Normally, Auto Invoice can be divided into three major phases, Pre-grouping: here the validates all of the line level data takes place, Grouping: groups lines based on the grouping rules and validates header level data, Transfer :validates information that exists in Receivables tables

What happen when AutoInvoice run?
Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.Line, accounting, and sales credit information for each line populates 3 interface tables
Lines are ordered and grouped
Tax is calculated
GL date is determined
GL accounts are assigned using Auto Accounting
Tax, freight, commitments, and credit memos are linked to transaction lines
All transactions are batched
Validated lines are used to create the transaction

How Data is flowing?
Select, insert and update and delete take place on certain tables once it is logged out.
Selects
– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL

Updates/Insert
– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL

Inserts
– RA_INTERFACE_ERRORS_ALL

AutoInvoice Exception Handling
Records that fail validation are called ‘Exceptions’. Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL. Errors can be corrected in the Exception Handling window. Once corrections are made, Auto invoice must be resubmitted. Records that pass validation get transferred to Receivables tables

AutoInvoice Exception Handling Windows
-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to

AR Adjustments Explanation

An Adjustment in AR is an amount added or subtracted from the amount due of an Invoice, Debit Memo, Chargeback, Deposit, or Guarantee.

Oracle AR lets you create Automatic or Manual Adjustments.

Receivables lets you make either Positive or Negative Adjustments to your Invoices, Debit Memos, Chargebacks, On–account Credits, Deposits, and Guarantees.

You can approve Adjustments that are within your approval limits and give pending statuses to Adjustments that are outside your approval limits.

You can also automatically write off debit items that meet your selection criteria.

Business need for Adjustment

Its very similar to write-off. If there is any unapplied balance in invoice after application of receipt in small value for rounding off purpose , that will be adjusted while making receipt application.

Or sometime, when Invoice amount is wrongly entered you can also add a line by adding or reducing amount.

These are very common across all industry.Telecom, Health care and retail sectors the need is very high for such functionality.

In R12 what is MO_GLOBAL.INIT

Purpose of mo_global.init :-

It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used.
If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp

When & from where is mo_global.init called ?

This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like
FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE

From SQL*Plus, you can simulate login to a specific responsibility by calling
a.Call FND_GLOBAL.INITIALIZE
This will set your responsibility id, user_id etc

b. call MO_GLOBAL.INIT
This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.

Introduction to LINUX

Introduction to LINUX

Typical Installation of Linux is as simple as installing windows operating systems. Custom installation requires some expertise in understanding the Linux directory structure. Linux always takes the unformatted partition to load operating system

Directory structure
/
root bin etc usr home dev lib boot .. d01

‘/’ is the starting point, under this various default, user-defined subdirectories like d01 in which oracle applications is created

Logging to Linux server using telnet will take the user to user’s home directory.Eg. For user1 the home directory is /home/user1

Using Telnet

Telnet provides user support for the Telnet protocol, a remote access protocol you can use to log on to a remote computer , network device, or private TCP/IP network.
• To open Telnet, click Start, click Run, and then type telnet. You can also type telnet at the
command prompt.
• Using Telnet, you can make a remote connection.
• To use Telnet, you must have the TCP/IP protocol installed and configured on your computer and you must have a user account established on a remote host.
• To display help for Telnet, type help at the command prompt.

Linux Commands

ls - list directory contents

ls [OPTION]... [FILE]...
-a displays all the files including starting with .
-A displays all the files excluding implicit “.” “..”
-t sort by modification time
-r reverse order
-R recursively displays the files , subdirectories and it’s contents
-s print the size of each file, in blocks
-S sort by file size
-l displays long format

example: when you use ls -ls
4 -rw-r--r-- 1 sdaggupa users 803 Apr 1 10:10 test.c
4 drwxr-xr-x 3 sdaggupa users 4096 Apr 7 11:00 conv
[number of operating system blocks; permissions (d- directory,- file); unknown; username; usergroup; size in bytes; month; date; time; file or directory name]

clear – Clears the screen

more -

mkdir - Create the DIRECTORY(ies), if they do not already exist.
mkdir [OPTION] DIRECTORY...
-m, --mode=MODE set permission mode (as in chmod), not rwxrwxrwx - umask
-p, --parents no error if existing, make parent directories as needed

cd - change directory
cd [ or or ] relative path : path from the working directory absolute path : complete path from “/” virtual path : path defined in the current environment

cd -changes to user’s home directory

cp - copy files and directories

cp [OPTION]... SOURCE DEST
cp [OPTION]... SOURCE... DIRECTORY

OPTION -R copies the source (including subdirectories and its contents) recursively.
-u copy only when the file is newer than the destination, and when destination file is missing.

mv - move (rename) files
mv [OPTION]... SOURCE DEST
mv [OPTION]... SOURCE... DIRECTORY
Rename SOURCE to DEST, or move SOURCE(s) to DIRECTORY.
-f, --force never prompt before overwriting
-i, --interactive prompt before overwriteNote: Practice well before using otherwise you may not get back your file some times

rm - remove files or directories

rm [OPTION]... FILE...
This manual page documents the GNU version of rm. rm removes each specified file. By default, it does not remove directories.
-f, --force ignore nonexistent files, never prompt
-i, --interactive prompt before any removal
-r, -R, --recursive remove the contents of directories recursively

rmdir - remove empty directory

chmod - change file access permissions
chmod [OPTION]... MODE[,MODE]... FILE...
-R to change the mode recursively if it is directory

ex: $chmod 755 test.cafter executing the above command test.c will have rwx permissions for the user, r-x permissions for group, r-x permissions for Other users

chgrp - chgrp - change group ownership
chgrp [OPTION]... GROUP FILE...
-R to change the group ownership recursively if it is Directory