[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_replicate_pre_update BOOLEAN; -- true when incident was replicated before the update
TYPE g_pre_update_rec IS RECORD(
INCIDENT_ID NUMBER,
CUSTOMER_ID NUMBER,
INCIDENT_LOCATION_ID NUMBER,
CUSTOMER_PRODUCT_ID NUMBER,
INVENTORY_ITEM_ID NUMBER,
INV_ORGANIZATION_ID NUMBER,
CONTRACT_SERVICE_ID NUMBER
);
g_cached_rec CSL_CS_INCIDENTS_ALL_ACC_PKG.g_pre_update_rec; --record to cache changes
SELECT incident_id -- Sql Performance Fix
FROM CS_INCIDENTS_ALL_B
WHERE incident_id = b_incident_id;
PROCEDURE Insert_ACC_Record
( p_incident_id IN NUMBER
,p_resource_id IN NUMBER
,p_flow_type IN NUMBER
)
IS
CURSOR c_incident (b_incident_id NUMBER) IS
-- 11.5.10 Changes - 3430663. Get based on incident_location_id not
-- on install_site_id
SELECT customer_id, incident_location_id, customer_product_id,
inventory_item_id, inv_organization_id
FROM CS_INCIDENTS_ALL_B
WHERE incident_id = b_incident_id;
, 'Entering Insert_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, 'Inserting ACC record for resource_id = ' || p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** Insert incident ACC record ***/
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
);
Call all incident related insert hook packages
these records are no show stoppers for incidents
hence it is not nessacary to put the in the pre_insert_child
function
***************************************************************/
OPEN c_incident( p_incident_id );
, 'Inserting non-critical dependant records'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
CSL_HZ_PARTIES_ACC_PKG.INSERT_PARTY( r_incident.customer_id,
p_resource_id, p_flow_type );
CSL_HZ_PARTY_SITES_ACC_PKG.INSERT_PARTY_SITE(
r_incident.incident_location_id, p_resource_id );
CSL_PARTY_CONTACTS_ACC_PKG.INSERT_CS_HZ_SR_CONTACTS( p_incident_id,
p_resource_id, p_flow_type );
l_return := CSL_JTF_NOTES_ACC_PKG.PRE_INSERT_CHILDREN
( P_SOURCE_OBJ_ID => p_incident_id
, P_SOURCE_OBJ_CODE => 'SR'
, P_RESOURCE_ID => p_resource_id );
l_return := CSL_CSI_ITEM_INSTANCES_ACC_PKG.PRE_INSERT_CHILD(
p_instance_id => r_incident.CUSTOMER_PRODUCT_ID
, p_resource_id => p_resource_id
, p_flow_type => p_flow_type
, p_party_site_id => r_incident.incident_location_id);
CSL_MTL_SYSTEM_ITEMS_ACC_PKG.PRE_INSERT_CHILD(
p_inventory_item_id => r_incident.INVENTORY_ITEM_ID
, p_organization_id => r_incident.INV_ORGANIZATION_ID
, p_resource_id => p_resource_id );
/*Insert contract record, use dynamic SQL because Contracts might not be
implemented/used */
l_cursorid := DBMS_SQL.open_cursor;
l_stmt := 'Begin CSL_CONTRACT_HANDLING_PKG.POST_INSERT_SR_CONTRACT_ACC( :1,:2,:3 );'||
, 'Leaving Insert_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Insert_ACC_Record;
PROCEDURE Update_ACC_Record
( p_incident_id IN NUMBER
,p_resource_id IN NUMBER
,p_acc_id IN NUMBER
)
IS
BEGIN
IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
jtm_message_log_pkg.Log_Msg
( p_incident_id
, g_table_name
, 'Entering Update_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
JTM_HOOK_UTIL_PKG.Update_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
,P_ACC_TABLE_NAME => g_acc_table_name
,P_RESOURCE_ID => p_resource_id
,P_ACCESS_ID => p_acc_id
);
, 'Leaving Update_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Update_ACC_Record;
/*** Private procedure that deletes incident for resource from acc table ***/
PROCEDURE Delete_ACC_Record
( p_incident_id IN NUMBER
,p_resource_id IN NUMBER
,p_flow_type IN NUMBER
)
IS
-- 11510 3430663 Changes. Get incident_location_id not install_site_id
CURSOR c_incident (b_incident_id NUMBER) IS
SELECT customer_id, incident_location_id, customer_product_id,
inventory_item_id, inv_organization_id
FROM CS_INCIDENTS_ALL_B
WHERE incident_id = b_incident_id;
, 'Entering Delete_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Delete incident ACC record ***/
JTM_HOOK_UTIL_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
,P_ACC_TABLE_NAME => g_acc_table_name
,P_PK1_NAME => g_pk1_name
,P_PK1_NUM_VALUE => p_incident_id
,P_RESOURCE_ID => p_resource_id
);
/*Delete also the dependant records*/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( p_incident_id
, g_table_name
, 'Deleting child records'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
CSL_HZ_PARTIES_ACC_PKG.DELETE_PARTY( r_incident.customer_id,
p_resource_id, p_flow_type );
CSL_HZ_PARTY_SITES_ACC_PKG.DELETE_PARTY_SITE(
r_incident.incident_location_id, p_resource_id );
CSL_PARTY_CONTACTS_ACC_PKG.DELETE_CS_HZ_SR_CONTACTS( p_incident_id,
p_resource_id, p_flow_type );
CSL_JTF_NOTES_ACC_PKG.POST_DELETE_CHILDREN(
P_SOURCE_OBJ_ID => p_incident_id
, P_SOURCE_OBJ_CODE => 'SR'
, P_RESOURCE_ID => p_resource_id );
CSL_CSI_ITEM_INSTANCES_ACC_PKG.POST_DELETE_CHILD(
p_instance_id => r_incident.CUSTOMER_PRODUCT_ID
, p_resource_id => p_resource_id
, p_flow_type => p_flow_type
, p_party_site_id => r_incident.incident_location_id);
CSL_MTL_SYSTEM_ITEMS_ACC_PKG.POST_DELETE_CHILD(
p_inventory_item_id => r_incident.INVENTORY_ITEM_ID
, p_organization_id => r_incident.INV_ORGANIZATION_ID
, p_resource_id => p_resource_id );
/*CSL_LOBS_ACC_PKG.DELETE_ATTACHMENTS ( p_entity_name => 'CS_INCIDENTS',
p_primary_key => p_incident_id,
p_resource_id => p_resource_id);*/
/* Delete contract record, use dynamic SQL because Contracts might not be
implemented/used */
l_cursorid := DBMS_SQL.open_cursor;
l_stmt := 'Begin CSL_CONTRACT_HANDLING_PKG.PRE_DELETE_SR_CONTRACT_ACC( :1,:2,:3 );'||
, 'Leaving Delete_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Delete_ACC_Record;
Public function that gets called when a incident needs to be inserted into ACC table.
Returns TRUE when record already was or has been inserted into ACC table.
***/
FUNCTION Pre_Insert_Child
( p_incident_id IN NUMBER
,p_resource_id IN NUMBER
,p_flow_type IN NUMBER --DEFAULT G_FLOW_NORMAL
)
RETURN BOOLEAN
IS
l_acc_id NUMBER;
, 'Entering Pre_Insert_Child procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/* Yes, so insert */
Insert_ACC_Record
( p_incident_id
,p_resource_id
,p_flow_type
);
, 'Leaving Pre_Insert_Child procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Pre_Insert_Child;
Public procedure that gets called when a task needs to be deleted from ACC table.
***/
PROCEDURE Post_Delete_Child
( p_incident_id IN NUMBER
,p_resource_id IN NUMBER
,p_flow_type IN NUMBER --DEFAULT G_FLOW_NORMAL
)
IS
BEGIN
/*** get debug level ***/
g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
, 'Entering Post_Delete_Child'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*Delete also the history for this SR*/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( p_incident_id
, g_table_name
, 'Delete all history records on incident id '||p_incident_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
CSL_SERVICE_HISTORY_PKG.DELETE_HISTORY( p_incident_id => p_incident_id
, p_resource_id => p_resource_id );
Delete_ACC_Record
( p_incident_id
, p_resource_id
, p_flow_type );
, 'Leaving Post_Delete_Child'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Post_Delete_Child;
/* Called before incident Insert */
PROCEDURE PRE_INSERT_INCIDENT
( x_return_status OUT NOCOPY varchar2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
END PRE_INSERT_INCIDENT;
/* Called after incident Insert */
PROCEDURE POST_INSERT_INCIDENT
( x_return_status OUT NOCOPY varchar2
)
IS
CURSOR c_incident( b_incident_id NUMBER ) IS
SELECT au.RESOURCE_ID
FROM ASG_USER au
, CS_INCIDENTS_ALL_B inc
WHERE au.USER_ID = inc.CREATED_BY
AND inc.INCIDENT_ID = b_incident_id;
, 'Entering POST_INSERT_INCIDENT hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, 'SR is logged by mobile resource '||r_incident.RESOURCE_ID||' hence inserting record in acc table'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
l_dummy := Pre_Insert_Child( l_incident_id, r_incident.RESOURCE_ID, G_FLOW_MOBILE_SR );
, 'Leaving POST_INSERT_INCIDENT hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, 'Caught exception in POST_INSERT_INCIDENT hook:' || fnd_global.local_chr(10) || sqlerrm
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
fnd_msg_pub.Add_Exc_Msg('CSL_CS_INCIDENTS_ALL_ACC_PKG','POST_INSERT_INCIDENT',sqlerrm);
END POST_INSERT_INCIDENT;
/* Called before incident Update */
PROCEDURE PRE_UPDATE_INCIDENT
( x_return_status OUT NOCOPY varchar2
)
IS
l_incident_id NUMBER;
SELECT INCIDENT_ID
, CUSTOMER_ID
, INCIDENT_LOCATION_ID
, CUSTOMER_PRODUCT_ID
, INVENTORY_ITEM_ID
, INV_ORGANIZATION_ID
, CONTRACT_SERVICE_ID
FROM CS_INCIDENTS_ALL_B
WHERE incident_id = b_incident_id;
, 'Entering PRE_UPDATE hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Check if task before update matched criteria ***/
g_replicate_pre_update := Replicate_Record( l_incident_id );
, 'Leaving PRE_UPDATE hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, 'Caught exception in PRE_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
fnd_msg_pub.Add_Exc_Msg('CSL_CS_INCIDENTS_ALL_ACC_PKG','PRE_UPDATE_TASK',sqlerrm);
END PRE_UPDATE_INCIDENT;
/* Called after task Update */
PROCEDURE POST_UPDATE_INCIDENT
( x_return_status OUT NOCOPY varchar2
)
IS
CURSOR c_task( b_incident_id NUMBER )
IS
SELECT task_id
FROM jtf_tasks_b
WHERE source_object_id = b_incident_id
AND source_object_type_code = 'SR';
SELECT INCIDENT_ID
, CUSTOMER_ID
, INCIDENT_LOCATION_ID
, CUSTOMER_PRODUCT_ID
, INVENTORY_ITEM_ID
, INV_ORGANIZATION_ID
, CONTRACT_SERVICE_ID
FROM CS_INCIDENTS_ALL_B
WHERE incident_id = b_incident_id;
, 'Entering POST_UPDATE hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Check if task after update matches criteria ***/
l_replicate := Replicate_Record( l_incident_id );
/*** replicate record after update? ***/
IF l_replicate THEN
/*** yes -> was record already replicated? ***/
IF g_replicate_pre_update THEN
/*** yes -> re-send updated task record to all resources ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( l_incident_id
, g_table_name
, 'Incident was replicateable before and after update. Re-sending incident record to mobile users.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Update_ACC_Record
( l_incident_id
,l_tab_resource_id(i)
,l_tab_access_id(i)
);
CSL_PARTY_CONTACTS_ACC_PKG.INSERT_CS_HZ_SR_CONTACTS( l_incident_id, l_tab_resource_id(i) );
/*Get the post update values*/
OPEN c_incident( l_incident_id );
/* First delete old customer product */
IF g_cached_rec.customer_product_id IS NOT NULL THEN
-- ER 3168446 - View ib at a location. Pass the Install Site Id
-- 11510 Changes 3430663. Pass incident_location_id now
CSL_CSI_ITEM_INSTANCES_ACC_PKG.POST_DELETE_CHILD(
g_cached_rec.customer_product_id
, l_tab_resource_id(i)
, p_party_site_id => r_incident.incident_location_id);
l_dummy := CSL_CSI_ITEM_INSTANCES_ACC_PKG.Pre_Insert_Child(
r_incident.customer_product_id
, l_tab_resource_id(i)
, p_party_site_id => r_incident.incident_location_id);
/* First delete old inventory item */
IF g_cached_rec.inventory_item_id IS NOT NULL THEN
CSL_MTL_SYSTEM_ITEMS_ACC_PKG.POST_DELETE_Child( g_cached_rec.inventory_item_id
, g_cached_rec.INV_ORGANIZATION_ID
, l_tab_resource_id(i));
CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Pre_Insert_Child( r_incident.inventory_item_id
, r_incident.INV_ORGANIZATION_ID
, l_tab_resource_id(i));
/*Delete contract record, use dynamic SQL because Contracts might not be implemented/used*/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( l_incident_id
, g_table_name
, 'Contract line of the incident has changed.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
l_stmt := 'Begin CSL_CONTRACT_HANDLING_PKG.POST_UPDATE_SR_CONTRACT_ACC( :1,:2,:3,:4,:5 );'||
record was not replicated before update so we don't need it
***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( l_incident_id
, g_table_name
, 'Incident was not replicated before update.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
IF g_replicate_pre_update THEN
/*** yes -> delete record related data for all resources ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( l_incident_id
, g_table_name
, 'Incident was replicated before update, but should no longer be replicated.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
, 'Leaving POST_UPDATE hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
fnd_msg_pub.Add_Exc_Msg('CSL_CS_INCIDENTS_ALL_ACC_PKG','POST_UPDATE_TASK',sqlerrm);
END POST_UPDATE_INCIDENT;
/* Called before task Delete */
PROCEDURE PRE_DELETE_INCIDENT
( x_return_status OUT NOCOPY varchar2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
END PRE_DELETE_INCIDENT;
/* Called after task Delete */
PROCEDURE POST_DELETE_INCIDENT
( x_return_status OUT NOCOPY varchar2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
END POST_DELETE_INCIDENT;
PROCEDURE INSERT_ALL_ACC_RECORDS
( p_resource_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2 ) IS
CURSOR c_incident( b_resource_id NUMBER ) IS
SELECT inc.INCIDENT_ID
FROM ASG_USER au
, CS_INCIDENTS_ALL_B inc
WHERE au.USER_ID = inc.CREATED_BY
AND au.RESOURCE_ID = b_resource_id;
, 'Entering POST_INSERT hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** insert all SRs created by resource ***/
FOR r_incident IN c_incident( p_resource_id ) LOOP
l_dummy := Pre_Insert_Child( r_incident.incident_id, p_resource_id, G_FLOW_MOBILE_SR );
, 'Leaving POST_INSERT hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, 'Caught exception in INSERT_ALL_ACC_RECORDS:' || fnd_global.local_chr(10) || sqlerrm
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
END INSERT_ALL_ACC_RECORDS;