The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT application_id
INTO x_app_id
FROM fnd_application
WHERE application_short_name = x_product_name ;
/* description : Validation checks for before inserting */
/* new record as well before modification */
/* takes place */
/*---------------------------------------------------------*/
PROCEDURE VALIDATE_DOCUMENT
(
p_document_id IN NUMBER ,
p_source_party_id IN NUMBER ,
p_doc_type_code IN VARCHAR2 ,
p_doc_sub_type_code IN VARCHAR2 ,
p_document_no IN VARCHAR2 ,
p_operator_code IN VARCHAR2 ,
p_product_type_code IN VARCHAR2 ,
p_subscribe_avail_flag IN VARCHAR2 ,
p_subscribe_to_flag IN VARCHAR2 ,
p_object_version_number IN NUMBER ,
p_delete_flag IN VARCHAR2 := 'N'
)
IS
-- Cursor to retrieve doc type code from fnd lookups
CURSOR get_doc_type_code(c_doc_type_code VARCHAR2)
IS
SELECT lookup_code
FROM FND_LOOKUPS
WHERE lookup_code = c_doc_type_code
AND lookup_type = 'AHL_DOC_TYPE'
AND ENABLED_FLAG = 'Y'
-- pbarman April 2003
AND sysdate between nvl(start_date_active,sysdate)
AND nvl(end_date_active,sysdate);
SELECT lookup_code
FROM FND_LOOKUPS
WHERE lookup_code = c_doc_sub_type_code
AND lookup_type = 'AHL_DOC_SUB_TYPE'
AND ENABLED_FLAG = 'Y'
-- pbarman April 2003
AND sysdate between nvl(start_date_active,sysdate)
AND nvl(end_date_active,sysdate);
SELECT party_id
FROM HZ_PARTIES
WHERE party_id = c_operator_code
AND ( party_type ='ORGANIZATION' or party_type = 'PERSON' );
SELECT lookup_code
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_code = c_product_type_code
--Enhancement #2525604: pbarman : April 2003
AND lookup_type = 'ITEM_TYPE'
AND sysdate between nvl(start_date_active,sysdate)
AND nvl(end_date_active,sysdate)
AND enabled_flag = 'Y'
AND view_Application_id = 3;
SELECT source_party_id,
doc_type_code,
doc_sub_type_code,
document_no,
operator_code,
product_type_code,
subscribe_avail_flag,
subscribe_to_flag
FROM AHL_DOCUMENTS_B
WHERE document_id = c_document_id;
SELECT doc_sub_type_code
FROM AHL_DOCUMENT_SUB_TYPES
WHERE doc_type_code like c_doc_type_code
AND doc_sub_type_code like c_doc_sub_type_code;
SELECT 'X'
FROM AHL_DOCUMENTS_B
WHERE document_no = c_document_no
AND source_party_id = c_source_party_id;
l_delete_flag VARCHAR2(1);
/*FND_MESSAGE.SET_NAME('AHL','i am in validate'|| p_Delete_flag);
l_delete_flag := nvl(p_delete_flag, 'N');
IF l_delete_flag <> 'Y'
THEN
IF p_document_id IS NOT NULL
THEN
OPEN get_doc_rec_b_info (p_document_id);
SELECT 'X'
FROM AHL_DOCUMENTS_B
WHERE document_no = c_document_no;
p_delete_flag => p_x_document_tbl(i).delete_flag);
SELECT AHL_DOCUMENTS_B_S.Nextval INTO
l_document_id from DUAL;
/* procedure name: AHL_DI_DOCUMENTS_PKG.INSERT_ROW */
/* description : Added by Senthil to call Table Handler */
/* Date : Dec 07 2001 */
/*---------------------------------------------------------*/
AHL_DOCUMENTS_PKG.INSERT_ROW(
X_ROWID => l_rowid1 ,
X_DOCUMENT_ID => l_document_id,
X_SUBSCRIBE_AVAIL_FLAG => p_x_document_tbl (i).subscribe_avail_flag,
X_SUBSCRIBE_TO_FLAG => p_x_document_tbl (i).subscribe_to_flag ,
X_DOC_TYPE_CODE => p_x_document_tbl (i).doc_type_code,
X_DOC_SUB_TYPE_CODE => l_document_info.doc_sub_type_code,
X_OPERATOR_CODE => l_document_info.operator_code,
X_PRODUCT_TYPE_CODE => l_document_info.product_type_code,
X_ATTRIBUTE_CATEGORY => l_document_info.attribute_category ,
X_ATTRIBUTE1 => l_document_info.attribute1,
X_ATTRIBUTE2 => l_document_info.attribute2,
X_ATTRIBUTE3 => l_document_info.attribute3,
X_ATTRIBUTE4 => l_document_info.attribute4,
X_ATTRIBUTE5 => l_document_info.attribute5,
X_ATTRIBUTE6 => l_document_info.attribute6,
X_ATTRIBUTE7 => l_document_info.attribute7,
X_ATTRIBUTE8 => l_document_info.attribute8,
X_ATTRIBUTE9 => l_document_info.attribute9,
X_ATTRIBUTE10 => l_document_info.attribute10,
X_ATTRIBUTE11 => l_document_info.attribute11,
X_ATTRIBUTE12 => l_document_info.attribute12,
X_ATTRIBUTE13 => l_document_info.attribute13,
X_ATTRIBUTE14 => l_document_info.attribute14,
X_ATTRIBUTE15 => l_document_info.attribute15,
X_OBJECT_VERSION_NUMBER => 1,
X_SOURCE_PARTY_ID => p_x_document_tbl (i).source_party_id,
X_DOCUMENT_NO => p_x_document_tbl (i).document_no,
X_DOCUMENT_TITLE => l_document_info.document_title,
X_CREATION_DATE => sysdate ,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate ,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
/* description : Updates the document record and its */
/* associated suppliers, recipients */
/* */
/*------------------------------------------------------*/
PROCEDURE MODIFY_DOCUMENT
(
p_api_version IN NUMBER := 1.0 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_x_document_tbl IN OUT NOCOPY document_tbl ,
p_x_supplier_tbl IN OUT NOCOPY Supplier_Tbl ,
p_x_recipient_tbl IN OUT NOCOPY Recipient_Tbl ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--Used to retrieve the existing record
CURSOR get_doc_rec_b_info(c_document_id NUMBER)
IS
SELECT ROWID,
document_id,
source_party_id,
doc_type_code,
doc_sub_type_code,
document_no,
operator_code,
product_type_code,
subscribe_avail_flag,
subscribe_to_flag,
object_version_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM AHL_DOCUMENTS_B
WHERE document_id = c_document_id;
p_delete_flag => p_x_document_tbl(i).delete_flag);
/* procedure name: AHL_DOCUMENTS_PKG.UPDATE_ROW */
/* description : Added by Senthil to call Table Handler */
/* Date : Dec 07 2001 */
/*---------------------------------------------------------*/
AHL_DOCUMENTS_PKG.UPDATE_ROW (
X_DOCUMENT_ID => p_x_document_tbl(i).document_id,
X_SUBSCRIBE_AVAIL_FLAG => p_x_document_tbl(i).subscribe_avail_flag,
X_SUBSCRIBE_TO_FLAG => p_x_document_tbl(i).subscribe_to_flag,
X_DOC_TYPE_CODE => p_x_document_tbl(i).doc_type_code,
X_DOC_SUB_TYPE_CODE => p_x_document_tbl(i).doc_sub_type_code,
X_OPERATOR_CODE => p_x_document_tbl(i).operator_code,
X_PRODUCT_TYPE_CODE => p_x_document_tbl(i).product_type_code,
X_ATTRIBUTE_CATEGORY => p_x_document_tbl(i).attribute_category,
X_ATTRIBUTE1 => p_x_document_tbl(i).attribute1,
X_ATTRIBUTE2 => p_x_document_tbl(i).attribute2,
X_ATTRIBUTE3 => p_x_document_tbl(i).attribute3,
X_ATTRIBUTE4 => p_x_document_tbl(i).attribute4,
X_ATTRIBUTE5 => p_x_document_tbl(i).attribute5,
X_ATTRIBUTE6 => p_x_document_tbl(i).attribute6 ,
X_ATTRIBUTE7 => p_x_document_tbl(i).attribute7 ,
X_ATTRIBUTE8 => p_x_document_tbl(i).attribute8 ,
X_ATTRIBUTE9 => p_x_document_tbl(i).attribute9 ,
X_ATTRIBUTE10 => p_x_document_tbl(i).attribute10 ,
X_ATTRIBUTE11 => p_x_document_tbl(i).attribute11 ,
X_ATTRIBUTE12 => p_x_document_tbl(i).attribute12 ,
X_ATTRIBUTE13 => p_x_document_tbl(i).attribute13 ,
X_ATTRIBUTE14 => p_x_document_tbl(i).attribute14 ,
X_ATTRIBUTE15 => p_x_document_tbl(i).attribute15 ,
X_OBJECT_VERSION_NUMBER => p_x_document_tbl(i).object_version_number+1,
X_SOURCE_PARTY_ID => p_x_document_tbl(i).source_party_id,
X_DOCUMENT_NO => p_x_document_tbl(i).document_no,
X_DOCUMENT_TITLE => p_x_document_tbl(i).document_title,
X_LAST_UPDATE_DATE => sysdate ,
X_LAST_UPDATED_BY => fnd_global.user_id ,
X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
/* description : Validation checks for before insert */
/* new record as well before update */
/*-----------------------------------------------------*/
PROCEDURE VALIDATE_SUPPLIER
( P_SUPPLIER_DOCUMENT_ID IN NUMBER ,
P_SUPPLIER_ID IN NUMBER ,
P_DOCUMENT_ID IN NUMBER ,
P_PREFERENCE_CODE IN VARCHAR2 ,
--P_OBJECT_VERSION_NUMBER IN NUMBER,
P_DELETE_FLAG IN VARCHAR2 := 'N')
IS
-- Cursor to retrieve the preference code from fnd lookups
CURSOR get_preference_code(c_preference_code VARCHAR2)
IS
SELECT lookup_code
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_code = c_preference_code
AND lookup_type = 'AHL_SUPPLIER_PREF_TYPE'
AND sysdate between nvl(start_date_active,sysdate)
AND nvl(end_date_active,sysdate);
SELECT 'X'
FROM AHL_DOCUMENTS_B
WHERE document_id = c_document_id;
SELECT supplier_id,
document_id,
preference_code
FROM AHL_SUPPLIER_DOCUMENTS
WHERE supplier_document_id = c_supplier_document_id;
SELECT 'X'
FROM AHL_SUPPLIER_DOCUMENTS
WHERE supplier_id = c_supplier_id
AND document_id = c_document_id;
SELECT party_number
FROM AHL_HZ_PO_SUPPLIERS_V
WHERE party_id =c_supplier_id;
SELECT party_number
FROM HZ_PARTIES
WHERE party_id =c_supplier_id;
SELECT SEGMENT1
FROM PO_VENDORS
WHERE VENDOR_ID =c_supplier_id;
SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
INTO l_prod_install_status
FROM DUAL;
IF p_delete_flag <> 'Y'
THEN
IF p_supplier_document_id IS NOT NULL
THEN
OPEN get_supplier_rec_info(p_supplier_document_id);
SELECT 'X'
FROM AHL_SUPPLIER_DOCUMENTS
WHERE supplier_id = c_supplier_id
AND document_id = c_document_id;
SELECT party_number
FROM AHL_HZ_PO_SUPPLIERS_V
WHERE party_id =c_supplier_id;
SELECT party_number
FROM HZ_PARTIES
WHERE party_id =c_supplier_id;
SELECT SEGMENT1
FROM PO_VENDORS
WHERE VENDOR_ID =c_supplier_id;
SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
INTO l_prod_install_status
FROM DUAL;
p_delete_flag => p_x_supplier_tbl(i).delete_flag
);
SELECT AHL_SUPPLIER_DOCUMENTS_S.Nextval INTO
l_supplier_document_id from DUAL;
INSERT INTO AHL_SUPPLIER_DOCUMENTS
(
SUPPLIER_DOCUMENT_ID,
SUPPLIER_ID,
DOCUMENT_ID,
PREFERENCE_CODE,
OBJECT_VERSION_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_supplier_document_id,
p_x_supplier_tbl(i).supplier_id,
p_x_supplier_tbl(i).document_id,
l_supplier_info.preference_code,
1,
l_supplier_info.attribute_category,
l_supplier_info.attribute1,
l_supplier_info.attribute2,
l_supplier_info.attribute3,
l_supplier_info.attribute4,
l_supplier_info.attribute5,
l_supplier_info.attribute6,
l_supplier_info.attribute7,
l_supplier_info.attribute8,
l_supplier_info.attribute9,
l_supplier_info.attribute10,
l_supplier_info.attribute11,
l_supplier_info.attribute12,
l_supplier_info.attribute13,
l_supplier_info.attribute14,
l_supplier_info.attribute15,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
/* description : Update the existing supplier record */
/* for an associated document */
/* */
/*------------------------------------------------------*/
PROCEDURE MODIFY_SUPPLIER
(
p_api_version IN NUMBER := 1.0 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_supplier_tbl IN supplier_tbl ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- To get the supplier info
CURSOR get_supplier_rec_info(c_supplier_document_id NUMBER)
IS
SELECT ROWID,
supplier_id,
document_id,
preference_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
object_version_number
FROM AHL_SUPPLIER_DOCUMENTS
WHERE supplier_document_id = c_supplier_document_id
FOR UPDATE OF object_version_number NOWAIT;
SELECT party_number
FROM AHL_HZ_PO_SUPPLIERS_V
WHERE party_id =c_supplier_id;
SELECT party_number
FROM HZ_PARTIES
WHERE party_id =c_supplier_id;
SELECT SEGMENT1
FROM PO_VENDORS
WHERE VENDOR_ID =c_supplier_id;
SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
INTO l_prod_install_status
FROM DUAL;
p_delete_flag => p_supplier_tbl(i).delete_flag
);
AND p_supplier_tbl(i).delete_flag <> 'Y'
THEN
l_supplier_info.supplier_id := p_supplier_tbl(i).supplier_id;
UPDATE AHL_SUPPLIER_DOCUMENTS
SET supplier_id = l_supplier_info.supplier_id,
document_id = l_supplier_info.document_id,
preference_code = l_supplier_info.preference_code,
object_version_number = l_supplier_info.object_version_number+1,
attribute_category = l_supplier_info.attribute_category,
attribute1 = l_supplier_info.attribute1,
attribute2 = l_supplier_info.attribute2,
attribute3 = l_supplier_info.attribute3,
attribute4 = l_supplier_info.attribute4,
attribute5 = l_supplier_info.attribute5,
attribute6 = l_supplier_info.attribute6,
attribute7 = l_supplier_info.attribute7,
attribute8 = l_supplier_info.attribute8,
attribute9 = l_supplier_info.attribute9,
attribute10 = l_supplier_info.attribute10,
attribute11 = l_supplier_info.attribute11,
attribute12 = l_supplier_info.attribute12,
attribute13 = l_supplier_info.attribute13,
attribute14 = l_supplier_info.attribute14,
attribute15 = l_supplier_info.attribute15,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE ROWID = l_supplier_info.rowid;
p_supplier_tbl(i).delete_flag = 'Y' )
THEN
DELETE_SUPPLIER
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
p_validate_only => FND_API.G_TRUE ,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_supplier_rec => p_supplier_tbl(i) ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data
);
END IF;--AD IF THERE IS ERROR DONT INSERT
/* procedure name: delete_supplier */
/* description : Removes the supplier record */
/* for an associated document */
/* */
/*------------------------------------------------------*/
PROCEDURE DELETE_SUPPLIER
(
p_api_version IN NUMBER := 1.0 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_supplier_rec IN supplier_rec ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2)
IS
--To get the supplier info
CURSOR get_supplier_rec_info(c_supplier_document_id NUMBER)
IS
SELECT ROWID ,
supplier_id,
document_id,
object_version_number
FROM AHL_SUPPLIER_DOCUMENTS
WHERE supplier_document_id = c_supplier_document_id
FOR UPDATE OF object_version_number NOWAIT;
SELECT 'X'
FROM AHL_SUBSCRIPTIONS_B
WHERE document_id = c_document_id
--AND subscribed_frm_party_id = c_supplier_id;
SELECT party_number
FROM AHL_HZ_PO_SUPPLIERS_V
WHERE party_id =c_supplier_id;
SELECT party_number
FROM HZ_PARTIES
WHERE party_id =c_supplier_id;
SELECT SEGMENT1
FROM PO_VENDORS
WHERE VENDOR_ID =c_supplier_id;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SUPPLIER';
SAVEPOINT delete_supplier;
AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Delete Supplier','+SUP+');
SELECT AHL_DI_DOC_INDEX_PVT.GET_PRODUCT_INSTALL_STATUS('PO')
INTO l_prod_install_status
FROM DUAL;
DELETE FROM AHL_SUPPLIER_DOCUMENTS
WHERE ROWID = l_rowid;
AHL_DEBUG_PUB.debug( 'End of private api Delete Supplier','+SUP+');
ROLLBACK TO delete_supplier;
AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.delete Supplier','+SUP+');
ROLLBACK TO delete_supplier;
AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Supplier','+SUP+');
ROLLBACK TO delete_supplier;
p_procedure_name => 'DELETE_SUPPLIER',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Supplier','+SUP+');
END DELETE_SUPPLIER;
/* description : Validation checks for before inserting */
/* new record as well before update */
/* */
/*-------------------------------------------------------*/
PROCEDURE VALIDATE_RECIPIENT
( p_recipient_document_id IN NUMBER ,
p_recipient_party_id IN NUMBER ,
p_document_id IN NUMBER ,
p_object_version_number IN NUMBER ,
p_delete_flag IN VARCHAR2 := 'N')
IS
-- Cursor to get the recipient info
CURSOR get_recipient_rec_info (c_recipient_document_id NUMBER)
IS
SELECT recipient_party_id,
document_id
FROM AHL_RECIPIENT_DOCUMENTS
WHERE recipient_document_id = c_recipient_document_id;
SELECT 'X'
FROM AHL_DOCUMENTS_B
WHERE document_id = c_document_id;
SELECT 'X'
FROM AHL_RECIPIENT_DOCUMENTS
WHERE recipient_party_id = c_recipient_party_id
AND document_id = c_document_id;
SELECT party_number
FROM hz_parties
WHERE party_id = c_recipient_party_id;
IF p_delete_flag <> 'Y'
THEN
IF p_recipient_document_id IS NOT NULL
THEN
OPEN get_recipient_rec_info(p_recipient_document_id);
SELECT 'X'
FROM AHL_RECIPIENT_DOCUMENTS
WHERE recipient_party_id = c_recipient_party_id
AND document_id = c_document_id;
SELECT party_number
FROM hz_parties
WHERE party_id = c_recipient_party_id;
p_delete_flag => p_x_recipient_tbl(i).delete_flag);
SELECT AHL_RECIPIENT_DOCUMENTS_S.Nextval INTO
l_recipient_document_id from DUAL;
INSERT INTO AHL_RECIPIENT_DOCUMENTS
(
RECIPIENT_DOCUMENT_ID,
RECIPIENT_PARTY_ID,
DOCUMENT_ID,
OBJECT_VERSION_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_recipient_document_id,
p_x_recipient_tbl(i).recipient_party_id,
p_x_recipient_tbl(i).document_id,
1,
l_recipient_info.attribute_category,
l_recipient_info.attribute1,
l_recipient_info.attribute2,
l_recipient_info.attribute3,
l_recipient_info.attribute4,
l_recipient_info.attribute5,
l_recipient_info.attribute6,
l_recipient_info.attribute7,
l_recipient_info.attribute8,
l_recipient_info.attribute9,
l_recipient_info.attribute10,
l_recipient_info.attribute11,
l_recipient_info.attribute12,
l_recipient_info.attribute13,
l_recipient_info.attribute14,
l_recipient_info.attribute15,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
/* description : Update the existing recipient record*/
/* for an associated document */
/* */
/*----------------------------------------------------*/
PROCEDURE MODIFY_RECIPIENT
(
p_api_version IN NUMBER := 1.0 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_recipient_tbl IN OUT NOCOPY recipient_tbl ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2)
IS
-- To get the exisitng record
CURSOR get_recipient_rec_info(c_recipient_document_id NUMBER)
IS
SELECT ROWID,
recipient_party_id,
document_id,
object_version_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM AHL_RECIPIENT_DOCUMENTS
WHERE recipient_document_id = c_recipient_document_id
FOR UPDATE OF object_version_number NOWAIT;
p_delete_flag => p_recipient_tbl(i).delete_flag
);
AND p_recipient_tbl(i).delete_flag <> 'Y')
THEN
l_recipient_info.recipient_party_id := p_recipient_tbl(i).recipient_party_id;
UPDATE AHL_RECIPIENT_DOCUMENTS
SET recipient_party_id = l_recipient_info.recipient_party_id,
document_id = l_recipient_info.document_id,
object_version_number = l_recipient_info.object_version_number+1,
attribute_category = l_recipient_info.attribute_category,
attribute1 = l_recipient_info.attribute1,
attribute2 = l_recipient_info.attribute2,
attribute3 = l_recipient_info.attribute3,
attribute4 = l_recipient_info.attribute4,
attribute5 = l_recipient_info.attribute5,
attribute6 = l_recipient_info.attribute6,
attribute7 = l_recipient_info.attribute7,
attribute8 = l_recipient_info.attribute8,
attribute9 = l_recipient_info.attribute9,
attribute10 = l_recipient_info.attribute10,
attribute11 = l_recipient_info.attribute11,
attribute12 = l_recipient_info.attribute12,
attribute13 = l_recipient_info.attribute13,
attribute14 = l_recipient_info.attribute14,
attribute15 = l_recipient_info.attribute15,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE ROWID = l_recipient_info.rowid;
p_recipient_tbl(i).delete_flag = 'Y')
THEN
DELETE_RECIPIENT
( p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
p_validate_only => FND_API.G_TRUE ,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_recipient_rec => p_recipient_tbl(i) ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data);
/* procedure name: delete_recipient */
/* description : Removes the recipient record for an */
/* associated document */
/* */
/*----------------------------------------------------*/
PROCEDURE DELETE_RECIPIENT
(
p_api_version IN NUMBER := 1.0 ,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_recipient_rec IN recipient_rec ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2)
IS
--
--Code commented: pjha 23-Jul-2002 :because recipient is uneditable after creation, hence no need.
--also bug#2473425
CURSOR get_recipient_rec_info(c_recipient_document_id NUMBER)
IS
SELECT ROWID ,
object_version_number
FROM AHL_RECIPIENT_DOCUMENTS
WHERE recipient_document_id = c_recipient_document_id
FOR UPDATE OF object_version_number NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RECIPIENT';
SAVEPOINT delete_recipient;
AHL_DEBUG_PUB.debug( 'enter ahl_di_doc_index_pvt.Delete Recipient','+REP+');
FND_MESSAGE.SET_NAME('AHL','AHL_DI_RECIPIENT_DELETED');
DELETE FROM AHL_RECIPIENT_DOCUMENTS
WHERE ROWID = l_rowid;
AHL_DEBUG_PUB.debug( 'End of private api Delete Recipient','+REP+');
ROLLBACK TO delete_recipient;
AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Recipient','+REP+');
ROLLBACK TO delete_recipient;
AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Recipient','+REP+');
ROLLBACK TO delete_recipient;
p_procedure_name => 'DELETE_RECIPIENT',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_di_doc_index_pvt.Delete Recipient','+REP+');
END DELETE_RECIPIENT;