[Home] [Help]
1: PACKAGE BODY OKC_CONTRACT_DOCS_GRP AS
2: /* $Header: OKCGCONTRACTDOCB.pls 120.5.12020000.4 2013/03/22 14:58:48 harchand ship $ */
3:
4:
5: ---------------------------------------------------------------------------
28: G_CONTRACT_DOC_CATEGORY CONSTANT VARCHAR2(200) := 'OKC_REPO_CONTRACT';
29: G_CONTRACT_IMAGE_CATEGORY CONSTANT VARCHAR2(200) := 'OKC_REPO_CONTRACT_IMAGE';
30: G_APP_ABSTRACT_CATEGORY CONSTANT VARCHAR2(200) := 'OKC_REPO_APP_ABSTRACT';
31: G_SUPPORTING_DOC_CATEGORY CONSTANT VARCHAR2(200) := 'OKC_REPO_SUPPORTING_DOC';
32: G_PKG_NAME CONSTANT VARCHAR2(40) := 'OKC_CONTRACT_DOCS_GRP';
33: G_MODULE CONSTANT VARCHAR2(200) := 'okc.plsql.'||G_PKG_NAME ||'.';
34: G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
35: G_COPY_ALL_DOCS CONSTANT VARCHAR2(30) := 'OKC_REP_COPY_ALL_CON_DOCS';
36:
82: ) IS
83:
84: l_api_version CONSTANT NUMBER := 1;
85: l_api_name CONSTANT VARCHAR2(30) := 'Insert_Contract_Doc';
86: l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE := 1;
87: l_created_by OKC_CONTRACT_DOCS.CREATED_BY%TYPE;
88: l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
89: l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
90: l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
83:
84: l_api_version CONSTANT NUMBER := 1;
85: l_api_name CONSTANT VARCHAR2(30) := 'Insert_Contract_Doc';
86: l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE := 1;
87: l_created_by OKC_CONTRACT_DOCS.CREATED_BY%TYPE;
88: l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
89: l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
90: l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
91: l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
84: l_api_version CONSTANT NUMBER := 1;
85: l_api_name CONSTANT VARCHAR2(30) := 'Insert_Contract_Doc';
86: l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE := 1;
87: l_created_by OKC_CONTRACT_DOCS.CREATED_BY%TYPE;
88: l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
89: l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
90: l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
91: l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
92: l_attached_document_id OKC_CONTRACT_DOCS.ATTACHED_DOCUMENT_ID%TYPE := p_attached_document_id;
85: l_api_name CONSTANT VARCHAR2(30) := 'Insert_Contract_Doc';
86: l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE := 1;
87: l_created_by OKC_CONTRACT_DOCS.CREATED_BY%TYPE;
88: l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
89: l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
90: l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
91: l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
92: l_attached_document_id OKC_CONTRACT_DOCS.ATTACHED_DOCUMENT_ID%TYPE := p_attached_document_id;
93: l_media_id FND_DOCUMENTS.MEDIA_ID%TYPE;
86: l_object_version_number OKC_CONTRACT_DOCS.OBJECT_VERSION_NUMBER%TYPE := 1;
87: l_created_by OKC_CONTRACT_DOCS.CREATED_BY%TYPE;
88: l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
89: l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
90: l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
91: l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
92: l_attached_document_id OKC_CONTRACT_DOCS.ATTACHED_DOCUMENT_ID%TYPE := p_attached_document_id;
93: l_media_id FND_DOCUMENTS.MEDIA_ID%TYPE;
94: l_pk2_value FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
87: l_created_by OKC_CONTRACT_DOCS.CREATED_BY%TYPE;
88: l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
89: l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
90: l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
91: l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
92: l_attached_document_id OKC_CONTRACT_DOCS.ATTACHED_DOCUMENT_ID%TYPE := p_attached_document_id;
93: l_media_id FND_DOCUMENTS.MEDIA_ID%TYPE;
94: l_pk2_value FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
95:
88: l_creation_date OKC_CONTRACT_DOCS.CREATION_DATE%TYPE;
89: l_last_updated_by OKC_CONTRACT_DOCS.LAST_UPDATED_BY%TYPE;
90: l_last_update_login OKC_CONTRACT_DOCS.LAST_UPDATE_LOGIN%TYPE;
91: l_last_update_date OKC_CONTRACT_DOCS.LAST_UPDATE_DATE%TYPE;
92: l_attached_document_id OKC_CONTRACT_DOCS.ATTACHED_DOCUMENT_ID%TYPE := p_attached_document_id;
93: l_media_id FND_DOCUMENTS.MEDIA_ID%TYPE;
94: l_pk2_value FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
95:
96:
300:
301: --------------------------------------------
302: -- Calling Simple API for Creating A Row
303: --------------------------------------------
304: OKC_CONTRACT_DOCS_PVT.Insert_Row(
305: p_validation_level => p_validation_level,
306: x_return_status => x_return_status,
307: p_business_document_type => p_business_document_type,
308: p_business_document_id => p_business_document_id,
435: l_primary_doc_yn VARCHAR2(1);
436:
437: CURSOR contract_doc_csr IS
438: SELECT primary_contract_doc_flag
439: FROM OKC_CONTRACT_DOCS
440: WHERE business_document_type = p_business_document_type
441: AND business_document_id = p_business_document_id
442: AND business_document_version = p_business_document_version
443: AND attached_document_id = p_attached_document_id;
529:
530: --------------------------------------------
531: -- Calling Simple API for Updating A Row
532: --------------------------------------------
533: OKC_CONTRACT_DOCS_PVT.Update_Row(
534: p_validation_level => p_validation_level,
535: x_return_status => x_return_status,
536: p_business_document_type => p_business_document_type,
537: p_business_document_id => p_business_document_id,
633: p_object_version_number IN NUMBER
634: ) IS
635: l_api_version CONSTANT NUMBER := 1;
636: l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract_Doc';
637: l_effective_from_type OKC_CONTRACT_DOCS.EFFECTIVE_FROM_TYPE%TYPE;
638: l_effective_from_id OKC_CONTRACT_DOCS.EFFECTIVE_FROM_ID%TYPE;
639: l_effective_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
640: l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE := -1;
641: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE := -1;
634: ) IS
635: l_api_version CONSTANT NUMBER := 1;
636: l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract_Doc';
637: l_effective_from_type OKC_CONTRACT_DOCS.EFFECTIVE_FROM_TYPE%TYPE;
638: l_effective_from_id OKC_CONTRACT_DOCS.EFFECTIVE_FROM_ID%TYPE;
639: l_effective_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
640: l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE := -1;
641: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE := -1;
642: l_object_version_number NUMBER := -1;
635: l_api_version CONSTANT NUMBER := 1;
636: l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract_Doc';
637: l_effective_from_type OKC_CONTRACT_DOCS.EFFECTIVE_FROM_TYPE%TYPE;
638: l_effective_from_id OKC_CONTRACT_DOCS.EFFECTIVE_FROM_ID%TYPE;
639: l_effective_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
640: l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE := -1;
641: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE := -1;
642: l_object_version_number NUMBER := -1;
643: l_doc_approved_flag VARCHAR2(1);
643: l_doc_approved_flag VARCHAR2(1);
644: l_msg_count NUMBER;
645: l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
646: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
647: l_parent_attached_doc_id OKC_CONTRACT_DOCS.PARENT_ATTACHED_DOC_ID%TYPE;
648: l_pk2_value FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
649:
650: CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER, l_attached_doc_id NUMBER) IS
651: SELECT * FROM OKC_CONTRACT_DOCS
647: l_parent_attached_doc_id OKC_CONTRACT_DOCS.PARENT_ATTACHED_DOC_ID%TYPE;
648: l_pk2_value FND_ATTACHED_DOCUMENTS.pk2_value%TYPE;
649:
650: CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER, l_attached_doc_id NUMBER) IS
651: SELECT * FROM OKC_CONTRACT_DOCS
652: WHERE business_document_type = l_bus_doc_type
653: AND business_document_id = l_bus_doc_id
654: AND business_document_version = l_bus_doc_version
655: AND attached_document_id = l_attached_doc_id
655: AND attached_document_id = l_attached_doc_id
656: AND delete_flag = 'N';
657:
658: CURSOR child_ref_attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER, l_attached_doc_id NUMBER) IS
659: SELECT * FROM OKC_CONTRACT_DOCS
660: WHERE business_document_type = l_bus_doc_type
661: AND business_document_id = l_bus_doc_id
662: AND effective_from_version = l_bus_doc_version
663: AND parent_attached_doc_id = l_attached_doc_id;
662: AND effective_from_version = l_bus_doc_version
663: AND parent_attached_doc_id = l_attached_doc_id;
664:
665: CURSOR rel_ref_attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_parent_attached_doc_id NUMBER) IS
666: SELECT * FROM OKC_CONTRACT_DOCS
667: WHERE business_document_type = l_bus_doc_type
668: AND business_document_id = l_bus_doc_id
669: AND business_document_version = G_CURRENT_VERSION
670: AND parent_attached_doc_id = l_parent_attached_doc_id
736:
737: -- Get the parent attached_doc_id
738: SELECT parent_attached_doc_id
739: INTO l_parent_attached_doc_id
740: FROM OKC_CONTRACT_DOCS
741: WHERE business_document_type = p_business_document_type
742: AND business_document_id = p_business_document_id
743: AND business_document_version = p_business_document_version
744: AND attached_document_id = p_attached_document_id;
760: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2264-1:effective_from_version is: ' || child_ref_attach_rec.effective_from_version);
761: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2265-1:delete_flag is: ' || child_ref_attach_rec.delete_flag);
762: END IF;
763:
764: OKC_CONTRACT_DOCS_GRP.delete_contract_doc(
765: p_api_version => p_api_version,
766: p_init_msg_list => p_init_msg_list,
767: p_business_document_type => child_ref_attach_rec.business_document_type,
768: p_business_document_id => child_ref_attach_rec.business_document_id,
795: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2264-2:effective_from_version is: ' || rel_ref_attach_rec.effective_from_version);
796: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2265-2:delete_flag is: ' || rel_ref_attach_rec.delete_flag);
797: END IF;
798:
799: OKC_CONTRACT_DOCS_GRP.delete_contract_doc(
800: p_api_version => p_api_version,
801: p_init_msg_list => p_init_msg_list,
802: p_business_document_type => rel_ref_attach_rec.business_document_type,
803: p_business_document_id => rel_ref_attach_rec.business_document_id,
820:
821:
822: SELECT effective_from_type, effective_from_id, effective_from_version
823: INTO l_effective_from_type, l_effective_from_id, l_effective_from_version
824: FROM OKC_CONTRACT_DOCS
825: WHERE business_document_type = p_business_document_type
826: AND business_document_id = p_business_document_id
827: AND business_document_version = p_business_document_version
828: AND attached_document_id = p_attached_document_id;
833: (NVL(l_parent_attached_doc_id, p_attached_document_id) <> p_attached_document_id)) THEN
834: -- (l_effective_from_type = p_business_document_type AND
835: -- l_effective_from_id = p_business_document_id AND
836: -- l_effective_from_version <> p_business_document_version) THEN
837: -- DO not delete anything. Just update the OKC_CONTRACT_DOCS record to delete_flag='Y' and
838: -- add 'D' suffix to FND_ATTACHED_DOCUMENTS record's pk2_value column.
839: FOR attach_rec IN attach_csr(p_business_document_type, p_business_document_id,
840: p_business_document_version, p_attached_document_id)
841: LOOP
902: -- Close cursors.
903: CLOSE fnd_attached_doc_csr;
904: CLOSE fnd_doc_csr;
905:
906: -- Update the record in OKC_CONTRACT_DOCS table, set delete_flag='Y'
907: OKC_CONTRACT_DOCS_PVT.Update_Row(
908: x_return_status => l_return_status,
909: p_business_document_type => attach_rec.business_document_type,
910: p_business_document_id => attach_rec.business_document_id,
903: CLOSE fnd_attached_doc_csr;
904: CLOSE fnd_doc_csr;
905:
906: -- Update the record in OKC_CONTRACT_DOCS table, set delete_flag='Y'
907: OKC_CONTRACT_DOCS_PVT.Update_Row(
908: x_return_status => l_return_status,
909: p_business_document_type => attach_rec.business_document_type,
910: p_business_document_id => attach_rec.business_document_id,
911: p_business_document_version => attach_rec.business_document_version,
961:
962: --------------------------------------------------------------------
963: -- Calling Simple API for Deleting A Row
964: --------------------------------------------------------------------
965: OKC_CONTRACT_DOCS_PVT.Delete_Row(
966: x_return_status => x_return_status,
967: p_business_document_type => p_business_document_type,
968: p_business_document_id => p_business_document_id,
969: p_business_document_version => p_business_document_version,
1064:
1065: l_msg_count NUMBER;
1066: l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1067: l_api_version NUMBER := 1;
1068: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
1069: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1070: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1071: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1072: l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE;
1065: l_msg_count NUMBER;
1066: l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1067: l_api_version NUMBER := 1;
1068: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
1069: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1070: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1071: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1072: l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE;
1073: l_effective_from_version OKC_CONTRACT_DOCS.effective_from_version%TYPE;
1066: l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1067: l_api_version NUMBER := 1;
1068: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
1069: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1070: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1071: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1072: l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE;
1073: l_effective_from_version OKC_CONTRACT_DOCS.effective_from_version%TYPE;
1074: l_parent_attached_doc_id OKC_CONTRACT_DOCS.parent_attached_doc_id%TYPE;
1069: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1070: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1071: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1072: l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE;
1073: l_effective_from_version OKC_CONTRACT_DOCS.effective_from_version%TYPE;
1074: l_parent_attached_doc_id OKC_CONTRACT_DOCS.parent_attached_doc_id%TYPE;
1075: l_non_ref_attachment_created VARCHAR2(1);
1076:
1077:
1070: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1071: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1072: l_datatype_id FND_DOCUMENTS.DATATYPE_ID%TYPE;
1073: l_effective_from_version OKC_CONTRACT_DOCS.effective_from_version%TYPE;
1074: l_parent_attached_doc_id OKC_CONTRACT_DOCS.parent_attached_doc_id%TYPE;
1075: l_non_ref_attachment_created VARCHAR2(1);
1076:
1077:
1078: -- Attachment cursor
1076:
1077:
1078: -- Attachment cursor
1079: CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER) IS
1080: SELECT * FROM OKC_CONTRACT_DOCS
1081: WHERE business_document_type = l_bus_doc_type
1082: AND business_document_id = l_bus_doc_id
1083: AND business_document_version = l_bus_doc_version;
1084: -- AND delete_flag = 'N';
1085:
1086: -- cursor for deleting prev. version deleted ref. attachments in -99
1087: CURSOR ref_del_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER,
1088: l_bus_doc_version NUMBER, l_curr_version NUMBER) IS
1089: SELECT * FROM OKC_CONTRACT_DOCS
1090: WHERE business_document_type = l_bus_doc_type
1091: AND business_document_id = l_bus_doc_id
1092: AND business_document_version = l_curr_version
1093: AND effective_from_type = l_bus_doc_type
1300: X_datatype_id => l_datatype_id,
1301: delete_document_flag => 'N'); -- Don't delete FND_DOCUMENTS records
1302:
1303: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1304: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2737: Deleting OKC_CONTRACT_DOCS record');
1305: END IF;
1306: -- Delete record in OKC_CONTRACT_DOCS table
1307: OKC_CONTRACT_DOCS_PVT.Delete_Row(
1308: x_return_status => l_return_status,
1302:
1303: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1304: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2737: Deleting OKC_CONTRACT_DOCS record');
1305: END IF;
1306: -- Delete record in OKC_CONTRACT_DOCS table
1307: OKC_CONTRACT_DOCS_PVT.Delete_Row(
1308: x_return_status => l_return_status,
1309: p_business_document_type => attach_rec.business_document_type,
1310: p_business_document_id => attach_rec.business_document_id,
1303: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1304: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2737: Deleting OKC_CONTRACT_DOCS record');
1305: END IF;
1306: -- Delete record in OKC_CONTRACT_DOCS table
1307: OKC_CONTRACT_DOCS_PVT.Delete_Row(
1308: x_return_status => l_return_status,
1309: p_business_document_type => attach_rec.business_document_type,
1310: p_business_document_id => attach_rec.business_document_id,
1311: p_business_document_version => attach_rec.business_document_version,
1330: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2741:attch doc id is: ' || ref_del_rec.attached_document_id);
1331: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2742:effective_from_type is: ' || ref_del_rec.effective_from_type);
1332: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2743:effective_from_id is: ' || ref_del_rec.effective_from_id);
1333: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2744:effective_from_version is: ' || ref_del_rec.effective_from_version);
1334: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2745: Deleting OKC_CONTRACT_DOCS ref. records');
1335: END IF;
1336: -- get the value of FND's datatype_id. Needed for
1337: OPEN datatype_csr(ref_del_rec.attached_document_id);
1338: FETCH datatype_csr INTO l_datatype_id;
1353: FND_ATTACHED_DOCUMENTS3_PKG.DELETE_ROW(
1354: X_attached_document_id => ref_del_rec.attached_document_id,
1355: X_datatype_id => l_datatype_id,
1356: delete_document_flag => 'N'); -- Don't delete FND_DOCUMENTS records
1357: -- Delete record in OKC_CONTRACT_DOCS table
1358: OKC_CONTRACT_DOCS_PVT.Delete_Row(
1359: x_return_status => l_return_status,
1360: p_business_document_type => ref_del_rec.business_document_type,
1361: p_business_document_id => ref_del_rec.business_document_id,
1354: X_attached_document_id => ref_del_rec.attached_document_id,
1355: X_datatype_id => l_datatype_id,
1356: delete_document_flag => 'N'); -- Don't delete FND_DOCUMENTS records
1357: -- Delete record in OKC_CONTRACT_DOCS table
1358: OKC_CONTRACT_DOCS_PVT.Delete_Row(
1359: x_return_status => l_return_status,
1360: p_business_document_type => ref_del_rec.business_document_type,
1361: p_business_document_id => ref_del_rec.business_document_id,
1362: p_business_document_version => ref_del_rec.business_document_version,
1454: l_api_version NUMBER := 1;
1455:
1456: -- Attachment cursor
1457: CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER) IS
1458: SELECT * FROM OKC_CONTRACT_DOCS
1459: WHERE business_document_type = l_bus_doc_type
1460: AND business_document_id = l_bus_doc_id
1461: AND business_document_version = l_bus_doc_version;
1462:
1495: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3220:attch doc id is: ' || attach_rec.attached_document_id);
1496: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3230:effective_from_version is: ' || attach_rec.effective_from_version);
1497: END IF;
1498:
1499: -- Delete OKC_CONTRACT_DOCS record
1500: Delete_Contract_Doc (
1501: p_api_version => l_api_version,
1502: p_init_msg_list => p_init_msg_list,
1503:
1590: l_api_version NUMBER := 1;
1591:
1592: -- Attachment cursor
1593: CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER) IS
1594: SELECT distinct business_document_version FROM OKC_CONTRACT_DOCS
1595: WHERE business_document_type = l_bus_doc_type
1596: AND business_document_id = l_bus_doc_id
1597: AND business_document_version <> -99
1598: ORDER by business_document_version DESC ;
1759: l_datatype_id NUMBER;
1760: l_msg_count NUMBER;
1761: l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1762: l_api_version NUMBER := 1;
1763: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
1764: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1765: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1766: l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
1767: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1760: l_msg_count NUMBER;
1761: l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1762: l_api_version NUMBER := 1;
1763: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
1764: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1765: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1766: l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
1767: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1768: l_contract_category_id FND_DOCUMENT_CATEGORIES.CATEGORY_ID%TYPE;
1761: l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
1762: l_api_version NUMBER := 1;
1763: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
1764: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1765: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1766: l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
1767: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1768: l_contract_category_id FND_DOCUMENT_CATEGORIES.CATEGORY_ID%TYPE;
1769: l_supporting_category_id FND_DOCUMENT_CATEGORIES.CATEGORY_ID%TYPE;
1762: l_api_version NUMBER := 1;
1763: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
1764: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
1765: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
1766: l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
1767: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
1768: l_contract_category_id FND_DOCUMENT_CATEGORIES.CATEGORY_ID%TYPE;
1769: l_supporting_category_id FND_DOCUMENT_CATEGORIES.CATEGORY_ID%TYPE;
1770: l_copy_all_docs_yn VARCHAR2(1);
1794: KDOC.delete_flag delete_flag,
1795: FADB2.attached_document_id to_attached_document_id,
1796: KDOC.primary_contract_doc_flag,
1797: KDOC.mergeable_doc_flag
1798: FROM FND_ATTACHED_DOCUMENTS FADB1, OKC_CONTRACT_DOCS KDOC,
1799: FND_ATTACHED_DOCUMENTS FADB2,
1800: FND_DOCUMENTS_vl doc1,
1801: FND_DOCUMENTS_vl doc2
1802: WHERE
2160: l_datatype_id NUMBER;
2161: l_msg_count NUMBER;
2162: l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2163: l_api_version NUMBER := 1;
2164: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
2165: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
2166: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
2167: l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
2168: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
2161: l_msg_count NUMBER;
2162: l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2163: l_api_version NUMBER := 1;
2164: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
2165: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
2166: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
2167: l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
2168: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
2169: l_category_name FND_DOCUMENT_CATEGORIES.NAME%TYPE;
2162: l_msg_data FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2163: l_api_version NUMBER := 1;
2164: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
2165: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
2166: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
2167: l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
2168: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
2169: l_category_name FND_DOCUMENT_CATEGORIES.NAME%TYPE;
2170: l_parent_attached_doc_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
2163: l_api_version NUMBER := 1;
2164: l_business_document_type OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_TYPE%TYPE;
2165: l_business_document_id OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_ID%TYPE;
2166: l_business_document_version OKC_CONTRACT_DOCS.BUSINESS_DOCUMENT_VERSION%TYPE;
2167: l_from_version OKC_CONTRACT_DOCS.EFFECTIVE_FROM_VERSION%TYPE;
2168: l_attached_document_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
2169: l_category_name FND_DOCUMENT_CATEGORIES.NAME%TYPE;
2170: l_parent_attached_doc_id FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
2171:
2171:
2172:
2173: -- Attachment cursor to get the attachments for a given doc type, id and version
2174: CURSOR attach_csr(l_bus_doc_type VARCHAR2, l_bus_doc_id NUMBER, l_bus_doc_version NUMBER) IS
2175: SELECT * FROM OKC_CONTRACT_DOCS
2176: WHERE business_document_type = l_bus_doc_type
2177: AND business_document_id = l_bus_doc_id
2178: AND business_document_version = l_bus_doc_version
2179: AND delete_flag = 'N';
2238: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2239: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5071: Category name is: ' || l_category_name);
2240: END IF;
2241:
2242: -- Insert the record in OKC_CONTRACT_DOCS
2243: -- If p_copy_for_amendment = 'Y' copy everything
2244: -- p_copy_primary_doc_flag = 'Y' - copy only primary contract
2245: -- Do not copy attachments if source doc. type is same as destination doc. type and
2246: -- category is Approval abstract or Contract Image
2642: ) RETURN VARCHAR2 IS
2643: l_api_name CONSTANT VARCHAR2(30) := 'Is_Primary_Terms_Doc_mergeable';
2644: CURSOR contract_doc_csr IS
2645: SELECT 'Y'
2646: FROM okc_contract_docs
2647: WHERE business_document_type = p_document_type
2648: AND business_document_id = p_document_id
2649: AND mergeable_doc_flag='Y'
2650: AND primary_contract_doc_flag='Y'
2696:
2697: CURSOR contract_doc_csr IS
2698:
2699: SELECT b.media_id , docs.mergeable_doc_flag
2700: FROM OKC_CONTRACT_DOCS docs, FND_ATTACHED_DOCUMENTS fnd, FND_DOCUMENTS b
2701: WHERE docs.primary_contract_doc_flag = 'Y'
2702: AND NVL(docs.delete_flag,'N') = 'N'
2703: AND docs.business_document_version = -99
2704: AND docs.business_document_type = p_document_type
2747: ) RETURN VARCHAR2 IS
2748: l_api_name CONSTANT VARCHAR2(30) := 'Has_Primary_Contract_Doc';
2749: CURSOR contract_doc_csr IS
2750: SELECT '!'
2751: FROM OKC_CONTRACT_DOCS
2752: WHERE business_document_type = p_document_type
2753: AND business_document_id = p_document_id
2754: AND primary_contract_doc_flag = 'Y'
2755: AND NVL(delete_flag,'N') = 'N'
2792: ) IS
2793: l_api_name CONSTANT VARCHAR2(30) := 'Clear_Primary_Doc_Flag';
2794: CURSOR primary_doc_csr IS
2795: SELECT attached_document_id,object_version_number
2796: FROM OKC_CONTRACT_DOCS
2797: WHERE business_document_type = p_document_type
2798: AND business_document_id = p_document_id
2799: AND primary_contract_doc_flag = 'Y'
2800: AND NVL(delete_flag,'N') = 'N'
2799: AND primary_contract_doc_flag = 'Y'
2800: AND NVL(delete_flag,'N') = 'N'
2801: AND business_document_version = -99;
2802:
2803: l_attached_document_id OKC_CONTRACT_DOCS.ATTACHED_DOCUMENT_ID%TYPE := NULL;
2804: l_object_version_number OKC_CONTRACT_DOCS.object_version_number%TYPE;
2805:
2806: BEGIN
2807: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2800: AND NVL(delete_flag,'N') = 'N'
2801: AND business_document_version = -99;
2802:
2803: l_attached_document_id OKC_CONTRACT_DOCS.ATTACHED_DOCUMENT_ID%TYPE := NULL;
2804: l_object_version_number OKC_CONTRACT_DOCS.object_version_number%TYPE;
2805:
2806: BEGIN
2807: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2808: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering Clear_Primary_Doc_Flag');
2813: FETCH primary_doc_csr into l_attached_document_id,l_object_version_number;
2814: CLOSE primary_doc_csr;
2815:
2816: IF l_attached_document_id IS NOT NULL THEN
2817: OKC_CONTRACT_DOCS_PVT.update_row(
2818: x_return_status => x_return_status,
2819: p_business_document_type => p_document_type,
2820: p_business_document_id => p_document_id,
2821: p_business_document_version => -99,
2836: x_return_status := G_RET_STS_UNEXP_ERROR ;
2837: END Clear_Primary_Doc_Flag;
2838:
2839:
2840: END OKC_CONTRACT_DOCS_GRP;