FND-CP-ESP: Child: exec:: No such file or directory

There could be 2 reasons this error could arise,

1. No Proper Symbolic link created or
    To resolve this create a Symbolic Link as below, without the file extension

    ln –s $FND_TOP/bin/fndcpesr $XXXX_TOP/bin/XXXXXXX

    XXXX – Custom TOP Name
    XXXXXXX – Executable name of the Program

2. The PROG File that’s created might have accidentally converted to DOS due to File Copy Mode.
  
    To resolve this, Convert the File to UNIX fileformat as below,
    dos2unix <FileName>.prog

How to update Oracle Application front end user password from backend

DECLARE
   lb_status   BOOLEAN;
BEGIN
   lb_status :=
      FND_USER_PKG.CHANGEPASSWORD (
         username      => 'GIRIDHART',
         newpassword   => 'welcome12345');
   IF lb_status
   THEN
      DBMS_OUTPUT.PUT_LINE ('Request processed sucessfully');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Error while processing the request');
   END IF;
END;

How to get the Oracle Application front end User password

First create below mentioned package in database and then run the following query.

--Package Specification
CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;

/

--Package Body

CREATE OR REPLACE PACKAGE BODY get_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2

   AS

      LANGUAGE JAVA

      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) 

      RETURN java.lang.String';

   END decrypt;

END get_pwd;

/

-- After compiling the above package please run below query by passing user name.

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 = 'GIRIDHART'; -- pass user name here

Oracle Application User Creation from backend

DECLARE
   l_user_id   NUMBER;
   l_user      VARCHAR2 (100) := 'GIRIDHART';           --Username goes here..
BEGIN
   Fnd_User_Pkg.CreateUser (l_user, 'CUST', 'welcome');
/* Validating the user*/
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user;

   Fnd_User_Resp_Groups_Api.Insert_Assignment (l_user_id,
                                               20420, -- System Administrator responsibility
                                               1,
                                               0,
                                               SYSDATE - 30,
                                               NULL,
                                               NULL);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error - ' || SUBSTR (SQLERRM, 1, 300));
END;

How to Extract a value from XML file by using Oracle SQL

Extract(XML) function is used to get a node or nodes inside a XML document stored in Oracle DB. The syntax for EXTRACT function is;

EXTRACT(XML-Document-Column, ‘XPath expression‘)

EXTRACT(XML-Document-Column, ‘XPath expression’, ‘namespace’)

Example usages;

CREATE TABLE LIBRARY
(
ID_COLUMN NUMBER PRIMARY KEY,
XML_DATA_COLUMN XMLType
);

INSERT INTO LIBRARY(ID_COLUMN ,XML_DATA_COLUMN) VALUES
( 1
, XMLType(‘<?xml version=”1.0″ encoding=”UTF-8″?>
<LIBRARY>
    <BOOKS>
        <BOOK isbn=”ABCD7327923″>
            <NAME>Java Programing</NAME>
            <SUBJECT>Java J2EE</SUBJECT>
            <AUTHORS>
                <AUTHOR>Tuna TORE</AUTHOR>
                <AUTHOR>Linus Torvalds</AUTHOR>
                <AUTHOR>James Gosling</AUTHOR>
            </AUTHORS>
        </BOOK>
        <BOOK isbn=”DFGH09093232″>
            <NAME>XPATH for Dummies</NAME>
            <SUBJECT>XPATH development</SUBJECT>
            <AUTHORS>
                <AUTHOR>Linus Torvalds</AUTHOR>
                <AUTHOR>John Hawking</AUTHOR>
            </AUTHORS>
        </BOOK>
        <BOOK isbn=”DSKL2393A”>
            <NAME>J2EE Patterns</NAME>
            <SUBJECT>Design Patterns</SUBJECT>
            <AUTHORS>
                <AUTHOR>Aka Tuna</AUTHOR>
            </AUTHORS>
        </BOOK>
    </BOOKS>
    <DVDS>
        <DVD id=”123456″>
            <NAME>Music DVD</NAME>
            <CONTENT>Music</CONTENT>
            <AUTHORS>
                <AUTHOR>James Gosling</AUTHOR>
                <AUTHOR>Bill Gates</AUTHOR>
            </AUTHORS>
        </DVD>
        <DVD id=”3213324″>
            <NAME>Natural Science</NAME>
            <CONTENT>Science</CONTENT>
            <AUTHORS>
                <AUTHOR>John Green</AUTHOR>
                <AUTHOR>Bill Gates</AUTHOR>
            </AUTHORS>
        </DVD>
        <DVD id=”4353534″>
            <NAME>Rally</NAME>
            <CONTENT>Race</CONTENT>
            <AUTHORS>
                <AUTHOR>Tuna</AUTHOR>
            </AUTHORS>
        </DVD>
    </DVDS>
</LIBRARY>
‘));

–you can get all DVDs in the library with the following query
SELECT ID_COLUMN, EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD‘)
FROM LIBRARY

–result will be

<DVD id=”123456″>
    <NAME>Music DVD</NAME>
    <CONTENT>Music</CONTENT>
    <AUTHORS>
        <AUTHOR>James Gosling</AUTHOR>
        <AUTHOR>Bill Gates</AUTHOR>
    </AUTHORS>
</DVD>
<DVD id=”3213324″>
    <NAME>Natural Science</NAME>
    <CONTENT>Science</CONTENT>
    <AUTHORS>
        <AUTHOR>John Green</AUTHOR>
        <AUTHOR>Bill Gates</AUTHOR>
    </AUTHORS>
</DVD>
<DVD id=”4353534″>
    <NAME>Rally</NAME>
    <CONTENT>Race</CONTENT>
    <AUTHORS>
        <AUTHOR>Tuna</AUTHOR>
    </AUTHORS>
</DVD>

–or you can get a specific DVD with the following query DVD having id –> 4353534
SELECT ID_COLUMN, EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=4353534]‘)
FROM LIBRARY

— the result is
<DVD id=”4353534″>
    <NAME>Rally</NAME>
    <CONTENT>Race</CONTENT>
    <AUTHORS>
        <AUTHOR>Tuna</AUTHOR>
    </AUTHORS>
</DVD>

You can also use the EXTRACTVALUE function for getting only the value inside XML tags
If you want to get value (name) for the DVD having id number –> 3213324

SELECT ID_COLUMN, EXTRACTVALUE(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]/NAME‘)
FROM LIBRARY

— the result is
Natural Science

And if you want to get XML tags for the above operation use EXTRACT instead of using EXTRACTVALUE

SELECT ID_COLUMN, EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]/NAME‘)
FROM LIBRARY

— the result is
<NAME>Natural Science</NAME>

You can also use EXTRACTVALUE in the WHERE clause of a SQL
like the following query

SELECT EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]‘) FROM LIBRARY
WHERE EXTRACTVALUE(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]/NAME‘) = ‘Natural Science’;

— the result is
<DVD id=”3213324″>
    <NAME>Natural Science</NAME>
    <CONTENT>Science</CONTENT>
    <AUTHORS>
        <AUTHOR>John Green</AUTHOR>
        <AUTHOR>Bill Gates</AUTHOR>
    </AUTHORS>
</DVD>

How to Kill Session in SQL

First you need to identify which object is locked and then identify session id.

For identifying session run below query.

SELECT O.OBJECT_NAME,
       S.SID,
       S.SERIAL#,
       P.SPID,
       S.PROGRAM,
       SQ.SQL_FULLTEXT,
       S.LOGON_TIME
  FROM V$LOCKED_OBJECT L,
       DBA_OBJECTS O,
       V$SESSION S,
       V$PROCESS P,
       V$SQL SQ
 WHERE     L.OBJECT_ID = O.OBJECT_ID
       AND L.SESSION_ID = S.SID
       AND S.PADDR = P.ADDR
       AND S.SQL_ADDRESS = SQ.ADDRESS
       AND o.object_name = <object name>;

Use below statement to kill the session.

  ALTER SYSTEM KILL SESSION '<sid>,<serial#>'

Regular queries in oracle apps

Find Directory where Trace Files are Stored:

SELECT value

FROM     v$parameter

WHERE  name = ‘user_dump_dest’;

User_dump_dest  is an initialization parameter defined in the init.ora file.

To check if multi-org is setup

Select multi_org_flag from FND_PRODUCT_GROUPS;

To find out details of concurrent request submitted with certain conditions

  SELECT request_id,

         req.CONCURRENT_PROGRAM_ID,

         prog.DESCRIPTION,

         Request_date,

         printer,

         number_of_copies,

         usr.user_name

    FROM fnd_concurrent_requests req,

         fnd_concurrent_programs_tl prog,

         fnd_user usr

   WHERE     printer <> 'noprint'

         AND requested_start_date > '14-AUG-06'

         AND status_code = 'C'

         AND number_of_copies > 0

         AND prog.CONCURRENT_PROGRAM_ID =req.CONCURRENT_PROGRAM_ID

         AND prog.language = 'US'

         AND req.requested_by = usr.user_id

ORDER BY request_date DESC

How To Compile/Generate A Report ( .RDF File) by using Unix Command

The following command will convert an .rdf report file to a .rex report file and back to a .rdf report file: r25convm 

syntax:

 ====

cd to where your .rdf file resides which is: /srw 

If your Application version is 10.7 /reports 

If your Application version is 11 Login as applmgr 

r25convm userid=/ source=.rdf stype=rdffile dtype=rexfile dest=.rex overwrite=yes batch=yes r25convm userid=/ source=.rex stype=rexfile dtype=rdffile dest=.rex overwrite=yes batch=yes 

 ie: r25convm FNDSCURS.rdf 

 Note: userid is the schema owner; 

 If the report is a GL report then the userid will be gl/gl 

 If the report is a FND report then the userid will be apps/apps 

 For the FNDSCURS.rdf example the report reside in: 

cd $FND_TOP/srw If your Application version is 10.7 cd $FND_TOP/reports 

 If your Application version is 11 Logon as applmgr The command line syntax is: 

 r25convm userid=apps/apps source=FNDSCURS.rdf stype=rdffile dtype=rexfile dest=FNDSCURS.rex overwrite=yes batch=yes 

 r25convm userid=apps/apps source=FNDSCURS.rex stype=rexfile dtype=rdffile dest=FNDSCURS.rdf overwrite=yes batch=yes 

 *** Note: You may get the following error; 

just ignore the error message, this *** is a known issue with the reports team. 

 REP-25200: Converting 'FNDSCURS.rex' to 'FNDSCURS.rdf'...

How to create a Collector in Oracle Applications

Setting up a User as a Collector in Collections

 1.      Make Sure they have an account

a.      System Admin > Security > Define > User

      2.      Add the user as an Employee

a.      Global HRMS Manager > People > Maintain

b.      Create Employment

c.       Enter User Details

d.      Save

      3.      Assign the Employee to a User Login Account

a.      System Admin > Security > Define > User

b.      Add Employee Name in Person field

c.       Save

      4.      Import Resource

a.      Turn off Custom Code: Help > Diagnostics > Custom Code

b.      CRM Resource Manager > Maintain Resources > Import Resources

c.       Search for your employee record

d.      Start Import

e.      Select the Role to assign to Apply (Collections Agent)

f.        Import and Save

g.      Turn on Custom Code

Query to identify profile values at different levels

SELECT po.profile_option_name "NAME",org.name name1,
pot.USER_PROFILE_OPTION_NAME,
        decode(to_char(pov.level_id),
               '10001', 'SITE',
               '10002', 'APP',
               '10003', 'RESP',
               '10005', 'SERVER',
               '10006', 'ORG',
               '10004', 'USER', '???') "LEV",
        decode(to_char(pov.level_id),
               '10001', '',
               '10002', app.application_short_name,
               '10003', rsp.responsibility_key,
               '10005', svr.node_name,
               '10006', org.name,
               '10004', usr.user_name,
               '???') "CONTEXT",
        pov.profile_option_value "VALUE"
FROM   apps.FND_PROFILE_OPTIONS po,
       apps.fnd_profile_options_tl pot,
        apps.FND_PROFILE_OPTION_VALUES pov,
        apps.fnd_user usr,
        apps.fnd_application app,
        apps.fnd_responsibility rsp,
        apps.fnd_nodes svr,
        apps.hr_all_organization_units org
WHERE  pot.profile_option_name LIKE 'XXG_GCC_PENDING_REQ_CHECK'
and    po.profile_option_name = pot.profile_option_name
AND    pov.application_id = po.application_id
AND    pov.profile_option_id = po.profile_option_id
AND    usr.user_id (+) = pov.level_value
AND    rsp.application_id (+) = pov.level_value_application_id
AND    rsp.responsibility_id (+) = pov.level_value
AND    app.application_id (+) = pov.level_value
AND    svr.node_id (+) = pov.level_value
AND    org.organization_id (+) = pov.level_value

ORDER BY "NAME", pov.level_id, "VALUE";