[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select Max(Concurrent_Process_ID)
Into icm_cid
From Fnd_Concurrent_Processes
Where Process_Status_Code = 'A'
And (Queue_Application_ID = 0 And
Concurrent_Queue_ID = 1);
--Dev_Status := 'DELETED'; -- Was deleted when pending
Dev_Status := 'CANCELLED'; -- Was deleted when pending
Select Concurrent_Program_ID, P.Application_ID
Into Program_ID, Prog_Appl_ID
From Fnd_Concurrent_Programs P,
Fnd_Application A
Where Concurrent_Program_Name = Program
And P.Application_ID = A.Application_ID
And A.Application_Short_Name = Appl_ShortName;
Select Max(Request_ID)
Into Req_ID
From Fnd_Concurrent_Requests
Where Program_Application_ID = Prog_Appl_ID
And Concurrent_Program_ID = Program_ID;
Select Phase_Code, Status_Code, Completion_Text,
Phase.Lookup_Code, Status.Lookup_Code,
Phase.Meaning, Status.Meaning
Into req_phase, req_status, comptext,
phase_code, status_code,
phasem, statusm
From Fnd_Concurrent_Requests R,
Fnd_Concurrent_programs P,
Fnd_Lookups Phase,
Fnd_Lookups Status
Where
Phase.Lookup_Type = PHASE_LOOKUP_TYPE
AND Phase.Lookup_Code = Decode(Status.Lookup_Code,
'H', 'I',
'S', 'I',
'U', 'I',
'M', 'I',
R.Phase_Code) AND
Status.Lookup_Type = STATUS_LOOKUP_TYPE AND
Status.Lookup_Code =
Decode(R.Phase_Code,
'P', Decode(R.Hold_Flag, 'Y', 'H',
Decode(P.Enabled_Flag, 'N', 'U',
Decode(Sign(R.Requested_Start_Date - SYSDATE),1,'P',
R.Status_Code))),
'R', Decode(R.Hold_Flag, 'Y', 'S',
Decode(R.Status_Code, 'Q', 'B',
'I', 'B',
R.Status_Code)),
R.Status_Code)
And (R.Concurrent_Program_Id = P.Concurrent_program_ID AND
R.Program_Application_ID= P.Application_ID )
And Request_Id = Req_ID;
Select To_Number(((To_Char(Sysdate, 'J') - 1 ) * 86400) +
To_Char(Sysdate, 'SSSSS'))
Into STime From Sys.Dual;
Select To_Number(((To_Char(Sysdate, 'J') - 1 ) * 86400) +
To_Char(Sysdate, 'SSSSS'))
Into ETime From Sys.Dual;
Select Concurrent_Process_Id, Session_Id
From Fnd_Concurrent_Processes
Where Process_Status_Code in ( 'A', 'C', 'T' )
And (Queue_Application_ID = applid and
Concurrent_Queue_ID = managerid );
select manager_type,
Running_processes, MAX_PROCESSES, Cartridge_Handle
into mtype, ActiveP, TargetP, CartType
from Fnd_Concurrent_Queues Q, Fnd_Cp_Services S
Where S.Service_ID = Q.Manager_Type
And (Q.Application_ID = applid
And Q.Concurrent_Queue_ID = managerid);
select count(*)
into ActiveP
from gv$session GV, fnd_concurrent_processes P
where
GV.Inst_id = P.Instance_number
And GV.audsid = p.session_id
And (Process_Status_Code not in ('S','K','U'))
And ( Queue_Application_ID = applid AND
Concurrent_Queue_ID = managerid );
Select Max_Processes Into TargetP From Fnd_Concurrent_Queues
Where Concurrent_Queue_ID = ManagerID
And Application_ID = ApplID;
program running in this session, select the current session id and
compare it with the session id of each manager process.
If they are they same, do not call check_process_status_by_handle,
as this will cause this session to lose its lock.
*/
SELECT userenv('SESSIONID') INTO cur_session_id FROM dual;
update fnd_concurrent_requests_remote
set interim_status_code = 'W',
req_information = substrb(message,1,240)
where request_id = l_request_id;
update fnd_concurrent_requests_remote
set interim_status_code = l_status,
completion_text = substrb(message, 1, 240)
where request_id = l_request_id;
update fnd_concurrent_requests_remote
set phase_code = 'C',
status_code = l_status,
completion_text = substrb(message, 1, 240)
where request_id = l_request_id;
update fnd_concurrent_requests
set interim_status_code = 'W',
req_information = substrb(message,1,240)
where request_id = do_set_status_autonomous.request_id;
--debug('updated req_information for request_id '|| do_set_status_autonomous.request_id);
update fnd_concurrent_requests
set interim_status_code = do_set_status_autonomous.status,
completion_text = substrb(message, 1, 240)
where request_id = do_set_status_autonomous.request_id;
--debug('updated completion_text for request_id '|| do_set_status_autonomous.request_id);
update fnd_concurrent_requests
set phase_code = 'C',
status_code = do_set_status_autonomous.status,
completion_text = substrb(message, 1, 240)
where request_id = do_set_status_autonomous.request_id;
select number_of_copies, print_style, printer, save_output_flag
into number_of_copies, print_style, printer, save_output_flag
from fnd_concurrent_requests r
where r.request_id = get_request_print_options.request_id;
select p.number_of_copies, r.print_style,
p.arguments, r.save_output_flag
from fnd_concurrent_requests r,
fnd_conc_pp_actions p
where r.request_id = p.concurrent_request_id
and p.action_type = 1
and p.concurrent_request_id = get_request_print_options.request_id
order by sequence;
Select Session_ID
into C_SessionID
from fnd_concurrent_processes cp,
fnd_concurrent_queues cq
where process_status_code = 'A'
and cp.Queue_Application_ID = cq.application_ID
and cp.concurrent_queue_id = cq.concurrent_queue_id
and cq.concurrent_queue_name = Queue_Name;
select SH.OSUSER,
SH.PROCESS,
SH.MACHINE,
SH.TERMINAL,
SH.PROGRAM
into UName, UProcess_ID, UNode, UTerminal, UProgram
from V$SESSION SH,
V$LOCK LW,
V$LOCK LH,
V$SESSION SW
where LH.SID = SH.SID
and LH.SID <> SW.SID
and LH.ID1 = LW.ID1
and LH.ID2 = LW.ID2
and LW.KADDR = SW.LOCKWAIT
and SW.LOCKWAIT is not null
and SW.AUDSID = C_SessionID;
select PRINTER_NAME, user_printer_style_name, l.meaning
into printer, style, save_output
from fnd_concurrent_programs p, fnd_printer_styles_VL ps,
fnd_lookups L, fnd_application_vl A
where
l.lookup_code = p.SAVE_OUTPUT_FLAG
and l.lookup_type = ltype
and ps.printer_style_name = p.OUTPUT_PRINT_STYLE
and p.application_id = a.application_id
and p.concurrent_program_name = program
and a.application_short_name = appl_shortname;
Select P.PID, P.SPID, AUDSID, PROCESS,
substr(userenv('LANGUAGE'),
instr( userenv('LANGUAGE'), '.') + 1)
Into cpid, csspid, csid, cspid, codeset
From V$Session S, V$Process P,
(select distinct sid from v$mystat ) m
Where P.Addr = S.Paddr
and s.sid = m.sid;
update fnd_concurrent_requests_remote
set ORACLE_SESSION_ID = csid,
ORACLE_PROCESS_ID = csspid,
OS_PROCESS_ID = cspid,
NLS_CodeSet = codeset
where request_id = l_request_id;
Select P.PID, P.SPID, AUDSID, PROCESS,
substr(userenv('LANGUAGE'),
instr( userenv('LANGUAGE'), '.') + 1)
Into cpid, csspid, csid, cspid, codeset
From V$Session S, V$Process P
Where P.Addr = S.Paddr
and S.AUDSID = userenv('SESSIONID');
update fnd_concurrent_requests
set ORACLE_SESSION_ID = csid,
ORACLE_PROCESS_ID = csspid,
OS_PROCESS_ID = cspid,
NLS_CodeSet = codeset
where request_id = fnd_global.conc_request_id;
select P.Optimizer_Mode, P.CONCURRENT_PROGRAM_NAME,
upper(P.enable_Trace), upper(R.enable_trace),
Decode(upper(P.ENABLE_TIME_STATISTICS),'Y','TRUE',NULL),
execution_method_code,
multi_org_category, org_id, p.application_id
into optmode, program_name, ptrace, rtrace, etstat, emethod,
morg_cat, orgid, temp
from FND_CONCURRENT_PROGRAMS P,
FND_CONCURRENT_REQUESTS R
WHERE P.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
And P.APPLICATION_ID = R.Program_APPLICATION_ID
And R.request_id = fnd_global.conc_request_id;
select lower(application_short_name) || '/' || upper(program_name) into temp
from fnd_application where application_id = temp;
Select plsql_log, plsql_out, plsql_dir
Into plog, pout, pdir
From Fnd_Concurrent_Processes P, Fnd_Concurrent_Requests R
Where P.Concurrent_Process_ID = R.Controlling_Manager
And R.Request_ID = fnd_global.conc_request_id;
select Q.RESOURCE_CONSUMER_GROUP
into que_rcg
from fnd_concurrent_requests r,
fnd_concurrent_processes p,
fnd_concurrent_queues q
where R.request_id = fnd_global.conc_request_id
and R.controlling_manager = P.concurrent_process_id
and Q.CONCURRENT_QUEUE_ID= P.CONCURRENT_QUEUE_ID
and Q.APPLICATION_ID = P.QUEUE_APPLICATION_ID;
select p.RESOURCE_CONSUMER_GROUP
into prg_rcg
from fnd_concurrent_programs P,
fnd_concurrent_requests R
where R.request_id = fnd_global.conc_request_id
and r.PROGRAM_APPLICATION_ID = P.APPLICATION_ID
and R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID;
select P.Rollback_Segment
into RBS
from FND_CONCURRENT_PROGRAMS P,
FND_CONCURRENT_REQUESTS R
WHERE R.request_id = fnd_global.conc_request_id
AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
And R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID;
SELECT execution_file_name from fnd_executables
where execution_method_code = 'Z';
Select Request_Id, Completion_Text,
Phase.Lookup_Code p_lookup_code,
Status.Lookup_Code s_lookup_code,
Phase.Meaning p_meaning, Status.Meaning s_meaning
From Fnd_Concurrent_Requests R,
Fnd_Concurrent_programs P,
Fnd_Lookups Phase,
Fnd_Lookups Status
Where
Phase.Lookup_Type = PHASE_LOOKUP_TYPE
AND Phase.Lookup_Code = Decode(Status.Lookup_Code,
'H', 'I',
'S', 'I',
'U', 'I',
'M', 'I',
R.Phase_Code) AND
Status.Lookup_Type = STATUS_LOOKUP_TYPE AND
Status.Lookup_Code =
Decode(R.Phase_Code,
'P', Decode(R.Hold_Flag, 'Y', 'H',
Decode(P.Enabled_Flag, 'N', 'U',
Decode(Sign(R.Requested_Start_Date - SYSDATE),1,'P',
R.Status_Code))),
'R', Decode(R.Hold_Flag, 'Y', 'S',
Decode(R.Status_Code, 'Q', 'B',
'I', 'B',
R.Status_Code)),
R.Status_Code)
And (R.Concurrent_Program_Id = P.Concurrent_program_ID AND
R.Program_Application_ID= P.Application_ID )
And Parent_Request_Id = p_request_id;
Select request_id
from fnd_concurrent_requests
where parent_request_id = parent_id;
Select sysdate + (greatest(Max_Wait, 0)/86400)
into end_of_time
from dual;
Select NVL(Parent_Request_ID,FND_GLOBAL.CONC_REQUEST_ID)
into parent_req_id
from dual;
Select count(*) into kount
from fnd_concurrent_requests
where parent_request_id = parent_req_id
and phase_code <> 'C';
select (end_of_time - sysdate) * 86400
into time_left
from dual;
* Create node. If it already exists,then we'll Update instead
*/
select count(*)
into kount
from fnd_nodes
where upper(node_name) = upper(name);
insert into fnd_nodes
(node_id, node_name,
support_forms, support_cp, support_web, support_admin,
platform_code, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
node_mode, server_id, server_address, description,
host, domain,support_db, virtual_ip)
select
fnd_nodes_s.nextval, name,
forms_tier, cp_tier, web_tier, admin_tier,
platform_id, 1, SYSDATE,
1, SYSDATE, 0,
'O', p_server_id, p_address, p_description,
p_host_name, p_domain,db_tier, p_virtual_ip
from dual;
update fnd_nodes set
description = p_description,
support_forms = decode(forms_tier, 'Y', 'Y', support_forms),
support_cp = decode(cp_tier, 'Y', 'Y', support_cp),
support_web = decode(web_tier, 'Y', 'Y', support_web),
support_admin = decode(admin_tier, 'Y', 'Y', support_admin),
platform_code = platform_id,
last_update_date = SYSDATE, last_updated_by = 1,
host = p_host_name,
domain = p_domain,
support_db = decode(db_tier,'Y','Y',support_db)
where upper(node_name) = upper(name);
-- If server_id is not null, update fnd_nodes.server_id.
-- fnd_nodes.server_id can only be null if the application server node has been
-- removed.
if (p_server_id is not null) then
update fnd_nodes
set server_id = p_server_id
where upper(node_name) = upper(name);
-- If server_address is not null, update fnd_nodes.server_address.
if (p_address is not null) then
update fnd_nodes
set server_address = p_address
where upper(node_name) = upper(name);
update fnd_nodes
set virtual_ip = p_virtual_ip
where upper(node_name) = upper(name);
select APPLICATION_SHORT_NAME c_appl_short_name,
CONCURRENT_QUEUE_NAME c_svc_name,
RUNNING_PROCESSES c_run_procs,
MAX_PROCESSES c_max_procs,
CONTROL_CODE c_ctrl_code,
ENABLED_FLAG c_enabled
from FND_CONCURRENT_QUEUES fcq,
FND_CP_SERVICES fcs,
FND_APPLICATION fa
where
fcq.MANAGER_TYPE = to_char(fcs.SERVICE_id)
and fcq.application_id = fa.application_id
and fcs.SERVICE_ID = svc_id;
select SERVICE_ID
into svc_id
from FND_CP_SERVICES
where SERVICE_HANDLE = upper(svc_handle);
select CONCURRENT_PROCESS_ID c_cpid,
MEANING c_state,
fcp.NODE_NAME c_node,
fcp.SERVICE_PARAMETERS c_parameters
from FND_CONCURRENT_QUEUES fcq,
FND_CONCURRENT_PROCESSES fcp,
FND_APPLICATION fa,
FND_LOOKUP_VALUES_VL flv
where
fcp.QUEUE_APPLICATION_ID = fcq.APPLICATION_ID
and fcp.CONCURRENT_QUEUE_ID = fcq.CONCURRENT_QUEUE_ID
and fcq.APPLICATION_ID = fa.APPLICATION_ID
and flv.LOOKUP_TYPE = ltype
and flv.LOOKUP_CODE = fcp.PROCESS_STATUS_CODE
and fa.APPLICATION_SHORT_NAME = upper(appl_short_name)
and fcq.CONCURRENT_QUEUE_NAME = upper(svc_instance_name)
and ((proc_state is not null)
or (fcp.PROCESS_STATUS_CODE not in ('S', 'K', 'U')))
and exists
(select 1
from fnd_lookup_values flv2
where flv2.LOOKUP_TYPE = ltype
and flv2.LOOKUP_CODE = fcp.PROCESS_STATUS_CODE
and upper(flv2.meaning) =
upper(nvl(proc_state,flv2.meaning)));
select 0
into i
from FND_CONCURRENT_QUEUES fcq,
FND_APPLICATION fa
where fcq.APPLICATION_ID = fa.APPLICATION_ID
and APPLICATION_SHORT_NAME = upper(appl_short_name)
and upper(CONCURRENT_QUEUE_NAME) = upper(svc_instance_name);
select argument1, argument2, argument3
into rarg1, rarg2, rarg3
from fnd_concurrent_requests R
where requestid = R.request_id;
select count(concurrent_queue_id)
into kount
from fnd_concurrent_queues
where concurrent_queue_id = rarg1
and application_id = rarg2
and manager_type = mtype;
select count(concurrent_queue_id)
into kount
from fnd_concurrent_queues
where application_id = rarg2
and manager_type = mtype;
select argument1, argument2, argument3
into rarg1, rarg2, rarg3
from fnd_concurrent_requests R
where requestid = R.request_id;
Select request_id
from fnd_concurrent_requests R, fnd_concurrent_programs P
where r.phase_code = 'P'
and p.application_id = r.PROGRAM_APPLICATION_ID
and p.concurrent_program_id = r.concurrent_program_id
and p.queue_control_flag = 'Y'
and msc_match(request_id, app_id, service_inst_id, my_service_id) = 1
order by request_id;
select manager_type, application_id
into my_service_id, app_id
from fnd_concurrent_queues
where concurrent_queue_id = service_inst_id;
Select R2.request_id
from fnd_concurrent_requests R1,
fnd_concurrent_requests R2,
fnd_concurrent_programs P1,
fnd_concurrent_programs P2,
fnd_concurrent_queues Q,
fnd_application A
where r1.request_id = reqid
and P1.APPLICATION_ID = R1.PROGRAM_APPLICATION_ID
and P1.concurrent_program_id = R1.concurrent_program_id
and p1.queue_control_flag = 'Y'
and r2.request_id > r1.request_id
and P2.APPLICATION_ID = R2.PROGRAM_APPLICATION_ID
and P2.concurrent_program_id = R2.concurrent_program_id
and P2.concurrent_program_id <> 2
and P2.concurrent_program_id <> 6
and p2.queue_control_flag = 'Y'
AND a.application_id = p2.application_id
AND a.application_short_name = 'FND'
and msc_match(reqid,
Q.application_id, Q.concurrent_queue_id, Q.manager_type) +
msc_match(R2.request_id,
Q.application_id, Q.concurrent_queue_id, Q.manager_type) = 2
order by R2.request_id;
select count(concurrent_queue_id)
into kount
from fnd_concurrent_queues
where concurrent_queue_id = rarg1
and application_id = rarg2
and ((Running_processes <> MAX_PROCESSES)
or ((goal_state is not null) and
((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
or ((goal_state is null) and (CONTROL_CODE is null)));
select count(concurrent_queue_id)
into kount
from fnd_concurrent_queues
where application_id = rarg2
and (
/* either CM or TM and request is for mgrs (or both) */
(((manager_type = 1) or (manager_type = 3))
and ((rarg3 = 0) or (rarg3 = 2)))
or /* or service and request is for services (or both) */
((manager_type > 999) and ((rarg3 = 1) or (rarg3 = 2))))
and ((Running_processes <> MAX_PROCESSES)
or ((goal_state is not null) and
((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
or ((goal_state is null) and (CONTROL_CODE is null)));
select count(concurrent_queue_id)
into kount
from fnd_concurrent_queues
where manager_type IN ('1', '3', '4', '5')
and ((Running_processes <> MAX_PROCESSES)
or ((goal_state is not null) and
((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
or ((goal_state is null) and (CONTROL_CODE is null)));
select count(concurrent_queue_id)
into kount
from fnd_concurrent_queues
where manager_type = to_char(rarg2)
and ((Running_processes <> MAX_PROCESSES)
or ((goal_state is not null) and
((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
or ((goal_state is null) and (CONTROL_CODE is null)));
select count(concurrent_queue_id)
into kount
from fnd_concurrent_queues
where manager_type < 1000
and ((Running_processes <> MAX_PROCESSES)
or ((goal_state is not null) and
((CONTROL_CODE <> goal_state) or (CONTROL_CODE is null)))
or ((goal_state is null) and (CONTROL_CODE is null)));
select sysdate
into mystart
from dual;
select concurrent_program_id, program_application_id, phase_code,
argument1, argument2, argument3, Decode(concurrent_program_id,
0,null, 1,'E', 3, null, 4, 'X', 5, 'E', 7, 'P', 8, null, null)
into prog_id, r_app_id, r_phase, rarg1, rarg2, rarg3, goal_state
from fnd_concurrent_requests
where request_id = reqid;
select ((sysdate - mystart) * 86400) - Timeout
into timesup
from dual;
select decode(phase_code, 'C', 0, -1)
into Done
from fnd_concurrent_requests
where request_id = reqid;
select sysdate
into mystart
from dual;
select ((sysdate - mystart) * 86400) - Timeout
into done
from dual;
select cp.USER_CONCURRENT_PROGRAM_NAME
into action
from fnd_concurrent_programs_vl cp, fnd_application a
where cp.concurrent_program_name = prog
AND cp.application_id = a.application_id
AND a.application_short_name = 'FND';
select USER_CONCURRENT_QUEUE_NAME
into Detail
from fnd_concurrent_queues_vl
where APPLICATION_ID = Arg2
and concurrent_queue_id = Arg1;
select APPLICATION_NAME
into Detail
from fnd_application_vl
where APPLICATION_ID = Arg2;
select SERVICE_NAME
into Detail
from fnd_cp_services_vl
where SERVICE_ID = Arg2;
Select Requested_By
into submitter
from fnd_concurrent_requests
where request_id = Cancel_Request.request_id;
-- Select all the information needed for this request from fnd_concurrent_requests,
-- using the fnd_cp_sql_requests table.
-- A row should have been inserted earlier in usdspid, containing the current request id,
-- machine name, and process id.
-- By joining these tables with v$session, we can pull out all the information we need,
-- using only our own session id.
begin
select process, machine
into lpid, lmachine
from v$session
where audsid = userenv('sessionid');
select 0, fcr.requested_by,
fcr.responsibility_id, fcr.responsibility_application_id,
fcr.security_group_id, 0,
fcr.requested_by, fcr.conc_login_id,
fcr.program_application_id, fcr.concurrent_program_id,
fcr.request_id, fcr.priority_request_id
into session_id, userid, respid, respappid,
secgrpid, siteid, loginid, cloginid,
progappid, cprogid, creqid, cprireqid
from fnd_concurrent_requests fcr,
fnd_cp_sql_requests sr
where fcr.phase_code = 'R'
and fcr.status_code = 'R'
and fcr.request_id = sr.request_id
and sr.machine = lmachine
and sr.client_process_id = lpid;
-- now delete the row, to avoid having to purge the table.
DELETE from fnd_cp_sql_requests where request_id = creqid;
-- insert into FND_CONCURRENT_DEBUG_INFO(TIME, ACTION, message, TIME_IN_NUMBER)
-- VALUES(sysdate,'FND_CONCURRENT.get_m_s',message,0);
SELECT user_id
FROM fnd_user
WHERE user_name = upper(p_user_name);
SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = p_responsibility_key;
SELECT count(responsibility_id)
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND responsibility_id = p_responsibility_id;
SELECT count(responsibility_id)
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND responsibility_id = p_resp_id
AND responsibility_application_id = p_resp_appl_id
AND security_group_id = p_sec_group_id;
SELECT user_id
FROM fnd_user
WHERE user_name = upper(p_user_name);
SELECT application_id
FROM fnd_application
WHERE application_short_name = p_application_short_name;
SELECT concurrent_program_id, srs_flag
FROM fnd_concurrent_programs
WHERE concurrent_program_name = p_program_name
AND application_id = p_application_id;
l_sql_stmt := 'select count(p.concurrent_program_id) from fnd_concurrent_programs p where p.concurrent_program_id = :1 and p.application_id = :2 and ' || l_predicate || '';