Query To Get All Assigned Responsibilities Of User In Oracle Apps R12

 SELECT distinct
    fuser.USER_NAME USER_NAME
    , fuser.user_id
    , fuser.creation_date
    , fuser.last_update_date
    , fuser.LAST_LOGON_DATE
    , fuser.START_DATE
    , fuser.END_DATE
    , per.FULL_NAME FULL_NAME
    , per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
    , frt.RESPONSIBILITY_NAME RESPONSIBILITY
    , TO_CHAR(furg.START_DATE,'DD-MON-YYYY') resp_attched_date
    , TO_CHAR(furg.END_DATE,'DD-MON-YYYY') resp_remove_date
FROM
    FND_USER fuser
    , PER_PEOPLE_F per
    , fnd_user_resp_groups_direct furg
    , FND_RESPONSIBILITY_TL frt
WHERE
    fuser.EMPLOYEE_ID = per.PERSON_ID
    AND fuser.USER_ID = furg.USER_ID
    AND (to_char(fuser.END_DATE) is null
    OR fuser.END_DATE > sysdate)
    AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
    AND frt.LANGUAGE = 'US'
    and fuser.user_name like 'SYSADMIN%'
ORDER BY
    fuser.USER_NAME;

Organization Table In Oracle Apps R12

 Here are tables for organization in oracle apps R12

Multiple organization tables are available in Oracle R12, listed tables are described about their uses in Oracle Apps R12

Organization Table in Oracle Apps R12

HR_ORGANIZATION_UNITS: This table stores information about all the organizations (including inventory organizations) are available.

HR_ALL_ORGANIZATION_UNITS: This table stores definitions that identify business groups and organization units within a single business group

HR_ORGANIZATION_INFORMATION

HR_LOCATIONS

HR_LEGAL_ENTITIES

HR_OPERATING_UNITS: This table stores information about all the operating units are available in the system.

Inventory Organization Table in Oracle Apps R12

ORG_ORGANIZATION_DEFINITIONS: This is a view and depends on MTL_PARAMETERS table

Sub Inventory Table in Oracle Apps R12

MTL_SECONDARY_INVENTORIES: This table stores the sub inventory information.

To import suppliers using Interface table

 How to insert data in PO_VENDORS TABLE.

1. INSERT DATA IN AP_SUPPLIERS_INT ( Mandatory columns: VENDOR_INTERFACE_ID, VENDOR_NAME, STATUS)
The status should be 'NEW'
2. EXECUTE THE IMPORT PROGRAM
Logon to responsibility PAYABLES >> REQUEST >> SUPPLIERS OPEN INTERFACE IMPORT

3. CHECK IN PO_VENDORS TABLE
OR
Logon to responsibility PAYABLES >> SUPPLIERS >> INQUIRY
Search for the supplier name

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;