[Home] [Help]
386: End getDelTypeIntFlag;
387:
388: /**
389: * Helper method to return Event Code and Before After value for given
390: * event id, stored in OKC_DELIVERABLES
391: */
392: PROCEDURE getDelEventDetails(
393: p_event_id IN NUMBER,
394: p_end_event_yn IN varchar2,
595: -- in the database. When the cursor is selected into the datastructure
596: -- there is a mismatch.
597: CURSOR del_cur IS
598: SELECT *
599: FROM OKC_DELIVERABLES
600: WHERE business_document_id = p_source_doc_id
601: AND business_document_type = p_source_doc_type
602: AND NVL(amendment_operation,'NONE') <> 'DELETED'
603: AND manage_yn = 'N'
606:
607:
608: CURSOR del_ins_cur(x NUMBER) IS
609: SELECT *
610: FROM okc_deliverables a
611: WHERE business_document_id = p_source_doc_id
612: AND business_document_type = p_source_doc_type
613: AND recurring_del_parent_id = x;
614: del_ins_rec del_ins_cur%ROWTYPE;
630: TYPE delIdTabType IS TABLE OF delIdRecType;
631: delIdTab delIdTabType;
632: l_recurring_del_parent_id NUMBER;
633:
634: l_target_start_event_id okc_deliverables.relative_st_date_event_id%TYPE:=null;
635: l_target_end_event_id okc_deliverables.relative_end_date_event_id%TYPE:=null;
636:
637: BEGIN
638:
631: delIdTab delIdTabType;
632: l_recurring_del_parent_id NUMBER;
633:
634: l_target_start_event_id okc_deliverables.relative_st_date_event_id%TYPE:=null;
635: l_target_end_event_id okc_deliverables.relative_end_date_event_id%TYPE:=null;
636:
637: BEGIN
638:
639: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1051: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1052: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'118: Before insert');
1053: END IF;
1054: FOR i IN delNewTab.FIRST..delNewTab.LAST LOOP
1055: INSERT INTO okc_deliverables
1056: (DELIVERABLE_ID,
1057: BUSINESS_DOCUMENT_TYPE ,
1058: BUSINESS_DOCUMENT_ID ,
1059: BUSINESS_DOCUMENT_NUMBER ,
1426: -- where fixed dates are nulled out
1427: -- bug#3675608 added new param p_target_doctype to check if recurring del end event
1428: -- matches target_doctype.
1429:
1430: FUNCTION copy_response_allowed (p_delrec IN okc_deliverables%ROWTYPE
1431: ,p_target_response_doctype IN VARCHAR2
1432: ,p_target_doctype IN VARCHAR2
1433: ) RETURN VARCHAR2
1434: IS
1631: even if the dates are null.
1632: */
1633: ---bug#3594008 redid copy allowed to handle recurring contractual deliverables
1634: -- where fixed dates are nulled out
1635: FUNCTION copy_allowed (p_delrec IN okc_deliverables%ROWTYPE,
1636: p_target_doc_type IN VARCHAR2,
1637: p_target_contractual_doctype IN VARCHAR2
1638: ) RETURN VARCHAR2
1639: IS
1908: )
1909: IS
1910: CURSOR del_cur IS
1911: SELECT *
1912: FROM OKC_DELIVERABLES
1913: WHERE business_document_id = p_source_doc_id
1914: AND business_document_version = -99
1915: AND business_document_type = p_source_doc_type
1916: AND NVL(amendment_operation,'NONE')<> 'DELETED'
1949: SELECT pf.person_id contact_id
1950: FROM per_all_workforce_v pf,fnd_user fu
1951: WHERE fu.user_id = fnd_global.user_id
1952: AND pf.person_id = fu.employee_id;
1953: l_rep_dflt_int_contact_id okc_deliverables.INTERNAL_PARTY_CONTACT_ID%TYPE;
1954: --End of ER Code Modifications
1955:
1956: BEGIN
1957: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2753: delNewTab(i).deliverable_status := 'INACTIVE';
2754: END IF;
2755: END LOOP;
2756: /*FORALL i IN delNewTab.FIRST..delNewTab.LAST
2757: INSERT INTO okc_deliverables VALUES delNewTab(i);*/
2758: FOR i IN delNewTab.FIRST..delNewTab.LAST LOOP
2759: INSERT INTO okc_deliverables
2760: (DELIVERABLE_ID,
2761: BUSINESS_DOCUMENT_TYPE ,
2755: END LOOP;
2756: /*FORALL i IN delNewTab.FIRST..delNewTab.LAST
2757: INSERT INTO okc_deliverables VALUES delNewTab(i);*/
2758: FOR i IN delNewTab.FIRST..delNewTab.LAST LOOP
2759: INSERT INTO okc_deliverables
2760: (DELIVERABLE_ID,
2761: BUSINESS_DOCUMENT_TYPE ,
2762: BUSINESS_DOCUMENT_ID ,
2763: BUSINESS_DOCUMENT_NUMBER ,
3063: IS
3064:
3065: CURSOR del_cur IS
3066: SELECT *
3067: FROM okc_deliverables
3068: WHERE business_document_id = p_current_docid
3069: AND business_document_version = p_current_doc_version
3070: AND business_document_type = p_current_doctype
3071: AND NVL(amendment_operation,'NONE')<> 'DELETED'
3075:
3076:
3077: CURSOR del_ins_cur(x NUMBER) IS
3078: SELECT *
3079: FROM okc_deliverables a
3080: WHERE a.business_document_id = p_current_docid
3081: AND a.business_document_type = p_current_doctype
3082: AND a.business_document_version <> -99
3083: AND a.original_deliverable_id = x
3092: delHistTab delHistTabType;
3093:
3094: CURSOR event_date_cursor(X NUMBER) IS
3095: select start_event_date, end_event_date
3096: from okc_deliverables
3097: where deliverable_id = X;
3098: event_date_rec event_date_cursor%ROWTYPE;
3099:
3100: l_api_name CONSTANT VARCHAR2(30) :='sync_deliverables';
3455: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
3456: ,'Def Id'||delRecTab(i).deliverable_id);
3457: END IF;
3458:
3459: Update okc_deliverables set start_event_date = event_date_rec.start_event_date,
3460: end_event_date = event_date_rec.end_event_date
3461: where deliverable_id = delRecTab(i).deliverable_id;
3462:
3463:
3679: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3680: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'outside def cursor loop');
3681: END IF;
3682:
3683: --BULK INSERT into okc_deliverables the new version of deliverables.
3684: IF delNewTab.COUNT <> 0 THEN
3685: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3686: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Insert new version records');
3687: END IF;
3733: END LOOP;
3734: -- insert records code changed for 8i compatability bug#3307941
3735: /* commented for 8i compatability bug#330794 major code change
3736: FORALL j IN delNewTab.FIRST..delNewTab.LAST
3737: INSERT INTO okc_deliverables VALUES delNewTab(j);*/
3738:
3739: --------------------------------------------
3740:
3741: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3741: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3742: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Before insert');
3743: END IF;
3744: FOR i IN delNewTab.FIRST..delNewTab.LAST LOOP
3745: INSERT INTO okc_deliverables
3746: (DELIVERABLE_ID,
3747: BUSINESS_DOCUMENT_TYPE ,
3748: BUSINESS_DOCUMENT_ID ,
3749: BUSINESS_DOCUMENT_NUMBER ,
4114: IS
4115:
4116: CURSOR del_cur IS
4117: SELECT *
4118: FROM okc_deliverables
4119: WHERE business_document_id = p_doc_id
4120: AND business_document_version = -99
4121: AND business_document_type = p_doc_type
4122: AND manage_yn = 'N';
4266: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
4267: ,'100: Amendment_operation_summary_code is null hard delete this deliverable'||
4268: to_char(delRecTab(i).deliverable_id));
4269: END IF;
4270: delete from okc_deliverables where deliverable_id = delRecTab(i).deliverable_id;*/
4271:
4272:
4273: j:=j+1;
4274: q:=q+1;
4291: END LOOP;-- delRecTab(i)
4292: END IF; --delRecTab.COUNT <> 0
4293:
4294: /***
4295: BULK INSERT into okc_deliverables the new version of deliverables.
4296: ***/
4297: IF delNewTab.COUNT <> 0 THEN
4298: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4299: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Insert new version records');
4313: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4314: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Before insert');
4315: END IF;
4316: FOR i IN delNewTab.FIRST..delNewTab.LAST LOOP
4317: INSERT INTO okc_deliverables
4318: (DELIVERABLE_ID,
4319: BUSINESS_DOCUMENT_TYPE ,
4320: BUSINESS_DOCUMENT_ID ,
4321: BUSINESS_DOCUMENT_NUMBER ,
4496:
4497: -- insert records code changed for 8i compatability bug#3307941
4498: /* commented for 8i compatability bug#330794 major code change
4499: FORALL j IN delNewTab.FIRST..delNewTab.LAST
4500: INSERT INTO okc_deliverables VALUES delNewTab(j);*/
4501:
4502: FOR j IN delIdTab.FIRST..delIdTab.LAST LOOP
4503: IF attachment_exists(p_entity_name => G_ENTITY_NAME
4504: ,p_pk1_value => delIdTab(j).orig_del_id) THEN
4531: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
4532: ,'100: Flush Amendment operation:'
4533: ||to_char(del_rec.deliverable_id));
4534: END IF;
4535: UPDATE okc_deliverables SET amendment_operation = null,
4536: --amendment_notes = null,
4537: --last_amendment_date = null,
4538: last_updated_by= Fnd_Global.User_Id,
4539: last_update_date = sysdate,
4624: CURSOR def_cur IS
4625: SELECT deliverable_id
4626: ,amendment_operation
4627: ,summary_amend_operation_code
4628: FROM okc_deliverables
4629: WHERE business_document_id = p_doc_id
4630: AND business_document_version = -99
4631: AND business_document_type = p_doc_type
4632: AND manage_yn = 'N';
4660: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,
4661: '100: deleted deliverable: '||to_char(def_rec.deliverable_id));
4662: END IF;
4663: -- delete deleverable definitions which are removed from approved business doc
4664: delete from okc_deliverables
4665: where deliverable_id = def_rec.deliverable_id;
4666: ELSE
4667: -- clear all amendment attributes but
4668: -- don't clear last_amendment_date this is needed for PO change History
4666: ELSE
4667: -- clear all amendment attributes but
4668: -- don't clear last_amendment_date this is needed for PO change History
4669: -- to enable or disable deliverables link
4670: UPDATE okc_deliverables SET amendment_operation = null,
4671: summary_amend_operation_code = null,
4672: amendment_notes = null,
4673: last_updated_by= Fnd_Global.User_Id,
4674: last_update_date = sysdate,
4689: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,
4690: '100: deleted deliverable: '||to_char(def_rec.deliverable_id));
4691: END IF;
4692: -- delete deleverable definitions which are removed from approved business doc
4693: delete from okc_deliverables
4694: where deliverable_id = def_rec.deliverable_id;
4695: ELSE
4696: -- clear only amendment_operation
4697: UPDATE okc_deliverables SET amendment_operation = null,
4693: delete from okc_deliverables
4694: where deliverable_id = def_rec.deliverable_id;
4695: ELSE
4696: -- clear only amendment_operation
4697: UPDATE okc_deliverables SET amendment_operation = null,
4698: last_updated_by= Fnd_Global.User_Id,
4699: last_update_date = sysdate,
4700: last_update_login=Fnd_Global.Login_Id
4701: WHERE deliverable_id = def_rec.deliverable_id;
4751: IS
4752: l_api_name CONSTANT VARCHAR2(30) :='disable_deliverable';
4753: CURSOR del_cur IS
4754: SELECT deliverable_id
4755: FROM okc_deliverables
4756: WHERE business_document_id = p_doc_id
4757: AND business_document_version =p_doc_version
4758: AND business_document_type = p_doc_type
4759: AND manage_yn = 'Y';
4769: OPEN del_cur;
4770: FETCH del_cur BULK COLLECT INTO delIdTab;
4771: IF delIdTab.COUNT <> 0 THEN
4772: FORALL i IN delIdTab.FIRST..delIdTab.LAST
4773: UPDATE okc_deliverables SET manage_yn = 'N',
4774: last_updated_by= Fnd_Global.User_Id,
4775: last_update_date = sysdate,
4776: last_update_login=Fnd_Global.Login_Id
4777: WHERE deliverable_id = delIdTab(i);
4937: IS
4938: l_api_name CONSTANT VARCHAR2(30) :='change_deliverable_status';
4939: CURSOR del_activate_cur IS
4940: SELECT deliverable_id
4941: FROM okc_deliverables
4942: WHERE business_document_id = p_doc_id
4943: AND business_document_version =p_doc_version
4944: AND business_document_type = p_doc_type
4945: AND deliverable_status = p_current_status
4946: AND actual_due_date is not null;
4947:
4948: /* CURSOR del_cancel_cur IS
4949: SELECT deliverable_id
4950: FROM okc_deliverables
4951: WHERE business_document_id = p_doc_id
4952: AND business_document_version =p_doc_version
4953: AND business_document_type = p_doc_type
4954: AND NVL(relative_st_date_event_id,0) NOT IN (
4994: delStsTab(j).last_update_date := sysdate;
4995: delStsTab(j).last_update_login := Fnd_Global.Login_Id;
4996: END LOOP;
4997:
4998: --Bulk update of status in okc_deliverables
4999: FORALL i IN delIdTab.FIRST..delIdTab.LAST
5000: UPDATE okc_deliverables
5001: SET
5002: deliverable_status = p_new_status,
4996: END LOOP;
4997:
4998: --Bulk update of status in okc_deliverables
4999: FORALL i IN delIdTab.FIRST..delIdTab.LAST
5000: UPDATE okc_deliverables
5001: SET
5002: deliverable_status = p_new_status,
5003: manage_yn = p_manage_yn,
5004: last_updated_by= Fnd_Global.User_Id,
5082:
5083: IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
5084: -- BULK UPDATE of status
5085: FORALL i IN delIdTab.FIRST..delIdTab.LAST
5086: UPDATE okc_deliverables
5087: SET
5088: deliverable_status = p_new_status,
5089: manage_yn = 'N',
5090: last_updated_by= Fnd_Global.User_Id,
5271: responsible_party,
5272: external_party_contact_id,
5273: internal_party_contact_id,
5274: requester_id
5275: FROM okc_deliverables
5276: WHERE manage_yn = 'Y'
5277: AND disable_notifications_yn = 'N'
5278: AND notify_overdue_yn = 'Y'
5279: AND overdue_notification_id is null
5289:
5290: TYPE del_cur_tbl IS TABLE OF del_cur%ROWTYPE;
5291: selected_dels del_cur_tbl;
5292:
5293: TYPE OverdueNtfIdList IS TABLE OF okc_deliverables.overdue_notification_id%TYPE NOT NULL
5294: INDEX BY PLS_INTEGER;
5295: TYPE DeliverableIdList IS TABLE OF okc_deliverables.deliverable_id%TYPE NOT NULL
5296: INDEX BY PLS_INTEGER;
5297: overdue_ntf_ids OverdueNtfIdList;
5291: selected_dels del_cur_tbl;
5292:
5293: TYPE OverdueNtfIdList IS TABLE OF okc_deliverables.overdue_notification_id%TYPE NOT NULL
5294: INDEX BY PLS_INTEGER;
5295: TYPE DeliverableIdList IS TABLE OF okc_deliverables.deliverable_id%TYPE NOT NULL
5296: INDEX BY PLS_INTEGER;
5297: overdue_ntf_ids OverdueNtfIdList;
5298: deliverable_ids DeliverableIdList;
5299:
5390:
5391: IF deliverable_ids.COUNT > 0 THEN
5392: FORALL i IN deliverable_ids.FIRST..NVL(deliverable_ids.LAST, -1)
5393:
5394: UPDATE okc_deliverables
5395: SET overdue_notification_id = overdue_ntf_ids(i),
5396: last_update_date = sysdate,
5397: last_updated_by = FND_GLOBAL.User_id,
5398: last_update_login =Fnd_Global.Login_Id
5542: x_msg_data OUT NOCOPY VARCHAR2)
5543: IS
5544: CURSOR del_cur IS
5545: SELECT *
5546: FROM okc_deliverables
5547: WHERE manage_yn = 'Y'
5548: AND disable_notifications_yn = 'N'
5549: AND notify_prior_due_date_yn = 'Y'
5550: AND prior_notification_id is null
5753: END IF;
5754: IF delNtfTab.COUNT > 0 THEN
5755: FOR i IN delNtfTab.FIRST..delNtfTab.LAST LOOP
5756: /** commented as this is not supported by 8i PL/SQL Bug#3307941
5757: UPDATE okc_deliverables SET ROW = delNtfTab(i)
5758: where deliverable_id = l_deliverable_id;
5759: l_deliverable_id := delNtfTab(i).deliverable_id;*/
5760: UPDATE okc_deliverables
5761: SET prior_notification_id = delNtfTab(i).prior_notification_id,
5756: /** commented as this is not supported by 8i PL/SQL Bug#3307941
5757: UPDATE okc_deliverables SET ROW = delNtfTab(i)
5758: where deliverable_id = l_deliverable_id;
5759: l_deliverable_id := delNtfTab(i).deliverable_id;*/
5760: UPDATE okc_deliverables
5761: SET prior_notification_id = delNtfTab(i).prior_notification_id,
5762: last_update_date = sysdate,
5763: last_updated_by = FND_GLOBAL.User_id,
5764: last_update_login =Fnd_Global.Login_Id
5902: IS
5903: l_api_name CONSTANT VARCHAR2(30) :='esc_del_notifier';
5904: CURSOR del_cur IS
5905: SELECT *
5906: FROM okc_deliverables
5907: WHERE manage_yn = 'Y'
5908: AND disable_notifications_yn = 'N'
5909: AND notify_escalation_yn = 'Y'
5910: AND escalation_assignee is not null
6109: END IF;
6110: IF delNtfTab.COUNT > 0 THEN
6111: FOR i IN delNtfTab.FIRST..delNtfTab.LAST LOOP
6112: /** commented as this is not supported by 8i PL/SQL Bug#3307941
6113: UPDATE okc_deliverables SET ROW = delNtfTab(i)
6114: where deliverable_id = l_deliverable_id;
6115: l_deliverable_id := delNtfTab(i).deliverable_id;*/
6116: UPDATE okc_deliverables
6117: SET escalation_notification_id = delNtfTab(i).escalation_notification_id,
6112: /** commented as this is not supported by 8i PL/SQL Bug#3307941
6113: UPDATE okc_deliverables SET ROW = delNtfTab(i)
6114: where deliverable_id = l_deliverable_id;
6115: l_deliverable_id := delNtfTab(i).deliverable_id;*/
6116: UPDATE okc_deliverables
6117: SET escalation_notification_id = delNtfTab(i).escalation_notification_id,
6118: last_update_date = sysdate,
6119: last_updated_by = FND_GLOBAL.User_id,
6120: last_update_login =Fnd_Global.Login_Id
6341: x_return_status OUT NOCOPY VARCHAR2)
6342: IS
6343: CURSOR del_cur IS
6344: SELECT deliverable_id
6345: FROM okc_deliverables
6346: WHERE business_document_id = p_doc_id
6347: AND business_document_type = p_doc_type;
6348:
6349: CURSOR del_version_cur IS
6347: AND business_document_type = p_doc_type;
6348:
6349: CURSOR del_version_cur IS
6350: SELECT deliverable_id
6351: FROM okc_deliverables
6352: WHERE business_document_id = p_doc_id
6353: AND business_document_type = p_doc_type
6354: AND business_document_version = p_doc_version;
6355:
6385: ,p_pk1_value => delIdTab(i)
6386: ,x_result => l_result);
6387: IF l_result = 'S' THEN
6388: -- delete deliverables
6389: DELETE FROM okc_deliverables
6390: WHERE deliverable_id = delIdTab(i);
6391: END IF;
6392: END LOOP;
6393: END IF; -- delIdTab.COUNT <> 0
6472: x_return_status OUT NOCOPY VARCHAR2)
6473: IS
6474: CURSOR del_cur IS
6475: SELECT deliverable_id
6476: FROM okc_deliverables
6477: WHERE business_document_id = p_working_template_id
6478: AND business_document_type = 'TEMPLATE';
6479: TYPE delIdRecTabType IS TABLE OF NUMBER;
6480: delIdTab delIdRecTabType;
6488: OPEN del_cur;
6489: FETCH del_cur BULK COLLECT INTO delIdTab;
6490: IF delIdTab.COUNT <> 0 THEN
6491: FORALL j IN delIdTab.FIRST..delIdTab.LAST
6492: UPDATE okc_deliverables
6493: SET business_document_id = p_base_template_id,
6494: last_updated_by= Fnd_Global.User_Id,
6495: last_update_date = sysdate,
6496: last_update_login=Fnd_Global.Login_Id
6576: -- updated cursor for bug#4069955
6577: CURSOR del_cur IS
6578: SELECT NVL(MAX(del.last_amendment_date),MAX(del.creation_date))
6579: FROM
6580: okc_deliverables del
6581: ,okc_deliverable_types_b delType
6582: WHERE del.business_document_id = p_busdoc_id
6583: AND del.business_document_type = p_busdoc_type
6584: AND del.recurring_del_parent_id is null
6589: AND delType.internal_flag = 'N';
6590:
6591: /*CURSOR create_date_cur IS
6592: SELECT MAX(last_amendment_date)
6593: FROM okc_deliverables
6594: WHERE business_document_id = p_busdoc_id
6595: AND business_document_type = p_busdoc_type
6596: AND business_document_version = -99
6597: AND deliverable_type not like '%INTERNAL%';*/
7137: * x_qa_status OKC_API.G_RET_STS_SUCCESS if no error/warning is found
7138: * OKC_API.G_RET_STS_ERROR if at least one error/warning is found
7139: */
7140: PROCEDURE check_notifications (
7141: del_rec IN OKC_DELIVERABLES%ROWTYPE,
7142: p_severity IN VARCHAR2,
7143: px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
7144: x_return_status OUT NOCOPY VARCHAR2
7145: ) IS
7263: * x_qa_status OKC_API.G_RET_STS_SUCCESS if no error/warning is found
7264: * OKC_API.G_RET_STS_ERROR if at least one error/warning is found
7265: */
7266: PROCEDURE check_internal_contacts (
7267: del_rec IN OKC_DELIVERABLES%ROWTYPE,
7268: p_severity IN VARCHAR2,
7269: px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
7270: x_return_status OUT NOCOPY VARCHAR2
7271: ) IS
7441: * x_qa_status OKC_API.G_RET_STS_SUCCESS if no error/warning is found
7442: * OKC_API.G_RET_STS_ERROR if at least one error/warning is found
7443: */
7444: PROCEDURE check_internal_contacts_valid (
7445: del_rec IN OKC_DELIVERABLES%ROWTYPE,
7446: p_severity IN VARCHAR2,
7447: px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
7448: x_return_status OUT NOCOPY VARCHAR2
7449: ) IS
7606: * x_qa_status OKC_API.G_RET_STS_SUCCESS if no error/warning is found
7607: * OKC_API.G_RET_STS_ERROR if at least one error/warning is found
7608: */
7609: PROCEDURE check_external_contacts (
7610: del_rec IN OKC_DELIVERABLES%ROWTYPE,
7611: p_severity IN VARCHAR2,
7612: px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
7613: x_return_status OUT NOCOPY VARCHAR2
7614: ) IS
7746: * x_qa_status OKC_API.G_RET_STS_SUCCESS if no error/warning is found
7747: * OKC_API.G_RET_STS_ERROR if at least one error/warning is found
7748: */
7749: PROCEDURE check_due_dates (
7750: del_rec IN OKC_DELIVERABLES%ROWTYPE,
7751: p_severity IN VARCHAR2,
7752: p_bus_doc_date_events_tbl IN OKC_TERMS_QA_GRP.BUSDOCDATES_TBL_TYPE,
7753: px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
7754: x_return_status OUT NOCOPY VARCHAR2
8115: * x_qa_status OKC_API.G_RET_STS_SUCCESS if no error/warning is found
8116: * OKC_API.G_RET_STS_ERROR if at least one error/warning is found
8117: */
8118: PROCEDURE check_amendments (
8119: del_rec IN OKC_DELIVERABLES%ROWTYPE,
8120: p_severity IN VARCHAR2,
8121: px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
8122: x_return_status OUT NOCOPY VARCHAR2
8123: ) IS
8343: ,p_del_type IN VARCHAR2
8344: ,p_internal_org IN VARCHAR2) IS
8345: select 'x'
8346: from
8347: okc_deliverables del
8348: where
8349: del.business_document_type = p_bus_doc_type
8350: and del.business_document_id = p_bus_doc_id
8351: and del.deliverable_type = p_del_type
8359: ,p_del_type IN VARCHAR2
8360: ,p_internal_org IN VARCHAR2) IS
8361: select 'x'
8362: from
8363: okc_deliverables del
8364: where
8365: del.business_document_type = p_bus_doc_type
8366: and del.business_document_id = p_bus_doc_id
8367: and del.deliverable_type = p_del_type
8373: ,p_bus_doc_id IN NUMBER
8374: ,p_del_type IN VARCHAR2) IS
8375: select 'x'
8376: from
8377: okc_deliverables del
8378: where
8379: del.business_document_type = p_bus_doc_type
8380: and del.business_document_id = p_bus_doc_id
8381: and del.deliverable_type = p_del_type;
8618: * x_qa_status OKC_API.G_RET_STS_SUCCESS if no error/warning is found
8619: * OKC_API.G_RET_STS_ERROR if at least one error/warning is found
8620: ***********************/
8621: PROCEDURE check_external_party_exists (
8622: del_rec IN OKC_DELIVERABLES%ROWTYPE,
8623: p_severity IN VARCHAR2,
8624: px_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_PVT.qa_result_tbl_type,
8625: x_return_status OUT NOCOPY VARCHAR2
8626: ) IS
8757:
8758: l_start PLS_INTEGER := 0;
8759: l_end PLS_INTEGER := 0;
8760: del_cur del_cur_type;
8761: del_rec okc_deliverables%ROWTYPE;
8762: l_qa_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
8763:
8764: -- l_due_date_msg VARCHAR2(2000) := '';
8765: l_check_int_contact VARCHAR2(19) := 'CHECK_BUYER_CONTACT';
9059: --End ExternalPartyExists cursors--
9060: /* IF(p_mode = G_AMEND_QA) THEN
9061: OPEN del_cur FOR
9062: select *
9063: from okc_deliverables
9064: where business_document_type = p_doc_type
9065: and business_document_id = p_doc_id
9066: and business_document_version = -99
9067: and deliverable_type in (select deltypes.deliverable_type_code from okc_bus_doc_types_b doctypes,
9084: --Repository Change: changed cursor to look at okc_deliverable_types_b
9085: -- updated cursor for bug#4069955
9086: OPEN del_cur FOR
9087: select *
9088: from okc_deliverables
9089: where business_document_type = p_doc_type
9090: and business_document_id = p_doc_id
9091: and business_document_version = -99
9092: and (amendment_operation is NULL OR amendment_operation <> 'DELETED')
9555: Add as part of fix for bug#3458149
9556: Checks Deliverable_Status_History table and returns 'Y' if the Status of a Deliverable was NOT changed by
9557: user since the Deliverable was first resolved, else returns 'N'
9558: */
9559: Function delStatusUnchanged(p_del_ID IN OKC_DELIVERABLES.deliverable_id%TYPE) RETURN VARCHAR2 IS
9560: l_open_count NUMBER ;
9561: l_inactive_count NUMBER ;
9562: l_others_count NUMBER ;
9563:
9682: ,p_pk1_value => l_del_id
9683: ,x_result => l_result);
9684: IF l_result = 'S' THEN
9685: -- delete deliverables
9686: DELETE FROM okc_deliverables
9687: WHERE deliverable_id = l_del_id;
9688: END IF;
9689: x_return_status := l_return_status;
9690: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
9741: SELECT
9742: del.deliverable_type
9743: ,delType.internal_flag
9744: FROM
9745: okc_deliverables del
9746: ,okc_deliverable_types_b delType
9747: WHERE del.business_document_id = p_docid
9748: AND del.business_document_type = p_doctype
9749: AND del.business_document_version = -99
9753:
9754: l_del_rec del_cur%ROWTYPE;
9755: l_api_name CONSTANT VARCHAR2(30) := 'deliverables_exist';
9756: l_exists VARCHAR2(60):= 'NONE';
9757: l_contractual okc_deliverables.deliverable_type%TYPE;
9758: l_internal okc_deliverables.deliverable_type%TYPE;
9759: l_sourcing okc_deliverables.deliverable_type%TYPE;
9760:
9761: BEGIN
9754: l_del_rec del_cur%ROWTYPE;
9755: l_api_name CONSTANT VARCHAR2(30) := 'deliverables_exist';
9756: l_exists VARCHAR2(60):= 'NONE';
9757: l_contractual okc_deliverables.deliverable_type%TYPE;
9758: l_internal okc_deliverables.deliverable_type%TYPE;
9759: l_sourcing okc_deliverables.deliverable_type%TYPE;
9760:
9761: BEGIN
9762:
9755: l_api_name CONSTANT VARCHAR2(30) := 'deliverables_exist';
9756: l_exists VARCHAR2(60):= 'NONE';
9757: l_contractual okc_deliverables.deliverable_type%TYPE;
9758: l_internal okc_deliverables.deliverable_type%TYPE;
9759: l_sourcing okc_deliverables.deliverable_type%TYPE;
9760:
9761: BEGIN
9762:
9763: -- Initialize API return status to success
9853: ,p_pk1_value => p_del_id
9854: ,x_result => l_result);
9855: IF l_result = 'S' THEN
9856: -- delete deliverables
9857: DELETE FROM okc_deliverables
9858: WHERE deliverable_id = p_del_id;
9859: END IF;
9860: x_return_status := l_result;
9861: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
9917: IS
9918: -- bug#4075168 changed the select "and original_deliverable_id IN ( select original_deliverable_id"
9919: CURSOR del_cur IS
9920: select deliverable_id
9921: from okc_deliverables
9922: where business_document_id = p_doc_id
9923: and business_document_type = p_doc_type
9924: and business_document_version = -99
9925: and original_deliverable_id IN (
9923: and business_document_type = p_doc_type
9924: and business_document_version = -99
9925: and original_deliverable_id IN (
9926: select original_deliverable_id
9927: from okc_deliverables
9928: where business_document_type = 'TEMPLATE');
9929:
9930: TYPE delIdTabType IS TABLE OF NUMBER;
9931: delIdTab delIdTabType;
9953: END IF;
9954: END LOOP;
9955: IF l_result = 'S' THEN
9956: FORALL i IN delIdTab.FIRST..delIdTab.LAST
9957: DELETE FROM okc_deliverables
9958: WHERE deliverable_id = delIdTab(i);
9959: END IF;
9960: END IF;-- delIdTab.COUNT
9961: IF del_cur %ISOPEN THEN
10027: del.amendment_operation
10028: ,del.deliverable_type
10029: ,delType.internal_flag
10030: FROM
10031: okc_deliverables del
10032: ,okc_deliverable_types_b delType
10033: WHERE del.business_document_id = p_docid
10034: AND del.business_document_type = p_doctype
10035: AND del.business_document_version = -99
10039: AND delType.internal_flag = 'N';
10040:
10041: l_del_rec del_cur%ROWTYPE;
10042: l_amended VARCHAR2(30):= 'NONE';
10043: l_contractual okc_deliverables.deliverable_type%TYPE;
10044: l_internal okc_deliverables.deliverable_type%TYPE;
10045: l_sourcing okc_deliverables.deliverable_type%TYPE;
10046: l_api_name CONSTANT VARCHAR2(30) := 'deliverables_amended';
10047:
10040:
10041: l_del_rec del_cur%ROWTYPE;
10042: l_amended VARCHAR2(30):= 'NONE';
10043: l_contractual okc_deliverables.deliverable_type%TYPE;
10044: l_internal okc_deliverables.deliverable_type%TYPE;
10045: l_sourcing okc_deliverables.deliverable_type%TYPE;
10046: l_api_name CONSTANT VARCHAR2(30) := 'deliverables_amended';
10047:
10048: BEGIN
10041: l_del_rec del_cur%ROWTYPE;
10042: l_amended VARCHAR2(30):= 'NONE';
10043: l_contractual okc_deliverables.deliverable_type%TYPE;
10044: l_internal okc_deliverables.deliverable_type%TYPE;
10045: l_sourcing okc_deliverables.deliverable_type%TYPE;
10046: l_api_name CONSTANT VARCHAR2(30) := 'deliverables_amended';
10047:
10048: BEGIN
10049:
10284: x_return_status OUT NOCOPY VARCHAR2)
10285: IS
10286: CURSOR del_cur IS
10287: SELECT *
10288: FROM OKC_DELIVERABLES
10289: WHERE business_document_id = p_source_doc_id
10290: AND business_document_type = p_source_doc_type
10291: AND recurring_del_parent_id is null;
10292: del_rec del_cur%ROWTYPE;
10292: del_rec del_cur%ROWTYPE;
10293:
10294: CURSOR del_ins_cur (X number) IS
10295: SELECT *
10296: FROM okc_deliverables
10297: WHERE business_document_id = p_source_doc_id
10298: AND business_document_type = p_source_doc_type
10299: AND recurring_del_parent_id = X;
10300: del_ins_rec del_ins_cur%ROWTYPE;
10597: FOR i IN delNewTab.FIRST..delNewTab.LAST LOOP
10598: IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
10599: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: New Deliverable Id :'||to_char(delNewTab(j).deliverable_id));
10600: END IF;
10601: INSERT INTO okc_deliverables
10602: (DELIVERABLE_ID,
10603: BUSINESS_DOCUMENT_TYPE ,
10604: BUSINESS_DOCUMENT_ID ,
10605: BUSINESS_DOCUMENT_NUMBER ,
11048: SELECT
11049: del.deliverable_type deliverable_type
11050: ,del.responsible_party responsible_party
11051: from
11052: okc_deliverables del
11053: ,okc_deliverable_types_b delType
11054: where del.business_document_type = p_bus_doc_type
11055: and del.business_document_id = p_bus_doc_id
11056: and del.business_document_version = -99
11241: x_return_status OUT NOCOPY VARCHAR2) IS
11242:
11243: CURSOR del_cur IS
11244: SELECT *
11245: FROM OKC_DELIVERABLES
11246: WHERE business_document_id = p_source_doc_id
11247: AND business_document_version = -99
11248: AND business_document_type = p_source_doc_type
11249: AND NVL(amendment_operation,'NONE')<> 'DELETED'
11409: END LOOP;
11410: -- bug#4083525 CopyDelForTemplateRevision
11411: IF delNewTab.COUNT <> 0 THEN
11412: FOR i IN delNewTab.FIRST..delNewTab.LAST LOOP
11413: INSERT INTO okc_deliverables
11414: (DELIVERABLE_ID,
11415: BUSINESS_DOCUMENT_TYPE ,
11416: BUSINESS_DOCUMENT_ID ,
11417: BUSINESS_DOCUMENT_NUMBER ,
11717: -- the current version of a business document
11718: CURSOR cur_vers_del_csr IS
11719: SELECT deliverable_id,
11720: original_deliverable_id
11721: FROM okc_deliverables
11722: WHERE business_document_id = p_bus_doc_id
11723: AND business_document_version = -99
11724: AND business_document_type = p_bus_doc_type;
11725:
11811: pay_hold_prior_due_date_value,
11812: pay_hold_prior_due_date_uom,
11813: pay_hold_prior_due_date_yn,
11814: pay_hold_overdue_yn
11815: FROM okc_deliverables
11816: WHERE business_document_id = p_bus_doc_id
11817: AND business_document_version = p_bus_doc_version - 1
11818: AND business_document_type = p_bus_doc_type
11819: AND recurring_del_parent_id IS NULL
11829: l_api_name CONSTANT VARCHAR2(30) :='deleteDeliverables';
11830: l_api_version CONSTANT NUMBER := 1.0;
11831: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
11832:
11833: l_deliverable_id okc_deliverables.deliverable_id%TYPE;
11834:
11835:
11836: BEGIN
11837:
12006: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
12007: 'Inserting a Deliverable with deliverable id ' || l_deliverable_id);
12008: END IF;
12009:
12010: -- Insert a row into okc_deliverables with id of the deleted deliverable
12011: -- and with other values with its corresponding previous version deliverable
12012: INSERT INTO okc_deliverables (
12013: deliverable_id,
12014: business_document_type,
12008: END IF;
12009:
12010: -- Insert a row into okc_deliverables with id of the deleted deliverable
12011: -- and with other values with its corresponding previous version deliverable
12012: INSERT INTO okc_deliverables (
12013: deliverable_id,
12014: business_document_type,
12015: business_document_id,
12016: business_document_number,