The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Select all inq records */
CURSOR c_incident( b_user_name VARCHAR2, b_tranid NUMBER) is
SELECT *
FROM CSM_INCIDENTS_ALL_INQ
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name;
/* Select all contact records for incident from inq table */
CURSOR c_contact( b_incident_id NUMBER, b_tranid NUMBER, b_user_name VARCHAR2 ) IS
SELECT *
FROM CSF_M_SR_CONTACTS_INQ
WHERE INCIDENT_ID = b_incident_id
AND TRANID$$ = b_tranid
AND clid$$cs = b_user_name;
SELECT APP_ID
FROM asg_user
WHERE user_id = l_userid;
SELECT RESPONSIBILITY_ID
FROM asg_user
WHERE user_id = l_userid;
SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
/* Cursor to select party Type */
CURSOR c_party ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE )
IS
SELECT PARTY_TYPE
FROM HZ_PARTIES
WHERE party_id = b_customer_id;
SELECT INSTANCE_ID, INVENTORY_ITEM_ID, LAST_VLD_ORGANIZATION_ID,
OWNER_PARTY_ID, INSTALL_LOCATION_ID, OWNER_PARTY_ACCOUNT_ID,
SERIAL_NUMBER, INVENTORY_REVISION,
DECODE(INSTALL_LOCATION_TYPE_CODE,'HZ_PARTY_SITES','HZ_PARTY_SITE','HZ_LOCATIONS','HZ_LOCATION', NULL) AS INSTALL_LOCATION_TYPE_CODE,
LOCATION_ID, DECODE(LOCATION_TYPE_CODE,'HZ_PARTY_SITES','HZ_PARTY_SITE','HZ_LOCATIONS','HZ_LOCATION', NULL) AS LOCATION_TYPE_CODE
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_NUMBER = c_instance_number;
SELECT INSTANCE_ID, INVENTORY_ITEM_ID, LAST_VLD_ORGANIZATION_ID,
OWNER_PARTY_ID, INSTALL_LOCATION_ID, OWNER_PARTY_ACCOUNT_ID,
SERIAL_NUMBER, INVENTORY_REVISION,
DECODE(INSTALL_LOCATION_TYPE_CODE,'HZ_PARTY_SITES','HZ_PARTY_SITE','HZ_LOCATIONS','HZ_LOCATION', NULL) AS INSTALL_LOCATION_TYPE_CODE,
LOCATION_ID, DECODE(LOCATION_TYPE_CODE,'HZ_PARTY_SITES','HZ_PARTY_SITE','HZ_LOCATIONS','HZ_LOCATION', NULL) AS LOCATION_TYPE_CODE
FROM CSI_ITEM_INSTANCES
WHERE SERIAL_NUMBER = c_serial_number;
SELECT custa.cust_account_id
FROM hz_cust_accounts custa
WHERE custa.status = 'A'
AND custa.party_id = b_customer_id;
/* Cursor to select object_version_number */
CURSOR C_OVN( B_INCIDENT_ID NUMBER)
IS
SELECT INCIDENT_ID
, OBJECT_VERSION_NUMBER
FROM CS_INCIDENTS
WHERE INCIDENT_ID = B_INCIDENT_ID;
This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_INSERT
(
p_record IN c_incident%ROWTYPE,
p_error_msg out nocopy VARCHAR2,
x_return_status IN out nocopy VARCHAR2
) IS
/* Bug 3917132
Cursor to get the Bill to Address */
CURSOR c_bill_to_site_id( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE)
IS
SELECT use.party_site_use_id
FROM hz_party_sites site, hz_party_site_uses use
WHERE site.party_site_id = use.party_site_id
AND site.status= 'A'
AND use.site_use_type= 'BILL_TO'
AND use.primary_per_type = 'Y'
AND use.status = 'A'
AND site.party_id = b_customer_id
AND trunc(SYSDATE) BETWEEN TRUNC (NVL(use.begin_date, SYSDATE))
AND (NVL(use.end_date, SYSDATE));
SELECT use.party_site_use_id
FROM hz_party_sites site, hz_party_site_uses use
WHERE site.party_site_id = use.party_site_id
AND site.status= 'A'
AND use.site_use_type= 'SHIP_TO'
AND use.primary_per_type = 'Y'
AND use.status = 'A'
AND site.party_id = b_customer_id
AND trunc(SYSDATE) BETWEEN TRUNC (NVL(use.begin_date, SYSDATE))
AND (NVL(use.end_date, SYSDATE));
SELECT install_location_id
FROM csi_item_instances
WHERE instance_id = p_customer_product_id
AND install_location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS');
CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT for incident_id ' || p_record.incident_id ,
'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
SELECT master_organization_id
INTO l_org_id
FROM mtl_parameters
WHERE organization_id = l_sr_rec.inventory_org_id;
'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_EVENT);
/* Calling CS API for actual insert */
CS_ServiceRequest_PUB.Create_ServiceRequest
( p_api_version => 4.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_TRUE
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_user_id => l_created_by
, p_org_id => p_record.org_id
, p_request_id => p_record.incident_id
, p_request_number => p_record.incident_number
, p_service_request_rec => l_sr_rec
, p_notes => l_notes_tab
, p_contacts => l_contacts_tab
, p_resp_id => l_resp_id
, p_default_contract_sla_ind => 'Y'
, p_auto_generate_tasks => l_auto_generate_task
, p_auto_assign => 'Y'
, x_sr_create_out_rec => l_sr_out_rec
);
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
|| ' ROOT ERROR: CS_ServiceRequest_PUB.Create_ServiceRequest ' || sqlerrm
|| ' for incident_id ' || p_record.incident_id,'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT for incident_id ' || p_record.incident_id ,
'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT: ' || sqlerrm
|| ' for incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);
END APPLY_INSERT;
This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
***/
PROCEDURE APPLY_UPDATE
(
p_record IN c_incident%ROWTYPE,
p_error_msg out nocopy VARCHAR2,
x_return_status IN out nocopy VARCHAR2
) IS
/* Cursor to select last_update_date */
CURSOR c_last_update_date ( b_incident_id NUMBER )
IS
SELECT LAST_UPDATE_DATE,
LAST_UPDATED_BY
FROM CS_INCIDENTS_ALL_B
WHERE incident_id = b_incident_id;
SELECT install_location_id
FROM csi_item_instances
WHERE instance_id = p_customer_product_id
AND install_location_type_code = 'HZ_PARTY_SITES';
r_last_update_date c_last_update_date%ROWTYPE;
l_last_updated_by NUMBER;
CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE for incident_id ' || p_record.incident_id ,
'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);
open c_last_update_date(b_incident_id => p_record.incident_id);
fetch c_last_update_date into r_last_update_date;
if c_last_update_date%found then
if r_last_update_date.last_update_date <> p_record.server_last_update_date and r_last_update_date.last_updated_by <> asg_base.get_user_id(p_record.clid$$cs) then
close c_last_update_date;
p_error_msg := 'UPWARD SYNC CONFLICT: CLIENT LOST: CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE: Incident_id = '
|| p_record.incident_id;
csm_util_pkg.log('UPWARD SYNC CONFLICT: CLIENT LOST: CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE: Incident_id = '
|| p_record.incident_id,'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);
close c_last_update_date;
l_last_updated_by := p_record.LAST_UPDATED_BY;
l_sr_rec.last_update_program_code := 'CSM_UPSYNC_WRAPPER';
SELECT master_organization_id
INTO l_org_id
FROM mtl_parameters
WHERE organization_id = l_sr_rec.inventory_org_id;
CSM_UTIL_PKG.LOG('Before calling CS_ServiceRequest_PUB.Update_ServiceRequest for ' || p_record.incident_id ,
'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EVENT);
CS_ServiceRequest_PUB.Update_ServiceRequest
( p_api_version => 3.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_TRUE
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_request_id => p_record.incident_id
, p_object_version_number => l_ovn
, p_last_updated_by => l_last_updated_by
, p_last_update_date => sysdate
, p_service_request_rec => l_sr_rec
, p_notes => l_notes_tab
, p_contacts => l_contacts_tab
, p_resp_id => l_resp_id
, p_default_contract_sla_ind => 'Y'
, x_workflow_process_id => l_workflow_id
, x_interaction_id => l_interaction_id
);
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
|| ' ROOT ERROR: CS_ServiceRequest_PUB.Update_ServiceRequest ' || sqlerrm
|| ' for incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE for incident_id ' || p_record.incident_id ,
'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE: ' || sqlerrm
|| ' for incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EXCEPTION);
END APPLY_UPDATE;
APPLY_INSERT
(
p_record,
p_error_msg,
x_return_status
);
APPLY_UPDATE
(
p_record,
p_error_msg,
x_return_status
);
( 'Delete is not supported for this entity'
|| ' for Incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
a fast sync. This procedure will insert the data that came from mobile into the backend tables using
public APIs.
***/
PROCEDURE APPLY_CLIENT_CHANGES
(
p_user_name IN VARCHAR2,
p_tranid IN NUMBER,
p_debug_level IN NUMBER,
x_return_status IN out nocopy VARCHAR2
) IS
l_process_status VARCHAR2(1);
/*** Yes -> delete record from inqueue ***/
CSM_UTIL_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_incident.seqno$$,
r_incident.incident_id,
g_object_name,
g_pub_name,
l_error_msg,
l_process_status
);
/*** was delete successful? ***/
IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
/*** no -> rollback ***/
CSM_UTIL_PKG.LOG
( 'Deleting from inqueue failed, rolling back to savepoint'
|| ' for incident_id ' || r_incident.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
/*** Yes -> Delete contact recs */
FOR r_contacts IN c_contact( r_incident.incident_id, p_tranid, p_user_name ) LOOP
/* Delete matching contact record(s) */
CSM_UTIL_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_contacts.seqno$$,
r_contacts.SR_CONTACT_POINT_ID,
g_object_name,
'CSF_M_SR_CONTACTS',
l_error_msg,
l_process_status
);
/*** was delete successful? ***/
IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
/*** no -> rollback ***/
CSM_UTIL_PKG.LOG
( 'Deleting from inqueue failed, rolling back to savepoint'
|| ' for incident_id ' || r_incident.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
SELECT b.last_updated_by
FROM CS_INCIDENTS_ALL_B b,
CSM_INCIDENTS_ALL_INQ a
WHERE a.clid$$cs = l_user_name
AND tranid$$ = l_tran_id
AND seqno$$ = l_sequence
AND a.incident_id = b.incident_id ;