Oracle Apps -Learn and grow: 2021

How to find password of a User in Oracle Apps R12 - (Decrypting Password)

To achieve this you need to create a small package and run a query :

Package Specification :

CREATE OR REPLACE PACKAGE get_user_pwd

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2;

END get_user_pwd;
/

Package Body:

CREATE OR REPLACE PACKAGE BODY get_user_pwd

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2

LANGUAGE JAVA

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

END get_user_pwd;

Query to execute :

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,

INSTR

(fnd_web_sec.get_guest_username_pwd,

'/'

)

- 1

)

FROM DUAL)),

usr.encrypted_user_password

) PASSWORD

FROM fnd_user usr

WHERE usr.user_name = ':USER_NAME';
;

Create and Drop Sequences in Oracle

In Oracle, you can create an autonumber field using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Syntax :

The syntax to create a sequence in Oracle:

CREATE SEQUENCE Sequence_Name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

Example :

CREATE SEQUENCE XX_PO_SEQ
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 50 / NONCACHE;

Note :

If you exclude the MAXVALUE option, your sequence will automatically default to: MAXVALUE 999999999999999999999999999

The cache option specifies how many sequence values will be stored in memory for faster access. The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values.

Retrieve the next value in the sequence :

To retrieve next value in sequence user Sequence_Name.NEXTVAL
INSERT INTO XX_PO_TABLE
(PO_ID, PO_NUMBER)
VALUES
(XX_PO_SEQ.NEXTVAL, 'PO-1234');

Check Cuurent value in the sequence :

To Check current value in sequence user Sequence_Name.CURRVAL
Select XX_PO_SEQ.NEXTVAL from dual;

Drop Sequence :

Syntax :
The syntax to a drop a sequence in Oracle is:

DROP SEQUENCE sequence_name;

DROP SEQUENCE XX_PO_SEQ;

Note :

To recover the lost sequence values or skip some values you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.

Query to get concurrent program run details in oracle apps

SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,
a.request_date,
a.actual_start_date,
a.actual_completion_date,
(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name,
a.phase_code,
a.status_code,
a.argument_text,
a.priority
FROM apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id
AND b.concurrent_program_id=c.concurrent_program_id
AND a.requested_by=d.user_id
and d.user_id=:P_USER_ID
x

P2P Accounting entries in Oracle

In Oracle purchasing 3 different events occur

1. Enter Purchase Order:
    No accounting takes place

2. Enter a Receipt:
    For Inventory, Asset and Expense Items:
        Dr: Inventory Receiving A/c
        Cr: Accrual A/c

3.Receiving Transaction:
   Based on type of item here accounting entry differs
   Inventory Item:
        Dr: Inventory Valuation A/c
        Cr: Inventory Receiving A/c
   Expense Item:
        Dr: Expense A/c
        Cr: Inventory Receiving A/c
  Asset Item:
        Dr: Asset Clearing A/c
        Cr: Inventory Receiving A/c

Oracle Payable:

In payable 3 events occur
1. Enter Invoice:
        Dr: Accrual A/c
        Cr: Liability A/c

2.Payment against Invoice:
        Dr: Liability A/c
        Cr: Bank clearing A/c

3.Clearing (Cash Management):
        Dr: Bank clearing A/c
        Cr: Bank A/c

Oracle P2P Accounting entries

Incase below type of items one more accounting entry happens in Oracle

Inventory Item: Item issued to Dept.
       Dr: Expense A/c
       Cr: Inventory valuation

Asset Item: Asset is capitalized
       Dr: Asset A/c
       Cr: Asset clearing A/c

Query to get details of XML/BI Publisher Template and Data Definition

SELECT XDDV.data_source_code "Data Definition Code",

XDDV.data_source_name "Data Definition",

XDDV.description "Data Definition Description",

XTB.template_code "Template Code",

XTT.template_name "Template Name",

XTT.description "Template Description",

XTB.template_type_code "Type",

XTB.default_output_type "Default Output Type",

xldd.FILE_NAME "Data Template File Name",

XLtemp.file_name "Template File Name",

xlbur.FILE_NAME "Bursting File Name"

FROM apps.XDO_DS_DEFINITIONS_VL XDDV,

apps.XDO_TEMPLATES_B XTB,

apps.XDO_TEMPLATES_TL XTT,

apps.XDO_LOBS XLTemp,

apps.XDO_LOBS XLDD,

apps.XDO_LOBS XLBUR,

apps.FND_APPLICATION_TL FAT,

APPS.FND_APPLICATION FA

WHERE XDDV.application_short_name = FA.application_short_name

AND FAT.application_id = FA.application_id

AND XTB.application_short_name = XDDV.application_short_name

AND XDDV.data_source_code = XTB.data_source_code

AND XTT.template_code = XTB.template_code

AND XTT.language = 'US'

AND fat.language = 'US'

AND XLtemp.LOB_CODE = XTB.TEMPLATE_CODE

AND XLtemp.XDO_FILE_TYPE = XTB.TEMPLATE_TYPE_CODE

AND xldd.LOB_CODE = XDDV.data_source_code

AND xldd.LOB_TYPE = 'DATA_TEMPLATE'

AND xlbur.LOB_CODE(+) = XDDV.data_source_code

AND xlbur.LOB_TYPE(+) = 'BURSTING_FILE'

AND XTT.TEMPLATE_CODE IN (:XXTEMPLATE_CODE)

Query to find the output file name and path

SELECT outfile_name output_file_path
FROM
fnd_concurrent_requests
WHERE
request_id = :p_request_id