Oracle Apps Technical

Sunday, December 24, 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';

Monday, August 29, 2016

Query to list all form personalization

This query is used to list all form personalization by passing form function name:

SELECT
FUNCTION_NAME,
FORM_NAME,
ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SEQUENCE,
RULE_KEY,
DESCRIPTION,
RULE_TYPE,
ENABLED,
TRIGGER_EVENT,
TRIGGER_OBJECT,
CONDITION,
FIRE_IN_ENTER_QUERY
FROM FND_FORM_CUSTOM_RULES
WHERE ( (function_name = 'PO_POXPOEPO' AND rule_type = 'A')
OR (form_name = 'POXPOEPO' AND rule_type = 'F'))
AND rule_key IS NULL
ORDER BY sequence

Thursday, August 25, 2016

How to purge e-mail notifications from the Workflow queue

Some times Due to large number of e-mail notifications to accumulated in the queue Workflow mailer will not start,To fix this issue we need purge the notifications from the Workflow queue.

The below outlines the steps, Please take proper backup before performing the below.

1) You need to update the notifications you do not want sent, in the WF_NOTIFICATIONS table.

2) Check the WF_NOTIFICATIONS table as below. Records where status = 'OPEN' and mail_status = 'MAIL' are notifications that will have an e-mail notification sent.

SQL> select notification_id,status,mail_status,begin_date from WF_NOTIFICATIONS where status = 'OPEN' and mail_status = 'MAIL';

3) This should show you which notifications are waiting to be e-mailed.

4) To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The mailer will think the e-mail has already been sent and it will not send it again.

SQL> update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

-> This will update all notifications waiting to be sent by the mailer.

5) Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

SQL>sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD APPLSYS

6) Now you can start your WF Containers and then Mailer

Wednesday, August 24, 2016

How to Enable Personalization link for OAF Pages

How to Enable Personalization link for OAF Pages

Set the values of following profiles to enable Personalization Page link in OAF Pages:
1. FND: Personalization Region Link Enabled = Yes
2. Personalize Self-Service Defn = Yes
3. Disable Self-Service Personal - No

After Setting the Values of above profiles:

Query to get GL drill down to AP

SELECT 'AP' source,
asup.segment1 vendor_number,
asup.vendor_name,
h.je_category,
h.je_source,
h.period_name je_period_name,
h.name journal_name,
h.status journal_status,
h.created_by je_created_by,
h.creation_date je_created_date,
glcc.concatenated_segments account,
l.description,
xlal.accounting_class_code,
xlal.description xlal_description,
xlal.accounting_date accounting_date,
xlate.transaction_number xlate_transaction_number,
gir.subledger_doc_sequence_value voucher_number,
h.currency_code entered_currencey,
(SELECT SUM (NVL (begin_balance_dr, 0))
- SUM (NVL (begin_balance_cr, 0))
FROM gl_balances gb
WHERE gb.code_combination_id = glcc.code_combination_id
AND gb.ledger_id = 2091
AND gb.actual_flag = 'A'
AND gb.period_name = NVL (:period, h.period_name)
AND gb.currency_code = h.currency_code)
begining_balance,
(SELECT SUM (NVL (period_net_dr, 0))
- SUM (NVL (period_net_cr, 0))
+ (SUM (NVL (begin_balance_dr, 0))
- SUM (NVL (begin_balance_cr, 0)))
FROM gl_balances gb
WHERE gb.code_combination_id = glcc.code_combination_id
AND gb.ledger_id = 2091
AND gb.actual_flag = 'A'
AND gb.period_name = NVL (:period, h.period_name)
AND gb.currency_code = h.currency_code)
ending_balance,
l.accounted_dr,
l.accounted_cr,
xlal.accounted_dr trx_accounted_dr_jod,
xlal.accounted_cr trx_accounted_cr_jod,
NVL (l.accounted_dr, 0) - NVL (l.accounted_cr, 0) balance,
NULL po_number,
NULL receipt_num,
(SELECT bank_name || ' - ' || bank_account_num_electronic
FROM ar_cash_receipts_v v, iby_ext_bank_accounts_v vv
WHERE vv.ext_bank_account_id = v.customer_bank_account_id
AND gir.subledger_doc_sequence_value =
v.document_number)
customer_bank,
(SELECT BANK_ACCOUNT_NAME || ' - ' || BANK_ACCOUNT_NUM
FROM ce_bank_acct_uses ba,
ce_bank_accounts cba,
ce_bank_branches_v bb,
ar_receipt_method_accounts v
WHERE bb.bank_name LIKE '%'
AND bb.bank_branch_name LIKE '%'
AND bb.bank_institution_type = 'BANK'
AND bb.branch_party_id = cba.bank_branch_id
AND cba.bank_account_id = ba.bank_account_id
AND cba.account_classification = 'INTERNAL'
AND ba.bank_acct_use_id = v.remit_bank_acct_use_id
AND CASH_CCID = glcc.code_combination_id
AND RECEIPT_METHOD_ID = 10000)
bank_account_name
FROM gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
gl_code_combinations_kfv glcc,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae,
xla.xla_transaction_entities xlate,
ap_suppliers asup
WHERE 1 = 1
AND glcc.chart_of_accounts_id = 50408
AND h.ledger_id = 2091
AND b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND xlal.code_combination_id = glcc.code_combination_id
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.event_id = xlae.event_id
AND xlae.entity_id = xlate.entity_id
AND xlae.application_id = xlate.application_id
AND h.je_source = 'Payables'
AND asup.vendor_id(+) = xlal.party_id
AND h.period_name = NVL (:period, h.period_name)
AND glcc.concatenated_segments BETWEEN NVL (
:p_min_flex,
glcc.concatenated_segments)
AND NVL (
:p_max_flex,
glcc.concatenated_segments)