[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_NOTIFICATION_ATTRIBUTE ( p_notification_id IN NUMBER, p_name IN VARCHAR2 )
IS
l_sender_user BOOLEAN;
SELECT user_id
FROM fnd_user usr
, wf_notifications wno
, wf_notification_attributes wna
WHERE usr.user_name = wna.text_value
AND wna.notification_id = wno.notification_id
AND wna.notification_id = b_notification_id
AND wno.MESSAGE_TYPE = 'CS_MSGS'
AND wno.MESSAGE_NAME = 'FYI_MESSAGE'
AND wno.STATUS = 'OPEN'
AND wna.name = 'SENDER';
SELECT user_id
FROM fnd_user usr
, wf_notifications wfn
WHERE wfn.recipient_role = usr.user_name
AND wfn.MESSAGE_TYPE = 'CS_MSGS'
AND wfn.MESSAGE_NAME = 'FYI_MESSAGE'
AND wfn.STATUS = 'OPEN'
AND wfn.notification_id = b_notification_id;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE nvl(end_date_active, sysdate) >= sysdate
AND category = 'EMPLOYEE'
AND user_id = b_user_id;
SELECT notification_id , name
FROM JTM_WF_NOTIFICATION_AT_ACC
WHERE notification_id = b_notification_id
AND resource_id = b_resource_id;
SELECT access_id
, resource_id
FROM JTM_WF_NOTIFICATIONS_ACC
WHERE NOTIFICATION_ID = b_notification_id;
, 'Entering Procedure INSERT_NOTIFICATION_ATTRIBUTE'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/* INSERT NOTIFICATION ATTRIBUTE */
/* There are 4 possible values for p_name: 'SENDER', 'DELETE_FLAG', 'READ_FLAG' or 'MESSAGE_TEXT'. */
/* First all specific code is executed depending of the value for p_name, then all common code. */
/* Specific code for Attribute Name = 'SENDER' */
IF (p_name = 'SENDER' AND l_sender_mobile_resource) THEN
OPEN c_notification_exists( p_notification_id );
/*** Notification id is not in ACC table yet: Insert ***/
CSL_WF_NOTIFICATIONS_ACC_PKG.INSERT_NOTIFICATION( p_notification_id );
/*** Specific code for Attribute Name = 'DELETE_FLAG' ***/
IF p_name = 'DELETE_FLAG' THEN
IF l_recipient_mobile_resource THEN
/*** Delete notification id for recipient from Notification ACC table. ***/
JTM_HOOK_UTIL_PKG.Delete_Acc
( p_publication_item_names => g_publication_item_name2
, p_acc_table_name => g_acc_table_name2
, p_pk1_name => g_pk1_name
, p_pk1_num_value => p_notification_id
, p_resource_id => r_get_recipient_resource.resource_id
);
, 'Deleted recipient notification id from Notification Attribute ACC table.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/* Delete all attributes of notification id of recipient from Notification Attribute ACC table. */
/* Retrieve all attributes, loop through them and call JTM_HOOK_UTIL_PKG.Delete_Acc for all attributes.*/
OPEN c_recipient_attr(p_notification_id, r_get_recipient_resource.resource_id);
/*** could not find any notification attribute records to be deleted ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( p_notification_id
, g_table_name
, 'Did not find any Notification Attribute records to be deleted for recipient.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** Loop over all available records and delete them from the Notification Attribute ACC table ***/
WHILE c_recipient_attr%FOUND LOOP
/*** Call delete function of JTM_HOOK_UTIL_PKG to delete records from the ACC table ***/
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_notification_id
, p_pk2_name => g_pk2_name
, p_pk2_char_value => r_recipient_attr.name
, p_resource_id => r_get_recipient_resource.resource_id
);
, 'Deleted all records for recipient from Notification Attribute ACC table.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/* Insert Notification Attribute id and name into ACC table for Notification Attributes. */
IF ((( p_name = 'SENDER')
OR ( p_name = 'MESSAGE_TEXT')
OR ( p_name = 'PRIORITY')
OR ( p_name = 'SUBJECT'))
AND l_sender_mobile_resource) THEN
/*** Do an insert into ACC table for sender of notification attribute ***/
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_notification_id
, p_pk2_name => g_pk2_name
, p_pk2_char_value => p_name
, p_resource_id => r_get_sender_resource.resource_id
);
, 'Inserted attributes for sender notification ' || p_notification_id|| ' + ' || p_name
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
/*** Insert Notification Attribute id and name into ACC table for Notification Attributes. ***/
IF ( ((p_name = 'SENDER')
OR (p_name = 'READ_FLAG')
OR (p_name = 'MESSAGE_TEXT')
OR (p_name = 'SUBJECT')
OR (p_name = 'PRIORITY'))
AND l_recipient_mobile_resource) THEN
/*** Insert recipient notification id and name into Notification Attribute ACC table. ***/
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_notification_id
, p_pk2_name => g_pk2_name
, p_pk2_char_value => p_name
, p_resource_id => r_get_recipient_resource.resource_id
);
, 'Inserted recipient notification id and name into Notification Attribute ACC table.'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, 'Leaving Procedure INSERT_NOTIFICATION_ATTRIBUTE'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END INSERT_NOTIFICATION_ATTRIBUTE;
PROCEDURE Insert_All_ACC_Records(
p_resource_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR c_notification_sender (b_resource_id NUMBER) IS
SELECT DISTINCT WNO.NOTIFICATION_ID
FROM WF_NOTIFICATIONS WNO,
WF_NOTIFICATION_ATTRIBUTES WNA,
FND_USER USR,
ASG_USER ADU
WHERE WNO.NOTIFICATION_ID = WNA.NOTIFICATION_ID
AND WNA.NAME = 'SENDER'
AND WNO.MESSAGE_TYPE = 'CS_MSGS'
AND WNO.MESSAGE_NAME = 'FYI_MESSAGE'
AND WNO.STATUS = 'OPEN'
AND WNA.TEXT_VALUE = USR.USER_NAME
AND USR.USER_ID = ADU.USER_ID
AND ADU.RESOURCE_ID = b_resource_id;
SELECT DISTINCT WNO.NOTIFICATION_ID
FROM WF_NOTIFICATIONS WNO,
FND_USER USR,
ASG_USER ADU
WHERE WNO.RECIPIENT_ROLE = USR.USER_NAME
AND USR.USER_ID = ADU.USER_ID
AND ADU.RESOURCE_ID = b_resource_id
AND WNO.STATUS = 'OPEN'
AND WNO.MESSAGE_TYPE = 'CS_MSGS'
AND WNO.MESSAGE_NAME = 'FYI_MESSAGE'
AND NOT EXISTS
( SELECT NULL
FROM WF_NOTIFICATION_ATTRIBUTES WNA_DEL
WHERE WNA_DEL.NOTIFICATION_ID = WNO.NOTIFICATION_ID
AND WNA_DEL.NAME = 'DELETE_FLAG')
AND EXISTS
( SELECT NULL
FROM WF_NOTIFICATION_ATTRIBUTES WNA_DEL
WHERE WNA_DEL.NOTIFICATION_ID = WNO.NOTIFICATION_ID
AND WNA_DEL.NAME = 'MESSAGE_TEXT')
AND EXISTS
( SELECT NULL
FROM WF_NOTIFICATION_ATTRIBUTES WNA_DEL
WHERE WNA_DEL.NOTIFICATION_ID = WNO.NOTIFICATION_ID
AND WNA_DEL.NAME = 'SENDER'
AND WNA_DEL.TEXT_VALUE IN (
SELECT USER_NAME
FROM FND_USER
)
);
SELECT NAME
FROM WF_NOTIFICATION_ATTRIBUTES
WHERE NOTIFICATION_ID = p_notification_id;
, v_message => 'Entering Insert_All_ACC_Records procedure for user: ' || p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Insert all Notification acc and Notification Attributes ACC records for user: '||
p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** First insert the send records of a mobile user ***/
OPEN c_notification_sender ( p_resource_id );
, v_message => 'Insert all Notification ACC: no send-records found for user: ' || p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
CSL_WF_NOTIFICATIONS_ACC_PKG.Insert_Notification(r_notification_sender.notification_id);
Insert_Notification_Attribute(r_notification_sender.notification_id,r_get_attribute_name.name);
/*** Second insert all received records ***/
OPEN c_notification_receive ( p_resource_id );
CSL_WF_NOTIFICATIONS_ACC_PKG.Insert_Notification(r_notification_receive.notification_id);
Insert_Notification_Attribute(r_notification_receive.notification_id,r_get_attribute_name.name);
, v_message => 'Leaving Insert_All_ACC_Records procedure for user: ' || p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_HEADERS_ACC_PKG','Insert_All_ACC_Records',sqlerrm);
END Insert_All_ACC_Records;
PROCEDURE Delete_All_ACC_Records(
p_resource_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR c_notification_sender (b_resource_id NUMBER) IS
SELECT DISTINCT WNO.NOTIFICATION_ID,
USR.USER_ID
FROM WF_NOTIFICATIONS WNO,
WF_NOTIFICATION_ATTRIBUTES WNA,
FND_USER USR,
ASG_USER ADU
WHERE WNO.NOTIFICATION_ID = WNA.NOTIFICATION_ID
AND WNA.NAME = 'SENDER'
AND WNA.TEXT_VALUE = USR.USER_NAME
AND USR.USER_ID = ADU.USER_ID
AND ADU.RESOURCE_ID = b_resource_id;
SELECT DISTINCT WNO.NOTIFICATION_ID,
USR.USER_ID
FROM WF_NOTIFICATIONS WNO,
FND_USER USR,
ASG_USER ADU
WHERE WNO.RECIPIENT_ROLE = USR.USER_NAME
AND USR.USER_ID = ADU.USER_ID
AND ADU.RESOURCE_ID = b_resource_id
AND WNO.STATUS = 'OPEN'
AND NOT EXISTS
( SELECT NULL
FROM WF_NOTIFICATION_ATTRIBUTES WNA_DEL
WHERE WNA_DEL.NOTIFICATION_ID = WNO.NOTIFICATION_ID
AND WNA_DEL.NAME = 'DELETE_FLAG');
SELECT NAME
FROM WF_NOTIFICATION_ATTRIBUTES
WHERE NOTIFICATION_ID = p_notification_id;
, v_message => 'Entering Delete_All_ACC_Records procedure for user: ' || p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Delete all Notification acc and Notification Attributes ACC records for user: '||
p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** First delete the send records of a mobile user ***/
OPEN c_notification_sender ( p_resource_id );
JTM_HOOK_UTIL_PKG.Delete_Acc
( p_publication_item_names => g_publication_item_name
,p_acc_table_name => g_acc_table_name2
,p_pk1_name => g_pk1_name
,p_pk1_num_value => r_notification_sender.notification_id
,p_resource_id => p_resource_id
);
/*Call CSL_FND_USER_ACC_PKG to delete the sender fnd_user from the acc table*/
CSL_FND_USER_ACC_PKG.Delete_User(r_notification_sender.user_id , p_resource_id);
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 => r_notification_sender.notification_id
, p_pk2_name => g_pk2_name
, p_pk2_char_value => r_get_attribute_name.name
, p_resource_id => p_resource_id
);
/*** Second Delete all received records ***/
OPEN c_notification_receive ( p_resource_id );
JTM_HOOK_UTIL_PKG.Delete_Acc
( p_publication_item_names => g_publication_item_name
, p_acc_table_name => g_acc_table_name2
, p_pk1_name => g_pk1_name
, p_pk1_num_value => r_notification_receive.notification_id
, p_resource_id => p_resource_id
);
/*Delete the receiving user*/
CSL_FND_USER_ACC_PKG.Delete_User(r_notification_receive.user_id , p_resource_id);
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 => r_notification_receive.notification_id
, p_pk2_name => g_pk2_name
, p_pk2_char_value => r_get_attribute_name.name
, p_resource_id => p_resource_id
);
, v_message => 'Leaving Delete_All_ACC_Records procedure for user: ' || p_resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Error occurred in Delete_All_ACC_Records'||sqlerrm
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
fnd_msg_pub.Add_Exc_Msg('CSL_WF_NOTIFICATION_AT_ACC_PKG','Delete_All_ACC_Records',sqlerrm);
END Delete_All_ACC_Records;