[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_MAPPING_RECORD( p_incident_id NUMBER
, p_history_id NUMBER
, p_resource_id NUMBER )
IS
BEGIN
IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
jtm_message_log_pkg.Log_Msg
( null
, g_table_name
, 'Entering INSERT_MAPPING_RECORD for incident '||p_incident_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_service_history_pkg');
JTM_HOOK_UTIL_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
,P_ACC_TABLE_NAME => g_acc_table_name
,P_RESOURCE_ID => p_resource_id
,P_PK1_NAME => g_pk1_name
,P_PK1_NUM_VALUE => p_incident_id
,P_PK2_NAME => g_pk2_name
,P_PK2_NUM_VALUE => p_history_id
);
, 'Leaving INSERT_MAPPING_RECORD for incident '||p_incident_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_service_history_pkg');
, 'Exception in CSL_SERVICE_HISTORY_PKG.INSERT_MAPPING_RECORD for incident '||p_incident_id||
' and resource '||p_resource_id || ':' || fnd_global.local_chr(10) || sqlerrm
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_service_history_pkg');
END INSERT_MAPPING_RECORD;
PROCEDURE DELETE_MAPPING_RECORD( p_incident_id NUMBER
, p_history_id NUMBER
, p_resource_id NUMBER )
IS
BEGIN
IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
jtm_message_log_pkg.Log_Msg
( null
, g_table_name
, 'Entering DELETE_MAPPING_RECORD for incident '||p_incident_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_service_history_pkg');
JTM_HOOK_UTIL_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
,P_ACC_TABLE_NAME => g_acc_table_name
,P_RESOURCE_ID => p_resource_id
,P_PK1_NAME => g_pk1_name
,P_PK1_NUM_VALUE => p_incident_id
,P_PK2_NAME => g_pk2_name
,P_PK2_NUM_VALUE => p_history_id
);
, 'Leaving DELETE_MAPPING_RECORD for incident '||p_incident_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_service_history_pkg');
, 'Exception in CSL_SERVICE_HISTORY_PKG.DELETE_MAPPING_RECORD for incident '||p_incident_id||
' and resource '||p_resource_id||':'|| fnd_global.local_chr(10) || sqlerrm
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_service_history_pkg');
END DELETE_MAPPING_RECORD;
SELECT fur.USER_ID
, fur.RESPONSIBILITY_ID
, fur.RESPONSIBILITY_APPLICATION_ID
FROM FND_USER_RESP_GROUPS fur
, ASG_USER au
WHERE au.RESOURCE_ID = b_resource_id
AND au.USER_ID = fur.USER_ID
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(fur.start_date,sysdate))
AND TRUNC(NVL(fur.end_date,sysdate));
PROCEDURE DELETE_HISTORY_SR_RECORD( p_incident_id NUMBER
, p_history_id NUMBER
, p_resource_id NUMBER )
IS
CURSOR c_closed_tasks( b_incident_id NUMBER )IS
SELECT tk.TASK_ID
FROM JTF_TASKS_B tk
, JTF_TASK_STATUSES_B ts
WHERE tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND tk.SOURCE_OBJECT_ID = b_incident_id
AND tk.TASK_STATUS_ID = ts.TASK_STATUS_ID
AND (ts.CLOSED_FLAG = 'Y'
OR ts.COMPLETED_FLAG = 'Y' )
AND NVL(ts.CANCELLED_FLAG,'N') <> 'Y'
AND NVL(ts.REJECTED_FLAG, 'N') <> 'Y';
SELECT TASK_ASSIGNMENT_ID
, RESOURCE_ID
FROM JTF_TASK_ASSIGNMENTS ta
, JTF_TASK_STATUSES_B ts
WHERE ta.TASK_ID = b_task_id
AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
AND ts.TASK_STATUS_ID = ta.ASSIGNMENT_STATUS_ID
AND (ts.CLOSED_FLAG = 'Y'
OR ts.COMPLETED_FLAG = 'Y' )
AND NVL(ts.CANCELLED_FLAG,'N') <> 'Y'
AND NVL(ts.REJECTED_FLAG, 'N') <> 'Y';
, 'Entering DELETE_HISTORY_SR_RECORD for incident '||p_incident_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_service_history_pkg');
CSL_CS_INCIDENTS_ALL_ACC_PKG.Post_Delete_Child( p_incident_id => p_history_id
, p_resource_id => p_resource_id
, p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
CSL_JTF_TASKS_ACC_PKG.Post_Delete_Child( p_task_id => r_closed_task.task_id
, p_resource_id => p_resource_id
, p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
CSL_JTF_TASK_ASS_ACC_PKG.Post_Delete_Child(
p_task_assignment_id => r_closed_assignement.task_assignment_id,
p_resource_id => p_resource_id,
p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Delete_Resource_Extns(
p_resource_extn_id => r_closed_assignement.resource_id,
p_resource_id => p_resource_id );
DELETE_MAPPING_RECORD( p_incident_id, p_history_id, p_resource_id );
, 'Leaving DELETE_HISTORY_SR_RECORD for incident '||p_incident_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_service_history_pkg');
, 'Exception in CSL_SERVICE_HISTORY_PKG.DELETE_HISTORY_SR_RECORD for incident '||p_incident_id||
' and resource '||p_resource_id||':'|| fnd_global.local_chr(10) || sqlerrm
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_service_history_pkg');
END DELETE_HISTORY_SR_RECORD;
SELECT tk.TASK_ID
FROM JTF_TASKS_B tk
, JTF_TASK_STATUSES_B ts
WHERE tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND tk.SOURCE_OBJECT_ID = b_incident_id
AND tk.TASK_STATUS_ID = ts.TASK_STATUS_ID
AND (ts.CLOSED_FLAG = 'Y'
OR ts.COMPLETED_FLAG = 'Y' )
AND NVL(ts.CANCELLED_FLAG,'N') <> 'Y'
AND NVL(ts.REJECTED_FLAG,'N') <> 'Y';
SELECT TASK_ASSIGNMENT_ID
, RESOURCE_ID
FROM JTF_TASK_ASSIGNMENTS ta
, JTF_TASK_STATUSES_B ts
WHERE ta.TASK_ID = b_task_id
AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
AND ts.TASK_STATUS_ID = ta.ASSIGNMENT_STATUS_ID
AND (ts.CLOSED_FLAG = 'Y'
OR ts.COMPLETED_FLAG = 'Y' )
AND NVL(ts.CANCELLED_FLAG,'N') <> 'Y'
AND NVL(ts.REJECTED_FLAG,'N') <> 'Y';
, 'Inserting history record '||p_history_id||' for incident '||
p_incident_id||' and resource '||p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
, 'csl_service_history_pkg');
INSERT_MAPPING_RECORD( p_incident_id, p_history_id, p_resource_id );
l_dummy := CSL_CS_INCIDENTS_ALL_ACC_PKG.Pre_Insert_Child(
p_incident_id => p_history_id
, p_resource_id => p_resource_id
, p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
l_dummy := CSL_JTF_TASKS_ACC_PKG.Pre_Insert_Child( p_task_id => r_closed_task.task_id
, p_resource_id => p_resource_id
, p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
l_dummy := CSL_JTF_TASK_ASS_ACC_PKG.Pre_Insert_Child(
p_task_assignment_id => r_closed_assignement.task_assignment_id,
p_resource_id => p_resource_id,
p_flow_type => CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY);
CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Insert_Resource_Extns(
p_resource_extn_id => r_closed_assignement.resource_id,
p_resource_id => p_resource_id );
PROCEDURE DELETE_HISTORY( p_incident_id IN NUMBER
, p_resource_id IN NUMBER )
IS
CURSOR c_history ( b_incident_id NUMBER, b_resource_id NUMBER ) IS
SELECT HISTORY_INCIDENT_ID
FROM CSL_SERVICE_HISTORY
WHERE INCIDENT_ID = b_incident_id
AND RESOURCE_ID = b_resource_id;
, 'Entering DELETE_HISTORY for incident '||p_incident_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_service_history_pkg');
, 'Calling delete history record for incident id '||p_incident_id||
' and history id '||r_history.HISTORY_INCIDENT_ID||' and resource id '||p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
, 'csl_service_history_pkg');
DELETE_HISTORY_SR_RECORD( p_incident_id, r_history.HISTORY_INCIDENT_ID, p_resource_id );
, 'Leaving DELETE_HISTORY for incident '||p_incident_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_service_history_pkg');
, 'Exception in CSL_SERVICE_HISTORY_PKG.DELETE_HISTORY for incident '||p_incident_id||
' and resource '||p_resource_id||':'|| fnd_global.local_chr(10) || sqlerrm
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_service_history_pkg');
END DELETE_HISTORY;
SELECT CUSTOMER_PRODUCT_ID
, INCIDENT_LOCATION_ID
, CUSTOMER_ID
FROM CS_INCIDENTS_ALL_B
WHERE INCIDENT_ID = b_incident_id;
SELECT MAX(tk.SCHEDULED_END_DATE ) AS "TASK_TIME"
FROM JTF_TASKS_B tk
, JTF_TASK_ASSIGNMENTS ta
WHERE tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND tk.SOURCE_OBJECT_ID = b_incident_id
AND tk.TASK_ID = ta.TASK_ID
AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
AND ta.RESOURCE_ID = b_resource_id;
SELECT DISTINCT inc.INCIDENT_ID
, inc.CLOSE_DATE
FROM CS_INCIDENTS_ALL_B inc
, JTF_TASKS_B tk
, JTF_TASK_ASSIGNMENTS ta
, CS_INCIDENT_STATUSES_B ists
, JTF_TASK_TYPES_B tt
, JTF_TASK_STATUSES_B tkst
, JTF_TASK_STATUSES_B tast
WHERE inc.CLOSE_DATE <= b_max_date
AND inc.INCIDENT_ID = tk.SOURCE_OBJECT_ID
AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
AND ists.CLOSE_FLAG = 'Y'
AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND tk.TASK_ID = ta.TASK_ID
AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
AND (tkst.CLOSED_FLAG = 'Y'
OR tkst.COMPLETED_FLAG = 'Y')
AND nvl(tkst.CANCELLED_FLAG,'N') <> 'Y'
AND nvl(tkst.REJECTED_FLAG,'N') <> 'Y'
AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
AND tt.RULE = 'DISPATCH'
AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
AND (tast.CLOSED_FLAG = 'Y'
OR tast.COMPLETED_FLAG = 'Y')
AND nvl(tast.CANCELLED_FLAG,'N') <> 'Y'
AND nvl(tast.REJECTED_FLAG,'N') <> 'Y'
AND inc.CUSTOMER_PRODUCT_ID = b_customer_product_id
ORDER BY inc.CLOSE_DATE DESC;
SELECT DISTINCT inc.INCIDENT_ID
, inc.CLOSE_DATE
FROM CS_INCIDENTS_ALL_B inc
, JTF_TASKS_B tk
, JTF_TASK_ASSIGNMENTS ta
, CS_INCIDENT_STATUSES_B ists
, JTF_TASK_TYPES_B tt
, JTF_TASK_STATUSES_B tkst
, JTF_TASK_STATUSES_B tast
WHERE inc.CLOSE_DATE <= b_max_date
AND inc.INCIDENT_ID = tk.SOURCE_OBJECT_ID
AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
AND ists.CLOSE_FLAG = 'Y'
AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND tk.TASK_ID = ta.TASK_ID
AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
AND (tkst.CLOSED_FLAG = 'Y'
OR tkst.COMPLETED_FLAG = 'Y')
AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
AND tt.RULE = 'DISPATCH'
AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
AND (tast.CLOSED_FLAG = 'Y'
OR tast.COMPLETED_FLAG = 'Y')
AND inc.CUSTOMER_ID = b_customer_id
AND inc.INCIDENT_LOCATION_ID = b_incident_location_id
ORDER BY inc.CLOSE_DATE DESC;
SELECT HISTORY_INCIDENT_ID
FROM CSL_SERVICE_HISTORY
WHERE INCIDENT_ID = b_incident_id
AND RESOURCE_ID = b_resource_id;
/* IF IT MATCHES RECORD DOES NOT NEED TO BE INSERTED AND THE PLSQL RECORD SHOULD BE DELETED*/
/* AT THE END PUSH A DELETE FOR THE REMAINING RECORDS AS THEY ARE NO LONGER NEEDED*/
OPEN c_history( p_incident_id, p_resource_id );
/*Record does exist do not insert but remove reference from list*/
l_history_table.DELETE(i);
/*Record does not yet exists so insert*/
CREATE_HISTORY_SR_RECORD( p_incident_id => p_incident_id
, p_history_id => r_get_cp_history.incident_id
, p_resource_id => p_resource_id
, p_closed_date => r_get_cp_history.close_date );
/*Record does not yet exists so insert*/
CREATE_HISTORY_SR_RECORD( p_incident_id => p_incident_id
, p_history_id => r_get_cp_history.incident_id
, p_resource_id => p_resource_id
, p_closed_date => r_get_cp_history.close_date );
/*Push delete to history records that are no longer history record*/
IF l_history_table.COUNT > 0 THEN
FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
IF l_history_table.EXISTS(i) THEN
DELETE_HISTORY_SR_RECORD( p_incident_id, l_history_table(i), p_resource_id );
/*Record does exist do not insert but remove reference from list*/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( null
, g_table_name
, 'Already replicated, deleting from PL/SQL table'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
, 'csl_service_history_pkg');
l_history_table.DELETE(i);
/*Record does not yet exists so insert*/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( null
, g_table_name
, 'Record not replicated yet; push it to client(s)'
/*Push delete to history records that are no longer history record*/
IF l_history_table.COUNT > 0 THEN
FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
IF l_history_table.EXISTS(i) THEN
DELETE_HISTORY_SR_RECORD( p_incident_id, l_history_table(i), p_resource_id );
SELECT acc.INCIDENT_ID
, acc.RESOURCE_ID
FROM CSL_CS_INCIDENTS_ALL_ACC acc
, CS_INCIDENTS_ALL_B inc
, JTF_TASKS_B tsk
, JTF_TASK_ASSIGNMENTS ta
WHERE tsk.SOURCE_OBJECT_ID = inc.INCIDENT_ID
AND tsk.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND tsk.TASK_ID = ta.TASK_ID
AND ta.RESOURCE_ID = acc.RESOURCE_ID
AND inc.INCIDENT_ID = acc.INCIDENT_ID
AND tsk.SCHEDULED_END_DATE >= TRUNC(SYSDATE)
AND NVL(inc.CLOSE_DATE, SYSDATE ) >= SYSDATE;
UPDATE JTM_CON_REQUEST_DATA
SET LAST_RUN_DATE = SYSDATE
WHERE PRODUCT_CODE = 'CSL'
AND PACKAGE_NAME = 'CSL_SERVICE_HISTORY_PKG'
AND PROCEDURE_NAME = 'CONCURRENT_HISTORY';