Script to retrive password for Oracle apps Front end User from Backend.

SELECT usr.user_name,

get_pwd.decrypt

((SELECT (SELECT get_pwd.decrypt

(fnd_web_sec.get_guest_username_pwd,

usertable.encrypted_foundation_password

)

FROM DUAL) AS apps_password

FROM fnd_user usertable

WHERE usertable.user_name =

(SELECT SUBSTR

(fnd_web_sec.get_guest_username_pwd,

1,

INSTR

(fnd_web_sec.get_guest_username_pwd,

'/'

)

- 1

)

FROM DUAL)),

usr.encrypted_user_password

) PASSWORD

FROM fnd_user usr

WHERE usr.user_name = 'SYSADMIN';

 Script to Create  a New User from Backend and Add system Administrator Respnsibility in Oracle Apps R12.

DECLARE

v_user_name VARCHAR2 (100) := upper('&Enter_User_Name');

 v_description VARCHAR2 (100) := 'User';

BEGIN

 fnd_user_pkg.createuser

(x_user_name => v_user_name,

 x_owner => NULL,

 x_unencrypted_password => '&input_passwo',

 x_session_number => 0,

 x_start_date => SYSDATE,

 x_end_date => NULL,

 x_last_logon_date => NULL,

 x_description => v_description,

 x_password_date => NULL,

 x_password_accesses_left => NULL,

 x_password_lifespan_accesses => NULL,

 x_password_lifespan_days => NULL,

 x_employee_id => NULL,

 x_email_address => NULL,

 x_fax => NULL,

 x_customer_id => NULL,

 x_supplier_id => NULL,

 x_user_guid => NULL,

 x_change_source => NULL

 );

 COMMIT;

END;

 /

SHOW ERR;

/

select a.application_short_name, r.responsibility_key

from fnd_responsibility_vl r, fnd_application_vl a where

r.application_id =a.application_id

and upper(r.responsibility_name) like  upper ('%SYS%admi%');

/

DECLARE

 v_user_name VARCHAR2 (100) := upper('&1');

BEGIN

fnd_user_pkg.addresp(username => v_user_name

 ,resp_app => 'SYSADMIN' --SYSADMIN

 ,resp_key => 'SYSTEM_ADMINISTRATOR' --SYSTEM_ADMINISTRATOR

 ,security_group => 'STANDARD'

 ,description => NULL

 ,start_date => SYSDATE

 ,end_date => null);

 commit;

END;

 /

SHOW ERR;

 Query to find Responsibility for the Concurrent Program

SELECT frt.responsibility_name,

               frg.request_group_name,

               frg.description

  FROM fnd_request_groups frg

             ,fnd_request_group_units frgu

             ,fnd_concurrent_programs fcp

             ,fnd_concurrent_programs_tl fcpt

             ,fnd_responsibility_tl frt

             ,fnd_responsibility frs

 WHERE frgu.unit_application_id = fcp.application_id

 AND   frgu.request_unit_id = fcp.concurrent_program_id

 AND   frg.request_group_id = frgu.request_group_id

 AND   frg.application_id = frgu.application_id

 AND   fcpt.source_lang = USERENV('LANG')

 AND   fcp.application_id = fcpt.application_id

 AND   fcp.concurrent_program_id = fcpt.concurrent_program_id

 AND   frs.application_id = frt.application_id

 AND   frs.responsibility_id = frt.responsibility_id

 AND   frt.source_lang = USERENV('LANG')

 AND   frs.request_group_id = frg.request_group_id

 AND   frs.application_id = frg.application_id

-- AND   fcp.concurrent_program_name = <shortname>

 AND   fcpt.user_concurrent_program_name LIKE '%XXXX%'--<XXXX --User concurrent program>

How to view data In Oracle apps R12 views

 Tips to Show data In Oracle apps R12 views

Oracle Views containts data from all the different Orgs, so while quering it may not show the data in it , so to fetch data in oracle views below are few tips 

Tip 1>Set multi org context as below,

begin

MO_GLOBAL.SET_POLICY_CONTEXT('S',84);-- — pass in the organization id

end ;

/

Find out org id from below query.

SELECT FND_PROFILE.VALUE('ORG_ID') FROM DUAL --82

/

Tips2 >>Some time you need to perform below setting

Issue got resolved after changing language in sql developer,

Tools>Preferences>Database>NLS>Language >  American .

It was English changed to American and it worked .

Order types in oracle apps

 Order Types in ORACLE APPS

1. Standard Order

2. Back to Back Order

3. Drop Ship Orders

4. Internal SO.

Back to Back Order : As soon as Customer places an order , an order is placed with suppliers to ship the goods at Enterprise inventory . Then those goods are shipped to customer from enterprise inventory is  called as Back to Back Order.

Drop Ship Cycle : As soon as customer places an order , order is placed with suppliers but goods are directly sent to customer place instead of enterprise receiving it in there inventory.

o2c cycle /Standard Order Process 

Enter SO > Book SO >>Pick Release >> Ship Confirm >>Create Invoice >> Transfer to Gl 

P2p Cycle 

Create Requisition >Create PO >>Inv receipts >>Payment to Suppliers >> Transfer to Gl

Types of PO 

1. Standard PO

2. Blanket PO

3. Planned 

4. Contract PO .

 Sample script to Create Journals Using API from Backend.

--Below is the sample script to call API from backend .

declare
      ------------------------
      --API Variables
      ------------------------
      p_init_msg_list         VARCHAR2(1):= FND_API.G_TRUE;
      lx_return_status        VARCHAR2(240);
      lx_msg_count            NUMBER;
      lx_msg_data             VARCHAR2(4000);
      lx_msg_index_out        NUMBER;
      lx_completion_retcode   VARCHAR2 (1) := 'N';
      lx_error                NUMBER := 0;   -- This is for line errors count
      lx_exception            EXCEPTION;
      LX_AE_HEADER_ID         NUMBER;
      LX_EVENT_ID              NUMBER;
    begin 
          -------------------------------
         --Apps Initialization
         -------------------------------
       -- fnd_global.apps_initialize ( user_id => 24214 --User Id
-- ,resp_id => 51108                             --Responsibility Id
-- ,resp_appl_id => 608                          --Responsibility Application Id
-- );
          
          XLA_JOURNAL_ENTRIES_PUB_PKG.create_journal_entry_header( p_api_version         => 1.0
                                                                 ,p_init_msg_list       => p_init_msg_list
                                                                 ,p_application_id      => 222--ln_application_id
                                                                 ,p_ledger_id           => 1001--ln_ledger_id
                                                                 ,p_legal_entity_id     => null
                                                                 ,p_gl_date             => '30-NOV-2019'--ld_accounting_date
                                                                 ,p_description         => 'TESTING '--lc_description
                                                                 ,p_je_category_name    => 'Accrual'--lc_je_category
                                                                 ,p_balance_type_code   => 'A'
                                                                 ,p_budget_version_id   => null
                                                                 ,p_reference_date      => null
                                                                 ,p_budgetary_control_flag => null
                                                                 ,p_attribute_category     => NULL
                                                                 ,p_attribute1             => NULL--lc_reversal_flag
                                                                 ,p_attribute2             => null
                                                                 ,p_attribute3             => null--TO_CHAR(ld_creation_date,'DD/MM/YYYY HH24:MI:SS')
                                                                 ,p_attribute4             => null
                                                                 ,p_attribute5            => null
                                                                 ,p_attribute6            => null
                                                                 ,p_attribute7            => null
                                                                 ,p_attribute8            => null
                                                                 ,p_attribute9            => null
                                                                 ,p_attribute10           => null
                                                                 ,p_attribute11           => null
                                                                 ,p_attribute12           => null
                                                                 ,p_attribute13           => null
                                                                 ,p_attribute14           => null
                                                                 ,p_attribute15           => null
                                                                 ,x_return_status         => lx_return_status
                                                                 ,x_msg_count             => lx_msg_count
                                                                 ,x_msg_data              => lx_msg_data
                                                                 ,x_ae_header_id          => lx_ae_header_id
                                                                 ,x_event_id              => lx_event_id
                                                                );
 
         /*--------------------------------+
         |Result status and error handling |
         +--------------------------------*/
         FND_FILE.put_line(FND_FILE.LOG,'***************************');
         FND_FILE.put_line(FND_FILE.LOG,'Header Output information ....');
         FND_FILE.put_line(FND_FILE.LOG,'return_status= '||lx_return_status);
         FND_FILE.put_line(FND_FILE.LOG,'msg_count= '||lx_msg_count);
         FND_FILE.put_line(FND_FILE.LOG,'ae_header_id= '||lx_ae_header_id);
         FND_FILE.put_line(FND_FILE.LOG,'event_id= '||lx_event_id);
         FND_FILE.put_line(FND_FILE.LOG,'***************************');
         
         DBMS_OUTPUT.put_line('***************************');
         DBMS_OUTPUT.put_line('Header Output information ....');
         DBMS_OUTPUT.put_line('return_status= '||lx_return_status);
         DBMS_OUTPUT.put_line('msg_count= '||lx_msg_count);
         DBMS_OUTPUT.put_line('ae_header_id= '||lx_ae_header_id);
         DBMS_OUTPUT.put_line('event_id= '||lx_event_id);
         DBMS_OUTPUT.put_line('***************************');
         
         IF lx_return_status <> 'S' AND lx_msg_count > 0 THEN
               FOR I IN 1..lx_msg_count 
               LOOP                
                  FND_MSG_PUB.get (p_msg_index       => i,
                                   p_encoded         => 'F',
                                   p_data            => lx_msg_data,
                                   p_msg_index_out   => lx_msg_index_out
                                  );
                  DBMS_OUTPUT.put_line(lx_msg_data);
               END LOOP;
            END IF;
 
end;   

JSON support in 12c

 JSON SUPPROT IN ORACLE 12C 

Oracle has started json support from 12c version onwards . JSON is used nowadays more populalry for data transfer among different subsystems similar to XML. 

below are some examples to understand the json support.

SET SERVEROUTPUT ON;

DECLARE

    TYPE nt_fName   IS TABLE OF VARCHAR2 (20);

    TYPE nt_JName   IS TABLE OF VARCHAR2 (20);

    

    fname   nt_fName;

    LJOB   nt_JName;

BEGIN

    SELECT Ename, JOB

        BULK COLLECT INTO fName, LJOB 

    FROM empL; 

        

        --Print values from the collection--

    FOR idx IN 1..fName.COUNT

    LOOP

        DBMS_OUTPUT.PUT_LINE (idx||' - EMP NAME IS : '||fName (idx) ||' AND JOB : '||LJOB (idx));

    END LOOP;

END;

---------------------JSON --------------------

SELECT JSON_OBJECT('ENAME ' VALUE ENAME ) FROM EMPL---

SELECT JSON_OBJECTAGG('ENAME' VALUE ENAME ) FROM EMPL--

SELECT JSON_ARRAY(ENAME ) FROM EMPL --

SELECT JSON_ARRAYAGG(ENAME) FROM EMPL

/

--GENERATE JSON DATA FRRMAT FOR DEPT AND EMPLOYEES WORKING IN THOSE DEPT

SELECT JSON_OBJECTAGG(D.DNAME

       VALUE JSON_ARRAY(E.ENAME))

   FROM EMPL E, DEP D

   WHERE E.DEPNO=D.DEPNO

   GROUP BY D.DNAME

/

SELECT JSON_OBJECTAGG(D.DNAME

       VALUE JSON_ARRAYAGG(E.ENAME))

   FROM EMPL E, DEP D

   WHERE E.DEPNO=D.DEPNO

   GROUP BY D.DNAME

--- dynamic sql---  EMPL , DEP 

SET SERVEROUTPUT ON;

DECLARE

SQL_STMT VARCHAR2(1000);

 EMP_TOT VARCHAR2(1000);

 v_qry varchar2(100);

BEGIN

v_qry := &a;

SQL_STMT := 'SELECT COUNT(*) FROM  ' ||v_qry; --always it shud be insingle quotes

EXECUTE IMMEDIATE SQL_STMT INTO EMP_TOT;

DBMS_OUTPUT.PUT_LINE('TOTAL rowcount from table '|| v_qry || ' is : '||EMP_TOT);

END;

/

Cuesors and Types of Cursors

 CURSORS IN PLSQL WITH SIMPLE                                         EXAMPLES

-----------------------------CURSORS--------------------------

 

 CURSOR :

  

-->> WHENEVER A SQL AUERY WHICH RETRIVES MORE THAN  ONE ROW , TO PROCESS THOSE RECORDS/ROWS WE USE CURSORS.

  >>CONTEXT AREA OF MEMORY WHERE QUERY RECORDS ARE SAVED.

 

 

 -----------

 

 DECLARE

 V_EMPNO EMPL.EMPNO%TYPE;

 V_SAL   EMPL.SAL%TYPE;

 V_HIREDATE EMPL.HIREDATE%TYPE;

 BEGIN

 SELECT EMPNO,SAL,HIREDATE 

 INTO V_EMPNO,V_SAL,V_HIREDATE --ONLY ONE ROW

 FROM EMPL

 WHERE  ROWNUM<10;

 

 DBMS_OUTPUT.PUT_LINE('ID  OF       EMPLOYE IS :'||V_EMPNO);

 DBMS_OUTPUT.PUT_LINE('SALARY  OF   EMPLOYE IS :'||V_SAL);

 DBMS_OUTPUT.PUT_LINE('HIREDATE  OF EMPLOYE IS :'||V_HIREDATE);

 END;

 

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

 

  --TYPES OF CUSRSORS

    1. IMPLICIT CURSORS: >> SYSTEM DEFINED

    2. EXPLICIT CURSORS  >> USER DEFINED 

    

    

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

 

 

DECLARE

  CURSOR C IS    SELECT *  FROM EMPL;

BEGIN

  FOR I IN c

  LOOP

    dbms_output.put_line('EMPLOYEE NAME IS :  '||i.ename || ' ::EMPLOYEE ID IS :'|| I.EMPNO ||' ::SALARY IS : '||I.SAL);

  END LOOP;

END;

/

 

 

 

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

 

 

 

 

DECLARE

CURSOR EMP_CUR IS SELECT ENAME, EMPNO FROM EMPL; --DECLARE

V_CUR EMP_CUR%ROWTYPE;

BEGIN

 OPEN EMP_CUR;  --2. OPEN THE CURSORS

 FETCH EMP_CUR INTO V_CUR;

 LOOP 

 EXIT WHEN EMP_CUR%NOTFOUND 

 DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS : ' || V_CUR.ENAME || ':: EMP ID IS : '||V_CUR.EMPNO );

 END LOOP;

 END;

 

 

 /

 

 DECLARE

  CURSOR c  IS    SELECT ename FROM empl; --DECLARE

  vname c%rowtype;

BEGIN

  OPEN c; --2. OPEN THE CURSORS

  loop

    fetch c INTO vname; -- FETCH CURSOR 

    exit  WHEN c%notfound; 

    dbms_output.put_line(vname.ename);

  END loop;

  CLOSE C;          --4. CLOSE CURSOR

END;

/

--CURSOR FOR LOOPS:

--OPEN , FETCH  AND CLOSE THE CURSR INTERNALLY...

--WE NEED NOT INITIALIZE VARIABLE AS WELL.

 --WAY001

 DECLARE

  CURSOR c

  IS

    SELECT ename FROM empl;

  Vname c%rowtype;

BEGIN

  OPEN c;

  LOOP

    FETCH c INTO vname;

    EXIT

  WHEN c%notfound;

    Dbms_output.put_line(vname.ename);

  END LOOP;

END;

/

--WAY _002

  DECLARE

  CURSOR c  IS    SELECT ename FROM empl; --DECLARE 

BEGIN  

  FOR I IN  C LOOP

  DBMS_OUTPUT.PUT_LINE('NAME OF EMPLOYEE IS :'||I.ENAME);

  END LOOP;

  

END;

--WAY _003

 

BEGIN  

  FOR I IN ( SELECT ename FROM empl) LOOP

  DBMS_OUTPUT.PUT_LINE('NAME OF EMPLOYEE IS :'||I.ENAME);

  END LOOP;

  

END;

  

 / 

 --WAY001

 DECLARE

  CURSOR c

  IS

    SELECT ename FROM empl;

  Vname c%rowtype;

BEGIN

  OPEN c;

  LOOP

    FETCH c INTO vname;

    EXIT

  WHEN c%notfound;

    Dbms_output.put_line(vname.ename);

  END LOOP;

END;

/

 ----------------------------------------------PARAMETERISED CURSORS:-------------------------------

 

 

 ----------------------------------------------PARAMETERISED CURSORS:-------------------------------

 

 A CURSOR WHICH CAN ACCEPT THE PARAMETER IS CALLED AS PARAMETERISED CURSOR.

 

  E.G 

  

  

  DECLARE

  CURSOR c (X NUMBER,V_ENAME VARCHAR2)  IS    SELECT HIREDATE,SAL  FROM empl WHERE EMPNO = X AND ENAME = V_ENAME; --DECLARE 

  VAR_1 NUMBER;

  VAR_2 VARCHAR2(100);

BEGIN  

 VAR_1 := &a;

 VAR_2 := &B;

 

  FOR I IN  C (VAR_1 , VAR_2) LOOP

  DBMS_OUTPUT.PUT_LINE('HIREDATE  OF EMPLOYEE IS :'||I.HIREDATE);

  DBMS_OUTPUT.PUT_LINE('SALARY  OF EMPLOYEE IS :'||I.SAL);

  END LOOP;

  

END;

/

Performance Tuning Tips in Oracle

 TIPS FOR PERFORMANCE TUNING 

1. try to Analyze cost of the query using explain plan

2. using explain plan try to find out the cost of query and find out which join is taking more cost , that is taking more time , so try to rewrite that join or expression.

3. Try to use exists () , instead of in () / like expression.

4. try to Add indexes on the id columns

5. If creating indexes doesnot work then try creating functional index which is more of related to a expression of a query join

6. try to use partition by clause.

7. Try to avoid the full table scans.

Dynamic SQL

💡 Dynamic SQL Example: DML Statement in Oracle PL/SQL

Dynamic SQL allows you to build SQL statements dynamically at runtime and execute them. This is useful when the exact SQL code is not known until the program runs. Below is a simple example using EXECUTE IMMEDIATE to execute a DML (SELECT) statement dynamically.

---

📌 Example: Count Total Employees Using Dynamic SQL

SET SERVEROUTPUT ON;

DECLARE
    SQL_STMT   VARCHAR2(1000);
    EMP_TOT    NUMBER;
BEGIN
    SQL_STMT := 'SELECT COUNT(*) FROM EMP';
    EXECUTE IMMEDIATE SQL_STMT INTO EMP_TOT;
    DBMS_OUTPUT.PUT_LINE('TOTAL EMP ARE: ' || EMP_TOT);
END;
/

---

🧠 Explanation:

• SQL_STMT: A variable that stores the SQL query string.
• EXECUTE IMMEDIATE: Executes the SQL command stored in SQL_STMT.
• INTO EMP_TOT: Stores the output of the SELECT statement.
• DBMS_OUTPUT: Displays the result in the output console.

---

✅ When to Use Dynamic SQL

• Building flexible, parameterized queries.
• Running DDL commands like CREATE, DROP, ALTER.
• Working with unknown table names or column lists at runtime.

---

📎 Related Posts

• Dynamic SQL vs Static SQL in Oracle
• How to Use EXECUTE IMMEDIATE with Bind Variables
• Best Practices for Writing Dynamic SQL

``` --- ✅ **Instructions to Use in Blogger**: * Go to Blogger post editor. * Switch to the **HTML view**. * Paste the HTML code above. * Add your images or customize the sidebar links if needed. Would you like the HTML for an example using **Dynamic DDL** or **Dynamic Insert/Update** next?

Query to find out executable of a Concurrent Program

🔍 Query to Find Executable of a Concurrent Program in Oracle EBS

In Oracle E-Business Suite (EBS), each Concurrent Program is linked to an executable file that runs in the backend. To find out the executable associated with a given concurrent program, use the SQL query below.

---

📌 SQL Query to Fetch Executable Details

SELECT 
    prog.user_concurrent_program_name AS "Program Name",
    prog.concurrent_program_name AS "Program Short Name",
    appl.application_name AS "Program Application Name",
    prog.description AS "Program Description",
    exe.executable_name AS "Executable Name",
    exe.execution_file_name AS "Executable File Name",
    DECODE(
        exe.execution_method_code,
        'I', 'PL/SQL Stored Procedure',
        'P', 'Oracle Reports',
        'L', 'SQL*Loader',
        'Q', 'SQL*Plus',
        exe.execution_method_code
    ) AS "Execution Method"
FROM 
    apps.fnd_executables exe,
    apps.fnd_application_tl appl,
    apps.fnd_concurrent_programs_vl prog
WHERE 
    exe.application_id = appl.application_id
    AND exe.executable_id = prog.executable_id
    AND appl.language = 'US'
    AND prog.user_concurrent_program_name LIKE '%AP%'; -- Optional filter

Tip: Replace '%AP%' with any part of the program name you want to search. For example, to find a specific report like "Invoice Register", use '%Invoice%'.

---

🧠 Columns Explained

• Program Name: User-friendly name of the concurrent program.
• Executable Name: Name given to the executable in EBS.
• Executable File Name: Actual file or procedure name that runs.
• Execution Method: How the program is run (e.g., Report, PL/SQL, SQL Loader).

---

📎 Related Topics

• How to Register a New Concurrent Program in Oracle EBS
• How to Schedule and Run Concurrent Programs
• Query to Find Last Run Status of Concurrent Requests