Thursday 24 November 2011

Registering AOL Objects through backend(Executable,Concurrent program etc...))

Registering Executable, Concurrent Program etc from backend

For Registering the Executable from backend.

PROMPT Creating Concurrent Executable XXM_XYZ_EMPLOYEE ......
PROMPT

BEGIN
FND_PROGRAM.executable('XXM_XYZ_EMPLOYEE' -- executable
, 'ABC' -- application
, 'ABC_XYZ_EMPLOYEE' -- short_name
, 'Executable for Migrating Employee' -- description
, 'PL/SQL Stored Procedure' -- execution_method
, 'abc_xyz_employee_pkg.create_employee' -- execution_file_name
, '' -- subroutine_name
, '' -- icon_name
, 'US' -- language_code
, '');

END;
/

For Registering the Concurrent program for the Executable file created.

PROMPT Creating Concurrent Program ABC_XYZ_EMPLOYEE ...
PROMPT

BEGIN
FND_PROGRAM.register('ABC Data migration program for HR-Employee' -- program
, 'XXM' -- application
, 'Y' -- enabled
, 'ABC_XYZ_EMPLOYEE' -- short_name
, 'Data Migration Program for Migrating HR-Employee' -- description
, 'ABC_XYZ_EMPLOYEE' -- executable_short_name
, 'XYZ' -- executable_application
, '' -- execution_options
, '' -- priority
, 'Y' -- save_output
, 'Y' -- print
, '' -- cols
, '' -- rows
, '' -- style
, 'N' -- style_required
, '' -- printer
, '' -- request_type
, '' -- request_type_application
, 'Y' -- use_in_srs
, 'N' -- allow_disabled_values
, 'N' -- run_alone
, 'TEXT' -- output_type
, 'N' -- enable_trace
, 'Y' -- restart
, 'Y' -- nls_compliant
, '' -- icon_name
, 'US'); -- language_code
END;
/

For attaching the concurrent program to the request group.

PROMPT Adding Concurrent program to request group 'XYZ Group'
PROMPT

BEGIN
FND_PROGRAM.add_to_group('XYZ_ABC_EMPLOYEE' -- program_short_name
, 'XYZ' -- application
, 'XYZ Group' -- Report Group Name
, 'XYZ'); -- Report Group Application

END;
/

Submit the Concurrent Program from Backend.

Submit Program is the Function. where. we need to pass the Application name, Program, stage etc.

function FND_SUBMIT.SUBMIT_PROGRAM
(application IN varchar2,
program IN varchar2,
stage IN varchar2,
argument1,...argument100)
return boolean;


PROMPT Submitting the Concurrent Program from backend.
PROMPT

BEGIN
--fnd_global.apps_initialize( user_id => , resp_id => , resp_appl_id => );

l_success := fnd_submit.set_request_set('', '');

IF l_success = TRUE
THEN


l_success := fnd_submit.submit_program(
'' --Application
,'' -- program
,'' --Stage
,'NEW' -- Arument1
,'10' -Arument2
,'N'
,'N'
,'N');

-- If fail then for the log record.

IF (NOT l_success)
THEN
fnd_file.put_line(
fnd_file.LOG
,'Request submission of stage STAGE FAILED');
ELSE
fnd_file.put_line(
fnd_file.LOG
,'Request submission of stage STAGE10 SUCCESSFUL');
END IF;
l_req_id := fnd_submit.submit_set(NULL, FALSE );

IF (l_req_id <= 0) THEN fnd_file.put_line( fnd_file.LOG ,'REQUEST SET SUBMISSION FAILED'); ELSE fnd_file.put_line( fnd_file.LOG ,'REQUEST SET SUBMITTED SUCCESSFULLY: ' || l_req_id); END IF; END IF; END;


I hope the above document is helpful to all of you for understanding the backend process and API's to register the program and submit from the backend.

Tuesday 8 November 2011

Query to link a Responsibility to a Set of Books/Operating Unit in Oracle


--- Link to Set of Books
SELECT fr.responsibility_name, fpov.profile_option_value set_of_books_name
FROM fnd_profile_options_vl fpo,
fnd_profile_option_values fpov,
applsys.fnd_responsibility_tl fr
WHERE fpo.user_profile_option_name = 'GL Set of Books Name'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_value = fr.responsibility_id


--- Link to Operating Unit
SELECT fr.responsibility_name, fpov.profile_option_value orgid, NAME org_name
FROM fnd_profile_options_vl fpo,
fnd_profile_option_values fpov,
applsys.fnd_responsibility_tl fr,
hr_operating_units hou
WHERE UPPER (fpo.user_profile_option_name) LIKE UPPER ('MO%OPERATIN%')
AND profile_option_name = 'ORG_ID'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_value = fr.responsibility_id
AND hou.organization_id = fpov.profile_option_value