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

Script to reset User password in Oracle Apps

🔐 Script to Reset User Password in Oracle EBS from Backend

Oracle E-Business Suite (EBS) provides a built-in API that allows administrators to reset user passwords directly from the backend. This is especially useful when access to the front end is restricted or for bulk password resets.

---

📌 API Used: fnd_user_pkg.changepassword

This API accepts two input parameters:

• username – The EBS user login ID
• newpassword – The new password to assign

---

💡 Example: PL/SQL Block to Reset Password

Use the following anonymous PL/SQL block to reset a user's password:

SET SERVEROUTPUT ON;

DECLARE
    v_reset BOOLEAN;
BEGIN
    v_reset := fnd_user_pkg.changepassword(
                  username    => 'AKS123',
                  newpassword => 'Oracle123'
              );

    IF v_reset THEN
        DBMS_OUTPUT.PUT_LINE('✅ Password has been successfully reset.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('❌ Password reset failed.');
    END IF;
END;
/ 
COMMIT;

Note: Replace 'AKS123' with the actual username and 'Oracle123' with a secure new password.

---

🔐 Best Practices

• Always run this script in a development or test instance first before using in production.
• Ensure your user has the necessary privileges to execute this API.
• Use complex passwords to meet security compliance.
• Keep audit logs of any manual password resets.

---

📎 Related Topics

• How to Lock/Unlock Users in Oracle EBS
• Create Users Programmatically in Oracle EBS
• Query to Check Last Login of EBS Users

How to Transfer the files from local machine to Oracle EBS server

 How to Transfer the files from local machine to Oracle EBS server

 

Download and Install Winscp software ,its an freeware you can download  from below link ,

 

https://winscp.net/eng/download.php 

 

Goto >  Winscp >  give Connection Details along with Servver details.

you can get this details  along with username and password from your  apps DBA.

 

 

now to goto exact file location , for this you should know the oracle apps file structure .

 few examples of files types with there server location

forms (.fmb)  >  $CUSTOM_TOP/forms/US

reports          >   $CUSTOM_TOP/reports

 to find out the exact custom top from putty exlained in below post or using table " applsys.FND_ENV_CONTEXT"

 

 for file transfer you need to select appropriate transfer setting ,in binary mode or text or else if you dont know  which mode to select you can use automatic default mode.

 

 

for transferring file from  local machine to server , 

in left window >goto file in local path > right click choose upload.

for transferring file from   server to local machine , 

in right  window >goto file in server path > right click choose download.

 

How to login to putty and find out custom_top path in oracle APPS

Putty is freeware , you can download it from below link

https://www.putty.org

 

 

Give username and password..

YOU need to run the command for setting environment EBSapps.env

 

Give  choice :R

 

Then type command

Ø  Cd $CUSTOM_TOP

 

 

Now give command >pwd

It will give the custom path foor your environment ..

Copy this path and paste it into winscp

 

 

Query to find front end link URL for Oracle EBS application from backend

🔍 How to Find Oracle EBS Front-End URL from Backend (SQL)

If you're an Oracle E-Business Suite (EBS) administrator or developer, there may be times when you need to identify the front-end login URL of your application. This can easily be retrieved directly from the database using a simple SQL query.

---

🧭 Step-by-Step Guide

Follow the instructions below to query the EBS base URL from the backend:

1. Open your preferred SQL tool: SQL Developer, TOAD, or PL/SQL Developer.
2. Connect to the Oracle EBS database using your schema credentials.
3. Run the below query to fetch the front-end base URL:

SELECT home_url FROM icx_parameters;

This query retrieves the HOME_URL field from the ICX_PARAMETERS table, which holds the base URL configured for your Oracle EBS instance.

Example result of the ICX_PARAMETERS query.

---

💡 Pro Tips

• Make sure you have SELECT privileges on ICX_PARAMETERS.
• If you have multiple environments (DEV, TEST, PROD), this query helps ensure you're using the correct login URL.
• The URL returned can be used to automate login links or embed in external apps.

---

📌 Related Posts

• How to Configure Oracle EBS Login URL
• Understanding ICX Modules in Oracle EBS

Stay tuned for more Oracle EBS insights and backend tips.