[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_task_sch_update_check varchar2(10);
l_task_priority_update_check varchar2(10);
SELECT distinct jtb.task_id,
jtb.scheduled_start_date,
jtb.scheduled_end_date,
jta.resource_id,
jta.resource_type_code,
jta.assignment_status_id
FROM jtf_tasks_b jtb,
jtf_task_assignments jta,
jtf_task_priorities_vl jp_vl,
jtf_task_types_vl jtt_vl
WHERE jta.task_assignment_id = v_task_assgn_id
and jta.task_id = jtb.task_id
and jtb.source_object_type_code = 'SR'
and nvl(jtb.scheduled_start_date, sysdate - 1) > sysdate
and nvl(jtb.scheduled_end_date, sysdate - 1) > sysdate
and jta.assignment_status_id in (
select
task_status_id
from
jtf_task_statuses_b
where
usage = 'TASK'
and nvl(assigned_flag, 'N') = 'Y'
and nvl(assignment_status_flag, 'N') = 'Y'
and sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate + 1)
)
and jtb.task_type_id = jtt_vl.task_type_id
and jtt_vl.task_type_id in (
select
task_type_id
from
JTF_TASK_TYPES_B
where
rule = 'DISPATCH'
) -- only dispatch tasks
and nvl(jtt_vl.schedule_flag, 'N') = 'Y' -- schedulable tasks
and sysdate between nvl(jtt_vl.start_date_active, sysdate)
and nvl(jtt_vl.end_date_active, sysdate + 1)
and jtb.task_priority_id = jp_vl.task_priority_id
and sysdate between nvl(jp_vl.start_date_active, sysdate)
and nvl(jp_vl.end_date_active, sysdate + 1)
and jp_vl.importance_level <= fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
getUserId(jta.resource_id, jta.resource_type_code),
21685,
513,
null,
null);
SELECT distinct jtb.task_id,
jtb.scheduled_start_date,
jtb.scheduled_end_date,
jta.resource_id,
jta.resource_type_code
FROM jtf_tasks_b jtb,
jtf_task_assignments jta,
jtf_task_priorities_vl jp_vl,
jtf_task_types_vl jtt_vl
WHERE jta.task_assignment_id = v_task_assgn_id
and jta.task_id = jtb.task_id
and jtb.source_object_type_code = 'SR'
and nvl(jtb.scheduled_start_date, sysdate - 1) > sysdate
and nvl(jtb.scheduled_end_date, sysdate - 1) > sysdate
and jtb.task_type_id = jtt_vl.task_type_id
and jtt_vl.task_type_id in (
select
task_type_id
from
JTF_TASK_TYPES_B
where
rule = 'DISPATCH'
) -- only dispatch tasks
and jtt_vl.schedule_flag = 'Y' -- schedulable tasks
and sysdate between nvl(jtt_vl.start_date_active, sysdate)
and nvl(jtt_vl.end_date_active, sysdate + 1)
and jtb.task_priority_id = jp_vl.task_priority_id
and sysdate between nvl(jp_vl.start_date_active, sysdate)
and nvl(jp_vl.end_date_active, sysdate + 1)
and jp_vl.importance_level <= fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
getUserId(jta.resource_id, jta.resource_type_code),
21685,
513,
null,
null);
select 'TRUE' from dual where v_old_status_id in (select
task_status_id
from
jtf_task_statuses_b
where
usage = 'TASK'
and nvl(assigned_flag, 'N') <> 'Y'
and nvl(assignment_status_flag, 'N') = 'Y'
and sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate + 1))
and v_new_status_id in (select
task_status_id
from
jtf_task_statuses_b
where
usage = 'TASK'
and nvl(assigned_flag, 'N') = 'Y'
and nvl(assignment_status_flag, 'N') = 'Y'
and sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate + 1));
SELECT jtb.scheduled_start_date,
jtb.scheduled_end_date
FROM jtf_tasks_b jtb,
jtf_task_priorities_vl jp_vl,
jtf_task_types_vl jtt_vl
WHERE jtb.task_id = v_task_id
and nvl(jtb.scheduled_start_date, sysdate - 1) > sysdate
and jtb.source_object_type_code = 'SR'
and jtb.task_type_id = jtt_vl.task_type_id
and jtt_vl.task_type_id in (
select
task_type_id
from
JTF_TASK_TYPES_B
where
rule = 'DISPATCH'
) -- only dispatch tasks
and jtt_vl.schedule_flag = 'Y' -- schedulable tasks
and sysdate between nvl(jtt_vl.start_date_active, sysdate)
and nvl(jtt_vl.end_date_active, sysdate + 1)
and jtb.task_priority_id = jp_vl.task_priority_id
and sysdate between nvl(jp_vl.start_date_active, sysdate)
and nvl(jp_vl.end_date_active, sysdate + 1)
and jp_vl.importance_level <= fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
getUserId(v_resource_id, v_resource_type_code),
21685,
513,
null,
null);
cursor c_task_update_check (v_task_id number, v_task_audit_id number) is
select
(select
'TRUE'
from
jtf_task_audits_b jtab,
jtf_tasks_b jtb
where
jtb.task_id = v_task_id
and jtb.source_object_type_code = 'SR'
and jtb.task_id = jtab.task_id
and jtab.task_audit_id = v_task_audit_id
and (jtab.new_scheduled_start_date <> jtab.old_scheduled_start_date
or jtab.new_scheduled_end_date <> jtab.old_scheduled_end_date)) as is_schedule_dates,
(select
'TRUE'
from
jtf_task_audits_b jtab,
jtf_tasks_b jtb
where
jtb.task_id = v_task_id
and jtb.source_object_type_code = 'SR'
and jtb.task_id = jtab.task_id
and jtab.task_audit_id = v_task_audit_id
and jtab.new_task_priority_id <> jtab.old_task_priority_id) as is_priority
from dual;
select
task_assignment_Id
from
jtf_task_assignments
where task_id = v_task_id;
select
'TRUE'
from
jtf_task_priorities_vl jp_vl1,
jtf_task_priorities_vl jp_vl2,
jtf_task_audits_b jtab
where
jtab.task_id = v_task_id
and jtab.task_audit_id = v_task_audit_id
and jp_vl1.task_priority_id = jtab.new_task_priority_id
and jp_vl1.importance_level <= fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
getUserId(v_resource_id, v_resource_type),
21685,
513,
null,
null)
and jp_vl2.task_priority_id = jtab.old_task_priority_id
and jp_vl2.importance_level > fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
getUserId(v_resource_id, v_resource_type),
21685,
513,
null,
null);
elsif (l_event_name = 'oracle.apps.jtf.cac.task.deleteTaskAssignment') then -- event type delete
l_task_id := p_event.GetValueForParameter('TASK_ID');
l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'DELETE');
l_event_key := getItemKey('oracle.apps.csf.deleteTaskAssignment',
l_resource_id,
l_resource_type_code,
l_task_asgn_id,
p_event.getEventKey());
elsif (l_event_name = 'oracle.apps.jtf.cac.task.updateTaskAssignment') then -- event type update assignment
l_old_resource := p_event.GetValueForParameter('OLD_RESOURCE_ID');
l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'DELETE_FOR_UPDATE');
l_event_key := getItemKey('oracle.apps.csf.deleteTaskAssignment',
l_old_resource,
l_old_resource_type,
l_task_asgn_id,
p_event.getEventKey());
l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'DELETE');
l_event_key := getItemKey('oracle.apps.csf.deleteTaskAssignment',
l_resource_id,
l_resource_type_code,
l_task_asgn_id,
p_event.getEventKey());
elsif (l_event_name = 'oracle.apps.jtf.cac.task.updateTask') then
l_task_id := p_event.GetValueForParameter('TASK_ID');
open c_task_update_check (l_task_id, l_task_audit_id);
fetch c_task_update_check into l_task_sch_update_check, l_task_priority_update_check;
close c_task_update_check;
if l_task_sch_update_check = 'TRUE' or l_task_priority_update_check = 'TRUE' then
-- fetch all the task assignemnts and resources
-- loop for each assignment and check other conditions
open c_get_all_assignments(l_task_id);
if l_task_priority_update_check = 'TRUE' then
-- check if new priority is higher than the profile and
-- old priority should be less than
open c_priority_test(l_task_id, l_task_audit_id, l_resource_id, l_resource_type_code);
elsif l_task_sch_update_check = 'TRUE' then
-- raise and sch_dates update alert
if checkAlertsEnabled(l_resource_id, l_resource_type_code) then
l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'SCH_UPDATE');
l_event_key := getItemKey('oracle.apps.csf.updateScheduleDates',
l_resource_id,
l_resource_type_code,
l_task_asgn_id,
p_event.getEventKey());
end if; -- event type update assignment over
select
jr.resource_id,
jt.resource_id,
jt.resource_type_code,
jt.assignment_status_id
from
jtf_task_assignments jt,
jtf_rs_resource_extns jr
where
jt.task_assignment_id = v_task_asgn_id
and jt.resource_id = jr.resource_id
and category_type(jt.resource_type_code) = jr.category;
elsif l_csfw_event_type = 'DELETE' or l_csfw_event_type = 'DELETE_FOR_UPDATE' then
if l_csfw_event_type = 'DELETE' then
l_resource_id := p_event.GetValueForParameter('RESOURCE_ID');
l_csfw_event_type := 'DELETE_EVENT';
elsif l_csfw_event_type = 'SCH_UPDATE' then
l_csfw_event_type := 'SCH_UPDATE_EVENT';
SELECT distinct jtb.task_id,
jta.resource_id,
jta.resource_type_code,
jta.assignment_status_id
FROM jtf_tasks_b jtb,
jtf_task_assignments jta,
jtf_task_priorities_vl jp_vl,
jtf_task_types_vl jtt_vl
WHERE jta.task_assignment_id = v_task_assgn_id
and jta.task_id = jtb.task_id
and nvl(jtb.scheduled_start_date, sysdate - 1) > sysdate
and nvl(jtb.scheduled_end_date, sysdate - 1) > sysdate
and jta.assignment_status_id in (
select
task_status_id
from
jtf_task_statuses_b
where
usage = 'TASK'
and nvl(assigned_flag, 'N') = 'Y'
and nvl(assignment_status_flag, 'N') = 'Y'
and sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate + 1)
)
and jtb.task_type_id = jtt_vl.task_type_id
and jtt_vl.task_type_id in (
select
task_type_id
from
JTF_TASK_TYPES_B
where
rule = 'DISPATCH'
) -- only dispatch tasks
and nvl(jtt_vl.schedule_flag, 'N') = 'Y' -- schedulable tasks
and sysdate between nvl(jtt_vl.start_date_active, sysdate)
and nvl(jtt_vl.end_date_active, sysdate + 1)
and jtb.task_priority_id = jp_vl.task_priority_id
and sysdate between nvl(jp_vl.start_date_active, sysdate)
and nvl(jp_vl.end_date_active, sysdate + 1)
and jp_vl.importance_level <= fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
getUserId(jta.resource_id, jta.resource_type_code),
21685,
513,
null,
null);
SELECT jta.object_version_number, jta.assignment_status_id
FROM
jtf_task_assignments jta
WHERE jta.task_assignment_id = v_task_assgn_id;
csf_task_assignments_pub.update_assignment_status(
p_api_version => 1.0,
p_task_assignment_id => l_task_assgn_id,
p_assignment_status_id => fnd_profile.VALUE_SPECIFIC('CSF_DEFAULT_ACCEPTED_STATUS',
getUserId(l_resource_id, l_resource_type),
21685,
513,
null,
null), -- accepted
p_object_version_number => l_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_task_object_version_number => l_task_object_version_number,
x_task_status_id => l_task_status_id
);
SELECT jta.object_version_number, jta.assignment_status_id
FROM
jtf_task_assignments jta
WHERE jta.task_assignment_id = v_task_assgn_id;
select task_id
from jtf_task_assignments
where task_assignment_id = v_task_asgn_id;
csf_task_assignments_pub.update_assignment_status(
p_api_version => 1.0,
p_task_assignment_id => l_task_assgn_id,
p_assignment_status_id => fnd_profile.VALUE_SPECIFIC('CSF_DEFAULT_REJECTED_STATUS',
getUserId(l_resource_id, l_resource_type),
21685,
513,
null,
null), -- Rejected
p_object_version_number => l_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_task_object_version_number => l_task_object_version_number,
x_task_status_id => l_task_status_id
);
select user_id from jtf_rs_resource_extns where resource_id = v_resource_id and category = v_category;
select
n.notes
from
jtf_notes_vl n,
jtf_task_assignments a
where
n.source_object_code = 'TASK'
and n.source_object_id = a.task_id
and a.task_assignment_id = v_task_asgn_id
union select
n.notes
from
jtf_notes_vl n,
jtf_task_assignments a,
jtf_tasks_b t
where
n.source_object_code = 'SR'
and n.source_object_id = t.source_object_id
and t.task_id = a.task_id
and a.task_assignment_id = v_task_asgn_id;
select
n.notes
from
jtf_notes_vl n,
jtf_task_assignments a
where
n.source_object_code = 'TASK'
and n.source_object_id = a.task_id
and a.task_assignment_id = v_task_asgn_id
union select
n.notes
from
jtf_notes_vl n,
jtf_task_assignments a,
jtf_tasks_b t
where
n.source_object_code = 'SR'
and n.source_object_id = t.source_object_id
and t.task_id = a.task_id
and a.task_assignment_id = v_task_asgn_id;
procedure getDeleteMessage(document_id varchar2,
display_type varchar2,
document in out nocopy varchar2,
document_type in out nocopy varchar2) is
l_resource_id number;
select
n.notes
from
jtf_notes_vl n
where
n.source_object_code = 'TASK'
and n.source_object_id = v_task_id
union select
n.notes
from
jtf_notes_vl n,
jtf_tasks_b t
where
n.source_object_code = 'SR'
and n.source_object_id = t.source_object_id
and t.task_id = v_task_id;
fnd_message.set_name('CSF', 'CSF_ALERTS_DELETE_HDR');
select
n.notes
from
jtf_notes_vl n
where
n.source_object_code = 'TASK'
and n.source_object_id = v_task_id
union select
n.notes
from
jtf_notes_vl n,
jtf_tasks_b t
where
n.source_object_code = 'SR'
and n.source_object_id = t.source_object_id
and t.task_id = v_task_id;
SELECT
per.full_name contactname,
per.email_address email,
ph.phone_number phone_number
FROM
per_all_people_f per,
per_phones ph
WHERE
per.person_id = v_id
and per.person_id = ph.parent_id
and ph.phone_type = 'W1'
AND ph.parent_table = 'PER_ALL_PEOPLE_F'
AND sysdate between nvl(per.effective_start_date, sysdate)
and nvl(per.effective_end_date, sysdate);
SELECT
hp.person_first_name ||' '|| hp.person_last_name contactname,
hp.email_address email
FROM
hz_relationships rel,
hz_parties hp
WHERE
rel.party_id = v_id
AND rel.subject_id = hp.party_id
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.subject_type = 'PERSON';
Select PARTY_NAME, EMAIL_ADDRESS from hz_parties where party_id = v_id;
SELECT
hcp.phone_country_code || ' ' || hcp.phone_area_code || ' ' || hcp.phone_number PHONE_NUMBER
FROM
cs_incidents_all_b ci_all_b,
cs_hz_sr_contact_points_v chscp,
hz_contact_points hcp
WHERE
ci_all_b.incident_id = chscp.incident_id
AND chscp.contact_point_id = hcp.contact_point_id
AND chscp.primary_flag = 'Y'
AND hcp.contact_point_type = 'PHONE'
AND ci_all_b.incident_id = v_incident_id;
SELECT
c_b.incident_id incident_id,
c_b.incident_number sr_number,
c_b.summary sr_summary,
hp.party_name cust_name,
hp.address1 || ', ' || hp.postal_code || ', ' || hp.city address,
jtb.task_number task_number,
j_vl.task_name task_name,
js_vl.name assignment_name,
jp_vl.name priority,
j_vl.PLANNED_EFFORT || ' ' || j_vl.PLANNED_EFFORT_UOM planned_effort,
jtb.scheduled_start_date sch_st_date,
jtb.scheduled_end_date sch_end_date,
j_vl.description task_desc,
msi_b.concatenated_segments product_nr,
ccp_all.current_serial_number item_serial,
msi_b.description item_description,
chscp.contact_type contact_type,
chscp.party_id contact_party_id
FROM
jtf_tasks_b jtb,
jtf_task_assignments jta,
jtf_tasks_vl j_vl,
jtf_task_priorities_vl jp_vl,
jtf_task_statuses_vl js_vl,
cs_incidents_all c_b,
hz_party_sites hps,
hz_parties hp,
mtl_system_items_vl msi_b,
cs_customer_products_all ccp_all,
cs_hz_sr_contact_points_v chscp
WHERE
jta.task_assignment_id = v_task_assgn_id
and jta.task_id = jtb.task_id
and j_vl.task_id = jta.task_id
and jp_vl.task_priority_id (+) = j_vl.task_priority_id
and js_vl.task_status_id = jta.assignment_status_id
and jtb.source_object_type_code = 'SR'
and jtb.source_object_id = c_b.incident_id
and jtb.address_id = hps.party_site_id
and hps.party_id = hp.party_id
and c_b.inventory_item_id = msi_b.inventory_item_id (+)
and c_b.customer_product_id = ccp_all.customer_product_id(+)
and msi_b.organization_id (+) = c_b.org_id
and chscp.primary_flag (+) = 'Y'
and chscp.incident_id (+) = c_b.incident_id;
SELECT
c_b.incident_id incident_id,
c_b.incident_number sr_number,
c_b.summary sr_summary,
hp.party_name cust_name,
hp.address1 || ', ' || hp.postal_code || ', ' || hp.city address,
jtb.task_number task_number,
j_vl.task_name task_name,
jp_vl.name priority,
j_vl.PLANNED_EFFORT || ' ' || j_vl.PLANNED_EFFORT_UOM planned_effort,
jtb.scheduled_start_date sch_st_date,
jtb.scheduled_end_date sch_end_date,
j_vl.description task_desc,
msi_b.concatenated_segments product_nr,
ccp_all.current_serial_number item_serial,
msi_b.description item_description,
chscp.contact_type contact_type,
chscp.party_id contact_party_id
FROM
jtf_tasks_b jtb,
jtf_tasks_vl j_vl,
jtf_task_priorities_vl jp_vl,
cs_incidents_all c_b,
hz_party_sites hps,
hz_parties hp,
mtl_system_items_vl msi_b,
cs_customer_products_all ccp_all,
cs_hz_sr_contact_points_v chscp
WHERE
jtb.task_id = v_task_id
and j_vl.task_id = jtb.task_id
and jp_vl.task_priority_id (+) = j_vl.task_priority_id
and jtb.source_object_type_code = 'SR'
and jtb.source_object_id = c_b.incident_id
and jtb.address_id = hps.party_site_id
and hps.party_id = hp.party_id
and c_b.inventory_item_id = msi_b.inventory_item_id (+)
and c_b.customer_product_id = ccp_all.customer_product_id(+)
and msi_b.organization_id (+) = c_b.org_id
and chscp.primary_flag (+) = 'Y'
and chscp.incident_id (+) = c_b.incident_id;
SELECT
c_b.incident_id incident_id,
c_b.incident_number sr_number,
c_b.summary sr_summary,
hp.party_name cust_name,
hp.address1 || ', ' || hp.postal_code || ', ' || hp.city address,
jtb.task_number task_number,
j_vl.task_name task_name,
jp_vl.name priority,
j_vl.PLANNED_EFFORT || ' ' || j_vl.PLANNED_EFFORT_UOM planned_effort,
jtb.scheduled_start_date sch_st_date,
jtb.scheduled_end_date sch_end_date,
j_vl.description task_desc,
msi_b.concatenated_segments product_nr,
ccp_all.current_serial_number item_serial,
msi_b.description item_description,
chscp.contact_type contact_type,
chscp.party_id contact_party_id,
jtab.old_scheduled_start_date old_sch_st_date,
jtab.old_scheduled_end_date old_sch_end_date
FROM
jtf_tasks_b jtb,
jtf_task_audits_b jtab,
jtf_tasks_vl j_vl,
jtf_task_priorities_vl jp_vl,
cs_incidents_all c_b,
hz_party_sites hps,
hz_parties hp,
mtl_system_items_vl msi_b,
cs_customer_products_all ccp_all,
cs_hz_sr_contact_points_v chscp
WHERE
jtb.task_id = v_task_id
and jtab.task_audit_id = v_task_audit_id
and jtab.task_id = jtb.task_id
and j_vl.task_id = jtb.task_id
and jp_vl.task_priority_id (+) = j_vl.task_priority_id
and jtb.source_object_type_code = 'SR'
and jtb.source_object_id = c_b.incident_id
and jtb.address_id = hps.party_site_id
and hps.party_id = hp.party_id
and c_b.inventory_item_id = msi_b.inventory_item_id (+)
and c_b.customer_product_id = ccp_all.customer_product_id(+)
and msi_b.organization_id (+) = c_b.org_id
and chscp.primary_flag (+) = 'Y'
and chscp.incident_id (+) = c_b.incident_id;
select
j.user_name
from
jtf_rs_resource_extns j,
wf_roles w
where
j.resource_id = v_resource_id
and j.user_name = w.name;