difference between DECODE and CASE

 Some differences between CASE and DECODE:

1. CASE is a statement while DECODE is a function.

2. CASE can work with logical operators other than "=".

3. DECODE performs an equality check only. CASE is capable of other logical comparisons such as <,>, BETWEEN, LIKE etc.

SELECT ename, empno, 

DECODE( deptno, 10, 'Accounting'

, 20, 'Research'

, 30, 'Sales'

, 40, 'Operations'

, 'Unknown'

) Department

FROM emp

ORDER BY ename;

SELECT 

(CASE 

WHEN sal <1000 THEN 'Low'

WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'

WHEN sal > 3000 THEN 'High'

ELSE 'N/A'

END ) Salary

FROM emp

ORDER BY ename;

4. CASE can work with predicates and searchable queries.

5. DECODE works with expressions that are scalar values only, CASE can work with predicates and sub queries in searchable form.

SELECT CASE 

-- predicates with IN 

WHEN salary IN (9000, 10000) THEN '9K - 10K' 

-- searchable subquery

WHEN EMP_NO IN (SELECT mgr_no FROM department ) THEN 'dept_mgr'

ELSE 'Unknown'

END category

FROM employee;

6. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.

7. DECODE can work as a function inside SQL only, CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL. In below example, if you replace CASE with DECODE then it gives error.

DECLARE 

NUMBER :=20;

VARCHAR2(20);

BEGIN

dept_desc := CASE deptno

WHEN 10 THEN 'Accounting'

WHEN 20 THEN 'Research'

WHEN 30 THEN 'Sales'

WHEN 40 THEN 'Operations'

ELSE 'Unknown'

END;

DBMS_OUTPUT.PUT_LINE(dept_desc);

END;

8. CASE can be used as parameter of a function/procedure, CASE can even work as a parameter to a procedure call, while DECODE cannot.

Exec MyProc(CASE:A when 'THREE' then 3 else 0 end);

above statement will not give error but below statement gives error.

Exec MyPorc(DECODE(:a, 'THREE', 3,0));

9. CASE expects datatype consistency, DECODE does not,

SELECT DECODE (200, 100, 100, '200', '200', '300') TEST from dual;

-- --------- output

TEST

--------

200

SELECT CASE 200 WHEN 100 THEN 100

WHEN '200' THEN '200'

ELSE '300'

END TEST

FROM dual;

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

Error on line 2 at position 14 WHEN '200' THEN '200'

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

10. CASE handles NULL differently

SELECT DECODE (NULL, NULL, 'This is NULL', 

'THIS IS NOT NULL') TEST

from dual;

----------------------- output

TEST

---------

this is Null

SELECT CASE NULL WHEN NULL THEN 'THIS IS NULL'

ELSE 'THIS IS NOT NULL'

END TEST

FROM DUAL;

---------------------output;

TEST

--------

This is not Null

SELECT CASE WHEN NULL IS NULL THEN 'this is null'

ELSE 'this is not null'

END TEST

FROM dual;

-------------------output

TEST

--------

This is null

based on above comparison, you can identify which one will be the best use for your requirement.

Oracle E-Business Suite Module List

Some of the key applications are:

• Oracle Customer Relationship Management (CRM)
• Services
• Oracle Financial
• Oracle Human Resource Management System (HRMS)
• Oracle Project Portfolio
• Oracle Contracts
• Oracle Supply Chain Management (SCM)
• Oracle Procurement
• Oracle Value Chain Planning

Transfer to Manufacturing using API in Oracle Apps

Transfer to Manufacturing

is a process in Oracle Manufacturing in this blog we will show how to do this by our code / PL/SQL using the API supplied by Oracle. For clarity I have mentioned the Standard way too, and before doing any of these we need to verify the item is not transferred yet.

Verify the item is not transferred to Manufacturing:

We can verify by 2 ways:

1.  The item will not be visible in “Item Master”.

2. In database table “mtl_system_items_b” The ENG_ITEM_FLAG will be ‘Y’ and ENGINEERING_DATE will be NULL

SELECT organization_id, inventory_item_id, eng_item_flag, engineering_date, engineering_item_id, description

FROM mtl_system_items_b

WHERE segment2 like 'DIE_11377_512';

 

Standard way to “Transfer to Manufacturing”

Switch to “PIM Engineer” responsibility >> Click Transfer to Mfg

Select “Item Master Organization”

Inter the Item and click on Transfer.

Now you can verify using the following query and search in “Item Master”.

SELECT organization_id, inventory_item_id, eng_item_flag, engineering_date, engineering_item_id, description

FROM mtl_system_items_b

WHERE segment2 like 'DIE_11377_512';

Using API to transfer to manufacturing from code

How to run concurrent request manually from code in oracle apps

Here is a sample code, we can run the concurrent request manually from code / PL/SQL.

 declare
  l_resp_id number;
  l_appl_id number;
    g_user_id number;
    --l_return varchar2(2000);
    l_request_id NUMBER;
 begin

--to create a session 

    execute immediate 'alter session set NLS_LANGUAGE = ''AMERICAN''';
    execute immediate 'alter session set NLS_TERRITORY = ''UNITED ARAB EMIRATES''';
     
      if g_user_id is null then
       select user_id
       into g_user_id
       from fnd_user
       where user_name = 'XXABC';
      
       select responsibility_id, application_id
       into l_resp_id, l_appl_id
       from apps.fnd_responsibility
       where responsibility_key = 'XX_MFG_ALL';
     
       fnd_global.apps_initialize(
       user_id => g_user_id,
       resp_id => l_resp_id,
       resp_appl_id => l_appl_id);
      
       --need to set policy context if blow line is uncommedet
       mo_global.init('ONT');
      end if;

   -- Run the concurrent request

      l_request_id := fnd_request.submit_request('PO', 'RVCTP', argument1 => 'BATCH');

    dbms_output.put_line('Request id= '||l_request_id);

  end;

column values to comma separated string using CURSOR

For example we need to display the locations in one string separated with commas:




we need to display the values like "New York, Dalas, Chicago, Boston"

  Function col_values_to_comma_string ( )
    return char
    is
      cursor string_cur is
        select loc
        FROM dept;

      l_length number := NULL;
      l_val varchar2(50) := NULL;
      l_dup_lines number := NULL;
  begin

        for rec_string in string_cur loop
                    l_val := rec_string.loc||','||l_val;
        end loop;
       
        l_length := length(l_val);
        l_val := substr(l_val,1,l_length-1);

        return l_val;
end;

Call the function

declare
    v_loc varchar2(100);

begin

v_loc := col_values_to_comma_string;

dbmsoutput.put_line('Locations = '||v_loc);

end;

New York, Dalas, Chicago, Boston

Drop Table If Exists in Oracle

In case of drop table statement you will face an exception in oracle, but in mysql there is a if exists in drop statement. so we can follow the following to avoid this in Oracle.

--CREATE TABLE MENNAN.TABLE_EXAMPLE AS SELECT * FROM DUAL;

---SIMPLE WAY, if execute immediate fails, because table not exists, transaction will be committed

BEGIN

  EXECUTE IMMEDIATE 'DROP TABLE MENNAN.TABLE_EXAMPLE';

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE <> -942 THEN

      RAISE;

    END IF;

END;

/

---- NAMED EXCEPTION, the same as SIMPLE WAY

DECLARE

  ve_TableNotExists EXCEPTION;

  PRAGMA EXCEPTION_INIT(ve_TableNotExists, -942);

  vs_DynamicDropTableSQL VARCHAR2(1024);

  vs_TableName           VARCHAR2(64);

BEGIN

  vs_TableName           := 'MENNAN.TABLE_EXAMPLE';

  vs_DynamicDropTableSQL := 'DROP TABLE ' || vs_TableName;

  EXECUTE IMMEDIATE vs_DynamicDropTableSQL;

EXCEPTION

  WHEN ve_TableNotExists THEN

    dbms_output.put_line(vs_TableName || ' not exist, skipping....');

  WHEN OTHERS THEN

    dbms_output.put_line(SQLERRM);

    RAISE;

END;

/

---- NAMED EXCEPTION, the same as SIMPLE WAY but more general one

DECLARE

  ve_TableNotExists EXCEPTION;

  PRAGMA EXCEPTION_INIT(ve_TableNotExists, -942);

  PROCEDURE DropTable(pis_TableName IN VARCHAR2) IS

    vs_DynamicDropTableSQL VARCHAR2(1024);

  BEGIN

 

    vs_DynamicDropTableSQL := 'DROP TABLE ' || pis_TableName; 

    EXECUTE IMMEDIATE vs_DynamicDropTableSQL;

   

  EXCEPTION

    WHEN ve_TableNotExists THEN

      dbms_output.put_line(pis_TableName || ' not exist, skipping....');

    WHEN OTHERS THEN

      dbms_output.put_line(SQLERRM);

      RAISE;

  END DropTable;

 

 

BEGIN

  DropTable('MENNAN.TABLE_EXAMPLE');

END;

/

---Yet another way, first check from dictionary, if exists drop the table. Checking from dictionary may take time but avoids unnecessary transactions

DECLARE

  vs_TableName VARCHAR2(64);

  vn_Count     PLS_INTEGER;

BEGIN

  vs_TableName := 'MENNAN.TABLE_EXAMPLE';

 

  SELECT COUNT(*)

    INTO vn_Count

    FROM ALL_TABLES

   WHERE OWNER || '.' || TABLE_NAME = vs_TableName;

  IF vn_Count > 0 THEN

    EXECUTE IMMEDIATE 'DROP TABLE ' || vs_TableName;

  END IF;

END;

/

Query for salesperson_name in Order Management

Query for salesperson_name in Order Management 

The following query can be used to display the saleperson name as shown in Order Form.


select resource_name as salesperson_name from
jtf_rs_defresources_v j, jtf_rs_salesreps s
where j.resource_id = s.resource_id
AND s.salesrep_id = oe_order_header_all.salesrep_id;