The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM csm_task_assignments_inq
WHERE tranid$$ = b_tranid
AND CLID$$CS = B_USER_NAME;
This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_INSERT
(
p_record IN c_task_assignments_inq%ROWTYPE,
p_error_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_task_assignment_id NUMBER;
SELECT jta.object_version_number
FROM jtf_task_assignments jta
WHERE jta.task_assignment_id = b_task_assignment_id;
CSM_UTIL_PKG.LOG('Entering CSM_TASK_ASSIGNMENTS_PKG.APPLY_INSERT for task_assignment_id ' || p_record.task_assignment_id ,
'CSM_TASK_ASSIGNMENTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
p_update_task => NULL,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_task_assignment_id => l_task_assignment_id,
x_ta_object_version_number => l_ta_object_version_number,
x_task_object_version_number => l_task_object_version_number,
x_task_status_id => l_task_status_id
);
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
|| ' ROOT ERROR: JTF_TASK_ASSIGNMENTS_PUB.create_task_assignment'
|| ' for PK ' || p_record.TASK_ASSIGNMENT_ID,
g_object_name || '.APPLY_INSERT',FND_LOG.LEVEL_ERROR );
'CSM_TASK_ASSIGNMENT_PKG.apply_insert', 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_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_NONE
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_task_assignment_id => p_record.task_assignment_id
, p_assignment_status_id => p_record.assignment_status_id
, p_object_version_number => l_ovn
, p_update_task => 'T'
, x_task_object_version_number => l_task_object_version_number
, x_task_status_id => l_task_status_id
);
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
|| ' ROOT ERROR: csf_tasks_pub.update_assignment_status'
|| ' for PK ' || p_record.TASK_ASSIGNMENT_ID,
g_object_name || '.APPLY_UPDATE',FND_LOG.LEVEL_ERROR );
CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:' ||g_object_name || '.APPLY_INSERT',
FND_LOG.LEVEL_EXCEPTION );
END APPLY_INSERT;
This procedure is called by APPLY_INSERT when
an update is to be reverted.
***/
PROCEDURE APPLY_UNDO
(
p_record IN c_task_assignments_inq%ROWTYPE,
p_error_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
)
IS
CURSOR c_get_undo_inq ( c_user_name VARCHAR2, c_tranid NUMBER,c_pk1_value NUMBER)
IS
SELECT SEQNO$$
FROM CSM_CLIENT_UNDO_REQUEST_INQ
WHERE tranid$$ = c_tranid
AND clid$$cs = c_user_name
AND PK1_VALUE = c_pk1_value;
SELECT ACCESS_ID
FROM CSM_TASK_ASSIGNMENTS_ACC
WHERE TASK_ASSIGNMENT_ID = c_task_assignment_id
AND USER_ID = c_user_id;
SELECT ACCESS_ID,INQ.TRANID$$,INQ.SEQNO$$
FROM CSM_TASKS_ACC acc,
CSM_TASKS_INQ inq
WHERE acc.TASK_ID = c_task_id
AND acc.USER_ID = c_user_id
AND inq.TASK_ID = acc.TASK_ID
AND inq.CLID$$CS = c_user_name;
SELECT ACCESS_ID,INQ.INCIDENT_ID,INQ.TRANID$$,INQ.SEQNO$$
FROM CSM_INCIDENTS_ALL_ACC acc,
JTF_TASKS_B b,
CSM_INCIDENTS_ALL_INQ inq
WHERE acc.USER_ID = c_user_id
AND b.TASK_ID = c_task_id
AND b.SOURCE_OBJECT_ID = acc.INCIDENT_ID
AND b.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND acc.INCIDENT_ID = inq.INCIDENT_ID
AND inq.CLID$$CS = c_user_name;
SELECT ACCESS_ID,INQ.DEBRIEF_HEADER_ID,INQ.TRANID$$,INQ.SEQNO$$
FROM CSM_DEBRIEF_HEADERS_ACC acc,
CSM_DEBRIEF_HEADERS_INQ inq
WHERE acc.USER_ID(+) = c_user_id
AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
AND inq.DEBRIEF_HEADER_ID = acc.DEBRIEF_HEADER_ID(+)
AND inq.CLID$$CS = c_user_name;
SELECT ACCESS_ID,INQ.DEBRIEF_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
FROM CSM_DEBRIEF_LINES_ACC acc,
CSF_M_DEBRIEF_EXPENSES_INQ inq
WHERE acc.USER_ID(+) = c_user_id
AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
AND inq.DEBRIEF_LINE_ID = acc.DEBRIEF_LINE_ID(+)
AND inq.CLID$$CS = c_user_name;
SELECT ACCESS_ID,INQ.DEBRIEF_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
FROM CSM_DEBRIEF_LINES_ACC acc,
CSF_M_DEBRIEF_LABOR_INQ inq
WHERE acc.USER_ID(+) = c_user_id
AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
AND inq.DEBRIEF_LINE_ID = acc.DEBRIEF_LINE_ID(+)
AND inq.CLID$$CS = c_user_name;
SELECT ACCESS_ID,INQ.DEBRIEF_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
FROM CSM_DEBRIEF_LINES_ACC acc,
CSF_M_DEBRIEF_PARTS_INQ inq
WHERE acc.USER_ID(+) = c_user_id
AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
AND inq.DEBRIEF_LINE_ID = acc.DEBRIEF_LINE_ID(+)
AND inq.CLID$$CS = c_user_name;
SELECT ACCESS_ID,INQ.REQUIREMENT_HEADER_ID,INQ.TRANID$$,INQ.SEQNO$$
FROM CSM_REQ_HEADERS_ACC acc,
CSM_REQ_HEADERS_INQ inq
WHERE acc.USER_ID(+) = c_user_id
AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
AND inq.REQUIREMENT_HEADER_ID = acc.REQUIREMENT_HEADER_ID(+)
AND inq.CLID$$CS = c_user_name;
SELECT ACCESS_ID,INQ.REQUIREMENT_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
FROM CSM_REQ_LINES_ACC acc,
CSM_REQ_LINES_INQ inq
WHERE acc.USER_ID (+) = c_user_id
AND inq.REQUIREMENT_HEADER_ID = c_req_header_id
AND inq.REQUIREMENT_LINE_ID = acc.REQUIREMENT_LINE_ID (+)
AND inq.CLID$$CS = c_user_name;
SELECT ACCESS_ID,INQ.JTF_NOTE_ID,INQ.TRANID$$,INQ.SEQNO$$, INQ.DMLTYPE$$
FROM CSM_NOTES_ACC acc,
CSF_M_NOTES_INQ inq
WHERE acc.USER_ID (+) = c_user_id
AND inq.JTF_NOTE_ID = acc.JTF_NOTE_ID (+)
AND inq.CLID$$CS = c_user_name
AND (
( inq.SOURCE_OBJECT_CODE = 'TASK' AND inq.SOURCE_OBJECT_ID = c_task_id )
OR ( inq.SOURCE_OBJECT_CODE = 'SR' AND inq.SOURCE_OBJECT_ID = c_incident_id)
OR ( inq.SOURCE_OBJECT_CODE = 'SD' AND inq.SOURCE_OBJECT_ID = c_debrief_header_id)
);
SELECT USER_ID
FROM ASG_USER
WHERE USER_NAME = c_user_name;
an updated record is to be processed.
***/
PROCEDURE APPLY_UPDATE
(
p_record IN c_task_assignments_inq%ROWTYPE,
p_error_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
CURSOR c_task_assignment( b_task_assignment_id NUMBER)
IS
SELECT jta.task_id
, jta.object_version_number
, jta.last_update_date
, jta.last_updated_by
FROM jtf_task_assignments jta
WHERE jta.task_assignment_id = b_task_assignment_id;
IF(r_task_assignment.last_update_date <> p_record.server_last_update_date AND r_task_assignment.last_updated_by <> asg_base.get_user_id(p_record.clid$$cs)) THEN
p_error_msg := 'UPWARD SYNC CONFLICT: CLIENT LOST For JTF_TASK_ASSIGNMENTS: CSM_TASK_ASSIGNMENTS_PKG.APPLY_UPDATE: P_KEY = '
|| p_record.task_assignment_id;
g_object_name || '.APPLY_UPDATE',
FND_LOG.LEVEL_ERROR);
'CSM_TASK_ASSIGNMENT_PKG.apply_update', 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_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_NONE
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_task_assignment_id => p_record.task_assignment_id
, p_assignment_status_id => p_record.assignment_status_id
, p_object_version_number => l_ovn
, p_update_task => 'T'
, x_task_object_version_number => l_task_object_version_number
, x_task_status_id => l_task_status_id
);
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
|| ' ROOT ERROR: csf_tasks_pub.update_assignment_status'
|| ' for PK ' || p_record.TASK_ASSIGNMENT_ID,
g_object_name || '.APPLY_UPDATE',FND_LOG.LEVEL_ERROR );
csf_task_assignments_pub.Update_Task_Assignment
( p_api_version => 1.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_FALSE
, p_task_assignment_id => p_record.task_assignment_id
, p_object_version_number => l_ovn
, p_task_id => p_record.task_id
, p_resource_type_code => FND_API.G_MISS_CHAR
, p_resource_id => FND_API.G_MISS_NUM --p_record.resource_id
, p_resource_territory_id => FND_API.G_MISS_NUM
, p_assignment_status_id => FND_API.G_MISS_NUM
, p_actual_start_date => p_record.actual_start_date
, p_actual_end_date => p_record.actual_end_date
, p_sched_travel_distance => FND_API.G_MISS_NUM
, p_sched_travel_duration => FND_API.G_MISS_NUM
, p_sched_travel_duration_uom => FND_API.G_MISS_CHAR
, p_shift_construct_id => FND_API.G_MISS_NUM
, p_object_capacity_id => FND_API.G_MISS_NUM
, p_attribute1 => p_record.attribute1
, p_attribute2 => p_record.attribute2
, p_attribute3 => p_record.attribute3
, p_attribute4 => p_record.attribute4
, p_attribute5 => p_record.attribute5
, p_attribute6 => p_record.attribute6
, p_attribute7 => p_record.attribute7
, p_attribute8 => p_record.attribute8
, p_attribute9 => p_record.attribute9
, p_attribute10 => p_record.attribute10
, p_attribute11 => p_record.attribute11
, p_attribute12 => p_record.attribute12
, p_attribute13 => p_record.attribute13
, p_attribute14 => p_record.attribute14
, p_attribute15 => p_record.attribute15
--Bug 5182470
, p_attribute_category => p_record.attribute_category
, x_return_status => x_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
);
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
|| ' ROOT ERROR: csf_tasks_pub.Update_Task_Assignment'
|| ' for PK ' || p_record.TASK_ASSIGNMENT_ID,
g_object_name || '.APPLY_UPDATE',
FND_LOG.LEVEL_ERROR );
CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
|| ' for PK ' || p_record.task_assignment_id,
g_object_name || '.APPLY_UPDATE',
FND_LOG.LEVEL_EXCEPTION );
END APPLY_UPDATE;
SELECT closed_flag
FROM jtf_task_statuses_b
WHERE task_status_id=b_task_status_id;
APPLY_INSERT
(
p_record,
p_error_msg,
x_return_status
);
APPLY_UPDATE
(
p_record,
p_error_msg,
x_return_status
);
( 'Delete and Insert is not supported for this entity'
|| ' for PK ' || p_record.task_assignment_id ,
g_object_name || '.APPLY_RECORD',
FND_LOG.LEVEL_ERROR);
SELECT dh.debrief_header_id, tst.rejected_flag, tst.on_hold_flag,
tst.cancelled_flag, tst.closed_flag, tst.completed_flag
FROM csf_debrief_headers dh, jtf_task_assignments tas,
jtf_task_statuses_b tst
WHERE dh.task_assignment_id = tas.task_assignment_id
AND tas.assignment_status_id = tst.task_status_id
AND tas.task_assignment_id = p_task_assignment_id;
SELECT PK1_VALUE
FROM CSM_CLIENT_UNDO_REQUEST_INQ
WHERE PUB_ITEM = 'CSM_TASK_ASSIGNMENTS'
AND TRANID$$ = c_tranid
AND PK1_VALUE = c_task_assignment_id;
select user_id,responsibility_id,app_id
into l_user_id ,l_resp_id ,l_app_id
from asg_user where user_name=p_user_name;
csf_debrief_update_pkg.form_Call (1.0, l_header_id);
/*** If Yes -> delete record from inqueue ***/
CSM_UTIL_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_task_assignments.seqno$$,
r_task_assignments.task_assignment_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
--Task Assignment Audit information is not important and hence
-- Task Assignment Audit information failure should not be
-- considered as failure and stops TA Upload
--Call Task Assignment Audit Upload
CSM_TA_AUDIT_PKG.APPLY_CLIENT_CHANGES
(
p_user_name =>p_user_name,
p_tranid =>p_tranid,
p_assignment_id =>r_task_assignments.task_assignment_id,
p_debug_level =>g_debug_level,
x_return_status =>l_process_status
);
( 'Inserting Task Assignment Audit Failed, Task Assignment Audit Failed for '
|| ' for PK ' || r_task_assignments.task_assignment_id ,
g_object_name || '.APPLY_CLIENT_CHANGES',
FND_LOG.LEVEL_ERROR); -- put PK column here
/*** Record was not processed successfully or delete failed
-> defer and reject record ***/
CSM_UTIL_PKG.LOG
( 'Record not processed successfully, deferring and rejecting record'
|| ' for PK ' || r_task_assignments.task_assignment_id ,
g_object_name || '.APPLY_CLIENT_CHANGES',
FND_LOG.LEVEL_ERROR); -- put PK column here
( 'Inserting Task Assignment Audit , Task Assignment Audit for '
|| ' for PK ' || r_task_assignments.task_assignment_id ,
g_object_name || '.APPLY_CLIENT_CHANGES',
FND_LOG.LEVEL_ERROR); -- put PK column here
SELECT b.last_updated_by
FROM JTF_TASK_ASSIGNMENTS b,
CSM_TASK_ASSIGNMENTS_INQ a
WHERE a.clid$$cs = l_user_name
AND tranid$$ = l_tran_id
AND seqno$$ = l_sequence
AND a.task_assignment_id = b.task_assignment_id ;