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