[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
fnd_msg_pub.delete_msg;
Select csi.owner_party_id,
csi.install_location_id ,
csi.instance_id,
csi.instance_number,
csi.inventory_item_id,
csi.last_vld_organization_id,
csi.serial_number,
-- get install location only if type code is hz_party_sites or hz_locations
-- for SR validation
decode(csi.location_type_code,'HZ_PARTY_SITES',
csi.location_id,'HZ_LOCATIONS',
csi.location_id,null) location_id,
csi.location_type_code,
csi.owner_party_account_id,
csi.external_reference,
csi.system_id,
--- get BILL_TO,SHIP_TO parties
csi.owner_party_id billto_party_id,
csi.owner_party_id shipto_party_id,
--- get party_site_id of use type BILL_TO and SHIP_TO
aueb.unit_effectivity_id,
amh.title,
aueb.mr_header_id,
aueb.program_mr_header_id,
aueb.service_line_id contract_service_id,
aueb.due_date,
aueb.earliest_due_date,
aueb.latest_due_date,
amh.description,
hp.party_type,
okch.authoring_org_id
From ahl_unit_effectivities_app_v aueb,
csi_item_instances csi,
ahl_mr_headers_vl amh,
hz_parties hp ,
okc_k_lines_b okcl,
okc_k_headers_all_b okch
Where nvl(aueb.earliest_due_date,aueb.due_date) <= trunc(sysdate) + p_period_size
-- Get only the open UMPs and SR not created
and (aueb.status_code is NULL or aueb.status_code = 'INIT-DUE')
-- Application_usg_code PM for Preventive Maintenance seeded for CMRO 11.5.10 changes
and aueb.application_usg_code = 'PM'
and aueb.unit_effectivity_id not in (select object_id
from cs_incident_links cil
where cil.object_type = 'AHL_UMP_EFF'
and cil.link_type_id = 6)
-- link_type_id 6 is 'REFERS TO' seeded value
and csi.instance_id = aueb.csi_item_instance_id
and amh.mr_header_id = aueb.mr_header_id
and hp.party_id = csi.owner_party_id
and okcl.cle_id = aueb.service_line_id
and okcl.dnz_chr_id = okch.id
and okch.authoring_org_id = nvl(p_org_id, okch.authoring_org_id)
order by nvl(aueb.earliest_due_date,aueb.due_date);
Select arb.route_id,
arb.task_template_group_id
From ahl_routes_b arb,
ahl_mr_routes amr
Where amr.mr_header_id = p_mr_header_id
and arb.route_id = amr.route_id;
Select hr.party_id,
hcp.contact_point_id,
hcp.contact_point_type,
hcp.primary_flag,
decode(primary_flag,'Y','Y',NULL)
primary_contact,
timezone_id
From Hz_Relationships hr,
Hz_Parties hp_obj,
Hz_Parties hp_sub,
Hz_Contact_points hcp
Where hr.object_id = p_party_id
and hr.status = 'A'
and NVL(hr.start_date, SYSDATE-1) < SYSDATE
and NVL(hr.end_date, SYSDATE+1) > SYSDATE
and hp_sub.party_id = hr.subject_id
and hp_sub.status = 'A'
and hp_sub.party_type = 'PERSON'
and hp_obj.party_id = hr.object_id
and hp_obj.status = 'A'
and hp_obj.party_type = 'ORGANIZATION'
and hcp.owner_table_id(+) = hr.party_id
and hcp.owner_table_name(+) = 'HZ_PARTIES'
and hcp.status(+) = 'A'
and hr.party_id is not null;
select hr.object_id,max(hr.party_id) party_id
from hz_relationships hr,hz_parties hp
where hr.object_id in (p_billto_party,p_shipto_party)
AND hr.status = 'A'
AND NVL(hr.start_date, SYSDATE-1) < SYSDATE
AND NVL(hr.end_date, SYSDATE+1) > SYSDATE
AND hp.party_id = hr.subject_id
AND hp.party_type = 'PERSON'
AND hp.status = 'A'
group by hr.object_id;
select hps1.party_site_id billto_site_id,
hps2.party_site_id shipto_site_id
From hz_party_sites hps1,
hz_party_sites hps2,
hz_party_site_uses hpsu1,
hz_party_site_uses hpsu2
Where hps1.party_id = p_party_id
and hpsu1.party_site_id = hps1.party_site_id
and hpsu1.site_use_type = 'BILL_TO'
and hpsu1.status = 'A'
and hpsu1.primary_per_type = 'Y'
and trunc(SYSDATE) BETWEEN TRUNC(NVL(hpsu1.begin_date,SYSDATE)) and
TRUNC(NVL(hpsu1.end_date,SYSDATE))
and hps2.party_id = p_party_id
and hpsu2.party_site_id = hps2.party_site_id
and hpsu2.site_use_type = 'SHIP_TO'
and hpsu2.status = 'A'
and hpsu2.primary_per_type = 'Y'
and hps1.status = 'A'
and hps2.status = 'A'
and trunc(SYSDATE) between TRUNC(NVL(hpsu2.begin_date,SYSDATE)) and
TRUNC(NVL(hpsu2.end_date,SYSDATE));
select mic.category_set_id,
mic.category_id
from mtl_item_categories mic,
mtl_category_sets_b mcsb
where mic.inventory_item_id = p_inventory_item_id
and mic.category_set_id = mcsb.category_set_id
and mcsb.mult_item_cat_assign_flag = 'N'
and mic.organization_id = p_organization_id
and mic.category_set_id = fnd_profile.value('CS_SR_DEFAULT_CATEGORY_SET');
SELECT location_id from HZ_PARTY_SITES WHERE party_site_id = l_ump_rec.location_id;
SELECT * from csf_map_access_hours_vl where
customer_location_id = l_acchr_loc_id;
SELECT * from csf_map_access_hours_vl where
customer_id = l_acchr_ct_id and
customer_site_id = l_acchr_ct_site_id;
SELECT * from csf_map_access_hours_vl where
customer_id = l_acchr_ct_id;
select org_id from fnd_concurrent_requests
where request_id = fnd_profile.value ('CONC_REQUEST_ID');
l_service_request_rec.last_update_program_code := 'PMCON';
l_contacts_table.DELETE;
Instead, after creating the SR, we update the SR with the list of contacts.
Update_ServiceRequest() API has no such restriction as it's create counterpart does.
The Update_ServiceRequest() API is also newly added to address the same ER.
*/
FND_FILE.put_line(FND_FILE.log,'MESSAGE: before calling cs_servicerequest_pub.Create_ServiceRequest API');
Calling Update_ServiceRequest() API to update the just created SR
with the list of contacts
*/
l_index := l_contacts_table.FIRST;
CS_ServiceRequest_PUB.Update_ServiceRequest
(
p_api_version => 3.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_request_id => x_request_id,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_object_version_number=> x_object_version_number,
p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
p_resp_id => FND_GLOBAL.RESP_ID,
p_service_request_rec => l_service_request_rec,
p_notes => l_no_notes_table,
p_contacts => l_contacts_table,
x_interaction_id => x_interaction_id,
x_workflow_process_id => x_workflow_process_id
);
fnd_message.set_name('CSF','CSF_PM_SR_UPDATE_ERROR');
l_link_rec.program_update_date := sysdate;
x_task_details_tbl.delete;
-- If customer conformation required then update all
-- the tasks statuses to CONFIRM status from profile option
-- csfpm_task_confirm_status
If x_pm_conf_reqd = 'Y' Then
SAVEPOINT Generate_SR_Tasks_PVT;
SELECT task_id,object_version_number
BULK COLLECT INTO conf_task_id,conf_object_version_number
FROM jtf_tasks_b
WHERE source_object_id = x_request_id;
csf_tasks_pub.update_cust_confirmation(
p_api_version =>1.0
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_id => conf_task_id(i)
, p_object_version_number => conf_object_version_number(i)
, p_action => csf_tasks_pub.g_action_conf_to_required
, p_initiated => csf_tasks_pub.g_dispatcher_initiated
);
fnd_message.set_name('CSF','CSF_PM_TASK_UPDATE_ERROR');
SELECT task_id,object_version_number
BULK COLLECT INTO conf_task_id,conf_object_version_number
FROM jtf_tasks_b
WHERE source_object_id = x_request_id;
p_LAST_UPDATED_BY => null,
p_LAST_UPDATE_DATE => null,
p_LAST_UPDATE_LOGIN => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
fnd_message.set_name('CSF','CSF_PM_TASK_UPDATE_ERROR');
PROCEDURE update_ump (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
P_Api_Version_Number IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_UMP';
Select aueb.unit_effectivity_id,
cil.subject_id incident_id,
csi.close_date,
csi.customer_product_id,
cccv.counter_id,
cccv.counter_name,
nvl(cccv.net_reading,0) net_reading
From ahl_unit_effectivities_app_v aueb,
cs_incident_links cil,
cs_incidents_all_b csi,
csi_cp_counters_v cccv
Where (aueb.status_code is NULL
or aueb.status_code = 'INIT-DUE')
-- Application_usg_code PM for Preventive Maintenance seeded for CMRO 11.5.10 changes
and aueb.application_usg_code = 'PM'
and cil.object_id = aueb.unit_effectivity_id
and cil.object_type = 'AHL_UMP_EFF'
and cil.link_type_id = 6
and csi.incident_id = cil.subject_id
and csi.status_flag = 'C'
and cccv.customer_product_id(+) = aueb.csi_item_instance_id
order by aueb.unit_effectivity_id;
SAVEPOINT update_ump_pvt;
-- Values to update AHL_UNIT_EFFECTIVITIES_B table.This
-- check is to Record only unique values of unit_effectivity_id
l_index := l_index + 1;
-- Counter values to update the ahl_unit_accomplishmnts table
-- for accomplished UMPs. A product instance can have more than one
-- counter
l_count := l_count + 1;
ahl_ump_unitmaint_pub.capture_mr_updates');
ahl_ump_unitmaint_pub.capture_mr_updates
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_unit_effectivity_tbl => l_unit_effectivity_tbl,
p_x_unit_threshold_tbl => l_unit_threshold_tbl,
p_x_unit_accomplish_tbl => l_unit_accomplish_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ahl_ump_unitmaint_pub.capture_mr_updates x_return_status=' || x_return_status);
End update_ump;
PROCEDURE update_sr_tasks (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
P_Api_Version_Number IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_sr_tasks';
Select aueb.unit_effectivity_id,
cil.subject_id ,
cil.link_id,
csi.close_date,
csi.customer_product_id,
csi.object_version_number,
jtb.task_id,
jtb.planned_start_date,
jtb.planned_end_date,
jtb.scheduled_start_date,
jtb.scheduled_end_date,
jtb.actual_start_date,
jtb.actual_end_date,
jtb.object_version_number tasks_object_version
From ahl_unit_effectivities_app_v aueb,
cs_incident_links cil,
cs_incidents_all_b csi,
jtf_tasks_b jtb
Where aueb.status_code in ('TERMINATED','EXCEPTION')
-- Application_usg_code PM for Preventive Maintenance seeded for CMRO 11.5.10 changes
and aueb.application_usg_code = 'PM'
and cil.object_id = aueb.unit_effectivity_id
and cil.object_type = 'AHL_UMP_EFF'
and cil.link_type_id = 6
and csi.incident_id = cil.subject_id
and jtb.source_object_id = cil.subject_id
order by cil.subject_id;
sr_update_success BOOLEAN;
SAVEPOINT update_sr_tasks_pvt;
SAVEPOINT update_sr_tasks_pvt;
sr_update_success := FALSE;
cs_servicerequest_pub.Update_Status
(p_api_version => 2.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
p_resp_id => FND_GLOBAL.RESP_ID,
p_user_id => FND_GLOBAL.USER_ID,
p_login_id => fnd_global.conc_login_id,
p_request_id => incident_rec.subject_id,
p_request_number => NULL,
p_object_version_number => incident_rec.object_version_number,
-- status code CLOSED seeded in cs_incident_statuses
p_status_id => 4,
p_status => 'CLOSED',
x_interaction_id => x_interaction_id);
fnd_message.set_name('CSF','CSF_PM_SR_UPDATE_ERROR');
ROLLBACK TO update_sr_tasks_pvt;
cs_incidentlinks_pub.DELETE_INCIDENTLINK (
P_API_VERSION => 2.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
P_RESP_APPL_ID => NULL,
P_RESP_ID => NULL,
P_USER_ID => NULL,
P_LOGIN_ID => FND_GLOBAL.CONC_LOGIN_ID,
P_ORG_ID => NULL,
P_LINK_ID => incident_rec.link_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data);
fnd_message.set_name('CSF','CSF_PM_SR_LINK_DELETE_ERROR');
ROLLBACK TO update_sr_tasks_pvt;
sr_update_success := TRUE;
If sr_update_success Then
csf_tasks_pub.Update_Task
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => fnd_api.g_false,
p_task_id => incident_rec.task_id,
p_object_version_number => incident_rec.tasks_object_version,
p_planned_start_date => incident_rec.planned_start_date,
p_planned_end_date => incident_rec.planned_end_date,
p_scheduled_start_date => incident_rec.scheduled_start_date,
p_scheduled_end_date => incident_rec.scheduled_end_date,
p_actual_start_date => incident_rec.actual_start_date,
p_actual_end_date => incident_rec.actual_end_date,
p_task_status_id => fnd_profile.value('csf_default_task_cancelled_status'), -- Task cancelled status
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
fnd_message.set_name('CSF','CSF_PM_TASK_UPDATE_ERROR');
ROLLBACK TO update_sr_tasks_pvt;
End update_sr_tasks;