Oracle apps Technical Interview Questions - Part 1

Oracle apps Technical Interview Questions - Part 2

Oracle apps Technical Interview Questions - Part 3

How to kill an active session in Oracle apps

Hi, This post shows the queries to find an active sessions for an object and kill the session.

Note: Use the query to kill a session only if you have authorized to kill the session.

Query to see if the object has an active session:

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name, session_id

FROM V$Locked_Object A, All_Objects B

WHERE A.Object_ID = B.Object_ID

and b.object_id =:p_object_id;

Query to find the sis and serial# based on session id that is fetched from the above query:

select * from v$session where sid = '2777';

Command to kill the session using sis and serial#:

alter system kill session '2777,23685';

Happy learning. Cheers!!

Query to get users who have a specific responsibility is attached to them in Oracle apps

Hi! This post shows you the query to fetch the users who have a particular responsibility to them.

SELECT fu.user_name,

  frt.responsibility_name,

  TO_CHAR(furg.start_date,'DD-MON-YYYY') start_date,

  furg.end_date,

  fu.DESCRIPTION 

FROM fnd_user fu ,

  fnd_user_resp_groups_direct furg ,

  fnd_responsibility_vl frt

WHERE fu.user_id                 = furg.user_id

AND frt.responsibility_id        = furg.responsibility_id

AND frt.application_id           = furg.responsibility_application_id

AND NVL(furg.end_date,sysdate+1) > sysdate

AND NVL(frt.end_date,sysdate +1) > sysdate

AND NVL(fu.end_date,sysdate  +1) > sysdate

AND frt.responsibility_name      = :p_resp_name;

Happy learning. Cheers!!

No organizations currently defined error in Oracle apps

Hi, this post shows how to fix the error "No organizations currently defined " in Oracle apps environment. This error usually occurs when a specific organization that we're trying to access is not defined.

Follow below steps to fix this error.

1. Go to the responsibility: Inventory

2. Navigate to Setup > Organization > Organizations Access.

3. Add the responsibility to the list - Responsibility Application and responsibility.

Getting errors is good in someway because they make us learn. Keep learning!!

Query to fetch users who have a specific responsibility attached to them in Oracle apps

Hi! This post shows you the query to fetch all the users who have a specific responsibility attached to them.

SELECT fu.user_name,

  frt.responsibility_name,

  TO_CHAR(furg.start_date,'DD-MON-YYYY') start_date,

  furg.end_date,

  fu.DESCRIPTION

FROM fnd_user fu ,

  fnd_user_resp_groups_direct furg ,

  fnd_responsibility_vl frt

WHERE fu.user_id                 = furg.user_id

AND frt.responsibility_id        = furg.responsibility_id

AND frt.application_id           = furg.responsibility_application_id

AND NVL(furg.end_date,sysdate+1) > sysdate

AND NVL(frt.end_date,sysdate +1) > sysdate

AND NVL(fu.end_date,sysdate  +1) > sysdate

AND frt.responsibility_name      ='Order Management Superuser';

Here, 'Order Management Superuser' is the responsibility and the query would fetch the users who have this responsibility attached to them.

Query to find the location of Application top in Oracle

Hi! This post shows the query to find the location of the application top in Oracle.

SELECT distinct fec.variable_name, fec.value

   FROM fnd_env_context fec

 WHERE fec.variable_name like '%ONT_TOP%' 

ORDER BY fec.variable_name;

Here, ONT_TOP is the application top. Similarly, other paths can be found by providing the application top name including the custom top.

How to fix invalid character in XML Error in Oracle apps R12

Hi, this post shows how to fix the invalid character in XML data error that may occur sometimes in RDF reports in Oracle apps. To fix the error, follow the below enlisted steps.

1) Run following query to determine the characterset:

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'

2) Set profile option FND: NATIVE CLIENT ENCODING to same value as the output of query in step 1.

--FND: NATIVE CLIENT ENCODING for XML data generation

If browser is not responding when loading XML data, recheck RDF then try fetching less data from query. If that works, recheck with all the data.

Cheers!!

Calling a procedure from a function in Oracle apps R12

Hi, this post will give you an idea on how to call a PL/SQL procedure from a PL/SQL function in Oracle apps R12.

In this example, the function named demo_function calls the procedure demo_procedure and returns procedure's output.

Procedure to return a value:

CREATE OR REPLACE PROCEDURE demo_procedure (p_output OUT VARCHAR2)

AS

l_dummy VARCHAR2(20);

BEGIN

SELECT dummy INTO l_dummy FROM dual;

IF l_dummy IS NOT NULL THEN

p_output := 'YES';

ELSE

p_output := 'NO';

END IF;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('An unknown exception occurred '||sqlerrm);

END;

/

Checking the output of the demo_procedure:

DECLARE

l_op VARCHAR2(20);

BEGIN

demo_procedure(l_op);

dbms_output.put_line(l_op);

END;

/

Function to call the demo_procedure:

CREATE OR REPLACE FUNCTION demo_function

RETURN VARCHAR2 AS

l_op VARCHAR2(10);

BEGIN

demo_procedure(l_op);

RETURN l_op;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('Error in executing procedure demo_procedure '||sqlerrm);

END;

/

Output:

SELECT demo_function FROM dual;

So, this is how a procedure can be called from a function.

Cheers!!

Query to find concurrent program responsibility in Oracle apps

Hi, This post will show the query to retrieve responsibility details for a concurrent program in Oracle apps.

Query to find concurrent program responsibility:

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   fcpt.user_concurrent_program_name LIKE <Concurrent program name>;

Cheers!!

How to compile a form in oracle apps

Hello! This post shows you the syntax to compile a form i.e., .fmb to .fmx file.

Syntax to compile a form:

frmcmp_batch module=$AU_TOP/forms/US/XX_DEMO_FORM.fmb Userid=<username>/<pwd> output_file=$FND_TOP/forms/US/XX_DEMO_FORM.fmx compile_all=special batch=yes

Here, XX_DEMO_FORM is the form name in .fmb format. <username> and<pwd> should be replaced with your username and password.

Cheers!!

How to display files without extensions in Putty

Hi, This post shows you the syntax to display the filenames without their extensions in Putty.

Syntax:

ls -1|sed -e 's/\.rdf$//'

Here, ".rdf" is a particular extension. Likewise, you can provide required extension in place of rdf to  display target files without specified extension.

Happy Learning!!

How to compile package spec and body dynamically in shell script in Oracle

Hi, This post is to show how to compile package spec and package body in bash script.

Compile package spec:

pkg_spec=`ls *.pks -1|sed -e ''s/\.pks$//''`;

pkg_body=`ls *.pkb -1|sed -e ''s/\.pkb$//''`;

compile_spec()

{

sqlplus -s <user>/<password>@<host>:<port>/<SID> << EOF

WHENEVER SQLERROR EXIT SQL.SQLCODE;

SET LINES 255

SET PAGES 0

SET FEEDBACK OFF

SET ECHO OFF

SET PAGESIZE 0

SET TIMING OFF

SET SERVEROUTPUT ON

SET TERM ON

ALTER PACKAGE $pkg_spec COMPILE PACKAGE;

EOF

}

compile=`compile_spec`

echo $compile

if [ $? -ne 0 ]; then

  echo "error in compiling package spec"

  exit 1

fi

Here, <user> should be replaced with your username.

Eg: user12

Likewise, <password>, <host>, <port> and <SID> should also be replaced with their respective values.

Compile package body:

compile_body()

{

sqlplus -s <user>/<password>@<host>:<port>/<SID> << EOF

WHENEVER SQLERROR EXIT SQL.SQLCODE;

SET LINES 255

SET PAGES 0

SET FEEDBACK OFF

SET ECHO OFF

SET PAGESIZE 0

SET TIMING OFF

SET SERVEROUTPUT ON

SET TERM ON

ALTER PACKAGE $pkg_body COMPILE BODY;

EOF

}

PKB=`compile_body`

echo $PKB

if [ $? -ne 0 ]; then

  echo "error in compiling body"

  exit 1

fi  

Cheers!!

Query to retrieve complete Sales Order details in Oracle apps R12

Hi In this post, I will show you how to retrieve complete sales order information along with Invoice details from backend in Oracle apps R12.

SELECT OOH.ORDER_NUMBER ORDER_NUMBER,

       OTT.NAME ORDER_TYPE,

       OOH.ORDERED_DATE DATE_ORDERED,

       QLH.NAME PRICE_LIST,

       OOH.CUST_PO_NUMBER CUSTOMER_PO,

       OOH.FLOW_STATUS_CODE STATUS,

       OOH.TRANSACTIONAL_CURR_CODE CURRENCY,

       OOL.LINE_NUMBER LINE,

       OOL.ORDERED_ITEM ORDERED_ITEM,

       MSIB.DESCRIPTION ITEM_DESC,

       OOL.ORDERED_QUANTITY QTY,

       OOL.UNIT_SELLING_PRICE UNIT_SELLING_PRICE,

       (OOL.ORDERED_QUANTITY*OOL.UNIT_SELLING_PRICE) SUBTOTAL,

       OOL.TAX_VALUE TAX,

       OOL.ORDER_QUANTITY_UOM UOM,

       OOL.REQUEST_DATE REQUEST_DATE,

       OOL.SCHEDULE_SHIP_DATE SCHEDULE_SHIP_DATE,

       OOL.SCHEDULE_ARRIVAL_DATE SCHEDULE_ARRIVAL_DATE,

       OOL.PROMISE_DATE PROMISE_DATE,

       OOL.FLOW_STATUS_CODE LINE_STATUS,

       OTT1.NAME LINE_TYPE,

       OOL.CANCELLED_QUANTITY QUANTITY_CANCELLED,

       OOL.SHIPPED_QUANTITY QUANTITY_SHIPPED,

       OOL.RETURN_REASON_CODE REASON,

       OOL.SERVICE_TXN_COMMENTS COMMENTS,

       OOL.SERVICE_REFERENCE_TYPE_CODE SERVICE_REFERENCE_TYPE,

       NULL PROJECT_NUMBER,

       NULL TASK_NUMBER,

       OOL.END_ITEM_UNIT_NUMBER UNIT_NUMBER,

       OOL.CUSTOMER_JOB CUSTOMER_JOB,

       OOL.CUSTOMER_PRODUCTION_LINE PRODUCTION_LINE,

       OOL.CUST_MODEL_SERIAL_NUMBER MODEL_SERIAL_NUMBER,

       OOL.CUSTOMER_DOCK_CODE CUSTOMER_DOCK,

       OOL.CUST_PRODUCTION_SEQ_NUM CUST_PRODUCTION_SEQUENCE,

       OOL.RLA_SCHEDULE_TYPE_CODE RLM_SCHEDULE_TYPE,

       OOS.NAME ORDER_SOURCE,

       OOH.ORIG_SYS_DOCUMENT_REF ORDER_SOURCE_REFERENCE,

       OOL.ORIG_SYS_LINE_REF ORDER_SOURCE_LINE_REFERENCE,

       OOL.TAX_CODE TAX_CLASSIFICATION_CODE,

       OOL.PRICING_QUANTITY_UOM PRICING_UOM,

       OOL.PRICING_DATE PRICING_DATE,

       MP.ORGANIZATION_CODE WAREHOUSE,

       OOL.SOURCE_TYPE_CODE SOURCE_TYPE,

       OOL.SHIPPING_METHOD_CODE SHIPPING_METHOD,

       RCT.TRX_NUMBER INVOICE_NUMBER,

       ACR.RECEIPT_NUMBER RECEIPT_NUMBER,

       OOD.ORGANIZATION_NAME LEGAL_ENTITY,

 HL.ADDRESS1||HL.ADDRESS2||HL.ADDRESS3||HL.ADDRESS4||','||HL.CITY||','||HL.STATE||','||HL.COUNTY SHIP_TO_LOC,

 HL1.ADDRESS1||HL1.ADDRESS2||HL1.ADDRESS3||HL1.ADDRESS4||','||HL1.CITY||','||HL1.STATE||','||HL1.COUNTY BILL_TO_LOC,

       JRS.NAME SALES_PERSON

FROM OE_ORDER_HEADERS_ALL OOH,

     OE_ORDER_LINES_ALL OOL,

     OE_TRANSACTION_TYPES_TL OTT,

     OE_TRANSACTION_TYPES_TL OTT1,

     QP_LIST_HEADERS_TL QLH,

     OE_ORDER_SOURCES OOS,

     MTL_SYSTEM_ITEMS_B MSIB,

     MTL_PARAMETERS MP,

     ORG_ORGANIZATION_DEFINITIONS OOD,

     AR_RECEIVABLE_APPLICATIONS_ALL ARA, 

     AR_CASH_RECEIPTS_ALL ACR, 

     RA_CUSTOMER_TRX_ALL RCT,

     RA_CUSTOMER_TRX_LINES_ALL RCTL,

     HZ_CUST_ACCOUNTS HCA,

     JTF_RS_SALESREPS JRS,

--   JTF_RS_DEFRESOURCES_V JRD,    

     HZ_PARTIES HP,

     HZ_PARTY_SITES HPS,

     HZ_CUST_ACCOUNTS_ALL HCAA,

     HZ_CUST_SITE_USES_ALL HCSUA,

     HZ_CUST_ACCT_SITES_ALL HCASA,

     HZ_LOCATIONS HL,   

     HZ_PARTY_SITES HPS1,

     HZ_CUST_ACCOUNTS_ALL HCAA1,

     HZ_CUST_SITE_USES_ALL HCSUA1,

     HZ_CUST_ACCT_SITES_ALL HCASA1,

     HZ_LOCATIONS HL1  

WHERE OOH.HEADER_ID = OOL.HEADER_ID

  AND OOH.ORG_ID = OOL.ORG_ID

  AND OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID

  AND OOL.LINE_TYPE_ID = OTT1.TRANSACTION_TYPE_ID

  AND QLH.LIST_HEADER_ID = OOH.PRICE_LIST_ID

  AND OOH.ORDER_SOURCE_ID = OOS.ORDER_SOURCE_ID

  AND OOL.ORG_ID = OOD.OPERATING_UNIT

  AND OOD.ORGANIZATION_ID = MSIB.ORGANIZATION_ID

  AND OOL.ORDERED_ITEM = MSIB.SEGMENT1

  AND OOL.ORG_ID = MP.ORGANIZATION_ID

  AND OOD.ORGANIZATION_ID = RCT.LEGAL_ENTITY_ID

  AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID

  AND OOL.LINE_ID = RCTL.INTERFACE_LINE_ATTRIBUTE6

  AND ARA.STATUS='APP' 

  AND ARA.CASH_RECEIPT_ID=ACR.CASH_RECEIPT_ID 

  AND ARA.APPLIED_CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID

  AND OOH.ORDER_NUMBER = RCT.INTERFACE_HEADER_ATTRIBUTE1

  AND OOH.SOLD_TO_ORG_ID = HCAA.CUST_ACCOUNT_ID

  AND JRS.SALESREP_ID = RCT.PRIMARY_SALESREP_ID

  AND HPS.PARTY_ID = HP.PARTY_ID

  AND HCASA.CUST_ACCOUNT_ID = HCAA.CUST_ACCOUNT_ID

  AND RCT.SHIP_TO_SITE_USE_ID = HCSUA.SITE_USE_ID

  AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID

  AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID

  AND HPS.LOCATION_ID = HL.LOCATION_ID

  AND RCT.BILL_TO_SITE_USE_ID = HCSUA1.SITE_USE_ID

  AND HCASA1.CUST_ACCOUNT_ID = HCAA1.CUST_ACCOUNT_ID

  AND HCSUA1.CUST_ACCT_SITE_ID = HCASA1.CUST_ACCT_SITE_ID

  AND HCASA1.PARTY_SITE_ID = HPS1.PARTY_SITE_ID

  AND HPS1.LOCATION_ID = HL1.LOCATION_ID

  AND OOH.ORDER_NUMBER = '90291' --<Your_order_number>

  AND ACR.RECEIPT_NUMBER = 'RECEIPT_90291'--<Your_receipt_ number>

GROUP BY OOH.ORDER_NUMBER,

         OTT.NAME,

         OOH.ORDERED_DATE,

         QLH.NAME,

         OOH.CUST_PO_NUMBER,

         OOH.FLOW_STATUS_CODE,

         OOH.TRANSACTIONAL_CURR_CODE,

         OOL.LINE_NUMBER,

         OOL.ORDERED_ITEM,

         MSIB.DESCRIPTION,

         OOL.ORDERED_QUANTITY,

         OOL.UNIT_SELLING_PRICE,

         OOL.TAX_VALUE,

         OOL.ORDER_QUANTITY_UOM,

         OOL.REQUEST_DATE,

         OOL.SCHEDULE_SHIP_DATE,

         OOL.SCHEDULE_ARRIVAL_DATE,

         OOL.PROMISE_DATE,

         OOL.FLOW_STATUS_CODE,

         OTT1.NAME,

         OOL.CANCELLED_QUANTITY,

         OOL.SHIPPED_QUANTITY,

         OOL.RETURN_REASON_CODE,

         OOL.SERVICE_TXN_COMMENTS,

         OOL.SERVICE_REFERENCE_TYPE_CODE,

         OOL.END_ITEM_UNIT_NUMBER,

         OOL.CUSTOMER_JOB,

         OOL.CUSTOMER_PRODUCTION_LINE,

         OOL.CUST_MODEL_SERIAL_NUMBER,

         OOL.CUSTOMER_DOCK_CODE,

         CUST_PRODUCTION_SEQ_NUM,

         OOL.RLA_SCHEDULE_TYPE_CODE,

         OOS.NAME,

         OOH.ORIG_SYS_DOCUMENT_REF,

         OOL.ORIG_SYS_LINE_REF,

         OOL.TAX_CODE,

         OOL.PRICING_QUANTITY_UOM,

         OOL.PRICING_DATE,

         MP.ORGANIZATION_CODE,

         OOL.SOURCE_TYPE_CODE,

         OOL.SHIPPING_METHOD_CODE,

         RCT.TRX_NUMBER,

         ACR.RECEIPT_NUMBER,

         OOD.ORGANIZATION_NAME,                 HL.ADDRESS1||HL.ADDRESS2||HL.ADDRESS3||HL.ADDRESS4||','||HL.CITY||','||HL.STATE||','||HL.COUNTY,

HL1.ADDRESS1||HL1.ADDRESS2||HL1.ADDRESS3||HL1.ADDRESS4||','||HL1.CITY||','||HL1.STATE||','||HL1.COUNTY,

JRS.NAME;

Happy Learning!!

Sample example to use Ref cursor in Oracle Apps for beginners

Hi, This post is to show you a sample example on how to use ref cursor in Oracle apps R12 which will be helpful for beginners. Below code shows an example of using Ref cursor.

CREATE OR REPLACE PROCEDURE RETURNING_REFCURSOR(

                   P_EMPNO EMP05.EMPNO%TYPE,

                  L_REFCURSOR OUT SYS_REFCURSOR) IS

L_ENAME EMP05.ENAME%TYPE;

L_DNAME DEPT05.DNAME%TYPE;

BEGIN

OPEN L_REFCURSOR FOR SELECT E.ENAME,D.DNAME FROM EMP05 E,DEPT05 D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = P_EMPNO;

LOOP

FETCH L_REFCURSOR INTO L_ENAME,L_DNAME;

EXIT WHEN L_REFCURSOR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(L_ENAME||' '||L_DNAME);

END LOOP;

CLOSE L_REFCURSOR;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END RETURNING_REFCURSOR;

/

SET SERVEROUTPUT ON

VARIABLE G_REF REFCURSOR

VARIABLE G_EMPNO NUMBER

EXECUTE :G_EMPNO := 7839

EXECUTE RETURNING_REFCURSOR(:G_EMPNO,:G_REF)

Happy learning!!

How to pass multiple values to a parameter in concurrent program in Oracle apps

Hi! In this post, I will show you how to pass multiple values separated by comma in concurrent program parameter in Oracle apps.

To achieve the same requirement in the database, we can use lexical parameter like below. It retrieves the department names based on the department numbers that we pass in the p_deptno parameter.

SELECT dname "Department" FROM dept WHERE deptno IN (&p_deptno);

However, the above format won't work in concurrent program parameter but it can be achieved using REGEXP. The below code shows a sample procedure that uses a cursor with SELECT query to achieve the same requirement that can be used in concurrent programs.

To pass multiple values separated by commas in Concurrent program parameter:

CREATE OR REPLACE PROCEDURE getDept (p_deptno IN VARCHAR2)

AS

CURSOR get_dept_cur IS SELECT dname FROM dept WHERE deptno IN 

(SELECT REGEXP_SUBSTR(UPPER(p_deptno), '[^,]+', 1, level) FROM dual

CONNECT BY REGEXP_SUBSTR(p_deptno, '[^,]+', 1, level) IS NOT NULL);

BEGIN

FOR i IN get_dept_cur LOOP

dbms_output.put_line(i.dname);

END LOOP;

END;

/

EXEC getDept('10,20,30,40');

Hope this helps. Happy learning! 

Query to find RDF report path in Oracle apps

Hi, This post shows you how to find the RDF report path in server in Oracle apps R12. The query below retrieves the report path along with application name which takes execution file name is the parameter.

SELECT fa.application_name,

               '$'||basepath||'/'||'reports/US' Reports_Path,

   FROM apps.fnd_executables_vl fe, 

                apps.fnd_application_vl fa 

WHERE fe.execution_method_code =' P' 

      AND fe.application_id = fa.application_id

      AND fe.execution_file_name = 'XXSAMPLEFILE';                  --Your file name without extension

Happy learning!!

Split one column into multiple columns in Oracle apps

Hi! In this post, I will show you how to split up one column into multiple columns based on delimiters.

Let's say there is a column called Cardholder_name and it has values in the format last_name, first_name. In order to split them into two separate columns i.e., last_name and first_name separately, below syntax can be used.

Method 1:

SELECT REGEXP_SUBSTR(cardholder_name, '[^,]+', 1, 1) AS last_name, 

                REGEXP_SUBSTR(cardholder_name, '[^,]+', 1, 2) AS first_name,

   FROM xx_cardholder_tbl;

Method 2:

SELECT SUBSTR( cardholder_name, 1, INSTR(cardholder_name, ',')-1 ) last_name,

                SUBSTR( cardholder_name, INSTR(cardholder_name, ',')+1, INSTR(cardholder_name, ',',1,2)-INSTR(cardholder_name, ',')-1 ) first_name

   FROM xx_cardholder_tbl;                         --Here ',' is the delimiter.

Happy learning. Cheers!

Script to delete data definition and template in XML Publisher Reports in Oracle apps

Hi! This post will show you the script to delete the data definition and template in XML Publisher reports in Oracle apps.

Script to delete template and data definition in XML Publisher Reports:

BEGIN

DELETE from xdo_templates_b WHERE template_code = 'XXEMP';

DELETE from xdo_templates_tl WHERE template_code = 'XXEMP';

DELETE from xdo_lobs WHERE lob_code = 'XXEMP';

DELETE from xdo_ds_definitions_tl WHERE data_source_code = 'XXEMP';

DELETE from xdo_ds_definitions_b WHERE data_source_code = 'XXEMP';

COMMIT;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('An unknown error occurred '||SQLERRM);

END;

In the above script, XXEMP is the sample template or data definition code which is nothing but the concurrent program short name.

Happy Learning!!

Query to find order holds in Oracle apps R12

Hi! This post is to show the query to find holds on sales orders in Oracle apps R12.

Below is the query that you can use to find holds on a specific order:

SELECT ooh.order_number,

       ohd.hold_id,

       ohd.name hold_name

FROM oe_order_headers_all ooh,

     oe_order_holds_all ooha,

     oe_hold_sources_all ohs,

     oe_hold_definitions ohd

WHERE ooh.header_id = ooha.header_id

  AND ohs.hold_id = ohd.hold_id

  AND ohs.hold_source_id = ooha.hold_source_id

  AND ooh.order_number = <order_number>;

Happy learning!!