Oracle Pricing Setup : Modifiers In Oracle Apps

Modifiers enable you to set up discounts,surcharges,free goods,coupons,freight and special charges that can be applied immediately to pricing requests or accrued for later disbursement. You can setup advanced deals and promotions as well

The following list describes the main modifier concepts and related entities:
• Modifier lists contain one or more modifiers. Modifiers have list level and line level components. Each list level must have one or more lines associated with it.
• By defining qualifiers at the list and line levels, you define a customer's eligibility for the modifier.
• Control features that influence modifiers are products and product groups, pricing attributes, phases, incompatibility groups, levels, and buckets.
• The use of modifiers can result in volume breaks, additional buy/get products, and benefits.


Go to : Oracle Pricing Manager Responsibility 

Navigate to Modifier Setup 

Populate all Mandatory Fields

Mandatory Fields are below :

• Type : Deal, Discount List, Freight, Promotion and Surcharge List
• Name : Unique Modifier Identifier
• Number : Unique Modifier Identifier
• Modifier Number : Unique Modifier Identifier
• Level  : Order/Line
• Modifier Type : Discount/Surcharge
• Pricing Phase :  Header or Line Adjustment
• Application Method : This Can be Percent, Lump sum

Read More

Oracle Pricing Setup : Understanding Price Lists

Price List can be imagined as a table of Item vs Item Price. Every Item must have a price. Items are integral part of Inventory and Order Management.

Price lists consist of a header region that defines general information such as effective dates and currency, and price list lines that define item and/or item category prices.

Below are the attributes of Price List :

Read More

Oracle Pricing Setup : Flow for Oracle Advanced Pricing

Below is the Pricing Setup flow checklist to be performed during implementation :

Read More

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;

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

Read More

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

Read More

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);

Read More

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;
/

Read More

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;

Read More

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;

Read More

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;

Read More