Subject: How to print error/success transaction detail in log and output file of the concurrent program.
Resolution:
We can use the below syntax in plsql code to generate log and output file with transaction details. User can append any number of transaction fields in the same syntax line using pipe seperator.
--FND Log--
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: '||l_error);
--FND Output--
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,xxtlm_rec.inventory_item_id||' Item Updated Successfully');
--Major Category --
SELECT fc.segment1,(SELECT
ffvt.description
FROM fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
AND ffvs.flex_value_set_name='FA_MAJOR_CATEGORY_VS' --Enter Major Category Value Set Name
AND ffv.flex_value=fc.segment1) DESC
FROM fa_categories_vl
--Minor Category--
SELECT fc.segment1,(SELECT
ffvt.description
FROM fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV('LANG')
AND ffvs.flex_value_set_name='FA_MAJOR_CATEGORY_VS' --Enter Minor Category Value Set Name
AND ffv.flex_value=fc.segment2) DESC
FROM fa_categories_vl
SELECT DISTINCT
pot.profile_option_name "PROFILE_CODE",
pot.user_profile_option_name "PROFILE_NAME",
decode(a.level_id, 10001, 'Site', 10002, 'Application',
10003,
'Responsibility',
10004,
'User',
10005,
'Server',
10006,
'Organization',
a.level_id) "LEVEL_IDENTIFIER",
decode(a.level_id, 10002, e.application_name, 10003, c.responsibility_name,
10004,
d.user_name,
10005,
f.host
|| '.'
|| f.domain,
10006,
g.name,
'-') "LEVEL_NAME",
decode(a.profile_option_value, '1', '1 (may be "Yes")', '2',
'2 (may be "No")',
a.profile_option_value) "PF_VALUE"
FROM
fnd_application_tl e,
fnd_user d,
fnd_responsibility_tl c,
fnd_profile_option_values a,
fnd_profile_options b,
fnd_profile_options_tl pot,
fnd_nodes f,
hr_all_organization_units g
WHERE
1 = 1
--AND UPPER(pot.USER_PROFILE_OPTION_NAME) LIKE UPPER('MO: Default Operating Unit')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id = a.application_id (+)
AND b.profile_option_id = a.profile_option_id (+)
AND a.level_value = c.responsibility_id (+)
AND a.level_value = d.user_id (+)
AND a.level_value = e.application_id (+)
AND a.level_value = f.node_id (+)
AND a.level_value = g.organization_id (+)
AND pot.language = 'US'
--AND POT.User_profile_option_name ='AHL: Application Usage Mode'
ORDER BY
profile_name,
level_identifier,
level_name,
pf_value;