Query to find the Request Group of a Concurrent Program

SELECT
  RG.APPLICATION_ID "Request Group Application ID",
  RG.REQUEST_GROUP_ID "Request Group - Group ID",
  RG.REQUEST_GROUP_NAME,
  RG.DESCRIPTION,
  rgu.unit_application_id,
  rgu.request_group_id "Request Group Unit - Group ID",
  rgu.request_unit_id,cp.concurrent_program_id,
  cp.concurrent_program_name,
  cpt.user_concurrent_program_name,
  DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
  fnd_request_groups rg,
  fnd_request_group_units rgu,
  fnd_concurrent_programs cp,
  FND_CONCURRENT_PROGRAMS_TL CPT
WHERE rg.request_group_id = rgu.request_group_id
  AND rgu.request_unit_id = cp.concurrent_program_id
  AND cp.concurrent_program_id = cpt.concurrent_program_id
  AND cpt.user_concurrent_program_name =:p_conc_prog_name;

TO OPEN MONTHLY PERIODS OF DIFFERENT MODULES OR NEXT YEAR AND FOLLOW THE BELOW STEPS

1. HOW TO OPEN INVENTORY PERIODS (INV) :

Follow the below given navigation path :

INVENTORY - > ORACLE INVENTORY - > ACCOUNTING CLOSE CYCLE - > INVENTORY ACCOUNTING PERIODS

2. HOW TO OPEN PAYABLE PERIODS (AP):

Follow the below given navigation path :

PAYABLES - > ORACLE PAYABLES - > ACCOUNTING - > CONTROL PAYABLES PERIODS

3. HOW TO OPEN RECEIVABLE PERIODS (AR) :

Follow the below given navigation path :

RECEIVABLES - > ORACLE RECEIVABLES - > CONTROL - > ACCOUNTING - > OPEN/CLOSE PERIODS

4. HOW TO OPEN GENERAL PERIODS (GL) :

Follow the below given navigation path :

GENERAL SUPER LEDGER - > SETUP - > OPEN/CLOSE

From Back End :
---------------------

SELECT (SELECT sob.NAME
FROM gl.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id) "SOB_Name",
a.period_name "Period_Name", a.period_num "Period_Num",
a.gl_status "GL_Status", b.po_status "PO_Status",
c.ap_status "AP_Status", d.ar_status "AR_Status",
e.fa_status "FA_Status"
FROM (SELECT period_name, period_num,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) gl_status,
set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 101
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) a,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) po_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 201
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) b,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ap_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 200
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) c,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
closing_status
) ar_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 222
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob') d,
(SELECT fdp.period_name,
DECODE (fdp.period_close_date,
NULL, 'Open',
'Closed'
) fa_status,fbc.set_of_books_id
FROM fa.fa_book_controls fbc, fa.fa_deprn_periods fdp
WHERE fbc.set_of_books_id ='&sob'
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
WHERE a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.set_of_books_id=b.set_of_books_id(+)
and a.set_of_books_id=c.set_of_books_id(+)
and a.set_of_books_id=d.set_of_books_id(+)
and a.set_of_books_id=e.set_of_books_id(+)
ORDER BY 1

Decriptive flexfield registration details

SELECT title
, application_table_name
, descriptive_flexfield_name
, context_column_name
FROM fnd_descriptive_flexs_vl
WHERE title = :p_title

2.DECRIPTIVE FLEXFIELD CONTEXT DETAILS

SELECT descriptive_flex_context_code
, descriptive_flex_context_name
, description
, enabled_flag
, application_id
, descriptive_flexfield_name
, global_flag
FROM fnd_descr_flex_contexts_vl
WHERE descriptive_flexfield_name = p_descriptive_flexfield_name

To findConcurrent program Details based on the request ID

select
prog.user_concurrent_program_name "program name",
prog.concurrent_program_name "program short name",
appl.application_name "program application name",
prog.description "program description",
exe.executable_name "executable name",
exe.execution_file_name "executable file name",
decode( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader','Q','SQL*Plus', exe.execution_method_code) "execution method"
from
fnd_executables exe,
fnd_application_tl appl,
fnd_concurrent_programs_vl prog
where exe.application_id = appl.application_id
AND exe.executable_id = prog.executable_id
AND appl.language='US'
AND prog.user_concurrent_program_name =:p_conc_prog_name 

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.

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

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

How to Define Apps User through back end?

This summary is not available. Please click here to view the post.