[Home] [Help]
58: OZF_UTILITY_PVT.debug_message('Entering ams_threshold_notify.Start_process : ');
59: END IF;
60:
61: -- Initialize API return status to SUCCESS
62: x_return_status := FND_API.G_RET_STS_SUCCESS;
63:
64: fnd_message.set_name('OZF', 'OZF_THRESHOLD_SUBJECT');
65: l_strSubject := fnd_message.get;
66: fnd_message.set_name('OZF', 'OZF_THRESHOLD_CHILDSUBJ');
79: ,x_notif_id => l_notification_id
80: ,x_return_status => l_return_status
81: );
82:
83: IF l_return_status <> fnd_api.g_ret_sts_success THEN
84: RAISE fnd_api.g_exc_error;
85: END IF;
86:
87:
80: ,x_return_status => l_return_status
81: );
82:
83: IF l_return_status <> fnd_api.g_ret_sts_success THEN
84: RAISE fnd_api.g_exc_error;
85: END IF;
86:
87:
88: IF p_parent_owner_id <>0 THEN
94: ,x_return_status => l_return_status
95: );
96: END IF;
97:
98: IF l_return_status <> fnd_api.g_ret_sts_success THEN
99: RAISE fnd_api.g_exc_error;
100: END IF;
101:
102: EXCEPTION
95: );
96: END IF;
97:
98: IF l_return_status <> fnd_api.g_ret_sts_success THEN
99: RAISE fnd_api.g_exc_error;
100: END IF;
101:
102: EXCEPTION
103:
100: END IF;
101:
102: EXCEPTION
103:
104: WHEN FND_API.G_EXC_ERROR THEN
105: x_return_status := FND_API.G_RET_STS_ERROR;
106: -- Standard call to get message count and if count=1, get the message
107: FND_MSG_PUB.Count_And_Get (
108: p_encoded => FND_API.G_FALSE,
101:
102: EXCEPTION
103:
104: WHEN FND_API.G_EXC_ERROR THEN
105: x_return_status := FND_API.G_RET_STS_ERROR;
106: -- Standard call to get message count and if count=1, get the message
107: FND_MSG_PUB.Count_And_Get (
108: p_encoded => FND_API.G_FALSE,
109: p_count => x_msg_count,
104: WHEN FND_API.G_EXC_ERROR THEN
105: x_return_status := FND_API.G_RET_STS_ERROR;
106: -- Standard call to get message count and if count=1, get the message
107: FND_MSG_PUB.Count_And_Get (
108: p_encoded => FND_API.G_FALSE,
109: p_count => x_msg_count,
110: p_data => x_msg_data
111: );
112:
109: p_count => x_msg_count,
110: p_data => x_msg_data
111: );
112:
113: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
114: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115: -- Standard call to get message count and if count=1, get the message
116: FND_MSG_PUB.Count_And_Get (
117: p_encoded => FND_API.G_FALSE,
110: p_data => x_msg_data
111: );
112:
113: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
114: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115: -- Standard call to get message count and if count=1, get the message
116: FND_MSG_PUB.Count_And_Get (
117: p_encoded => FND_API.G_FALSE,
118: p_count => x_msg_count,
113: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
114: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115: -- Standard call to get message count and if count=1, get the message
116: FND_MSG_PUB.Count_And_Get (
117: p_encoded => FND_API.G_FALSE,
118: p_count => x_msg_count,
119: p_data => x_msg_data
120: );
121:
119: p_data => x_msg_data
120: );
121:
122: WHEN OTHERS THEN
123: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
125: THEN
126: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
127: END IF;
126: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
127: END IF;
128: -- Standard call to get message count and if count=1, get the message
129: FND_MSG_PUB.Count_And_Get (
130: p_encoded => FND_API.G_FALSE,
131: p_count => x_msg_count,
132: p_data => x_msg_data
133: );
134: END start_process; /* START_PROCESS */
380: OPEN c_trans_seq;
381: FETCH c_trans_seq INTO l_trans_id;
382: CLOSE c_trans_seq;
383: -- Initialize API return status to SUCCESS
384: -- x_return_status := FND_API.G_RET_STS_SUCCESS;
385:
386: OPEN c_all_resources;
387: FETCH c_all_resources BULK COLLECT INTO l_resource_list;
388: CLOSE c_all_resources;
475: FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside Product loop - l_value_limit :' || l_value_limit );
476: FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside Product loop - l_base_line_amt :' || l_base_line_amt );
477: --l_base_line_amt is rhs for operation_result imput.
478: operation_result(l_api_version_number,
479: FND_API.G_FALSE,
480: l_Msg_Count,
481: l_Msg_Data,
482: l_return_status,
483: l_value_limit,
488: OZF_UTILITY_PVT.debug_message('l_operation_result: ' || l_operation_result);
489: END IF;
490: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_operation_result :' || l_operation_result );
491:
492: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
493: RAISE FND_API.G_EXC_ERROR;
494: END IF;
495:
496: IF l_operation_result = 'VIOLATED' THEN
489: END IF;
490: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_operation_result :' || l_operation_result );
491:
492: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
493: RAISE FND_API.G_EXC_ERROR;
494: END IF;
495:
496: IF l_operation_result = 'VIOLATED' THEN
497: update_alerts(l_api_version_number,
494: END IF;
495:
496: IF l_operation_result = 'VIOLATED' THEN
497: update_alerts(l_api_version_number,
498: FND_API.G_FALSE,
499: l_Msg_Count,
500: l_Msg_Data,
501: l_return_status,
502: budget.owner,
505: product.item_id,
506: budget.alert_type,
507: l_alert_str,
508: 0);
509: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
510: RAISE FND_API.G_EXC_ERROR;
511: END IF;
512: END IF;
513:
506: budget.alert_type,
507: l_alert_str,
508: 0);
509: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
510: RAISE FND_API.G_EXC_ERROR;
511: END IF;
512: END IF;
513:
514: END LOOP;
580: FND_FILE.PUT_LINE(FND_FILE.LOG,'Customer - l_base_line_amt :' || l_base_line_amt );
581:
582: --l_base_line_amt is rhs for operation_result imput.
583: operation_result(l_api_version_number,
584: FND_API.G_FALSE,
585: l_Msg_Count,
586: l_Msg_Data,
587: l_return_status,
588: l_value_limit,
593: OZF_UTILITY_PVT.debug_message('Operator: ' || l_operation_result);
594: END IF;
595: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_operation_result :' || l_operation_result );
596:
597: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
598: RAISE FND_API.G_EXC_ERROR;
599: END IF;
600:
601: IF l_operation_result = 'VIOLATED' THEN
594: END IF;
595: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_operation_result :' || l_operation_result );
596:
597: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
598: RAISE FND_API.G_EXC_ERROR;
599: END IF;
600:
601: IF l_operation_result = 'VIOLATED' THEN
602: update_alerts(l_api_version_number,
599: END IF;
600:
601: IF l_operation_result = 'VIOLATED' THEN
602: update_alerts(l_api_version_number,
603: FND_API.G_FALSE,
604: l_Msg_Count,
605: l_Msg_Data,
606: l_return_status,
607: budget.owner,
610: customer.ship_to_site_use_id,
611: budget.alert_type,
612: l_alert_str,
613: customer.cust_account_id);
614: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
615: RAISE FND_API.G_EXC_ERROR;
616: END IF;
617: END IF;
618:
611: budget.alert_type,
612: l_alert_str,
613: customer.cust_account_id);
614: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
615: RAISE FND_API.G_EXC_ERROR;
616: END IF;
617: END IF;
618:
619: END LOOP;
700: FND_FILE.PUT_LINE(FND_FILE.LOG,'Quota - l_base_line_amt :' || l_base_line_amt );
701:
702: --l_base_line_amt is rhs for operation_result imput.
703: operation_result(l_api_version_number,
704: FND_API.G_FALSE,
705: l_Msg_Count,
706: l_Msg_Data,
707: l_return_status,
708: l_value_limit,
714: END IF;
715: FND_FILE.PUT_LINE(FND_FILE.LOG,'budget.operator_code :' || budget.operator_code );
716: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_operation_result :' || l_operation_result );
717:
718: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
719: RAISE FND_API.G_EXC_ERROR;
720: END IF;
721:
722: IF l_operation_result = 'VIOLATED' THEN
715: FND_FILE.PUT_LINE(FND_FILE.LOG,'budget.operator_code :' || budget.operator_code );
716: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_operation_result :' || l_operation_result );
717:
718: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
719: RAISE FND_API.G_EXC_ERROR;
720: END IF;
721:
722: IF l_operation_result = 'VIOLATED' THEN
723: update_alerts(l_api_version_number,
720: END IF;
721:
722: IF l_operation_result = 'VIOLATED' THEN
723: update_alerts(l_api_version_number,
724: FND_API.G_FALSE,
725: l_Msg_Count,
726: l_Msg_Data,
727: l_return_status,
728: budget.owner,
731: l_alert_no,
732: budget.alert_type,
733: NULL,
734: 0);
735: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
736: RAISE FND_API.G_EXC_ERROR;
737: END IF;
738: l_operation_result_notify := 'VIOLATED';
739: OZF_UTILITY_PVT.debug_message(' budget.alert_type:' || budget.alert_type);
732: budget.alert_type,
733: NULL,
734: 0);
735: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
736: RAISE FND_API.G_EXC_ERROR;
737: END IF;
738: l_operation_result_notify := 'VIOLATED';
739: OZF_UTILITY_PVT.debug_message(' budget.alert_type:' || budget.alert_type);
740: OZF_UTILITY_PVT.debug_message(' l_alert_no:' ||l_alert_no );
749: else if the l_opearation_result is 'NOT VIOLATED' then we will not call verify_notification*/
750: FND_FILE.PUT_LINE(FND_FILE.LOG,' l_operation_result_notify : ' || l_operation_result_notify);
751: IF l_operation_result_notify = 'VIOLATED' THEN
752: verify_notification( l_api_version_number,
753: FND_API.G_FALSE,
754: l_Msg_Count,
755: l_Msg_Data,
756: l_return_status,
757: budget.budget_id,
761: budget.repeat_frequency,
762: budget.rule_start_date,
763: l_notification_result);
764:
765: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
766: RAISE FND_API.G_EXC_ERROR;
767: END IF;
768: FND_FILE.PUT_LINE(FND_FILE.LOG,' l_notification_result : ' || l_notification_result);
769: --l_notification_result will drive write_to_log
762: budget.rule_start_date,
763: l_notification_result);
764:
765: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
766: RAISE FND_API.G_EXC_ERROR;
767: END IF;
768: FND_FILE.PUT_LINE(FND_FILE.LOG,' l_notification_result : ' || l_notification_result);
769: --l_notification_result will drive write_to_log
770: IF G_DEBUG THEN
835: l_trans_id,
836: SYSDATE
837: );
838:
839: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
840: RAISE FND_API.G_EXC_ERROR;
841: END IF;
842: END IF; -- Notification result end if
843: END IF; -- operation result end if
836: SYSDATE
837: );
838:
839: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
840: RAISE FND_API.G_EXC_ERROR;
841: END IF;
842: END IF; -- Notification result end if
843: END IF; -- operation result end if
844:
925: l_notify_table(i).message_text,
926: l_log_id
927: );
928:
929: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
930: RAISE FND_API.G_EXC_ERROR;
931: END IF;
932:
933: END LOOP;
926: l_log_id
927: );
928:
929: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
930: RAISE FND_API.G_EXC_ERROR;
931: END IF;
932:
933: END LOOP;
934: END IF;
942:
943: ozf_utility_pvt.write_conc_log(l_msg_data);
944:
945: FND_FILE.PUT_LINE(FND_FILE.LOG,'End of quota validation : ' );
946: -- RAISE FND_API.G_EXC_ERROR;
947: EXCEPTION
948:
949: WHEN FND_API.G_EXC_ERROR THEN
950: --ROLLBACK TO validate_quota_threshold;
945: FND_FILE.PUT_LINE(FND_FILE.LOG,'End of quota validation : ' );
946: -- RAISE FND_API.G_EXC_ERROR;
947: EXCEPTION
948:
949: WHEN FND_API.G_EXC_ERROR THEN
950: --ROLLBACK TO validate_quota_threshold;
951: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception G_EXC_ERROR '||l_api_name);
952: x_retcode := 1;
953: x_errbuf := substr(l_msg_data,1,1000);
952: x_retcode := 1;
953: x_errbuf := substr(l_msg_data,1,1000);
954: ozf_utility_pvt.write_conc_log(l_msg_data);
955:
956: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
957: --ROLLBACK TO validate_quota_threshold;
958: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception G_EXC_UNEXPECTED_ERROR '||l_api_name);
959: x_retcode := 1;
960: x_errbuf := substr(l_msg_data,1,1000);
979:
980: -----------------------------------------------------------------------
981: PROCEDURE operation_result(
982: p_api_version_number IN NUMBER,
983: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
984: X_Msg_Count OUT NOCOPY NUMBER,
985: X_Msg_Data OUT NOCOPY VARCHAR2,
986: x_return_status OUT NOCOPY VARCHAR2,
987: p_lhs IN NUMBER,
994:
995:
996: BEGIN
997: -- Standard call to check for call compatibility.
998: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
999: p_api_version_number,
1000: l_api_name,
1001: G_PKG_NAME)
1002: THEN
999: p_api_version_number,
1000: l_api_name,
1001: G_PKG_NAME)
1002: THEN
1003: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004: END IF;
1005:
1006: -- Initialize message list if p_init_msg_list is set to TRUE.
1007: IF FND_API.to_Boolean( p_init_msg_list )
1003: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004: END IF;
1005:
1006: -- Initialize message list if p_init_msg_list is set to TRUE.
1007: IF FND_API.to_Boolean( p_init_msg_list )
1008: THEN
1009: FND_MSG_PUB.initialize;
1010: END IF;
1011:
1012: -- Debug Message
1013: --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1014:
1015: -- Initialize API return status to SUCCESS
1016: x_return_status := FND_API.G_RET_STS_SUCCESS;
1017:
1018: -- Derive the result based on operator_code
1019:
1020: IF p_operator_code = '2' THEN
1050: );
1051:
1052: EXCEPTION
1053:
1054: WHEN FND_API.G_EXC_ERROR THEN
1055: x_return_status := FND_API.G_RET_STS_ERROR;
1056: -- Standard call to get message count and if count=1, get the message
1057: FND_MSG_PUB.Count_And_Get (
1058: p_encoded => FND_API.G_FALSE,
1051:
1052: EXCEPTION
1053:
1054: WHEN FND_API.G_EXC_ERROR THEN
1055: x_return_status := FND_API.G_RET_STS_ERROR;
1056: -- Standard call to get message count and if count=1, get the message
1057: FND_MSG_PUB.Count_And_Get (
1058: p_encoded => FND_API.G_FALSE,
1059: p_count => x_msg_count,
1054: WHEN FND_API.G_EXC_ERROR THEN
1055: x_return_status := FND_API.G_RET_STS_ERROR;
1056: -- Standard call to get message count and if count=1, get the message
1057: FND_MSG_PUB.Count_And_Get (
1058: p_encoded => FND_API.G_FALSE,
1059: p_count => x_msg_count,
1060: p_data => x_msg_data
1061: );
1062:
1059: p_count => x_msg_count,
1060: p_data => x_msg_data
1061: );
1062:
1063: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1064: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065: -- Standard call to get message count and if count=1, get the message
1066: FND_MSG_PUB.Count_And_Get (
1067: p_encoded => FND_API.G_FALSE,
1060: p_data => x_msg_data
1061: );
1062:
1063: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1064: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065: -- Standard call to get message count and if count=1, get the message
1066: FND_MSG_PUB.Count_And_Get (
1067: p_encoded => FND_API.G_FALSE,
1068: p_count => x_msg_count,
1063: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1064: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065: -- Standard call to get message count and if count=1, get the message
1066: FND_MSG_PUB.Count_And_Get (
1067: p_encoded => FND_API.G_FALSE,
1068: p_count => x_msg_count,
1069: p_data => x_msg_data
1070: );
1071:
1069: p_data => x_msg_data
1070: );
1071:
1072: WHEN OTHERS THEN
1073: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1075: THEN
1076: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1077: END IF;
1076: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1077: END IF;
1078: -- Standard call to get message count and if count=1, get the message
1079: FND_MSG_PUB.Count_And_Get (
1080: p_encoded => FND_API.G_FALSE,
1081: p_count => x_msg_count,
1082: p_data => x_msg_data
1083: );
1084: END operation_result;
1086: -- PROCEDURE
1087: -- verify_notification
1088: -- In Parozf
1089: -- p_api_version_number IN NUMBER
1090: -- p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1091: -- p_budget_id IN NUMBER -- budget to which the threshold applies
1092: -- p_threshold_id IN NUMBER -- threshold_id
1093: -- p_threshold_rule_id IN NUMBER -- threhold_rule_id
1094: -- p_frequency_period IN VARCHAR2 -- MONTHLY or DAILY
1106:
1107: -----------------------------------------------------------------------
1108: PROCEDURE verify_notification(
1109: p_api_version_number IN NUMBER,
1110: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1111: X_Msg_Count OUT NOCOPY NUMBER,
1112: X_Msg_Data OUT NOCOPY VARCHAR2,
1113: x_return_status OUT NOCOPY VARCHAR2,
1114: p_budget_id IN NUMBER,
1136: AND threshold_id = x_threshold_id;
1137:
1138: BEGIN
1139: -- Standard call to check for call compatibility.
1140: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1141: p_api_version_number,
1142: l_api_name,
1143: G_PKG_NAME)
1144: THEN
1141: p_api_version_number,
1142: l_api_name,
1143: G_PKG_NAME)
1144: THEN
1145: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1146: END IF;
1147:
1148: -- Initialize message list if p_init_msg_list is set to TRUE.
1149: IF FND_API.to_Boolean( p_init_msg_list )
1145: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1146: END IF;
1147:
1148: -- Initialize message list if p_init_msg_list is set to TRUE.
1149: IF FND_API.to_Boolean( p_init_msg_list )
1150: THEN
1151: FND_MSG_PUB.initialize;
1152: END IF;
1153:
1155: --OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1156:
1157:
1158: -- Initialize API return status to SUCCESS
1159: x_return_status := FND_API.G_RET_STS_SUCCESS;
1160:
1161: IF p_frequency_period = 'DAILY' THEN
1162: l_notify_freq_days := p_repeat_frequency;
1163: END IF;
1207: p_data => x_msg_data
1208: );
1209: EXCEPTION
1210:
1211: WHEN FND_API.G_EXC_ERROR THEN
1212: x_return_status := FND_API.G_RET_STS_ERROR;
1213: -- Standard call to get message count and if count=1, get the message
1214: FND_MSG_PUB.Count_And_Get (
1215: p_encoded => FND_API.G_FALSE,
1208: );
1209: EXCEPTION
1210:
1211: WHEN FND_API.G_EXC_ERROR THEN
1212: x_return_status := FND_API.G_RET_STS_ERROR;
1213: -- Standard call to get message count and if count=1, get the message
1214: FND_MSG_PUB.Count_And_Get (
1215: p_encoded => FND_API.G_FALSE,
1216: p_count => x_msg_count,
1211: WHEN FND_API.G_EXC_ERROR THEN
1212: x_return_status := FND_API.G_RET_STS_ERROR;
1213: -- Standard call to get message count and if count=1, get the message
1214: FND_MSG_PUB.Count_And_Get (
1215: p_encoded => FND_API.G_FALSE,
1216: p_count => x_msg_count,
1217: p_data => x_msg_data
1218: );
1219:
1216: p_count => x_msg_count,
1217: p_data => x_msg_data
1218: );
1219:
1220: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1221: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1222: -- Standard call to get message count and if count=1, get the message
1223: FND_MSG_PUB.Count_And_Get (
1224: p_encoded => FND_API.G_FALSE,
1217: p_data => x_msg_data
1218: );
1219:
1220: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1221: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1222: -- Standard call to get message count and if count=1, get the message
1223: FND_MSG_PUB.Count_And_Get (
1224: p_encoded => FND_API.G_FALSE,
1225: p_count => x_msg_count,
1220: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1221: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1222: -- Standard call to get message count and if count=1, get the message
1223: FND_MSG_PUB.Count_And_Get (
1224: p_encoded => FND_API.G_FALSE,
1225: p_count => x_msg_count,
1226: p_data => x_msg_data
1227: );
1228:
1226: p_data => x_msg_data
1227: );
1228:
1229: WHEN OTHERS THEN
1230: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1231: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1232: THEN
1233: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1234: END IF;
1233: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1234: END IF;
1235: -- Standard call to get message count and if count=1, get the message
1236: FND_MSG_PUB.Count_And_Get (
1237: p_encoded => FND_API.G_FALSE,
1238: p_count => x_msg_count,
1239: p_data => x_msg_data
1240: );
1241: END verify_notification;
1241: END verify_notification;
1242:
1243: PROCEDURE update_alerts(
1244: p_api_version_number IN NUMBER,
1245: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1246: x_Msg_Count OUT NOCOPY NUMBER,
1247: x_Msg_Data OUT NOCOPY VARCHAR2,
1248: x_return_status OUT NOCOPY VARCHAR2,
1249: p_resource_id IN NUMBER,
1272: FROM DUAL;
1273:
1274: BEGIN
1275: -- Standard call to check for call compatibility.
1276: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1277: p_api_version_number,
1278: l_api_name,
1279: G_PKG_NAME)
1280: THEN
1277: p_api_version_number,
1278: l_api_name,
1279: G_PKG_NAME)
1280: THEN
1281: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1282: END IF;
1283:
1284: -- Initialize message list if p_init_msg_list is set to TRUE.
1285: IF FND_API.to_Boolean( p_init_msg_list )
1281: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1282: END IF;
1283:
1284: -- Initialize message list if p_init_msg_list is set to TRUE.
1285: IF FND_API.to_Boolean( p_init_msg_list )
1286: THEN
1287: FND_MSG_PUB.initialize;
1288: END IF;
1289:
1297: FND_FILE.PUT_LINE(FND_FILE.LOG,' p_select_attribute: '|| p_select_attribute);
1298: FND_FILE.PUT_LINE(FND_FILE.LOG,' p_cust_account_id: '|| p_cust_account_id);
1299:
1300: -- Initialize API return status to SUCCESS
1301: x_return_status := FND_API.G_RET_STS_SUCCESS;
1302: l_sql_stmt := '';
1303: l_sql_str := '';
1304: l_ins_csr := 0;
1305: l_ignore := 0;
1319: || 'AND product_attr_value = :3';
1320: EXECUTE IMMEDIATE l_sql_stmt
1321: INTO l_alert_type
1322: USING p_resource_id, p_product_attribute, p_attribute2;
1323: IF ( l_alert_type IS NULL OR l_alert_type = FND_API.G_MISS_CHAR OR l_alert_type = '') THEN
1324: l_new_alert_type := p_alert_type;
1325: l_update := TRUE;
1326: ELSIF l_alert_type = 'ACCEPTABLE' THEN
1327: IF (p_alert_type = 'WARNING' OR p_alert_type = 'UNACCEPTABLE' )THEN
1392: FND_DSQL.do_binds;
1393: l_ignore := DBMS_SQL.execute(l_ins_csr);
1394:
1395: WHEN OTHERS THEN
1396: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1397: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1398: THEN
1399: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1400: END IF;
1399: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1400: END IF;
1401: -- Standard call to get message count and if count=1, get the message
1402: FND_MSG_PUB.Count_And_Get (
1403: p_encoded => FND_API.G_FALSE,
1404: p_count => x_msg_count,
1405: p_data => x_msg_data
1406: );
1407: FND_FILE.PUT_LINE(FND_FILE.LOG,'PROD EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
1415: || 'AND ship_to_site_use_id = :3';
1416: EXECUTE IMMEDIATE l_sql_stmt
1417: INTO l_alert_type
1418: USING p_resource_id, p_cust_account_id, p_attribute2;
1419: IF ( l_alert_type IS NULL OR l_alert_type = FND_API.G_MISS_CHAR OR l_alert_type = '') THEN
1420: l_new_alert_type := p_alert_type;
1421: l_update := TRUE;
1422: ELSIF l_alert_type = 'ACCEPTABLE' THEN
1423: IF (p_alert_type = 'WARNING' OR p_alert_type = 'UNACCEPTABLE' )THEN
1489: FND_DSQL.do_binds;
1490: l_ignore := DBMS_SQL.execute(l_ins_csr);
1491:
1492: WHEN OTHERS THEN
1493: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1494: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1495: THEN
1496: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1497: END IF;
1496: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1497: END IF;
1498: -- Standard call to get message count and if count=1, get the message
1499: FND_MSG_PUB.Count_And_Get (
1500: p_encoded => FND_API.G_FALSE,
1501: p_count => x_msg_count,
1502: p_data => x_msg_data
1503: );
1504: FND_FILE.PUT_LINE(FND_FILE.LOG,'CUST EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
1512:
1513: EXECUTE IMMEDIATE l_sql_stmt
1514: INTO l_alert_type
1515: USING p_resource_id, p_attribute2;
1516: IF ( l_alert_type IS NULL OR l_alert_type = FND_API.G_MISS_CHAR OR l_alert_type = '') THEN
1517: l_new_alert_type := p_alert_type;
1518: l_update := TRUE;
1519: ELSIF l_alert_type = 'ACCEPTABLE' THEN
1520: IF (p_alert_type = 'WARNING' OR p_alert_type = 'UNACCEPTABLE' )THEN
1557:
1558: END IF;
1559: EXCEPTION
1560: WHEN NO_DATA_FOUND THEN
1561: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1562: x_msg_count := 1;
1563: x_msg_data := SUBSTR(SQLERRM, 1, 2000);
1564: FND_FILE.PUT_LINE(FND_FILE.LOG,'QUOTA EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
1565:
1563: x_msg_data := SUBSTR(SQLERRM, 1, 2000);
1564: FND_FILE.PUT_LINE(FND_FILE.LOG,'QUOTA EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
1565:
1566: WHEN OTHERS THEN
1567: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1568: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1569: THEN
1570: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1571: END IF;
1570: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1571: END IF;
1572: -- Standard call to get message count and if count=1, get the message
1573: FND_MSG_PUB.Count_And_Get (
1574: p_encoded => FND_API.G_FALSE,
1575: p_count => x_msg_count,
1576: p_data => x_msg_data
1577: );
1578: FND_FILE.PUT_LINE(FND_FILE.LOG,'QUOTA EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));
1588: p_data => x_msg_data
1589: );
1590: EXCEPTION
1591: WHEN OTHERS THEN
1592: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1593: IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1594: THEN
1595: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1596: END IF;
1595: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1596: END IF;
1597: -- Standard call to get message count and if count=1, get the message
1598: FND_MSG_PUB.Count_And_Get (
1599: p_encoded => FND_API.G_FALSE,
1600: p_count => x_msg_count,
1601: p_data => x_msg_data
1602: );
1603: FND_FILE.PUT_LINE(FND_FILE.LOG,'update_alerts EXCEPTION : '|| SUBSTR(SQLERRM, 1, 2000));