[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select RESOURCE_ID FROM
jtf_rs_resource_extns
WHERE USER_ID = p_user_id;
select gm.GROUP_ID
from jtf_rs_role_relations rr,
jtf_rs_roles_b rb,
jtf_rs_group_members gm
WHERE rr.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
AND rr.ROLE_RESOURCE_ID = p_resource_id
AND rr.ROLE_ID = rb.ROLE_ID
AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID'
AND rr.ROLE_RESOURCE_ID = gm.RESOURCE_ID;
SELECT user_id, count(*) over () row_count
FROM fnd_user
WHERE UPPER(email_address) = p_email_id
AND start_date <= sysdate
AND(end_date IS NULL OR end_date > sysdate);
SELECT user_name INTO g_user_name
FROM FND_USER
WHERE user_id = l_fnd_user_id;
SELECT resp.responsibility_id
FROM fnd_user usr,
fnd_user_resp_groups resp
WHERE usr.user_id = p_user_id
AND resp.responsibility_id = p_resp_id
AND usr.user_id = resp.user_id
AND resp.start_date <= sysdate
AND(resp.end_date IS NULL OR resp.end_date >= sysdate);
SELECT resp.responsibility_id
FROM fnd_user usr,
fnd_user_resp_groups resp,
asg_responsibility_vl mresp
WHERE usr.user_id = p_user_id
AND resp.responsibility_id = mresp.responsibility_id
AND usr.user_id = resp.user_id
AND resp.start_date <= sysdate
AND(resp.end_date IS NULL OR resp.end_date >= sysdate);
SELECT count(*) over () row_count
FROM jtf_task_assignments jta,
jtf_tasks_b jtb
WHERE jtb.task_number = p_task_number
AND jtb.task_id = jta.task_id
AND jta.resource_id = p_resource_id;
select 1 from
jtf_rs_role_relations rr,
jtf_rs_roles_b rb,
jtf_rs_group_members gm
WHERE rr.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
AND rr.ROLE_RESOURCE_ID = p_resource_id
AND rr.ROLE_ID = rb.ROLE_ID
AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
--AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID'
AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR'
AND rr.ROLE_RESOURCE_ID = gm.RESOURCE_ID
AND gm.GROUP_ID IN (SELECT jta.resource_id
FROM jtf_task_assignments jta,
jtf_tasks_b jtb
WHERE jtb.task_number = p_task_number
AND jtb.task_id = jta.task_id
AND jta.resource_type_code ='RS_GROUP');
SELECT count(*) over () row_count
from JTF_TASK_ASSIGNMENTS JTA,
JTF_TASKS_B JTB,
JTF_RS_GROUP_MEMBERS jtm
WHERE jtb.task_number = p_task_number
and JTB.TASK_ID = JTA.TASK_ID
and JTA.RESOURCE_ID = JTM.RESOURCE_ID
and JTM.DELETE_FLAG = 'N'
AND jtm.GROUP_ID IN( select gm.GROUP_ID
from jtf_rs_role_relations rr,
jtf_rs_roles_b rb,
jtf_rs_group_members gm
where RR.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
and sysdate between NVL(RR.START_DATE_ACTIVE,sysdate) and NVL(RR.END_DATE_ACTIVE,sysdate)
and RR.DELETE_FLAG = 'N'
and gm.DELETE_FLAG = 'N'
AND rr.ROLE_RESOURCE_ID = p_resource_id
AND rr.ROLE_ID = rb.ROLE_ID
AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
and RB.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR'
and RR.ROLE_RESOURCE_ID = GM.RESOURCE_ID);
SELECT QUERY_ID,
RESTRICTED_FLAG,
DISABLED_FLAG,
LEVEL_ID,
LEVEL_VALUE
FROM CSM_QUERY_B
WHERE UPPER(QUERY_NAME) = p_command_name
AND NVL(DELETE_FLAG,'N') = 'N';
SELECT variable_id,
variable_value_char,
variable_value_date,
default_flag
FROM CSM_QUERY_VARIABLES_B
WHERE QUERY_ID = p_query_id;
SELECT count(*)
FROM CSM_QUERY_VARIABLES_B
WHERE QUERY_ID = p_query_id
AND DEFAULT_FLAG = 'N';
SELECT resp.application_id,
resp.responsibility_id
FROM fnd_application appl,
fnd_responsibility resp
WHERE appl.application_short_name = 'CSM'
and resp.application_id = appl.application_id
AND resp.responsibility_key = 'OMFS_PALM';
CSM_QUERY_PKG.INSERT_INSTANCE
( p_USER_ID => g_user_id,
p_QUERY_ID => l_query_id,
p_INSTANCE_ID => NULL,
p_INSTANCE_NAME => NULL,
p_VARIABLE_ID => l_variable_id_lst,
p_VARIABLE_VALUE_CHAR => l_var_value_lst,
p_VARIABLE_VALUE_DATE => l_var_value_date_lst,
p_commit => fnd_api.G_TRUE,
x_INSTANCE_ID => p_instance_id,
x_return_status => x_return_status,
x_error_message => x_error_message
);
|| ' ROOT ERROR: CSM_QUERY_PKG.INSERT_INSTANCE '
|| ' for QUERY_ID ' || l_query_id || ' Detail: ' || x_error_message;
UPDATE csm_query_results_acc
SET user_email_id = p_email_id
WHERE instance_id = p_instance_id;
SELECT gb.group_id,
gtl.group_name
FROM jtf_rs_resource_extns rs,
jtf_rs_groups_b gb,
jtf_rs_groups_tl gtl,
jtf_rs_group_members gm,
jtf_rs_roles_b rb,
jtf_rs_role_relations rr_grp,
jtf_rs_role_relations rr_res
WHERE rs.user_id = p_user_id
AND gm.resource_id = rs.resource_id
AND gm.delete_flag = 'N'
AND gm.group_id = gb.group_id
AND gb.start_date_active <= sysdate
AND(gb.end_date_active IS NULL OR gb.end_date_active >= sysdate)
AND gtl.group_id = gb.group_id
AND gtl.LANGUAGE = userenv('LANG')
AND rr_grp.role_resource_type = 'RS_GROUP_MEMBER'
AND rr_grp.role_resource_id = gm.group_member_id
AND rr_grp.delete_flag = 'N'
AND rr_grp.role_id = rb.role_id
AND rr_grp.start_date_active <= sysdate
AND(rr_grp.end_date_active IS NULL OR rr_grp.end_date_active >= sysdate)
AND rr_res.role_resource_type = 'RS_INDIVIDUAL'
AND rr_res.role_resource_id = gm.resource_id
AND rr_res.delete_flag = 'N'
AND rr_res.role_id = rb.role_id
AND rr_res.start_date_active <= sysdate
AND(rr_res.end_date_active IS NULL OR rr_res.end_date_active >= sysdate)
AND rb.admin_flag = 'Y';
SELECT fusr.user_id
from FND_USER FUSR,
JTF_RS_RESOURCE_EXTNS RS,
JTF_RS_GROUP_MEMBERS GM
where gm.group_id = p_group_id
AND gm.delete_flag = 'N'
AND rs.resource_id = gm.resource_id
AND rs.start_date_active <= sysdate
AND(rs.end_date_active IS NULL OR rs.end_date_active >= sysdate)
AND fusr.user_id = rs.user_id
and FUSR.START_DATE <= sysdate
and (FUSR.END_DATE is null or FUSR.END_DATE >= sysdate);
/* SELECT fusr.user_id
from FND_USER FUSR,
JTF_RS_RESOURCE_EXTNS RS,
jtf_rs_groups_b gb,
JTF_RS_GROUP_MEMBERS GM
jtf_rs_roles_b rb,
jtf_rs_role_relations rr_grp,
jtf_rs_role_relations rr_res
where AND gm.group_id = p_group_id
gb.group_id = p_group_id
AND gb.start_date_active <= sysdate
AND(gb.end_date_active IS NULL OR gb.end_date_active >= sysdate)
AND gm.group_id = gb.group_id
AND gm.delete_flag = 'N'
AND rs.resource_id = gm.resource_id
AND rs.start_date_active <= sysdate
AND(rs.end_date_active IS NULL OR rs.end_date_active >= sysdate)
AND fusr.user_id = rs.user_id
and FUSR.START_DATE <= sysdate
and(FUSR.END_DATE is null or FUSR.END_DATE >= sysdate);
AND rr_grp.delete_flag = 'N'
AND rr_grp.role_resource_type = 'RS_GROUP_MEMBER'
AND rr_grp.start_date_active <= sysdate
AND(rr_grp.end_date_active IS NULL OR rr_grp.end_date_active >= sysdate)
AND rr_grp.role_id = rb.role_id
AND rr_res.role_resource_id = gm.resource_id
AND rr_res.role_resource_type = 'RS_INDIVIDUAL'
AND rr_res.delete_flag = 'N'
AND rr_res.start_date_active <= sysdate
and(RR_RES.END_DATE_ACTIVE is null or RR_RES.END_DATE_ACTIVE >= sysdate)
AND rr_res.role_id = rb.role_id;*/
select 1 from
jtf_rs_role_relations rr,
jtf_rs_roles_b rb
WHERE rr.ROLE_RESOURCE_TYPE ='RS_GROUP'
AND rr.ROLE_RESOURCE_ID = p_group_id
AND rr.ROLE_ID = rb.ROLE_ID
AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID';
l_query_text := ' SELECT cia.incident_number service_request,
hzp.party_name customer,
res.source_name assignee,
ct.task_number task_number,
ctl.task_name as subject,
jtstl.name task_status,
to_char(CSM_EMAIL_QUERY_PKG.ADJUST_TIME(ct.scheduled_start_date), '''|| g_date_format_mask ||''') scheduled_start_date,
to_char(CSM_EMAIL_QUERY_PKG.ADJUST_TIME(ct.scheduled_end_date), '''|| g_date_format_mask ||''') scheduled_end_date
FROM jtf_rs_resource_extns res,
jtf_task_assignments a,
jtf_tasks_b ct,
jtf_tasks_tl ctl,
cs_incidents_all_b cia,
hz_parties hzp,
jtf_task_statuses_b jts,
jtf_task_statuses_tl jtstl
WHERE res.user_id IN '|| l_user_lst || '
AND a.resource_id = res.resource_id
AND a.resource_type_code = ''RS_EMPLOYEE''
AND ct.task_id = a.task_id
AND ct.open_flag = ''Y''
AND ct.source_object_type_code = ''SR''
AND ct.scheduled_start_date IS NOT NULL
AND ct.scheduled_end_date IS NOT NULL
AND ct.source_object_id = cia.incident_id
AND cia.customer_id = hzp.party_id
AND ctl.task_id = ct.task_id
AND ct.task_status_id = jts.task_status_id
AND ctl.LANGUAGE = USERENV(''LANG'')
AND jts.assigned_flag = ''Y''
AND nvl(jts.COMPLETED_FLAG,''N'') = ''N''
AND nvl(jts.CANCELLED_FLAG,''N'') = ''N''
AND nvl(jts.CLOSED_FLAG,''N'') = ''N''
AND nvl(jts.REJECTED_FLAG,''N'') = ''N''
AND jtstl.task_status_id = jts.task_status_id
AND jtstl.LANGUAGE = USERENV(''LANG'') ';
l_query_text := l_query_text || ' UNION ALL SELECT cia.incident_number service_request,
hzp.party_name customer,
gtl.GROUP_NAME assignee,
ct.task_number task_number,
ctl.task_name as subject,
jtstl.name task_status,
to_char(CSM_EMAIL_QUERY_PKG.ADJUST_TIME(ct.scheduled_start_date), '''|| g_date_format_mask ||''') scheduled_start_date,
to_char(CSM_EMAIL_QUERY_PKG.ADJUST_TIME(ct.scheduled_end_date), '''|| g_date_format_mask ||''') scheduled_end_date
FROM jtf_task_assignments a,
jtf_tasks_b ct,
jtf_tasks_tl ctl,
cs_incidents_all_b cia,
hz_parties hzp,
jtf_task_statuses_b jts,
jtf_task_statuses_tl jtstl,
jtf_rs_groups_tl gtl
WHERE a.resource_type_code = ''RS_GROUP''
AND a.resource_id IN ' || l_group_lst ||'
AND ct.task_id = a.task_id
AND ct.open_flag = ''Y''
AND ct.source_object_type_code = ''SR''
AND ct.scheduled_start_date IS NOT NULL
AND ct.scheduled_end_date IS NOT NULL
AND ct.source_object_id = cia.incident_id
AND cia.customer_id = hzp.party_id
AND ctl.task_id = ct.task_id
AND ct.task_status_id = jts.task_status_id
AND ctl.LANGUAGE = USERENV(''LANG'')
AND jts.assigned_flag = ''Y''
AND nvl(jts.COMPLETED_FLAG,''N'') = ''N''
AND nvl(jts.CANCELLED_FLAG,''N'') = ''N''
AND nvl(jts.CLOSED_FLAG,''N'') = ''N''
AND nvl(jts.REJECTED_FLAG,''N'') = ''N''
AND jtstl.task_status_id = jts.task_status_id
AND jtstl.LANGUAGE = USERENV(''LANG'')
AND a.RESOURCE_ID = gtl.GROUP_ID
AND gtl.LANGUAGE = USERENV(''LANG'')
ORDER BY task_number ';
PROCEDURE UPDATE_TASK
( p_task_number IN VARCHAR2,
p_task_status_id IN VARCHAR2,
p_result OUT nocopy CLOB,
x_return_status OUT nocopy VARCHAR2,
x_error_message OUT nocopy VARCHAR2
)
AS
CURSOR c_task_assignments(p_task_number VARCHAR2, p_resource_id NUMBER)
IS
SELECT jta.task_id,
jta.object_version_number,
jta.last_update_date,
jta.last_updated_by,
jta.task_assignment_id,
jta.assignment_status_id
FROM jtf_task_assignments jta,
jtf_tasks_b jtb
WHERE jtb.task_number = p_task_number
AND jtb.task_id = jta.task_id
AND jta.resource_id = p_resource_id
UNION ALL
SELECT jta.task_id,
jta.object_version_number,
jta.last_update_date,
jta.last_updated_by,
jta.task_assignment_id,
jta.assignment_status_id
FROM jtf_task_assignments jta,
jtf_tasks_b jtb
WHERE jtb.task_number = p_task_number
AND jtb.task_id = jta.task_id
AND jta.resource_type_code ='RS_GROUP'
AND jta.resource_id IN ( SELECT gm.GROUP_ID from
jtf_rs_role_relations rr,
jtf_rs_roles_b rb,
jtf_rs_group_members gm
WHERE rr.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
AND rr.ROLE_RESOURCE_ID = p_resource_id
AND rr.ROLE_ID = rb.ROLE_ID
AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
--AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID'
AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR'
AND rr.ROLE_RESOURCE_ID = gm.RESOURCE_ID);
CSM_UTIL_PKG.LOG('Entering UPDATE_TASK for TASK_NUMBER: ' || p_task_number || ' STATUS_ID is : ' || p_task_status_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
csf_task_assignments_pub.update_assignment_status
( p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_TRUE
-- Bug 101406041 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
-- Validate task status transitions.
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_task_assignment_id => r_task_assignments.task_assignment_id
, p_assignment_status_id => p_task_status_id
, p_object_version_number => r_task_assignments.object_version_number
, p_update_task => 'T'
, x_task_object_version_number => l_task_object_version_number
, x_task_status_id => l_task_status_id
);
x_error_message := 'Error in UPDATE_TASK :'
|| ' ROOT ERROR: csf_task_assignments_pub.update_assignment_status'
|| ' for PK : ' || r_task_assignments.task_assignment_id
|| ' Details:' || l_msg_data ;
CSM_UTIL_PKG.LOG('Leaving UPDATE_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
x_error_message := 'Exception occurred in UPDATE_TASK: ' || sqlerrm;
END UPDATE_TASK;
UPDATE_TASK
( p_task_number => p_task_number,
p_task_status_id => l_profile_value,
p_result => p_result,
x_return_status => x_return_status,
x_error_message => x_error_message);
UPDATE_TASK
( p_task_number => p_task_number,
p_task_status_id => l_profile_value,
p_result => p_result,
x_return_status => x_return_status,
x_error_message => x_error_message);
UPDATE_TASK
( p_task_number => p_task_number,
p_task_status_id => l_profile_value,
p_result => p_result,
x_return_status => x_return_status,
x_error_message => x_error_message);
/*Procedure to update task statu to Travelling*/
PROCEDURE TRAVELING_TASK
( p_task_number IN VARCHAR2,
p_result OUT nocopy CLOB,
x_return_status OUT nocopy VARCHAR2,
x_error_message OUT nocopy VARCHAR2
)
AS
l_profile_value NUMBER;
UPDATE_TASK
( p_task_number => p_task_number,
p_task_status_id => l_profile_value,
p_result => p_result,
x_return_status => x_return_status,
x_error_message => x_error_message);
/*Procedure to update task statu to Working*/
PROCEDURE WORKING_TASK
( p_task_number IN VARCHAR2,
p_result OUT nocopy CLOB,
x_return_status OUT nocopy VARCHAR2,
x_error_message OUT nocopy VARCHAR2
)
AS
l_profile_value NUMBER;
UPDATE_TASK
( p_task_number => p_task_number,
p_task_status_id => l_profile_value,
p_result => p_result,
x_return_status => x_return_status,
x_error_message => x_error_message);
/*Procedure to update task statu to Completed*/
PROCEDURE COMPLETED_TASK
( p_task_number IN VARCHAR2,
p_result OUT nocopy CLOB,
x_return_status OUT nocopy VARCHAR2,
x_error_message OUT nocopy VARCHAR2
)
AS
CURSOR c_task_status
IS
SELECT tsb.task_status_id
FROM jtf_task_statuses_b tsb
WHERE tsb.COMPLETED_FLAG = 'Y'
AND tsb.USAGE ='TASK'
AND tsb.SEEDED_FLAG = 'Y'
AND tsb.CLOSED_FLAG IS NULL
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE) AND NVL(END_DATE_ACTIVE,SYSDATE);
UPDATE_TASK
( p_task_number => p_task_number,
p_task_status_id => l_profile_value,
p_result => p_result,
x_return_status => x_return_status,
x_error_message => x_error_message);
SELECT jtb.task_id
FROM jtf_tasks_b jtb
WHERE jtb.task_number = p_task_number;
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'JTF_NOTE_STATUS'
AND meaning = p_meaning
AND language = userenv('LANG');
, p_last_updated_by => g_user_id --NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID) --12.1
, p_last_update_date => SYSDATE
, p_last_update_login => g_user_id
, p_note_type => l_note_type
, x_jtf_note_id => l_jtf_note_id
);
, p_last_updated_by => g_user_id --NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID) --12.1
, p_last_update_date => SYSDATE
, p_last_update_login => g_user_id
, p_note_type => l_note_type
, x_jtf_note_id => l_jtf_note_id
);
SELECT tsk.task_number,
tsk.task_id,
tsktl.task_name,
tsktl.description,
tptl.name priority,
tttl.name type,
tstl.name status,
hzp.party_name customer,
ADJUST_TIME(tsk.scheduled_start_date) scheduled_start_date,
ADJUST_TIME(tsk.scheduled_end_date) scheduled_end_date,
csb.incident_id,
cstl.summary,
csb.problem_code,
hzloc.address1 || decode(hzloc.address1, NULL, '', ',')
|| hzloc.address2 || decode(hzloc.address2, NULL, '', ',')
|| hzloc.city || decode(hzloc.city, NULL, '', ',')
|| hzloc.state || decode(hzloc.state, NULL, '', ',')
|| hzloc.postal_code || decode(hzloc.postal_code, NULL, '', ',')
|| hzloc.country
AS
address
FROM jtf_tasks_b tsk,
jtf_tasks_tl tsktl,
jtf_task_priorities_b tpb,
jtf_task_priorities_tl tptl,
jtf_task_types_b ttb,
jtf_task_types_tl tttl,
jtf_task_statuses_b ts,
jtf_task_statuses_tl tstl,
cs_incidents_all_b csb,
cs_incidents_all_tl cstl,
hz_parties hzp,
hz_party_sites hzps,
hz_locations hzloc
WHERE tsk.task_number = p_task_number
AND tsktl.task_id = tsk.task_id
AND tsktl.LANGUAGE = userenv('LANG')
AND tpb.task_priority_id(+) = tsk.task_priority_id
AND tptl.task_priority_id = tpb.task_priority_id
AND tptl.LANGUAGE = userenv('LANG')
AND ttb.task_type_id(+) = tsk.task_type_id
AND tttl.task_type_id = ttb.task_type_id
AND tttl.LANGUAGE = userenv('LANG')
AND ts.task_status_id(+) = tsk.task_status_id
AND tstl.task_status_id = ts.task_status_id
AND tstl.LANGUAGE = userenv('LANG')
AND tsk.source_object_type_code = 'SR'
AND csb.incident_id(+) = tsk.source_object_id
AND csb.incident_id = cstl.incident_id
AND cstl.LANGUAGE = userenv('LANG')
AND hzp.party_id(+) = tsk.customer_id
AND hzps.party_site_id = tsk.address_id
AND hzloc.location_id(+) = hzps.location_id;
SELECT tsk.planned_effort,
uom.unit_of_measure
FROM jtf_tasks_b tsk,
mtl_units_of_measure_tl uom
WHERE tsk.task_id = p_task_id
AND uom.uom_code(+) = tsk.planned_effort_uom
AND uom.LANGUAGE = userenv('LANG');
SELECT item.segment1 AS item
FROM cs_incidents_all_b csb,
mtl_system_items_b item
WHERE csb.incident_id = p_incident_id
AND item.inventory_item_id(+) = csb.inventory_item_id
AND item.organization_id = csb.org_id;
SELECT inst.serial_number
FROM cs_incidents_all_b csb,
csi_item_instances inst
WHERE csb.incident_id = p_incident_id
AND inst.instance_id(+) = csb.customer_product_id
AND inst.inv_master_organization_id = csb.org_id
AND inst.inventory_item_id = csb.inventory_item_id;
SELECT nttl.notes note_text,
lkp.meaning note_status,
rs.source_name entered_by,
ADJUST_TIME(ntb.entered_date) entered_date
FROM jtf_tasks_b tsk,
jtf_notes_b ntb,
jtf_notes_tl nttl,
jtf_rs_resource_extns rs,
fnd_lookup_values lkp
WHERE tsk.task_number = p_task_number
AND ntb.source_object_id = tsk.task_id
AND ntb.source_object_code = 'TASK'
AND nttl.jtf_note_id = ntb.jtf_note_id
AND nttl.LANGUAGE = userenv('LANG')
AND rs.user_id = ntb.entered_by
AND lkp.lookup_code = ntb.note_status
AND lkp.lookup_type = 'JTF_NOTE_STATUS'
AND lkp.LANGUAGE = userenv('LANG');
SELECT sr.incident_id, tsk.TASK_NUMBER
FROM cs_incidents_all_b sr,
jtf_tasks_b tsk
WHERE sr.incident_number = p_incident_number
AND tsk.source_object_id = sr.incident_id
AND tsk.source_object_type_code = 'SR';
SELECT csb.incident_number,
cstl.summary name,
it.name type,
isevtl.name severity,
isttl.name status,
hzp.party_name customer,
csb.problem_code,
csb.resolution_code,
ADJUST_TIME(csb.incident_date) reported_date
FROM cs_incidents_all_b csb,
cs_incidents_all_tl cstl,
cs_incident_types it,
cs_incident_types_tl ittl,
cs_incident_severities isev,
cs_incident_severities_tl isevtl,
cs_incident_statuses ist,
cs_incident_statuses_tl isttl,
hz_parties hzp
WHERE csb.incident_id = p_incident_id
AND cstl.incident_id = csb.incident_id
AND cstl.LANGUAGE = userenv('LANG')
AND it.incident_type_id(+) = csb.incident_type_id
AND it.incident_type_id = ittl.incident_type_id
AND ittl.LANGUAGE = userenv('LANG')
AND isev.incident_severity_id(+) = csb.incident_severity_id
AND isev.incident_severity_id = isevtl.incident_severity_id
AND isevtl.LANGUAGE = userenv('LANG')
AND ist.incident_status_id(+) = csb.incident_status_id
AND ist.incident_status_id = isttl.incident_status_id
AND isttl.LANGUAGE = userenv('LANG')
AND hzp.party_id(+) = csb.customer_id;
SELECT item.segment1 AS item
FROM cs_incidents_all_b csb,
mtl_system_items_b item
WHERE csb.incident_id = p_incident_id
AND item.inventory_item_id(+) = csb.inventory_item_id
AND item.organization_id = csb.org_id;
SELECT inst.instance_number AS instance,
inst.serial_number
FROM cs_incidents_all_b csb,
csi_item_instances inst
WHERE csb.incident_id = p_incident_id
AND inst.instance_id(+) = csb.customer_product_id
AND inst.inv_master_organization_id = csb.org_id
AND inst.inventory_item_id = csb.inventory_item_id;
SELECT description
FROM fnd_lookup_values
WHERE lookup_code = p_problem_code
AND lookup_type = 'REQUEST_PROBLEM_CODE'
AND LANGUAGE = userenv('LANG');
SELECT description
FROM fnd_lookup_values
WHERE lookup_code = p_resolution_code
AND lookup_type = 'REQUEST_RESOLUTION_CODE'
AND LANGUAGE = userenv('LANG');
SELECT nttl.notes note_text,
lkp.meaning note_status,
rs.source_name entered_by,
ADJUST_TIME(ntb.entered_date) entered_date
FROM cs_incidents_all_b cs,
jtf_notes_b ntb,
jtf_notes_tl nttl,
jtf_rs_resource_extns rs,
fnd_lookup_values lkp
WHERE cs.incident_number = p_incident_number
AND ntb.source_object_id = cs.incident_id
AND ntb.source_object_code = 'SR'
AND nttl.jtf_note_id = ntb.jtf_note_id
AND nttl.LANGUAGE = userenv('LANG')
AND rs.user_id = ntb.entered_by
AND lkp.lookup_code = ntb.note_status
AND lkp.lookup_type = 'JTF_NOTE_STATUS'
AND lkp.LANGUAGE = userenv('LANG');
SELECT csi.instance_id,
cs.contract_service_id
FROM csi_item_instances csi,
cs_incidents_all_b cs,
jtf_tasks_b tsk,
jtf_task_assignments ass
WHERE csi.serial_number = p_serial_number
AND csi.instance_id = cs.customer_product_id
AND tsk.source_object_id = cs.incident_id
AND tsk.source_object_type_code = 'SR'
AND ass.task_id = tsk.task_id
AND ass.resource_id = p_resource_id;
SELECT csi.instance_id,
cs.contract_service_id
FROM csi_item_instances csi,
cs_incidents_all_b cs,
jtf_tasks_b tsk,
jtf_task_assignments ass
WHERE csi.serial_number = p_serial_number
AND csi.instance_id = cs.customer_product_id
AND tsk.source_object_id = cs.incident_id
AND tsk.source_object_type_code = 'SR'
AND ass.task_id = tsk.task_id
AND ass.resource_type_code ='RS_GROUP'
AND ass.resource_id IN( SELECT gm.GROUP_ID from
jtf_rs_role_relations rr,
jtf_rs_roles_b rb,
jtf_rs_group_members gm
WHERE rr.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
AND rr.ROLE_RESOURCE_ID = p_resource_id
AND rr.ROLE_ID = rb.ROLE_ID
AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
--AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID'
AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR'
and RR.ROLE_RESOURCE_ID = GM.RESOURCE_ID);
SELECT csi.instance_id,
cs.contract_service_id
FROM csi_item_instances csi,
cs_incidents_all_b cs,
jtf_tasks_b tsk,
JTF_TASK_ASSIGNMENTS ASS,
JTF_RS_GROUP_MEMBERS jtm
WHERE csi.serial_number = p_serial_number
AND csi.instance_id = cs.customer_product_id
AND tsk.source_object_id = cs.incident_id
AND tsk.source_object_type_code = 'SR'
and ASS.TASK_ID = TSK.TASK_ID
and ASS.RESOURCE_TYPE_CODE ='RS_EMPLOYEE'
and ASS.RESOURCE_ID = JTM.RESOURCE_ID
AND jtm.GROUP_ID IN( SELECT gm.GROUP_ID from
jtf_rs_role_relations rr,
jtf_rs_roles_b rb,
jtf_rs_group_members gm
WHERE rr.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
AND rr.ROLE_RESOURCE_ID = p_resource_id
AND rr.ROLE_ID = rb.ROLE_ID
AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
--AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID'
and RB.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR'
and RR.ROLE_RESOURCE_ID = GM.RESOURCE_ID);
l_query_text := 'SELECT query_name command_name,
description
FROM csm_query_tl qtl,
csm_query_b qb
WHERE UPPER(qb.query_name) like UPPER(''%'|| trim(p_query_name) ||'%'')
AND qb.email_enabled = ''Y''
AND NVL(qb.disabled_flag,''N'') = ''N''
AND qtl.language = userenv(''LANG'')
AND qb.query_id = qtl.query_id';
SELECT wf.name as role_name, fu.user_name as user_name
FROM wf_local_roles wf,
fnd_user fu
WHERE wf.email_address = p_email_id
AND fu.user_id = p_user_id
AND wf.status = 'ACTIVE'
AND wf.start_date <= sysdate
AND (wf.expiration_date IS NULL OR wf.expiration_date > sysdate);
SELECT
B.SEGMENT1,
TL.DESCRIPTION,
(SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
LOOKUP_TYPE = 'CSP_RECOVERED_PART_DISP_CODE'
AND LOOKUP_CODE = B.RECOVERED_PART_DISP_CODE
AND LANGUAGE = USERENV('LANG')
) AS PART_DISPOSITION,
DECODE(NVL(B.REVISION_QTY_CONTROL_CODE,'1'),'2', 'Yes','1','No') AS REVISION,
(SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
LOOKUP_TYPE = 'MTL_LOCATOR_RESTRICTIONS'
AND LOOKUP_CODE = B.RESTRICT_LOCATORS_CODE
AND LANGUAGE = USERENV('LANG')
) AS RESTRICT_LOCATORS_CODE
,
(SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
LOOKUP_TYPE = 'MTL_SUBINVENTORY_RESTRICTIONS'
AND LOOKUP_CODE = B.RESTRICT_SUBINVENTORIES_CODE
AND LANGUAGE = USERENV('LANG')
) AS RESTRICT_SUBINVENTORIES_CODE,
(SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
LOOKUP_TYPE = 'MTL_LOCATION_CONTROL'
AND LOOKUP_CODE = B.LOCATION_CONTROL_CODE
AND LANGUAGE = USERENV('LANG')
) AS LOCATION_CONTROL_CODE,
(SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
LOOKUP_TYPE = 'MTL_LOT_CONTROL'
AND LOOKUP_CODE = B.LOT_CONTROL_CODE
AND LANGUAGE = USERENV('LANG')
) AS LOT_CONTROL_CODE,
(SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
LOOKUP_TYPE = 'MTL_SERIAL_NUMBER'
AND LOOKUP_CODE = B.SERIAL_NUMBER_CONTROL_CODE
AND LANGUAGE = USERENV('LANG')
) AS SERIAL_NUMBER_CONTROL_CODE ,
DECODE(NVL(B.COMMS_NL_TRACKABLE_FLAG,'N'),'Y', 'Yes','N','No') AS IB_TRACKABLE
FROM MTL_SYSTEM_ITEMS_B B,MTL_SYSTEM_ITEMS_TL TL
WHERE UPPER(B.SEGMENT1) = UPPER(p_item)
AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = USERENV('LANG')
AND B.ORGANIZATION_ID = p_org_id;
SELECT NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE ORGANIZATION_ID =p_org_id
AND LANGUAGE = USERENV('LANG');
SELECT B.SEGMENT1,
TL.DESCRIPTION,(SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
LOOKUP_TYPE = 'MTL_RELATIONSHIP_TYPES'
AND LOOKUP_CODE = R.RELATIONSHIP_TYPE_ID
AND LANGUAGE = USERENV('LANG')
) AS ITEM_TYPE,
R.RELATED_ITEM_ID
FROM
MTL_RELATED_ITEMS R,
MTL_SYSTEM_ITEMS_B B,
MTL_SYSTEM_ITEMS_TL TL
WHERE R.INVENTORY_ITEM_ID = p_INV_ITEM_ID
AND R.ORGANIZATION_ID = p_ORG_ID
AND R.RELATIONSHIP_TYPE_ID IN(2,8)
AND B.INVENTORY_ITEM_ID = R.RELATED_ITEM_ID
AND B.ORGANIZATION_ID = R.ORGANIZATION_ID
AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = USERENV('LANG');
SELECT B.INVENTORY_ITEM_ID,B.SEGMENT1,TL.DESCRIPTION
FROM
MTL_SYSTEM_ITEMS_B B,
MTL_SYSTEM_ITEMS_TL TL
WHERE UPPER(B.SEGMENT1) = UPPER(p_item)
AND B.ORGANIZATION_ID = p_ORG_ID
AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = USERENV('LANG');
SELECT SUBINVENTORY_CODE
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE INVENTORY_ITEM_ID = p_INV_ITEM_ID
AND ORGANIZATION_ID = p_ORG_ID
GROUP BY INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE
HAVING SUM (TRANSACTION_QUANTITY) > 0 ;
SELECT NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE ORGANIZATION_ID =p_org_id
AND LANGUAGE = USERENV('LANG');
SELECT B.INVENTORY_ITEM_ID,B.SEGMENT1,TL.DESCRIPTION
FROM
MTL_SYSTEM_ITEMS_B B,
MTL_SYSTEM_ITEMS_TL TL
WHERE UPPER(B.SEGMENT1) = UPPER(p_item)
AND B.ORGANIZATION_ID = p_ORG_ID
AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = USERENV('LANG');
SELECT NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE ORGANIZATION_ID =p_org_id
AND LANGUAGE = USERENV('LANG');
SELECT B.SEGMENT1,TL.DESCRIPTION
FROM
MTL_SYSTEM_ITEMS_B B,
MTL_SYSTEM_ITEMS_TL TL
WHERE B.INVENTORY_ITEM_ID = p_item_id
AND B.ORGANIZATION_ID = p_ORG_ID
AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = USERENV('LANG');
SELECT
SUPPLIED_ITEM_ID,
SUPPLIED_ITEM_TYPE,
SUPPLIED_QUANTITY,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
SOURCE_TYPE_CODE,
DISTANCE
FROM
csp_available_parts_temp;
SELECT resource_id INTO l_resource_id
FROM jtf_rs_resource_extns
WHERE user_id = G_USER_ID
AND category = 'EMPLOYEE';
SELECT B.INVENTORY_ITEM_ID,B.SEGMENT1,TL.DESCRIPTION
FROM
MTL_SYSTEM_ITEMS_B B,
MTL_SYSTEM_ITEMS_TL TL
WHERE B.segment1 = p_item
AND B.ORGANIZATION_ID = p_ORG_ID
AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = USERENV('LANG');
SELECT NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL
WHERE ORGANIZATION_ID =p_org_id
AND LANGUAGE = USERENV('LANG');
SELECT B.SEGMENT1,TL.DESCRIPTION
FROM
MTL_SYSTEM_ITEMS_B B,
MTL_SYSTEM_ITEMS_TL TL
WHERE B.INVENTORY_ITEM_ID = p_item_id
AND B.ORGANIZATION_ID = p_ORG_ID
AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
AND TL.LANGUAGE = USERENV('LANG');
SELECT ila.SUBINVENTORY_CODE , csi.condition_type
FROM CSP_INV_LOC_ASSIGNMENTS ila,
csp_sec_inventories csi
WHERE ila.RESOURCE_ID = p_resource_id
AND ila.RESOURCE_TYPE = 'RS_EMPLOYEE'
AND ila.ORGANIZATION_ID = p_org_id
AND csi.secondary_inventory_name = ila.subinventory_code
AND csi.organization_id = ila.organization_id;
SELECT
SUPPLIED_ITEM_ID,
SUPPLIED_ITEM_TYPE,
SUPPLIED_QUANTITY,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
SOURCE_TYPE_CODE,
DISTANCE
FROM
csp_available_parts_temp;
L_EXCESS_REC.LAST_UPDATED_BY := 1;
L_EXCESS_REC.LAST_UPDATE_DATE := SYSDATE;
L_EXCESS_REC.LAST_UPDATE_LOGIN := 1;
p_is_insert_record => 'N'
);
p_is_insert_record => 'N'
);