[Home] [Help]
102: FROM ozf_funds_all_vl
103: WHERE fund_id = p_node_id;
104:
105: BEGIN
106: x_return_status := FND_API.G_RET_STS_SUCCESS;
107:
108: -- dbms_output.put_line('get_node_info: hier_type=' || p_hierarchy_type || ' node=' || p_node_id || ' hier_id=' || p_hierarchy_id);
109: IF (p_hierarchy_type = 'TERRITORY') THEN
110: -- territory
131: x_node_info := l_node_info;
132:
133: EXCEPTION
134: WHEN OTHERS THEN
135: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
136: -- dbms_output.put_line('get_node_info: UNEXP exception ' || substr(sqlerrm, 1, 150));
137: fnd_msg_pub.count_and_get(
138: p_encoded => fnd_api.g_false
139: , p_count => x_msg_count
134: WHEN OTHERS THEN
135: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
136: -- dbms_output.put_line('get_node_info: UNEXP exception ' || substr(sqlerrm, 1, 150));
137: fnd_msg_pub.count_and_get(
138: p_encoded => fnd_api.g_false
139: , p_count => x_msg_count
140: , p_data => x_msg_data);
141: END get_node_info;
142:
183: WHERE parent_fund_id = p_node_id
184: AND status_code = 'ACTIVE';
185:
186: BEGIN
187: x_return_status := FND_API.G_RET_STS_SUCCESS;
188: l_index := 1;
189:
190: -- dbms_output.put_line('get_node_children: hier_type=' || p_hierarchy_type || ' node=' || p_node_id || ' hier_id=' || p_hierarchy_id);
191: IF (p_hierarchy_type = 'TERRITORY') THEN
211: x_child_node_tbl := l_child_node_tbl;
212:
213: EXCEPTION
214: WHEN OTHERS THEN
215: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
216: -- dbms_output.put_line('get_node_children: UNEXP exception ' || substr(sqlerrm, 1, 150));
217: fnd_msg_pub.count_and_get(
218: p_encoded => fnd_api.g_false
219: , p_count => x_msg_count
214: WHEN OTHERS THEN
215: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
216: -- dbms_output.put_line('get_node_children: UNEXP exception ' || substr(sqlerrm, 1, 150));
217: fnd_msg_pub.count_and_get(
218: p_encoded => fnd_api.g_false
219: , p_count => x_msg_count
220: , p_data => x_msg_data);
221: END get_node_children;
222:
354:
355: BEGIN
356: x_self_amount := 0;
357: x_rollup_amount := 0;
358: x_return_status := FND_API.G_RET_STS_SUCCESS;
359:
360: -- Fetch currency_code, start_date and end_date
361: OPEN c_prior_year_parameters(p_node_id);
362: FETCH c_prior_year_parameters INTO l_curr_code, l_temp_start_date, l_temp_end_date;
429: ,p_from_amount => l_total_terr_self_amount
430: ,x_to_amount => x_self_amount
431: ,x_rate => l_rate);
432:
433: IF l_return_status = fnd_api.g_ret_sts_error
434: THEN
435: RAISE fnd_api.g_exc_error;
436: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
437: THEN
431: ,x_rate => l_rate);
432:
433: IF l_return_status = fnd_api.g_ret_sts_error
434: THEN
435: RAISE fnd_api.g_exc_error;
436: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
437: THEN
438: RAISE fnd_api.g_exc_unexpected_error;
439: END IF;
432:
433: IF l_return_status = fnd_api.g_ret_sts_error
434: THEN
435: RAISE fnd_api.g_exc_error;
436: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
437: THEN
438: RAISE fnd_api.g_exc_unexpected_error;
439: END IF;
440:
434: THEN
435: RAISE fnd_api.g_exc_error;
436: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
437: THEN
438: RAISE fnd_api.g_exc_unexpected_error;
439: END IF;
440:
441: -- Convert the rollup amount
442:
447: ,p_from_amount => l_total_terr_rollup_amount
448: ,x_to_amount => x_rollup_amount
449: ,x_rate => l_rate);
450:
451: IF l_return_status = fnd_api.g_ret_sts_error
452: THEN
453: RAISE fnd_api.g_exc_error;
454: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
455: THEN
449: ,x_rate => l_rate);
450:
451: IF l_return_status = fnd_api.g_ret_sts_error
452: THEN
453: RAISE fnd_api.g_exc_error;
454: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
455: THEN
456: RAISE fnd_api.g_exc_unexpected_error;
457: END IF;
450:
451: IF l_return_status = fnd_api.g_ret_sts_error
452: THEN
453: RAISE fnd_api.g_exc_error;
454: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
455: THEN
456: RAISE fnd_api.g_exc_unexpected_error;
457: END IF;
458:
452: THEN
453: RAISE fnd_api.g_exc_error;
454: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
455: THEN
456: RAISE fnd_api.g_exc_unexpected_error;
457: END IF;
458:
459: EXCEPTION
460: WHEN OTHERS THEN
457: END IF;
458:
459: EXCEPTION
460: WHEN OTHERS THEN
461: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
462: -- dbms_output.put_line('get_prior_year_sales: UNEXP exception ' || substr(sqlerrm, 1, 150));
463: fnd_msg_pub.count_and_get(
464: p_encoded => fnd_api.g_false
465: , p_count => x_msg_count
460: WHEN OTHERS THEN
461: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
462: -- dbms_output.put_line('get_prior_year_sales: UNEXP exception ' || substr(sqlerrm, 1, 150));
463: fnd_msg_pub.count_and_get(
464: p_encoded => fnd_api.g_false
465: , p_count => x_msg_count
466: , p_data => x_msg_data);
467: END get_prior_year_sales;
468:
528: l_tmp_pct NUMBER;
529: l_index NUMBER := 1;
530:
531: BEGIN
532: x_return_status := FND_API.G_RET_STS_SUCCESS;
533:
534: l_worksheet_rec.node_id := p_node_id;
535: l_worksheet_rec.parent_node_id := p_parent_node_id;
536: l_worksheet_rec.level_depth := p_curr_level;
563: , x_rollup_amount => l_sum_amount
564: , x_return_status => l_return_status
565: , x_msg_count => x_msg_count
566: , x_msg_data => x_msg_data);
567: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
568: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
569: END IF;
570: -- dbms_output.put_line('compute_worksheet: get_prior_year_sales returns' || x_return_status);
571:
564: , x_return_status => l_return_status
565: , x_msg_count => x_msg_count
566: , x_msg_data => x_msg_data);
567: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
568: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
569: END IF;
570: -- dbms_output.put_line('compute_worksheet: get_prior_year_sales returns' || x_return_status);
571:
572: IF (l_sum_amount = 0) THEN
623: , x_return_status => l_return_status
624: , x_msg_count => x_msg_count
625: , x_msg_data => x_msg_data
626: );
627: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
628: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629: END IF;
630: --dbms_output.put_line('compute_worksheet: child count=' || l_child_node_tbl.COUNT);
631:
624: , x_msg_count => x_msg_count
625: , x_msg_data => x_msg_data
626: );
627: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
628: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629: END IF;
630: --dbms_output.put_line('compute_worksheet: child count=' || l_child_node_tbl.COUNT);
631:
632: -- compute worksheet for children. recursive call
660: , x_return_status => x_return_status
661: , x_msg_count => x_msg_count
662: , x_msg_data => x_msg_data
663: );
664: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
665: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
666: END IF;
667:
668: IF (l_child_worksheet_tbl.COUNT IS NOT NULL AND l_child_worksheet_tbl.COUNT > 0) THEN
661: , x_msg_count => x_msg_count
662: , x_msg_data => x_msg_data
663: );
664: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
665: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
666: END IF;
667:
668: IF (l_child_worksheet_tbl.COUNT IS NOT NULL AND l_child_worksheet_tbl.COUNT > 0) THEN
669: l_sum_amount := l_sum_amount + l_child_worksheet_tbl(1).total_amount;
681: x_worksheet_tbl := l_worksheet_tbl;
682:
683: EXCEPTION
684: WHEN OTHERS THEN
685: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
686: -- dbms_output.put_line('compute_worksheet(node_id=' || p_node_id || '): exception - ' || substr(sqlerrm, 1, 200));
687: fnd_msg_pub.count_and_get(
688: p_encoded => fnd_api.g_false
689: , p_count => x_msg_count
684: WHEN OTHERS THEN
685: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
686: -- dbms_output.put_line('compute_worksheet(node_id=' || p_node_id || '): exception - ' || substr(sqlerrm, 1, 200));
687: fnd_msg_pub.count_and_get(
688: p_encoded => fnd_api.g_false
689: , p_count => x_msg_count
690: , p_data => x_msg_data);
691: END compute_worksheet;
692:
746: AND level_depth = p_level_depth;
747:
748: BEGIN
749: SAVEPOINT create_alloc_hierarchy_sp;
750: x_return_status := FND_API.G_RET_STS_SUCCESS;
751:
752: IF (fnd_api.to_boolean(p_init_msg_list)) THEN
753: fnd_msg_pub.initialize;
754: END IF;
748: BEGIN
749: SAVEPOINT create_alloc_hierarchy_sp;
750: x_return_status := FND_API.G_RET_STS_SUCCESS;
751:
752: IF (fnd_api.to_boolean(p_init_msg_list)) THEN
753: fnd_msg_pub.initialize;
754: END IF;
755:
756: IF (NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name)) THEN
752: IF (fnd_api.to_boolean(p_init_msg_list)) THEN
753: fnd_msg_pub.initialize;
754: END IF;
755:
756: IF (NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name)) THEN
757: RAISE fnd_api.g_exc_unexpected_error;
758: END IF;
759:
760: IF G_DEBUG THEN
753: fnd_msg_pub.initialize;
754: END IF;
755:
756: IF (NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name)) THEN
757: RAISE fnd_api.g_exc_unexpected_error;
758: END IF;
759:
760: IF G_DEBUG THEN
761: Ozf_Utility_Pvt.Debug_Message('create_alloc_hierarchy() start ');
771: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
772: fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
773: fnd_msg_pub.add;
774: END IF;
775: RAISE fnd_api.g_exc_error;
776: END IF;
777: CLOSE c_get_alloc_details;
778:
779: compute_worksheet(p_alloc_id => p_alloc_id
794: , x_msg_count => x_msg_count
795: , x_msg_data => x_msg_data
796: );
797: -- dbms_output.put_line('create_alloc_hierarchy: compute_worksheet returns ' || l_return_status || ' fact count=' || l_worksheet_tbl.COUNT);
798: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
799: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
800: END IF;
801:
802: IF (l_worksheet_tbl.COUNT IS NOT NULL AND l_worksheet_tbl.COUNT > 0) THEN
795: , x_msg_data => x_msg_data
796: );
797: -- dbms_output.put_line('create_alloc_hierarchy: compute_worksheet returns ' || l_return_status || ' fact count=' || l_worksheet_tbl.COUNT);
798: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
799: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
800: END IF;
801:
802: IF (l_worksheet_tbl.COUNT IS NOT NULL AND l_worksheet_tbl.COUNT > 0) THEN
803: FOR I IN l_worksheet_tbl.FIRST .. l_worksheet_tbl.LAST LOOP
835: , p_act_metric_fact_rec => l_fact_rec
836: , x_activity_metric_fact_id => l_fact_id
837: );
838: -- dbms_output.put_line('create_alloc_herarchy: create_actmetricfact returns ' || l_return_status || ' fact_id=' || l_fact_id);
839: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
840: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841: END IF;
842: END LOOP;
843: END IF;
836: , x_activity_metric_fact_id => l_fact_id
837: );
838: -- dbms_output.put_line('create_alloc_herarchy: create_actmetricfact returns ' || l_return_status || ' fact_id=' || l_fact_id);
839: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
840: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841: END IF;
842: END LOOP;
843: END IF;
844:
844:
845: EXCEPTION
846: WHEN OTHERS THEN
847: ROLLBACK TO create_alloc_hierarchy_sp;
848: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849: -- dbms_output.put_line('create_alloc_hierarchy: exception - ' || substr(sqlerrm, 1, 200));
850: fnd_msg_pub.count_and_get(
851: p_encoded => fnd_api.g_false
852: , p_count => x_msg_count
847: ROLLBACK TO create_alloc_hierarchy_sp;
848: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849: -- dbms_output.put_line('create_alloc_hierarchy: exception - ' || substr(sqlerrm, 1, 200));
850: fnd_msg_pub.count_and_get(
851: p_encoded => fnd_api.g_false
852: , p_count => x_msg_count
853: , p_data => x_msg_data);
854: END create_alloc_hierarchy;
855:
933: );
934:
935: BEGIN
936: SAVEPOINT create_budget_for_one_node_sp;
937: x_return_status := FND_API.G_RET_STS_SUCCESS;
938:
939: -- create child fund for the node
940: ozf_funds_pvt.create_fund( p_api_version => p_api_version
941: , p_init_msg_list => fnd_api.g_false
937: x_return_status := FND_API.G_RET_STS_SUCCESS;
938:
939: -- create child fund for the node
940: ozf_funds_pvt.create_fund( p_api_version => p_api_version
941: , p_init_msg_list => fnd_api.g_false
942: , p_commit => fnd_api.g_false
943: , p_validation_level => fnd_api.g_valid_level_full
944: , x_return_status => l_return_status
945: , x_msg_count => x_msg_count
938:
939: -- create child fund for the node
940: ozf_funds_pvt.create_fund( p_api_version => p_api_version
941: , p_init_msg_list => fnd_api.g_false
942: , p_commit => fnd_api.g_false
943: , p_validation_level => fnd_api.g_valid_level_full
944: , x_return_status => l_return_status
945: , x_msg_count => x_msg_count
946: , x_msg_data => x_msg_data
939: -- create child fund for the node
940: ozf_funds_pvt.create_fund( p_api_version => p_api_version
941: , p_init_msg_list => fnd_api.g_false
942: , p_commit => fnd_api.g_false
943: , p_validation_level => fnd_api.g_valid_level_full
944: , x_return_status => l_return_status
945: , x_msg_count => x_msg_count
946: , x_msg_data => x_msg_data
947: , p_fund_rec => p_fund_rec
948: , x_fund_id => l_new_fund_id
949: );
950: -- dbms_output.put_line('create_budget_for_one_node(): create fund for node returns ' || l_return_status);
951:
952: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
953: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
954: END IF;
955:
956: IF p_resource_id IS NOT NULL THEN
949: );
950: -- dbms_output.put_line('create_budget_for_one_node(): create fund for node returns ' || l_return_status);
951:
952: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
953: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
954: END IF;
955:
956: IF p_resource_id IS NOT NULL THEN
957: l_access_exists := NULL;
968: l_access_rec.admin_flag := 'Y';
969: l_access_rec.owner_flag := 'N';
970: ams_access_pvt.create_access(
971: p_api_version => p_api_version
972: , p_init_msg_list => fnd_api.g_true
973: , p_validation_level => fnd_api.g_valid_level_full
974: , p_commit => fnd_api.g_false
975: , p_access_rec => l_access_rec
976: , x_return_status => l_return_status
969: l_access_rec.owner_flag := 'N';
970: ams_access_pvt.create_access(
971: p_api_version => p_api_version
972: , p_init_msg_list => fnd_api.g_true
973: , p_validation_level => fnd_api.g_valid_level_full
974: , p_commit => fnd_api.g_false
975: , p_access_rec => l_access_rec
976: , x_return_status => l_return_status
977: , x_msg_count => x_msg_count
970: ams_access_pvt.create_access(
971: p_api_version => p_api_version
972: , p_init_msg_list => fnd_api.g_true
973: , p_validation_level => fnd_api.g_valid_level_full
974: , p_commit => fnd_api.g_false
975: , p_access_rec => l_access_rec
976: , x_return_status => l_return_status
977: , x_msg_count => x_msg_count
978: , x_msg_data => x_msg_data
977: , x_msg_count => x_msg_count
978: , x_msg_data => x_msg_data
979: , x_access_id => l_return_id
980: );
981: IF l_return_status = fnd_api.g_ret_sts_error THEN
982: RAISE fnd_api.g_exc_error;
983: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
984: RAISE fnd_api.g_exc_unexpected_error;
985: END IF;
978: , x_msg_data => x_msg_data
979: , x_access_id => l_return_id
980: );
981: IF l_return_status = fnd_api.g_ret_sts_error THEN
982: RAISE fnd_api.g_exc_error;
983: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
984: RAISE fnd_api.g_exc_unexpected_error;
985: END IF;
986: END IF;
979: , x_access_id => l_return_id
980: );
981: IF l_return_status = fnd_api.g_ret_sts_error THEN
982: RAISE fnd_api.g_exc_error;
983: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
984: RAISE fnd_api.g_exc_unexpected_error;
985: END IF;
986: END IF;
987: END IF;
980: );
981: IF l_return_status = fnd_api.g_ret_sts_error THEN
982: RAISE fnd_api.g_exc_error;
983: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
984: RAISE fnd_api.g_exc_unexpected_error;
985: END IF;
986: END IF;
987: END IF;
988:
1005: l_access_rec.admin_flag := 'N';
1006: l_access_rec.owner_flag := 'N';
1007: ams_access_pvt.create_access(
1008: p_api_version => p_api_version
1009: , p_init_msg_list => fnd_api.g_true
1010: , p_validation_level => fnd_api.g_valid_level_full
1011: , p_commit => fnd_api.g_false
1012: , x_return_status => l_return_status
1013: , x_msg_count => x_msg_count
1006: l_access_rec.owner_flag := 'N';
1007: ams_access_pvt.create_access(
1008: p_api_version => p_api_version
1009: , p_init_msg_list => fnd_api.g_true
1010: , p_validation_level => fnd_api.g_valid_level_full
1011: , p_commit => fnd_api.g_false
1012: , x_return_status => l_return_status
1013: , x_msg_count => x_msg_count
1014: , x_msg_data => x_msg_data
1007: ams_access_pvt.create_access(
1008: p_api_version => p_api_version
1009: , p_init_msg_list => fnd_api.g_true
1010: , p_validation_level => fnd_api.g_valid_level_full
1011: , p_commit => fnd_api.g_false
1012: , x_return_status => l_return_status
1013: , x_msg_count => x_msg_count
1014: , x_msg_data => x_msg_data
1015: , p_access_rec => l_access_rec
1014: , x_msg_data => x_msg_data
1015: , p_access_rec => l_access_rec
1016: , x_access_id => l_return_id
1017: );
1018: IF l_return_status = fnd_api.g_ret_sts_error THEN
1019: RAISE fnd_api.g_exc_error;
1020: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1021: RAISE fnd_api.g_exc_unexpected_error;
1022: END IF;
1015: , p_access_rec => l_access_rec
1016: , x_access_id => l_return_id
1017: );
1018: IF l_return_status = fnd_api.g_ret_sts_error THEN
1019: RAISE fnd_api.g_exc_error;
1020: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1021: RAISE fnd_api.g_exc_unexpected_error;
1022: END IF;
1023: END IF;
1016: , x_access_id => l_return_id
1017: );
1018: IF l_return_status = fnd_api.g_ret_sts_error THEN
1019: RAISE fnd_api.g_exc_error;
1020: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1021: RAISE fnd_api.g_exc_unexpected_error;
1022: END IF;
1023: END IF;
1024: END IF;
1017: );
1018: IF l_return_status = fnd_api.g_ret_sts_error THEN
1019: RAISE fnd_api.g_exc_error;
1020: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1021: RAISE fnd_api.g_exc_unexpected_error;
1022: END IF;
1023: END IF;
1024: END IF;
1025:
1045: l_access_rec.admin_flag := 'N';
1046: l_access_rec.owner_flag := 'N';
1047: ams_access_pvt.create_access(
1048: p_api_version => p_api_version
1049: , p_init_msg_list => fnd_api.g_true
1050: , p_validation_level => fnd_api.g_valid_level_full
1051: , p_commit => fnd_api.g_false
1052: , x_return_status => l_return_status
1053: , x_msg_count => x_msg_count
1046: l_access_rec.owner_flag := 'N';
1047: ams_access_pvt.create_access(
1048: p_api_version => p_api_version
1049: , p_init_msg_list => fnd_api.g_true
1050: , p_validation_level => fnd_api.g_valid_level_full
1051: , p_commit => fnd_api.g_false
1052: , x_return_status => l_return_status
1053: , x_msg_count => x_msg_count
1054: , x_msg_data => x_msg_data
1047: ams_access_pvt.create_access(
1048: p_api_version => p_api_version
1049: , p_init_msg_list => fnd_api.g_true
1050: , p_validation_level => fnd_api.g_valid_level_full
1051: , p_commit => fnd_api.g_false
1052: , x_return_status => l_return_status
1053: , x_msg_count => x_msg_count
1054: , x_msg_data => x_msg_data
1055: , p_access_rec => l_access_rec
1054: , x_msg_data => x_msg_data
1055: , p_access_rec => l_access_rec
1056: , x_access_id => l_return_id
1057: );
1058: IF l_return_status = fnd_api.g_ret_sts_error THEN
1059: RAISE fnd_api.g_exc_error;
1060: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1061: RAISE fnd_api.g_exc_unexpected_error;
1062: END IF;
1055: , p_access_rec => l_access_rec
1056: , x_access_id => l_return_id
1057: );
1058: IF l_return_status = fnd_api.g_ret_sts_error THEN
1059: RAISE fnd_api.g_exc_error;
1060: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1061: RAISE fnd_api.g_exc_unexpected_error;
1062: END IF;
1063: END IF;
1056: , x_access_id => l_return_id
1057: );
1058: IF l_return_status = fnd_api.g_ret_sts_error THEN
1059: RAISE fnd_api.g_exc_error;
1060: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1061: RAISE fnd_api.g_exc_unexpected_error;
1062: END IF;
1063: END IF;
1064: END LOOP;
1057: );
1058: IF l_return_status = fnd_api.g_ret_sts_error THEN
1059: RAISE fnd_api.g_exc_error;
1060: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1061: RAISE fnd_api.g_exc_unexpected_error;
1062: END IF;
1063: END IF;
1064: END LOOP;
1065:
1080: p_errmsg => l_errmsg
1081: );
1082: -- dbms_output.put_line(l_full_name || 'create market eligibility for new fund(' || l_child_fund_id || ') returns ' || l_errnum || ' errcode=' || l_errcode || ' errmsg=' || l_errmsg);
1083: IF (l_errnum <> 0) THEN
1084: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1085: END IF;
1086: */
1087:
1088: FOR segments_rec IN c_segments_cur(p_fund_id, p_fund_rec.hierarchy_id) LOOP
1110: l_segments_rec.exclude_flag := segments_rec.exclude_flag;
1111: l_segments_rec.group_code := segments_rec.group_code;
1112: ams_act_market_segments_pvt.create_market_segments (
1113: p_api_version => p_api_version
1114: , p_init_msg_list => fnd_api.g_true
1115: , p_commit => fnd_api.g_false
1116: , p_validation_level => fnd_api.g_valid_level_full
1117: , x_return_status => l_return_status
1118: , x_msg_count => x_msg_count
1111: l_segments_rec.group_code := segments_rec.group_code;
1112: ams_act_market_segments_pvt.create_market_segments (
1113: p_api_version => p_api_version
1114: , p_init_msg_list => fnd_api.g_true
1115: , p_commit => fnd_api.g_false
1116: , p_validation_level => fnd_api.g_valid_level_full
1117: , x_return_status => l_return_status
1118: , x_msg_count => x_msg_count
1119: , x_msg_data => x_msg_data
1112: ams_act_market_segments_pvt.create_market_segments (
1113: p_api_version => p_api_version
1114: , p_init_msg_list => fnd_api.g_true
1115: , p_commit => fnd_api.g_false
1116: , p_validation_level => fnd_api.g_valid_level_full
1117: , x_return_status => l_return_status
1118: , x_msg_count => x_msg_count
1119: , x_msg_data => x_msg_data
1120: , p_mks_rec => l_segments_rec
1120: , p_mks_rec => l_segments_rec
1121: , x_act_mks_id => l_return_id
1122: );
1123:
1124: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1125: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1126: END IF;
1127: END LOOP;
1128:
1121: , x_act_mks_id => l_return_id
1122: );
1123:
1124: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1125: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1126: END IF;
1127: END LOOP;
1128:
1129:
1141: p_errmsg => l_errmsg
1142: );
1143: -- dbms_output.put_line(l_full_name || 'create product eligibility for new fund(' || l_child_fund_id || ') returns ' || l_errnum || ' errcode=' || l_errcode || ' errmsg=' || l_errmsg);
1144: IF (l_errnum <> 0) THEN
1145: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1146: END IF;
1147:
1148: /* yzhao: 04/09/2002 fix bug 2653078: 1158.9FP:MAPREL68: ALLOCATED BUDGET DOES NOT SHOW TERRITORY UNDER MARKET ELIGIBI */
1149: l_errnum := NULL;
1159: l_mktelig_rec.exclude_flag := 'N';
1160: l_mktelig_rec.group_code := 'TERRITORY' || p_fund_rec.node_id;
1161: ams_act_market_segments_pvt.create_market_segments(
1162: p_api_version => p_api_version
1163: , p_init_msg_list => fnd_api.g_true
1164: , p_commit => fnd_api.g_false
1165: , p_validation_level => fnd_api.g_valid_level_full
1166: , x_return_status => l_return_status
1167: , x_msg_count => x_msg_count
1160: l_mktelig_rec.group_code := 'TERRITORY' || p_fund_rec.node_id;
1161: ams_act_market_segments_pvt.create_market_segments(
1162: p_api_version => p_api_version
1163: , p_init_msg_list => fnd_api.g_true
1164: , p_commit => fnd_api.g_false
1165: , p_validation_level => fnd_api.g_valid_level_full
1166: , x_return_status => l_return_status
1167: , x_msg_count => x_msg_count
1168: , x_msg_data => x_msg_data
1161: ams_act_market_segments_pvt.create_market_segments(
1162: p_api_version => p_api_version
1163: , p_init_msg_list => fnd_api.g_true
1164: , p_commit => fnd_api.g_false
1165: , p_validation_level => fnd_api.g_valid_level_full
1166: , x_return_status => l_return_status
1167: , x_msg_count => x_msg_count
1168: , x_msg_data => x_msg_data
1169: , p_mks_rec => l_mktelig_rec
1169: , p_mks_rec => l_mktelig_rec
1170: , x_act_mks_id => l_errnum
1171: );
1172: -- dbms_output.put_line('create_market_segments returns ' || l_return_status);
1173: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1174: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1175: END IF;
1176: END IF;
1177: /* yzhao: 11/01/2002 fix bug 2653078 ends */
1170: , x_act_mks_id => l_errnum
1171: );
1172: -- dbms_output.put_line('create_market_segments returns ' || l_return_status);
1173: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1174: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1175: END IF;
1176: END IF;
1177: /* yzhao: 11/01/2002 fix bug 2653078 ends */
1178:
1180:
1181: EXCEPTION
1182: WHEN OTHERS THEN
1183: ROLLBACK TO create_budget_for_one_node_sp;
1184: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1185: -- dbms_output.put_line('create_budget_for_one_node(): exception - ' || substr(sqlerrm, 1, 200));
1186: fnd_msg_pub.count_and_get(
1187: p_encoded => fnd_api.g_false
1188: , p_count => x_msg_count
1183: ROLLBACK TO create_budget_for_one_node_sp;
1184: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1185: -- dbms_output.put_line('create_budget_for_one_node(): exception - ' || substr(sqlerrm, 1, 200));
1186: fnd_msg_pub.count_and_get(
1187: p_encoded => fnd_api.g_false
1188: , p_count => x_msg_count
1189: , p_data => x_msg_data);
1190: END create_budget_for_one_node;
1191:
1243: , x_msg_count => x_msg_count
1244: , x_msg_data => x_msg_data
1245: , x_act_budget_id => l_act_budget_id);
1246: -- dbms_output.put_line('transfer_approved_budget: create_act_Budget returns ' || l_return_status || ' act_budget_id=' || l_act_budget_id);
1247: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1248: RAISE fnd_api.g_exc_unexpected_error;
1249: END IF;
1250:
1251: -- update actbudget record as APPROVED
1244: , x_msg_data => x_msg_data
1245: , x_act_budget_id => l_act_budget_id);
1246: -- dbms_output.put_line('transfer_approved_budget: create_act_Budget returns ' || l_return_status || ' act_budget_id=' || l_act_budget_id);
1247: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1248: RAISE fnd_api.g_exc_unexpected_error;
1249: END IF;
1250:
1251: -- update actbudget record as APPROVED
1252: ozf_actbudgets_pvt.init_act_budgets_rec(l_act_budget_rec);
1257: l_act_budget_rec.user_status_id :=
1258: ozf_utility_pvt.get_default_user_status('OZF_BUDGETSOURCE_STATUS', l_act_budget_rec.status_code);
1259: ozf_actbudgets_pvt.update_act_budgets(
1260: p_api_version => p_api_version
1261: , p_init_msg_list => fnd_api.g_true
1262: , p_commit => fnd_api.g_false
1263: , p_act_budgets_rec => l_act_budget_rec
1264: , p_child_approval_flag => fnd_api.g_false -- false since child budget already active
1265: , p_requestor_owner_flag => 'Y' -- set it to bypass approval
1258: ozf_utility_pvt.get_default_user_status('OZF_BUDGETSOURCE_STATUS', l_act_budget_rec.status_code);
1259: ozf_actbudgets_pvt.update_act_budgets(
1260: p_api_version => p_api_version
1261: , p_init_msg_list => fnd_api.g_true
1262: , p_commit => fnd_api.g_false
1263: , p_act_budgets_rec => l_act_budget_rec
1264: , p_child_approval_flag => fnd_api.g_false -- false since child budget already active
1265: , p_requestor_owner_flag => 'Y' -- set it to bypass approval
1266: , x_return_status => l_return_status
1260: p_api_version => p_api_version
1261: , p_init_msg_list => fnd_api.g_true
1262: , p_commit => fnd_api.g_false
1263: , p_act_budgets_rec => l_act_budget_rec
1264: , p_child_approval_flag => fnd_api.g_false -- false since child budget already active
1265: , p_requestor_owner_flag => 'Y' -- set it to bypass approval
1266: , x_return_status => l_return_status
1267: , x_msg_count => x_msg_count
1268: , x_msg_data => x_msg_data);
1266: , x_return_status => l_return_status
1267: , x_msg_count => x_msg_count
1268: , x_msg_data => x_msg_data);
1269: -- dbms_output.put_line('transfer_approved_budget: update_act_Budget approved returns ' || l_return_status || ' act_budget_id=' || l_act_budget_id);
1270: IF l_return_status = fnd_api.g_ret_sts_error THEN
1271: RAISE fnd_api.g_exc_error;
1272: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1273: RAISE fnd_api.g_exc_unexpected_error;
1274: END IF;
1267: , x_msg_count => x_msg_count
1268: , x_msg_data => x_msg_data);
1269: -- dbms_output.put_line('transfer_approved_budget: update_act_Budget approved returns ' || l_return_status || ' act_budget_id=' || l_act_budget_id);
1270: IF l_return_status = fnd_api.g_ret_sts_error THEN
1271: RAISE fnd_api.g_exc_error;
1272: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1273: RAISE fnd_api.g_exc_unexpected_error;
1274: END IF;
1275:
1268: , x_msg_data => x_msg_data);
1269: -- dbms_output.put_line('transfer_approved_budget: update_act_Budget approved returns ' || l_return_status || ' act_budget_id=' || l_act_budget_id);
1270: IF l_return_status = fnd_api.g_ret_sts_error THEN
1271: RAISE fnd_api.g_exc_error;
1272: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1273: RAISE fnd_api.g_exc_unexpected_error;
1274: END IF;
1275:
1276: x_return_status := fnd_api.g_ret_sts_success;
1269: -- dbms_output.put_line('transfer_approved_budget: update_act_Budget approved returns ' || l_return_status || ' act_budget_id=' || l_act_budget_id);
1270: IF l_return_status = fnd_api.g_ret_sts_error THEN
1271: RAISE fnd_api.g_exc_error;
1272: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1273: RAISE fnd_api.g_exc_unexpected_error;
1274: END IF;
1275:
1276: x_return_status := fnd_api.g_ret_sts_success;
1277:
1272: ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1273: RAISE fnd_api.g_exc_unexpected_error;
1274: END IF;
1275:
1276: x_return_status := fnd_api.g_ret_sts_success;
1277:
1278:
1279: EXCEPTION
1280: WHEN OTHERS THEN
1278:
1279: EXCEPTION
1280: WHEN OTHERS THEN
1281: -- dbms_output.put_line('transfer_approved_budget: exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
1282: x_return_status := fnd_api.g_ret_sts_unexp_error;
1283: fnd_msg_pub.count_and_get(
1284: p_encoded => fnd_api.g_false
1285: , p_count => x_msg_count
1286: , p_data => x_msg_data);
1280: WHEN OTHERS THEN
1281: -- dbms_output.put_line('transfer_approved_budget: exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
1282: x_return_status := fnd_api.g_ret_sts_unexp_error;
1283: fnd_msg_pub.count_and_get(
1284: p_encoded => fnd_api.g_false
1285: , p_count => x_msg_count
1286: , p_data => x_msg_data);
1287: END transfer_approved_budget;
1288:
1395: SAVEPOINT publish_allocation_sp;
1396: IF G_DEBUG THEN
1397: ozf_utility_pvt.debug_message(l_full_name || ': start');
1398: END IF;
1399: x_return_status := fnd_api.g_ret_sts_success;
1400:
1401: IF fnd_api.to_boolean(p_init_msg_list) THEN
1402: fnd_msg_pub.initialize;
1403: END IF;
1397: ozf_utility_pvt.debug_message(l_full_name || ': start');
1398: END IF;
1399: x_return_status := fnd_api.g_ret_sts_success;
1400:
1401: IF fnd_api.to_boolean(p_init_msg_list) THEN
1402: fnd_msg_pub.initialize;
1403: END IF;
1404:
1405: OPEN c_get_metric_info;
1423: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCACTIVE_ERROR');
1424: END IF;
1425: FND_MSG_PUB.Add;
1426: END IF;
1427: x_return_status := FND_API.G_RET_STS_ERROR;
1428: RAISE FND_API.G_EXC_ERROR;
1429: END IF;
1430: l_alloc_fund_status := p_alloc_status;
1431: l_alloc_status := p_alloc_status;
1424: END IF;
1425: FND_MSG_PUB.Add;
1426: END IF;
1427: x_return_status := FND_API.G_RET_STS_ERROR;
1428: RAISE FND_API.G_EXC_ERROR;
1429: END IF;
1430: l_alloc_fund_status := p_alloc_status;
1431: l_alloc_status := p_alloc_status;
1432: ELSE
1467: , x_return_status => l_return_status
1468: , x_msg_count => x_msg_count
1469: , x_msg_data => x_msg_data
1470: );
1471: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1472: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1473: END IF;
1474: END IF;
1475: END IF; -- IF (l_alloc_status = 'ACTIVE') THEN
1468: , x_msg_count => x_msg_count
1469: , x_msg_data => x_msg_data
1470: );
1471: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1472: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1473: END IF;
1474: END IF;
1475: END IF; -- IF (l_alloc_status = 'ACTIVE') THEN
1476: ELSE
1564: , x_msg_data => x_msg_data
1565: );
1566: -- dbms_output.put_line(l_full_name || ' create fund for node ' || l_node_info.node_name || ' returns ' || l_return_status || ' new fund id=' || l_child_fund_id);
1567:
1568: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1569: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1570: END IF;
1571:
1572: l_index := l_index + 1;
1565: );
1566: -- dbms_output.put_line(l_full_name || ' create fund for node ' || l_node_info.node_name || ' returns ' || l_return_status || ' new fund id=' || l_child_fund_id);
1567:
1568: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1569: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1570: END IF;
1571:
1572: l_index := l_index + 1;
1573: l_fund_id_table(l_index).fact_id := l_worksheet_rec.activity_metric_fact_id;
1588: l_metric_fact_rec.actual_hb_amount := NVL(l_worksheet_rec.recommend_hb_amount, 0);
1589: l_metric_fact_rec.approval_date := sysdate;
1590: END IF;
1591: ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
1592: , p_init_msg_list => FND_API.G_FALSE
1593: , p_commit => FND_API.G_FALSE
1594: , p_validation_level => p_validation_level
1595: , x_return_status => l_return_status
1596: , x_msg_count => x_msg_count
1589: l_metric_fact_rec.approval_date := sysdate;
1590: END IF;
1591: ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
1592: , p_init_msg_list => FND_API.G_FALSE
1593: , p_commit => FND_API.G_FALSE
1594: , p_validation_level => p_validation_level
1595: , x_return_status => l_return_status
1596: , x_msg_count => x_msg_count
1597: , x_msg_data => x_msg_data
1597: , x_msg_data => x_msg_data
1598: , p_act_metric_fact_rec => l_metric_fact_rec
1599: );
1600: -- dbms_output.put_line(l_full_name || 'update metric fact status ' || l_return_status);
1601: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1602: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1603: END IF;
1604:
1605: /* send notification to child budget owner */
1598: , p_act_metric_fact_rec => l_metric_fact_rec
1599: );
1600: -- dbms_output.put_line(l_full_name || 'update metric fact status ' || l_return_status);
1601: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1602: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1603: END IF;
1604:
1605: /* send notification to child budget owner */
1606: IF l_fund_type = 'QUOTA' THEN
1633: , x_notif_id => l_return_id
1634: , x_return_status => l_return_status
1635: );
1636: -- dbms_output.put_line(l_full_name || 'send notificaiton returns ' || l_return_status);
1637: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1638: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1639: END IF;
1640:
1641: END LOOP;
1634: , x_return_status => l_return_status
1635: );
1636: -- dbms_output.put_line(l_full_name || 'send notificaiton returns ' || l_return_status);
1637: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1638: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1639: END IF;
1640:
1641: END LOOP;
1642:
1654: so validation will fail. should < jtf_plsql_api.G_VALID_LEVEL_RECORD CONSTANT NUMBER:= 80.
1655: set to 70 here.
1656: */
1657: Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
1658: , p_init_msg_list => FND_API.G_FALSE
1659: , p_commit => FND_API.G_FALSE
1660: , p_validation_level => 70
1661: , x_return_status => l_return_status
1662: , x_msg_count => x_msg_count
1655: set to 70 here.
1656: */
1657: Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
1658: , p_init_msg_list => FND_API.G_FALSE
1659: , p_commit => FND_API.G_FALSE
1660: , p_validation_level => 70
1661: , x_return_status => l_return_status
1662: , x_msg_count => x_msg_count
1663: , x_msg_data => x_msg_data
1663: , x_msg_data => x_msg_data
1664: , p_act_metric_rec => l_metric_rec
1665: );
1666: -- dbms_output.put_line(l_full_name || 'set new alloc status returns ' || l_return_status);
1667: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1668: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1669: END IF;
1670:
1671: IF p_commit = FND_API.G_TRUE THEN
1664: , p_act_metric_rec => l_metric_rec
1665: );
1666: -- dbms_output.put_line(l_full_name || 'set new alloc status returns ' || l_return_status);
1667: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1668: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1669: END IF;
1670:
1671: IF p_commit = FND_API.G_TRUE THEN
1672: commit;
1667: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1668: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1669: END IF;
1670:
1671: IF p_commit = FND_API.G_TRUE THEN
1672: commit;
1673: END IF;
1674:
1675: EXCEPTION
1675: EXCEPTION
1676: WHEN OTHERS THEN
1677: -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
1678: ROLLBACK TO publish_allocation_sp;
1679: x_return_status := fnd_api.g_ret_sts_unexp_error;
1680: fnd_msg_pub.count_and_get(
1681: p_encoded => fnd_api.g_false
1682: ,p_count => x_msg_count
1683: ,p_data => x_msg_data);
1677: -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
1678: ROLLBACK TO publish_allocation_sp;
1679: x_return_status := fnd_api.g_ret_sts_unexp_error;
1680: fnd_msg_pub.count_and_get(
1681: p_encoded => fnd_api.g_false
1682: ,p_count => x_msg_count
1683: ,p_data => x_msg_data);
1684:
1685: END publish_allocation;
1705: FROM ozf_funds_all_b
1706: WHERE FUND_id = p_fund_id ;
1707:
1708: BEGIN
1709: x_return_status := fnd_api.g_ret_sts_success;
1710:
1711: OPEN c_get_fund_amount;
1712: FETCH c_get_fund_amount INTO l_avail_amount;
1713: CLOSE c_get_fund_amount;
1720: FND_MESSAGE.Set_Token('NODEVALUE', 'FUND');
1721: FND_MESSAGE.Set_Token('PAMT', l_avail_amount);
1722: FND_MSG_PUB.Add;
1723: END IF;
1724: x_return_status := FND_API.G_RET_STS_ERROR;
1725: RAISE FND_API.G_EXC_ERROR;
1726: END IF;
1727:
1728: IF p_hb_amount > p_alloc_amount THEN
1721: FND_MESSAGE.Set_Token('PAMT', l_avail_amount);
1722: FND_MSG_PUB.Add;
1723: END IF;
1724: x_return_status := FND_API.G_RET_STS_ERROR;
1725: RAISE FND_API.G_EXC_ERROR;
1726: END IF;
1727:
1728: IF p_hb_amount > p_alloc_amount THEN
1729: -- holdback amount can not exceed allocation amount
1732: FND_MESSAGE.Set_Token('HOLDBACK', p_hb_amount);
1733: FND_MESSAGE.Set_Token('ALLOCAMT', p_alloc_amount);
1734: FND_MSG_PUB.Add;
1735: END IF;
1736: x_return_status := FND_API.G_RET_STS_ERROR;
1737: RAISE FND_API.G_EXC_ERROR;
1738: END IF;
1739:
1740: EXCEPTION
1733: FND_MESSAGE.Set_Token('ALLOCAMT', p_alloc_amount);
1734: FND_MSG_PUB.Add;
1735: END IF;
1736: x_return_status := FND_API.G_RET_STS_ERROR;
1737: RAISE FND_API.G_EXC_ERROR;
1738: END IF;
1739:
1740: EXCEPTION
1741: WHEN OTHERS THEN
1738: END IF;
1739:
1740: EXCEPTION
1741: WHEN OTHERS THEN
1742: x_return_status := fnd_api.g_ret_sts_unexp_error;
1743: fnd_msg_pub.count_and_get(
1744: p_encoded => fnd_api.g_false
1745: ,p_count => x_msg_count
1746: ,p_data => x_msg_data);
1740: EXCEPTION
1741: WHEN OTHERS THEN
1742: x_return_status := fnd_api.g_ret_sts_unexp_error;
1743: fnd_msg_pub.count_and_get(
1744: p_encoded => fnd_api.g_false
1745: ,p_count => x_msg_count
1746: ,p_data => x_msg_data);
1747: END check_budget_available_amount;
1748:
1784: l_return_status VARCHAR2(2);
1785: l_fact_rec ozf_actmetricfact_Pvt.act_metric_fact_rec_type;
1786:
1787: BEGIN
1788: x_return_status := fnd_api.g_ret_sts_success;
1789:
1790: OPEN c_get_alloc_info;
1791: FETCH c_get_alloc_info INTO l_alloc_from_level;
1792: CLOSE c_get_alloc_info;
1823: l_fact_rec.base_hb_pct := l_new_hb_pct;
1824: -- update table ozf_act_metric_facts
1825: ozf_actmetricfact_Pvt.update_actmetricfact(
1826: p_api_version => p_api_version,
1827: p_init_msg_list => fnd_api.g_false,
1828: p_commit => fnd_api.g_false,
1829: p_validation_level => fnd_api.g_valid_level_full,
1830: x_return_status => l_return_status,
1831: x_msg_count => x_msg_count,
1824: -- update table ozf_act_metric_facts
1825: ozf_actmetricfact_Pvt.update_actmetricfact(
1826: p_api_version => p_api_version,
1827: p_init_msg_list => fnd_api.g_false,
1828: p_commit => fnd_api.g_false,
1829: p_validation_level => fnd_api.g_valid_level_full,
1830: x_return_status => l_return_status,
1831: x_msg_count => x_msg_count,
1832: x_msg_data => x_msg_data,
1825: ozf_actmetricfact_Pvt.update_actmetricfact(
1826: p_api_version => p_api_version,
1827: p_init_msg_list => fnd_api.g_false,
1828: p_commit => fnd_api.g_false,
1829: p_validation_level => fnd_api.g_valid_level_full,
1830: x_return_status => l_return_status,
1831: x_msg_count => x_msg_count,
1832: x_msg_data => x_msg_data,
1833: p_act_metric_fact_rec => l_fact_rec
1832: x_msg_data => x_msg_data,
1833: p_act_metric_fact_rec => l_fact_rec
1834: );
1835: -- dbms_output.put_line('update fact percentage ' || l_fact_rec.activity_metric_fact_id || ' returns ' || l_return_status);
1836: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1837: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1838: END IF;
1839: END IF;
1840: END LOOP;
1833: p_act_metric_fact_rec => l_fact_rec
1834: );
1835: -- dbms_output.put_line('update fact percentage ' || l_fact_rec.activity_metric_fact_id || ' returns ' || l_return_status);
1836: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1837: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1838: END IF;
1839: END IF;
1840: END LOOP;
1841:
1840: END LOOP;
1841:
1842: EXCEPTION
1843: WHEN OTHERS THEN
1844: x_return_status := fnd_api.g_ret_sts_unexp_error;
1845: fnd_msg_pub.count_and_get(
1846: p_encoded => fnd_api.g_false
1847: , p_count => x_msg_count
1848: , p_data => x_msg_data);
1842: EXCEPTION
1843: WHEN OTHERS THEN
1844: x_return_status := fnd_api.g_ret_sts_unexp_error;
1845: fnd_msg_pub.count_and_get(
1846: p_encoded => fnd_api.g_false
1847: , p_count => x_msg_count
1848: , p_data => x_msg_data);
1849: END update_basepct_info;
1850:
1864: l_index NUMBER;
1865: l_sorted_fact_ids factLevelTableType;
1866: l_tmp_fact_ids factLevelTableType;
1867: BEGIN
1868: x_return_status := fnd_api.g_ret_sts_success;
1869:
1870: IF p_fact_table.FIRST IS NULL THEN
1871: RETURN;
1872: END IF;
1907: x_sorted_fact_ids := l_sorted_fact_ids;
1908:
1909: EXCEPTION
1910: WHEN OTHERS THEN
1911: x_return_status := fnd_api.g_ret_sts_unexp_error;
1912: FND_MSG_PUB.Count_And_Get (
1913: p_encoded => fnd_api.g_false,
1914: p_count => x_msg_count,
1915: p_data => x_msg_data
1909: EXCEPTION
1910: WHEN OTHERS THEN
1911: x_return_status := fnd_api.g_ret_sts_unexp_error;
1912: FND_MSG_PUB.Count_And_Get (
1913: p_encoded => fnd_api.g_false,
1914: p_count => x_msg_count,
1915: p_data => x_msg_data
1916: );
1917: END sort_changes_by_level;
1991: FROM ozf_act_metric_facts_all
1992: WHERE activity_metric_fact_id = l_fact_id);
1993:
1994: BEGIN
1995: x_return_status := fnd_api.g_ret_sts_success;
1996:
1997: -- validate the new recommended amount
1998: OPEN c_get_subtree_root;
1999: FETCH c_get_subtree_root INTO l_parent_fact_id, l_recommend_total, l_recommend_hb, l_fund_id;
2008: FND_MESSAGE.Set_Token('HOLDBACK', l_recommend_hb);
2009: FND_MESSAGE.Set_Token('ALLOCAMT', l_recommend_total);
2010: FND_MSG_PUB.Add;
2011: END IF;
2012: x_return_status := FND_API.G_RET_STS_ERROR;
2013: RAISE FND_API.G_EXC_ERROR;
2014: END IF;
2015:
2016: -- sum(sibling allocation amount) can not exceed parent allocation down amount
2009: FND_MESSAGE.Set_Token('ALLOCAMT', l_recommend_total);
2010: FND_MSG_PUB.Add;
2011: END IF;
2012: x_return_status := FND_API.G_RET_STS_ERROR;
2013: RAISE FND_API.G_EXC_ERROR;
2014: END IF;
2015:
2016: -- sum(sibling allocation amount) can not exceed parent allocation down amount
2017: OPEN c_get_child_sum(l_parent_fact_id);
2038: FND_MESSAGE.Set_Token('NODEVALUE', l_node_info.node_name);
2039: FND_MESSAGE.Set_Token('PAMT', l_parent_amount);
2040: FND_MSG_PUB.Add;
2041: END IF;
2042: x_return_status := FND_API.G_RET_STS_ERROR;
2043: RAISE FND_API.G_EXC_ERROR;
2044: END IF;
2045: END IF; -- IF l_parent_fact_id IS NOT NULL THEN
2046:
2039: FND_MESSAGE.Set_Token('PAMT', l_parent_amount);
2040: FND_MSG_PUB.Add;
2041: END IF;
2042: x_return_status := FND_API.G_RET_STS_ERROR;
2043: RAISE FND_API.G_EXC_ERROR;
2044: END IF;
2045: END IF; -- IF l_parent_fact_id IS NOT NULL THEN
2046:
2047: -- cascade changes
2056: l_fact_rec.recommend_total_amount := l_parent_amount * worksheet_rec.base_total_pct / 100;
2057: l_fact_rec.recommend_hb_amount := l_fact_rec.recommend_total_amount * worksheet_rec.base_hb_pct / 100;
2058: ozf_actmetricfact_Pvt.update_actmetricfact(
2059: p_api_version => p_api_version,
2060: p_init_msg_list => fnd_api.g_false,
2061: p_commit => fnd_api.g_false,
2062: p_validation_level => fnd_api.g_valid_level_full,
2063: x_return_status => l_return_status,
2064: x_msg_count => x_msg_count,
2057: l_fact_rec.recommend_hb_amount := l_fact_rec.recommend_total_amount * worksheet_rec.base_hb_pct / 100;
2058: ozf_actmetricfact_Pvt.update_actmetricfact(
2059: p_api_version => p_api_version,
2060: p_init_msg_list => fnd_api.g_false,
2061: p_commit => fnd_api.g_false,
2062: p_validation_level => fnd_api.g_valid_level_full,
2063: x_return_status => l_return_status,
2064: x_msg_count => x_msg_count,
2065: x_msg_data => x_msg_data,
2058: ozf_actmetricfact_Pvt.update_actmetricfact(
2059: p_api_version => p_api_version,
2060: p_init_msg_list => fnd_api.g_false,
2061: p_commit => fnd_api.g_false,
2062: p_validation_level => fnd_api.g_valid_level_full,
2063: x_return_status => l_return_status,
2064: x_msg_count => x_msg_count,
2065: x_msg_data => x_msg_data,
2066: p_act_metric_fact_rec => l_fact_rec
2065: x_msg_data => x_msg_data,
2066: p_act_metric_fact_rec => l_fact_rec
2067: );
2068: -- dbms_output.put_line('cascade_down_subtree() update fact ' || l_fact_rec.activity_metric_fact_id || ' total=' || l_fact_rec.recommend_total_amount || ' holdback=' || l_fact_rec.recommend_hb_amount || ' returns ' || l_return_status);
2069: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2070: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2071: END IF;
2072:
2073: l_index := l_index + 1;
2066: p_act_metric_fact_rec => l_fact_rec
2067: );
2068: -- dbms_output.put_line('cascade_down_subtree() update fact ' || l_fact_rec.activity_metric_fact_id || ' total=' || l_fact_rec.recommend_total_amount || ' holdback=' || l_fact_rec.recommend_hb_amount || ' returns ' || l_return_status);
2069: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2070: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2071: END IF;
2072:
2073: l_index := l_index + 1;
2074: l_visited_node_set(l_index) := worksheet_rec.activity_metric_fact_id;
2083: CLOSE c_get_fund_info;
2084: l_fund_rec.original_budget := l_fact_rec.recommend_total_amount;
2085: l_fund_rec.holdback_amt := l_fact_rec.recommend_hb_amount;
2086: ozf_funds_pvt.update_fund( p_api_version => p_api_version
2087: , p_init_msg_list => fnd_api.g_false
2088: , p_commit => fnd_api.g_false
2089: /* yzhao: 12/17/2002 disable validation here since for cascading, parent budget got updated
2090: before child budget, which may cause amount validation failure
2091: , p_validation_level => fnd_api.g_valid_level_full
2084: l_fund_rec.original_budget := l_fact_rec.recommend_total_amount;
2085: l_fund_rec.holdback_amt := l_fact_rec.recommend_hb_amount;
2086: ozf_funds_pvt.update_fund( p_api_version => p_api_version
2087: , p_init_msg_list => fnd_api.g_false
2088: , p_commit => fnd_api.g_false
2089: /* yzhao: 12/17/2002 disable validation here since for cascading, parent budget got updated
2090: before child budget, which may cause amount validation failure
2091: , p_validation_level => fnd_api.g_valid_level_full
2092: */
2087: , p_init_msg_list => fnd_api.g_false
2088: , p_commit => fnd_api.g_false
2089: /* yzhao: 12/17/2002 disable validation here since for cascading, parent budget got updated
2090: before child budget, which may cause amount validation failure
2091: , p_validation_level => fnd_api.g_valid_level_full
2092: */
2093: , p_validation_level => fnd_api.g_valid_level_none
2094: , p_fund_rec => l_fund_rec
2095: , x_return_status => l_return_status
2089: /* yzhao: 12/17/2002 disable validation here since for cascading, parent budget got updated
2090: before child budget, which may cause amount validation failure
2091: , p_validation_level => fnd_api.g_valid_level_full
2092: */
2093: , p_validation_level => fnd_api.g_valid_level_none
2094: , p_fund_rec => l_fund_rec
2095: , x_return_status => l_return_status
2096: , x_msg_count => x_msg_count
2097: , x_msg_data => x_msg_data
2095: , x_return_status => l_return_status
2096: , x_msg_count => x_msg_count
2097: , x_msg_data => x_msg_data
2098: );
2099: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2100: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2101: END IF;
2102: END IF; -- IF p_alloc_status_code = 'PLANNED' THEN
2103:
2096: , x_msg_count => x_msg_count
2097: , x_msg_data => x_msg_data
2098: );
2099: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2100: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2101: END IF;
2102: END IF; -- IF p_alloc_status_code = 'PLANNED' THEN
2103:
2104: END LOOP;
2107: -- dbms_output.put_line('cascade_down_subtree: visited_node_set.COUNT=' || x_visited_node_set.COUNT);
2108:
2109: EXCEPTION
2110: WHEN OTHERS THEN
2111: x_return_status := fnd_api.g_ret_sts_unexp_error;
2112: FND_MSG_PUB.Count_And_Get (
2113: p_encoded => fnd_api.g_false,
2114: p_count => x_msg_count,
2115: p_data => x_msg_data
2109: EXCEPTION
2110: WHEN OTHERS THEN
2111: x_return_status := fnd_api.g_ret_sts_unexp_error;
2112: FND_MSG_PUB.Count_And_Get (
2113: p_encoded => fnd_api.g_false,
2114: p_count => x_msg_count,
2115: p_data => x_msg_data
2116: );
2117: END cascade_down_subtree;
2172: IF G_DEBUG THEN
2173: OZF_Utility_PVT.debug_message('Validate_worksheet: start');
2174: END IF;
2175:
2176: IF FND_API.To_Boolean (p_init_msg_list) THEN
2177: FND_MSG_PUB.Initialize;
2178: END IF;
2179: x_return_status := fnd_api.g_ret_sts_success;
2180:
2175:
2176: IF FND_API.To_Boolean (p_init_msg_list) THEN
2177: FND_MSG_PUB.Initialize;
2178: END IF;
2179: x_return_status := fnd_api.g_ret_sts_success;
2180:
2181: OPEN c_get_alloc_info;
2182: FETCH c_get_alloc_info INTO l_fund_id, l_alloc_from_level, l_alloc_status_code;
2183: CLOSE c_get_alloc_info;
2195: , p_hb_amount => worksheet_rec.recommend_hb_amount
2196: , x_return_status => l_return_status
2197: , x_msg_count => x_msg_count
2198: , x_msg_data => x_msg_data);
2199: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2200: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2201: END IF;
2202: ELSE
2203: -- for each node: holdback amount can not exceed allocation amount
2196: , x_return_status => l_return_status
2197: , x_msg_count => x_msg_count
2198: , x_msg_data => x_msg_data);
2199: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2200: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2201: END IF;
2202: ELSE
2203: -- for each node: holdback amount can not exceed allocation amount
2204: IF worksheet_rec.recommend_hb_amount > worksheet_rec.recommend_total_amount THEN
2207: FND_MESSAGE.Set_Token('HOLDBACK', worksheet_rec.recommend_hb_amount);
2208: FND_MESSAGE.Set_Token('ALLOCAMT', worksheet_rec.recommend_total_amount);
2209: FND_MSG_PUB.Add;
2210: END IF;
2211: x_return_status := FND_API.G_RET_STS_ERROR;
2212: RAISE FND_API.G_EXC_ERROR;
2213: END IF;
2214: END IF; -- IF l_worksheet_rec.level_depth = l_alloc_from_level THEN
2215:
2208: FND_MESSAGE.Set_Token('ALLOCAMT', worksheet_rec.recommend_total_amount);
2209: FND_MSG_PUB.Add;
2210: END IF;
2211: x_return_status := FND_API.G_RET_STS_ERROR;
2212: RAISE FND_API.G_EXC_ERROR;
2213: END IF;
2214: END IF; -- IF l_worksheet_rec.level_depth = l_alloc_from_level THEN
2215:
2216: -- for each node: check its allocation down amount against its children's allocation sum
2234: FND_MESSAGE.Set_Token('NODEVALUE', l_node_info.node_name);
2235: FND_MESSAGE.Set_Token('PAMT', l_avail_amount);
2236: FND_MSG_PUB.Add;
2237: END IF;
2238: x_return_status := FND_API.G_RET_STS_ERROR;
2239: RAISE FND_API.G_EXC_ERROR;
2240: END IF;
2241: END LOOP;
2242:
2235: FND_MESSAGE.Set_Token('PAMT', l_avail_amount);
2236: FND_MSG_PUB.Add;
2237: END IF;
2238: x_return_status := FND_API.G_RET_STS_ERROR;
2239: RAISE FND_API.G_EXC_ERROR;
2240: END IF;
2241: END LOOP;
2242:
2243: EXCEPTION
2241: END LOOP;
2242:
2243: EXCEPTION
2244: WHEN OTHERS THEN
2245: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2246: FND_MSG_PUB.Count_And_Get (
2247: p_encoded => fnd_api.g_false,
2248: p_count => x_msg_count,
2249: p_data => x_msg_data
2243: EXCEPTION
2244: WHEN OTHERS THEN
2245: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2246: FND_MSG_PUB.Count_And_Get (
2247: p_encoded => fnd_api.g_false,
2248: p_count => x_msg_count,
2249: p_data => x_msg_data
2250: );
2251: END validate_worksheet;
2313:
2314: IF G_DEBUG THEN
2315: ozf_utility_pvt.debug_message(l_full_name || ': start');
2316: END IF;
2317: x_return_status := fnd_api.g_ret_sts_success;
2318:
2319: IF fnd_api.to_boolean(p_init_msg_list) THEN
2320: fnd_msg_pub.initialize;
2321: END IF;
2315: ozf_utility_pvt.debug_message(l_full_name || ': start');
2316: END IF;
2317: x_return_status := fnd_api.g_ret_sts_success;
2318:
2319: IF fnd_api.to_boolean(p_init_msg_list) THEN
2320: fnd_msg_pub.initialize;
2321: END IF;
2322:
2323: OPEN c_get_alloc_info;
2338: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2339: fnd_message.set_name('OZF', 'OZF_FUND_ALLOCAMT_NEG_ERROR');
2340: fnd_msg_pub.add;
2341: END IF;
2342: x_return_status := fnd_api.g_ret_sts_error;
2343: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2344: END IF;
2345:
2346: -- kdass: 01/23/2004 fix bug 3392738 - the allocation worksheet should not accept negative amounts for holdback amounts
2339: fnd_message.set_name('OZF', 'OZF_FUND_ALLOCAMT_NEG_ERROR');
2340: fnd_msg_pub.add;
2341: END IF;
2342: x_return_status := fnd_api.g_ret_sts_error;
2343: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2344: END IF;
2345:
2346: -- kdass: 01/23/2004 fix bug 3392738 - the allocation worksheet should not accept negative amounts for holdback amounts
2347: IF NVL(p_fact_table(l_index).recommend_hb_amount,0) < 0 THEN -- check for recommended holdback amount
2348: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2349: fnd_message.set_name('OZF', 'OZF_FUND_NO_HOLDBACK_BUDGET');
2350: fnd_msg_pub.add;
2351: END IF;
2352: x_return_status := fnd_api.g_ret_sts_error;
2353: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2354: END IF;
2355:
2356: IF p_fact_table(l_index).node_id = l_alloc_start_node THEN
2349: fnd_message.set_name('OZF', 'OZF_FUND_NO_HOLDBACK_BUDGET');
2350: fnd_msg_pub.add;
2351: END IF;
2352: x_return_status := fnd_api.g_ret_sts_error;
2353: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2354: END IF;
2355:
2356: IF p_fact_table(l_index).node_id = l_alloc_start_node THEN
2357: -- start node
2362: , p_hb_amount => p_fact_table(l_index).recommend_hb_amount
2363: , x_return_status => l_return_status
2364: , x_msg_count => x_msg_count
2365: , x_msg_data => x_msg_data);
2366: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2367: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2368: END IF;
2369:
2370: IF l_alloc_amount <> p_fact_table(l_index).recommend_total_amount THEN
2363: , x_return_status => l_return_status
2364: , x_msg_count => x_msg_count
2365: , x_msg_data => x_msg_data);
2366: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2367: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2368: END IF;
2369:
2370: IF l_alloc_amount <> p_fact_table(l_index).recommend_total_amount THEN
2371: -- top level's total amount is changed, need to update metric record
2378: so validation will fail. should < jtf_plsql_api.G_VALID_LEVEL_RECORD CONSTANT NUMBER:= 80.
2379: set to 70 here.
2380: */
2381: Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2382: , p_init_msg_list => FND_API.G_FALSE
2383: , p_commit => FND_API.G_FALSE
2384: , p_validation_level => 70
2385: , x_return_status => l_return_status
2386: , x_msg_count => x_msg_count
2379: set to 70 here.
2380: */
2381: Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2382: , p_init_msg_list => FND_API.G_FALSE
2383: , p_commit => FND_API.G_FALSE
2384: , p_validation_level => 70
2385: , x_return_status => l_return_status
2386: , x_msg_count => x_msg_count
2387: , x_msg_data => x_msg_data
2387: , x_msg_data => x_msg_data
2388: , p_act_metric_rec => l_metric_rec
2389: );
2390: -- dbms_output.put_line(l_full_name || 'update allocation amount returns ' || l_return_status);
2391: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2392: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2393: END IF;
2394: END IF; -- IF l_alloc_amount <> p_fact_table(l_index).recommend_total_amount THEN
2395: END IF; -- IF p_fact_table(l_index).node_id = l_alloc_start_node THEN
2388: , p_act_metric_rec => l_metric_rec
2389: );
2390: -- dbms_output.put_line(l_full_name || 'update allocation amount returns ' || l_return_status);
2391: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2392: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2393: END IF;
2394: END IF; -- IF l_alloc_amount <> p_fact_table(l_index).recommend_total_amount THEN
2395: END IF; -- IF p_fact_table(l_index).node_id = l_alloc_start_node THEN
2396:
2396:
2397: -- update table ozf_act_metric_facts
2398: ozf_actmetricfact_Pvt.update_actmetricfact(
2399: p_api_version => p_api_version,
2400: p_init_msg_list => fnd_api.g_false,
2401: p_commit => fnd_api.g_false,
2402: p_validation_level => fnd_api.g_valid_level_full,
2403: x_return_status => l_return_status,
2404: x_msg_count => x_msg_count,
2397: -- update table ozf_act_metric_facts
2398: ozf_actmetricfact_Pvt.update_actmetricfact(
2399: p_api_version => p_api_version,
2400: p_init_msg_list => fnd_api.g_false,
2401: p_commit => fnd_api.g_false,
2402: p_validation_level => fnd_api.g_valid_level_full,
2403: x_return_status => l_return_status,
2404: x_msg_count => x_msg_count,
2405: x_msg_data => x_msg_data,
2398: ozf_actmetricfact_Pvt.update_actmetricfact(
2399: p_api_version => p_api_version,
2400: p_init_msg_list => fnd_api.g_false,
2401: p_commit => fnd_api.g_false,
2402: p_validation_level => fnd_api.g_valid_level_full,
2403: x_return_status => l_return_status,
2404: x_msg_count => x_msg_count,
2405: x_msg_data => x_msg_data,
2406: p_act_metric_fact_rec => l_fact_rec
2405: x_msg_data => x_msg_data,
2406: p_act_metric_fact_rec => l_fact_rec
2407: );
2408: -- dbms_output.put_line(l_full_name || 'update fact ' || l_index || ' returns ' || l_return_status);
2409: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2410: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2411: END IF;
2412: END LOOP;
2413:
2406: p_act_metric_fact_rec => l_fact_rec
2407: );
2408: -- dbms_output.put_line(l_full_name || 'update fact ' || l_index || ' returns ' || l_return_status);
2409: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2410: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2411: END IF;
2412: END LOOP;
2413:
2414: IF p_cascade_flag = 'N' THEN
2413:
2414: IF p_cascade_flag = 'N' THEN
2415: -- validate worksheet update if it's not cascade
2416: validate_worksheet(p_api_version => p_api_version,
2417: p_init_msg_list => fnd_api.g_false,
2418: p_commit => fnd_api.g_false,
2419: p_validation_level => fnd_api.g_valid_level_full,
2420: p_alloc_id => p_alloc_id,
2421: x_return_status => l_return_status,
2414: IF p_cascade_flag = 'N' THEN
2415: -- validate worksheet update if it's not cascade
2416: validate_worksheet(p_api_version => p_api_version,
2417: p_init_msg_list => fnd_api.g_false,
2418: p_commit => fnd_api.g_false,
2419: p_validation_level => fnd_api.g_valid_level_full,
2420: p_alloc_id => p_alloc_id,
2421: x_return_status => l_return_status,
2422: x_msg_count => x_msg_count,
2415: -- validate worksheet update if it's not cascade
2416: validate_worksheet(p_api_version => p_api_version,
2417: p_init_msg_list => fnd_api.g_false,
2418: p_commit => fnd_api.g_false,
2419: p_validation_level => fnd_api.g_valid_level_full,
2420: p_alloc_id => p_alloc_id,
2421: x_return_status => l_return_status,
2422: x_msg_count => x_msg_count,
2423: x_msg_data => x_msg_data);
2421: x_return_status => l_return_status,
2422: x_msg_count => x_msg_count,
2423: x_msg_data => x_msg_data);
2424: -- dbms_output.put_line(l_full_name || 'validate_worksheet returns ' || l_return_status);
2425: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2426: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2427: END IF;
2428:
2429: IF l_alloc_status_code = 'PLANNED' AND
2422: x_msg_count => x_msg_count,
2423: x_msg_data => x_msg_data);
2424: -- dbms_output.put_line(l_full_name || 'validate_worksheet returns ' || l_return_status);
2425: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2426: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2427: END IF;
2428:
2429: IF l_alloc_status_code = 'PLANNED' AND
2430: l_alloc_action_code = 'CREATE_NEW_BUDGET' THEN
2437: CLOSE c_get_fund_info;
2438: l_fund_rec.original_budget := p_fact_table(l_index).recommend_total_amount;
2439: l_fund_rec.holdback_amt := p_fact_table(l_index).recommend_hb_amount;
2440: ozf_funds_pvt.update_fund( p_api_version => p_api_version
2441: , p_init_msg_list => fnd_api.g_false
2442: , p_commit => fnd_api.g_false
2443: , p_validation_level => fnd_api.g_valid_level_full
2444: , p_fund_rec => l_fund_rec
2445: , x_return_status => l_return_status
2438: l_fund_rec.original_budget := p_fact_table(l_index).recommend_total_amount;
2439: l_fund_rec.holdback_amt := p_fact_table(l_index).recommend_hb_amount;
2440: ozf_funds_pvt.update_fund( p_api_version => p_api_version
2441: , p_init_msg_list => fnd_api.g_false
2442: , p_commit => fnd_api.g_false
2443: , p_validation_level => fnd_api.g_valid_level_full
2444: , p_fund_rec => l_fund_rec
2445: , x_return_status => l_return_status
2446: , x_msg_count => x_msg_count
2439: l_fund_rec.holdback_amt := p_fact_table(l_index).recommend_hb_amount;
2440: ozf_funds_pvt.update_fund( p_api_version => p_api_version
2441: , p_init_msg_list => fnd_api.g_false
2442: , p_commit => fnd_api.g_false
2443: , p_validation_level => fnd_api.g_valid_level_full
2444: , p_fund_rec => l_fund_rec
2445: , x_return_status => l_return_status
2446: , x_msg_count => x_msg_count
2447: , x_msg_data => x_msg_data
2445: , x_return_status => l_return_status
2446: , x_msg_count => x_msg_count
2447: , x_msg_data => x_msg_data
2448: );
2449: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2450: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2451: END IF;
2452: END LOOP;
2453: END IF;
2446: , x_msg_count => x_msg_count
2447: , x_msg_data => x_msg_data
2448: );
2449: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2450: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2451: END IF;
2452: END LOOP;
2453: END IF;
2454:
2459: x_return_status => l_return_status,
2460: x_msg_count => x_msg_count,
2461: x_msg_data => x_msg_data);
2462: -- dbms_output.put_line(l_full_name || ' sort fact changes by level returns ' || l_return_status);
2463: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2464: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2465: END IF;
2466:
2467: -- cascade recommend total and holdback amount changes.
2460: x_msg_count => x_msg_count,
2461: x_msg_data => x_msg_data);
2462: -- dbms_output.put_line(l_full_name || ' sort fact changes by level returns ' || l_return_status);
2463: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2464: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2465: END IF;
2466:
2467: -- cascade recommend total and holdback amount changes.
2468: -- start from the toppest level. If update multiple levels under the same node, higher level change superceds lower level change
2486: x_return_status => l_return_status,
2487: x_msg_count => x_msg_count,
2488: x_msg_data => x_msg_data);
2489: -- dbms_output.put_line(l_full_name || ' cascade down subtree returns ' || l_return_status || ' subtree node count=' || l_new_visited_node_set.COUNT);
2490: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2491: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2492: END IF;
2493:
2494: l_index := NVL(l_visited_node_set.LAST, 0);
2487: x_msg_count => x_msg_count,
2488: x_msg_data => x_msg_data);
2489: -- dbms_output.put_line(l_full_name || ' cascade down subtree returns ' || l_return_status || ' subtree node count=' || l_new_visited_node_set.COUNT);
2490: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2491: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2492: END IF;
2493:
2494: l_index := NVL(l_visited_node_set.LAST, 0);
2495: FOR J IN NVL(l_new_visited_node_set.FIRST, 1) .. NVL(l_new_visited_node_set.LAST, -1) LOOP
2508: x_return_status => l_return_status,
2509: x_msg_count => x_msg_count,
2510: x_msg_data => x_msg_data);
2511: -- dbms_output.put_line(l_full_name || 'update worksheet percentage returns ' || l_return_status);
2512: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2513: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2514: END IF;
2515:
2516: IF p_commit = FND_API.G_TRUE THEN
2509: x_msg_count => x_msg_count,
2510: x_msg_data => x_msg_data);
2511: -- dbms_output.put_line(l_full_name || 'update worksheet percentage returns ' || l_return_status);
2512: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2513: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2514: END IF;
2515:
2516: IF p_commit = FND_API.G_TRUE THEN
2517: commit;
2512: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2513: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2514: END IF;
2515:
2516: IF p_commit = FND_API.G_TRUE THEN
2517: commit;
2518: END IF;
2519:
2520: EXCEPTION
2520: EXCEPTION
2521: WHEN OTHERS THEN
2522: ROLLBACK TO update_worksheet_amount_sp;
2523: -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
2524: x_return_status := fnd_api.g_ret_sts_unexp_error;
2525: fnd_msg_pub.count_and_get(
2526: p_encoded => fnd_api.g_false
2527: , p_count => x_msg_count
2528: , p_data => x_msg_data);
2522: ROLLBACK TO update_worksheet_amount_sp;
2523: -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
2524: x_return_status := fnd_api.g_ret_sts_unexp_error;
2525: fnd_msg_pub.count_and_get(
2526: p_encoded => fnd_api.g_false
2527: , p_count => x_msg_count
2528: , p_data => x_msg_data);
2529: END update_worksheet_amount;
2530:
2588: l_metric_fact_rec.actual_total_amount := p_approved_total;
2589: l_metric_fact_rec.actual_hb_amount := NVL(p_approved_hb, 0);
2590: l_metric_fact_rec.approval_date := sysdate;
2591: ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
2592: , p_init_msg_list => fnd_api.g_true
2593: , p_commit => fnd_api.g_false
2594: , p_validation_level => fnd_api.g_valid_level_full
2595: , p_act_metric_fact_rec => l_metric_fact_rec
2596: , x_return_status => x_return_status
2589: l_metric_fact_rec.actual_hb_amount := NVL(p_approved_hb, 0);
2590: l_metric_fact_rec.approval_date := sysdate;
2591: ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
2592: , p_init_msg_list => fnd_api.g_true
2593: , p_commit => fnd_api.g_false
2594: , p_validation_level => fnd_api.g_valid_level_full
2595: , p_act_metric_fact_rec => l_metric_fact_rec
2596: , x_return_status => x_return_status
2597: , x_msg_count => x_msg_count
2590: l_metric_fact_rec.approval_date := sysdate;
2591: ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
2592: , p_init_msg_list => fnd_api.g_true
2593: , p_commit => fnd_api.g_false
2594: , p_validation_level => fnd_api.g_valid_level_full
2595: , p_act_metric_fact_rec => l_metric_fact_rec
2596: , x_return_status => x_return_status
2597: , x_msg_count => x_msg_count
2598: , x_msg_data => x_msg_data
2597: , x_msg_count => x_msg_count
2598: , x_msg_data => x_msg_data
2599: );
2600: -- dbms_output.put_line('activate_one_node: update child node fact id=' || p_child_fact_id || ' active returns ' || x_return_status);
2601: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2602: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2603: END IF;
2604:
2605: OPEN c_get_child_info;
2598: , x_msg_data => x_msg_data
2599: );
2600: -- dbms_output.put_line('activate_one_node: update child node fact id=' || p_child_fact_id || ' active returns ' || x_return_status);
2601: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2602: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2603: END IF;
2604:
2605: OPEN c_get_child_info;
2606: FETCH c_get_child_info INTO l_child_fund_id, l_child_fund_obj_ver, l_child_fund_owner, l_child_fund_name
2614: l_fund_rec.original_budget := p_approved_total;
2615: l_fund_rec.holdback_amt := p_approved_hb;
2616: l_fund_rec.status_code := 'ACTIVE';
2617: ozf_funds_pvt.update_fund( p_api_version => p_api_version
2618: , p_init_msg_list => fnd_api.g_true
2619: , p_commit => fnd_api.g_false
2620: , p_validation_level => fnd_api.g_valid_level_full
2621: , p_fund_rec => l_fund_rec
2622: , x_return_status => x_return_status
2615: l_fund_rec.holdback_amt := p_approved_hb;
2616: l_fund_rec.status_code := 'ACTIVE';
2617: ozf_funds_pvt.update_fund( p_api_version => p_api_version
2618: , p_init_msg_list => fnd_api.g_true
2619: , p_commit => fnd_api.g_false
2620: , p_validation_level => fnd_api.g_valid_level_full
2621: , p_fund_rec => l_fund_rec
2622: , x_return_status => x_return_status
2623: , x_msg_count => x_msg_count
2616: l_fund_rec.status_code := 'ACTIVE';
2617: ozf_funds_pvt.update_fund( p_api_version => p_api_version
2618: , p_init_msg_list => fnd_api.g_true
2619: , p_commit => fnd_api.g_false
2620: , p_validation_level => fnd_api.g_valid_level_full
2621: , p_fund_rec => l_fund_rec
2622: , x_return_status => x_return_status
2623: , x_msg_count => x_msg_count
2624: , x_msg_data => x_msg_data
2623: , x_msg_count => x_msg_count
2624: , x_msg_data => x_msg_data
2625: );
2626: -- dbms_output.put_line('activate_one_node: update_fund to active returns ' || x_return_status || ' budget id=' || l_fund_rec.fund_id || ' request_amount=' || p_approved_total);
2627: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2628: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2629: END IF;
2630: ELSIF (p_alloc_action_code = 'TRANSFER_TO_BUDGET') THEN
2631: -- create an approved budget transfer from parent to child
2624: , x_msg_data => x_msg_data
2625: );
2626: -- dbms_output.put_line('activate_one_node: update_fund to active returns ' || x_return_status || ' budget id=' || l_fund_rec.fund_id || ' request_amount=' || p_approved_total);
2627: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2628: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2629: END IF;
2630: ELSIF (p_alloc_action_code = 'TRANSFER_TO_BUDGET') THEN
2631: -- create an approved budget transfer from parent to child
2632: transfer_approved_budget(p_api_version => p_api_version
2635: , x_return_status => x_return_status
2636: , x_msg_count => x_msg_count
2637: , x_msg_data => x_msg_data
2638: );
2639: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2640: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2641: END IF;
2642: END IF;
2643:
2636: , x_msg_count => x_msg_count
2637: , x_msg_data => x_msg_data
2638: );
2639: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2640: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2641: END IF;
2642: END IF;
2643:
2644: /* send notification to child budget owner */
2675: , x_notif_id => l_return_id
2676: , x_return_status => x_return_status
2677: );
2678: -- dbms_output.put_line('activate_one_node: send notificaiton returns ' || x_return_status);
2679: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2680: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2681: END IF;
2682:
2683: EXCEPTION
2676: , x_return_status => x_return_status
2677: );
2678: -- dbms_output.put_line('activate_one_node: send notificaiton returns ' || x_return_status);
2679: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2680: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2681: END IF;
2682:
2683: EXCEPTION
2684: WHEN OTHERS THEN
2682:
2683: EXCEPTION
2684: WHEN OTHERS THEN
2685: -- dbms_output.put_line('activate_one_node: exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
2686: x_return_status := fnd_api.g_ret_sts_unexp_error;
2687: fnd_msg_pub.count_and_get(
2688: p_encoded => fnd_api.g_false
2689: , p_count => x_msg_count
2690: , p_data => x_msg_data);
2684: WHEN OTHERS THEN
2685: -- dbms_output.put_line('activate_one_node: exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
2686: x_return_status := fnd_api.g_ret_sts_unexp_error;
2687: fnd_msg_pub.count_and_get(
2688: p_encoded => fnd_api.g_false
2689: , p_count => x_msg_count
2690: , p_data => x_msg_data);
2691: END activate_one_node;
2692:
2760: BEGIN
2761: IF G_DEBUG THEN
2762: ozf_utility_pvt.debug_message(l_full_name || ': start');
2763: END IF;
2764: x_return_status := fnd_api.g_ret_sts_success;
2765:
2766: OPEN c_get_metric_info;
2767: FETCH c_get_metric_info INTO l_fund_id, l_alloc_creator, l_alloc_old_status, l_alloc_action_code, l_ex_start_node;
2768: CLOSE c_get_metric_info;
2780: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCNOTOWNER_ERROR');
2781: END IF;
2782: FND_MSG_PUB.Add;
2783: END IF;
2784: x_return_status := FND_API.G_RET_STS_ERROR;
2785: RAISE FND_API.G_EXC_ERROR;
2786: END IF;
2787:
2788: -- statuses other than PLANNED are not allowed changed to ACTIVE
2781: END IF;
2782: FND_MSG_PUB.Add;
2783: END IF;
2784: x_return_status := FND_API.G_RET_STS_ERROR;
2785: RAISE FND_API.G_EXC_ERROR;
2786: END IF;
2787:
2788: -- statuses other than PLANNED are not allowed changed to ACTIVE
2789: IF l_alloc_old_status <> 'PLANNED' THEN
2790: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2791: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCSTATUS_ERROR');
2792: FND_MSG_PUB.Add;
2793: END IF;
2794: x_return_status := FND_API.G_RET_STS_ERROR;
2795: RAISE FND_API.G_EXC_ERROR;
2796: END IF;
2797:
2798:
2791: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCSTATUS_ERROR');
2792: FND_MSG_PUB.Add;
2793: END IF;
2794: x_return_status := FND_API.G_RET_STS_ERROR;
2795: RAISE FND_API.G_EXC_ERROR;
2796: END IF;
2797:
2798:
2799:
2806: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCACTIVE_ERROR');
2807: END IF;
2808: FND_MSG_PUB.Add;
2809: END IF;
2810: x_return_status := FND_API.G_RET_STS_ERROR;
2811: RAISE FND_API.G_EXC_ERROR;
2812: END IF;
2813:
2814: /* allocation status 'PLANNED' -> 'ACTIVE' */
2807: END IF;
2808: FND_MSG_PUB.Add;
2809: END IF;
2810: x_return_status := FND_API.G_RET_STS_ERROR;
2811: RAISE FND_API.G_EXC_ERROR;
2812: END IF;
2813:
2814: /* allocation status 'PLANNED' -> 'ACTIVE' */
2815: Ozf_Actmetric_Pvt.Init_ActMetric_Rec(l_metric_rec);
2817: l_metric_rec.status_code := 'ACTIVE';
2818: l_metric_rec.object_version_number := p_alloc_obj_ver;
2819: l_metric_rec.act_metric_date := sysdate;
2820: Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2821: , p_init_msg_list => fnd_api.g_false
2822: , p_commit => fnd_api.g_false
2823: , p_validation_level => fnd_api.g_valid_level_full
2824: , x_return_status => x_return_status
2825: , x_msg_count => x_msg_count
2818: l_metric_rec.object_version_number := p_alloc_obj_ver;
2819: l_metric_rec.act_metric_date := sysdate;
2820: Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2821: , p_init_msg_list => fnd_api.g_false
2822: , p_commit => fnd_api.g_false
2823: , p_validation_level => fnd_api.g_valid_level_full
2824: , x_return_status => x_return_status
2825: , x_msg_count => x_msg_count
2826: , x_msg_data => x_msg_data
2819: l_metric_rec.act_metric_date := sysdate;
2820: Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2821: , p_init_msg_list => fnd_api.g_false
2822: , p_commit => fnd_api.g_false
2823: , p_validation_level => fnd_api.g_valid_level_full
2824: , x_return_status => x_return_status
2825: , x_msg_count => x_msg_count
2826: , x_msg_data => x_msg_data
2827: , p_act_metric_rec => l_metric_rec
2826: , x_msg_data => x_msg_data
2827: , p_act_metric_rec => l_metric_rec
2828: );
2829: -- dbms_output.put_line(l_full_name || 'set alloc status ACTIVE returns ' || x_return_status);
2830: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2831: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2832: END IF;
2833:
2834: l_fact_id := NULL;
2827: , p_act_metric_rec => l_metric_rec
2828: );
2829: -- dbms_output.put_line(l_full_name || 'set alloc status ACTIVE returns ' || x_return_status);
2830: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2831: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2832: END IF;
2833:
2834: l_fact_id := NULL;
2835: l_fact_obj_ver := NULL;
2850: x_return_status => x_return_status,
2851: x_msg_count => x_msg_count,
2852: x_msg_data => x_msg_data);
2853: -- dbms_output.put_line(l_full_name || 'activate top level node returns ' || x_return_status);
2854: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2855: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2856: END IF;
2857: ELSE
2858: -- start node is excluded, so activate all nodes in the top level
2851: x_msg_count => x_msg_count,
2852: x_msg_data => x_msg_data);
2853: -- dbms_output.put_line(l_full_name || 'activate top level node returns ' || x_return_status);
2854: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2855: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2856: END IF;
2857: ELSE
2858: -- start node is excluded, so activate all nodes in the top level
2859: FOR fact_rec IN c_get_alloc_toplevels(l_fact_id) LOOP
2868: x_return_status => x_return_status,
2869: x_msg_count => x_msg_count,
2870: x_msg_data => x_msg_data);
2871: -- dbms_output.put_line(l_full_name || 'activate top level node returns ' || x_return_status);
2872: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2873: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2874: END IF;
2875: END LOOP;
2876: END IF;
2869: x_msg_count => x_msg_count,
2870: x_msg_data => x_msg_data);
2871: -- dbms_output.put_line(l_full_name || 'activate top level node returns ' || x_return_status);
2872: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2873: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2874: END IF;
2875: END LOOP;
2876: END IF;
2877:
2877:
2878: EXCEPTION
2879: WHEN OTHERS THEN
2880: -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
2881: x_return_status := fnd_api.g_ret_sts_unexp_error;
2882: fnd_msg_pub.count_and_get(
2883: p_encoded => fnd_api.g_false
2884: , p_count => x_msg_count
2885: , p_data => x_msg_data);
2879: WHEN OTHERS THEN
2880: -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
2881: x_return_status := fnd_api.g_ret_sts_unexp_error;
2882: fnd_msg_pub.count_and_get(
2883: p_encoded => fnd_api.g_false
2884: , p_count => x_msg_count
2885: , p_data => x_msg_data);
2886: END activate_allocation;
2887:
2948: BEGIN
2949: IF G_DEBUG THEN
2950: ozf_utility_pvt.debug_message(l_full_name || ': start');
2951: END IF;
2952: x_return_status := fnd_api.g_ret_sts_success;
2953:
2954: OPEN c_get_metric_info;
2955: FETCH c_get_metric_info INTO l_alloc_creator, l_alloc_old_status, l_alloc_action_code, l_root_fund_id;
2956: CLOSE c_get_metric_info;
2969: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCNOTOWNER_ERROR');
2970: END IF;
2971: FND_MSG_PUB.Add;
2972: END IF;
2973: x_return_status := FND_API.G_RET_STS_ERROR;
2974: RAISE FND_API.G_EXC_ERROR;
2975: END IF;
2976:
2977: -- statuses other than NEW or PLANNED are not allowed changed to CANCELLED
2970: END IF;
2971: FND_MSG_PUB.Add;
2972: END IF;
2973: x_return_status := FND_API.G_RET_STS_ERROR;
2974: RAISE FND_API.G_EXC_ERROR;
2975: END IF;
2976:
2977: -- statuses other than NEW or PLANNED are not allowed changed to CANCELLED
2978: IF l_alloc_old_status <> 'NEW' AND l_alloc_old_status <> 'PLANNED' THEN
2979: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2980: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCSTATUS_ERROR');
2981: FND_MSG_PUB.Add;
2982: END IF;
2983: x_return_status := FND_API.G_RET_STS_ERROR;
2984: RAISE FND_API.G_EXC_ERROR;
2985: END IF;
2986:
2987: /* allocation status 'NEW' or 'PLANNED' -> 'CANCELLED' */
2980: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCSTATUS_ERROR');
2981: FND_MSG_PUB.Add;
2982: END IF;
2983: x_return_status := FND_API.G_RET_STS_ERROR;
2984: RAISE FND_API.G_EXC_ERROR;
2985: END IF;
2986:
2987: /* allocation status 'NEW' or 'PLANNED' -> 'CANCELLED' */
2988: Ozf_Actmetric_Pvt.Init_ActMetric_Rec(l_metric_rec);
2990: l_metric_rec.status_code := 'CANCELLED';
2991: l_metric_rec.object_version_number := p_alloc_obj_ver;
2992: l_metric_rec.act_metric_date := sysdate;
2993: Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2994: , p_init_msg_list => fnd_api.g_false
2995: , p_commit => fnd_api.g_false
2996: , p_validation_level => fnd_api.g_valid_level_full
2997: , x_return_status => x_return_status
2998: , x_msg_count => x_msg_count
2991: l_metric_rec.object_version_number := p_alloc_obj_ver;
2992: l_metric_rec.act_metric_date := sysdate;
2993: Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2994: , p_init_msg_list => fnd_api.g_false
2995: , p_commit => fnd_api.g_false
2996: , p_validation_level => fnd_api.g_valid_level_full
2997: , x_return_status => x_return_status
2998: , x_msg_count => x_msg_count
2999: , x_msg_data => x_msg_data
2992: l_metric_rec.act_metric_date := sysdate;
2993: Ozf_Actmetric_Pvt.update_Actmetric( p_api_version => p_api_version
2994: , p_init_msg_list => fnd_api.g_false
2995: , p_commit => fnd_api.g_false
2996: , p_validation_level => fnd_api.g_valid_level_full
2997: , x_return_status => x_return_status
2998: , x_msg_count => x_msg_count
2999: , x_msg_data => x_msg_data
3000: , p_act_metric_rec => l_metric_rec
2999: , x_msg_data => x_msg_data
3000: , p_act_metric_rec => l_metric_rec
3001: );
3002: -- dbms_output.put_line(l_full_name || 'set alloc status CANCELLED returns ' || x_return_status);
3003: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3004: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3005: END IF;
3006:
3007: FOR fact_rec IN c_get_all_facts LOOP
3000: , p_act_metric_rec => l_metric_rec
3001: );
3002: -- dbms_output.put_line(l_full_name || 'set alloc status CANCELLED returns ' || x_return_status);
3003: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3004: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3005: END IF;
3006:
3007: FOR fact_rec IN c_get_all_facts LOOP
3008: -- update child fact status as CANCELLED
3010: l_metric_fact_rec.activity_metric_fact_id := fact_rec.activity_metric_fact_id;
3011: l_metric_fact_rec.object_version_number := fact_rec.object_version_number;
3012: l_metric_fact_rec.status_code := 'CANCELLED';
3013: ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
3014: , p_init_msg_list => fnd_api.g_true
3015: , p_commit => fnd_api.g_false
3016: , p_validation_level => fnd_api.g_valid_level_full
3017: , p_act_metric_fact_rec => l_metric_fact_rec
3018: , x_return_status => x_return_status
3011: l_metric_fact_rec.object_version_number := fact_rec.object_version_number;
3012: l_metric_fact_rec.status_code := 'CANCELLED';
3013: ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
3014: , p_init_msg_list => fnd_api.g_true
3015: , p_commit => fnd_api.g_false
3016: , p_validation_level => fnd_api.g_valid_level_full
3017: , p_act_metric_fact_rec => l_metric_fact_rec
3018: , x_return_status => x_return_status
3019: , x_msg_count => x_msg_count
3012: l_metric_fact_rec.status_code := 'CANCELLED';
3013: ozf_actmetricfact_Pvt.Update_ActMetricFact( p_api_version => p_api_version
3014: , p_init_msg_list => fnd_api.g_true
3015: , p_commit => fnd_api.g_false
3016: , p_validation_level => fnd_api.g_valid_level_full
3017: , p_act_metric_fact_rec => l_metric_fact_rec
3018: , x_return_status => x_return_status
3019: , x_msg_count => x_msg_count
3020: , x_msg_data => x_msg_data
3019: , x_msg_count => x_msg_count
3020: , x_msg_data => x_msg_data
3021: );
3022: -- dbms_output.put_line(l_full_name || ': update child node fact id=' || fact_rec.activity_metric_fact_id || ' cancelled returns ' || x_return_status);
3023: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3024: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3025: END IF;
3026:
3027: -- if allocation already published, notify child budget owners
3020: , x_msg_data => x_msg_data
3021: );
3022: -- dbms_output.put_line(l_full_name || ': update child node fact id=' || fact_rec.activity_metric_fact_id || ' cancelled returns ' || x_return_status);
3023: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3024: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3025: END IF;
3026:
3027: -- if allocation already published, notify child budget owners
3028: -- if new budgets already created, update budgets as CANCELLED
3037: l_fund_rec.fund_id := fact_rec.act_metric_used_by_id;
3038: l_fund_rec.object_version_number := l_fund_obj_ver;
3039: l_fund_rec.status_code := 'CANCELLED';
3040: ozf_funds_pvt.update_fund( p_api_version => p_api_version
3041: , p_init_msg_list => fnd_api.g_true
3042: , p_commit => fnd_api.g_false
3043: , p_validation_level => fnd_api.g_valid_level_full
3044: , p_fund_rec => l_fund_rec
3045: , x_return_status => x_return_status
3038: l_fund_rec.object_version_number := l_fund_obj_ver;
3039: l_fund_rec.status_code := 'CANCELLED';
3040: ozf_funds_pvt.update_fund( p_api_version => p_api_version
3041: , p_init_msg_list => fnd_api.g_true
3042: , p_commit => fnd_api.g_false
3043: , p_validation_level => fnd_api.g_valid_level_full
3044: , p_fund_rec => l_fund_rec
3045: , x_return_status => x_return_status
3046: , x_msg_count => x_msg_count
3039: l_fund_rec.status_code := 'CANCELLED';
3040: ozf_funds_pvt.update_fund( p_api_version => p_api_version
3041: , p_init_msg_list => fnd_api.g_true
3042: , p_commit => fnd_api.g_false
3043: , p_validation_level => fnd_api.g_valid_level_full
3044: , p_fund_rec => l_fund_rec
3045: , x_return_status => x_return_status
3046: , x_msg_count => x_msg_count
3047: , x_msg_data => x_msg_data
3046: , x_msg_count => x_msg_count
3047: , x_msg_data => x_msg_data
3048: );
3049: -- dbms_output.put_line(l_full_name || ': update_fund to cancelled returns ' || x_return_status || ' budget id=' || l_fund_rec.fund_id);
3050: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3051: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3052: END IF;
3053: END IF;
3054:
3047: , x_msg_data => x_msg_data
3048: );
3049: -- dbms_output.put_line(l_full_name || ': update_fund to cancelled returns ' || x_return_status || ' budget id=' || l_fund_rec.fund_id);
3050: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3051: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3052: END IF;
3053: END IF;
3054:
3055: /* send notification to child budget owner */
3083: , x_notif_id => l_return_id
3084: , x_return_status => x_return_status
3085: );
3086: -- dbms_output.put_line(l_full_name || ': send notificaiton returns ' || x_return_status);
3087: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3088: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3089: END IF;
3090: END IF; -- IF (l_alloc_old_status == 'PLANNED') THEN
3091: END LOOP;
3084: , x_return_status => x_return_status
3085: );
3086: -- dbms_output.put_line(l_full_name || ': send notificaiton returns ' || x_return_status);
3087: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3088: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3089: END IF;
3090: END IF; -- IF (l_alloc_old_status == 'PLANNED') THEN
3091: END LOOP;
3092:
3092:
3093: EXCEPTION
3094: WHEN OTHERS THEN
3095: -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3096: x_return_status := fnd_api.g_ret_sts_unexp_error;
3097: fnd_msg_pub.count_and_get(
3098: p_encoded => fnd_api.g_false
3099: , p_count => x_msg_count
3100: , p_data => x_msg_data);
3094: WHEN OTHERS THEN
3095: -- dbms_output.put_line(l_full_name || 'exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3096: x_return_status := fnd_api.g_ret_sts_unexp_error;
3097: fnd_msg_pub.count_and_get(
3098: p_encoded => fnd_api.g_false
3099: , p_count => x_msg_count
3100: , p_data => x_msg_data);
3101: END cancel_allocation;
3102:
3135: FROM ozf_act_metrics_all
3136: WHERE activity_metric_id = p_alloc_id;
3137: BEGIN
3138: SAVEPOINT update_alloc_status_sp;
3139: x_return_status := FND_API.G_RET_STS_SUCCESS;
3140:
3141: IF fnd_api.to_boolean(p_init_msg_list) THEN
3142: fnd_msg_pub.initialize;
3143: END IF;
3137: BEGIN
3138: SAVEPOINT update_alloc_status_sp;
3139: x_return_status := FND_API.G_RET_STS_SUCCESS;
3140:
3141: IF fnd_api.to_boolean(p_init_msg_list) THEN
3142: fnd_msg_pub.initialize;
3143: END IF;
3144:
3145: OPEN c_get_alloc_status;
3191: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3192: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCSTATUS_ERROR');
3193: FND_MSG_PUB.Add;
3194: END IF;
3195: x_return_status := FND_API.G_RET_STS_ERROR;
3196: RAISE FND_API.G_EXC_ERROR;
3197: END IF;
3198:
3199: IF p_commit = FND_API.G_TRUE THEN
3192: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCSTATUS_ERROR');
3193: FND_MSG_PUB.Add;
3194: END IF;
3195: x_return_status := FND_API.G_RET_STS_ERROR;
3196: RAISE FND_API.G_EXC_ERROR;
3197: END IF;
3198:
3199: IF p_commit = FND_API.G_TRUE THEN
3200: commit;
3195: x_return_status := FND_API.G_RET_STS_ERROR;
3196: RAISE FND_API.G_EXC_ERROR;
3197: END IF;
3198:
3199: IF p_commit = FND_API.G_TRUE THEN
3200: commit;
3201: END IF;
3202:
3203: EXCEPTION
3202:
3203: EXCEPTION
3204: WHEN OTHERS THEN
3205: ROLLBACK TO update_alloc_status_sp;
3206: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3207: -- dbms_output.put_line('update_alloc_status: exception - ' || substr(sqlerrm, 1, 200));
3208: fnd_msg_pub.count_and_get(
3209: p_encoded => fnd_api.g_false
3210: , p_count => x_msg_count
3205: ROLLBACK TO update_alloc_status_sp;
3206: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3207: -- dbms_output.put_line('update_alloc_status: exception - ' || substr(sqlerrm, 1, 200));
3208: fnd_msg_pub.count_and_get(
3209: p_encoded => fnd_api.g_false
3210: , p_count => x_msg_count
3211: , p_data => x_msg_data);
3212: END update_alloc_status;
3213:
3287: SAVEPOINT approve_levels_sp;
3288: IF G_DEBUG THEN
3289: ozf_utility_pvt.debug_message('approve_levels(): start');
3290: END IF;
3291: x_return_status := fnd_api.g_ret_sts_success;
3292:
3293: IF p_factid_table.COUNT = 0 THEN
3294: RETURN;
3295: END IF;
3293: IF p_factid_table.COUNT = 0 THEN
3294: RETURN;
3295: END IF;
3296:
3297: IF fnd_api.to_boolean(p_init_msg_list) THEN
3298: fnd_msg_pub.initialize;
3299: END IF;
3300:
3301: OPEN c_get_alloc_status(p_factid_table(p_factid_table.FIRST).fact_id);
3314: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3315: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCAPPROVE_ERROR');
3316: FND_MSG_PUB.Add;
3317: END IF;
3318: x_return_status := FND_API.G_RET_STS_ERROR;
3319: RAISE FND_API.G_EXC_ERROR;
3320: END IF;
3321:
3322: FOR I IN p_factid_table.FIRST .. p_factid_table.LAST LOOP
3315: FND_MESSAGE.Set_Name ('OZF', 'OZF_FUND_ALLOCAPPROVE_ERROR');
3316: FND_MSG_PUB.Add;
3317: END IF;
3318: x_return_status := FND_API.G_RET_STS_ERROR;
3319: RAISE FND_API.G_EXC_ERROR;
3320: END IF;
3321:
3322: FOR I IN p_factid_table.FIRST .. p_factid_table.LAST LOOP
3323: l_fact_status := NULL;
3347: x_return_status => x_return_status,
3348: x_msg_count => x_msg_count,
3349: x_msg_data => x_msg_data);
3350: -- dbms_output.put_line('approve_levels(): one node ' || p_factid_table(I).fact_id || ' returns ' || x_return_status);
3351: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3352: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3353: END IF;
3354:
3355: l_fact_status := 'ACTIVE';
3348: x_msg_count => x_msg_count,
3349: x_msg_data => x_msg_data);
3350: -- dbms_output.put_line('approve_levels(): one node ' || p_factid_table(I).fact_id || ' returns ' || x_return_status);
3351: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3352: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3353: END IF;
3354:
3355: l_fact_status := 'ACTIVE';
3356: END IF;
3375: p_alloc_action_code => l_alloc_action_code,
3376: x_return_status => x_return_status,
3377: x_msg_count => x_msg_count,
3378: x_msg_data => x_msg_data);
3379: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3380: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3381: END IF;
3382: END IF;
3383: END LOOP;
3376: x_return_status => x_return_status,
3377: x_msg_count => x_msg_count,
3378: x_msg_data => x_msg_data);
3379: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3380: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3381: END IF;
3382: END IF;
3383: END LOOP;
3384: END IF;
3387: EXCEPTION
3388: WHEN OTHERS THEN
3389: -- dbms_output.put_line('approve_levels(): exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3390: ROLLBACK TO approve_levels_sp;
3391: x_return_status := fnd_api.g_ret_sts_unexp_error;
3392: fnd_msg_pub.count_and_get(
3393: p_encoded => fnd_api.g_false
3394: , p_count => x_msg_count
3395: , p_data => x_msg_data);
3389: -- dbms_output.put_line('approve_levels(): exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3390: ROLLBACK TO approve_levels_sp;
3391: x_return_status := fnd_api.g_ret_sts_unexp_error;
3392: fnd_msg_pub.count_and_get(
3393: p_encoded => fnd_api.g_false
3394: , p_count => x_msg_count
3395: , p_data => x_msg_data);
3396: END approve_levels;
3397:
3463: SAVEPOINT submit_request_sp;
3464: IF G_DEBUG THEN
3465: ozf_utility_pvt.debug_message('submit_request(): start');
3466: END IF;
3467: x_return_status := fnd_api.g_ret_sts_success;
3468:
3469: IF fnd_api.to_boolean(p_init_msg_list) THEN
3470: fnd_msg_pub.initialize;
3471: END IF;
3465: ozf_utility_pvt.debug_message('submit_request(): start');
3466: END IF;
3467: x_return_status := fnd_api.g_ret_sts_success;
3468:
3469: IF fnd_api.to_boolean(p_init_msg_list) THEN
3470: fnd_msg_pub.initialize;
3471: END IF;
3472:
3473: OPEN c_get_fact_info;
3479: IF (l_alloc_status <> 'PLANNED' AND l_alloc_status <> 'REJECTED') THEN
3480: IF G_DEBUG THEN
3481: ozf_utility_pvt.debug_message('submit_request(): only planned or rejected can submit. status=' || l_alloc_status);
3482: END IF;
3483: RAISE FND_API.G_EXC_ERROR;
3484: END IF;
3485:
3486: -- update this node allocation status as 'SUBMITTED'
3487: Ozf_Actmetricfact_Pvt.Init_ActMetricFact_Rec(x_fact_rec => l_fact_rec);
3490: l_fact_rec.status_code := 'SUBMITTED';
3491: -- update table ozf_act_metric_facts
3492: ozf_actmetricfact_Pvt.update_actmetricfact(
3493: p_api_version => p_api_version,
3494: p_init_msg_list => fnd_api.g_false,
3495: p_commit => fnd_api.g_false,
3496: p_validation_level => fnd_api.g_valid_level_full,
3497: x_return_status => l_return_status,
3498: x_msg_count => x_msg_count,
3491: -- update table ozf_act_metric_facts
3492: ozf_actmetricfact_Pvt.update_actmetricfact(
3493: p_api_version => p_api_version,
3494: p_init_msg_list => fnd_api.g_false,
3495: p_commit => fnd_api.g_false,
3496: p_validation_level => fnd_api.g_valid_level_full,
3497: x_return_status => l_return_status,
3498: x_msg_count => x_msg_count,
3499: x_msg_data => x_msg_data,
3492: ozf_actmetricfact_Pvt.update_actmetricfact(
3493: p_api_version => p_api_version,
3494: p_init_msg_list => fnd_api.g_false,
3495: p_commit => fnd_api.g_false,
3496: p_validation_level => fnd_api.g_valid_level_full,
3497: x_return_status => l_return_status,
3498: x_msg_count => x_msg_count,
3499: x_msg_data => x_msg_data,
3500: p_act_metric_fact_rec => l_fact_rec
3499: x_msg_data => x_msg_data,
3500: p_act_metric_fact_rec => l_fact_rec
3501: );
3502: -- dbms_output.put_line('update fact percentage ' || l_fact_rec.activity_metric_fact_id || ' returns ' || l_return_status);
3503: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3504: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3505: END IF;
3506:
3507: -- send notification to parent budget owner
3500: p_act_metric_fact_rec => l_fact_rec
3501: );
3502: -- dbms_output.put_line('update fact percentage ' || l_fact_rec.activity_metric_fact_id || ' returns ' || l_return_status);
3503: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3504: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3505: END IF;
3506:
3507: -- send notification to parent budget owner
3508: IF l_parent_fact_id IS NULL THEN
3534: ,x_msg_count => x_msg_count
3535: ,x_msg_data => x_msg_data
3536: );
3537:
3538: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3539: RAISE fnd_api.g_exc_unexpected_error;
3540: END IF;
3541: END IF;
3542:
3535: ,x_msg_data => x_msg_data
3536: );
3537:
3538: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3539: RAISE fnd_api.g_exc_unexpected_error;
3540: END IF;
3541: END IF;
3542:
3543: /* send request notice to parent budget owner */
3574: , x_notif_id => l_return_id
3575: , x_return_status => l_return_status
3576: );
3577: -- dbms_output.put_line('submit_request(): send notificaiton returns ' || l_return_status);
3578: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3579: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3580: END IF;
3581:
3582: EXCEPTION
3575: , x_return_status => l_return_status
3576: );
3577: -- dbms_output.put_line('submit_request(): send notificaiton returns ' || l_return_status);
3578: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3579: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3580: END IF;
3581:
3582: EXCEPTION
3583: WHEN OTHERS THEN
3582: EXCEPTION
3583: WHEN OTHERS THEN
3584: -- dbms_output.put_line('submit_request(): exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3585: ROLLBACK TO submit_request_sp;
3586: x_return_status := fnd_api.g_ret_sts_unexp_error;
3587: fnd_msg_pub.count_and_get(
3588: p_encoded => fnd_api.g_false
3589: , p_count => x_msg_count
3590: , p_data => x_msg_data);
3584: -- dbms_output.put_line('submit_request(): exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3585: ROLLBACK TO submit_request_sp;
3586: x_return_status := fnd_api.g_ret_sts_unexp_error;
3587: fnd_msg_pub.count_and_get(
3588: p_encoded => fnd_api.g_false
3589: , p_count => x_msg_count
3590: , p_data => x_msg_data);
3591: END submit_request;
3592:
3641: SAVEPOINT reject_request_sp;
3642: IF G_DEBUG THEN
3643: ozf_utility_pvt.debug_message('reject_request(): start');
3644: END IF;
3645: x_return_status := fnd_api.g_ret_sts_success;
3646:
3647: IF fnd_api.to_boolean(p_init_msg_list) THEN
3648: fnd_msg_pub.initialize;
3649: END IF;
3643: ozf_utility_pvt.debug_message('reject_request(): start');
3644: END IF;
3645: x_return_status := fnd_api.g_ret_sts_success;
3646:
3647: IF fnd_api.to_boolean(p_init_msg_list) THEN
3648: fnd_msg_pub.initialize;
3649: END IF;
3650:
3651: /* only allocation in 'PLANNED' or 'ACTIVE' status can user reject request
3670: l_fact_rec.status_code := 'REJECTED';
3671: -- update table ozf_act_metric_facts
3672: ozf_actmetricfact_Pvt.update_actmetricfact(
3673: p_api_version => p_api_version,
3674: p_init_msg_list => fnd_api.g_false,
3675: p_commit => fnd_api.g_false,
3676: p_validation_level => fnd_api.g_valid_level_full,
3677: x_return_status => l_return_status,
3678: x_msg_count => x_msg_count,
3671: -- update table ozf_act_metric_facts
3672: ozf_actmetricfact_Pvt.update_actmetricfact(
3673: p_api_version => p_api_version,
3674: p_init_msg_list => fnd_api.g_false,
3675: p_commit => fnd_api.g_false,
3676: p_validation_level => fnd_api.g_valid_level_full,
3677: x_return_status => l_return_status,
3678: x_msg_count => x_msg_count,
3679: x_msg_data => x_msg_data,
3672: ozf_actmetricfact_Pvt.update_actmetricfact(
3673: p_api_version => p_api_version,
3674: p_init_msg_list => fnd_api.g_false,
3675: p_commit => fnd_api.g_false,
3676: p_validation_level => fnd_api.g_valid_level_full,
3677: x_return_status => l_return_status,
3678: x_msg_count => x_msg_count,
3679: x_msg_data => x_msg_data,
3680: p_act_metric_fact_rec => l_fact_rec
3679: x_msg_data => x_msg_data,
3680: p_act_metric_fact_rec => l_fact_rec
3681: );
3682: -- dbms_output.put_line('update fact as rejected ' || l_fact_rec.activity_metric_fact_id || ' returns ' || l_return_status);
3683: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3684: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3685: END IF;
3686:
3687: -- send notification to child budget owner of rejection
3680: p_act_metric_fact_rec => l_fact_rec
3681: );
3682: -- dbms_output.put_line('update fact as rejected ' || l_fact_rec.activity_metric_fact_id || ' returns ' || l_return_status);
3683: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3684: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3685: END IF;
3686:
3687: -- send notification to child budget owner of rejection
3688: IF l_fund_type = 'QUOTA' THEN
3717: , x_notif_id => l_return_id
3718: , x_return_status => l_return_status
3719: );
3720: -- dbms_output.put_line('reject_request(): send notificaiton returns ' || l_return_status);
3721: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3722: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3723: END IF;
3724: END IF;
3725: END LOOP;
3718: , x_return_status => l_return_status
3719: );
3720: -- dbms_output.put_line('reject_request(): send notificaiton returns ' || l_return_status);
3721: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3722: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3723: END IF;
3724: END IF;
3725: END LOOP;
3726:
3727: EXCEPTION
3728: WHEN OTHERS THEN
3729: -- dbms_output.put_line('reject_request(): exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3730: ROLLBACK TO reject_request_sp;
3731: x_return_status := fnd_api.g_ret_sts_unexp_error;
3732: fnd_msg_pub.count_and_get(
3733: p_encoded => fnd_api.g_false
3734: , p_count => x_msg_count
3735: , p_data => x_msg_data);
3729: -- dbms_output.put_line('reject_request(): exception errcode=' || sqlcode || ' :' || substr(sqlerrm, 1, 150));
3730: ROLLBACK TO reject_request_sp;
3731: x_return_status := fnd_api.g_ret_sts_unexp_error;
3732: fnd_msg_pub.count_and_get(
3733: p_encoded => fnd_api.g_false
3734: , p_count => x_msg_count
3735: , p_data => x_msg_data);
3736: END reject_request;
3737: