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!!