The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*** cache variables used by pre/post update ***/
CURSOR c_update_cache_rec( b_debrief_line_id NUMBER)
IS
SELECT inventory_item_id
, NVL( NVL(issuing_inventory_org_id, receiving_inventory_org_id)
, FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')) AS organization_id
FROM csf_debrief_lines
WHERE debrief_line_id = b_debrief_line_id;
g_pre_update_rec c_update_cache_rec%ROWTYPE;
SELECT debrief_header_id
FROM CSF_DEBRIEF_LINES
WHERE debrief_line_id = b_debrief_line_id;
SELECT *
FROM CSF_DEBRIEF_LINES
WHERE debrief_line_id = b_debrief_line_id;
PROCEDURE Insert_ACC_Record
( p_debrief_line_id IN NUMBER
,p_resource_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_debrief_line_id
, g_table_name
, '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 debrief_line 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_PK1_NAME => g_pk1_name
,P_PK1_NUM_VALUE => p_debrief_line_id
,P_RESOURCE_ID => p_resource_id
);
, 'Leaving Insert_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Insert_ACC_Record;
PROCEDURE Update_ACC_Record
( p_debrief_line_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_debrief_line_id
, g_table_name
, 'Entering Update_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Update debrief_line ACC record ***/
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 debrief_line for resource from acc table ***/
PROCEDURE Delete_ACC_Record
( p_debrief_line_id IN NUMBER
,p_resource_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_debrief_line_id
, g_table_name
, 'Entering Delete_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Delete debrief_line 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_debrief_line_id
,P_RESOURCE_ID => p_resource_id
);
, 'Leaving Delete_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Delete_ACC_Record;
Public function that gets called when a debrief_line 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_debrief_line_id IN NUMBER
,p_resource_id IN NUMBER
)
RETURN BOOLEAN
IS
CURSOR c_debrief_line( b_debrief_line_id NUMBER)
IS
SELECT inventory_item_id
, NVL( NVL(issuing_inventory_org_id, receiving_inventory_org_id)
, FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')) AS organization_id
FROM csf_debrief_lines
WHERE debrief_line_id = b_debrief_line_id;
, 'Entering Pre_Insert_Child procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Insert the debrief header ***/
CSL_CSF_DEBRIEF_HDR_ACC_PKG.Insert_Debrief_Header
( l_debrief_header_id
,p_resource_id
);
/*** insert the system item used by the debrief line ***/
OPEN c_debrief_line( p_debrief_line_id );
CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Pre_Insert_Child (
p_inventory_item_id => r_debrief_line.inventory_item_id
,p_organization_id => r_debrief_line.organization_id
,p_resource_id => p_resource_id
);
/*** yes -> insert debrief_line acc record ***/
Insert_ACC_Record
( p_debrief_line_id
,p_resource_id
);
, 'Leaving Pre_Insert_Child procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Pre_Insert_Child;
Public procedure that gets called when debrief lines need to be inserted into ACC table.
***/
PROCEDURE Pre_Insert_Children
( p_task_assignment_id IN NUMBER
,p_resource_id IN NUMBER
)
IS
CURSOR c_debrief_line (b_task_assignment_id NUMBER) IS
SELECT CDL.debrief_line_id
FROM CSF_DEBRIEF_HEADERS CDH, CSF_DEBRIEF_LINES CDL
WHERE CDH.task_assignment_id = b_task_assignment_id
AND CDH.debrief_header_id = CDL.debrief_header_id;
, 'Entering Pre_Insert_Children procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Insert record if applicable ***/
l_dummy := Pre_Insert_Child
( r_debrief_line.debrief_line_id
,p_resource_id
);
, 'Pre_Insert_Children: debrief line was not insertable.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
, 'Leaving Pre_Insert_Children procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Pre_Insert_Children;
Public procedure that gets called when a debrief_line needs to be deleted from ACC table.
***/
PROCEDURE Post_Delete_Child
( p_debrief_line_id IN NUMBER
,p_resource_id IN NUMBER
)
IS
CURSOR c_debrief_line( b_debrief_line_id NUMBER)
IS
SELECT inventory_item_id
, NVL( NVL(issuing_inventory_org_id, receiving_inventory_org_id)
, FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')) AS organization_id
FROM csf_debrief_lines
WHERE debrief_line_id = b_debrief_line_id;
, 'Entering Post_Delete_Child'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** no -> delete debrief_line record from ACC ***/
Delete_ACC_Record
( p_debrief_line_id
,p_resource_id);
/*** delete the system item used by the debrief line ***/
OPEN c_debrief_line( p_debrief_line_id );
CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Post_Delete_Child (
p_inventory_item_id => r_debrief_line.inventory_item_id
,p_organization_id => r_debrief_line.organization_id
,p_resource_id => p_resource_id
);
/*** Delete the debrief header ***/
CSL_CSF_DEBRIEF_HDR_ACC_PKG.Delete_Debrief_Header
( l_debrief_header_id
,p_resource_id
);
, 'Leaving Post_Delete_Child'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Post_Delete_Child;
Public procedure that gets called when debrief lines need to be deleted into ACC table.
***/
PROCEDURE Post_Delete_Children
( p_task_assignment_id IN NUMBER
,p_resource_id IN NUMBER
)
IS
CURSOR c_debrief_line (b_task_assignment_id NUMBER) IS
SELECT CDL.debrief_line_id
FROM CSF_DEBRIEF_HEADERS CDH, CSF_DEBRIEF_LINES CDL
WHERE CDH.task_assignment_id = b_task_assignment_id
AND CDH.debrief_header_id = CDL.debrief_header_id;
, 'Entering Post_Delete_Children procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Delete record if applicable ***/
Post_Delete_Child
( r_debrief_line.debrief_line_id
,p_resource_id
);
, 'Leaving Post_Delete_Children procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Post_Delete_Children;
SELECT resource_id
FROM jtf_task_assignments jta
, csf_debrief_headers dbh
, csf_debrief_lines dbl
WHERE jta.task_assignment_id = dbh.task_assignment_id
AND dbh.debrief_header_id = dbl.debrief_header_id
AND dbl.debrief_line_id = b_debrief_line_id;
/* Called before debrief_line Insert */
PROCEDURE PRE_INSERT_DEBRIEF_LINE
( x_return_status OUT NOCOPY varchar2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
END PRE_INSERT_DEBRIEF_LINE;
/* Called after debrief_line Insert */
PROCEDURE POST_INSERT_DEBRIEF_LINE
( x_return_status OUT NOCOPY varchar2
)
IS
l_debrief_line_id NUMBER;
, 'Entering POST_INSERT hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, 'POST_INSERT_DEBRIEF_LINE' || fnd_global.local_chr(10) ||
'Resource_id ' || l_resource_id || ' is not a mobile user.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** Insert record if applicable ***/
l_dummy := Pre_Insert_Child
( l_debrief_line_id
,l_resource_id
);
, 'Leaving POST_INSERT hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, 'Caught exception in POST_INSERT hook:' || fnd_global.local_chr(10) || sqlerrm
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
fnd_msg_pub.Add_Exc_Msg('CSL_CSF_DEBRIEF_LINE_ACC_PKG','POST_INSERT_DEBRIEF_LINE',sqlerrm);
END POST_INSERT_DEBRIEF_LINE;
/* Called before debrief_line Update */
PROCEDURE PRE_UPDATE_DEBRIEF_LINE
( x_return_status OUT NOCOPY varchar2
)
IS
l_debrief_line_id NUMBER;
, 'Entering PRE_UPDATE hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Check if debrief_line before update matches criteria ***/
IF JTM_HOOK_UTIL_PKG.isMobileFSresource( l_resource_id ) THEN
g_pre_replicate := Replicate_Record( l_debrief_line_id );
, 'Debrief line was replicated before update.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
OPEN c_update_cache_rec( l_debrief_line_id );
FETCH c_update_cache_rec INTO g_pre_update_rec;
CLOSE c_update_cache_rec;
, 'Debrief line was not replicated before update'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
, '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_CSF_DEBRIEF_LINE_ACC_PKG','PRE_UPDATE_DEBRIEF_LINE',sqlerrm);
END PRE_UPDATE_DEBRIEF_LINE;
/* Called after debrief_line Update */
PROCEDURE POST_UPDATE_DEBRIEF_LINE
( x_return_status OUT NOCOPY varchar2
)
IS
l_debrief_line_id NUMBER;
l_post_update_rec c_update_cache_rec%ROWTYPE;
, 'Entering POST_UPDATE hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Check if debrief_line after update matches criteria ***/
l_replicate := FALSE;
, 'Debrief line should be replicated after update.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
, 'Debrief line should not be replicated after update'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** Check results from pre update ***/
IF g_pre_replicate THEN
/*** replicate record after update? ***/
IF NOT l_replicate THEN
/*** No -> Delete the record ***/
Post_Delete_Child
( l_debrief_line_id
, l_resource_id );
, 'Debrief line was deleted during post update.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** yes -> re-send updated debrief record to resource ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( l_debrief_line_id
, g_table_name
, 'Debrief line being re-sent to mobile user.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/* Update the debrief line */
Update_ACC_Record
( l_debrief_line_id
,l_resource_id
,l_access_id
);
OPEN c_update_cache_rec( l_debrief_line_id );
FETCH c_update_cache_rec INTO l_post_update_rec;
IF (g_pre_update_rec.inventory_item_id <> l_post_update_rec.inventory_item_id
OR g_pre_update_rec.organization_id <> l_post_update_rec.organization_id) THEN
-- yes -> remove old item and insert new item
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( l_debrief_line_id
, g_table_name
, 'System item changed -> deleting old item and inserting new item.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Post_Delete_Child (
p_inventory_item_id => g_pre_update_rec.inventory_item_id
,p_organization_id => g_pre_update_rec.organization_id
,p_resource_id => l_resource_id
);
CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Pre_Insert_Child (
p_inventory_item_id => l_post_update_rec.inventory_item_id
,p_organization_id => l_post_update_rec.organization_id
,p_resource_id => l_resource_id
);
CLOSE c_update_cache_rec;
/*** record was not replicated before update -> replicate now ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( l_debrief_line_id
, g_table_name
, 'Debrief line was inserted during post update.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** Insert record if applicable ***/
l_dummy := Pre_Insert_Child
( l_debrief_line_id
,l_resource_id
);
, '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_CSF_DEBRIEF_LINE_ACC_PKG','POST_UPDATE_DEBRIEF_LINE',sqlerrm);
END POST_UPDATE_DEBRIEF_LINE;
/* Called before debrief_line Delete */
PROCEDURE PRE_DELETE_DEBRIEF_LINE
( x_return_status OUT NOCOPY varchar2
)
IS
l_debrief_line_id NUMBER;
, v_message => 'Entering PRE_DELETE hook'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Delete debrief line from ACC table. This also deletes its Debrief Header ***/
Post_Delete_Child ( l_debrief_line_id
, l_resource_id );
, v_message => 'Leaving PRE_DELETE hook'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Caught exception in PRE_DELETE hook:' || fnd_global.local_chr(10) || sqlerrm
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
fnd_msg_pub.Add_Exc_Msg('CSL_CSF_DEBRIEF_LINE_ACC_PKG','PRE_DELETE_DEBRIEF_LINE',sqlerrm);
END PRE_DELETE_DEBRIEF_LINE;
/* Called after debrief_line Delete */
PROCEDURE POST_DELETE_DEBRIEF_LINE
( x_return_status OUT NOCOPY varchar2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
END POST_DELETE_DEBRIEF_LINE;