Below is the Pricing Setup flow checklist to be performed during implementation :
Tuesday, August 26, 2014
Friday, August 22, 2014
Ref Cursor in Oracle PL/SQL
A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time.
Let us start with a small sub-program as follows:
%ROWTYPE with REF CURSOR
In the previous section, I retrieved only one column (ename) of information using REF CURSOR. Now I would like to retrieve more than one column (or entire row) of information using the same. Let us consider the following example:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
er emp%rowtype;
begin
open c_emp for select * from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.ename || ' - ' || er.sal);
end loop;
close c_emp;
end;
----------------------------------------------------------------------------------------------------
As defined earlier, a REF CURSOR can be associated with more than one SELECT statement at run-time. Before associating a new SELECT statement, we need to close the CURSOR. Let us have an example as follows:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
begin
open c_emp for select ename,sal from emp where deptno = 10;
dbms_output.put_line('Department: 10');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
open c_emp for select ename,sal from emp where deptno = 20;
dbms_output.put_line('Department: 20');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
end;
-----------------------------------------------------------------------------------------------------------
Let us start with a small sub-program as follows:
%ROWTYPE with REF CURSOR
In the previous section, I retrieved only one column (ename) of information using REF CURSOR. Now I would like to retrieve more than one column (or entire row) of information using the same. Let us consider the following example:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
er emp%rowtype;
begin
open c_emp for select * from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.ename || ' - ' || er.sal);
end loop;
close c_emp;
end;
----------------------------------------------------------------------------------------------------
As defined earlier, a REF CURSOR can be associated with more than one SELECT statement at run-time. Before associating a new SELECT statement, we need to close the CURSOR. Let us have an example as follows:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
begin
open c_emp for select ename,sal from emp where deptno = 10;
dbms_output.put_line('Department: 10');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
open c_emp for select ename,sal from emp where deptno = 20;
dbms_output.put_line('Department: 20');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
end;
-----------------------------------------------------------------------------------------------------------
Wednesday, August 20, 2014
Nested Table in PL/SQL
Nested Table:
Nested table is a collection of rows, represented as a column within the main table. For each record within the main table, the nested table may contain multiple rows. In one sense, it's a way of storing a one-to-many relationship within one table, Consider a table that contains information about departments, each of which may have many projects in progress at any one time. In a strictly relational model, you would create two separate tables - DEPT and PROJECT.
nested table allow you to store the information about projects within the DEPT table. The PROJECT table records can be accessed directly via the DEPT table, without the need to perform a join. The ability to select the data without traversing joins may make data easier to access for users. Even if you do not define methods for accessing the nested data, you have clearly associated the department and project data. In a strictly relational model, the association between the DEPT and PROJECT tables would be accomplished via foreign key relationships.
SQL> create or replace type emp_ty as object
2 (desg varchar2(10),
3 dname varchar2(10),
4 doj date);
5 /
Type created.
SQL> create type emp_nt as table of emp_ty;
2 /
Type created.
SQL> create table empdata
2 (ename varchar2(10),
3 details emp_nt)
4 nested table details store as emp_nt_tab;
Table created.
SQL> desc empdata
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
DETAILS EMP_NT
SQL> set describe depth 2
SQL> desc empdata
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
DETAILS EMP_NT
DESG VARCHAR2(10)
DNAME VARCHAR2(10)
DOJ DATE
SQL> ed
Wrote file afiedt.buf
1 insert into empdata values (
2 'Raju',
3 emp_nt(
4 emp_ty('Clerk','Sales','12-Sep-05'),
5 emp_ty('Asst','Mrkt','15-Oct-04'),
6* emp_ty('Mngr','Sales','13-Aug-05')))
SQL> /
1 row created.
SQL> select * from empdata;
ENAME
----------
DETAILS(DESG, DNAME, DOJ)
--------------------------------------------------------------------------------
Raju
EMP_NT(EMP_TY('Clerk', 'Sales', '12-SEP-05'), EMP_TY('Asst', 'Mrkt', '15-OCT-04'
), EMP_TY('Mngr', 'Sales', '13-AUG-05'))
SQL> select ename,n.desg,n.doj from empdata,table(empdata.details) n;
ENAME DESG DOJ
---------- ---------- ---------
Raju Clerk 12-SEP-05
Raju Asst 15-OCT-04
Raju Mngr 13-AUG-05
Nested table is a collection of rows, represented as a column within the main table. For each record within the main table, the nested table may contain multiple rows. In one sense, it's a way of storing a one-to-many relationship within one table, Consider a table that contains information about departments, each of which may have many projects in progress at any one time. In a strictly relational model, you would create two separate tables - DEPT and PROJECT.
nested table allow you to store the information about projects within the DEPT table. The PROJECT table records can be accessed directly via the DEPT table, without the need to perform a join. The ability to select the data without traversing joins may make data easier to access for users. Even if you do not define methods for accessing the nested data, you have clearly associated the department and project data. In a strictly relational model, the association between the DEPT and PROJECT tables would be accomplished via foreign key relationships.
SQL> create or replace type emp_ty as object
2 (desg varchar2(10),
3 dname varchar2(10),
4 doj date);
5 /
Type created.
SQL> create type emp_nt as table of emp_ty;
2 /
Type created.
SQL> create table empdata
2 (ename varchar2(10),
3 details emp_nt)
4 nested table details store as emp_nt_tab;
Table created.
SQL> desc empdata
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
DETAILS EMP_NT
SQL> set describe depth 2
SQL> desc empdata
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
DETAILS EMP_NT
DESG VARCHAR2(10)
DNAME VARCHAR2(10)
DOJ DATE
SQL> ed
Wrote file afiedt.buf
1 insert into empdata values (
2 'Raju',
3 emp_nt(
4 emp_ty('Clerk','Sales','12-Sep-05'),
5 emp_ty('Asst','Mrkt','15-Oct-04'),
6* emp_ty('Mngr','Sales','13-Aug-05')))
SQL> /
1 row created.
SQL> select * from empdata;
ENAME
----------
DETAILS(DESG, DNAME, DOJ)
--------------------------------------------------------------------------------
Raju
EMP_NT(EMP_TY('Clerk', 'Sales', '12-SEP-05'), EMP_TY('Asst', 'Mrkt', '15-OCT-04'
), EMP_TY('Mngr', 'Sales', '13-AUG-05'))
SQL> select ename,n.desg,n.doj from empdata,table(empdata.details) n;
ENAME DESG DOJ
---------- ---------- ---------
Raju Clerk 12-SEP-05
Raju Asst 15-OCT-04
Raju Mngr 13-AUG-05
Merge, Exists and Non-Exists in PL/SQL
Exists:
-------------
The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
Ex:
---
SELECT *
FROM dept
WHERE EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
--------------------------------------
Not Exists:
SELECT *
FROM dept
WHERE NOT EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
-------------------------------------
Merge:
create table student10 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student10 values ( 101, 'arun',30);
insert into student10 values ( 102, 'anil',40);
insert into student10 values ( 103, 'kiran',50);
create table student20 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student20 values ( 101, 'JOHN',30);
insert into student20 values ( 105, 'SMITH',50);
merge into student10 s1
using student20 s2
on ( s1.sno = s2.sno)
when matched
then update set sname=s2.sname, marks = s2.marks
when not matched
then insert (sno,sname,marks ) values (s2.sno,s2.sname,s2.marks);
-------------
The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
Ex:
---
SELECT *
FROM dept
WHERE EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
--------------------------------------
Not Exists:
SELECT *
FROM dept
WHERE NOT EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
-------------------------------------
Merge:
create table student10 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student10 values ( 101, 'arun',30);
insert into student10 values ( 102, 'anil',40);
insert into student10 values ( 103, 'kiran',50);
create table student20 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student20 values ( 101, 'JOHN',30);
insert into student20 values ( 105, 'SMITH',50);
merge into student10 s1
using student20 s2
on ( s1.sno = s2.sno)
when matched
then update set sname=s2.sname, marks = s2.marks
when not matched
then insert (sno,sname,marks ) values (s2.sno,s2.sname,s2.marks);
Tuesday, August 19, 2014
Where Current of : PL/SQL
WHERE CURRENT OF clause is used in some UPDATE statements.
The WHERE CURRENT OF clause is an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated.
We must declare the cursor with FOR UPDATE clause to use this feature.
When the session opens the cursor with the FOR UPDATE clause, all rows in the return set will hold row-level locks. Other sessions can only query for the rows, but cannot update or delete.
declare
cursor c1
is
select empno,ename,sal from emp
where comm is null
for update of comm;
var_comm number(4);
begin
for cur_rec in c1 loop
if cur_rec.sal < 2000 then
var_comm:=200;
elsif cur_rec.sal <4000 then
var_comm:=400;
else
var_comm:=100;
end if;
update emp set comm=var_comm
where current of c1;
end loop;
end;
/
The WHERE CURRENT OF clause is an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated.
We must declare the cursor with FOR UPDATE clause to use this feature.
When the session opens the cursor with the FOR UPDATE clause, all rows in the return set will hold row-level locks. Other sessions can only query for the rows, but cannot update or delete.
declare
cursor c1
is
select empno,ename,sal from emp
where comm is null
for update of comm;
var_comm number(4);
begin
for cur_rec in c1 loop
if cur_rec.sal < 2000 then
var_comm:=200;
elsif cur_rec.sal <4000 then
var_comm:=400;
else
var_comm:=100;
end if;
update emp set comm=var_comm
where current of c1;
end loop;
end;
/
Rank Function in PL/SQL
Rank :
In Oracle/PLSQL, the rank function returns the rank of a value in a group of values.
The rank function can be used two ways - as an Aggregate function or as an Analytic function.
---
Rank used as aggregate function:
select rank (800) within group (order by sal) rank from emp;
select rank (850) within group (order by sal) rank from emp;
select rank (5000) within group (order by sal) rank from emp;
Rank used as analytical function:
As an Analytic function, the rank returns the rank of each row of a query
select ename, sal,
rank() OVER (ORDER BY sal) rank
from emp;
Friday, August 8, 2014
Create Person Party and Org Contact for a Organization Pary in Oracle R12
--It is always a good idea to initialize apps and set global policy context before using standard API
CREATE OR REPLACE PROCEDURE create_person_party (
p_in_first_name IN VARCHAR2,
p_in_org_party_id IN VARCHAR2,
p_in_party_number IN VARCHAR2,
p_in_cdh_person_party_number IN VARCHAR2,
x_ou_ebs_per_party_id OUT VARCHAR2,
x_ou_org_contact_id OUT VARCHAR2
)
IS
v_person_rec hz_party_v2pub.person_rec_type;
v_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
x_ou_party_rel_id NUMBER;
x_ou_party_id NUMBER;
x_ou_party_number VARCHAR2 (2000);
x_ou_profile_id NUMBER;
x_ou_return_status VARCHAR2 (2000);
x_ou_msg_count NUMBER;
x_ou_msg_data VARCHAR2 (2000);
v_message VARCHAR2 (5000);
v_err_message VARCHAR2 (5000);
v_ebs_per_party_id NUMBER := NULL; --Party ID of the relationship
v_org_contact_id NUMBER := NULL;
BEGIN
fnd_msg_pub.delete_msg (NULL);
fnd_msg_pub.initialize;
v_person_rec.person_first_name := p_in_first_name;
v_person_rec.created_by_module := 'TCA_V2_API';
hz_party_v2pub.create_person (p_init_msg_list => 'T',
p_person_rec => v_person_rec,
x_party_id => x_ou_party_id,
x_party_number => x_ou_party_number,
x_profile_id => x_ou_profile_id,
x_return_status => x_ou_return_status,
x_msg_count => x_ou_msg_count,
x_msg_data => x_ou_msg_data
);
IF x_ou_return_status <> 'S'
THEN
FOR j IN 1 .. x_ou_msg_count
LOOP
fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
v_err_message :=
v_err_message
|| ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('v_err_message: ' || v_err_message);
ELSE
v_ebs_per_party_id := x_ou_party_id;
v_org_contact_rec.created_by_module := 'TCA_V2_API';
v_org_contact_rec.party_rel_rec.subject_id := v_ebs_per_party_id;
v_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
v_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.object_id := p_in_org_party_id;
v_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
v_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
v_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
v_org_contact_rec.party_rel_rec.start_date := SYSDATE;
v_org_contact_rec.party_rel_rec.status := 'A';
hz_party_contact_v2pub.create_org_contact ('T',
v_org_contact_rec,
x_ou_org_contact_id,
x_ou_party_rel_id,
x_ou_party_id,
x_ou_party_number,
x_ou_return_status,
x_ou_msg_count,
x_ou_msg_data
);
COMMIT;
IF x_ou_return_status <> 'S'
THEN
FOR j IN 1 .. x_ou_msg_count
LOOP
fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
v_err_message :=
v_err_message
|| ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('v_err_message: ' || v_err_message);
ELSE
DBMS_OUTPUT.put_line ('Org Contact ID = ' || x_ou_org_contact_id);
DBMS_OUTPUT.put_line (' Party Relationship id = ' || x_ou_party_id);
v_ebs_per_party_id := x_ou_party_id;
v_org_contact_id := x_ou_org_contact_id;
END IF;
END IF;
x_ou_org_contact_id := v_org_contact_id;
x_ou_ebs_per_party_id := v_ebs_per_party_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END create_person_party;
CREATE OR REPLACE PROCEDURE create_person_party (
p_in_first_name IN VARCHAR2,
p_in_org_party_id IN VARCHAR2,
p_in_party_number IN VARCHAR2,
p_in_cdh_person_party_number IN VARCHAR2,
x_ou_ebs_per_party_id OUT VARCHAR2,
x_ou_org_contact_id OUT VARCHAR2
)
IS
v_person_rec hz_party_v2pub.person_rec_type;
v_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
x_ou_party_rel_id NUMBER;
x_ou_party_id NUMBER;
x_ou_party_number VARCHAR2 (2000);
x_ou_profile_id NUMBER;
x_ou_return_status VARCHAR2 (2000);
x_ou_msg_count NUMBER;
x_ou_msg_data VARCHAR2 (2000);
v_message VARCHAR2 (5000);
v_err_message VARCHAR2 (5000);
v_ebs_per_party_id NUMBER := NULL; --Party ID of the relationship
v_org_contact_id NUMBER := NULL;
BEGIN
fnd_msg_pub.delete_msg (NULL);
fnd_msg_pub.initialize;
v_person_rec.person_first_name := p_in_first_name;
v_person_rec.created_by_module := 'TCA_V2_API';
hz_party_v2pub.create_person (p_init_msg_list => 'T',
p_person_rec => v_person_rec,
x_party_id => x_ou_party_id,
x_party_number => x_ou_party_number,
x_profile_id => x_ou_profile_id,
x_return_status => x_ou_return_status,
x_msg_count => x_ou_msg_count,
x_msg_data => x_ou_msg_data
);
IF x_ou_return_status <> 'S'
THEN
FOR j IN 1 .. x_ou_msg_count
LOOP
fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
v_err_message :=
v_err_message
|| ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('v_err_message: ' || v_err_message);
ELSE
v_ebs_per_party_id := x_ou_party_id;
v_org_contact_rec.created_by_module := 'TCA_V2_API';
v_org_contact_rec.party_rel_rec.subject_id := v_ebs_per_party_id;
v_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
v_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.object_id := p_in_org_party_id;
v_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
v_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
v_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
v_org_contact_rec.party_rel_rec.start_date := SYSDATE;
v_org_contact_rec.party_rel_rec.status := 'A';
hz_party_contact_v2pub.create_org_contact ('T',
v_org_contact_rec,
x_ou_org_contact_id,
x_ou_party_rel_id,
x_ou_party_id,
x_ou_party_number,
x_ou_return_status,
x_ou_msg_count,
x_ou_msg_data
);
COMMIT;
IF x_ou_return_status <> 'S'
THEN
FOR j IN 1 .. x_ou_msg_count
LOOP
fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
v_err_message :=
v_err_message
|| ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('v_err_message: ' || v_err_message);
ELSE
DBMS_OUTPUT.put_line ('Org Contact ID = ' || x_ou_org_contact_id);
DBMS_OUTPUT.put_line (' Party Relationship id = ' || x_ou_party_id);
v_ebs_per_party_id := x_ou_party_id;
v_org_contact_id := x_ou_org_contact_id;
END IF;
END IF;
x_ou_org_contact_id := v_org_contact_id;
x_ou_ebs_per_party_id := v_ebs_per_party_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END create_person_party;
Thursday, August 7, 2014
Example : get_service_duration API in Oracle R12 Order Management
--It is always a good practice to initialize apps and set global policy context
PROCEDURE get_service_duration (
p_serv_period IN VARCHAR2,
p_end_date IN DATE,
p_start_date IN DATE,
p_serv_duration IN NUMBER,
p_out_serv_duration OUT NOCOPY NUMBER,
p_out_service_period OUT NOCOPY VARCHAR2,
p_out_end_date OUT NOCOPY VARCHAR2,
p_out_dur_return_sts OUT NOCOPY VARCHAR2
)
IS
v_ou_dur_return_sts VARCHAR2 (1) := fnd_api.g_ret_sts_success;
v_dur_line_rec oe_oe_form_line.line_rec_type;
v_dur_null_line_rec oe_oe_form_line.line_rec_type;
v_dur_line_rec oe_oe_form_line.line_rec_type;
BEGIN
v_dur_line_rec := v_dur_null_line_rec;
v_dur_line_rec.service_duration := p_serv_duration;
v_dur_line_rec.service_start_date := p_start_date;
v_dur_line_rec.service_period := p_serv_period;
v_dur_line_rec.service_end_date := p_end_date;
oe_service_util.get_service_duration (x_return_status => v_dur_return_sts,
p_line_rec => v_dur_line_rec,
x_line_rec => v_dur_line_rec
);
p_out_service_period := v_dur_line_rec.service_period;
p_out_serv_duration := v_dur_line_rec.service_duration;
p_out_dur_return_sts := v_dur_return_sts;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END get_service_duration;
PROCEDURE get_service_duration (
p_serv_period IN VARCHAR2,
p_end_date IN DATE,
p_start_date IN DATE,
p_serv_duration IN NUMBER,
p_out_serv_duration OUT NOCOPY NUMBER,
p_out_service_period OUT NOCOPY VARCHAR2,
p_out_end_date OUT NOCOPY VARCHAR2,
p_out_dur_return_sts OUT NOCOPY VARCHAR2
)
IS
v_ou_dur_return_sts VARCHAR2 (1) := fnd_api.g_ret_sts_success;
v_dur_line_rec oe_oe_form_line.line_rec_type;
v_dur_null_line_rec oe_oe_form_line.line_rec_type;
v_dur_line_rec oe_oe_form_line.line_rec_type;
BEGIN
v_dur_line_rec := v_dur_null_line_rec;
v_dur_line_rec.service_duration := p_serv_duration;
v_dur_line_rec.service_start_date := p_start_date;
v_dur_line_rec.service_period := p_serv_period;
v_dur_line_rec.service_end_date := p_end_date;
oe_service_util.get_service_duration (x_return_status => v_dur_return_sts,
p_line_rec => v_dur_line_rec,
x_line_rec => v_dur_line_rec
);
p_out_service_period := v_dur_line_rec.service_period;
p_out_serv_duration := v_dur_line_rec.service_duration;
p_out_dur_return_sts := v_dur_return_sts;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END get_service_duration;
Tuesday, August 5, 2014
API to create Contact Point ID for a Party in Oracle R12 Example
-- Make Sure you initialize apps and set mo_global.set_policy_context
PROCEDURE create_contact_point_id (
p_in_contact_point_type IN VARCHAR2,
p_in_contact_point IN VARCHAR2,
p_in_party_id IN VARCHAR2,
x_out_cont_point_id OUT VARCHAR2
)
IS
v_cpr hz_contact_point_v2pub.contact_point_rec_type;
v_edi_rec hz_contact_point_v2pub.edi_rec_type;
v_email_rec hz_contact_point_v2pub.email_rec_type;
v_phone_rec hz_contact_point_v2pub.phone_rec_type;
v_telex_rec hz_contact_point_v2pub.telex_rec_type;
v_web_rec hz_contact_point_v2pub.web_rec_type;
x_ou_return_status VARCHAR2 (2000);
x_ou_msg_count NUMBER;
x_ou_contact_point_id NUMBER;
x_ou_msg_data VARCHAR2 (2000);
x_ou_output_message VARCHAR2 (2000) := ' ';
v_message VARCHAR2 (5000);
v_err_message VARCHAR2 (5000);
BEGIN
IF p_in_contact_point_type = 'PHONE'
THEN
v_phone_rec.phone_number := p_in_contact_point;
v_phone_rec.phone_line_type := 'GEN';
ELSIF p_in_contact_point_type = 'EMAIL'
THEN
v_email_rec.email_format := 'MAILTEXT';
v_email_rec.email_address := p_in_contact_point;
END IF;
/* build contact point rec */
v_cpr.contact_point_id := NULL;
v_cpr.contact_point_type := p_in_contact_point_type;
v_cpr.status := 'A';
v_cpr.owner_table_name := 'HZ_PARTIES';
v_cpr.owner_table_id := p_in_party_id;
v_cpr.primary_flag := 'N';
v_cpr.created_by_module := 'TCA_V2_API';
/* call api to create rec */
IF p_in_contact_point IS NOT NULL
THEN
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => fnd_api.g_true,
p_contact_point_rec => v_cpr,
p_edi_rec => v_edi_rec,
p_email_rec => v_email_rec,
p_phone_rec => v_phone_rec,
p_telex_rec => v_telex_rec,
p_web_rec => v_web_rec,
x_contact_point_id => x_ou_contact_point_id,
x_return_status => x_ou_return_status,
x_msg_count => x_ou_msg_count,
x_msg_data => x_ou_msg_data
);
COMMIT;
dbms_output.put_line (x_ou_msg_data);
dbms_output.put_line (x_ou_return_status);
dbms_output.put_line (x_ou_msg_count);
IF x_ou_return_status <> 'S'
THEN
FOR j IN 1 .. x_ou_msg_count
LOOP
fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
v_err_message :=
v_err_message
|| ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
END LOOP;
dbms_output.put_line ('v_err_message: ' || v_err_message);
ELSE
x_out_cont_point_id := x_ou_contact_point_id;
END IF;
ELSE
dbms_output.put_line ('No Valid Value for Contact Point Creation: ');
END IF;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line (SQLERRM);
END create_contact_point_id;
PROCEDURE create_contact_point_id (
p_in_contact_point_type IN VARCHAR2,
p_in_contact_point IN VARCHAR2,
p_in_party_id IN VARCHAR2,
x_out_cont_point_id OUT VARCHAR2
)
IS
v_cpr hz_contact_point_v2pub.contact_point_rec_type;
v_edi_rec hz_contact_point_v2pub.edi_rec_type;
v_email_rec hz_contact_point_v2pub.email_rec_type;
v_phone_rec hz_contact_point_v2pub.phone_rec_type;
v_telex_rec hz_contact_point_v2pub.telex_rec_type;
v_web_rec hz_contact_point_v2pub.web_rec_type;
x_ou_return_status VARCHAR2 (2000);
x_ou_msg_count NUMBER;
x_ou_contact_point_id NUMBER;
x_ou_msg_data VARCHAR2 (2000);
x_ou_output_message VARCHAR2 (2000) := ' ';
v_message VARCHAR2 (5000);
v_err_message VARCHAR2 (5000);
BEGIN
IF p_in_contact_point_type = 'PHONE'
THEN
v_phone_rec.phone_number := p_in_contact_point;
v_phone_rec.phone_line_type := 'GEN';
ELSIF p_in_contact_point_type = 'EMAIL'
THEN
v_email_rec.email_format := 'MAILTEXT';
v_email_rec.email_address := p_in_contact_point;
END IF;
/* build contact point rec */
v_cpr.contact_point_id := NULL;
v_cpr.contact_point_type := p_in_contact_point_type;
v_cpr.status := 'A';
v_cpr.owner_table_name := 'HZ_PARTIES';
v_cpr.owner_table_id := p_in_party_id;
v_cpr.primary_flag := 'N';
v_cpr.created_by_module := 'TCA_V2_API';
/* call api to create rec */
IF p_in_contact_point IS NOT NULL
THEN
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => fnd_api.g_true,
p_contact_point_rec => v_cpr,
p_edi_rec => v_edi_rec,
p_email_rec => v_email_rec,
p_phone_rec => v_phone_rec,
p_telex_rec => v_telex_rec,
p_web_rec => v_web_rec,
x_contact_point_id => x_ou_contact_point_id,
x_return_status => x_ou_return_status,
x_msg_count => x_ou_msg_count,
x_msg_data => x_ou_msg_data
);
COMMIT;
dbms_output.put_line (x_ou_msg_data);
dbms_output.put_line (x_ou_return_status);
dbms_output.put_line (x_ou_msg_count);
IF x_ou_return_status <> 'S'
THEN
FOR j IN 1 .. x_ou_msg_count
LOOP
fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
v_err_message :=
v_err_message
|| ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
END LOOP;
dbms_output.put_line ('v_err_message: ' || v_err_message);
ELSE
x_out_cont_point_id := x_ou_contact_point_id;
END IF;
ELSE
dbms_output.put_line ('No Valid Value for Contact Point Creation: ');
END IF;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line (SQLERRM);
END create_contact_point_id;
Monday, August 4, 2014
API to Reprice Order Total in Oracle R12.
-- Make Sure you initialize apps and set mo_global.set_policy_context
PROCEDURE reprice_order (p_in_header_id IN NUMBER)
IS
v_header_count NUMBER;
v_header_list VARCHAR2 (32000);
v_line_count NUMBER;
v_line_list VARCHAR2 (32000);
v_price_level VARCHAR2 (32000);
v_return_status VARCHAR2 (32000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (32000);
BEGIN
v_header_count := 1;
v_header_list := TO_CHAR (p_in_header_id);
v_price_level := 'ORDER';
BEGIN
oe_order_adj_pvt.price_action (p_header_count => v_header_count,
p_header_list => v_header_list,
p_line_count => v_line_count,
p_line_list => v_line_list,
p_price_level => v_price_level,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
COMMIT;
IF (v_return_status != fnd_api.g_ret_sts_success)
THEN
dbms_output.put_line(v_msg_data);
END IF;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(sqlerrm);
END;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(sqlerrm);
END reprice_order;
PROCEDURE reprice_order (p_in_header_id IN NUMBER)
IS
v_header_count NUMBER;
v_header_list VARCHAR2 (32000);
v_line_count NUMBER;
v_line_list VARCHAR2 (32000);
v_price_level VARCHAR2 (32000);
v_return_status VARCHAR2 (32000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (32000);
BEGIN
v_header_count := 1;
v_header_list := TO_CHAR (p_in_header_id);
v_price_level := 'ORDER';
BEGIN
oe_order_adj_pvt.price_action (p_header_count => v_header_count,
p_header_list => v_header_list,
p_line_count => v_line_count,
p_line_list => v_line_list,
p_price_level => v_price_level,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
COMMIT;
IF (v_return_status != fnd_api.g_ret_sts_success)
THEN
dbms_output.put_line(v_msg_data);
END IF;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(sqlerrm);
END;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(sqlerrm);
END reprice_order;
Subscribe to:
Posts (Atom)
