Category: Uncategorized

Adding Responsibility to a user from backend

Begin
fnd_user_pkg.addresp (‘YOUR_USER_NAME’,’SYSADMIN’,’SYSTEM_ADMINISTRATOR’,’STANDARD’,’add responsibility to user using pl/sql’,sysdate-1,sysdate + 100);
commit;
dbms_output.put_line(‘responsibility added successfully to the user ‘);
exception
when others then
dbms_output.put_line(‘ responsibility adding failed due to’ || sqlcode || substr(sqlerrm, 1, 100));
rollback;
end;

–You can view the changes immediately.

Query to find concurrent program parameters and associated value sets

SELECT fcpl.user_concurrent_program_name “Concurrent Program Name”,
fcp.concurrent_program_name “Program Short Name”,
fdfcuv.column_seq_num “Column Seq #”,
fdfcuv.end_user_column_name “Parameter Name”,
fdfcuv.form_left_prompt “Prompt Name”,
fdfcuv.enabled_flag “Enabled Flag”,
fdfcuv.required_flag “Required Flag”,
fdfcuv.display_flag “Display Flag”,
fdfcuv.flex_value_set_id “Value Set ID”,
ffvs.flex_value_set_name “Value Set Name”,
flv.meaning “Default Type”,
fdfcuv.default_value “Default Value”
FROM fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fdfcuv.descriptive_flexfield_name = ‘$SRS$.’ || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = ‘FLEX_DEFAULT_TYPE’
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.LANGUAGE = USERENV(‘LANG’)
AND flv.LANGUAGE(+) = USERENV(‘LANG’)
AND fdfcuv.enabled_flag = ‘Y’
and FCPL.USER_CONCURRENT_PROGRAM_NAME = :pconcurrent_program
ORDER BY fdfcuv.column_seq_num;

Concurrent program running between particular date and their incompatible programs

SELECT distinct FU.USER_NAME,
papf.full_name,
FCR.REQUEST_ID,
FCR.ACTUAL_START_DATE,
FCR.ACTUAL_COMPLETION_DATE,
round( ((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)*24*60),2 )RUN_TIME_Mins,
FCR.IS_SUB_REQUEST,
fcr.parent_request_id,
FCR.HAS_SUB_REQUEST,
FCPT.USER_CONCURRENT_PROGRAM_NAME,
B.USER_CONCURRENT_QUEUE_NAME MANAGER,
FCPT2.USER_CONCURRENT_PROGRAM_NAME INCOMPATIBLE_TO,
fcs.incompatibility_type
FROM APPS.FND_CONCURRENT_REQUESTS FCR,
APPS.FND_USER FU,
APPS.PER_ALL_PEOPLE_F PAPF,
APPS.FND_CONCURRENT_PROCESSES a,
APPS.FND_CONCURRENT_QUEUES_VL B,

APPS.FND_CONCURRENT_PROGRAMS_TL FCPT,
APPS.FND_CONCURRENT_PROGRAMS_TL FCPT2,
APPS.FND_CONCURRENT_PROGRAM_SERIAL FCS
where FCR.ACTUAL_START_DATE >=:P_START_DATE
AND fcr.ACTUAL_COMPLETION_DATE <=:p_completion_date
AND FCR.REQUESTED_BY =FU.USER_ID
AND FU.USER_ID =PAPF.PERSON_ID
and FCR.REQUESTED_BY =PAPF.PERSON_ID
and a.CONCURRENT_QUEUE_ID = B.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = FCR.CONTROLLING_MANAGER
and FCPT.CONCURRENT_PROGRAM_ID =FCR.CONCURRENT_PROGRAM_ID
and FCS.RUNNING_CONCURRENT_PROGRAM_ID=FCPT.CONCURRENT_PROGRAM_ID
and FCS.TO_RUN_CONCURRENT_PROGRAM_ID=FCPT2.CONCURRENT_PROGRAM_ID
and FCPT.LANGUAGE=’US’
and fcpt2.language=’US’
AND fcr.REQUEST_ID = FCR.REQUEST_ID

Concurrent Program Executable

SELECT DISTINCT fcpt.user_concurrent_program_name ,
fcp.concurrent_program_name short_name ,
fat.application_name program_application_name ,
fet.executable_name ,
fat1.application_name executable_application_name ,
flv.meaning execution_method ,
fet.execution_file_name ,
FCP.ENABLE_TRACE
FROM APPS.FND_CONCURRENT_PROGRAMS_TL FCPT ,
APPS.FND_CONCURRENT_PROGRAMS FCP ,
APPS.FND_APPLICATION_TL FAT ,
APPS.FND_EXECUTABLES FET ,
APPS.FND_APPLICATION_TL FAT1 ,
apps.FND_LOOKUP_VALUES FLV
WHERE 1 =1
AND fcpt.user_concurrent_program_name=:p_program_name
AND fcpt.concurrent_program_id = fcp.concurrent_program_id
AND fcpt.application_id = fcp.application_id
AND fcp.application_id = fat.application_id
AND fcpt.application_id = fat.application_id
AND fcp.executable_id = fet.executable_id
AND fcp.executable_application_id = fet.application_id
AND fet.application_id = fat1.application_id
AND FLV.LOOKUP_CODE = FET.EXECUTION_METHOD_CODE
AND FCPT.LANGUAGE =FAT.LANGUAGE
AND FCPT.LANGUAGE =Flv.LANGUAGE
AND fcpt.language =userenv(‘LANG’)
AND FLV.LOOKUP_TYPE =’CP_EXECUTION_METHOD_CODE’ ;

List of Users Having A Particular Responsibility

SELECT FU.USER_NAME,
papf.full_name,
frt.responsibility_name,
TO_CHAR(furg.start_date,’DD-MON-YYYY’) start_date,
furg.end_date
FROM fnd_user fu ,
fnd_user_resp_groups_direct furg ,
FND_RESPONSIBILITY_VL FRT,
per_all_people_f papf
WHERE fu.user_id = furg.user_id
AND frt.responsibility_id = furg.responsibility_id
AND frt.application_id = furg.responsibility_application_id
AND NVL(furg.end_date,sysdate+1) > sysdate
AND NVL(frt.end_date,sysdate +1) > sysdate
and NVL(FU.END_DATE,sysdate +1) > sysdate
AND papf.person_id =fu.user_id
AND FRT.RESPONSIBILITY_NAME =’Application Developer’ ;

Finding Responsibilities assigned to a user

SELECT fu.user_name ,
frt.responsibility_name,
furg.start_date,
FURG.END_DATE
FROM APPS.FND_USER FU ,
APPS.FND_USER_RESP_GROUPS_DIRECT FURG ,
apps.fnd_responsibility_vl frt
WHERE fu.user_id = furg.user_id
AND frt.responsibility_id = furg.responsibility_id
AND frt.application_id = furg.responsibility_application_id
AND NVL(furg.end_date,sysdate+1) > sysdate
AND NVL(FRT.END_DATE,sysdate +1) > sysdate
AND FU.USER_NAME = :p_user_name;