Find out Oracle Session for a Concurrent Request

Find out Oracle Session for a Concurrent Request SELECT a.request_id, d.sid, d.serial# , c.SPID FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id = d.audsid AND a.request_id =

Reset password from backend in Oracle APPS

declare
v_user_name varchar2(30):=upper('USER');
v_new_password varchar2(30):='PASSWD';
v_status boolean;
begin
v_status:= fnd_user_pkg.ChangePassword (
username => v_user_name,
newpassword => v_new_password
);
if v_status =true then
dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
commit;
else
DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
rollback;
END if;
end;

convert columns to rows in 11g

I found an interesting feature of 11g today while trying to convert columns to rows:


There is a SELECT ... UNPIVOT in 11i that we can use like this



SELECT ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10
FROM CZ_CONFIG_ATTRIBUTES
WHERE CONFIG_HDR_ID = 1048 AND ATTRIBUTE_CATEGORY = 'ST_KEYS'

ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10
KAQTY          0                      No                   No                   No


Now using UNPIVOT


SELECT ATTR, ATTR_VAL
FROM CZ_CONFIG_ATTRIBUTES
UNPIVOT INCLUDE NULLS
(attr_val FOR attr
IN ( ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10
)
)
WHERE CONFIG_HDR_ID = 1048 AND ATTRIBUTE_CATEGORY = 'ST_KEYS'
ORDER BY attr;



ATTR                       ATTR_VAL
ATTRIBUTE10        No
ATTRIBUTE6          KAQTY
ATTRIBUTE7          0
ATTRIBUTE8          No
ATTRIBUTE9          No

How to debug OE messages

How to generate this file.




1. Set "OM: Debug Log" => 5 at user level

2. Query the order, Go to lines

3. Tools >  Enable Debug, Which will give a file name. Please note down the file name

4. Re-Price the line or some activity

5. Turn off the debug option

6. Run the program called "Diagnostics: OM Debug File Retrieval", Which will give you the file in output file

Get Legal entity

SELECT


hr.name operating_unit,

hr.organization_id org_id,

le.name legal_entity_name,

decode(ar.set_of_books_id, '-1' ,'No', NULL,'No','Yes') system_option_defined

FROM

hr_operating_units hr,

hr_all_organization_units_tl le,

ar_system_parameters_all ar

WHERE le.organization_id = to_number(hr.legal_entity_id)

AND le.LANGUAGE = userenv('LANG')

AND hr.organization_id = ar.org_id(+)

ORDER BY 2

How to fix: error "The Function Is Not Available Under The Responsibility"

Sometimes after logging into application, we get error "The Function Is Not Available Under The Responsibility ". This is applicable to 11.5.10 only.

Please perform following steps to fix the issue -

1) Manually run the 'Compile Security' concurrent program, set the parameter to YES.

2) Shut down Middle Tier (Apache)

3) Delete the contents of $OA_HTML/_pages directory (11.5.10 only).

4) Restart Middle tier

Metalink Reference: The Function Is Not Available Under The Responsibility [ID 454285.1]

FSG Transfer Program - To transfer FSG definitions from source to target database

• Database links must be available

• Copy is initiated from target database, not source

• Any budgets, currencies referenced must exist in target database

Navigate to Submit concurrent request window and select ‘Program - FSG Transfer’

Parameters:

- Component Type – Pick one or ALL

- Component Name – Leave blank to copy all of a type or ALL

- Source DB Chart of Accounts – must be typed and must match exactly as no way to validate this field

- Target DB Chart of Accounts – choose from LOV

- Source Database – choose from LOV

• If object with same name already exists, no copy will be done and warning message will be printed

• If currency/budgets/encumbrances do not exist, copy will be performed, but references will be removed from the copied report

• If program is interrupted, can be re-started with same parameters, objects already copied will just give warning message