[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM JTF_NOTES_B
WHERE jtf_note_id = b_jtf_note_id;
SELECT entered_by
FROM JTF_NOTES_B
WHERE jtf_note_id = b_jtf_note_id;
SELECT resource_id
FROM JTF_RS_RESOURCE_EXTNS
WHERE user_id = b_user_id;
PROCEDURE Insert_ACC_Record
( p_jtf_note_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_jtf_note_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 jtf_note 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_pk_name
,P_PK1_NUM_VALUE => p_jtf_note_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_jtf_note_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_jtf_note_id
, g_table_name
, 'Entering Update_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Update jtf_note 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 jtf_note for resource from acc table ***/
PROCEDURE Delete_ACC_Record
( p_jtf_note_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_jtf_note_id
, g_table_name
, 'Entering Delete_ACC_Record'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Delete jtf_note 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_pk_name
,P_PK1_NUM_VALUE => p_jtf_note_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 jtf_note 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_jtf_note_id IN NUMBER
,p_resource_id IN NUMBER
)
RETURN BOOLEAN
IS
l_acc_id NUMBER;
, 'Entering Pre_Insert_Child procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** yes -> insert jtf_note acc record ***/
Insert_ACC_Record
( p_jtf_note_id
,p_resource_id
);
/*** Insert the user ***/
CSL_FND_USER_ACC_PKG.Insert_User
( l_user_id
,p_resource_id
);
/*** Insert the resource ext ***/
/*** Only if resource id is not -1 ***/
IF l_resource_extn_id > -1 THEN
CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Insert_Resource_Extns
( l_resource_extn_id
,p_resource_id
);
, 'Leaving Pre_Insert_Child procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Pre_Insert_Child;
Public function that gets called when jtf_notes needs to be inserted into ACC table.
Returns TRUE when records already were or have been inserted into ACC table.
***/
FUNCTION Pre_Insert_Children
( p_source_obj_id IN NUMBER
,p_source_obj_code IN VARCHAR2
,p_resource_id IN NUMBER
)
RETURN BOOLEAN
IS
CURSOR c_jtf_note (b_source_obj_id NUMBER,
b_source_obj_code VARCHAR2) IS
SELECT *
FROM JTF_NOTES_B
WHERE source_object_id = b_source_obj_id
AND source_object_code = b_source_obj_code;
, 'Entering Pre_Insert_Children procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Insert record if applicable ***/
l_dummy := Pre_Insert_Child
( r_jtf_note.jtf_note_id
,p_resource_id
);
, 'Pre_Insert_Children: note 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 jtf_note needs to be deleted from the ACC table.
***/
PROCEDURE Post_Delete_Child
( p_jtf_note_id IN NUMBER
,p_resource_id IN NUMBER
)
IS
l_acc_id NUMBER;
, 'Entering Post_Delete_Child'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** no -> delete jtf_note record from ACC ***/
Delete_ACC_Record
( p_jtf_note_id
,p_resource_id);
/*** Delete the user ***/
CSL_FND_USER_ACC_PKG.Delete_User
( l_user_id
,p_resource_id
);
/*** Delete the resource ext ***/
/*** Only if resource id is not -1 ***/
IF l_resource_extn_id > -1 THEN
CSL_JTF_RESOURCE_EXTNS_ACC_PKG.Delete_Resource_Extns
( l_resource_extn_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 jtf_notes need to be deleted from the ACC table.
***/
PROCEDURE Post_Delete_Children
( p_source_obj_id IN NUMBER
,p_source_obj_code IN VARCHAR2
,p_resource_id IN NUMBER
)
IS
CURSOR c_jtf_note (b_source_obj_id NUMBER,
b_source_obj_code VARCHAR2) IS
SELECT *
FROM JTF_NOTES_B
WHERE source_object_id = b_source_obj_id
AND source_object_code = b_source_obj_code;
, 'Entering Post_Delete_Children procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Insert record if applicable ***/
Post_Delete_Child
( r_jtf_note.jtf_note_id
,p_resource_id
);
, 'Leaving Post_Delete_Children procedure'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END Post_Delete_Children;
/* Called before jtf_note Insert */
PROCEDURE PRE_INSERT_NOTES
( x_return_status OUT NOCOPY varchar2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
END PRE_INSERT_NOTES;
/* Called after jtf_note Insert */
PROCEDURE POST_INSERT_NOTES ( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, p_jtf_note_id IN NUMBER )
IS
l_resource_id NUMBER; /* Get from API */
SELECT SOURCE_OBJECT_ID
, SOURCE_OBJECT_CODE
FROM JTF_NOTES_B
WHERE JTF_NOTE_ID = b_note_id;
SELECT resource_id
FROM csl_cs_incidents_all_acc
WHERE incident_id = b_id;
SELECT resource_id
FROM csl_jtf_tasks_acc
WHERE task_id = b_id;
SELECT resource_id
FROM csl_hz_parties_acc
WHERE party_id = b_id;
SELECT resource_id
FROM csl_csi_item_instances_acc
WHERE instance_id = b_id;
SELECT resource_id
FROM CSL_SR_CONTRACT_HEADERS_ACC a,
CSL_SR_CONTRACT_HEADERS b
WHERE a.incident_id = b.incident_id
AND b.contract_service_id = b_id;
SELECT resource_id
FROM JTM_CSF_DEBRIEF_HEADERS_ACC
WHERE debrief_header_id = b_id;
, 'Entering POST_INSERT hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
l_replicate := Pre_Insert_Child( p_jtf_note_id, r_sr.resource_id );
l_replicate := Pre_Insert_Child( p_jtf_note_id, r_task.resource_id );
l_replicate := Pre_Insert_Child( p_jtf_note_id, r_party.resource_id );
l_replicate := Pre_Insert_Child( p_jtf_note_id, r_cp.resource_id );
l_replicate := Pre_Insert_Child( p_jtf_note_id, r_contracts.resource_id );
l_replicate := Pre_Insert_Child( p_jtf_note_id, r_debrief.resource_id );
/*** Insert record if applicable ***/
IF l_replicate = FALSE THEN
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( p_jtf_note_id
, g_table_name
, 'Note '||p_jtf_note_id||' did not match the criteria to be replicated'||fnd_global.local_chr(10)||
'Object id = '||r_object.SOURCE_OBJECT_ID||fnd_global.local_chr(10)||
'Object_code = '||r_object.SOURCE_OBJECT_CODE
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
, '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_JTF_NOTES_ACC_PKG','POST_INSERT_JTF_NOTES',sqlerrm);
END POST_INSERT_NOTES;
/* Called before jtf_note Update */
PROCEDURE PRE_UPDATE_NOTES
( x_return_status OUT NOCOPY varchar2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
END PRE_UPDATE_NOTES;
/* Called after jtf_note Update */
PROCEDURE POST_UPDATE_NOTES ( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, p_jtf_note_id IN NUMBER )
IS
l_jtf_note_id NUMBER;
, 'Entering POST_UPDATE hook'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Check if jtf_note after update matches criteria ***/
l_replicate := Replicate_Record( l_jtf_note_id );
/*** replicate record after update? ***/
IF NOT l_replicate THEN
/*** yes -> re-send updated jtf_note record to all resources ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( l_jtf_note_id
, g_table_name
, 'Note was not replicateable after update.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** yes -> re-send updated note record to all resources ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( l_jtf_note_id
, g_table_name
, 'Note being re-sent to mobile users.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
, 'POST_UPDATE_DEBRIEF_LINE' || fnd_global.local_chr(10) ||
'Resource_id ' || l_tab_resource_id(i) || ' is not a mobile user.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Update_ACC_Record
( l_jtf_note_id
,l_tab_resource_id(i)
,l_tab_access_id(i)
);
, '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_JTF_NOTES_ACC_PKG','POST_UPDATE_JTF_NOTES',sqlerrm);
END POST_UPDATE_NOTES;
/* Called before jtf_note Delete */
PROCEDURE PRE_DELETE_NOTES
( x_return_status OUT NOCOPY varchar2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
END PRE_DELETE_NOTES;
/* Called after jtf_note Delete */
PROCEDURE POST_DELETE_NOTES
( x_return_status OUT NOCOPY varchar2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
END POST_DELETE_NOTES;