[Home] [Help]
617:
618: WHEN OTHERS THEN
619:
620: -- store SQL error message on message stack for caller
621: OKL_API.SET_MESSAGE (
622: p_app_name => G_APP_NAME
623: ,p_msg_name => G_UNEXPECTED_ERROR
624: ,p_token1 => G_SQLCODE_TOKEN
625: ,p_token1_value => sqlcode
662: FROM okl_trx_types_tl t
663: WHERE Upper (t.name) LIKE Upper (cp_try_name)
664: AND t.language = Upper (cp_language);
665:
666: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
667: l_try_id NUMBER := NULL;
668:
669: BEGIN
670:
671: -- Get the try_id for the name passed
672: OPEN l_try_id_csr (p_try_name, p_language);
673: FETCH l_try_id_csr INTO l_try_id;
674: IF l_try_id_csr%NOTFOUND THEN
675: l_return_status := OKL_API.G_RET_STS_ERROR;
676: END IF;
677: CLOSE l_try_id_csr;
678:
679: x_return_status := l_return_status;
687: CLOSE l_try_id_csr;
688: END IF;
689:
690: -- store SQL error message on message stack for caller
691: OKL_API.SET_MESSAGE (
692: p_app_name => G_APP_NAME
693: ,p_msg_name => G_UNEXPECTED_ERROR
694: ,p_token1 => G_SQLCODE_TOKEN
695: ,p_token1_value => sqlcode
696: ,p_token2 => G_SQLERRM_TOKEN
697: ,p_token2_value => sqlerrm);
698:
699: -- notify caller of an UNEXPECTED error
700: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
701:
702: END get_transaction_id;
703:
704:
721: SELECT id
722: FROM okl_strm_type_b s
723: WHERE s.code = cp_sty_code;
724:
725: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
726: l_sty_id NUMBER := NULL;
727:
728: BEGIN
729:
730: -- Get the sty_id for the code passed
731: OPEN l_sty_id_csr (p_sty_code);
732: FETCH l_sty_id_csr INTO l_sty_id;
733: IF l_sty_id_csr%NOTFOUND THEN
734: l_return_status := OKL_API.G_RET_STS_ERROR;
735: END IF;
736: CLOSE l_sty_id_csr;
737:
738: x_return_status := l_return_status;
746: CLOSE l_sty_id_csr;
747: END IF;
748:
749: -- store SQL error message on message stack for caller
750: OKL_API.SET_MESSAGE (
751: p_app_name => G_APP_NAME
752: ,p_msg_name => G_UNEXPECTED_ERROR
753: ,p_token1 => G_SQLCODE_TOKEN
754: ,p_token1_value => sqlcode
755: ,p_token2 => G_SQLERRM_TOKEN
756: ,p_token2_value => sqlerrm);
757:
758: -- notify caller of an UNEXPECTED error
759: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
760:
761: END get_stream_type_id;
762:
763:
843: -- **************
844: -- Create message
845: -- **************
846:
847: OKL_API.SET_MESSAGE(
848: p_app_name => p_app_name
849: ,p_msg_name => p_msg_name
850: ,p_token1 => p_token1
851: ,p_token1_value => l_token1_value
874:
875: WHEN OTHERS THEN
876:
877: -- store SQL error message on message stack for caller
878: OKL_API.SET_MESSAGE (
879: p_app_name => G_APP_NAME
880: ,p_msg_name => G_UNEXPECTED_ERROR
881: ,p_token1 => G_SQLCODE_TOKEN
882: ,p_token1_value => sqlcode
929: l_label_value := l_group_meaning || ' - ' || l_rule_meaning;
930:
931: END IF;
932:
933: OKL_API.SET_MESSAGE (
934: p_app_name => G_APP_NAME
935: ,p_msg_name => 'OKL_AM_INVALID_RULE_SETUP'
936: ,p_token1 => 'LABEL'
937: ,p_token1_value => l_label_value);
944: CLOSE l_rule_csr;
945: END IF;
946:
947: -- store SQL error message on message stack for caller
948: OKL_API.SET_MESSAGE (
949: p_app_name => G_APP_NAME
950: ,p_msg_name => G_UNEXPECTED_ERROR
951: ,p_token1 => G_SQLCODE_TOKEN
952: ,p_token1_value => sqlcode
977:
978: l_rgpv_tbl okl_rule_pub.rgpv_tbl_type;
979: l_rulv_tbl okl_rule_pub.rulv_tbl_type;
980:
981: l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
982: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
983:
984: l_api_version CONSTANT NUMBER := g_api_version;
985: l_msg_count NUMBER := OKL_API.G_MISS_NUM;
978: l_rgpv_tbl okl_rule_pub.rgpv_tbl_type;
979: l_rulv_tbl okl_rule_pub.rulv_tbl_type;
980:
981: l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
982: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
983:
984: l_api_version CONSTANT NUMBER := g_api_version;
985: l_msg_count NUMBER := OKL_API.G_MISS_NUM;
986: l_msg_data VARCHAR2(2000);
981: l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
982: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
983:
984: l_api_version CONSTANT NUMBER := g_api_version;
985: l_msg_count NUMBER := OKL_API.G_MISS_NUM;
986: l_msg_data VARCHAR2(2000);
987:
988: l_rg_count NUMBER;
989: l_rule_count NUMBER;
1012: ELSE
1013:
1014: okl_rule_apis_pub.get_contract_rgs (
1015: p_api_version => l_api_version,
1016: p_init_msg_list => OKL_API.G_FALSE,
1017: p_chr_id => p_chr_id,
1018: p_cle_id => p_cle_id,
1019: p_rgd_code => p_rgd_code,
1020: x_return_status => l_return_status,
1022: x_msg_data => l_msg_data,
1023: x_rgpv_tbl => l_rgpv_tbl,
1024: x_rg_count => l_rg_count);
1025:
1026: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1027: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1028: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1029: RAISE OKL_API.G_EXCEPTION_ERROR;
1030: ELSIF (NVL (l_rg_count, 0) <> 1) THEN
1023: x_rgpv_tbl => l_rgpv_tbl,
1024: x_rg_count => l_rg_count);
1025:
1026: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1027: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1028: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1029: RAISE OKL_API.G_EXCEPTION_ERROR;
1030: ELSIF (NVL (l_rg_count, 0) <> 1) THEN
1031: RAISE l_no_rule_data;
1024: x_rg_count => l_rg_count);
1025:
1026: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1027: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1028: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1029: RAISE OKL_API.G_EXCEPTION_ERROR;
1030: ELSIF (NVL (l_rg_count, 0) <> 1) THEN
1031: RAISE l_no_rule_data;
1032: END IF;
1025:
1026: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1027: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1028: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1029: RAISE OKL_API.G_EXCEPTION_ERROR;
1030: ELSIF (NVL (l_rg_count, 0) <> 1) THEN
1031: RAISE l_no_rule_data;
1032: END IF;
1033:
1038: -- ***************
1039:
1040: okl_rule_apis_pub.get_contract_rules (
1041: p_api_version => l_api_version,
1042: p_init_msg_list => OKL_API.G_FALSE,
1043: p_rgpv_rec => l_rgpv_tbl(1),
1044: p_rdf_code => p_rdf_code,
1045: x_return_status => l_return_status,
1046: x_msg_count => l_msg_count,
1047: x_msg_data => l_msg_data,
1048: x_rulv_tbl => l_rulv_tbl,
1049: x_rule_count => l_rule_count);
1050:
1051: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1052: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1053: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1054: RAISE OKL_API.G_EXCEPTION_ERROR;
1055: ELSIF (NVL (l_rule_count, 0) <> 1) THEN
1048: x_rulv_tbl => l_rulv_tbl,
1049: x_rule_count => l_rule_count);
1050:
1051: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1052: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1053: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1054: RAISE OKL_API.G_EXCEPTION_ERROR;
1055: ELSIF (NVL (l_rule_count, 0) <> 1) THEN
1056: RAISE l_no_rule_data;
1049: x_rule_count => l_rule_count);
1050:
1051: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1052: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1053: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1054: RAISE OKL_API.G_EXCEPTION_ERROR;
1055: ELSIF (NVL (l_rule_count, 0) <> 1) THEN
1056: RAISE l_no_rule_data;
1057: END IF;
1050:
1051: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1052: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1053: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1054: RAISE OKL_API.G_EXCEPTION_ERROR;
1055: ELSIF (NVL (l_rule_count, 0) <> 1) THEN
1056: RAISE l_no_rule_data;
1057: END IF;
1058:
1090: ,p_token2_value => p_rdf_code);
1091: --gboomina Bug 4734134 - End
1092: END IF;
1093:
1094: x_return_status := OKL_API.G_RET_STS_ERROR;
1095:
1096: WHEN OTHERS THEN
1097:
1098: -- error message will come from called APIs
1096: WHEN OTHERS THEN
1097:
1098: -- error message will come from called APIs
1099: -- notify caller of an UNEXPECTED error
1100: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1101:
1102: END get_rule_record;
1103:
1104:
1142: WHEN OTHERS THEN
1143:
1144: -- error message will come from called APIs
1145: -- notify caller of an UNEXPECTED error
1146: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1147:
1148: END get_rule_record;
1149:
1150:
1189: FROM okx_cust_site_uses_v cst
1190: WHERE cst.id1 = cp_id1;
1191: -- AND cst.id2 = cp_id2;
1192:
1193: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1194: l_rulv_rec okl_rule_pub.rulv_rec_type;
1195: l_bill_to_address_rec okx_cust_site_uses_v%ROWTYPE;
1196:
1197: -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
1210: p_message_yn => TRUE,
1211: x_rulv_rec => l_rulv_rec,
1212: x_return_status => l_return_status);
1213:
1214: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1215: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1216: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1217: RAISE OKL_API.G_EXCEPTION_ERROR;
1218: END IF;
1211: x_rulv_rec => l_rulv_rec,
1212: x_return_status => l_return_status);
1213:
1214: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1215: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1216: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1217: RAISE OKL_API.G_EXCEPTION_ERROR;
1218: END IF;
1219: */
1212: x_return_status => l_return_status);
1213:
1214: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1215: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1216: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1217: RAISE OKL_API.G_EXCEPTION_ERROR;
1218: END IF;
1219: */
1220:
1213:
1214: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1215: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1216: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1217: RAISE OKL_API.G_EXCEPTION_ERROR;
1218: END IF;
1219: */
1220:
1221: -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
1226:
1227: IF l_bto_values_csr%NOTFOUND THEN
1228:
1229: -- Set the message
1230: OKL_API.set_message (
1231: p_app_name => G_APP_NAME
1232: ,p_msg_name => G_INVALID_VALUE1
1233: ,p_token1 => 'COL_NAME'
1234: ,p_token1_value => 'contract_id');
1232: ,p_msg_name => G_INVALID_VALUE1
1233: ,p_token1 => 'COL_NAME'
1234: ,p_token1_value => 'contract_id');
1235:
1236: l_return_status := OKL_API.G_RET_STS_ERROR;
1237:
1238: ELSIF l_bto_values_rec.bill_to_site_use_id IS NULL THEN
1239:
1240: -- Get party name
1251: p_token1_value => l_party_name,
1252: p_token2 => 'PARTY_ROLE',
1253: p_token2_value => l_bto_values_rec.party_role);
1254:
1255: l_return_status := OKL_API.G_RET_STS_ERROR;
1256: END IF;
1257:
1258: CLOSE l_bto_values_csr;
1259:
1263: (l_bto_values_rec.bill_to_site_use_id); -- RMUNJULU 3394507 Pass the right bill to site id
1264: FETCH l_bill_to_address_csr INTO l_bill_to_address_rec;
1265:
1266: IF (l_bill_to_address_csr%NOTFOUND) THEN
1267: l_return_status := Okl_Api.G_RET_STS_ERROR;
1268: END IF;
1269:
1270: CLOSE l_bill_to_address_csr;
1271:
1286: END IF;
1287:
1288: IF p_message_yn THEN
1289: -- store SQL error message on message stack for caller
1290: OKL_API.SET_MESSAGE (
1291: p_app_name => G_APP_NAME
1292: ,p_msg_name => G_UNEXPECTED_ERROR
1293: ,p_token1 => G_SQLCODE_TOKEN
1294: ,p_token1_value => sqlcode
1296: ,p_token2_value => sqlerrm);
1297: END IF;
1298:
1299: -- notify caller of an UNEXPECTED error
1300: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1301:
1302: END get_bill_to_address;
1303:
1304:
1320: x_formula_value OUT NOCOPY NUMBER,
1321: x_return_status OUT NOCOPY VARCHAR2) IS
1322:
1323: l_api_version CONSTANT NUMBER := g_api_version;
1324: l_msg_count NUMBER := OKL_API.G_MISS_NUM;
1325: l_msg_data VARCHAR2(2000);
1326: l_formula_value NUMBER := 0;
1327: l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1328: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1323: l_api_version CONSTANT NUMBER := g_api_version;
1324: l_msg_count NUMBER := OKL_API.G_MISS_NUM;
1325: l_msg_data VARCHAR2(2000);
1326: l_formula_value NUMBER := 0;
1327: l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1328: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1329:
1330: BEGIN
1331:
1324: l_msg_count NUMBER := OKL_API.G_MISS_NUM;
1325: l_msg_data VARCHAR2(2000);
1326: l_formula_value NUMBER := 0;
1327: l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1328: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1329:
1330: BEGIN
1331:
1332: okl_execute_formula_pub.execute (
1330: BEGIN
1331:
1332: okl_execute_formula_pub.execute (
1333: p_api_version => l_api_version,
1334: p_init_msg_list => OKL_API.G_FALSE,
1335: x_return_status => l_return_status,
1336: x_msg_count => l_msg_count,
1337: x_msg_data => l_msg_data,
1338: p_formula_name => p_formula_name,
1340: p_line_id => p_cle_id,
1341: p_additional_parameters => p_additional_parameters,
1342: x_value => l_formula_value);
1343:
1344: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1345: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1346: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1347: RAISE OKL_API.G_EXCEPTION_ERROR;
1348: END IF;
1341: p_additional_parameters => p_additional_parameters,
1342: x_value => l_formula_value);
1343:
1344: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1345: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1346: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1347: RAISE OKL_API.G_EXCEPTION_ERROR;
1348: END IF;
1349:
1342: x_value => l_formula_value);
1343:
1344: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1345: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1346: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1347: RAISE OKL_API.G_EXCEPTION_ERROR;
1348: END IF;
1349:
1350: x_formula_value := l_formula_value;
1343:
1344: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1345: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1346: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1347: RAISE OKL_API.G_EXCEPTION_ERROR;
1348: END IF;
1349:
1350: x_formula_value := l_formula_value;
1351: x_return_status := l_overall_status;
1355: WHEN OTHERS THEN
1356:
1357: -- error message will come from called APIs
1358: -- notify caller of an UNEXPECTED error
1359: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1360:
1361: END get_formula_value;
1362:
1363:
1400:
1401: WHEN OTHERS THEN
1402:
1403: -- store SQL error message on message stack for caller
1404: OKL_API.SET_MESSAGE (
1405: p_app_name => G_APP_NAME
1406: ,p_msg_name => G_UNEXPECTED_ERROR
1407: ,p_token1 => G_SQLCODE_TOKEN
1408: ,p_token1_value => sqlcode
1432: p_trx_source_table IN OKL_TRX_MSGS.trx_source_table%TYPE,
1433: p_trx_id IN OKL_TRX_MSGS.trx_id%TYPE,
1434: x_return_status OUT NOCOPY VARCHAR2) IS
1435:
1436: px_error_rec okl_api.error_rec_type;
1437: lp_tmgv_tbl okl_trx_msgs_pub.tmgv_tbl_type;
1438: lx_tmgv_tbl okl_trx_msgs_pub.tmgv_tbl_type;
1439:
1440: l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1436: px_error_rec okl_api.error_rec_type;
1437: lp_tmgv_tbl okl_trx_msgs_pub.tmgv_tbl_type;
1438: lx_tmgv_tbl okl_trx_msgs_pub.tmgv_tbl_type;
1439:
1440: l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1441: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1442:
1443: l_api_version CONSTANT NUMBER := g_api_version;
1444: l_msg_count NUMBER := OKL_API.G_MISS_NUM;
1437: lp_tmgv_tbl okl_trx_msgs_pub.tmgv_tbl_type;
1438: lx_tmgv_tbl okl_trx_msgs_pub.tmgv_tbl_type;
1439:
1440: l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1441: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1442:
1443: l_api_version CONSTANT NUMBER := g_api_version;
1444: l_msg_count NUMBER := OKL_API.G_MISS_NUM;
1445: l_msg_data VARCHAR2(2000);
1440: l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1441: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1442:
1443: l_api_version CONSTANT NUMBER := g_api_version;
1444: l_msg_count NUMBER := OKL_API.G_MISS_NUM;
1445: l_msg_data VARCHAR2(2000);
1446:
1447: l_seq INTEGER := NVL(lp_tmgv_tbl.LAST, 0) + 1;
1448: last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
1484: IF (lp_tmgv_tbl.COUNT > 0) THEN
1485:
1486: OKL_TRX_MSGS_PUB.insert_trx_msgs (
1487: p_api_version => l_api_version,
1488: p_init_msg_list => OKL_API.G_FALSE,
1489: x_return_status => l_return_status,
1490: x_msg_count => l_msg_count,
1491: x_msg_data => l_msg_data,
1492: p_tmgv_tbl => lp_tmgv_tbl,
1491: x_msg_data => l_msg_data,
1492: p_tmgv_tbl => lp_tmgv_tbl,
1493: x_tmgv_tbl => lx_tmgv_tbl);
1494:
1495: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1496: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1497: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1498: RAISE OKL_API.G_EXCEPTION_ERROR;
1499: END IF;
1492: p_tmgv_tbl => lp_tmgv_tbl,
1493: x_tmgv_tbl => lx_tmgv_tbl);
1494:
1495: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1496: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1497: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1498: RAISE OKL_API.G_EXCEPTION_ERROR;
1499: END IF;
1500:
1493: x_tmgv_tbl => lx_tmgv_tbl);
1494:
1495: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1496: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1497: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1498: RAISE OKL_API.G_EXCEPTION_ERROR;
1499: END IF;
1500:
1501: END IF;
1494:
1495: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1496: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1497: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1498: RAISE OKL_API.G_EXCEPTION_ERROR;
1499: END IF;
1500:
1501: END IF;
1502:
1505: EXCEPTION
1506:
1507: WHEN OTHERS THEN
1508: -- store SQL error message on message stack for caller
1509: OKL_API.SET_MESSAGE (
1510: p_app_name => G_APP_NAME
1511: ,p_msg_name => G_UNEXPECTED_ERROR
1512: ,p_token1 => G_SQLCODE_TOKEN
1513: ,p_token1_value => sqlcode
1514: ,p_token2 => G_SQLERRM_TOKEN
1515: ,p_token2_value => sqlerrm);
1516:
1517: -- notify caller of an UNEXPECTED error
1518: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1519:
1520: END process_messages;
1521:
1522:
1538: p_other_where IN where_tbl_type,
1539: x_object_tbl OUT NOCOPY jtf_object_tbl_type,
1540: x_return_status OUT NOCOPY VARCHAR2) IS
1541:
1542: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1543: l_cnt NUMBER := 0;
1544:
1545: TYPE object_curs_type IS REF CURSOR;
1546: l_object_curs object_curs_type;
1662: WHEN OTHERS THEN
1663:
1664: -- error message will come from called APIs
1665: -- notify caller of an UNEXPECTED error
1666: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1667:
1668: END get_object_details;
1669:
1670:
1683: p_object_id2 IN VARCHAR2)
1684: RETURN VARCHAR2 IS
1685:
1686: l_object_tbl jtf_object_tbl_type;
1687: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1688:
1689: BEGIN
1690:
1691: get_object_details (
1694: p_object_id2 => p_object_id2,
1695: x_object_tbl => l_object_tbl,
1696: x_return_status => l_return_status);
1697:
1698: IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1699: RETURN l_object_tbl(1).name;
1700: ELSE
1701: RETURN NULL;
1702: END IF;
1727: RETURN VARCHAR2 IS
1728:
1729: l_object_tbl jtf_object_tbl_type;
1730: l_other_cols select_tbl_type;
1731: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1732:
1733: BEGIN
1734:
1735: l_other_cols(1) := p_column;
1741: p_other_select => l_other_cols,
1742: x_object_tbl => l_object_tbl,
1743: x_return_status => l_return_status);
1744:
1745: IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1746: RETURN l_object_tbl(1).other_values;
1747: ELSE
1748: RETURN NULL;
1749: END IF;
1801: AND rdf.rgp_id = rgp.id
1802: AND rdf.rule_information_category = cp_rdf_code;
1803:
1804: l_rule_rec l_rule_csr%ROWTYPE;
1805: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1806:
1807: l_chr_id NUMBER;
1808: l_cle_id NUMBER;
1809: l_dnz_chr_id NUMBER;
1834:
1835: OPEN l_rule_csr (p_rgd_code, p_rdf_code, l_dnz_chr_id, l_chr_id, l_cle_id);
1836: FETCH l_rule_csr INTO l_rule_rec;
1837: IF l_rule_csr%NOTFOUND THEN
1838: l_return_status := OKL_API.G_RET_STS_ERROR;
1839: END IF;
1840: CLOSE l_rule_csr;
1841:
1842: IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1838: l_return_status := OKL_API.G_RET_STS_ERROR;
1839: END IF;
1840: CLOSE l_rule_csr;
1841:
1842: IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1843:
1844: IF p_object_type = 'OBJECT1' THEN
1845: l_object_code := l_rule_rec.jtot_object1_code;
1846: l_object_id1 := l_rule_rec.object1_id1;
1856: l_object_id1 := l_rule_rec.object3_id1;
1857: l_object_id2 := l_rule_rec.object3_id2;
1858:
1859: ELSE
1860: l_return_status := OKL_API.G_RET_STS_ERROR;
1861: END IF;
1862:
1863: END IF;
1864:
1861: END IF;
1862:
1863: END IF;
1864:
1865: IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1866: RETURN get_jtf_object_name
1867: (l_object_code, l_object_id1, l_object_id2);
1868: ELSE
1869: RETURN NULL;
1961: WHERE opt.ptm_code = c_ptm_code
1962: AND opt.start_date < sysdate
1963: AND nvl(opt.end_date, sysdate+1) > sysdate;
1964:
1965: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1966: l_content_id jtf_amv_items_vl.item_id%type;
1967: l_default_subject okl_process_tmplts_v.email_subject_line%type;
1968:
1969: BEGIN
1972: FETCH c_content_csr INTO l_content_id,
1973: l_default_subject;
1974: IF c_content_csr%NOTFOUND THEN
1975:
1976: OKL_API.set_message( p_app_name => 'OKL',
1977: p_msg_name => 'OKL_AM_MISSING_PTM_CODE',
1978: p_token1 => 'PTM_CODE',
1979: p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
1980: p_lookup_code => p_ptm_code));
1989:
1990: EXCEPTION
1991:
1992: WHEN OTHERS THEN
1993: OKL_API.SET_MESSAGE (
1994: p_app_name => G_APP_NAME
1995: ,p_msg_name => G_UNEXPECTED_ERROR
1996: ,p_token1 => G_SQLCODE_TOKEN
1997: ,p_token1_value => sqlcode
2020: x_return_status OUT NOCOPY VARCHAR2,
2021: x_msg_count OUT NOCOPY NUMBER,
2022: x_msg_data OUT NOCOPY VARCHAR2 ) IS
2023:
2024: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2025:
2026: CURSOR c_agent_csr (c_agent_id NUMBER) IS
2027: SELECT nvl(ppf.email_address , fu.email_address) email
2028: FROM fnd_user fu,
2076: CLOSE c_agent_csr;
2077:
2078: IF l_email IS NULL THEN
2079:
2080: OKL_API.set_message( p_app_name => 'OKL',
2081: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2082: p_token1 => 'PTM_MEANING',
2083: p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2084: p_lookup_code => g_ptm_code
2084: p_lookup_code => g_ptm_code
2085: )
2086: );
2087:
2088: OKL_API.set_message( p_app_name => 'OKL',
2089: p_msg_name => 'OKL_AM_FM_AGENT',
2090: p_token1 => 'USERNAME',
2091: p_token1_value => l_user_name);
2092:
2095: END IF;
2096:
2097: IF l_server_id IS NULL THEN
2098:
2099: OKL_API.set_message( p_app_name => 'OKL',
2100: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2101: p_token1 => 'PTM_MEANING',
2102: p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2103: p_lookup_code => g_ptm_code
2103: p_lookup_code => g_ptm_code
2104: )
2105: );
2106:
2107: OKL_API.set_message( p_app_name => 'OKL',
2108: p_msg_name => 'OKL_AM_FM_SERVER_NOT_FOUND');
2109:
2110: l_return_status := OKC_API.G_RET_STS_ERROR;
2111:
2116: CLOSE c_server_csr;
2117:
2118: IF l_server_name IS NULL THEN -- This agent is not associated to the server
2119:
2120: OKL_API.set_message( p_app_name => 'OKL',
2121: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2122: p_token1 => 'PTM_MEANING',
2123: p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2124: p_lookup_code => g_ptm_code
2127: OPEN c_server_name_csr(l_server_id);
2128: FETCH c_server_name_csr INTO l_server_name;
2129: CLOSE c_server_name_csr;
2130:
2131: OKL_API.set_message( p_app_name => 'OKL',
2132: p_msg_name => 'OKL_AM_FM_AGENT_NOT_FOUND',
2133: p_token1 => 'USERNAME',
2134: p_token1_value => l_user_name,
2135: p_token2 => 'SERVER_NAME',
2147: EXCEPTION
2148:
2149: WHEN OTHERS THEN
2150:
2151: OKL_API.SET_MESSAGE (
2152: p_app_name => G_APP_NAME
2153: ,p_msg_name => G_UNEXPECTED_ERROR
2154: ,p_token1 => G_SQLCODE_TOKEN
2155: ,p_token1_value => sqlcode
2179: x_return_status OUT NOCOPY VARCHAR2,
2180: x_msg_count OUT NOCOPY NUMBER,
2181: x_msg_data OUT NOCOPY VARCHAR2 ) IS
2182:
2183: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2184: l_content_id jtf_amv_items_vl.item_id%type;
2185: l_default_subject okl_process_tmplts_v.email_subject_line%type;
2186:
2187: l_email_tbl recipient_tbl;
2200: p_id_value => p_recipient_id,
2201: x_party_object_tbl => l_party_object_tbl,
2202: x_return_status => l_return_status);
2203:
2204: IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2205:
2206: OKL_API.set_message( p_app_name => 'OKL',
2207: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2208: p_token1 => 'PTM_MEANING',
2202: x_return_status => l_return_status);
2203:
2204: IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2205:
2206: OKL_API.set_message( p_app_name => 'OKL',
2207: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2208: p_token1 => 'PTM_MEANING',
2209: p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2210: p_lookup_code => g_ptm_code
2210: p_lookup_code => g_ptm_code
2211: )
2212: );
2213:
2214: OKL_API.set_message( p_app_name => 'OKL',
2215: p_msg_name => 'OKL_AM_INVALID_RECIPIENT',
2216: p_token1 => 'PARTY_TYPE',
2217: p_token1_value => p_recipient_type,
2218: p_token2 => 'PARTY_ID',
2236:
2237: END LOOP;
2238:
2239: ELSE
2240: OKL_API.set_message( p_app_name => 'OKL',
2241: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2242: p_token1 => 'PTM_MEANING',
2243: p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2244: p_lookup_code => g_ptm_code
2244: p_lookup_code => g_ptm_code
2245: )
2246: );
2247:
2248: OKL_API.set_message( p_app_name => 'OKL',
2249: p_msg_name => 'OKL_AM_INVALID_RECIPIENT',
2250: p_token1 => 'PARTY_TYPE',
2251: p_token1_value => p_recipient_type,
2252: p_token2 => 'PARTY_ID',
2256: END IF;
2257:
2258: j := l_email_tbl.FIRST;
2259:
2260: IF (j IS NOT NULL) AND (l_email_tbl(j) <> OKL_API.G_MISS_CHAR AND l_email_tbl(j) IS NOT NULL) THEN
2261:
2262: x_email := l_email_tbl;
2263:
2264: ELSE
2261:
2262: x_email := l_email_tbl;
2263:
2264: ELSE
2265: OKL_API.set_message( p_app_name => 'OKL',
2266: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2267: p_token1 => 'PTM_MEANING',
2268: p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2269: p_lookup_code => g_ptm_code
2269: p_lookup_code => g_ptm_code
2270: )
2271: );
2272:
2273: OKL_API.set_message( p_app_name => 'OKL',
2274: p_msg_name => 'OKL_AM_INVALID_RECIPIENT',
2275: p_token1 => 'PARTY_TYPE',
2276: p_token1_value => p_recipient_type,
2277: p_token2 => 'PARTY_ID',
2285: EXCEPTION
2286:
2287: WHEN OTHERS THEN
2288:
2289: OKL_API.SET_MESSAGE (
2290: p_app_name => G_APP_NAME
2291: ,p_msg_name => G_UNEXPECTED_ERROR
2292: ,p_token1 => G_SQLCODE_TOKEN
2293: ,p_token1_value => sqlcode
2328: , p_pt_bind_types IN p_bind_type_tbl
2329: ) IS
2330:
2331: l_api_version CONSTANT NUMBER := G_API_VERSION;
2332: l_msg_count NUMBER := OKL_API.G_MISS_NUM;
2333: l_msg_data VARCHAR2(8000);
2334:
2335: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2336: l_agent_id NUMBER;
2356: BEGIN
2357:
2358: --Check API version, initialize message list and create savepoint.
2359:
2360: l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2361: G_PKG_NAME,
2362: p_init_msg_list,
2363: l_api_version,
2364: p_api_version,
2366: x_return_status);
2367:
2368:
2369:
2370: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2371: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2372: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2373: RAISE OKL_API.G_EXCEPTION_ERROR;
2374: END IF;
2367:
2368:
2369:
2370: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2371: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2372: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2373: RAISE OKL_API.G_EXCEPTION_ERROR;
2374: END IF;
2375:
2368:
2369:
2370: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2371: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2372: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2373: RAISE OKL_API.G_EXCEPTION_ERROR;
2374: END IF;
2375:
2376: -- Check all mandatory parameters have been passed
2369:
2370: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2371: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2372: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2373: RAISE OKL_API.G_EXCEPTION_ERROR;
2374: END IF;
2375:
2376: -- Check all mandatory parameters have been passed
2377: IF p_ptm_code IS NULL OR p_ptm_code = OKL_API.G_MISS_CHAR THEN
2373: RAISE OKL_API.G_EXCEPTION_ERROR;
2374: END IF;
2375:
2376: -- Check all mandatory parameters have been passed
2377: IF p_ptm_code IS NULL OR p_ptm_code = OKL_API.G_MISS_CHAR THEN
2378: OKC_API.SET_MESSAGE (
2379: p_app_name => 'OKC'
2380: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2381: ,p_token1 => G_COL_NAME_TOKEN
2380: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2381: ,p_token1 => G_COL_NAME_TOKEN
2382: ,p_token1_value => 'P_PTM_CODE');
2383:
2384: l_return_status := OKL_API.G_RET_STS_ERROR;
2385: ELSE
2386: g_ptm_code := p_ptm_code;
2387: END IF;
2388:
2385: ELSE
2386: g_ptm_code := p_ptm_code;
2387: END IF;
2388:
2389: IF p_agent_id IS NULL OR p_agent_id = OKL_API.G_MISS_NUM THEN
2390: OKC_API.SET_MESSAGE (
2391: p_app_name => 'OKC'
2392: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2393: ,p_token1 => G_COL_NAME_TOKEN
2392: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2393: ,p_token1 => G_COL_NAME_TOKEN
2394: ,p_token1_value => 'P_AGENT_ID');
2395:
2396: l_return_status := OKL_API.G_RET_STS_ERROR;
2397: END IF;
2398:
2399: -- check mandatory parameters for the call type made
2400:
2397: END IF;
2398:
2399: -- check mandatory parameters for the call type made
2400:
2401: IF p_transaction_id IS NOT NULL AND p_transaction_id <> OKL_API.G_MISS_NUM THEN
2402:
2403: IF p_recipient_email IS NULL OR p_recipient_email = OKL_API.G_MISS_CHAR THEN
2404:
2405: IF p_recipient_type IS NULL OR p_recipient_type = OKL_API.G_MISS_CHAR THEN
2399: -- check mandatory parameters for the call type made
2400:
2401: IF p_transaction_id IS NOT NULL AND p_transaction_id <> OKL_API.G_MISS_NUM THEN
2402:
2403: IF p_recipient_email IS NULL OR p_recipient_email = OKL_API.G_MISS_CHAR THEN
2404:
2405: IF p_recipient_type IS NULL OR p_recipient_type = OKL_API.G_MISS_CHAR THEN
2406: OKC_API.SET_MESSAGE (
2407: p_app_name => 'OKC'
2401: IF p_transaction_id IS NOT NULL AND p_transaction_id <> OKL_API.G_MISS_NUM THEN
2402:
2403: IF p_recipient_email IS NULL OR p_recipient_email = OKL_API.G_MISS_CHAR THEN
2404:
2405: IF p_recipient_type IS NULL OR p_recipient_type = OKL_API.G_MISS_CHAR THEN
2406: OKC_API.SET_MESSAGE (
2407: p_app_name => 'OKC'
2408: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2409: ,p_token1 => G_COL_NAME_TOKEN
2408: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2409: ,p_token1 => G_COL_NAME_TOKEN
2410: ,p_token1_value => 'P_RECIPIENT_TYPE');
2411:
2412: l_return_status := OKL_API.G_RET_STS_ERROR;
2413: END IF;
2414:
2415: IF p_recipient_id IS NULL OR p_recipient_id = OKL_API.G_MISS_CHAR THEN
2416: OKC_API.SET_MESSAGE (
2411:
2412: l_return_status := OKL_API.G_RET_STS_ERROR;
2413: END IF;
2414:
2415: IF p_recipient_id IS NULL OR p_recipient_id = OKL_API.G_MISS_CHAR THEN
2416: OKC_API.SET_MESSAGE (
2417: p_app_name => 'OKC'
2418: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2419: ,p_token1 => G_COL_NAME_TOKEN
2418: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2419: ,p_token1 => G_COL_NAME_TOKEN
2420: ,p_token1_value => 'P_RECIPIENT_ID');
2421:
2422: l_return_status := OKL_API.G_RET_STS_ERROR;
2423: END IF;
2424: END IF;
2425: ELSE -- Called from AM Send Fulfillment screen
2426:
2425: ELSE -- Called from AM Send Fulfillment screen
2426:
2427: IF p_pt_bind_names.COUNT > 0 THEN
2428:
2429: IF p_pt_bind_names(p_pt_bind_names.FIRST) IS NULL OR p_pt_bind_names(p_pt_bind_names.FIRST)= OKL_API.G_MISS_CHAR THEN
2430: OKC_API.SET_MESSAGE (
2431: p_app_name => 'OKC'
2432: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2433: ,p_token1 => G_COL_NAME_TOKEN
2432: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2433: ,p_token1 => G_COL_NAME_TOKEN
2434: ,p_token1_value => 'P_PT_BIND_NAMES');
2435:
2436: l_return_status := OKL_API.G_RET_STS_ERROR;
2437: END IF;
2438: ELSE
2439:
2440: OKC_API.SET_MESSAGE (
2442: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2443: ,p_token1 => G_COL_NAME_TOKEN
2444: ,p_token1_value => 'P_TRANSACTION_ID');
2445:
2446: l_return_status := OKL_API.G_RET_STS_ERROR;
2447: END IF;
2448:
2449: IF p_recipient_email IS NULL OR p_recipient_email = OKL_API.G_MISS_CHAR THEN
2450: OKC_API.SET_MESSAGE (
2445:
2446: l_return_status := OKL_API.G_RET_STS_ERROR;
2447: END IF;
2448:
2449: IF p_recipient_email IS NULL OR p_recipient_email = OKL_API.G_MISS_CHAR THEN
2450: OKC_API.SET_MESSAGE (
2451: p_app_name => 'OKC'
2452: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2453: ,p_token1 => G_COL_NAME_TOKEN
2452: ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2453: ,p_token1 => G_COL_NAME_TOKEN
2454: ,p_token1_value => 'P_RECIPIENT_EMAIL');
2455:
2456: l_return_status := OKL_API.G_RET_STS_ERROR;
2457: END IF;
2458:
2459: END IF;
2460:
2457: END IF;
2458:
2459: END IF;
2460:
2461: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2462: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2463: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2464: RAISE OKL_API.G_EXCEPTION_ERROR;
2465: END IF;
2458:
2459: END IF;
2460:
2461: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2462: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2463: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2464: RAISE OKL_API.G_EXCEPTION_ERROR;
2465: END IF;
2466:
2459: END IF;
2460:
2461: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2462: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2463: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2464: RAISE OKL_API.G_EXCEPTION_ERROR;
2465: END IF;
2466:
2467: -- validate content and get default subject line
2460:
2461: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2462: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2463: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2464: RAISE OKL_API.G_EXCEPTION_ERROR;
2465: END IF;
2466:
2467: -- validate content and get default subject line
2468: get_content_id( p_ptm_code => p_ptm_code,
2472: x_msg_count => l_msg_count,
2473: x_msg_data => l_msg_data );
2474:
2475:
2476: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2477: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2478: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2479: RAISE OKL_API.G_EXCEPTION_ERROR;
2480: END IF;
2473: x_msg_data => l_msg_data );
2474:
2475:
2476: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2477: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2478: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2479: RAISE OKL_API.G_EXCEPTION_ERROR;
2480: END IF;
2481:
2474:
2475:
2476: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2477: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2478: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2479: RAISE OKL_API.G_EXCEPTION_ERROR;
2480: END IF;
2481:
2482: -- Check if a subject line is given, if not use the default line returned from get_content_id
2475:
2476: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2477: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2478: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2479: RAISE OKL_API.G_EXCEPTION_ERROR;
2480: END IF;
2481:
2482: -- Check if a subject line is given, if not use the default line returned from get_content_id
2483: IF length(trim(p_subject_line)) = 0 OR trim(p_subject_line) <> OKL_API.G_MISS_CHAR THEN
2479: RAISE OKL_API.G_EXCEPTION_ERROR;
2480: END IF;
2481:
2482: -- Check if a subject line is given, if not use the default line returned from get_content_id
2483: IF length(trim(p_subject_line)) = 0 OR trim(p_subject_line) <> OKL_API.G_MISS_CHAR THEN
2484: l_default_subject := p_subject_line;
2485: END IF;
2486:
2487: -- get agent id, from agents email
2492: x_return_status => l_return_status,
2493: x_msg_count => l_msg_count,
2494: x_msg_data => l_msg_data);
2495:
2496: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2497: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2498: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2499: RAISE OKL_API.G_EXCEPTION_ERROR;
2500: END IF;
2493: x_msg_count => l_msg_count,
2494: x_msg_data => l_msg_data);
2495:
2496: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2497: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2498: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2499: RAISE OKL_API.G_EXCEPTION_ERROR;
2500: END IF;
2501:
2494: x_msg_data => l_msg_data);
2495:
2496: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2497: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2498: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2499: RAISE OKL_API.G_EXCEPTION_ERROR;
2500: END IF;
2501:
2502:
2495:
2496: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2497: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2498: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2499: RAISE OKL_API.G_EXCEPTION_ERROR;
2500: END IF;
2501:
2502:
2503: -- Check if a sender email is given, if not use the email returned from get_agent_details
2500: END IF;
2501:
2502:
2503: -- Check if a sender email is given, if not use the email returned from get_agent_details
2504: IF length(trim(p_sender_email))= 0 OR trim(p_sender_email) <> OKL_API.G_MISS_CHAR THEN
2505: l_agent_email := p_sender_email;
2506: END IF;
2507:
2508:
2505: l_agent_email := p_sender_email;
2506: END IF;
2507:
2508:
2509: IF p_recipient_email IS NOT NULL AND p_recipient_email <> OKL_API.G_MISS_CHAR THEN
2510:
2511: l_recipient_email(1) := p_recipient_email;
2512:
2513: ELSE
2522: x_msg_data => l_msg_data);
2523:
2524: END IF;
2525:
2526: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2527: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2528: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2529: RAISE OKL_API.G_EXCEPTION_ERROR;
2530: END IF;
2523:
2524: END IF;
2525:
2526: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2527: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2528: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2529: RAISE OKL_API.G_EXCEPTION_ERROR;
2530: END IF;
2531:
2524: END IF;
2525:
2526: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2527: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2528: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2529: RAISE OKL_API.G_EXCEPTION_ERROR;
2530: END IF;
2531:
2532: -- compile fulfillment bind parameters
2525:
2526: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2527: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2528: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2529: RAISE OKL_API.G_EXCEPTION_ERROR;
2530: END IF;
2531:
2532: -- compile fulfillment bind parameters
2533:
2536: i := p_pt_bind_names.FIRST;
2537: j := p_pt_bind_values.FIRST;
2538: k := p_pt_bind_types.FIRST;
2539:
2540: IF p_pt_bind_names(i) <> OKL_API.G_MISS_CHAR AND p_pt_bind_names(i) IS NOT NULL
2541: AND p_pt_bind_values(j) <> OKL_API.G_MISS_CHAR AND p_pt_bind_values(j) IS NOT NULL
2542: AND p_pt_bind_types(k) <> OKL_API.G_MISS_CHAR AND p_pt_bind_types(k) IS NOT NULL THEN
2543: l_bind_var_tbl := p_pt_bind_names;
2544: l_bind_val_tbl := p_pt_bind_values;
2537: j := p_pt_bind_values.FIRST;
2538: k := p_pt_bind_types.FIRST;
2539:
2540: IF p_pt_bind_names(i) <> OKL_API.G_MISS_CHAR AND p_pt_bind_names(i) IS NOT NULL
2541: AND p_pt_bind_values(j) <> OKL_API.G_MISS_CHAR AND p_pt_bind_values(j) IS NOT NULL
2542: AND p_pt_bind_types(k) <> OKL_API.G_MISS_CHAR AND p_pt_bind_types(k) IS NOT NULL THEN
2543: l_bind_var_tbl := p_pt_bind_names;
2544: l_bind_val_tbl := p_pt_bind_values;
2545: l_bind_type_tbl := p_pt_bind_types;
2538: k := p_pt_bind_types.FIRST;
2539:
2540: IF p_pt_bind_names(i) <> OKL_API.G_MISS_CHAR AND p_pt_bind_names(i) IS NOT NULL
2541: AND p_pt_bind_values(j) <> OKL_API.G_MISS_CHAR AND p_pt_bind_values(j) IS NOT NULL
2542: AND p_pt_bind_types(k) <> OKL_API.G_MISS_CHAR AND p_pt_bind_types(k) IS NOT NULL THEN
2543: l_bind_var_tbl := p_pt_bind_names;
2544: l_bind_val_tbl := p_pt_bind_values;
2545: l_bind_type_tbl := p_pt_bind_types;
2546:
2583: x_msg_count => l_msg_count,
2584: x_msg_data => l_msg_data);
2585:
2586:
2587: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2588:
2589: OKL_API.set_message( p_app_name => 'OKL',
2590: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2591: p_token1 => 'PTM_MEANING',
2585:
2586:
2587: IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2588:
2589: OKL_API.set_message( p_app_name => 'OKL',
2590: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2591: p_token1 => 'PTM_MEANING',
2592: p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2593: p_lookup_code => g_ptm_code
2593: p_lookup_code => g_ptm_code
2594: )
2595: );
2596:
2597: OKL_API.set_message(
2598: p_app_name => 'OKL',
2599: p_msg_name => 'OKL_AM_UNEXP_FM_ERROR',
2600: p_token1 => 'EMAIL',
2601: p_token1_value => l_recipient_email(l_loop_counter));
2599: p_msg_name => 'OKL_AM_UNEXP_FM_ERROR',
2600: p_token1 => 'EMAIL',
2601: p_token1_value => l_recipient_email(l_loop_counter));
2602:
2603: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2604:
2605: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2606:
2607: OKL_API.set_message( p_app_name => 'OKL',
2601: p_token1_value => l_recipient_email(l_loop_counter));
2602:
2603: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2604:
2605: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2606:
2607: OKL_API.set_message( p_app_name => 'OKL',
2608: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2609: p_token1 => 'PTM_MEANING',
2603: RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2604:
2605: ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2606:
2607: OKL_API.set_message( p_app_name => 'OKL',
2608: p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2609: p_token1 => 'PTM_MEANING',
2610: p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2611: p_lookup_code => g_ptm_code
2610: p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2611: p_lookup_code => g_ptm_code
2612: )
2613: );
2614: OKL_API.set_message(
2615: p_app_name => 'OKL',
2616: p_msg_name => 'OKL_AM_EXP_FM_ERROR',
2617: p_token1 => 'EMAIL',
2618: p_token1_value => l_recipient_email(l_loop_counter));
2616: p_msg_name => 'OKL_AM_EXP_FM_ERROR',
2617: p_token1 => 'EMAIL',
2618: p_token1_value => l_recipient_email(l_loop_counter));
2619:
2620: RAISE OKL_API.G_EXCEPTION_ERROR;
2621: END IF;
2622:
2623: EXIT WHEN (l_loop_counter = l_recipient_email.LAST);
2624: l_loop_counter := l_recipient_email.NEXT(l_loop_counter );
2629: x_return_status:= l_return_status;
2630: x_msg_count := l_msg_count;
2631: x_msg_data := l_msg_data;
2632:
2633: OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2634:
2635: EXCEPTION
2636: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2637: x_return_status := OKL_API.HANDLE_EXCEPTIONS
2632:
2633: OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2634:
2635: EXCEPTION
2636: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2637: x_return_status := OKL_API.HANDLE_EXCEPTIONS
2638: (
2639: l_api_name,
2640: G_PKG_NAME,
2633: OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2634:
2635: EXCEPTION
2636: WHEN OKL_API.G_EXCEPTION_ERROR THEN
2637: x_return_status := OKL_API.HANDLE_EXCEPTIONS
2638: (
2639: l_api_name,
2640: G_PKG_NAME,
2641: 'OKL_API.G_RET_STS_ERROR',
2637: x_return_status := OKL_API.HANDLE_EXCEPTIONS
2638: (
2639: l_api_name,
2640: G_PKG_NAME,
2641: 'OKL_API.G_RET_STS_ERROR',
2642: x_msg_count,
2643: x_msg_data,
2644: '_PVT'
2645: );
2642: x_msg_count,
2643: x_msg_data,
2644: '_PVT'
2645: );
2646: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2647: x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2648: (
2649: l_api_name,
2650: G_PKG_NAME,
2643: x_msg_data,
2644: '_PVT'
2645: );
2646: WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2647: x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2648: (
2649: l_api_name,
2650: G_PKG_NAME,
2651: 'OKL_API.G_RET_STS_UNEXP_ERROR',
2647: x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2648: (
2649: l_api_name,
2650: G_PKG_NAME,
2651: 'OKL_API.G_RET_STS_UNEXP_ERROR',
2652: x_msg_count,
2653: x_msg_data,
2654: '_PVT'
2655: );
2653: x_msg_data,
2654: '_PVT'
2655: );
2656: WHEN OTHERS THEN
2657: x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2658: (
2659: l_api_name,
2660: G_PKG_NAME,
2661: 'OTHERS',
2903: WHERE WFEV.guid = WFES.event_filter_guid
2904: AND WFES.wf_process_type = p_process_type
2905: AND UPPER(WFES.wf_process_name) = UPPER(p_process_name);
2906:
2907: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2908: l_name VARCHAR2(200);
2909:
2910: BEGIN
2911:
2922: IF okl_get_event_name_csr%ISOPEN THEN
2923: CLOSE okl_get_event_name_csr;
2924: END IF;
2925:
2926: OKL_API.set_message(p_app_name => g_app_name,
2927: p_msg_name => g_unexpected_error,
2928: p_token1 => g_sqlcode_token,
2929: p_token1_value => sqlcode,
2930: p_token2 => g_sqlerrm_token,
2929: p_token1_value => sqlcode,
2930: p_token2 => g_sqlerrm_token,
2931: p_token2_value => sqlerrm);
2932:
2933: l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2934:
2935: x_return_status := l_return_status;
2936:
2937: RETURN l_return_status;
2977:
2978:
2979:
2980: l_quote_tbl quote_tbl_type;
2981: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2982: i NUMBER;
2983:
2984:
2985: BEGIN
3012: IF get_qte_csr%ISOPEN THEN
3013: CLOSE get_qte_csr;
3014: END IF;
3015:
3016: OKL_API.set_message(p_app_name => g_app_name,
3017: p_msg_name => g_unexpected_error,
3018: p_token1 => g_sqlcode_token,
3019: p_token1_value => sqlcode,
3020: p_token2 => g_sqlerrm_token,
3019: p_token1_value => sqlcode,
3020: p_token2 => g_sqlerrm_token,
3021: p_token2_value => sqlerrm);
3022:
3023: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3024:
3025:
3026: END get_contract_quotes;
3027:
3065: AND TQL.kle_id = p_kle_id;
3066:
3067:
3068: l_quote_tbl quote_tbl_type;
3069: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3070: i NUMBER;
3071:
3072:
3073: BEGIN
3101: IF get_qte_csr%ISOPEN THEN
3102: CLOSE get_qte_csr;
3103: END IF;
3104:
3105: OKL_API.set_message(p_app_name => g_app_name,
3106: p_msg_name => g_unexpected_error,
3107: p_token1 => g_sqlcode_token,
3108: p_token1_value => sqlcode,
3109: p_token2 => g_sqlerrm_token,
3108: p_token1_value => sqlcode,
3109: p_token2 => g_sqlerrm_token,
3110: p_token2_value => sqlerrm);
3111:
3112: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3113:
3114: END get_line_quotes;
3115:
3116:
3166: --
3167:
3168:
3169: l_trn_tbl trn_tbl_type;
3170: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3171: i NUMBER;
3172:
3173:
3174: BEGIN
3205: IF get_trn_csr%ISOPEN THEN
3206: CLOSE get_trn_csr;
3207: END IF;
3208:
3209: OKL_API.set_message(p_app_name => g_app_name,
3210: p_msg_name => g_unexpected_error,
3211: p_token1 => g_sqlcode_token,
3212: p_token1_value => sqlcode,
3213: p_token2 => g_sqlerrm_token,
3212: p_token1_value => sqlcode,
3213: p_token2 => g_sqlerrm_token,
3214: p_token2_value => sqlerrm);
3215:
3216: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3217:
3218: END get_contract_transactions;
3219:
3220:
3264: AND TQL.kle_id = p_kle_id;
3265:
3266:
3267: l_trn_tbl trn_tbl_type;
3268: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3269: i NUMBER;
3270:
3271: BEGIN
3272:
3302: IF get_trn_csr%ISOPEN THEN
3303: CLOSE get_trn_csr;
3304: END IF;
3305:
3306: OKL_API.set_message(p_app_name => g_app_name,
3307: p_msg_name => g_unexpected_error,
3308: p_token1 => g_sqlcode_token,
3309: p_token1_value => sqlcode,
3310: p_token2 => g_sqlerrm_token,
3309: p_token1_value => sqlcode,
3310: p_token2 => g_sqlerrm_token,
3311: p_token2_value => sqlerrm);
3312:
3313: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3314:
3315: END get_line_transactions;
3316:
3317:
3363: AND TRX.qte_id = p_qte_id;
3364:
3365:
3366: l_quote_tbl quote_tbl_type;
3367: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3368: i NUMBER;
3369: l_id NUMBER := -9999;
3370:
3371: BEGIN
3417: IF get_trn_csr%ISOPEN THEN
3418: CLOSE get_trn_csr;
3419: END IF;
3420:
3421: OKL_API.set_message(p_app_name => g_app_name,
3422: p_msg_name => g_unexpected_error,
3423: p_token1 => g_sqlcode_token,
3424: p_token1_value => sqlcode,
3425: p_token2 => g_sqlerrm_token,
3424: p_token1_value => sqlcode,
3425: p_token2 => g_sqlerrm_token,
3426: p_token2_value => sqlerrm);
3427:
3428: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3429:
3430: END get_non_trn_contract_quotes;
3431:
3432:
3584: WHERE profile_option_name = p_profile_option_name;
3585:
3586: l_user_profile_name VARCHAR2(240);
3587: BEGIN
3588: x_return_status := OKL_API.G_RET_STS_SUCCESS;
3589:
3590: OPEN l_profileoptionsvl_csr;
3591: FETCH l_profileoptionsvl_csr INTO l_user_profile_name;
3592: IF l_profileoptionsvl_csr%NOTFOUND THEN
3589:
3590: OPEN l_profileoptionsvl_csr;
3591: FETCH l_profileoptionsvl_csr INTO l_user_profile_name;
3592: IF l_profileoptionsvl_csr%NOTFOUND THEN
3593: x_return_status := OKL_API.G_RET_STS_ERROR;
3594: END IF;
3595: CLOSE l_profileoptionsvl_csr;
3596:
3597: RETURN l_user_profile_name;
3602: IF l_profileoptionsvl_csr%ISOPEN THEN
3603: CLOSE l_profileoptionsvl_csr;
3604: END IF;
3605: -- unexpected error
3606: OKL_API.set_message(p_app_name => 'OKC',
3607: p_msg_name => 'OKC_CONTRACTS_UNEXPECTED_ERROR',
3608: p_token1 => g_sqlcode_token,
3609: p_token1_value => sqlcode,
3610: p_token2 => g_sqlerrm_token,
3608: p_token1 => g_sqlcode_token,
3609: p_token1_value => sqlcode,
3610: p_token2 => g_sqlerrm_token,
3611: p_token2_value => sqlerrm);
3612: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3613: RETURN NULL;
3614: END get_user_profile_option_name;
3615:
3616: -- Start of comments
3652: x_currency_conversion_rate => l_currency_conversion_rate,
3653: x_currency_conversion_date => l_currency_conversion_date,
3654: x_converted_amount => l_converted_amount);
3655:
3656: IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3657: l_converted_amount := 0;
3658: END IF;
3659: RETURN l_converted_amount;
3660:
3662:
3663: WHEN OTHERS THEN
3664:
3665: -- unexpected error
3666: OKL_API.set_message(p_app_name => 'OKC',
3667: p_msg_name => 'OKC_CONTRACTS_UNEXPECTED_ERROR',
3668: p_token1 => g_sqlcode_token,
3669: p_token1_value => sqlcode,
3670: p_token2 => g_sqlerrm_token,
3714: AND TQL.kle_id = p_kle_id;
3715:
3716:
3717: l_quote_tbl quote_tbl_type;
3718: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3719: i NUMBER;
3720:
3721:
3722: BEGIN
3751: IF get_qte_csr%ISOPEN THEN
3752: CLOSE get_qte_csr;
3753: END IF;
3754:
3755: OKL_API.set_message(p_app_name => g_app_name,
3756: p_msg_name => g_unexpected_error,
3757: p_token1 => g_sqlcode_token,
3758: p_token1_value => sqlcode,
3759: p_token2 => g_sqlerrm_token,
3758: p_token1_value => sqlcode,
3759: p_token2 => g_sqlerrm_token,
3760: p_token2_value => sqlerrm);
3761:
3762: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3763:
3764: END get_all_term_quotes_for_line;
3765:
3766:
3833: -- End : Bug 6030917 : prasjain
3834:
3835: BEGIN
3836:
3837: x_return_status := OKL_API.G_RET_STS_SUCCESS ;
3838:
3839: OPEN l_oklheaders_csr(p_khr_id);
3840: FETCH l_oklheaders_csr INTO l_sts_code, l_deal_type; -- SECHAWLA 09-AUG-05 4304230 : added sts_code
3841: IF l_oklheaders_csr%NOTFOUND THEN
3842: OKC_API.set_message( p_app_name => 'OKC',
3843: p_msg_name => G_INVALID_VALUE,
3844: p_token1 => G_COL_NAME_TOKEN,
3845: p_token1_value => 'KHR_ID');
3846: x_return_status := OKL_API.G_RET_STS_ERROR;
3847: RETURN 0;
3848: END IF;
3849: CLOSE l_oklheaders_csr;
3850:
3898: p_additional_parameters => l_add_params, -- rmunjulu LOANS_ENHANCEMENT
3899: x_formula_value => l_net_investment,
3900: x_return_status => x_return_status);
3901:
3902: IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3903: IF p_message_yn THEN
3904: l_asset_value := get_ak_attribute(p_code => 'OKL_NET_INVESTMENT');
3905: -- Unable to calculate ASSET_VALUE
3906: OKL_API.set_message( p_app_name => 'OKL',
3902: IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3903: IF p_message_yn THEN
3904: l_asset_value := get_ak_attribute(p_code => 'OKL_NET_INVESTMENT');
3905: -- Unable to calculate ASSET_VALUE
3906: OKL_API.set_message( p_app_name => 'OKL',
3907: p_msg_name => 'OKL_AM_FORMULA_ERROR',
3908: p_token1 => 'ASSET_VALUE',
3909: p_token1_value => l_asset_value);
3910: END IF;
3929: IF l_oklheaders_csr%ISOPEN THEN
3930: CLOSE l_oklheaders_csr;
3931: END IF;
3932: -- unexpected error
3933: OKL_API.set_message(p_app_name => 'OKC',
3934: p_msg_name => 'OKC_CONTRACTS_UNEXPECTED_ERROR',
3935: p_token1 => g_sqlcode_token,
3936: p_token1_value => sqlcode,
3937: p_token2 => g_sqlerrm_token,
3936: p_token1_value => sqlcode,
3937: p_token2 => g_sqlerrm_token,
3938: p_token2_value => sqlerrm);
3939:
3940: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3941:
3942: RETURN 0;
3943: END get_net_investment;
3944: --
3954: -- Version : 1.0
3955: -- History : BAKUCHIB 19-FEB-2003 - 2757368 created
3956: -- BAKUCHIB 21-FEB-2303 - 2757368 - Modified to reomve
3957: -- Message stack population of invaid value, and removed
3958: -- checking of OKL_API.g_miss_* validation in the
3959: -- c_party_csr%not_found if condition. Added logic in
3960: -- exception section to close the cursor if open.
3961: -- End of Commnets
3962:
3985: AND nvl(cle_id,1) = nvl(p_Kle_id,nvl(cle_id,1));
3986:
3987: BEGIN
3988: IF (p_chr_id IS NULL OR
3989: p_chr_id = OKL_API.G_MISS_NUM) OR
3990: (p_rle_code IS NULL OR
3991: p_rle_code = OKL_API.G_MISS_CHAR) THEN
3992: RAISE excp_party_error;
3993: END IF;
3987: BEGIN
3988: IF (p_chr_id IS NULL OR
3989: p_chr_id = OKL_API.G_MISS_NUM) OR
3990: (p_rle_code IS NULL OR
3991: p_rle_code = OKL_API.G_MISS_CHAR) THEN
3992: RAISE excp_party_error;
3993: END IF;
3994: OPEN c_party_csr(p_chr_id => p_chr_id,
3995: p_kle_id => p_kle_id,
4062: AND QTE.qtp_code LIKE 'TER%';
4063:
4064:
4065: l_quote_tbl quote_tbl_type;
4066: l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4067: i NUMBER;
4068:
4069:
4070: BEGIN
4098: IF get_qte_csr%ISOPEN THEN
4099: CLOSE get_qte_csr;
4100: END IF;
4101:
4102: OKL_API.set_message(p_app_name => g_app_name,
4103: p_msg_name => g_unexpected_error,
4104: p_token1 => g_sqlcode_token,
4105: p_token1_value => sqlcode,
4106: p_token2 => g_sqlerrm_token,
4105: p_token1_value => sqlcode,
4106: p_token2 => g_sqlerrm_token,
4107: p_token2_value => sqlerrm);
4108:
4109: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4110:
4111: END get_all_term_qte_for_contract;
4112:
4113: -- Start of comments
4293: -- If without purchase and OP LEASE then
4294: -- get the net book value on the termination date
4295:
4296: -- Check for data validity
4297: IF p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM
4298: OR p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM THEN
4299:
4300: RAISE Expected_Error;
4301:
4294: -- get the net book value on the termination date
4295:
4296: -- Check for data validity
4297: IF p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM
4298: OR p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM THEN
4299:
4300: RAISE Expected_Error;
4301:
4302: END IF;
4643:
4644: BEGIN
4645:
4646: -- Check for data validity
4647: IF p_kle_id IS NULL OR p_kle_id = OKL_API.G_MISS_NUM THEN
4648:
4649: RAISE Expected_Error;
4650:
4651: END IF;
4775: AND PQY.NAME IN ( 'INTEREST_CALCULATION_BASIS', 'REVENUE_RECOGNITION_METHOD', 'TAXOWNER');
4776:
4777: BEGIN
4778:
4779: x_return_status := OKL_API.G_RET_STS_SUCCESS;
4780:
4781: FOR get_prd_details_rec IN get_prd_details_csr(p_khr_id) LOOP
4782: x_deal_type := get_prd_details_rec.deal_type;
4783: IF get_prd_details_rec.quality = 'INTEREST_CALCULATION_BASIS' THEN
4789: END IF;
4790: END LOOP;
4791:
4792: IF x_deal_type IS NULL THEN
4793: x_return_status := OKL_API.G_RET_STS_ERROR;
4794: END IF;
4795:
4796: EXCEPTION
4797:
4796: EXCEPTION
4797:
4798: WHEN OTHERS THEN
4799:
4800: OKL_API.set_message(p_app_name => g_app_name,
4801: p_msg_name => g_unexpected_error,
4802: p_token1 => g_sqlcode_token,
4803: p_token1_value => sqlcode,
4804: p_token2 => g_sqlerrm_token,
4803: p_token1_value => sqlcode,
4804: p_token2 => g_sqlerrm_token,
4805: p_token2_value => sqlerrm);
4806:
4807: x_return_status := OKL_API.G_RET_STS_ERROR;
4808:
4809: END get_contract_product_details;
4810:
4811: -- rmunjulu LOANS_ENHANCEMENTS get excess loan payment amount
4820: l_tax_owner VARCHAR2(300);
4821:
4822: BEGIN
4823:
4824: x_return_status := OKL_API.G_RET_STS_SUCCESS;
4825:
4826: -- Get the contract product details
4827: OKL_AM_UTIL_PVT.get_contract_product_details(
4828: p_khr_id => p_khr_id,
4831: x_int_cal_basis => l_int_cal_basis,
4832: x_tax_owner => l_tax_owner,
4833: x_return_status => x_return_status);
4834:
4835: IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4836: RAISE OKL_API.G_EXCEPTION_ERROR;
4837: END IF;
4838:
4839: -- get refunds only for EstandAct and Act cases
4832: x_tax_owner => l_tax_owner,
4833: x_return_status => x_return_status);
4834:
4835: IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4836: RAISE OKL_API.G_EXCEPTION_ERROR;
4837: END IF;
4838:
4839: -- get refunds only for EstandAct and Act cases
4840: IF l_deal_type LIKE 'LOAN%'
4843: l_loan_refund_amount := OKL_VARIABLE_INT_UTIL_PVT.get_excess_loan_payment(
4844: x_return_status => x_return_status,
4845: p_khr_id => p_khr_id);
4846:
4847: IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4848: RAISE OKL_API.G_EXCEPTION_ERROR;
4849: END IF;
4850: END IF;
4851: RETURN l_loan_refund_amount;
4844: x_return_status => x_return_status,
4845: p_khr_id => p_khr_id);
4846:
4847: IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4848: RAISE OKL_API.G_EXCEPTION_ERROR;
4849: END IF;
4850: END IF;
4851: RETURN l_loan_refund_amount;
4852:
4851: RETURN l_loan_refund_amount;
4852:
4853: EXCEPTION
4854:
4855: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4856:
4857: x_return_status := OKL_API.G_RET_STS_ERROR;
4858:
4859: WHEN OTHERS THEN
4853: EXCEPTION
4854:
4855: WHEN OKL_API.G_EXCEPTION_ERROR THEN
4856:
4857: x_return_status := OKL_API.G_RET_STS_ERROR;
4858:
4859: WHEN OTHERS THEN
4860:
4861: OKL_API.set_message(p_app_name => g_app_name,
4857: x_return_status := OKL_API.G_RET_STS_ERROR;
4858:
4859: WHEN OTHERS THEN
4860:
4861: OKL_API.set_message(p_app_name => g_app_name,
4862: p_msg_name => g_unexpected_error,
4863: p_token1 => g_sqlcode_token,
4864: p_token1_value => sqlcode,
4865: p_token2 => g_sqlerrm_token,
4863: p_token1 => g_sqlcode_token,
4864: p_token1_value => sqlcode,
4865: p_token2 => g_sqlerrm_token,
4866: p_token2_value => sqlerrm);
4867: x_return_status := OKL_API.G_RET_STS_ERROR;
4868:
4869: END get_excess_loan_payment;
4870:
4871: -- rmunjulu BUYOUT check full termination transaction being processed.
4890: l_term_in_progress VARCHAR2(3);
4891:
4892: BEGIN
4893:
4894: x_return_status := OKL_API.G_RET_STS_SUCCESS;
4895:
4896: OPEN get_termination_trn_csr (p_khr_id);
4897: FETCH get_termination_trn_csr INTO l_term_in_progress;
4898: CLOSE get_termination_trn_csr;
4902: EXCEPTION
4903:
4904: WHEN OTHERS THEN
4905:
4906: OKL_API.set_message(p_app_name => g_app_name,
4907: p_msg_name => g_unexpected_error,
4908: p_token1 => g_sqlcode_token,
4909: p_token1_value => sqlcode,
4910: p_token2 => g_sqlerrm_token,
4908: p_token1 => g_sqlcode_token,
4909: p_token1_value => sqlcode,
4910: p_token2 => g_sqlerrm_token,
4911: p_token2_value => sqlerrm);
4912: x_return_status := OKL_API.G_RET_STS_ERROR;
4913: RETURN NULL;
4914:
4915: END check_full_term_in_progress;
4916: --asawanka added
5159: l_book_type_code fa_book_controls.book_type_code%TYPE;
5160: rel_date DATE;
5161: l_units NUMBER := 0;
5162:
5163: x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5164: x_msg_count NUMBER := OKL_API.G_MISS_NUM;
5165: x_msg_data VARCHAR(2000);
5166: l_api_name CONSTANT VARCHAR2(30) := 'GET_NBV';
5167: l_api_version CONSTANT NUMBER := 1.0;
5160: rel_date DATE;
5161: l_units NUMBER := 0;
5162:
5163: x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5164: x_msg_count NUMBER := OKL_API.G_MISS_NUM;
5165: x_msg_data VARCHAR(2000);
5166: l_api_name CONSTANT VARCHAR2(30) := 'GET_NBV';
5167: l_api_version CONSTANT NUMBER := 1.0;
5168: l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
5176: l_currency_conversion_date OKL_K_HEADERS_FULL_V.currency_conversion_date%TYPE;
5177: p_init_msg_list VARCHAR(1) := 'T';
5178:
5179: BEGIN
5180: IF p_chr_id IS NULL OR p_chr_id = OKL_API.G_MISS_NUM THEN
5181: OKL_API.set_message (
5182: p_app_name => G_APP_NAME
5183: ,p_msg_name => G_INVALID_VALUE1
5184: ,p_token1 => 'COL_NAME'
5177: p_init_msg_list VARCHAR(1) := 'T';
5178:
5179: BEGIN
5180: IF p_chr_id IS NULL OR p_chr_id = OKL_API.G_MISS_NUM THEN
5181: OKL_API.set_message (
5182: p_app_name => G_APP_NAME
5183: ,p_msg_name => G_INVALID_VALUE1
5184: ,p_token1 => 'COL_NAME'
5185: ,p_token1_value => 'Contract Id');
5183: ,p_msg_name => G_INVALID_VALUE1
5184: ,p_token1 => 'COL_NAME'
5185: ,p_token1_value => 'Contract Id');
5186:
5187: Raise OKL_API.G_EXCEPTION_ERROR;
5188: END IF;
5189:
5190: IF p_asset_id IS NULL OR p_asset_id = OKL_API.G_MISS_NUM THEN
5191: OKL_API.set_message (
5186:
5187: Raise OKL_API.G_EXCEPTION_ERROR;
5188: END IF;
5189:
5190: IF p_asset_id IS NULL OR p_asset_id = OKL_API.G_MISS_NUM THEN
5191: OKL_API.set_message (
5192: p_app_name => G_APP_NAME
5193: ,p_msg_name => G_INVALID_VALUE1
5194: ,p_token1 => 'COL_NAME'
5187: Raise OKL_API.G_EXCEPTION_ERROR;
5188: END IF;
5189:
5190: IF p_asset_id IS NULL OR p_asset_id = OKL_API.G_MISS_NUM THEN
5191: OKL_API.set_message (
5192: p_app_name => G_APP_NAME
5193: ,p_msg_name => G_INVALID_VALUE1
5194: ,p_token1 => 'COL_NAME'
5195: ,p_token1_value => 'Asset Id');
5193: ,p_msg_name => G_INVALID_VALUE1
5194: ,p_token1 => 'COL_NAME'
5195: ,p_token1_value => 'Asset Id');
5196:
5197: Raise OKL_API.G_EXCEPTION_ERROR;
5198: END IF;
5199:
5200: open get_rel_date(p_chr_id);
5201: fetch get_rel_date into rel_date;
5212: l_asset_hdr_rec.asset_id := p_asset_id;
5213: l_asset_hdr_rec.book_type_code := l_book_type_code;
5214:
5215: if NOT fa_cache_pkg.fazcbc(x_book => l_asset_hdr_rec.book_type_code) then
5216: OKL_API.SET_MESSAGE(p_app_name => g_app_name,
5217: p_msg_name => 'OKL_LLA_FA_CACHE_ERROR'
5218: );
5219: Raise OKL_API.G_EXCEPTION_ERROR;
5220: end if;
5215: if NOT fa_cache_pkg.fazcbc(x_book => l_asset_hdr_rec.book_type_code) then
5216: OKL_API.SET_MESSAGE(p_app_name => g_app_name,
5217: p_msg_name => 'OKL_LLA_FA_CACHE_ERROR'
5218: );
5219: Raise OKL_API.G_EXCEPTION_ERROR;
5220: end if;
5221:
5222: -- To fetch Asset Current Cost
5223: if not FA_UTIL_PVT.get_asset_fin_rec
5226: p_transaction_header_id => NULL,
5227: p_mrc_sob_type_code => 'P'
5228: ) then
5229:
5230: OKL_API.SET_MESSAGE(p_app_name => g_app_name,
5231: p_msg_name => 'OKL_LLA_FA_ASSET_FIN_REC_ERROR'
5232: );
5233: Raise OKL_API.G_EXCEPTION_ERROR;
5234: end if;
5229:
5230: OKL_API.SET_MESSAGE(p_app_name => g_app_name,
5231: p_msg_name => 'OKL_LLA_FA_ASSET_FIN_REC_ERROR'
5232: );
5233: Raise OKL_API.G_EXCEPTION_ERROR;
5234: end if;
5235:
5236: -- To fetch Depreciation Reserve
5237: if not FA_UTIL_PVT.get_asset_deprn_rec
5239: px_asset_deprn_rec => l_asset_deprn_rec,
5240: p_period_counter => NULL,
5241: p_mrc_sob_type_code => 'P'
5242: ) then
5243: OKL_API.SET_MESSAGE(p_app_name => g_app_name,
5244: p_msg_name => 'OKL_LLA_FA_DEPRN_REC_ERROR'
5245: );
5246: Raise OKL_API.G_EXCEPTION_ERROR;
5247: end if;
5242: ) then
5243: OKL_API.SET_MESSAGE(p_app_name => g_app_name,
5244: p_msg_name => 'OKL_LLA_FA_DEPRN_REC_ERROR'
5245: );
5246: Raise OKL_API.G_EXCEPTION_ERROR;
5247: end if;
5248:
5249: l_nbv := l_asset_fin_rec.cost - l_asset_deprn_rec.deprn_reserve;
5250:
5260: x_currency_conversion_rate => l_currency_conversion_rate,
5261: x_currency_conversion_date => l_currency_conversion_date,
5262: x_converted_amount => l_converted_amount);
5263:
5264: IF(x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
5265: Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
5266: p_msg_name => 'OKL_CONV_TO_FUNC_CURRENCY_FAIL');
5267:
5268: RAISE Okl_Api.G_EXCEPTION_ERROR;
5261: x_currency_conversion_date => l_currency_conversion_date,
5262: x_converted_amount => l_converted_amount);
5263:
5264: IF(x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
5265: Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
5266: p_msg_name => 'OKL_CONV_TO_FUNC_CURRENCY_FAIL');
5267:
5268: RAISE Okl_Api.G_EXCEPTION_ERROR;
5269: END IF;
5264: IF(x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
5265: Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
5266: p_msg_name => 'OKL_CONV_TO_FUNC_CURRENCY_FAIL');
5267:
5268: RAISE Okl_Api.G_EXCEPTION_ERROR;
5269: END IF;
5270:
5271: l_nbv := l_converted_amount;
5272: if (l_units > 1) then
5274: end if;
5275:
5276: RETURN l_nbv;
5277: --Call end Activity
5278: OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5279: EXCEPTION
5280:
5281: WHEN OTHERS THEN
5282: null;