[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_PARTY_ASSG (p_user_id IN NUMBER,
p_party_id IN NUMBER,
p_owner_id IN NUMBER,
p_party_site_id IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
)
IS
--variable declarations
l_chk_party NUMBER := NULL;
l_deleted_flag VARCHAR2(1) := NULL;
CSM_UTIL_PKG.LOG('Entering CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG Package ', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
SELECT COUNT(1)
INTO l_chk_party
FROM hz_parties hp
WHERE hp.party_id = p_party_id;
SELECT COUNT(1)
INTO l_chk_party_site
FROM hz_party_sites hps
WHERE hps.party_id = p_party_id
AND hps.party_site_id = p_party_site_id;
SELECT COUNT(1)
INTO l_cnt_party
FROM CSM_PARTY_ASSIGNMENT
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND PARTY_SITE_ID = l_party_site_id;
CSM_UTIL_PKG.LOG('Inserting the record with party id only ', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
INSERT INTO CSM_PARTY_ASSIGNMENT( USER_ID, PARTY_ID,
OWNER_ID, PARTY_SITE_ID,
DELETED_FLAG, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
( p_user_id, p_party_id,
p_owner_id, l_party_site_id,
'N', 1,
SYSDATE, 1,
SYSDATE, 1
);
SELECT COUNT(1)
INTO l_cnt_party_site
FROM CSM_PARTY_ASSIGNMENT
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND PARTY_SITE_ID = -2;
we have to delete the record which hold the party alone */
SELECT COUNT(1)
INTO l_cnt_upd_party
FROM CSM_PARTY_ASSIGNMENT
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND PARTY_SITE_ID = -1;
CSM_UTIL_PKG.LOG('Updating the deleted flag to Y for party record ', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
UPDATE CSM_PARTY_ASSIGNMENT
SET DELETED_FLAG = 'Y'
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND PARTY_SITE_ID = -1;
/* if the party is inserted along with the party_site
another record is inserted with party_site_id as -2
for deleting the access table purpose*/
CSM_UTIL_PKG.LOG('Inserting the record with party id and party site id along with another record with - 2 as party site value', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
INSERT INTO CSM_PARTY_ASSIGNMENT( USER_ID, PARTY_ID,
OWNER_ID, PARTY_SITE_ID,
DELETED_FLAG, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
( p_user_id, p_party_id,
p_owner_id, -2,
'N', 1,
SYSDATE, 1,
SYSDATE, 1
);
INSERT INTO CSM_PARTY_ASSIGNMENT( USER_ID, PARTY_ID,
OWNER_ID, PARTY_SITE_ID,
DELETED_FLAG, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
( p_user_id, p_party_id,
p_owner_id, l_party_site_id,
'N', 1,
SYSDATE, 1,
SYSDATE, 1
);
CSM_UTIL_PKG.LOG('Inserting the record with party id and party site id only', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
INSERT INTO CSM_PARTY_ASSIGNMENT( USER_ID, PARTY_ID,
OWNER_ID, PARTY_SITE_ID,
DELETED_FLAG, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
)
VALUES
( p_user_id, p_party_id,
p_owner_id, l_party_site_id,
'N', 1,
SYSDATE, 1,
SYSDATE, 1
);
we are updating the deleted flag to N*/
IF l_cnt_party <> 0 THEN
SELECT deleted_flag
INTO l_deleted_flag
FROM CSM_PARTY_ASSIGNMENT
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND PARTY_SITE_ID = l_party_site_id;
IF l_deleted_flag = 'Y' THEN
CSM_UTIL_PKG.LOG('Updating the deleted flag to N for record which was deleted by mistake', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
UPDATE CSM_PARTY_ASSIGNMENT
SET DELETED_FLAG = 'N'
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND PARTY_SITE_ID = l_party_site_id;
/* if the deleted flag is set to Y for a party
and then a new record with party and party site id is inserted
then we are updating the deleted flag to N*/
SELECT COUNT(1)
INTO l_cnt_upd_site
FROM CSM_PARTY_ASSIGNMENT
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND PARTY_SITE_ID not in (-2,-1)
AND DELETED_FLAG = 'N';
CSM_UTIL_PKG.LOG('Updating the deleted flag to N for record which hold the -2 value if any record with part site is inserted', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
UPDATE CSM_PARTY_ASSIGNMENT
SET DELETED_FLAG = 'N'
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND OWNER_ID = p_owner_id
AND PARTY_SITE_ID = -2;
x_error_message := 'PARTY_ID successfully Inserted ';
CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG Package ', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
x_error_message := 'Exception in CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG Procedure :'||'while inserting the party -'||p_party_id|| 'for the user -'||p_user_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
END INSERT_PARTY_ASSG;
PROCEDURE DELETE_PARTY_ASSG (p_user_id IN NUMBER,
p_party_id IN NUMBER,
p_owner_id IN NUMBER,
p_party_site_id IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_error_message OUT NOCOPY VARCHAR2
)
IS
--variable declarations
l_cnt_upd NUMBER := 0;
CSM_UTIL_PKG.LOG('Entering CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG Package ', 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
SELECT COUNT(1)
INTO l_cnt_upd
FROM CSM_PARTY_ASSIGNMENT
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND PARTY_SITE_ID = l_party_site_id;
CSM_UTIL_PKG.LOG('Updating the deleted flag to Y for Deleted records', 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
UPDATE CSM_PARTY_ASSIGNMENT
SET DELETED_FLAG = 'Y'
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND OWNER_ID = p_owner_id
AND PARTY_SITE_ID = l_party_site_id;
l_error_message := 'PARTY_ID successfully Deleted ';
CSM_UTIL_PKG.LOG('No records found for Deleting', 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
/* if all the party_sites are deleted
then record which holds the party_id (i.e -2)
will also be deleted*/
SELECT COUNT(1)
INTO l_cnt_upd_site
FROM CSM_PARTY_ASSIGNMENT
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND PARTY_SITE_ID not in (-2,-1)
AND DELETED_FLAG = 'N';
CSM_UTIL_PKG.LOG('Updating the deleted flag to Y for the record which holds the -2 value', 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
UPDATE CSM_PARTY_ASSIGNMENT
SET DELETED_FLAG = 'Y'
WHERE USER_ID = p_user_id
AND PARTY_ID = p_party_id
AND OWNER_ID = p_owner_id
AND PARTY_SITE_ID = -2;
CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG Package ', 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
x_error_message := 'Exception in CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG Procedure :'||'while deleting the party -'||p_party_id|| 'for the user -'||p_user_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
END DELETE_PARTY_ASSG;