[Home] [Help]
36: FETCH c_product_elig INTO l_dummy;
37: CLOSE c_product_elig;
38:
39: IF l_dummy IS NULL THEN
40: ozf_utility_pvt.error_message('OZF_ACCRUAL_NO_PROD');
41: x_return_status := fnd_api.g_ret_sts_error;
42: END IF;
43: END check_product_elig_exists;
44:
101:
102: IF l_par_curr_code = p_child_curr THEN
103: l_coverted_orig_budget := p_original_budget;
104: ELSE
105: ozf_utility_pvt.convert_currency(
106: x_return_status=> x_return_status,
107: p_from_currency=> p_child_curr,
108: p_to_currency=> l_par_curr_code,
109: p_from_amount=> p_original_budget,
398: LOOP
399: IF l_sub_rec.currency_code = p_parent_currency THEN
400: l_coverted_orig_budget := l_sub_rec.original_budget;
401: ELSE
402: ozf_utility_pvt.convert_currency(
403: x_return_status=> x_return_status,
404: p_from_currency=> l_sub_rec.currency_code,
405: p_to_currency=> p_parent_currency,
406: p_from_amount=> l_sub_rec.original_budget,
413: NVL(l_org_budget, 0);
414:
415: IF (l_par_total_budget < NVL(l_org_budget, 0)) THEN
416: IF l_parent_fund_type = 'QUOTA' THEN
417: ozf_utility_pvt.error_message(
418: 'OZF_PAR_QUOTA_LESS_THAN_CHILD',
419: 'FUND_NAME',
420: l_sub_rec.short_name);
421: ELSE
418: 'OZF_PAR_QUOTA_LESS_THAN_CHILD',
419: 'FUND_NAME',
420: l_sub_rec.short_name);
421: ELSE
422: ozf_utility_pvt.error_message(
423: 'OZF_PAR_BUDGET_LESS_THAN_CHILD',
424: 'FUND_NAME',
425: l_sub_rec.short_name);
426: END IF;
465: LOOP
466: IF p_start_date > l_sub_rec.start_date THEN
467: x_return_status := fnd_api.g_ret_sts_error;
468: IF l_sub_rec.fund_type = 'QUOTA' THEN
469: ozf_utility_pvt.error_message('OZF_TP_START_AFT_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
470: ELSE
471: ozf_utility_pvt.error_message('OZF_FUND_START_AFT_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
472: END IF;
473: ELSIF p_start_date > l_sub_rec.end_date THEN
467: x_return_status := fnd_api.g_ret_sts_error;
468: IF l_sub_rec.fund_type = 'QUOTA' THEN
469: ozf_utility_pvt.error_message('OZF_TP_START_AFT_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
470: ELSE
471: ozf_utility_pvt.error_message('OZF_FUND_START_AFT_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
472: END IF;
473: ELSIF p_start_date > l_sub_rec.end_date THEN
474: x_return_status := fnd_api.g_ret_sts_error;
475: IF l_sub_rec.fund_type = 'QUOTA' THEN
472: END IF;
473: ELSIF p_start_date > l_sub_rec.end_date THEN
474: x_return_status := fnd_api.g_ret_sts_error;
475: IF l_sub_rec.fund_type = 'QUOTA' THEN
476: ozf_utility_pvt.error_message('OZF_TP_START_AFT_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
477: ELSE
478: ozf_utility_pvt.error_message('OZF_FUND_START_AFT_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
479: END IF;
480: END IF;
474: x_return_status := fnd_api.g_ret_sts_error;
475: IF l_sub_rec.fund_type = 'QUOTA' THEN
476: ozf_utility_pvt.error_message('OZF_TP_START_AFT_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
477: ELSE
478: ozf_utility_pvt.error_message('OZF_FUND_START_AFT_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
479: END IF;
480: END IF;
481:
482: IF p_end_date < l_sub_rec.end_date THEN
481:
482: IF p_end_date < l_sub_rec.end_date THEN
483: x_return_status := fnd_api.g_ret_sts_error;
484: IF l_sub_rec.fund_type = 'QUOTA' THEN
485: ozf_utility_pvt.error_message('OZF_TP_QUOTA_END_BEF_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
486: ELSE
487: ozf_utility_pvt.error_message('OZF_FUND_END_BEF_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
488: END IF;
489: ELSIF p_end_date < l_sub_rec.start_date THEN
483: x_return_status := fnd_api.g_ret_sts_error;
484: IF l_sub_rec.fund_type = 'QUOTA' THEN
485: ozf_utility_pvt.error_message('OZF_TP_QUOTA_END_BEF_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
486: ELSE
487: ozf_utility_pvt.error_message('OZF_FUND_END_BEF_SUB_END', 'FUND_NAME', l_sub_rec.short_name);
488: END IF;
489: ELSIF p_end_date < l_sub_rec.start_date THEN
490: x_return_status := fnd_api.g_ret_sts_error;
491: IF l_sub_rec.fund_type = 'QUOTA' THEN
488: END IF;
489: ELSIF p_end_date < l_sub_rec.start_date THEN
490: x_return_status := fnd_api.g_ret_sts_error;
491: IF l_sub_rec.fund_type = 'QUOTA' THEN
492: ozf_utility_pvt.error_message('OZF_TP_END_BEF_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
493: ELSE
494: ozf_utility_pvt.error_message('OZF_FUND_END_BEF_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
495: END IF;
496: END IF;
490: x_return_status := fnd_api.g_ret_sts_error;
491: IF l_sub_rec.fund_type = 'QUOTA' THEN
492: ozf_utility_pvt.error_message('OZF_TP_END_BEF_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
493: ELSE
494: ozf_utility_pvt.error_message('OZF_FUND_END_BEF_SUB_START', 'FUND_NAME', l_sub_rec.short_name);
495: END IF;
496: END IF;
497: END LOOP;
498: END check_fund_dates_vs_child;
530: LOOP
531: --- fund type of the parent and child fund should be the same
532: IF p_fund_type <> l_sub_rec.fund_type THEN
533: IF l_sub_rec.fund_type = 'QUOTA' THEN
534: ozf_utility_pvt.error_message('OZF_TP_PAR_MISMATCH_CHILD_TYPE', 'FUND_NAME', l_sub_rec.short_name);
535: ELSE
536: ozf_utility_pvt.error_message('OZF_PAR_MISMATCH_CHILD_TYPE', 'FUND_NAME', l_sub_rec.short_name);
537: END IF;
538: x_return_status := fnd_api.g_ret_sts_error;
532: IF p_fund_type <> l_sub_rec.fund_type THEN
533: IF l_sub_rec.fund_type = 'QUOTA' THEN
534: ozf_utility_pvt.error_message('OZF_TP_PAR_MISMATCH_CHILD_TYPE', 'FUND_NAME', l_sub_rec.short_name);
535: ELSE
536: ozf_utility_pvt.error_message('OZF_PAR_MISMATCH_CHILD_TYPE', 'FUND_NAME', l_sub_rec.short_name);
537: END IF;
538: x_return_status := fnd_api.g_ret_sts_error;
539: END IF;
540: END LOOP;
574: LOOP
575: --- fund curr of the parent and child fund should be the same
576: IF p_fund_curr <> l_sub_rec.fund_curr THEN
577: IF l_sub_rec.fund_type = 'QUOTA' THEN
578: ozf_utility_pvt.error_message('OZF_TP_PAR_MISMATCH_CHILD_CURR', 'FUND_NAME', l_sub_rec.short_name);
579: ELSE
580: ozf_utility_pvt.error_message('OZF_PAR_MISMATCH_CHILD_CURR', 'FUND_NAME', l_sub_rec.short_name);
581: END IF;
582: x_return_status := fnd_api.g_ret_sts_error;
576: IF p_fund_curr <> l_sub_rec.fund_curr THEN
577: IF l_sub_rec.fund_type = 'QUOTA' THEN
578: ozf_utility_pvt.error_message('OZF_TP_PAR_MISMATCH_CHILD_CURR', 'FUND_NAME', l_sub_rec.short_name);
579: ELSE
580: ozf_utility_pvt.error_message('OZF_PAR_MISMATCH_CHILD_CURR', 'FUND_NAME', l_sub_rec.short_name);
581: END IF;
582: x_return_status := fnd_api.g_ret_sts_error;
583: END IF;
584: END LOOP;
677: IF p_fund_calendar IS NULL AND p_start_period_name IS NULL AND p_end_period_name IS NULL THEN
678: RETURN;
679: ELSIF p_fund_calendar IS NULL THEN
680: x_return_status := fnd_api.g_ret_sts_error;
681: ozf_utility_pvt.error_message('OZF_FUND_NO_CALENDAR');
682: RETURN;
683: END IF;
684:
685: -- check if p_fund_calendar is valid
688: CLOSE c_fund_calendar;
689:
690: IF l_local IS NULL THEN
691: x_return_status := fnd_api.g_ret_sts_error;
692: ozf_utility_pvt.error_message('OZF_FUND_BAD_CALENDAR');
693: RETURN;
694: END IF;
695:
696: -- check p_start_period_name
707: END IF;
708:
709: IF l_start_start IS NULL THEN
710: x_return_status := fnd_api.g_ret_sts_error;
711: ozf_utility_pvt.error_message('OZF_FUND_BAD_START_PERIOD');
712: RETURN;
713: ELSIF p_start_date < l_start_start
714: OR p_start_date > l_start_end THEN
715: x_return_status := fnd_api.g_ret_sts_error;
713: ELSIF p_start_date < l_start_start
714: OR p_start_date > l_start_end THEN
715: x_return_status := fnd_api.g_ret_sts_error;
716: IF p_fund_type = 'QUOTA' THEN
717: ozf_utility_pvt.error_message('OZF_TP_QUOTA_OUT_START_PERIOD');
718: ELSE
719: ozf_utility_pvt.error_message('OZF_FUND_OUT_START_PERIOD');
720: END IF;
721: RETURN;
715: x_return_status := fnd_api.g_ret_sts_error;
716: IF p_fund_type = 'QUOTA' THEN
717: ozf_utility_pvt.error_message('OZF_TP_QUOTA_OUT_START_PERIOD');
718: ELSE
719: ozf_utility_pvt.error_message('OZF_FUND_OUT_START_PERIOD');
720: END IF;
721: RETURN;
722: END IF;
723: END IF;
735: END IF;
736:
737: IF l_end_end IS NULL THEN
738: x_return_status := fnd_api.g_ret_sts_error;
739: ozf_utility_pvt.error_message('OZF_FUND_BAD_END_PERIOD');
740: RETURN;
741: ELSIF TRUNC(p_end_date) < TRUNC(l_end_start)
742: OR TRUNC(p_end_date) > TRUNC(l_end_end) THEN
743: x_return_status := fnd_api.g_ret_sts_error;
741: ELSIF TRUNC(p_end_date) < TRUNC(l_end_start)
742: OR TRUNC(p_end_date) > TRUNC(l_end_end) THEN
743: x_return_status := fnd_api.g_ret_sts_error;
744: IF p_fund_type = 'QUOTA' THEN
745: ozf_utility_pvt.error_message('OZF_TP_QUOTA_OUT_END_PERIOD');
746: ELSE
747: ozf_utility_pvt.error_message('OZF_FUND_OUT_END_PERIOD');
748: END IF;
749: RETURN;
743: x_return_status := fnd_api.g_ret_sts_error;
744: IF p_fund_type = 'QUOTA' THEN
745: ozf_utility_pvt.error_message('OZF_TP_QUOTA_OUT_END_PERIOD');
746: ELSE
747: ozf_utility_pvt.error_message('OZF_FUND_OUT_END_PERIOD');
748: END IF;
749: RETURN;
750: END IF;
751: END IF;
752:
753: -- compare the start date and the end date
754: IF TRUNC(l_start_start) > TRUNC(l_end_end) THEN
755: x_return_status := fnd_api.g_ret_sts_error;
756: ozf_utility_pvt.error_message('OZF_FUND_BAD_PERIODS');
757: END IF;
758: END check_fund_calendar;
759:
760:
843:
844: BEGIN
845: SAVEPOINT process_offers;
846: IF G_DEBUG THEN
847: ozf_utility_pvt.debug_message(l_full_name ||
848: ': create offers');
849: END IF;
850: x_return_status := fnd_api.g_ret_sts_success;
851:
860: END IF;
861:
862: IF p_mode = 'CREATE' THEN
863: IF G_DEBUG THEN
864: ozf_utility_pvt.debug_message('Create');
865: END IF;
866: -- first process_offerss in QP
867: l_offer_hdr_rec.qp_list_header_id := fnd_api.g_miss_num;
868: l_offer_hdr_rec.activity_media_id := p_fund_rec.task_id;
888: IF p_fund_rec.status_code = 'ACTIVE' THEN
889: */
890: IF p_fund_rec.status_code in ('ACTIVE','ON_HOLD') AND p_mode ='ACTIVE' THEN
891: IF G_DEBUG THEN
892: ozf_utility_pvt.debug_message(l_full_name ||
893: ': begin create act budgets ');
894: END IF;
895: -- Create_act_budgets for the created offers
896: l_act_budgets_rec_type.act_budget_used_by_id := p_fund_rec.plan_id;
906: l_act_budgets_rec_type.request_currency := p_fund_rec.currency_code_tc;
907: l_act_budgets_rec_type.request_date := SYSDATE;
908: l_act_budgets_rec_type.user_status_id := 5001;
909: l_act_budgets_rec_type.status_code := 'APPROVED';
910: l_act_budgets_rec_type.user_status_id := ozf_utility_pvt.get_default_user_status(
911: 'OZF_BUDGETSOURCE_STATUS',
912: 'APPROVED');
913: l_act_budgets_rec_type.approved_amount := NVL(p_fund_rec.accrual_cap, 0);
914: l_act_budgets_rec_type.approved_original_amount := NVL(p_fund_rec.accrual_cap, 0);
934: RAISE fnd_api.g_exc_unexpected_error;
935: END IF;
936:
937: IF G_DEBUG THEN
938: ozf_utility_pvt.debug_message(l_full_name ||
939: ': end create budgets ');
940: END IF;
941: END IF; -- end if for active
942: /* -- 10/11/2002
955:
956: -- dbms_OUTPUT.put_line('fund_status ' ||
957: -- p_fund_rec.status_code);
958: IF G_DEBUG THEN
959: ozf_utility_pvt.debug_message('debug6');
960: END IF;
961: l_qp_list_header_id := p_fund_rec.plan_id;
962: IF G_DEBUG THEN
963: ozf_utility_pvt.debug_message('l_qp_list_header_id =>' ||
959: ozf_utility_pvt.debug_message('debug6');
960: END IF;
961: l_qp_list_header_id := p_fund_rec.plan_id;
962: IF G_DEBUG THEN
963: ozf_utility_pvt.debug_message('l_qp_list_header_id =>' ||
964: l_qp_list_header_id);
965: END IF;
966: OPEN c_offer_id(l_qp_list_header_id);
967: FETCH c_offer_id INTO l_ozf_offer_id, l_offer_obj_ver_num;
981: l_offer_hdr_rec.modifier_operation := 'UPDATE';
982: END IF; -- end if for p_mode
983:
984: IF p_fund_rec.status_code = 'CLOSED' THEN
985: l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
986: l_status_type,
987: 'COMPLETED');
988: l_offer_hdr_rec.status_code := 'COMPLETED';
989: ELSIF p_fund_rec.status_code = 'CANCELLED' THEN
986: l_status_type,
987: 'COMPLETED');
988: l_offer_hdr_rec.status_code := 'COMPLETED';
989: ELSIF p_fund_rec.status_code = 'CANCELLED' THEN
990: l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
991: l_status_type,
992: 'TERMINATED');
993: l_offer_hdr_rec.status_code := 'TERMINATED';
994: ELSIF p_fund_rec.status_code = 'DRAFT' THEN
992: 'TERMINATED');
993: l_offer_hdr_rec.status_code := 'TERMINATED';
994: ELSIF p_fund_rec.status_code = 'DRAFT' THEN
995: l_offer_hdr_rec.status_code := 'DRAFT';
996: l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
997: l_status_type,
998: 'DRAFT');
999: ELSIF p_fund_rec.status_code = 'PENDING' THEN
1000: IF l_old_fund_status = 'REJECTED' THEN
998: 'DRAFT');
999: ELSIF p_fund_rec.status_code = 'PENDING' THEN
1000: IF l_old_fund_status = 'REJECTED' THEN
1001: l_offer_hdr_rec.status_code := 'REJECTED';
1002: l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
1003: l_status_type,
1004: 'REJECTED');
1005: ELSE
1006: l_offer_hdr_rec.status_code := 'DRAFT';
1003: l_status_type,
1004: 'REJECTED');
1005: ELSE
1006: l_offer_hdr_rec.status_code := 'DRAFT';
1007: l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
1008: l_status_type,
1009: 'DRAFT');
1010: END IF;
1011: l_offer_pending_flag := 'T';
1009: 'DRAFT');
1010: END IF;
1011: l_offer_pending_flag := 'T';
1012: IF G_DEBUG THEN
1013: ozf_utility_pvt.debug_message('debug5');
1014: END IF;
1015: ELSIF p_fund_rec.status_code = 'ON_HOLD' THEN
1016: l_offer_hdr_rec.status_code := 'ONHOLD';
1017: l_offer_hdr_rec.user_status_id := ams_utility_pvt.get_default_user_status(
1022: l_offer_hdr_rec.user_status_id := ams_utility_pvt.get_default_user_status(
1023: l_status_type,
1024: 'REJECTED');
1025: ELSIF p_fund_rec.status_code = 'ACTIVE' THEN
1026: l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
1027: l_status_type,
1028: 'ACTIVE');
1029: l_offer_hdr_rec.status_code := 'ACTIVE';
1030: END IF;
1082: -- QP_SECURITY_PROFILE IS ON.
1083: IF(p_fund_rec.org_id IS NULL OR p_fund_rec.org_id = FND_API.G_MISS_NUM) THEN
1084: -- DEFAULT OU IS NULL
1085: IF (NVL(fnd_profile.value('OZF_GLOBAL_FLAG'), 'N') = 'N') THEN
1086: OZF_Utility_PVT.Error_Message('OZF_ORG_ID_OR_GFLAG_REQD');
1087: RAISE FND_API.G_EXC_ERROR;
1088: ELSE
1089: l_offer_hdr_rec.global_flag := 'Y';
1090: END IF;
1096: ELSE
1097: --QP_SECURITY_PROFILE IS OFF.
1098: IF (NVL(fnd_profile.value('OZF_GLOBAL_FLAG'), 'N') = 'N') THEN
1099: -- OZF_GLOBAL_FLAG IS NO.
1100: OZF_Utility_PVT.Error_Message('QP_SEC_PROF_AND_GFLAG_NOT_COMP');
1101: RAISE FND_API.G_EXC_ERROR;
1102: ELSE
1103: l_offer_hdr_rec.global_flag := 'Y';
1104: END IF;
1105: END IF;
1106:
1107: ELSIF p_mode = 'UPDATE' THEN
1108:
1109: OZF_Utility_PVT.debug_message(FND_LOG.LEVEL_STATEMENT,'ozf_fundrules_pvt','Update mode = p_fund_rec.org_id'|| p_fund_rec.org_id);
1110:
1111: IF p_fund_rec.org_id IS NOT NULL AND p_fund_rec.org_id <> FND_API.G_MISS_NUM AND NVL(fnd_profile.value('QP_SECURITY_CONTROL'), 'OFF') <> 'OFF' THEN
1112: l_offer_hdr_rec.global_flag := 'N';
1113: l_offer_hdr_rec.orig_org_id := p_fund_rec.org_id; /*12_11_2012 pass it to offer API process_modifier*/
1140: );
1141:
1142: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1143: IF G_DEBUG THEN
1144: ozf_utility_pvt.debug_message('OZF_OFFR_UPDATE_SATAUS FAIL');
1145: END IF;
1146:
1147: IF l_return_status = fnd_api.g_ret_sts_error THEN
1148: RAISE fnd_api.g_exc_error;
1152: END IF;
1153:
1154: ELSE
1155: IF G_DEBUG THEN
1156: ozf_utility_pvt.debug_message('process modifiers');
1157: END IF;
1158: ozf_offer_pvt.process_modifiers(
1159: p_init_msg_list=> fnd_api.g_false,
1160: p_api_version=> 1.0,
1167: p_offer_type=> l_offer_hdr_rec.offer_type,
1168: x_qp_list_header_id=> l_qp_list_header_id,
1169: x_error_location=> l_error_location);
1170: IF G_DEBUG THEN
1171: ozf_utility_pvt.debug_message(
1172: 'l_return_status' ||
1173: l_return_status ||
1174: '-' ||
1175: l_error_location ||
1178: END IF;
1179:
1180: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1181: IF G_DEBUG THEN
1182: ozf_utility_pvt.debug_message('OZF_OFFR_QP_FAILURE' ||
1183: l_error_location ||
1184: x_msg_data);
1185: END IF;
1186:
1183: l_error_location ||
1184: x_msg_data);
1185: END IF;
1186:
1187: -- ozf_utility_pvt.error_message('OZF_OFFR_QP_FAILURE'||l_error_location||x_msg_data);
1188: IF l_return_status = fnd_api.g_ret_sts_error THEN
1189: RAISE fnd_api.g_exc_error;
1190: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1191: RAISE fnd_api.g_exc_unexpected_error;
1193: END IF;
1194:
1195: -- Update ozf_offers with status code 'PENDING' by incrementing object_version_number.
1196: IF l_offer_pending_flag = 'T' THEN
1197: l_offer_hdr_rec.user_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1198:
1199: -- 10/11/2002 mpande pass the default user status and check what is the default status code
1200: UPDATE ozf_offers
1201: SET status_code = 'PENDING',
1203: object_version_number = object_version_number + 1
1204: WHERE offer_id = l_ozf_offer_id;
1205:
1206: IF G_DEBUG THEN
1207: ozf_utility_pvt.debug_message('In update 1');
1208: END IF;
1209: END IF;
1210:
1211: IF p_mode = 'CREATE' THEN
1215: WHERE fund_id = p_fund_rec.fund_id;
1216: ELSIF p_mode = 'UPDATE' THEN
1217: l_qp_list_header_id := p_fund_rec.plan_id;
1218: IF G_DEBUG THEN
1219: ozf_utility_pvt.debug_message('qp_list_header_id =>' ||
1220: l_qp_list_header_id);
1221: END IF;
1222: END IF; -- end of p_mode = CREATE/UPDATE
1223:
1221: END IF;
1222: END IF; -- end of p_mode = CREATE/UPDATE
1223:
1224: IF G_DEBUG THEN
1225: ozf_utility_pvt.debug_message(l_full_name ||
1226: ': end update offer advanced option ');
1227: END IF;
1228: -- dbms_output.put_line('fundrules: process_modifiers to UPDATE returns ' || l_return_status || ' x_qp_list_header_id=' || l_qp_list_header_id);
1229: IF G_DEBUG THEN
1226: ': end update offer advanced option ');
1227: END IF;
1228: -- dbms_output.put_line('fundrules: process_modifiers to UPDATE returns ' || l_return_status || ' x_qp_list_header_id=' || l_qp_list_header_id);
1229: IF G_DEBUG THEN
1230: ozf_utility_pvt.debug_message(l_full_name ||
1231: ': begin exclusion ');
1232: END IF;
1233:
1234: --kdass 11-MAR-2004 fixed bug 3465281 - advanced options is handled by offers, budgets need to handle this
1236: -- for Fund_status = 'DRAFT' or p_mode = 'CREATE'.
1237: IF ( p_mode = 'CREATE'
1238: OR p_fund_rec.status_code = 'DRAFT') THEN
1239: IF G_DEBUG THEN
1240: ozf_utility_pvt.debug_message(l_full_name ||
1241: ': begin update offer advanced option ');
1242: END IF;
1243: --- 08/20/2001 mpande addded for discount level and bucket to process advanced options
1244: l_offer_advd_opt_rec.list_header_id := l_qp_list_header_id;
1259: x_msg_count=> l_msg_count,
1260: x_msg_data=> l_msg_data,
1261: p_advanced_options_rec=> l_offer_advd_opt_rec);
1262: IF G_DEBUG THEN
1263: ozf_utility_pvt.debug_message('ret status for process adv options =>' ||
1264: l_return_status);
1265: END IF;
1266:
1267: IF l_return_status = fnd_api.g_ret_sts_error THEN
1393: END IF;
1394: END IF;
1395:
1396: IF G_DEBUG THEN
1397: ozf_utility_pvt.debug_message(l_full_name ||
1398: ': end');
1399: END IF;
1400: EXCEPTION
1401: WHEN fnd_api.g_exc_error THEN
1472: IF l_total_budget > l_committed_amt THEN
1473: -- total > committed >= utilized >= earned >= paid
1474: -- don't close budget because there's available money
1475: IF l_fund_type = 'QUOTA' THEN
1476: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1477: ELSE
1478: ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE');
1479: END IF;
1480: x_return_status := fnd_api.g_ret_sts_error;
1474: -- don't close budget because there's available money
1475: IF l_fund_type = 'QUOTA' THEN
1476: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1477: ELSE
1478: ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE');
1479: END IF;
1480: x_return_status := fnd_api.g_ret_sts_error;
1481: ELSIF l_total_budget < l_committed_amt THEN
1482: -- total < re-calculated committed This only happens when profile 'OZF_BUDGET_ADJ_ALLOW_RECAL' is 'Y'
1482: -- total < re-calculated committed This only happens when profile 'OZF_BUDGET_ADJ_ALLOW_RECAL' is 'Y'
1483: -- don't close budget because re-calculated committed has committed funds for over the pool of money originally available,
1484: -- need a budget transfer into the budget
1485: IF l_fund_type = 'QUOTA' THEN
1486: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1487: ELSE
1488: ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_COMM_MORE');
1489: END IF;
1490: x_return_status := fnd_api.g_ret_sts_error;
1484: -- need a budget transfer into the budget
1485: IF l_fund_type = 'QUOTA' THEN
1486: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1487: ELSE
1488: ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_COMM_MORE');
1489: END IF;
1490: x_return_status := fnd_api.g_ret_sts_error;
1491: ELSE
1492: -- total = committed
1493: IF l_committed_amt > l_utilized_amt THEN
1494: -- total = calculated committed > utilized
1495: -- don't close budget because there is committed fund but not yet utilized
1496: IF l_fund_type = 'QUOTA' THEN
1497: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1498: ELSE
1499: ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_UTIL_LESS');
1500: END IF;
1501: x_return_status := fnd_api.g_ret_sts_error;
1495: -- don't close budget because there is committed fund but not yet utilized
1496: IF l_fund_type = 'QUOTA' THEN
1497: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1498: ELSE
1499: ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_UTIL_LESS');
1500: END IF;
1501: x_return_status := fnd_api.g_ret_sts_error;
1502: ELSIF l_committed_amt = l_utilized_amt THEN
1503: -- total = committed = utilized
1504: IF l_utilized_amt > l_earned_amt THEN
1505: -- total = calculated committed = utilized > earned
1506: -- don't close budget because there is utilized fund but not yet posted to GL
1507: IF l_fund_type = 'QUOTA' THEN
1508: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1509: ELSE
1510: ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_EARN_LESS');
1511: END IF;
1512: x_return_status := fnd_api.g_ret_sts_error;
1506: -- don't close budget because there is utilized fund but not yet posted to GL
1507: IF l_fund_type = 'QUOTA' THEN
1508: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1509: ELSE
1510: ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_EARN_LESS');
1511: END IF;
1512: x_return_status := fnd_api.g_ret_sts_error;
1513: ELSIF l_utilized_amt = l_earned_amt THEN
1514: -- total = calculated committed = utilized = earned
1516: -- total = re-calculated committed = utilized = earned > paid
1517: -- don't close budget because there's un-paid fund (trade management is implemented,
1518: -- accrual earnings not paid out by claim or deduction yet).
1519: IF l_fund_type = 'QUOTA' THEN
1520: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1521: ELSE
1522: ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_PAID_LESS');
1523: END IF;
1524: x_return_status := fnd_api.g_ret_sts_error;
1518: -- accrual earnings not paid out by claim or deduction yet).
1519: IF l_fund_type = 'QUOTA' THEN
1520: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_CLOSE');
1521: ELSE
1522: ozf_utility_pvt.error_message('OZF_FUND_BAN_CLOSE_PAID_LESS');
1523: END IF;
1524: x_return_status := fnd_api.g_ret_sts_error;
1525: ELSIF l_earned_amt = l_paid_amt THEN
1526: -- total = re-calculated committed = utilized = earned = paid
1605:
1606: BEGIN
1607: SAVEPOINT update_fund_status;
1608: IF G_DEBUG THEN
1609: ozf_utility_pvt.debug_message(l_full_name ||
1610: '- enter');
1611: END IF;
1612: x_return_status := fnd_api.g_ret_sts_success;
1613: -- initiallize the out params 08/14/2001 mpande added
1616: --Get old_status
1617: OPEN l_old_status;
1618: FETCH l_old_status INTO l_old_status_code, l_old_user_status_id, l_old_owner_id, l_fund_type, l_plan_id;
1619: CLOSE l_old_status;
1620: l_reject_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'REJECTED');
1621: l_new_status_code := p_fund_rec.status_code;
1622:
1623: IF l_old_status_code <> l_new_status_code THEN
1624: IF l_old_status_code IN ('CLOSED', 'ARCHIVED', 'CANCELLED') THEN
1623: IF l_old_status_code <> l_new_status_code THEN
1624: IF l_old_status_code IN ('CLOSED', 'ARCHIVED', 'CANCELLED') THEN
1625: IF l_new_status_code NOT IN ('ARCHIVED') THEN
1626: IF G_DEBUG THEN
1627: ozf_utility_pvt.debug_message(l_full_name ||
1628: 'fund closed');
1629: END IF;
1630: IF l_fund_type = 'QUOTA' THEN
1631: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_UPDATE');
1627: ozf_utility_pvt.debug_message(l_full_name ||
1628: 'fund closed');
1629: END IF;
1630: IF l_fund_type = 'QUOTA' THEN
1631: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_UPDATE');
1632: ELSE
1633: ozf_utility_pvt.error_message('OZF_FUND_BAN_UPDATE');
1634: END IF;
1635: --return the old status
1629: END IF;
1630: IF l_fund_type = 'QUOTA' THEN
1631: ozf_utility_pvt.error_message('OZF_TP_QUOTA_BAN_UPDATE');
1632: ELSE
1633: ozf_utility_pvt.error_message('OZF_FUND_BAN_UPDATE');
1634: END IF;
1635: --return the old status
1636: x_new_status_id := l_old_user_status_id;
1637: x_new_status_code := l_old_status_code;
1649: ELSIF l_old_status_code = 'PENDING' THEN
1650: IF l_new_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1651: --The following subroutine will take care of all the processing that take place for a active fund.
1652: IF G_DEBUG THEN
1653: ozf_utility_pvt.debug_message(l_full_name ||
1654: 'fund aproved');
1655: END IF;
1656: x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, l_new_status_code);
1657: x_new_status_code := l_new_status_code;
1652: IF G_DEBUG THEN
1653: ozf_utility_pvt.debug_message(l_full_name ||
1654: 'fund aproved');
1655: END IF;
1656: x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, l_new_status_code);
1657: x_new_status_code := l_new_status_code;
1658: --END IF;
1659: ELSIF l_new_status_code = 'REJECTED' THEN
1660: -- Update the fund with status REJECTED
1658: --END IF;
1659: ELSIF l_new_status_code = 'REJECTED' THEN
1660: -- Update the fund with status REJECTED
1661: IF G_DEBUG THEN
1662: ozf_utility_pvt.debug_message(l_full_name ||
1663: 'fund rejected');
1664: END IF;
1665: x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'REJECTED');
1666: x_new_status_code := 'REJECTED';
1661: IF G_DEBUG THEN
1662: ozf_utility_pvt.debug_message(l_full_name ||
1663: 'fund rejected');
1664: END IF;
1665: x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'REJECTED');
1666: x_new_status_code := 'REJECTED';
1667: ELSIF l_new_status_code = 'DRAFT' THEN
1668: --An error occurred during the approval process, revert back to 'DRAFT
1669: --Update the fund with status 'DRAFT'
1667: ELSIF l_new_status_code = 'DRAFT' THEN
1668: --An error occurred during the approval process, revert back to 'DRAFT
1669: --Update the fund with status 'DRAFT'
1670: IF G_DEBUG THEN
1671: ozf_utility_pvt.debug_message(l_full_name ||
1672: 'error in aproval');
1673: END IF;
1674: x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'DRAFT');
1675: x_new_status_code := 'DRAFT';
1670: IF G_DEBUG THEN
1671: ozf_utility_pvt.debug_message(l_full_name ||
1672: 'error in aproval');
1673: END IF;
1674: x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'DRAFT');
1675: x_new_status_code := 'DRAFT';
1676: END IF; -- end pending
1677: ELSIF l_old_status_code IN ('DRAFT', 'REJECTED') THEN
1678: IF l_new_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1677: ELSIF l_old_status_code IN ('DRAFT', 'REJECTED') THEN
1678: IF l_new_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1679: -- niprakas added
1680: IF (p_fund_rec.fund_usage = 'MTRAN') THEN
1681: ozf_utility_pvt.write_conc_log(' The fund_usage is Mass Transfer');
1682: x_new_status_id := ozf_utility_pvt.get_default_user_status(
1683: l_status_type,
1684: l_new_status_code);
1685: x_new_status_code := l_new_status_code;
1678: IF l_new_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1679: -- niprakas added
1680: IF (p_fund_rec.fund_usage = 'MTRAN') THEN
1681: ozf_utility_pvt.write_conc_log(' The fund_usage is Mass Transfer');
1682: x_new_status_id := ozf_utility_pvt.get_default_user_status(
1683: l_status_type,
1684: l_new_status_code);
1685: x_new_status_code := l_new_status_code;
1686: IF p_fund_rec.parent_fund_id IS NOT NULL THEN
1690: -- niprakas ends
1691:
1692: IF (p_fund_rec.fund_usage = 'ALLOC') THEN
1693: -- yzhao: 02/26/2003 fix bug bug 2823606 - when called from budget allocation activation, approval is not needed
1694: x_new_status_id := ozf_utility_pvt.get_default_user_status(
1695: l_status_type,
1696: l_new_status_code);
1697: x_new_status_code := l_new_status_code;
1698: IF p_fund_rec.parent_fund_id IS NOT NULL THEN
1721: -- changing status from 'DRAFT or 'REJECTED' to 'ACTIVE or ON_HOLD is
1722: -- equivalent to submitting for approval.
1723: -- Approval submission child fund
1724: IF G_DEBUG THEN
1725: ozf_utility_pvt.debug_message(l_full_name ||
1726: 'owner' ||
1727: p_fund_rec.owner);
1728: END IF;
1729: x_submit_child_approval := fnd_api.g_true;
1750: l_is_requestor_owner := 'N';
1751: END IF;
1752:
1753: IF l_is_requestor_owner = 'N' THEN
1754: x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1755: x_new_status_code := 'PENDING';
1756: ELSE
1757: x_new_status_id := ozf_utility_pvt.get_default_user_status(
1758: l_status_type,
1753: IF l_is_requestor_owner = 'N' THEN
1754: x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1755: x_new_status_code := 'PENDING';
1756: ELSE
1757: x_new_status_id := ozf_utility_pvt.get_default_user_status(
1758: l_status_type,
1759: l_new_status_code);
1760: x_new_status_code := l_new_status_code;
1761: END IF;
1763: -- Here Approval submission would be done for parent less fund
1764: -- call the approval API**********************************
1765: -- the approval API would updatethe fund status to pending
1766: IF G_DEBUG THEN
1767: ozf_utility_pvt.debug_message('Approval');
1768: END IF;
1769: x_submit_budget_approval := fnd_api.g_true;
1770: x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1771: x_new_status_code := 'PENDING';
1766: IF G_DEBUG THEN
1767: ozf_utility_pvt.debug_message('Approval');
1768: END IF;
1769: x_submit_budget_approval := fnd_api.g_true;
1770: x_new_status_id := ozf_utility_pvt.get_default_user_status(l_status_type, 'PENDING');
1771: x_new_status_code := 'PENDING';
1772: END IF; -- end of parent fund id check
1773: END IF; -- IF (p_fund_rec.fund_usage == 'ALLOC')
1774: -- 07/03/2001 bUG#1540719 -- Cancelled spellingwas incorrect
1772: END IF; -- end of parent fund id check
1773: END IF; -- IF (p_fund_rec.fund_usage == 'ALLOC')
1774: -- 07/03/2001 bUG#1540719 -- Cancelled spellingwas incorrect
1775: ELSIF l_new_status_code NOT IN ('CANCELLED') THEN
1776: ozf_utility_pvt.error_message('OZF_FUND_WRONG_STATUS');
1777: --return the old status
1778: x_new_status_id := l_old_user_status_id;
1779: x_new_status_code := l_old_status_code;
1780: x_return_status := fnd_api.g_ret_sts_error;
1811: -- 05/11/2003 niprakas else loop ends here for the bug#2950428
1812:
1813: ELSE
1814: -- Invalid status change, should not get here.
1815: ozf_utility_pvt.error_message('OZF_FUND_WRONG_STATUS');
1816: -- return the old status
1817: x_new_status_id := l_old_user_status_id;
1818: x_new_status_code := l_old_status_code;
1819: x_return_status := fnd_api.g_ret_sts_error;
1828: ELSIF l_old_status_code IN ('ACTIVE','CANCELLED','CLOSED','ARCHIVED','ON_HOLD') THEN
1829: */
1830: ELSIF l_old_status_code IN ('ACTIVE', 'ON_HOLD') THEN
1831: IF G_DEBUG THEN
1832: ozf_utility_pvt.debug_message(l_full_name ||
1833: 'fund no update except owner');
1834: END IF;
1835: --06/04/2001 added validations for accrual type fund
1836: -- all locking rules are to be implemented by locking rules so I am not raising a error here
1834: END IF;
1835: --06/04/2001 added validations for accrual type fund
1836: -- all locking rules are to be implemented by locking rules so I am not raising a error here
1837: -- Accrual Type Offer could be modified
1838: l_resource_id := ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id);
1839:
1840: -- the owner could be changed in a active status by the current owner
1841: -- 12/18/2001 yzhao: owner could be changed by super admin too
1842: IF p_fund_rec.owner <> l_old_owner_id THEN
1845: x_new_status_id := l_old_user_status_id;
1846: x_new_status_code := l_old_status_code;
1847: x_return_status := fnd_api.g_ret_sts_success;
1848: ELSE
1849: --ozf_utility_pvt.error_message('OZF_FUND_BAN_UPDATE');
1850: IF l_fund_type = 'QUOTA' THEN
1851: ozf_utility_pvt.error_message('OZF_TP_QUOTA_UPDT_OWNER_PERM');
1852: ELSE
1853: ozf_utility_pvt.error_message('OZF_FUND_UPDT_OWNER_PERM');
1847: x_return_status := fnd_api.g_ret_sts_success;
1848: ELSE
1849: --ozf_utility_pvt.error_message('OZF_FUND_BAN_UPDATE');
1850: IF l_fund_type = 'QUOTA' THEN
1851: ozf_utility_pvt.error_message('OZF_TP_QUOTA_UPDT_OWNER_PERM');
1852: ELSE
1853: ozf_utility_pvt.error_message('OZF_FUND_UPDT_OWNER_PERM');
1854: END IF;
1855: --return the old status
1849: --ozf_utility_pvt.error_message('OZF_FUND_BAN_UPDATE');
1850: IF l_fund_type = 'QUOTA' THEN
1851: ozf_utility_pvt.error_message('OZF_TP_QUOTA_UPDT_OWNER_PERM');
1852: ELSE
1853: ozf_utility_pvt.error_message('OZF_FUND_UPDT_OWNER_PERM');
1854: END IF;
1855: --return the old status
1856: x_new_status_id := l_old_user_status_id;
1857: x_new_status_code := l_old_status_code;
1873: x_new_status_id := p_fund_rec.user_status_id;
1874: END IF; --check for old and new diff
1875:
1876: IF G_DEBUG THEN
1877: ozf_utility_pvt.debug_message(l_full_name ||
1878: ': end');
1879: END IF;
1880: EXCEPTION
1881: WHEN fnd_api.g_exc_error THEN
1947: l_fund_status VARCHAR2(30);
1948: l_accrual_basis VARCHAR2(30);
1949: BEGIN
1950: IF G_DEBUG THEN
1951: ozf_utility_pvt.debug_message(l_full_name ||
1952: ': begin');
1953: END IF;
1954: SAVEPOINT process_accrual;
1955: x_return_status := fnd_api.g_ret_sts_success;