Oracle Apps Technical: 2017

Query to get all Org attached to responsibility

SELECT responsibility_name,
(SELECT name
FROM hr_all_organization_units
WHERE organization_id = fpov.profile_option_value)
operating_unit
FROM applsys.fnd_profile_option_values fpov,
applsys.fnd_profile_options fpo,
applsys.fnd_profile_options_tl fpot,
applsys.fnd_responsibility_tl fr
WHERE 1 = 1
AND fpo.profile_option_name = fpot.profile_option_name
AND fpo.profile_option_id = fpov.profile_option_id
AND fr.responsibility_id(+) = fpov.level_value
-- AND fr.responsibility_id = :$PROFILES$.RESP_ID
AND fpot.profile_option_name = 'ORG_ID'
-- and fpov.profile_option_value='11'
--AND fr.responsibility_name LIKE '%Payables%Manager%'
AND fr.language = 'US'
AND fpot.language = 'US'
ORDER BY 2

sending report output (PDF) as attachment in customizable email

1. Create RDF file using the attached template (XXTEST_REP.rdf)
2. You have to rename the file name to your report file name.
3. You have to set the following formulas inside the RDF file (PLSQL Formula).

4. Use the BODY_TEXT formula to return the email HTML body.
5. You can customize FORMAT_BODY function to customize body text using HTML tags.
6. Use PUT_LINE to add new text to email body ends with BR(Enter).
7. Use NEW_LINE function to add new empty line to email body.
8. You have to set attachment file name using REPORT_NAME.
9. You have to copy RTF file to the same path with RDF file on the server.
10. You have to set report application top inside RTF_FILE_PATH formula (FND_TOP in our sample and PER_TOP for human resources application,…..etc).

11. You have to set the RTF file path/name on the server using RTF_FILE_PATH.

12. You have to change the following query without changing the group name (G_BURSTING).

13. Upload the attached bursting control file (BURSTING_CONTROL.xml) when you define the template data definition (Update Mode) .

BURSTING_CONTROL.xml:

<xapi:requestset type="bursting" xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">

<xapi:request select="/XXTEST_REP/G_BURSTING">

<xapi:delivery>

<xapi:email reply-to="" from="erp@xyz.com" port="25" server="10.10.100.41">

<xapi:message subject="${EMAIL_SUBJECT}" content-type="text/html" attachment="true" to="${SEND_TO}" cc="${CC_SEND_TO}" id="123">

<![CDATA[<HTML> <BODY> ${BODY_TEXT}</BODY></HTML> ]]>

</xapi:message>

</xapi:email>

</xapi:delivery>

<xapi:document delivery="123" output-type="pdf" output="${REPORT_NAME}">

<xapi:template type="rtf" location="${RTF_FILE_PATH}"> </xapi:template>

</xapi:document>

</xapi:request>

</xapi:requestset>

Query to get Oracle apps users password

Sometimes you need to get the password for specific oracle apps user, firstly you should create the below Package:

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

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 get_pwd;

Query:
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';

Oracle Apps Technical

Translate

Sunday, December 24, 2017

Query to get all Org attached to responsibility

sending report output (PDF) as attachment in customizable email

Query to get Oracle apps users password

Featured Post

To Add user Responsibility like another user

Zain