[Home] [Help]
50:
51: EXCEPTION
52: WHEN OTHERS THEN
53: -- store SQL error message on message stack
54: OKC_API.SET_MESSAGE
55: (
56: p_app_name => G_APP_NAME,
57: p_msg_name => G_UNEXPECTED_ERROR,
58: p_token1 => G_SQLCODE_TOKEN,
104: RETURN l_contr_name;
105: EXCEPTION
106: WHEN OTHERS THEN
107: -- store SQL error message on message stack
108: OKC_API.SET_MESSAGE
109: (
110: p_app_name => G_APP_NAME,
111: p_msg_name => G_UNEXPECTED_ERROR,
112: p_token1 => G_SQLCODE_TOKEN,
193:
194: EXCEPTION
195: WHEN OTHERS THEN
196: -- store SQL error message on message stack
197: OKC_API.SET_MESSAGE
198: (
199: p_app_name => G_APP_NAME,
200: p_msg_name => G_UNEXPECTED_ERROR,
201: p_token1 => G_SQLCODE_TOKEN,
245: END IF;
246: EXCEPTION
247: WHEN OTHERS THEN
248: -- store SQL error message on message stack
249: OKC_API.SET_MESSAGE
250: (
251: p_app_name => G_APP_NAME,
252: p_msg_name => G_UNEXPECTED_ERROR,
253: p_token1 => G_SQLCODE_TOKEN,
294: l_return_status VARCHAR2(1);
295: l_renewal_status VARCHAR2(30);
296:
297: BEGIN
298: l_return_status := OKC_API.G_RET_STS_SUCCESS;
299: --adding the following code for Bug#4069048
300: -- get renewal status to see if this is called for Electronic renewal
301: -- if so bypass the PO Number validation
302: l_renewal_status := get_renewal_status(p_chr_id);
309: OPEN get_po;
310: FETCH get_po INTO l_po_flag, l_po_num;
311: IF get_po%FOUND THEN
312: IF l_po_num IS NULL THEN
313: OKC_API.set_message(
314: p_app_name => G_APP_NAME,
315: p_msg_name => G_PO_NUM_REQUIRED
316: );
317: -- notify caller of an error
314: p_app_name => G_APP_NAME,
315: p_msg_name => G_PO_NUM_REQUIRED
316: );
317: -- notify caller of an error
318: l_return_status := OKC_API.G_RET_STS_ERROR;
319: END IF;
320: END IF;
321: CLOSE get_po;
322: END IF;
326:
327: EXCEPTION
328: WHEN OTHERS THEN
329: -- store SQL error message on message stack
330: OKC_API.SET_MESSAGE
331: (
332: p_app_name => G_APP_NAME,
333: p_msg_name => G_UNEXPECTED_ERROR,
334: p_token1 => G_SQLCODE_TOKEN,
335: p_token1_value => SQLCODE,
336: p_token2 => G_SQLERRM_TOKEN,
337: p_token2_value => SQLERRM
338: );
339: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
340: -- verify that cursor was closed
341: IF get_po%ISOPEN THEN
342: CLOSE get_po;
343: END IF;
373: l_sr_po_num VARCHAR2(150);
374: l_null VARCHAR2(200);
375:
376: BEGIN
377: x_return_status := OKC_API.G_RET_STS_SUCCESS;
378:
379: -- Check if Service PO flag has been set for SPO rule
380: OPEN rules_cur;
381: FETCH rules_cur INTO l_sr_po_num, l_sr_po_flag;
381: FETCH rules_cur INTO l_sr_po_num, l_sr_po_flag;
382: CLOSE rules_cur;
383: -- If PO flag is set for renewal then check if the po number has been entered.
384: IF l_sr_po_flag = 'Y' AND l_sr_po_num IS NULL THEN
385: OKC_API.set_message(
386: p_app_name => G_APP_NAME,
387: p_msg_name => G_SERVICE_PO_NUM
388: );
389: -- notify caller of an error
386: p_app_name => G_APP_NAME,
387: p_msg_name => G_SERVICE_PO_NUM
388: );
389: -- notify caller of an error
390: x_return_status := OKC_API.G_RET_STS_ERROR;
391: END IF;
392:
393:
394: EXCEPTION
393:
394: EXCEPTION
395: WHEN OTHERS THEN
396: -- store SQL error message on message stack
397: OKC_API.SET_MESSAGE
398: (
399: p_app_name => G_APP_NAME,
400: p_msg_name => G_UNEXPECTED_ERROR,
401: p_token1 => G_SQLCODE_TOKEN,
402: p_token1_value => SQLCODE,
403: p_token2 => G_SQLERRM_TOKEN,
404: p_token2_value => SQLERRM
405: );
406: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
407: END Check_Service_PO_Flag;
408:
409: /*============================================================================+
410: | Procedure: Check_Currency_Conv_Type
437: FROM okc_k_headers_all_b
438: WHERE id = p_chr_id;
439:
440: BEGIN
441: x_return_status := OKC_API.G_RET_STS_SUCCESS;
442:
443: OPEN get_contract_info;
444: FETCH get_contract_info INTO from_currency, l_org_id;
445: CLOSE get_contract_info;
448: IF from_currency <> to_currency THEN
449: OPEN get_rule;
450: FETCH get_rule INTO l_conversion_type;
451: IF get_rule%NOTFOUND THEN
452: OKC_API.set_message(
453: p_app_name => G_APP_NAME,
454: p_msg_name => G_CURR_CONV_REQUIRED
455: );
456: -- notify caller of an error
453: p_app_name => G_APP_NAME,
454: p_msg_name => G_CURR_CONV_REQUIRED
455: );
456: -- notify caller of an error
457: x_return_status := OKC_API.G_RET_STS_ERROR;
458: END IF;
459: CLOSE get_rule;
460: END IF;
461:
461:
462: EXCEPTION
463: WHEN OTHERS THEN
464: -- store SQL error message on message stack
465: OKC_API.SET_MESSAGE
466: (
467: p_app_name => G_APP_NAME,
468: p_msg_name => G_UNEXPECTED_ERROR,
469: p_token1 => G_SQLCODE_TOKEN,
470: p_token1_value => SQLCODE,
471: p_token2 => G_SQLERRM_TOKEN,
472: p_token2_value => SQLERRM
473: );
474: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
475: END Check_Currency_Conv_Type;
476:
477: /*============================================================================+
478: | Procedure: Check_Price_List_Currency
556: l_line_ste_code VARCHAR2(30);
557:
558: BEGIN
559:
560: x_return_status := OKC_API.G_RET_STS_SUCCESS;
561:
562: OPEN get_hdr_info(p_chr_id);
563: FETCH get_hdr_info INTO l_currency_code, l_price_list_id, l_sts_code;
564: CLOSE get_hdr_info;
582:
583:
584: IF UPPER(nvl(l_validate_result, 'N')) <> 'Y' THEN
585: -- Throw error in check list
586: OKC_API.set_message(
587: p_app_name => G_APP_NAME,
588: p_msg_name => G_PRICE_LIST_CURR_H -- MSG FOR INVALID PL AT HDR
589: );
590: -- notify caller of an error
587: p_app_name => G_APP_NAME,
588: p_msg_name => G_PRICE_LIST_CURR_H -- MSG FOR INVALID PL AT HDR
589: );
590: -- notify caller of an error
591: x_return_status := OKC_API.G_RET_STS_ERROR;
592: END IF;
593:
594:
595: -- CHECK RENEWAL PRICE LIST --
606: , l_validate_result => l_validate_result);
607:
608: IF UPPER(nvl(l_validate_result, 'N')) <> 'Y' THEN
609: -- Throw error in check list
610: OKC_API.set_message(
611: p_app_name => G_APP_NAME,
612: p_msg_name => G_PRICE_LIST_CURR_R -- MSG FOR INVALID PL FOR RENEWAL
613: );
614: -- notify caller of an error
611: p_app_name => G_APP_NAME,
612: p_msg_name => G_PRICE_LIST_CURR_R -- MSG FOR INVALID PL FOR RENEWAL
613: );
614: -- notify caller of an error
615: x_return_status := OKC_API.G_RET_STS_ERROR;
616: END IF;
617: END IF ; -- IF NVL(l_renwal_price_list_id, -99) <> -99 THEN
618: END IF; -- If l_hdr_ste_code = 'ENTERED'
619:
635: , l_validate_result => l_validate_result);
636:
637: IF UPPER(nvl(l_validate_result, 'N')) <> 'Y' THEN
638: -- Throw error in check list
639: OKC_API.set_message(
640: p_app_name => G_APP_NAME,
641: p_msg_name => G_PRICE_LIST_CURR_L, -- MSG FOR INVALID PL AT LINES
642: p_token1 => 'TOKEN1',
643: p_token1_value => l_line_number
641: p_msg_name => G_PRICE_LIST_CURR_L, -- MSG FOR INVALID PL AT LINES
642: p_token1 => 'TOKEN1',
643: p_token1_value => l_line_number
644: );
645: x_return_status := OKC_API.G_RET_STS_ERROR;
646: END IF;
647: END IF;
648: CLOSE get_status;
649:
668: ,l_validate_result => l_validate_result);
669:
670: If UPPER(nvl(l_validate_result,'N')) <> 'Y' THEN
671: -- Throw error in check list
672: OKC_API.set_message(
673: p_app_name => G_APP_NAME,
674: p_msg_name => G_PRICE_LIST_CURR_L,-- MSG FOR INVALID PL AT LINES
675: p_token1 => 'TOKEN1',
676: p_token1_value => l_line_number_tbl(i)
674: p_msg_name => G_PRICE_LIST_CURR_L,-- MSG FOR INVALID PL AT LINES
675: p_token1 => 'TOKEN1',
676: p_token1_value => l_line_number_tbl(i)
677: );
678: x_return_status := OKC_API.G_RET_STS_ERROR;
679: End If;
680:
681: END LOOP;
682:
685:
686: EXCEPTION
687: WHEN OTHERS THEN
688: -- store SQL error message on message stack
689: OKC_API.SET_MESSAGE
690: (
691: p_app_name => G_APP_NAME,
692: p_msg_name => G_UNEXPECTED_ERROR,
693: p_token1 => G_SQLCODE_TOKEN,
694: p_token1_value => SQLCODE,
695: p_token2 => G_SQLERRM_TOKEN,
696: p_token2_value => SQLERRM
697: );
698: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
699: END Check_Price_List_Currency;
700:
701: /*============================================================================+
702: | Procedure: Check_Inv_Trx
736: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
737: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_mod_name,'Entering: '|| l_api_name);
738: END IF;
739:
740: x_return_status := OKC_API.G_RET_STS_SUCCESS;
741:
742: OPEN ORG_CUR;
743: FETCH ORG_CUR INTO l_org_id;
744: CLOSE ORG_CUR;
764: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_mod_name,'latin tax, l_inv_trx_type: '|| l_inv_trx_type);
765: END IF;
766:
767: IF l_inv_trx_type IS NULL THEN
768: OKC_API.set_message(
769: p_app_name => G_APP_NAME,
770: p_msg_name => 'OKS_INV_TRX_TYPE_REQUIRED');
771: END IF;
772: END IF;
788: IF TAX_METHOD_CUR%ISOPEN THEN
789: CLOSE TAX_METHOD_CUR;
790: END IF;
791: -- store SQL error message on message stack
792: OKC_API.SET_MESSAGE
793: (
794: p_app_name => G_APP_NAME,
795: p_msg_name => G_UNEXPECTED_ERROR,
796: p_token1 => G_SQLCODE_TOKEN,
797: p_token1_value => SQLCODE,
798: p_token2 => G_SQLERRM_TOKEN,
799: p_token2_value => SQLERRM
800: );
801: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
802: END Check_Inv_Trx;
803:
804:
805:
823: l_return_status VARCHAR2(1);
824:
825: BEGIN
826: -- initialize return status
827: x_return_status := OKC_API.G_RET_STS_SUCCESS;
828:
829: check_req_values
830: (
831: x_return_status => l_return_status,
831: x_return_status => l_return_status,
832: p_chr_id => p_chr_id
833: );
834:
835: IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
836: x_return_status := l_return_status;
837: END IF;
838:
839:
842: x_return_status => l_return_status,
843: p_chr_id => p_chr_id
844: );
845:
846: IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
847: x_return_status := l_return_status;
848: END IF;
849:
850: Check_Service_PO_Flag
852: x_return_status => l_return_status,
853: p_chr_id => p_chr_id
854: );
855:
856: IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
857: x_return_status := l_return_status;
858: END IF;
859:
860: Check_Currency_Conv_Type
861: (
862: x_return_status => l_return_status,
863: p_chr_id => p_chr_id
864: );
865: IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
866: x_return_status := l_return_status;
867: END IF;
868:
869: Check_Price_List_Currency
870: (
871: x_return_status => l_return_status,
872: p_chr_id => p_chr_id
873: );
874: IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
875: x_return_status := l_return_status;
876: END IF;
877:
878: --Begin: Added for R12 eBTax
880: (
881: x_return_status => l_return_status,
882: p_chr_id => p_chr_id
883: );
884: IF l_return_status = OKC_API.G_RET_STS_ERROR THEN
885: x_return_status := l_return_status;
886: END IF;
887: --End: Added for R12 eBTax
888:
887: --End: Added for R12 eBTax
888:
889:
890:
891: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
892: OKC_API.set_message(
893: p_app_name => G_APP_NAME,
894: p_msg_name => 'OKS_QA_SUCCESS');
895: END IF;
888:
889:
890:
891: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
892: OKC_API.set_message(
893: p_app_name => G_APP_NAME,
894: p_msg_name => 'OKS_QA_SUCCESS');
895: END IF;
896:
899: -- no processing necessary; validation can continue with next column
900: NULL;
901: WHEN OTHERS THEN
902: -- store SQL error message on message stack
903: OKC_API.SET_MESSAGE
904: (
905: p_app_name => G_APP_NAME,
906: p_msg_name => G_UNEXPECTED_ERROR,
907: p_token1 => G_SQLCODE_TOKEN,
909: p_token2 => G_SQLERRM_TOKEN,
910: p_token2_value => SQLERRM
911: );
912: -- notify caller of an error as UNEXPETED error
913: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
914: -- verify that cursor was closed
915: END check_required_values;
916:
917:
923: IS
924:
925: BEGIN
926: -- initialize return status
927: x_return_status := OKC_API.G_RET_STS_SUCCESS;
928: NULL;
929: -- code taken out.
930: EXCEPTION
931: WHEN G_EXCEPTION_HALT_VALIDATION THEN
932: -- no processing necessary; validation can continue with next column
933: NULL;
934: WHEN OTHERS THEN
935: -- store SQL error message on message stack
936: OKC_API.SET_MESSAGE(
937: p_app_name => G_APP_NAME,
938: p_msg_name => G_UNEXPECTED_ERROR,
939: p_token1 => G_SQLCODE_TOKEN,
940: p_token1_value => SQLCODE,
940: p_token1_value => SQLCODE,
941: p_token2 => G_SQLERRM_TOKEN,
942: p_token2_value => SQLERRM);
943: -- notify caller of an error as UNEXPETED error
944: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
945:
946:
947: END;
948:
1048: p_start_date IN DATE,
1049: p_end_date IN DATE
1050: ) IS
1051:
1052: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1053: l_dummy VARCHAR2(1) := '?';
1054: l_count NUMBER := 0;
1055:
1056: CURSOR l_chr_csr IS
1090:
1091: BEGIN
1092:
1093: -- initialize return status
1094: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1095:
1096: -- Find Coverage Overlap
1097: OPEN l_chr_csr;
1098: LOOP
1097: OPEN l_chr_csr;
1098: LOOP
1099: FETCH l_chr_csr INTO l_chr_rec;
1100: EXIT WHEN l_chr_csr%NOTFOUND;
1101: x_return_status := OKC_API.G_RET_STS_ERROR;
1102: l_contr_num := l_chr_rec.contract_number || '(' || get_line_name(l_chr_rec.id) || ')';
1103:
1104: IF l_contr_num_list IS NULL THEN
1105: l_contr_num_list := l_contr_num;
1108: END IF;
1109: l_count_contr := l_count_contr + 1;
1110:
1111: IF l_count_contr = 50 OR length(l_contr_num_list) > 30000 THEN
1112: OKC_API.set_message
1113: (
1114: p_app_name => 'OKS',
1115: p_msg_name => G_COVERAGE_OVERLAP_LIST,
1116: p_token1 => 'LINE_NAME',
1121: l_contr_num_list := NULL;
1122: l_count_contr := 0;
1123: END IF;
1124: /*
1125: OKC_API.set_message
1126: (
1127: p_app_name => 'OKS',
1128: p_msg_name => G_COVERAGE_OVERLAP,
1129: p_token1 => 'LINE_NAME',
1137: */
1138: END LOOP;
1139: CLOSE l_chr_csr;
1140: IF l_contr_num_list IS NOT NULL AND l_count_contr > 0 THEN
1141: OKC_API.set_message
1142: (
1143: p_app_name => 'OKS',
1144: p_msg_name => G_COVERAGE_OVERLAP_LIST,
1145: p_token1 => 'LINE_NAME',
1154: -- no processing necessary; validation can continue with next column
1155: NULL;
1156: WHEN OTHERS THEN
1157: -- store SQL error message on message stack
1158: OKC_API.SET_MESSAGE(
1159: p_app_name => G_APP_NAME,
1160: p_msg_name => G_UNEXPECTED_ERROR,
1161: p_token1 => G_SQLCODE_TOKEN,
1162: p_token1_value => SQLCODE,
1162: p_token1_value => SQLCODE,
1163: p_token2 => G_SQLERRM_TOKEN,
1164: p_token2_value => SQLERRM);
1165: -- notify caller of an error as UNEXPETED error
1166: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1167: -- verify that cursor was closed
1168: IF l_chr_csr%ISOPEN THEN
1169: CLOSE l_chr_csr;
1170: END IF;
1204: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
1205: END IF;
1206:
1207: -- initialize return status
1208: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1209:
1210: OPEN c_conv_date;
1211: FETCH c_conv_date INTO l_invalid;
1212: CLOSE c_conv_date;
1212: CLOSE c_conv_date;
1213:
1214: IF l_invalid = 'Y' THEN
1215:
1216: x_return_status := OKC_API.G_RET_STS_ERROR;
1217: OKC_API.set_message(
1218: p_app_name => G_APP_NAME,
1219: p_msg_name => 'OKS_INV_CURR_CONV_DATE');
1220:
1213:
1214: IF l_invalid = 'Y' THEN
1215:
1216: x_return_status := OKC_API.G_RET_STS_ERROR;
1217: OKC_API.set_message(
1218: p_app_name => G_APP_NAME,
1219: p_msg_name => 'OKS_INV_CURR_CONV_DATE');
1220:
1221: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1223: END IF;
1224:
1225: END IF;
1226:
1227: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1228: OKC_API.set_message(
1229: p_app_name => G_APP_NAME,
1230: p_msg_name => 'OKS_QA_SUCCESS');
1231: END IF;
1224:
1225: END IF;
1226:
1227: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1228: OKC_API.set_message(
1229: p_app_name => G_APP_NAME,
1230: p_msg_name => 'OKS_QA_SUCCESS');
1231: END IF;
1232: END;
1253: ) IS
1254:
1255: /***
1256: Bug 4767013: commented out
1257: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1258: l_dummy VARCHAR2(1) := '?';
1259: l_count NUMBER := 0;
1260:
1261: CURSOR l_cle_csr IS
1311: TYPE chr640_tbl_type IS TABLE OF VARCHAR2(640) INDEX BY BINARY_INTEGER;
1312: TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1313:
1314: CURSOR c_chk_overlap(cp_chr_id IN NUMBER, cp_date IN DATE) IS
1315: SELECT trgh.contract_number, nvl(trgh.contract_number_modifier, OKC_API.G_MISS_CHAR),
1316: nvl(rtrim(trgtl.line_number) || '.' || RTRIM(trgsl.line_number) ||', '|| RTRIM(trgst.name) ||' '|| RTRIM(trgsl.name), OKC_API.G_MISS_CHAR) trg_name,
1317: nvl(rtrim(srctl.line_number) || '.' || RTRIM(srcsl.line_number) ||', '|| RTRIM(srcst.name) ||' '|| RTRIM(srcsl.name), OKC_API.G_MISS_CHAR) src_name,
1318: srcsl.id srcsl_id
1319: FROM okc_k_headers_all_b trgh,
1312: TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1313:
1314: CURSOR c_chk_overlap(cp_chr_id IN NUMBER, cp_date IN DATE) IS
1315: SELECT trgh.contract_number, nvl(trgh.contract_number_modifier, OKC_API.G_MISS_CHAR),
1316: nvl(rtrim(trgtl.line_number) || '.' || RTRIM(trgsl.line_number) ||', '|| RTRIM(trgst.name) ||' '|| RTRIM(trgsl.name), OKC_API.G_MISS_CHAR) trg_name,
1317: nvl(rtrim(srctl.line_number) || '.' || RTRIM(srcsl.line_number) ||', '|| RTRIM(srcst.name) ||' '|| RTRIM(srcsl.name), OKC_API.G_MISS_CHAR) src_name,
1318: srcsl.id srcsl_id
1319: FROM okc_k_headers_all_b trgh,
1320: okc_k_lines_v trgsl,
1313:
1314: CURSOR c_chk_overlap(cp_chr_id IN NUMBER, cp_date IN DATE) IS
1315: SELECT trgh.contract_number, nvl(trgh.contract_number_modifier, OKC_API.G_MISS_CHAR),
1316: nvl(rtrim(trgtl.line_number) || '.' || RTRIM(trgsl.line_number) ||', '|| RTRIM(trgst.name) ||' '|| RTRIM(trgsl.name), OKC_API.G_MISS_CHAR) trg_name,
1317: nvl(rtrim(srctl.line_number) || '.' || RTRIM(srcsl.line_number) ||', '|| RTRIM(srcst.name) ||' '|| RTRIM(srcsl.name), OKC_API.G_MISS_CHAR) src_name,
1318: srcsl.id srcsl_id
1319: FROM okc_k_headers_all_b trgh,
1320: okc_k_lines_v trgsl,
1321: okc_k_items trgi,
1379: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
1380: END IF;
1381:
1382: -- initialize return status
1383: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1384: l_date := SYSDATE;
1385:
1386: OPEN c_chk_overlap(p_chr_id, l_date);
1387: LOOP
1391: EXIT WHEN (l_k_num_tbl.COUNT = 0);
1392:
1393: FOR i IN l_k_num_tbl.FIRST..l_k_num_tbl.LAST LOOP
1394:
1395: x_return_status := OKC_API.G_RET_STS_ERROR;
1396:
1397: IF i > l_k_num_tbl.FIRST AND l_srcsl_id_tbl(l_srcsl_id_tbl.PRIOR(i)) <> l_srcsl_id_tbl(i) AND l_count_contr > 0 THEN
1398: -- the source subline has changed so we need to dump information (if any) for the last source subline processed
1399:
1404: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'previous l_count_contr: ' || l_count_contr);
1405: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'previous l_contr_num_list: ' || l_contr_num_list);
1406: END IF;
1407:
1408: OKC_API.set_message
1409: (
1410: p_app_name => 'OKS',
1411: p_msg_name => G_COVERAGE_OVERLAP_LIST,
1412: p_token1 => 'LINE_NAME',
1451: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_count_contr: ' || l_count_contr);
1452: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_contr_num_list: ' || l_contr_num_list);
1453: END IF;
1454:
1455: OKC_API.set_message
1456: (
1457: p_app_name => 'OKS',
1458: p_msg_name => G_COVERAGE_OVERLAP_LIST,
1459: p_token1 => 'LINE_NAME',
1483: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_count_contr: ' || l_count_contr);
1484: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_contr_num_list: ' || l_contr_num_list);
1485: END IF;
1486:
1487: OKC_API.set_message
1488: (
1489: p_app_name => 'OKS',
1490: p_msg_name => G_COVERAGE_OVERLAP_LIST,
1491: p_token1 => 'LINE_NAME',
1508: /***
1509: Bug 4767013 commented out
1510:
1511: -- initialize return status
1512: l_return_status := OKC_API.G_RET_STS_SUCCESS;
1513:
1514: -- Get Top Lines
1515: OPEN l_cle_csr;
1516: LOOP
1533: ,l_cve_rec.object1_id2
1534: ,l_cve_rec.start_date
1535: ,nvl(l_cve_rec.date_terminated, l_cve_rec.end_date)
1536: );
1537: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1538: x_return_status := l_return_status;
1539: END IF;
1540:
1541: END LOOP;
1545: CLOSE l_cle_csr;
1546: x_return_status := l_return_status;
1547: ***/
1548:
1549: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1550: OKC_API.set_message(
1551: p_app_name => G_APP_NAME,
1552: p_msg_name => 'OKS_QA_SUCCESS');
1553: END IF;
1546: x_return_status := l_return_status;
1547: ***/
1548:
1549: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1550: OKC_API.set_message(
1551: p_app_name => G_APP_NAME,
1552: p_msg_name => 'OKS_QA_SUCCESS');
1553: END IF;
1554:
1567: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'WHEN OTHERS: setting message after encountering error: ' || SQLCODE || ' ' || SQLERRM);
1568: END IF;
1569:
1570: -- store SQL error message on message stack
1571: OKC_API.SET_MESSAGE(
1572: p_app_name => G_APP_NAME,
1573: p_msg_name => G_UNEXPECTED_ERROR,
1574: p_token1 => G_SQLCODE_TOKEN,
1575: p_token1_value => SQLCODE,
1575: p_token1_value => SQLCODE,
1576: p_token2 => G_SQLERRM_TOKEN,
1577: p_token2_value => SQLERRM);
1578: -- notify caller of an error as UNEXPETED error
1579: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1580: -- verify that cursor was closed
1581: IF c_chk_overlap%ISOPEN THEN
1582: CLOSE c_chk_overlap;
1583: END IF;
1611: x_return_status OUT NOCOPY VARCHAR2,
1612: p_chr_id IN NUMBER
1613: ) IS
1614:
1615: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1616: l_dummy VARCHAR2(1) := '?';
1617: l_count NUMBER := 0;
1618:
1619: CURSOR l_cle_csr IS
1777: l_org_id NUMBER;
1778:
1779: BEGIN
1780: -- initialize return status
1781: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1782:
1783: -- The following code added for bug # 2364436 - Anupama
1784:
1785: OPEN l_cvd_lvl_csr (p_chr_id);
1790: OPEN l_cvd_item_csr (l_cvd_lvl_rec.id);
1791: FETCH l_cvd_item_csr INTO l_cvd_item_rec;
1792: IF (l_cvd_item_csr%NOTFOUND) OR (l_cvd_item_rec.object1_id1 IS NULL) THEN
1793: /**
1794: OKC_API.set_message(
1795: p_app_name => G_APP_NAME,
1796: p_msg_name => G_REQUIRED_LINE_VALUE,
1797: p_token1 => G_COL_NAME_TOKEN,
1798: p_token1_value => 'Covered Level Name',
1799: p_token2 => 'LINE_NAME',
1800: p_token2_value => get_line_name(l_cvd_lvl_rec.id));
1801: **/
1802:
1803: OKC_API.set_message(
1804: p_app_name => G_APP_NAME,
1805: p_msg_name => G_COVERED_LINE_REQUIRED,
1806: p_token1 => 'LINE_NAME',
1807: p_token1_value => get_line_name(l_cvd_lvl_rec.id));
1806: p_token1 => 'LINE_NAME',
1807: p_token1_value => get_line_name(l_cvd_lvl_rec.id));
1808:
1809: -- notify caller of an error
1810: x_return_status := OKC_API.G_RET_STS_ERROR;
1811: END IF;
1812: CLOSE l_cvd_item_csr;
1813: END LOOP;
1814: CLOSE l_cvd_lvl_csr;
1829: IF l_cve_rec.lty_code <> 'INST_CTR' THEN
1830: -- Negotiated amount at covered levels is required.
1831: IF l_cve_rec.PRICE_NEGOTIATED IS NULL THEN
1832: /**
1833: OKC_API.set_message(
1834: p_app_name => G_APP_NAME,
1835: p_msg_name => G_REQUIRED_LINE_VALUE,
1836: p_token1 => G_COL_NAME_TOKEN,
1837: p_token1_value => 'Final Price',
1838: p_token2 => 'LINE_NAME',
1839: p_token2_value => get_line_name(l_cve_rec.id));
1840: **/
1841:
1842: OKC_API.set_message(
1843: p_app_name => G_APP_NAME,
1844: p_msg_name => G_NEG_AMT_REQUIRED,
1845: p_token1 => 'LINE_NAME',
1846: p_token1_value => get_line_name(l_cve_rec.id));
1844: p_msg_name => G_NEG_AMT_REQUIRED,
1845: p_token1 => 'LINE_NAME',
1846: p_token1_value => get_line_name(l_cve_rec.id));
1847: -- notify caller of an error
1848: x_return_status := OKC_API.G_RET_STS_ERROR;
1849: END IF;
1850: END IF;
1851:
1852:
1859: FETCH l_default_csr INTO l_default_qty;
1860: CLOSE l_default_csr;
1861:
1862: IF l_usage_type = 'VRT' AND l_default_qty IS NULL THEN
1863: x_return_status := OKC_API.G_RET_STS_ERROR;
1864:
1865: OKC_API.set_message
1866: (
1867: p_app_name => G_APP_NAME,
1861:
1862: IF l_usage_type = 'VRT' AND l_default_qty IS NULL THEN
1863: x_return_status := OKC_API.G_RET_STS_ERROR;
1864:
1865: OKC_API.set_message
1866: (
1867: p_app_name => G_APP_NAME,
1868: p_msg_name => G_DEFAULT_READING,
1869: p_token1 => 'TOKEN',
1877: -- if statement added for subscription lines.
1878: IF l_cle_rec.lse_id <> 46 THEN
1879: -- A Contract Line must have at least 1 covered level line
1880: IF l_cve_csr%ROWCOUNT <= 0 THEN
1881: OKC_API.set_message(
1882: p_app_name => G_APP_NAME,
1883: p_msg_name => G_REQUIRED_COVERED_LINE,
1884: p_token1 => 'LINE_NAME',
1885: p_token1_value => get_line_name(l_cle_rec.id));
1883: p_msg_name => G_REQUIRED_COVERED_LINE,
1884: p_token1 => 'LINE_NAME',
1885: p_token1_value => get_line_name(l_cle_rec.id));
1886: -- notify caller of an error
1887: x_return_status := OKC_API.G_RET_STS_ERROR;
1888: END IF;
1889: END IF; -- l_cle_rec.lse_id <> 46
1890: CLOSE l_cve_csr;
1891:
1895: CLOSE l_cva_csr;
1896:
1897: IF l_count > 1 THEN
1898: -- A Service Line must have at most 1 coverage line
1899: OKC_API.set_message(
1900: p_app_name => G_APP_NAME,
1901: p_msg_name => G_INVALID_COVERAGE_LINE,
1902: p_token1 => 'LINE_NAME',
1903: p_token1_value => get_line_name(l_cle_rec.id));
1901: p_msg_name => G_INVALID_COVERAGE_LINE,
1902: p_token1 => 'LINE_NAME',
1903: p_token1_value => get_line_name(l_cle_rec.id));
1904: -- notify caller of an error
1905: x_return_status := OKC_API.G_RET_STS_ERROR;
1906: END IF;
1907: END IF;
1908:
1909:
1937:
1938: IF l_sc_percent <> 100 THEN
1939:
1940: /**
1941: OKC_API.set_message(
1942: p_app_name => G_APP_NAME,
1943: p_msg_name => G_REQUIRED_LINE_VALUE,
1944: p_token1 => G_COL_NAME_TOKEN,
1945: p_token1_value => 'Quota Sales Credit at line must be assigned to 100%',
1945: p_token1_value => 'Quota Sales Credit at line must be assigned to 100%',
1946: p_token2 => 'LINE_NAME',
1947: p_token2_value => get_line_name(l_cle_rec.id));
1948: **/
1949: OKC_API.set_message( -- Bug 4708540 (changed message)
1950: p_app_name => G_APP_NAME,
1951: p_msg_name => 'OKS_INCOMP_QUOTA_SALES_CREDIT',
1952: p_token1 => 'LINE_NAME',
1953: p_token1_value => get_line_name(l_cle_rec.id));
1952: p_token1 => 'LINE_NAME',
1953: p_token1_value => get_line_name(l_cle_rec.id));
1954:
1955: -- notify caller of an error
1956: x_return_status := OKC_API.G_RET_STS_ERROR;
1957:
1958: END IF;
1959:
1960: END IF;
1980: -- Bug 4558172--
1981: IF nvl(l_cust_id, 0) <> nvl(l_customer_id, - 99) THEN
1982: -- Bug 4558172--
1983: /**
1984: OKC_API.set_message(
1985: p_app_name => G_APP_NAME,
1986: p_msg_name => G_REQUIRED_VALUE,
1987: p_token1 => G_COL_NAME_TOKEN,
1988: p_token1_value => 'INVALID BILLING CONTACTS ');
1987: p_token1 => G_COL_NAME_TOKEN,
1988: p_token1_value => 'INVALID BILLING CONTACTS ');
1989: **/
1990:
1991: OKC_API.set_message(
1992: p_app_name => G_APP_NAME,
1993: p_msg_name => G_INVALID_BILLING_CONTACTS);
1994:
1995: -- notify caller of an error
1992: p_app_name => G_APP_NAME,
1993: p_msg_name => G_INVALID_BILLING_CONTACTS);
1994:
1995: -- notify caller of an error
1996: x_return_status := OKC_API.G_RET_STS_ERROR;
1997: END IF;
1998:
1999: END IF;
2000:
2005: -- A Contract must have at least 1 Contract Line
2006:
2007: IF l_cle_csr%ROWCOUNT <= 0 THEN
2008: /**
2009: OKC_API.set_message(
2010: p_app_name => G_APP_NAME,
2011: p_msg_name => G_REQUIRED_VALUE,
2012: p_token1 => G_COL_NAME_TOKEN,
2013: p_token1_value => 'Contract Line');
2012: p_token1 => G_COL_NAME_TOKEN,
2013: p_token1_value => 'Contract Line');
2014: **/
2015:
2016: OKC_API.set_message(
2017: p_app_name => G_APP_NAME,
2018: p_msg_name => G_LINE_REQUIRED);
2019:
2020:
2018: p_msg_name => G_LINE_REQUIRED);
2019:
2020:
2021: -- notify caller of an error
2022: x_return_status := OKC_API.G_RET_STS_ERROR;
2023: END IF;
2024:
2025:
2026: CLOSE l_cle_csr;
2034: FETCH l_salesperson_contact INTO l_contact_id;
2035: CLOSE l_salesperson_contact;
2036:
2037: IF l_contact_id IS NULL THEN
2038: OKC_API.set_message(
2039: p_app_name => G_APP_NAME,
2040: p_msg_name => G_MISSING_SALESREP);
2041:
2042: -- notify caller of an error
2039: p_app_name => G_APP_NAME,
2040: p_msg_name => G_MISSING_SALESREP);
2041:
2042: -- notify caller of an error
2043: x_return_status := OKC_API.G_RET_STS_ERROR;
2044: END IF;
2045:
2046: END IF;
2047:
2046: END IF;
2047:
2048:
2049:
2050: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2051: OKC_API.set_message(
2052: p_app_name => G_APP_NAME,
2053: p_msg_name => 'OKS_QA_SUCCESS');
2054: END IF;
2047:
2048:
2049:
2050: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2051: OKC_API.set_message(
2052: p_app_name => G_APP_NAME,
2053: p_msg_name => 'OKS_QA_SUCCESS');
2054: END IF;
2055:
2060: -- no processing necessary; validation can continue with next column
2061: NULL;
2062: WHEN OTHERS THEN
2063: -- store SQL error message on message stack
2064: OKC_API.SET_MESSAGE(
2065: p_app_name => G_APP_NAME,
2066: p_msg_name => G_UNEXPECTED_ERROR,
2067: p_token1 => G_SQLCODE_TOKEN,
2068: p_token1_value => SQLCODE,
2068: p_token1_value => SQLCODE,
2069: p_token2 => G_SQLERRM_TOKEN,
2070: p_token2_value => SQLERRM);
2071: -- notify caller of an error as UNEXPETED error
2072: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2073: -- verify that cursor was closed
2074: IF l_cle_csr%ISOPEN THEN
2075: CLOSE l_cle_csr;
2076: END IF;
2100: x_return_status OUT NOCOPY VARCHAR2,
2101: p_chr_id IN NUMBER
2102: ) IS
2103:
2104: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2105: l_dummy VARCHAR2(1) := '?';
2106: l_count NUMBER := 0;
2107:
2108: CURSOR l_cle_csr IS
2304:
2305:
2306: BEGIN
2307: -- initialize return status
2308: x_return_status := OKC_API.G_RET_STS_SUCCESS;
2309:
2310: --changes for passing org_id in is_service_available
2311: OPEN get_auth_org_csr;
2312: FETCH get_auth_org_csr INTO get_auth_org_rec;
2324: FROM okc_k_lines_b
2325: WHERE id = l_cle_rec.cle_id;
2326:
2327: IF l_date_cancelled IS NULL THEN
2328: OKC_API.set_message(
2329: p_app_name => G_APP_NAME,
2330: p_msg_name => G_INVALID_LINE_ITEM,
2331: p_token1 => 'LINE_NAME',
2332: --p_token1_value => get_line_name(l_cle_rec.id));
2333: p_token1_value => get_line_name(l_cle_rec.cle_id));
2334:
2335:
2336: -- notify caller of an error
2337: x_return_status := OKC_API.G_RET_STS_ERROR;
2338: END IF;
2339:
2340: END LOOP;
2341: CLOSE l_cle_csr;
2345: --OPEN l_cle1_csr;
2346: --LOOP
2347: -- FETCH l_cle1_csr INTO l_cle1_rec;
2348: -- EXIT WHEN l_cle1_csr%NOTFOUND;
2349: -- OKC_API.set_message(
2350: -- p_app_name => G_APP_NAME,
2351: -- p_msg_name => G_REQUIRED_LINE_VALUE,
2352: -- p_token1 => G_COL_NAME_TOKEN,
2353: -- p_token1_value => 'Name',
2354: -- p_token2 => 'LINE_NAME',
2355: -- p_token2_value => get_line_name(l_cle1_rec.id));
2356:
2357: -- -- notify caller of an error
2358: -- x_return_status := OKC_API.G_RET_STS_ERROR;
2359: --END LOOP;
2360: --CLOSE l_cle1_csr;
2361: OPEN l_qa_csr;
2362: FETCH l_qa_csr INTO l_k_no, l_qcl, l_contr_category;
2362: FETCH l_qa_csr INTO l_k_no, l_qcl, l_contr_category;
2363: CLOSE l_qa_csr;
2364:
2365: IF l_qcl IS NULL THEN
2366: OKC_API.set_message(
2367: p_app_name => G_APP_NAME,
2368: p_msg_name => G_QA_CHECK,
2369: p_token1 => 'TOKEN',
2370: p_token1_value => l_k_no);
2369: p_token1 => 'TOKEN',
2370: p_token1_value => l_k_no);
2371:
2372: -- notify caller of an error
2373: x_return_status := OKC_API.G_RET_STS_ERROR;
2374: END IF;
2375:
2376: IF l_contr_category = 'SUBSCRIPTION' THEN
2377: l_customer := 'SUBSCRIBER';
2385: FETCH l_cpl_csr INTO l_count;
2386: CLOSE l_cpl_csr;
2387:
2388: IF (l_count > 1) THEN
2389: OKC_API.set_message(
2390: p_app_name => G_APP_NAME,
2391: p_msg_name => G_PARTY_ROLE,
2392: p_token1 => 'ROLE',
2393: p_token1_value => l_customer);
2391: p_msg_name => G_PARTY_ROLE,
2392: p_token1 => 'ROLE',
2393: p_token1_value => l_customer);
2394: -- notify caller of an error
2395: x_return_status := OKC_API.G_RET_STS_ERROR;
2396: END IF;
2397:
2398: -- It is required that one customer or subscriber may be attached to
2399: -- a contract. Added by mkhayer on 05/03/2002
2397:
2398: -- It is required that one customer or subscriber may be attached to
2399: -- a contract. Added by mkhayer on 05/03/2002
2400: IF (l_count < 1) THEN
2401: OKC_API.set_message(
2402: p_app_name => G_APP_NAME,
2403: p_msg_name => G_PARTY_MISSING_ROLE,
2404: p_token1 => 'ROLE',
2405: p_token1_value => l_customer);
2403: p_msg_name => G_PARTY_MISSING_ROLE,
2404: p_token1 => 'ROLE',
2405: p_token1_value => l_customer);
2406: -- notify caller of an error (The contract has no Customer attached to it)
2407: x_return_status := OKC_API.G_RET_STS_ERROR;
2408: END IF;
2409:
2410: -- At most only 1 vendor or merchant may be attached to the Contract
2411: OPEN l_cpl_csr(l_vedor);
2412: FETCH l_cpl_csr INTO l_count;
2413: CLOSE l_cpl_csr;
2414:
2415: IF (l_count > 1) THEN
2416: OKC_API.set_message(
2417: p_app_name => G_APP_NAME,
2418: p_msg_name => G_PARTY_ROLE,
2419: p_token1 => 'ROLE',
2420: p_token1_value => l_vedor);
2418: p_msg_name => G_PARTY_ROLE,
2419: p_token1 => 'ROLE',
2420: p_token1_value => l_vedor);
2421: -- notify caller of an error
2422: x_return_status := OKC_API.G_RET_STS_ERROR;
2423: END IF;
2424:
2425: -- It is required that one vendor or merchant may be attached to a contract.
2426: -- Added by mkhayer on 05/03/2002
2424:
2425: -- It is required that one vendor or merchant may be attached to a contract.
2426: -- Added by mkhayer on 05/03/2002
2427: IF (l_count < 1) THEN
2428: OKC_API.set_message(
2429: p_app_name => G_APP_NAME,
2430: p_msg_name => G_PARTY_MISSING_ROLE,
2431: p_token1 => 'ROLE',
2432: p_token1_value => l_vedor);
2430: p_msg_name => G_PARTY_MISSING_ROLE,
2431: p_token1 => 'ROLE',
2432: p_token1_value => l_vedor);
2433: -- notify caller of an error (The contract has no Vendor attached to it)
2434: x_return_status := OKC_API.G_RET_STS_ERROR;
2435: END IF;
2436:
2437: OPEN l_desc_csr;
2438: FETCH l_desc_csr INTO l_k_no, l_desc;
2438: FETCH l_desc_csr INTO l_k_no, l_desc;
2439: CLOSE l_desc_csr;
2440:
2441: IF l_desc IS NULL THEN
2442: OKC_API.set_message(
2443: p_app_name => G_APP_NAME,
2444: p_msg_name => G_SHORT_DESC,
2445: p_token1 => 'TOKEN',
2446: p_token1_value => l_k_no);
2444: p_msg_name => G_SHORT_DESC,
2445: p_token1 => 'TOKEN',
2446: p_token1_value => l_k_no);
2447: -- notify caller of an error
2448: x_return_status := OKC_API.G_RET_STS_ERROR;
2449: END IF;
2450:
2451: OPEN l_price_csr;
2452: FETCH l_price_csr INTO l_price_id;
2452: FETCH l_price_csr INTO l_price_id;
2453: CLOSE l_price_csr;
2454:
2455: IF l_price_id IS NULL THEN
2456: OKC_API.set_message(
2457: p_app_name => G_APP_NAME,
2458: p_msg_name => G_PRICE_LIST,
2459: p_token1 => 'TOKEN',
2460: --p_token1_value => 'BILLING HEADER');
2459: p_token1 => 'TOKEN',
2460: --p_token1_value => 'BILLING HEADER');
2461: p_token1_value => l_k_no);
2462: -- notify caller of an error
2463: x_return_status := OKC_API.G_RET_STS_ERROR;
2464: END IF;
2465:
2466:
2467: OPEN l_k_grp_csr;
2468: FETCH l_k_grp_csr INTO l_grp_id;
2469:
2470: IF l_grp_id IS NULL THEN
2471:
2472: OKC_API.set_message(
2473: p_app_name => G_APP_NAME,
2474: p_msg_name => G_K_GROUP,
2475: p_token1 => 'TOKEN',
2476: --p_token1_value => 'Header Admin');
2477: p_token1_value => l_k_no);
2478:
2479:
2480: -- notify caller of an error
2481: x_return_status := OKC_API.G_RET_STS_ERROR;
2482: END IF;
2483:
2484: CLOSE l_k_grp_csr;
2485:
2489: CLOSE l_wf_csr;
2490:
2491: IF l_wf_id IS NULL THEN
2492:
2493: OKC_API.set_message(
2494: p_app_name => G_APP_NAME,
2495: p_msg_name => G_WORKFLOW );
2496:
2497:
2495: p_msg_name => G_WORKFLOW );
2496:
2497:
2498: -- notify caller of an error
2499: x_return_status := OKC_API.G_RET_STS_ERROR;
2500: END IF;
2501:
2502: /* check for product availability*/
2503: /*** Get customer_id ****/
2591: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_PKG_NAME||'.'||l_api_name,'l_org_id: '||get_auth_org_rec.authoring_org_id);
2592: END IF;
2593:
2594: If l_available_yn = 'N' then
2595: x_return_status := OKC_API.G_RET_STS_ERROR;
2596:
2597: OKC_API.set_message
2598: (
2599: p_app_name => 'OKS',
2593:
2594: If l_available_yn = 'N' then
2595: x_return_status := OKC_API.G_RET_STS_ERROR;
2596:
2597: OKC_API.set_message
2598: (
2599: p_app_name => 'OKS',
2600: p_msg_name => 'OKS_PRODUCT_AVAILABILITY',
2601: p_token1 => 'TOKEN1',
2628: IF (l_get_sub_line_rec.price_unit IS NULL OR l_get_item_rec.uom_code IS NULL)
2629: THEN
2630:
2631: -- store SQL error message on message stack
2632: OKC_API.SET_MESSAGE
2633: (
2634: p_app_name => G_APP_NAME,
2635: p_msg_name => 'OKS_UOM_VALUES',
2636: p_token1 => 'TOKEN1',
2635: p_msg_name => 'OKS_UOM_VALUES',
2636: p_token1 => 'TOKEN1',
2637: p_token1_value => get_line_name(l_get_sub_line_rec.id));
2638:
2639: x_return_status := OKC_API.G_RET_STS_ERROR;
2640:
2641: END IF; -- IF (l_get_sub_line_rec.price_unit IS NULL OR l_get_item_rec.uom_code IS NULL)
2642:
2643: END LOOP; --FOR l_get_item_rec in l_get_item_csr
2659: IF (l_get_item_rec.uom_code IS NULL)
2660: THEN
2661:
2662: -- store SQL error message on message stack
2663: OKC_API.SET_MESSAGE
2664: (
2665: p_app_name => G_APP_NAME,
2666: p_msg_name => 'OKS_UOM_VALUES',
2667: p_token1 => 'TOKEN1',
2666: p_msg_name => 'OKS_UOM_VALUES',
2667: p_token1 => 'TOKEN1',
2668: p_token1_value => get_line_name(l_get_sub_line_rec.id));
2669:
2670: x_return_status := OKC_API.G_RET_STS_ERROR;
2671:
2672: END IF; -- IF (l_get_sub_line_rec.price_unit IS NULL OR l_get_item_rec.uom_code IS NULL)
2673:
2674: END LOOP; --FOR l_get_item_rec in l_get_item_csr
2700:
2701: IF (l_price_unit_tbl(i) IS NULL OR l_uom_code_tbl(i) IS NULL)
2702: THEN
2703: -- store SQL error message on message stack
2704: OKC_API.SET_MESSAGE
2705: (
2706: p_app_name => G_APP_NAME,
2707: p_msg_name => 'OKS_UOM_VALUES',
2708: p_token1 => 'TOKEN1',
2707: p_msg_name => 'OKS_UOM_VALUES',
2708: p_token1 => 'TOKEN1',
2709: p_token1_value => get_line_name(l_sub_line_id_tbl(i)));
2710:
2711: x_return_status := OKC_API.G_RET_STS_ERROR;
2712:
2713: END IF;
2714:
2715: ELSE
2716: Bug #6317316 commented the above IF part */
2717: IF (l_uom_code_tbl(i) IS NULL)
2718: THEN
2719: -- store SQL error message on message stack
2720: OKC_API.SET_MESSAGE
2721: (
2722: p_app_name => G_APP_NAME,
2723: p_msg_name => 'OKS_UOM_VALUES',
2724: p_token1 => 'TOKEN1',
2723: p_msg_name => 'OKS_UOM_VALUES',
2724: p_token1 => 'TOKEN1',
2725: p_token1_value => get_line_name(l_sub_line_id_tbl(i)));
2726:
2727: x_return_status := OKC_API.G_RET_STS_ERROR;
2728:
2729: END IF;
2730: --commented for bug#6317316 harlaksh
2731: --END IF;
2739:
2740:
2741:
2742: /* Commented out because it's getting called in check_required_values
2743: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2744: OKC_API.set_message(
2745: p_app_name => G_APP_NAME,
2746: p_msg_name => 'OKS_QA_SUCCESS');
2747: END IF;
2740:
2741:
2742: /* Commented out because it's getting called in check_required_values
2743: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2744: OKC_API.set_message(
2745: p_app_name => G_APP_NAME,
2746: p_msg_name => 'OKS_QA_SUCCESS');
2747: END IF;
2748: */
2751: -- no processing necessary; validation can continue with next column
2752: NULL;
2753: WHEN OTHERS THEN
2754: -- store SQL error message on message stack
2755: OKC_API.SET_MESSAGE(
2756: p_app_name => G_APP_NAME,
2757: p_msg_name => G_UNEXPECTED_ERROR,
2758: p_token1 => G_SQLCODE_TOKEN,
2759: p_token1_value => SQLCODE,
2759: p_token1_value => SQLCODE,
2760: p_token2 => G_SQLERRM_TOKEN,
2761: p_token2_value => SQLERRM);
2762: -- notify caller of an error as UNEXPETED error
2763: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2764: -- verify that cursor was closed
2765:
2766: IF l_cle_csr%ISOPEN THEN
2767: CLOSE l_cle_csr;
2868:
2869: RETURN l_cust_trx_type_id;
2870: EXCEPTION
2871: WHEN OTHERS THEN
2872: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
2873: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2874: FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, G_PKG_NAME || '.' || l_api_name,'Exception OTHERS: '|| SQLERRM);
2875: END IF;
2876:
3138: RAISE FND_API.g_exc_error;
3139: END IF;
3140:
3141: -- initialize return status
3142: x_return_status := OKC_API.G_RET_STS_SUCCESS;
3143:
3144:
3145:
3146: --modified top_line_csr to use bulk collect for bug 5442886
3287:
3288:
3289: IF l_valid_flag <> 'Y' THEN
3290:
3291: OKC_API.set_message(p_app_name => G_APP_NAME,
3292: p_msg_name => G_INVALID_TAX_EXEMPT_DATE,
3293: p_token1 => 'LINE_NUM',
3294: p_token1_value => l_number_tbl(i),
3295: p_token2 => 'EXEMPT_NUM',
3295: p_token2 => 'EXEMPT_NUM',
3296: p_token2_value => l_exempt_cert_number_tbl(i)
3297: );
3298:
3299: x_return_status := OKC_API.G_RET_STS_ERROR;
3300:
3301:
3302: -- GCHADHA --
3303: -- IKON Enhancement --
3349: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_mod_name,'l_legal_entity_id: '|| l_legal_entity_id);
3350: END IF;
3351:
3352: IF l_valid_flag <> 'Y' THEN
3353: OKC_API.set_message(
3354: p_app_name => G_APP_NAME,
3355: p_msg_name => 'OKS_PARTIAL_TAX_EXEMPT_LINE',
3356: p_token1 => 'EXEMPT_NUM',
3357: p_token1_value => l_exempt_cert_number_tbl(i),
3357: p_token1_value => l_exempt_cert_number_tbl(i),
3358: p_token2 => 'LINE_NUM',
3359: p_token2_value => l_number_tbl(i));
3360: -- notify caller of an error
3361: x_return_status := OKC_API.G_RET_STS_ERROR;
3362:
3363: /** not needed for new R12 flow bug 5264786
3364: ELSE
3365:
3369: IF l_tax_exempt_acct <> l_cust_acct_id THEN
3370: OPEN Get_Customer_Name (l_cust_acct_id);
3371: FETCH Get_Customer_Name INTO l_cust_acct_name;
3372: CLOSE Get_Customer_Name;
3373: OKC_API.set_message(
3374: p_app_name => G_APP_NAME,
3375: p_msg_name => G_INVALID_TAX_EXEMPT_LINE,
3376: p_token1 => 'TOKEN1',
3377: p_token1_value => l_exempt_certificate_number,
3378: p_token2 => 'TOKEN2',
3379: p_token2_value => l_cust_acct_name,
3380: p_token3 => 'TOKEN3',
3381: p_token3_value => l_number);
3382: x_return_status := OKC_API.G_RET_STS_ERROR;
3383: END IF;
3384: **/
3385:
3386: -- END GCHADHA --
3400: -- if found means date does not fall between the start date/end date of the exemption
3401: IF old_valid_exemption_csr%FOUND THEN
3402: -- Line LINE_NUM start date does not fall within the effective dates
3403: -- for Tax Exemption EXEMPT_NUM. The Tax Exemption will not be applied to this line.
3404: OKC_API.set_message(p_app_name => G_APP_NAME,
3405: p_msg_name => G_INVALID_TAX_EXEMPT_DATE,
3406: p_token1 => 'LINE_NUM',
3407: p_token1_value => l_number_tbl(i),
3408: p_token2 => 'EXEMPT_NUM',
3407: p_token1_value => l_number_tbl(i),
3408: p_token2 => 'EXEMPT_NUM',
3409: p_token2_value => l_tax_exemption_number
3410: );
3411: x_return_status := OKC_API.G_RET_STS_ERROR;
3412:
3413: ELSE
3414: OPEN trx_date_csr(l_cle_id_tbl(i));
3415: FETCH trx_date_csr INTO l_trx_date;
3421: IF trx_date_csr%found AND l_tax_exemption_id_tbl(i) IS NOT NULL THEN
3422: OPEN old_exemption_period_csr (l_line_start_date_tbl(i), l_tax_exemption_id_tbl(i), l_trx_date) ;
3423: FETCH old_exemption_period_csr INTO l_tax_exemption_number, l_start_date, l_end_date ;
3424: IF old_exemption_period_csr%FOUND THEN
3425: OKC_API.set_message(
3426: p_app_name => G_APP_NAME,
3427: p_msg_name => G_INVALID_TAX_EXEMPT,
3428: p_token1 => 'EXEMPTNUMBER',
3429: p_token1_value => l_tax_exemption_number,
3431: p_token2_value => l_number_tbl(i),
3432: p_token3 => 'DATE',
3433: p_token3_value => l_end_date);
3434: -- notify caller of an error
3435: x_return_status := OKC_API.G_RET_STS_ERROR;
3436: -- GCHADHA --
3437: -- IKON Enhancement --
3438: -- 5/6/2005 --
3439: ELSE
3443: IF l_tax_exempt_acct <> l_cust_acct_id_tbl(i) THEN
3444: OPEN Get_Customer_Name (l_cust_acct_id_tbl(i));
3445: FETCH Get_Customer_Name INTO l_cust_acct_name;
3446: CLOSE Get_Customer_Name;
3447: OKC_API.set_message(
3448: p_app_name => G_APP_NAME,
3449: p_msg_name => G_INVALID_TAX_EXEMPT_LINE,
3450: p_token1 => 'TOKEN1',
3451: p_token1_value => l_tax_exemption_number,
3452: p_token2 => 'TOKEN2',
3453: p_token2_value => l_cust_acct_name,
3454: p_token3 => 'TOKEN3',
3455: p_token3_value => l_number_tbl(i));
3456: x_return_status := OKC_API.G_RET_STS_ERROR;
3457: END IF;
3458: -- END GCHADHA --
3459: -- 5/6/2005 --
3460: -- IKON Enhancement --
3471: IF old_approved_exemption_csr%FOUND THEN
3472: -- Line LINE_NUM has an unapproved exemption. Billing this
3473: -- line will result in creation of an invoice with an
3474: -- unapproved tax exemption.
3475: OKC_API.set_message(p_app_name => G_APP_NAME,
3476: p_msg_name => G_UNAPPROVED_TAX_EXEMPT,
3477: p_token1 => 'LINE_NUM',
3478: p_token1_value => l_number_tbl(i)
3479: );
3476: p_msg_name => G_UNAPPROVED_TAX_EXEMPT,
3477: p_token1 => 'LINE_NUM',
3478: p_token1_value => l_number_tbl(i)
3479: );
3480: x_return_status := OKC_API.G_RET_STS_ERROR;
3481: END IF;
3482: CLOSE old_approved_exemption_csr;
3483:
3484: END IF;
3497: IF hdr_tax_exemp_rec.tax_exemption_id IS NOT NULL THEN
3498: OPEN old_approved_exemption_csr(hdr_tax_exemp_rec.tax_exemption_id);
3499: FETCH old_approved_exemption_csr INTO l_status;
3500: IF old_approved_exemption_csr%FOUND THEN
3501: OKC_API.set_message(p_app_name => G_APP_NAME,
3502: p_msg_name => G_UNAPPROVED_HDR_TAX_EXEMPT
3503: );
3504: x_return_status := OKC_API.G_RET_STS_ERROR;
3505: END IF;
3500: IF old_approved_exemption_csr%FOUND THEN
3501: OKC_API.set_message(p_app_name => G_APP_NAME,
3502: p_msg_name => G_UNAPPROVED_HDR_TAX_EXEMPT
3503: );
3504: x_return_status := OKC_API.G_RET_STS_ERROR;
3505: END IF;
3506: CLOSE old_approved_exemption_csr;
3507: ELSIF hdr_tax_exemp_rec.exempt_certificate_number IS NOT NULL THEN
3508: -- This contract has an unapproved exemption. Billing this
3602: END IF;
3603:
3604:
3605: IF l_valid_flag <> 'Y' THEN
3606: OKC_API.set_message(
3607: p_app_name => G_APP_NAME,
3608: p_msg_name => 'OKS_INVALID_TAX_EXEMPT_HEADER',
3609: p_token1 => 'EXEMPT_NUM',
3610: p_token1_value => hdr_tax_exemp_rec.exempt_certificate_number);
3608: p_msg_name => 'OKS_INVALID_TAX_EXEMPT_HEADER',
3609: p_token1 => 'EXEMPT_NUM',
3610: p_token1_value => hdr_tax_exemp_rec.exempt_certificate_number);
3611:
3612: x_return_status := OKC_API.G_RET_STS_ERROR;
3613:
3614: ELSE
3615: ZX_TCM_VALIDATE_EXEMPT_PKG.VALIDATE_TAX_EXEMPTIONS
3616: (p_tax_exempt_number => hdr_tax_exemp_rec.exempt_certificate_number,
3637: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_mod_name,'l_valid_flag: '|| l_valid_flag);
3638: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_mod_name,'l_legal_entity_id: '|| l_legal_entity_id);
3639: END IF;
3640: IF l_valid_flag <> 'Y' THEN
3641: OKC_API.set_message(
3642: p_app_name => G_APP_NAME,
3643: p_msg_name => 'OKS_PARTIAL_TAX_EXEMPT_HEADER',
3644: p_token1 => 'EXEMPT_NUM',
3645: p_token1_value => hdr_tax_exemp_rec.exempt_certificate_number);
3643: p_msg_name => 'OKS_PARTIAL_TAX_EXEMPT_HEADER',
3644: p_token1 => 'EXEMPT_NUM',
3645: p_token1_value => hdr_tax_exemp_rec.exempt_certificate_number);
3646:
3647: x_return_status := OKC_API.G_RET_STS_ERROR;
3648: END IF;
3649:
3650:
3651: END IF;
3655: END IF;
3656: END IF;
3657: CLOSE get_hdr_tax_exemp;
3658:
3659: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3660: OKC_API.set_message(
3661: p_app_name => G_APP_NAME,
3662: p_msg_name => 'OKS_QA_SUCCESS');
3663: END IF;
3656: END IF;
3657: CLOSE get_hdr_tax_exemp;
3658:
3659: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3660: OKC_API.set_message(
3661: p_app_name => G_APP_NAME,
3662: p_msg_name => 'OKS_QA_SUCCESS');
3663: END IF;
3664:
3671: -- no processing necessary; validation can continue with next column
3672: NULL;
3673: WHEN OTHERS THEN
3674: -- store SQL error message on message stack
3675: OKC_API.SET_MESSAGE(
3676: p_app_name => G_APP_NAME,
3677: p_msg_name => G_UNEXPECTED_ERROR,
3678: p_token1 => G_SQLCODE_TOKEN,
3679: p_token1_value => SQLCODE,
3679: p_token1_value => SQLCODE,
3680: p_token2 => G_SQLERRM_TOKEN,
3681: p_token2_value => SQLERRM);
3682: -- notify caller of an error as UNEXPETED error
3683: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3684: IF top_line_csr%ISOPEN THEN
3685: CLOSE top_line_csr;
3686: END IF;
3687:
3857:
3858: BEGIN
3859:
3860: -- initialize return status
3861: x_return_status := OKC_API.G_RET_STS_SUCCESS;
3862:
3863: OPEN check_cust_on_credit_hold;
3864: FETCH check_cust_on_credit_hold INTO v_customer_name, v_crd_hold_desc, v_crd_typ;
3865:
3866:
3867: IF check_cust_on_credit_hold%FOUND
3868: THEN
3869:
3870: OKC_API.set_message(
3871: p_app_name => G_APP_NAME,
3872: p_msg_name => G_CUSTOMER_ON_CREDIT_HOLD,
3873: p_token1 => 'CUSTOMER_NAME',
3874: p_token1_value => v_customer_name,
3875: p_token2 => 'HOLD_TYPE',
3876: p_token2_value => v_crd_hold_desc);
3877:
3878: -- notify caller of an error
3879: x_return_status := OKC_API.G_RET_STS_ERROR;
3880:
3881: END IF;
3882:
3883: CLOSE check_cust_on_credit_hold;
3881: END IF;
3882:
3883: CLOSE check_cust_on_credit_hold;
3884:
3885: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3886: OKC_API.set_message(
3887: p_app_name => G_APP_NAME,
3888: p_msg_name => 'OKS_QA_SUCCESS');
3889: END IF;
3882:
3883: CLOSE check_cust_on_credit_hold;
3884:
3885: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3886: OKC_API.set_message(
3887: p_app_name => G_APP_NAME,
3888: p_msg_name => 'OKS_QA_SUCCESS');
3889: END IF;
3890:
3893: -- no processing necessary; validation can continue with next column
3894: NULL;
3895: WHEN OTHERS THEN
3896: -- store SQL error message on message stack
3897: OKC_API.SET_MESSAGE(
3898: p_app_name => G_APP_NAME,
3899: p_msg_name => G_UNEXPECTED_ERROR,
3900: p_token1 => G_SQLCODE_TOKEN,
3901: p_token1_value => SQLCODE,
3901: p_token1_value => SQLCODE,
3902: p_token2 => G_SQLERRM_TOKEN,
3903: p_token2_value => SQLERRM);
3904: -- notify caller of an error as UNEXPETED error
3905: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3906:
3907: END check_cust_credit_hold;
3908:
3909: /*============================================================================+
4044: -- END GCHADHA --
4045: BEGIN
4046:
4047: -- initialize return status
4048: x_return_status := OKC_API.G_RET_STS_SUCCESS;
4049:
4050: -- Bug 4138244 --
4051: -- GCHADHA --
4052: OPEN get_billto_shipto(p_chr_id);
4057: OPEN check_address_header(l_bill_to_site_use_id, 'BILL_TO');
4058: FETCH check_address_header INTO l_bto_address_rec;
4059: IF check_address_header%FOUND THEN
4060: IF l_bto_address_rec.status <> 'A' THEN
4061: OKC_API.set_message(
4062: p_app_name => G_APP_NAME,
4063: p_msg_name => G_BTO_INVALID_HEAD
4064: );
4065:
4063: p_msg_name => G_BTO_INVALID_HEAD
4064: );
4065:
4066: -- notify caller of an error
4067: x_return_status := OKC_API.G_RET_STS_ERROR;
4068: ELSE
4069: OPEN check_site (l_bto_address_rec.cust_acct_site_id);
4070: FETCH check_site INTO l_status;
4071: IF check_site%FOUND THEN
4069: OPEN check_site (l_bto_address_rec.cust_acct_site_id);
4070: FETCH check_site INTO l_status;
4071: IF check_site%FOUND THEN
4072: IF l_status <> 'A' THEN
4073: OKC_API.set_message(
4074: p_app_name => G_APP_NAME,
4075: p_msg_name => G_BTO_INVALID_HEAD
4076: );
4077: -- notify caller of an error
4074: p_app_name => G_APP_NAME,
4075: p_msg_name => G_BTO_INVALID_HEAD
4076: );
4077: -- notify caller of an error
4078: x_return_status := OKC_API.G_RET_STS_ERROR;
4079: END IF;
4080: END IF;
4081: CLOSE check_site;
4082: END IF; -- If l_bto_address_rec.status <> 'A' THEN
4089: OPEN check_address_header(l_ship_to_site_use_id, 'SHIP_TO');
4090: FETCH check_address_header INTO l_sto_address_rec;
4091: IF check_address_header%FOUND THEN
4092: IF l_sto_address_rec.status <> 'A' THEN
4093: OKC_API.set_message(
4094: p_app_name => G_APP_NAME,
4095: p_msg_name => G_STO_INVALID_HEAD
4096: );
4097:
4095: p_msg_name => G_STO_INVALID_HEAD
4096: );
4097:
4098: -- notify caller of an error
4099: x_return_status := OKC_API.G_RET_STS_ERROR;
4100: ELSE
4101: OPEN check_site (l_sto_address_rec.cust_acct_site_id);
4102: FETCH check_site INTO l_status;
4103: IF check_site%FOUND THEN
4101: OPEN check_site (l_sto_address_rec.cust_acct_site_id);
4102: FETCH check_site INTO l_status;
4103: IF check_site%FOUND THEN
4104: IF l_status <> 'A' THEN
4105: OKC_API.set_message(
4106: p_app_name => G_APP_NAME,
4107: p_msg_name => G_STO_INVALID_HEAD
4108: );
4109: -- notify caller of an error
4106: p_app_name => G_APP_NAME,
4107: p_msg_name => G_STO_INVALID_HEAD
4108: );
4109: -- notify caller of an error
4110: x_return_status := OKC_API.G_RET_STS_ERROR;
4111: END IF;
4112: END IF;
4113: CLOSE check_site;
4114: END IF; -- If l_sto_address_rec.status <> 'A' THEN
4131: FETCH check_address INTO l_bto_address_rec;
4132:
4133: IF check_address%NOTFOUND THEN
4134:
4135: OKC_API.set_message(
4136: p_app_name => G_APP_NAME,
4137: p_msg_name => G_BTO_REQUIRED,
4138: p_token1 => 'LINE',
4139: p_token1_value => line_rec.line_number);
4138: p_token1 => 'LINE',
4139: p_token1_value => line_rec.line_number);
4140:
4141: -- notify caller of an error
4142: x_return_status := OKC_API.G_RET_STS_ERROR;
4143: ELSIF l_bto_address_rec.status <> 'A' THEN
4144: -- If bill to address is entered then
4145: -- check if it is of active status
4146:
4143: ELSIF l_bto_address_rec.status <> 'A' THEN
4144: -- If bill to address is entered then
4145: -- check if it is of active status
4146:
4147: OKC_API.set_message(
4148: p_app_name => G_APP_NAME,
4149: p_msg_name => G_BTO_INVALID,
4150: p_token1 => 'LINE',
4151: p_token1_value => line_rec.line_number);
4150: p_token1 => 'LINE',
4151: p_token1_value => line_rec.line_number);
4152:
4153: -- notify caller of an error
4154: x_return_status := OKC_API.G_RET_STS_ERROR;
4155: ELSE
4156: OPEN check_site (l_bto_address_rec.cust_acct_site_id);
4157: FETCH check_site INTO l_status;
4158: IF check_site%FOUND THEN
4156: OPEN check_site (l_bto_address_rec.cust_acct_site_id);
4157: FETCH check_site INTO l_status;
4158: IF check_site%FOUND THEN
4159: IF l_status <> 'A' THEN
4160: OKC_API.set_message(
4161: p_app_name => G_APP_NAME,
4162: p_msg_name => G_BTO_INVALID,
4163: p_token1 => 'LINE',
4164: p_token1_value => line_rec.line_number);
4162: p_msg_name => G_BTO_INVALID,
4163: p_token1 => 'LINE',
4164: p_token1_value => line_rec.line_number);
4165: -- notify caller of an error
4166: x_return_status := OKC_API.G_RET_STS_ERROR;
4167: END IF;
4168: END IF;
4169: CLOSE check_site;
4170: END IF; -- Elsif l_bto_address_rec.status <> 'A' THEN
4173: OPEN check_address(line_id, 'SHIP_TO');
4174: FETCH check_address INTO l_sto_address_rec;
4175: IF check_address%FOUND THEN
4176: IF l_sto_address_rec.status <> 'A' THEN
4177: OKC_API.set_message(
4178: p_app_name => G_APP_NAME,
4179: p_msg_name => G_STO_INVALID,
4180: p_token1 => 'LINE',
4181: p_token1_value => line_rec.line_number);
4180: p_token1 => 'LINE',
4181: p_token1_value => line_rec.line_number);
4182:
4183: -- notify caller of an error
4184: x_return_status := OKC_API.G_RET_STS_ERROR;
4185: ELSE
4186: OPEN check_site (l_sto_address_rec.cust_acct_site_id);
4187: FETCH check_site INTO l_status;
4188: IF check_site%FOUND THEN
4186: OPEN check_site (l_sto_address_rec.cust_acct_site_id);
4187: FETCH check_site INTO l_status;
4188: IF check_site%FOUND THEN
4189: IF l_status <> 'A' THEN
4190: OKC_API.set_message(
4191: p_app_name => G_APP_NAME,
4192: p_msg_name => G_STO_INVALID,
4193: p_token1 => 'LINE',
4194: p_token1_value => line_rec.line_number);
4192: p_msg_name => G_STO_INVALID,
4193: p_token1 => 'LINE',
4194: p_token1_value => line_rec.line_number);
4195: -- notify caller of an error
4196: x_return_status := OKC_API.G_RET_STS_ERROR;
4197: END IF;
4198: END IF;
4199: CLOSE check_site;
4200: END IF; -- If l_sto_address_rec.status <> 'A' THEN
4221:
4222: IF l_site_use_id_tbl(i) IS NULL AND l_use_code_tbl(i) = 'BILL_TO' THEN
4223: -- Check if Bill to/Ship to Address is entered
4224:
4225: OKC_API.set_message(
4226: p_app_name => G_APP_NAME,
4227: p_msg_name => G_BTO_REQUIRED,
4228: p_token1 => 'LINE',
4229: p_token1_value => l_line_number_tbl(i));
4228: p_token1 => 'LINE',
4229: p_token1_value => l_line_number_tbl(i));
4230:
4231: -- notify caller of an error
4232: x_return_status := OKC_API.G_RET_STS_ERROR;
4233:
4234: ELSIF l_site_use_status_tbl(i) <> 'A' THEN
4235:
4236: IF l_use_code_tbl(i) = 'BILL_TO' THEN
4234: ELSIF l_site_use_status_tbl(i) <> 'A' THEN
4235:
4236: IF l_use_code_tbl(i) = 'BILL_TO' THEN
4237: -- If bill to address is entered then check if it is of active status
4238: OKC_API.set_message(
4239: p_app_name => G_APP_NAME,
4240: p_msg_name => G_BTO_INVALID,
4241: p_token1 => 'LINE',
4242: p_token1_value => l_line_number_tbl(i));
4242: p_token1_value => l_line_number_tbl(i));
4243:
4244: ELSIF l_site_use_id_tbl(i) IS NOT NULL AND l_use_code_tbl(i) = 'SHIP_TO' THEN
4245: -- If ship to address is entered then check if it is of active status
4246: OKC_API.set_message(
4247: p_app_name => G_APP_NAME,
4248: p_msg_name => G_STO_INVALID,
4249: p_token1 => 'LINE',
4250: p_token1_value => l_line_number_tbl(i));
4250: p_token1_value => l_line_number_tbl(i));
4251: END IF;
4252:
4253: -- notify caller of an error
4254: x_return_status := OKC_API.G_RET_STS_ERROR;
4255: ELSE
4256: IF l_cust_acct_site_id_tbl(i) IS NOT NULL AND l_site_status_tbl(i) <> 'A' THEN
4257:
4258: IF l_use_code_tbl(i) = 'BILL_TO' THEN
4255: ELSE
4256: IF l_cust_acct_site_id_tbl(i) IS NOT NULL AND l_site_status_tbl(i) <> 'A' THEN
4257:
4258: IF l_use_code_tbl(i) = 'BILL_TO' THEN
4259: OKC_API.set_message(
4260: p_app_name => G_APP_NAME,
4261: p_msg_name => G_BTO_INVALID,
4262: p_token1 => 'LINE',
4263: p_token1_value => l_line_number_tbl(i));
4262: p_token1 => 'LINE',
4263: p_token1_value => l_line_number_tbl(i));
4264:
4265: ELSIF l_use_code_tbl(i) = 'SHIP_TO' THEN
4266: OKC_API.set_message(
4267: p_app_name => G_APP_NAME,
4268: p_msg_name => G_STO_INVALID,
4269: p_token1 => 'LINE',
4270: p_token1_value => l_line_number_tbl(i));
4271: END IF;
4272:
4273:
4274: -- notify caller of an error
4275: x_return_status := OKC_API.G_RET_STS_ERROR;
4276:
4277: END IF;
4278: END IF;
4279:
4282: END LOOP;
4283: CLOSE check_top_line_address;
4284:
4285:
4286: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
4287: THEN
4288: OKC_API.set_message(
4289: p_app_name => G_APP_NAME,
4290: p_msg_name => 'OKS_QA_SUCCESS');
4284:
4285:
4286: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
4287: THEN
4288: OKC_API.set_message(
4289: p_app_name => G_APP_NAME,
4290: p_msg_name => 'OKS_QA_SUCCESS');
4291: END IF;
4292:
4295: -- no processing necessary; validation can continue with next column
4296: NULL;
4297: WHEN OTHERS THEN
4298: -- store SQL error message on message stack
4299: OKC_API.SET_MESSAGE(
4300: p_app_name => G_APP_NAME,
4301: p_msg_name => G_UNEXPECTED_ERROR,
4302: p_token1 => G_SQLCODE_TOKEN,
4303: p_token1_value => SQLCODE,
4303: p_token1_value => SQLCODE,
4304: p_token2 => G_SQLERRM_TOKEN,
4305: p_token2_value => SQLERRM);
4306: -- notify caller of an error as UNEXPETED error
4307: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4308: -- verify that cursor was closed
4309: IF check_address_header%ISOPEN THEN
4310: CLOSE check_address_header;
4311: END IF;
4474:
4475: BEGIN
4476:
4477: -- initialize return status
4478: x_return_status := OKC_API.G_RET_STS_SUCCESS;
4479: -- Get Contract Lines
4480:
4481: OPEN l_org_csr;
4482: FETCH l_org_csr INTO l_org_id, l_organization_id;
4496: CLOSE l_status_csr;
4497:
4498: IF l_status IS NULL THEN
4499: /**
4500: OKC_API.set_message(
4501: p_app_name => G_APP_NAME,
4502: p_msg_name => G_REQUIRED_LINE_VALUE,
4503: p_token1 => G_COL_NAME_TOKEN,
4504: p_token1_value => 'Inventory item status not active',
4505: p_token2 => 'LINE_NAME',
4506: p_token2_value => get_line_name(l_cle_rec.id));
4507: **/
4508:
4509: OKC_API.set_message(
4510: p_app_name => G_APP_NAME,
4511: p_msg_name => G_INACTIVE_INVENTORY_ITEM,
4512: p_token1 => 'LINE_NAME',
4513: p_token1_value => get_line_name(l_cle_rec.id));
4512: p_token1 => 'LINE_NAME',
4513: p_token1_value => get_line_name(l_cle_rec.id));
4514:
4515: -- notify caller of an error
4516: x_return_status := OKC_API.G_RET_STS_ERROR;
4517:
4518: END IF; -- if l_status is NULL
4519: END IF; -- if (l_cle_rec.date_terminated is null )AND (l_cle_rec.sts_code = 'ENTERED')
4520:
4529: OR (l_chk_service_prod_rec.customer_order_enabled_flag = 'N' AND l_chk_service_prod_rec.internal_order_enabled_flag = 'N')
4530: OR l_chk_service_prod_rec.invoice_enabled_flag = 'N'))
4531: THEN
4532:
4533: OKC_API.SET_MESSAGE
4534: (
4535: p_app_name => G_APP_NAME,
4536: p_msg_name => 'OKS_INVALID_SERVICE_ITEM',
4537: p_token1 => 'ITEM',
4536: p_msg_name => 'OKS_INVALID_SERVICE_ITEM',
4537: p_token1 => 'ITEM',
4538: p_token1_value => get_line_name(l_cle_rec.id)
4539: );
4540: x_return_status := OKC_API.G_RET_STS_ERROR;
4541:
4542: END IF; --l_chk_service_prod_rec.service_item_flag = 'Y' and l_chk_service_prod_rec.vendor_warranty_flag = 'N'
4543:
4544: --for USAGE
4547: OR (l_chk_service_prod_rec.customer_order_enabled_flag = 'N' AND l_chk_service_prod_rec.internal_order_enabled_flag = 'N')
4548: OR l_chk_service_prod_rec.invoice_enabled_flag = 'N'))
4549: THEN
4550:
4551: OKC_API.SET_MESSAGE
4552: (
4553: p_app_name => G_APP_NAME,
4554: p_msg_name => 'OKS_INVALID_USAGE_ITEM',
4555: p_token1 => 'ITEM',
4554: p_msg_name => 'OKS_INVALID_USAGE_ITEM',
4555: p_token1 => 'ITEM',
4556: p_token1_value => get_line_name(l_cle_rec.id)
4557: );
4558: x_return_status := OKC_API.G_RET_STS_ERROR;
4559:
4560: END IF; --l_chk_service_prod_rec.usage_item_flag = 'Y'
4561:
4562: CLOSE l_chk_service_prod_csr;
4581: --for product
4582: IF (l_chk_service_prod_rec.serviceable_product_flag = 'N' AND l_cve_rec.lse_id IN (9, 25))
4583: THEN
4584:
4585: OKC_API.SET_MESSAGE
4586: (
4587: p_app_name => G_APP_NAME,
4588: p_msg_name => 'OKS_PRODUCT_INACTIVE',
4589: p_token1 => 'ITEM',
4588: p_msg_name => 'OKS_PRODUCT_INACTIVE',
4589: p_token1 => 'ITEM',
4590: p_token1_value => get_line_name(l_cve_rec.id)
4591: );
4592: x_return_status := OKC_API.G_RET_STS_ERROR;
4593:
4594:
4595: END IF; -- IF (l_chk_service_prod_rec.service_item_flag = 'Y'
4596: CLOSE l_chk_service_prod_csr;
4605: CLOSE l_covitm_csr;
4606:
4607: IF nvl(l_stat, 'X') <> 'A' THEN --Bug 5583158
4608: /**
4609: OKC_API.set_message(
4610: p_app_name => G_APP_NAME,
4611: p_msg_name => G_REQUIRED_LINE_VALUE,
4612: p_token1 => G_COL_NAME_TOKEN,
4613: p_token1_value => 'Covered item status not active',
4614: p_token2 => 'LINE_NAME',
4615: p_token2_value => get_line_name(l_cve_rec.id));
4616: **/
4617:
4618: OKC_API.set_message(
4619: p_app_name => G_APP_NAME,
4620: p_msg_name => G_INACTIVE_COVERED_ITEM,
4621: p_token1 => 'LINE_NAME',
4622: p_token1_value => get_line_name(l_cve_rec.id));
4621: p_token1 => 'LINE_NAME',
4622: p_token1_value => get_line_name(l_cve_rec.id));
4623:
4624: -- notify caller of an error
4625: x_return_status := OKC_API.G_RET_STS_ERROR;
4626:
4627: END IF; -- If l_stat <> 'A' Then
4628: END IF; -- If l_cve_rec.lse_id = 7 Then
4629:
4633: FETCH l_covparty_csr INTO l_stat;
4634: CLOSE l_covparty_csr;
4635: IF nvl(l_stat, 'X') <> 'A' THEN --Bug 5583158
4636: /**
4637: OKC_API.set_message(
4638: p_app_name => G_APP_NAME,
4639: p_msg_name => G_REQUIRED_LINE_VALUE,
4640: p_token1 => G_COL_NAME_TOKEN,
4641: p_token1_value => 'Covered Party status not active',
4642: p_token2 => 'LINE_NAME',
4643: p_token2_value => get_line_name(l_cve_rec.id));
4644: **/
4645:
4646: OKC_API.set_message(
4647: p_app_name => G_APP_NAME,
4648: p_msg_name => G_INACTIVE_COVERED_PARTY,
4649: p_token1 => 'LINE_NAME',
4650: p_token1_value => get_line_name(l_cve_rec.id));
4649: p_token1 => 'LINE_NAME',
4650: p_token1_value => get_line_name(l_cve_rec.id));
4651:
4652: -- notify caller of an error
4653: x_return_status := OKC_API.G_RET_STS_ERROR;
4654:
4655: END IF;
4656: END IF;
4657:
4668:
4669: IF nvl(l_sts_code, 'X') <> 'QA_HOLD' THEN
4670: IF nvl(l_stat, 'X') <> 'Y' THEN --Bug 5583158
4671: /**
4672: OKC_API.set_message(
4673: p_app_name => G_APP_NAME,
4674: p_msg_name => G_REQUIRED_LINE_VALUE,
4675: p_token1 => G_COL_NAME_TOKEN,
4676: p_token1_value => 'Covered Product status not active',
4677: p_token2 => 'LINE_NAME',
4678: p_token2_value => get_line_name(l_cve_rec.id));
4679: **/
4680:
4681: OKC_API.set_message(
4682: p_app_name => G_APP_NAME,
4683: p_msg_name => G_INACTIVE_COVERED_PRODUCT,
4684: p_token1 => 'LINE_NAME',
4685: p_token1_value => get_line_name(l_cve_rec.id));
4684: p_token1 => 'LINE_NAME',
4685: p_token1_value => get_line_name(l_cve_rec.id));
4686:
4687: -- notify caller of an error
4688: x_return_status := OKC_API.G_RET_STS_ERROR;
4689: END IF;
4690: ELSE
4691: IF nvl(l_stat, 'X') <> 'Y' AND --Bug5583158
4692: nvl(l_cve_rec.date_terminated, l_cve_rec.end_date) > nvl(l_end_date, SYSDATE) THEN
4690: ELSE
4691: IF nvl(l_stat, 'X') <> 'Y' AND --Bug5583158
4692: nvl(l_cve_rec.date_terminated, l_cve_rec.end_date) > nvl(l_end_date, SYSDATE) THEN
4693: /**
4694: OKC_API.set_message(
4695: p_app_name => G_APP_NAME,
4696: p_msg_name => G_REQUIRED_LINE_VALUE,
4697: p_token1 => G_COL_NAME_TOKEN,
4698: p_token1_value => 'Covered Product status not active',
4699: p_token2 => 'LINE_NAME',
4700: p_token2_value => get_line_name(l_cve_rec.id));
4701: **/
4702:
4703: OKC_API.set_message(
4704: p_app_name => G_APP_NAME,
4705: p_msg_name => G_INACTIVE_COVERED_PRODUCT,
4706: p_token1 => 'LINE_NAME',
4707: p_token1_value => get_line_name(l_cve_rec.id));
4706: p_token1 => 'LINE_NAME',
4707: p_token1_value => get_line_name(l_cve_rec.id));
4708:
4709: -- notify caller of an error
4710: x_return_status := OKC_API.G_RET_STS_ERROR;
4711: END IF;
4712: END IF;
4713: END IF;
4714:
4720: FETCH l_covsys_csr INTO l_stat;
4721: CLOSE l_covsys_csr;
4722: IF nvl(l_stat, 'X') <> 'A' THEN
4723: /**
4724: OKC_API.set_message(
4725: p_app_name => G_APP_NAME,
4726: p_msg_name => G_REQUIRED_LINE_VALUE,
4727: p_token1 => G_COL_NAME_TOKEN,
4728: p_token1_value => 'Covered System status not active',
4729: p_token2 => 'LINE_NAME',
4730: p_token2_value => get_line_name(l_cve_rec.id));
4731: **/
4732:
4733: OKC_API.set_message(
4734: p_app_name => G_APP_NAME,
4735: p_msg_name => G_INACTIVE_COVERED_SYSTEM,
4736: p_token1 => 'LINE_NAME',
4737: p_token1_value => get_line_name(l_cve_rec.id));
4736: p_token1 => 'LINE_NAME',
4737: p_token1_value => get_line_name(l_cve_rec.id));
4738:
4739: -- notify caller of an error
4740: x_return_status := OKC_API.G_RET_STS_ERROR;
4741:
4742: END IF;
4743: END IF;
4744:
4750: FETCH l_covsit_csr INTO l_stat;
4751: CLOSE l_covsit_csr;
4752: IF nvl(l_stat, 'X') <> 'A' THEN --Bug 5583158
4753: /**
4754: OKC_API.set_message(
4755: p_app_name => G_APP_NAME,
4756: p_msg_name => G_REQUIRED_LINE_VALUE,
4757: p_token1 => G_COL_NAME_TOKEN,
4758: p_token1_value => 'Covered Site status not active',
4759: p_token2 => 'LINE_NAME',
4760: p_token2_value => get_line_name(l_cle_rec.id));
4761: **/
4762:
4763: OKC_API.set_message(
4764: p_app_name => G_APP_NAME,
4765: p_msg_name => G_INACTIVE_COVERED_SITE,
4766: p_token1 => 'LINE_NAME',
4767: p_token1_value => get_line_name(l_cle_rec.id));
4766: p_token1 => 'LINE_NAME',
4767: p_token1_value => get_line_name(l_cle_rec.id));
4768:
4769: -- notify caller of an error
4770: x_return_status := OKC_API.G_RET_STS_ERROR;
4771: END IF;
4772: END IF;
4773: END IF;
4774: END LOOP;
4776: CLOSE l_cve_csr;
4777:
4778: END LOOP;
4779: CLOSE l_cle_csr;
4780: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
4781: OKC_API.set_message(
4782: p_app_name => G_APP_NAME,
4783: p_msg_name => 'OKS_QA_SUCCESS');
4784: END IF;
4777:
4778: END LOOP;
4779: CLOSE l_cle_csr;
4780: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
4781: OKC_API.set_message(
4782: p_app_name => G_APP_NAME,
4783: p_msg_name => 'OKS_QA_SUCCESS');
4784: END IF;
4785:
4788: -- no processing necessary; validation can continue with next column
4789: NULL;
4790: WHEN OTHERS THEN
4791: -- store SQL error message on message stack
4792: OKC_API.SET_MESSAGE(
4793: p_app_name => G_APP_NAME,
4794: p_msg_name => G_UNEXPECTED_ERROR,
4795: p_token1 => G_SQLCODE_TOKEN,
4796: p_token1_value => SQLCODE,
4796: p_token1_value => SQLCODE,
4797: p_token2 => G_SQLERRM_TOKEN,
4798: p_token2_value => SQLERRM);
4799: -- notify caller of an error as UNEXPETED error
4800: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4801: IF l_cve_csr%ISOPEN THEN
4802: CLOSE l_cve_csr;
4803: END IF;
4804:
4973: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_auth_result.auth_code: '|| l_auth_result.auth_code);
4974: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_auth_result.auth_id: '|| l_auth_result.auth_id);
4975: END IF;
4976:
4977: IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
4978: --show IBY error message on standard FND stack in QA results UI
4979: x_return_status := OKC_API.G_RET_STS_ERROR;
4980:
4981:
4975: END IF;
4976:
4977: IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
4978: --show IBY error message on standard FND stack in QA results UI
4979: x_return_status := OKC_API.G_RET_STS_ERROR;
4980:
4981:
4982: --also pick up IBY message in l_response and place onto standard FND message stack
4983: IF NVL(l_response.Result_Category,'x') <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
4993: --delete the 'AUTH_SUCCESS' message put on stack by IBY
4994: FND_MSG_PUB.delete_msg(x_msg_count);
4995:
4996: --put success message to display in QA result list for header
4997: OKC_API.set_message(
4998: p_app_name => G_APP_NAME,
4999: p_msg_name => 'OKS_QA_SUCCESS');
5000:
5001: COMMIT; --the authorization is always committed by IBY regardless so this commit is necessary to retain the record
5040: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Category: '|| l_response.Result_Category);
5041: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Message: '|| l_response.Result_Message);
5042: END IF;
5043:
5044: IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
5045: -- show IBY error message on standard FND stack in QA results UI
5046: x_return_status := OKC_API.G_RET_STS_ERROR;
5047:
5048:
5042: END IF;
5043:
5044: IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
5045: -- show IBY error message on standard FND stack in QA results UI
5046: x_return_status := OKC_API.G_RET_STS_ERROR;
5047:
5048:
5049: --also pick up IBY message in l_response and place onto standard FND message stack
5050: IF NVL(l_response.Result_Category,'x') <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
5060: --delete the 'AUTH_SUCCESS' message put on stack by IBY
5061: FND_MSG_PUB.delete_msg(x_msg_count);
5062:
5063: --put success message to display in QA result list for line
5064: OKC_API.set_message(
5065: p_app_name => G_APP_NAME,
5066: p_msg_name => 'OKS_QA_SUCCESS');
5067:
5068:
5084: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5085: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
5086: END IF;
5087:
5088: x_return_status := OKC_API.G_RET_STS_SUCCESS;
5089:
5090:
5091:
5092: -- If OKS: Credit Card Validation Level profile does not hold value 'Authorize Payment', skip check
5101: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Profile is turned off, no need to perform any check');
5102: END IF;
5103:
5104: -- skip the process, No Authorization required
5105: x_return_status := OKC_API.G_RET_STS_SUCCESS;
5106: RAISE G_SUCCESS_HALT_VALIDATION;
5107: END IF;
5108:
5109:
5195: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Message: '|| l_response.Result_Message);
5196: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_auth_result.auth_id: '|| l_auth_result.auth_id);
5197: END IF;
5198:
5199: IF x_return_status = OKC_API.G_RET_STS_SUCCESS AND l_response.Result_Code = IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN
5200: --authorization already exists
5201:
5202: --delete the 'SUCCESS' message put on stack by IBY
5203: FND_MSG_PUB.delete_msg(x_msg_count);
5206: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5207: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Authorization already exists so returning success for header');
5208: END IF;
5209:
5210: OKC_API.set_message(
5211: p_app_name => G_APP_NAME,
5212: p_msg_name => 'OKS_QA_SUCCESS');
5213:
5214:
5222:
5223: create_header_authorization;
5224:
5225: ELSIF NVL(l_response.Result_Category,'x') <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
5226: x_return_status := OKC_API.G_RET_STS_ERROR; --show IBY error message (should be in stack) in QA results UI
5227:
5228: --also pick up IBY message in l_response and place onto standard FND message stack
5229: IF l_response.Result_Category <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
5230: FND_MSG_PUB.add_exc_msg(
5325: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Message: '|| l_response.Result_Message);
5326: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_auth_result.auth_id: '|| l_auth_result.auth_id);
5327: END IF;
5328:
5329: IF x_return_status = OKC_API.G_RET_STS_SUCCESS AND l_response.Result_Code = IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN
5330: --authorization already exists
5331:
5332: --delete the 'SUCCESS' message put on stack by IBY
5333: FND_MSG_PUB.delete_msg(x_msg_count);
5336: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5337: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Authorization already exists so returning success for line');
5338: END IF;
5339:
5340: OKC_API.set_message(
5341: p_app_name => G_APP_NAME,
5342: p_msg_name => 'OKS_QA_SUCCESS');
5343:
5344:
5359:
5360: ELSIF NVL(l_response.Result_Category,'x') <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
5361:
5362: --show IBY error message (should be in stack) in QA results UI
5363: x_return_status := OKC_API.G_RET_STS_ERROR;
5364:
5365: --also pick up IBY message in l_response and place onto standard FND message stack
5366: IF l_response.Result_Category <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
5367: FND_MSG_PUB.add_exc_msg(
5410: CLOSE cust_account_csr;
5411: END IF;
5412:
5413: x_return_status := x_return_status;
5414: OKC_API.set_message(
5415: p_app_name => G_APP_NAME,
5416: p_msg_name => 'OKS_QA_SUCCESS');
5417:
5418: WHEN G_ERROR_HALT_VALIDATION THEN
5445: IF cust_account_csr%ISOPEN THEN
5446: CLOSE cust_account_csr;
5447: END IF;
5448:
5449: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5450: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN,'Check_Authorize_Payment:- '|| SQLERRM);
5451:
5452: END Check_Authorize_Payment;
5453:
5446: CLOSE cust_account_csr;
5447: END IF;
5448:
5449: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5450: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN,'Check_Authorize_Payment:- '|| SQLERRM);
5451:
5452: END Check_Authorize_Payment;
5453:
5454:
5657:
5658: BEGIN
5659:
5660:
5661: x_return_status := OKC_API.G_RET_STS_SUCCESS;
5662: --adding the following code for Bug#4069048
5663: -- get renewal status to see if this is called for Electronic renewal
5664: -- if so bypass the Billing Schedule validation
5665: l_renewal_status := get_renewal_status(p_chr_id);
5692: IF l_top_line_SLL = 0
5693: THEN
5694:
5695: -- store SQL error message on message stack
5696: OKC_API.SET_MESSAGE
5697: (
5698: p_app_name => G_APP_NAME,
5699: p_msg_name => 'OKS_TOPLINE_SLL_NOT_EXISTS',
5700: p_token1 => 'TOKEN1',
5700: p_token1 => 'TOKEN1',
5701: --p_token1_value => top_line_grp_rec.line_number
5702: p_token1_value => get_line_name(top_line_grp_rec.id)
5703: );
5704: x_return_status := OKC_API.G_RET_STS_ERROR;
5705: ELSE
5706:
5707: --l_SLL_end_date := top_line_grp_rec.start_date;
5708: --l_rules_ctr := 0;
5741: IF l_bs_rec = 0
5742: THEN
5743:
5744: -- store SQL error message on message stack
5745: OKC_API.SET_MESSAGE
5746: (
5747: p_app_name => G_APP_NAME,
5748: p_msg_name => 'OKS_LVL_ELEMENT_NOT_EXISTS',
5749: p_token1 => 'TOKEN1',
5749: p_token1 => 'TOKEN1',
5750: --p_token1_value => top_line_grp_rec.line_number
5751: p_token1_value => get_line_name(top_line_grp_rec.id)
5752: );
5753: x_return_status := OKC_API.G_RET_STS_ERROR;
5754:
5755: END IF; --end if of l_bs_rec = 0
5756:
5757:
5767: IF ((rules_rec.level_periods <> level_elements_amt_rec.lvl_count) AND l_ETP_flag <> 'T')
5768: THEN
5769:
5770: -- store SQL error message on message stack
5771: OKC_API.SET_MESSAGE
5772: (
5773: p_app_name => G_APP_NAME,
5774: p_msg_name => 'OKS_SL_LVL_PERIOD_MISMATCH',
5775: p_token1 => 'TOKEN1',
5778: p_token2_value => rules_rec.level_periods, --rule_information3,
5779: p_token3 => 'TOKEN3',
5780: p_token3_value => level_elements_amt_rec.lvl_count
5781: );
5782: x_return_status := OKC_API.G_RET_STS_ERROR;
5783:
5784: END IF;
5785: **/
5786:
5807: oks_extwar_util_pvt.round_currency_amt(NVL(l_lvl_total_amt, 0), l_currency) <>
5808: oks_extwar_util_pvt.round_currency_amt(NVL(top_line_grp_rec.price_negotiated, 0),
5809: l_currency) THEN
5810: */
5811: OKC_API.SET_MESSAGE
5812: (
5813: p_app_name => G_APP_NAME,
5814: p_msg_name => 'OKS_LVL_TOTAL_PRICE_MIS',
5815: p_token1 => 'TOKEN1',
5818: p_token2_value => top_line_grp_rec.price_negotiated,
5819: p_token3 => 'TOKEN3',
5820: p_token3_value => l_lvl_total_amt
5821: );
5822: x_return_status := OKC_API.G_RET_STS_ERROR;
5823: END IF;
5824:
5825:
5826: --Check for Product billing cycle end date equal to lines end date
5828: IF TRUNC(l_SLL_end_date) < TRUNC(top_line_grp_rec.end_date) OR
5829: TRUNC(l_SLL_start_date) <> TRUNC(top_line_grp_rec.start_date)
5830: THEN
5831:
5832: OKC_API.SET_MESSAGE
5833: (
5834: p_app_name => G_APP_NAME,
5835: p_msg_name => 'OKS_TOPLINE_SLL_PERIOD_INVALID',
5836: p_token1 => 'TOKEN1',
5835: p_msg_name => 'OKS_TOPLINE_SLL_PERIOD_INVALID',
5836: p_token1 => 'TOKEN1',
5837: p_token1_value => get_line_name(top_line_grp_rec.id)
5838: );
5839: x_return_status := OKC_API.G_RET_STS_ERROR;
5840: END IF;
5841:
5842: -- It's possible in Top level billing type that the SLL end date > top line end date
5843: IF l_ETP_flag <> 'T' AND TRUNC(l_SLL_end_date) > TRUNC(top_line_grp_rec.end_date)
5842: -- It's possible in Top level billing type that the SLL end date > top line end date
5843: IF l_ETP_flag <> 'T' AND TRUNC(l_SLL_end_date) > TRUNC(top_line_grp_rec.end_date)
5844: THEN
5845:
5846: OKC_API.SET_MESSAGE
5847: (
5848: p_app_name => G_APP_NAME,
5849: p_msg_name => 'OKS_TOPLINE_SLL_PERIOD_INVALID',
5850: p_token1 => 'TOKEN1',
5849: p_msg_name => 'OKS_TOPLINE_SLL_PERIOD_INVALID',
5850: p_token1 => 'TOKEN1',
5851: p_token1_value => get_line_name(top_line_grp_rec.id)
5852: );
5853: x_return_status := OKC_API.G_RET_STS_ERROR;
5854:
5855: END IF;
5856:
5857: END IF; --end if of l_top_line_SLL = 0
5869: OPEN check_lvl_element(top_line_grp_rec.id);
5870: FETCH check_lvl_element INTO l_amount;
5871: IF check_lvl_element%FOUND THEN
5872: -- The billing schedule amount for line number LINE_NO is negative.
5873: OKC_API.SET_MESSAGE
5874: (
5875: p_app_name => G_APP_NAME,
5876: p_msg_name => 'OKS_NEG_LVL_ELEM',
5877: p_token1 => 'LINE_NO',
5876: p_msg_name => 'OKS_NEG_LVL_ELEM',
5877: p_token1 => 'LINE_NO',
5878: p_token1_value => get_line_number(top_line_grp_rec.id)
5879: );
5880: x_return_status := OKC_API.G_RET_STS_ERROR;
5881: END IF;
5882: CLOSE check_lvl_element;
5883: END IF;
5884: -- End modification for bug # 4053552
5901: IF (oks_extwar_util_pvt.round_currency_amt(NVL(l_temp_total_billed, 0), l_currency) >
5902: oks_extwar_util_pvt.round_currency_amt(NVL(l_price_negotiated, 0), l_currency))
5903:
5904: THEN
5905: OKC_API.SET_MESSAGE
5906: (
5907: p_app_name => G_APP_NAME,
5908: p_msg_name => 'OKS_SUBSCR_BILLED_AMT_MISMATCH',
5909: p_token1 => 'TOKEN1',
5912: p_token2_value => nvl(l_line_price, 0),
5913: p_token3 => 'TOKEN3',
5914: p_token3_value => nvl(l_total_billed, 0)
5915: );
5916: x_return_status := OKC_API.G_RET_STS_ERROR;
5917: END IF; -- IF ((oks_extwar_util_pvt.round_currency_amt(NVL(l_lvl_total_billed_amt,0),l_currency) >
5918: END IF; -- nvl(l_total_billed, 0) <> 0
5919: l_temp_total_billed := 0;
5920: END IF;
5941: IF get_ETP_rec.BILLING_SCHEDULE_TYPE <> l_ETP_flag
5942: THEN
5943:
5944: -- store SQL error message on message stack
5945: OKC_API.SET_MESSAGE
5946: (
5947: p_app_name => G_APP_NAME,
5948: p_msg_name => 'OKS_BILLING_FLAG_MISMATCH'
5949: );
5946: (
5947: p_app_name => G_APP_NAME,
5948: p_msg_name => 'OKS_BILLING_FLAG_MISMATCH'
5949: );
5950: x_return_status := OKC_API.G_RET_STS_ERROR;
5951:
5952: END IF; --end if of get_ETP_rec
5953: END LOOP;
5954:
5958: OR TRUNC(top_line_grp_rec.end_date) <> TRUNC(line_grp_rec.end_date))
5959: THEN
5960:
5961: -- store SQL error message on message stack
5962: OKC_API.SET_MESSAGE
5963: (
5964: p_app_name => G_APP_NAME,
5965: p_msg_name => 'OKS_TOP_SUB_LINE_DATE_MISMATCH'
5966: );
5963: (
5964: p_app_name => G_APP_NAME,
5965: p_msg_name => 'OKS_TOP_SUB_LINE_DATE_MISMATCH'
5966: );
5967: x_return_status := OKC_API.G_RET_STS_ERROR;
5968:
5969: END IF; --IF l_ETP_flag = 'E' and top_line_grp_rec.start_date <> line_grp_rec.start_date
5970:
5971: IF l_sub_line_SLL = 0
5971: IF l_sub_line_SLL = 0
5972: THEN
5973:
5974: -- store SQL error message on message stack
5975: OKC_API.SET_MESSAGE
5976: (
5977: p_app_name => G_APP_NAME,
5978: p_msg_name => 'OKS_SUBLINE_SLL_NOT_EXISTS',
5979: p_token1 => 'TOKEN1',
5979: p_token1 => 'TOKEN1',
5980: --p_token1_value => top_line_grp_rec.line_number||'.'||line_grp_rec.line_number
5981: p_token1_value => get_line_name(line_grp_rec.id)
5982: );
5983: x_return_status := OKC_API.G_RET_STS_ERROR;
5984:
5985: ELSE
5986:
5987: OPEN sll_end_date_csr(line_grp_rec.id);
6017: WHERE cle_id = line_grp_rec.id; -- rul_id = rules_rec.id;
6018:
6019: IF l_bs_rec = 0 THEN
6020: -- store SQL error message on message stack
6021: OKC_API.SET_MESSAGE
6022: (
6023: p_app_name => G_APP_NAME,
6024: p_msg_name => 'OKS_LVL_ELEMENT_NOT_EXISTS',
6025: p_token1 => 'TOKEN1',
6025: p_token1 => 'TOKEN1',
6026: --p_token1_value => top_line_grp_rec.line_number||'.'||line_grp_rec.line_number
6027: p_token1_value => get_line_name(line_grp_rec.id)
6028: );
6029: x_return_status := OKC_API.G_RET_STS_ERROR;
6030:
6031: END IF; --end if of l_bs_rec = 0
6032:
6033: --level element validation
6041: IF ((rules_rec.level_periods <> level_elements_amt_rec.lvl_count) AND l_ETP_flag <> 'T')
6042: THEN
6043:
6044: -- store SQL error message on message stack
6045: OKC_API.SET_MESSAGE
6046: (
6047: p_app_name => G_APP_NAME,
6048: p_msg_name => 'OKS_SL_LVL_PERIOD_MISMATCH',
6049: p_token1 => 'TOKEN1',
6052: p_token2_value => rules_rec.level_periods, --rule_information3,
6053: p_token3 => 'TOKEN3',
6054: p_token3_value => level_elements_amt_rec.lvl_count
6055: );
6056: x_return_status := OKC_API.G_RET_STS_ERROR;
6057:
6058: END IF;
6059: **/
6060:
6075: AND line_grp_rec.lse_id <> 13)
6076: THEN
6077:
6078: -- store SQL error message on message stack
6079: OKC_API.SET_MESSAGE
6080: (
6081: p_app_name => G_APP_NAME,
6082: p_msg_name => 'OKS_SL_LVL_TOT_AMT_MISMATCH',
6083: p_token1 => 'TOKEN1',
6086: p_token2_value => line_grp_rec.price_negotiated,
6087: p_token3 => 'TOKEN3',
6088: p_token3_value => l_lvl_total_amt
6089: );
6090: x_return_status := OKC_API.G_RET_STS_ERROR;
6091:
6092: END IF;
6093:
6094: -- Added for bug # 4053552
6097: OPEN check_lvl_element(line_grp_rec.id);
6098: FETCH check_lvl_element INTO l_amount;
6099: IF check_lvl_element%FOUND THEN
6100: -- The billing schedule amount for line number LINE_NO is negative.
6101: OKC_API.SET_MESSAGE
6102: (
6103: p_app_name => G_APP_NAME,
6104: p_msg_name => 'OKS_NEG_LVL_ELEM',
6105: p_token1 => 'LINE_NO',
6104: p_msg_name => 'OKS_NEG_LVL_ELEM',
6105: p_token1 => 'LINE_NO',
6106: p_token1_value => get_line_number(line_grp_rec.id)
6107: );
6108: x_return_status := OKC_API.G_RET_STS_ERROR;
6109: END IF;
6110: CLOSE check_lvl_element;
6111: END IF;
6112: -- End modification for bug # 4053552
6138: IF (oks_extwar_util_pvt.round_currency_amt(NVL(l_temp_total_billed, 0), l_currency) >
6139: oks_extwar_util_pvt.round_currency_amt(NVL(l_price_negotiated, 0), l_currency))
6140:
6141: THEN
6142: OKC_API.SET_MESSAGE
6143: (
6144: p_app_name => G_APP_NAME,
6145: p_msg_name => 'OKS_SL_LVL_BILLED_AMT_MISMATCH',
6146: p_token1 => 'TOKEN1',
6149: p_token2_value => nvl(l_line_price, 0),
6150: p_token3 => 'TOKEN3',
6151: p_token3_value => nvl(l_total_billed, 0)
6152: );
6153: x_return_status := OKC_API.G_RET_STS_ERROR;
6154: END IF; -- IF ((oks_extwar_util_pvt.round_currency_amt(NVL(l_lvl_total_billed_amt,0),l_currency) >
6155: END IF; -- nvl(l_total_billed, 0) <> 0
6156: END IF; -- line_grp_rec.lse_id <> 13
6157: /*
6172: AND line_grp_rec.lse_id <> 13)
6173: THEN
6174:
6175: -- store SQL error message on message stack
6176: OKC_API.SET_MESSAGE
6177: (
6178: p_app_name => G_APP_NAME,
6179: p_msg_name => 'OKS_SL_LVL_BILLED_AMT_MISMATCH',
6180: p_token1 => 'TOKEN1',
6183: p_token2_value => line_grp_rec.price_negotiated,
6184: p_token3 => 'TOKEN3',
6185: p_token3_value => l_lvl_total_billed_amt
6186: );
6187: x_return_status := OKC_API.G_RET_STS_ERROR;
6188:
6189: END IF;
6190: End If; -- if there is a billed amount
6191:
6199: OR
6200: (l_ETP_flag = 'P' AND TRUNC(l_SLL_start_date) <> TRUNC(line_grp_rec.start_date) )
6201: THEN
6202:
6203: OKC_API.SET_MESSAGE
6204: (
6205: p_app_name => G_APP_NAME,
6206: p_msg_name => 'OKS_SUBLINE_SLL_PERIOD_INVALID',
6207: p_token1 => 'TOKEN1',
6206: p_msg_name => 'OKS_SUBLINE_SLL_PERIOD_INVALID',
6207: p_token1 => 'TOKEN1',
6208: p_token1_value => get_line_name(line_grp_rec.id)
6209: );
6210: x_return_status := OKC_API.G_RET_STS_ERROR;
6211: END IF;
6212:
6213:
6214: END IF; --end if of l_sub_line_SLL = 0
6221: oks_extwar_util_pvt.round_currency_amt(NVL(line_grp_rec.price_negotiated,0),l_currency))
6222:
6223: THEN
6224:
6225: OKC_API.SET_MESSAGE
6226: (
6227: p_app_name => G_APP_NAME,
6228: p_msg_name => 'OKS_LVL_TOTAL_PRICE_MIS',
6229: p_token1 => 'TOKEN1',
6232: p_token2_value => line_grp_rec.price_negotiated, -- each negotiated sub line
6233: p_token3 => 'TOKEN3',
6234: p_token3_value => l_lvl_total_amt -- sum of subline level elements
6235: );
6236: x_return_status := OKC_API.G_RET_STS_ERROR;
6237: END IF;
6238: */
6239:
6240: END LOOP; --end loop of line_grp_rec
6242:
6243:
6244: END LOOP; --end loop of top_line_grp_rec
6245: END IF;
6246: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
6247: THEN
6248: OKC_API.set_message(
6249: p_app_name => G_APP_NAME,
6250: p_msg_name => 'OKS_QA_SUCCESS');
6244: END LOOP; --end loop of top_line_grp_rec
6245: END IF;
6246: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
6247: THEN
6248: OKC_API.set_message(
6249: p_app_name => G_APP_NAME,
6250: p_msg_name => 'OKS_QA_SUCCESS');
6251: END IF; -- IF x_return_status = OKC_API.G_RET_STS_SUCCESS
6252:
6247: THEN
6248: OKC_API.set_message(
6249: p_app_name => G_APP_NAME,
6250: p_msg_name => 'OKS_QA_SUCCESS');
6251: END IF; -- IF x_return_status = OKC_API.G_RET_STS_SUCCESS
6252:
6253:
6254: EXCEPTION
6255: WHEN OTHERS THEN
6252:
6253:
6254: EXCEPTION
6255: WHEN OTHERS THEN
6256: x_return_status := OKC_API.G_RET_STS_ERROR;
6257: OKC_API.SET_MESSAGE
6258: (
6259: p_app_name => G_APP_NAME,
6260: p_msg_name => G_UNEXPECTED_ERROR,
6253:
6254: EXCEPTION
6255: WHEN OTHERS THEN
6256: x_return_status := OKC_API.G_RET_STS_ERROR;
6257: OKC_API.SET_MESSAGE
6258: (
6259: p_app_name => G_APP_NAME,
6260: p_msg_name => G_UNEXPECTED_ERROR,
6261: p_token1 => G_SQLCODE_TOKEN,
6361: l_lse_id NUMBER;
6362:
6363: BEGIN
6364:
6365: X_return_status := OKC_API.G_RET_STS_SUCCESS;
6366: /*** Get customer_id ****/
6367:
6368: l_service_id := NULL;
6369: l_sts_code := NULL;
6463: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_available_YN: '|| l_available_YN);
6464: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_org_id: '|| get_auth_org_rec.authoring_org_id);
6465: END IF;
6466: IF l_available_yn = 'N' THEN
6467: x_return_status := OKC_API.G_RET_STS_ERROR;
6468:
6469: OKC_API.set_message
6470: (
6471: p_app_name => 'OKS',
6465: END IF;
6466: IF l_available_yn = 'N' THEN
6467: x_return_status := OKC_API.G_RET_STS_ERROR;
6468:
6469: OKC_API.set_message
6470: (
6471: p_app_name => 'OKS',
6472: p_msg_name => 'OKS_PRODUCT_AVAILABILITY',
6473: p_token1 => 'TOKEN1',
6485: ----------------- Product line id loop -------------------------------
6486:
6487: END LOOP; /** End loop get service line id **/
6488: CLOSE l_csr_get_service_line_id;
6489: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
6490: OKC_API.set_message(
6491: p_app_name => G_APP_NAME,
6492: p_msg_name => 'OKS_QA_SUCCESS');
6493: END IF;
6486:
6487: END LOOP; /** End loop get service line id **/
6488: CLOSE l_csr_get_service_line_id;
6489: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
6490: OKC_API.set_message(
6491: p_app_name => G_APP_NAME,
6492: p_msg_name => 'OKS_QA_SUCCESS');
6493: END IF;
6494:
6499: -- no processing necessary; validation can continue with next column
6500: NULL;
6501: WHEN OTHERS THEN
6502: -- store SQL error message on message stack
6503: OKC_API.SET_MESSAGE(
6504: p_app_name => G_APP_NAME,
6505: p_msg_name => G_UNEXPECTED_ERROR,
6506: p_token1 => G_SQLCODE_TOKEN,
6507: p_token1_value => SQLCODE,
6507: p_token1_value => SQLCODE,
6508: p_token2 => G_SQLERRM_TOKEN,
6509: p_token2_value => SQLERRM);
6510: -- notify caller of an error as UNEXPETED error
6511: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6512: -- verify that cursor was closed
6513:
6514:
6515: END Check_product_availability;
6587: AND A.status = 'A'
6588: AND A.org_id = orgId;
6589:
6590: BEGIN
6591: x_return_status := OKC_API.G_RET_STS_SUCCESS;
6592: -- Gets the account name of the party owning the line
6593: OPEN get_cust_id(l_cust_acct_id);
6594: FETCH get_cust_id INTO l_acct_name, l_cust_party_id;
6595: CLOSE get_cust_id;
6626: END LOOP;
6627: -- Bug Fix end 4253417
6628: IF l_rel_cust_flag = 'N' THEN
6629: IF p_usage = 'BTO' THEN
6630: OKC_API.set_message(
6631: p_app_name => G_APP_NAME,
6632: p_msg_name => G_BTO_ACCT,
6633: p_token1 => 'ACCOUNT_NAME',
6634: p_token1_value => l_acct_name,
6634: p_token1_value => l_acct_name,
6635: p_token2 => 'LINE_NUMBER',
6636: p_token2_value => p_line_number);
6637: ELSE
6638: OKC_API.set_message(
6639: p_app_name => G_APP_NAME,
6640: p_msg_name => G_STO_ACCT,
6641: p_token1 => 'ACCOUNT_NAME',
6642: p_token1_value => l_acct_name,
6642: p_token1_value => l_acct_name,
6643: p_token2 => 'LINE_NUMBER',
6644: p_token2_value => p_line_number);
6645: END IF;
6646: x_return_status := OKC_API.G_RET_STS_ERROR;
6647: END IF;
6648:
6649: END IF; -- cust_exist_csr%NOTFOUND
6650: CLOSE cust_exist_csr;
6657: -- no processing necessary; validation can continue with next column
6658: NULL;
6659: WHEN OTHERS THEN
6660: -- store SQL error message on message stack
6661: OKC_API.SET_MESSAGE(
6662: p_app_name => G_APP_NAME,
6663: p_msg_name => G_UNEXPECTED_ERROR,
6664: p_token1 => G_SQLCODE_TOKEN,
6665: p_token1_value => SQLCODE,
6665: p_token1_value => SQLCODE,
6666: p_token2 => G_SQLERRM_TOKEN,
6667: p_token2_value => SQLERRM);
6668: -- notify caller of an error as UNEXPETED error
6669: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6670:
6671: END check_customer_avail_loop;
6672:
6673: /*============================================================================+
6854: l_cust_acct_id NUMBER;
6855: l_line_number VARCHAR2(150);
6856: l_status VARCHAR2(30);
6857: l_name VARCHAR2(360);
6858: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
6859: l_ship_to_cust_acct_id NUMBER;
6860: l_cust_not_found BOOLEAN;
6861:
6862:
6861:
6862:
6863:
6864: BEGIN
6865: x_return_status := OKC_API.G_RET_STS_SUCCESS;
6866:
6867: l_cust_not_found := TRUE;
6868: -- GCHADHA --
6869: -- BUG 4138244 ---
6883: CLOSE is_cust_hdr_active;
6884: l_cust_inactive := 1;
6885: -- Get the customer name
6886: IF get_cust_rec.rle_code = 'CUSTOMER' THEN
6887: OKC_API.set_message(
6888: p_app_name => G_APP_NAME,
6889: p_msg_name => G_CUST_INACTIVE, -- Customer is not acitive.
6890: p_token1 => 'NAME',
6891: p_token1_value => l_name
6889: p_msg_name => G_CUST_INACTIVE, -- Customer is not acitive.
6890: p_token1 => 'NAME',
6891: p_token1_value => l_name
6892: );
6893: x_return_status := OKC_API.G_RET_STS_ERROR;
6894: ELSIF get_cust_rec.rle_code = 'THIRD_PARTY' THEN
6895: OKC_API.set_message(
6896: p_app_name => G_APP_NAME,
6897: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
6891: p_token1_value => l_name
6892: );
6893: x_return_status := OKC_API.G_RET_STS_ERROR;
6894: ELSIF get_cust_rec.rle_code = 'THIRD_PARTY' THEN
6895: OKC_API.set_message(
6896: p_app_name => G_APP_NAME,
6897: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
6898: p_token1 => 'NAME',
6899: p_token1_value => l_name
6897: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
6898: p_token1 => 'NAME',
6899: p_token1_value => l_name
6900: );
6901: x_return_status := OKC_API.G_RET_STS_ERROR;
6902: ELSIF get_cust_rec.rle_code = 'SUBSCRIBER' THEN
6903: OKC_API.set_message(
6904: p_app_name => G_APP_NAME,
6905: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
6899: p_token1_value => l_name
6900: );
6901: x_return_status := OKC_API.G_RET_STS_ERROR;
6902: ELSIF get_cust_rec.rle_code = 'SUBSCRIBER' THEN
6903: OKC_API.set_message(
6904: p_app_name => G_APP_NAME,
6905: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
6906: p_token1 => 'NAME',
6907: p_token1_value => l_name
6905: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
6906: p_token1 => 'NAME',
6907: p_token1_value => l_name
6908: );
6909: x_return_status := OKC_API.G_RET_STS_ERROR;
6910: END IF;
6911:
6912: END IF;
6913: CLOSE Get_Status_Party_Main;
6949: OPEN Get_Status_Party (l_cust_acct_id, l_temp_party_id);
6950: FETCH Get_Status_Party INTO l_cust_acct_id, l_status, l_name;
6951: IF Get_Status_Party%FOUND THEN
6952: IF l_status <> 'A' AND l_bto_flag <> 1 THEN
6953: OKC_API.set_message(
6954: p_app_name => G_APP_NAME,
6955: p_msg_name => G_BILL_CUST_INACTIVE -- Customer is not acitive.
6956:
6957: );
6954: p_app_name => G_APP_NAME,
6955: p_msg_name => G_BILL_CUST_INACTIVE -- Customer is not acitive.
6956:
6957: );
6958: x_return_status := OKC_API.G_RET_STS_ERROR;
6959: END IF;
6960: END IF;
6961: CLOSE Get_Status_Party;
6962:
6990: OPEN Get_Status_Party (l_cust_acct_id, l_temp_party_id);
6991: FETCH Get_Status_Party INTO l_cust_acct_id, l_status, l_name;
6992: IF Get_Status_Party%FOUND THEN
6993: IF l_status <> 'A' AND l_sto_flag <> 2 THEN
6994: OKC_API.set_message(
6995: p_app_name => G_APP_NAME,
6996: p_msg_name => G_SHIP_CUST_INACTIVE -- Bill to Customer Account is not acitive.
6997: );
6998: x_return_status := OKC_API.G_RET_STS_ERROR;
6994: OKC_API.set_message(
6995: p_app_name => G_APP_NAME,
6996: p_msg_name => G_SHIP_CUST_INACTIVE -- Bill to Customer Account is not acitive.
6997: );
6998: x_return_status := OKC_API.G_RET_STS_ERROR;
6999: END IF;
7000: END IF;
7001: CLOSE Get_Status_Party;
7002:
7008: -- billto/shipto account is a related customer and, this customer is
7009: -- inactive.
7010: IF l_flag = 1 THEN
7011: IF l_bto_flag = 1 THEN -- IF THE RELATED CUSTOMER IS IN BILL TO
7012: OKC_API.set_message(
7013: p_app_name => G_APP_NAME,
7014: p_msg_name => G_BILL_CUST_INACTIVE -- Bill To Related Customer is not acitive.
7015: );
7016: x_return_status := OKC_API.G_RET_STS_ERROR;
7012: OKC_API.set_message(
7013: p_app_name => G_APP_NAME,
7014: p_msg_name => G_BILL_CUST_INACTIVE -- Bill To Related Customer is not acitive.
7015: );
7016: x_return_status := OKC_API.G_RET_STS_ERROR;
7017: END IF;
7018: IF l_sto_flag = 2 THEN -- SHIP TO
7019: OKC_API.set_message(
7020: p_app_name => G_APP_NAME,
7015: );
7016: x_return_status := OKC_API.G_RET_STS_ERROR;
7017: END IF;
7018: IF l_sto_flag = 2 THEN -- SHIP TO
7019: OKC_API.set_message(
7020: p_app_name => G_APP_NAME,
7021: p_msg_name => G_SHIP_CUST_INACTIVE -- Ship To Related Customer is not acitive.
7022: );
7023: x_return_status := OKC_API.G_RET_STS_ERROR;
7019: OKC_API.set_message(
7020: p_app_name => G_APP_NAME,
7021: p_msg_name => G_SHIP_CUST_INACTIVE -- Ship To Related Customer is not acitive.
7022: );
7023: x_return_status := OKC_API.G_RET_STS_ERROR;
7024: END IF;
7025: l_sto_flag := 0;
7026: l_bto_flag := 0;
7027: END IF;
7035: Fetch is_cust_hdr_active into l_cust_acct_id, l_status, l_name;
7036: If is_cust_hdr_active%FOUND Then
7037: If l_status <> 'A' Then
7038: If get_cust_rec.rle_code = 'CUSTOMER' Then
7039: OKC_API.set_message(
7040: p_app_name => G_APP_NAME,
7041: p_msg_name => G_CUST_INACTIVE, -- Customer is not acitive.
7042: p_token1 => 'NAME',
7043: p_token1_value => l_name
7041: p_msg_name => G_CUST_INACTIVE, -- Customer is not acitive.
7042: p_token1 => 'NAME',
7043: p_token1_value => l_name
7044: );
7045: x_return_status := OKC_API.G_RET_STS_ERROR;
7046: Elsif get_cust_rec.rle_code = 'THIRD_PARTY' Then
7047: OKC_API.set_message(
7048: p_app_name => G_APP_NAME,
7049: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
7043: p_token1_value => l_name
7044: );
7045: x_return_status := OKC_API.G_RET_STS_ERROR;
7046: Elsif get_cust_rec.rle_code = 'THIRD_PARTY' Then
7047: OKC_API.set_message(
7048: p_app_name => G_APP_NAME,
7049: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
7050: p_token1 => 'NAME',
7051: p_token1_value => l_name
7049: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
7050: p_token1 => 'NAME',
7051: p_token1_value => l_name
7052: );
7053: x_return_status := OKC_API.G_RET_STS_ERROR;
7054: Elsif get_cust_rec.rle_code = 'SUBSCRIBER' Then
7055: OKC_API.set_message(
7056: p_app_name => G_APP_NAME,
7057: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
7051: p_token1_value => l_name
7052: );
7053: x_return_status := OKC_API.G_RET_STS_ERROR;
7054: Elsif get_cust_rec.rle_code = 'SUBSCRIBER' Then
7055: OKC_API.set_message(
7056: p_app_name => G_APP_NAME,
7057: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
7058: p_token1 => 'NAME',
7059: p_token1_value => l_name
7057: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
7058: p_token1 => 'NAME',
7059: p_token1_value => l_name
7060: );
7061: x_return_status := OKC_API.G_RET_STS_ERROR;
7062: End If;
7063: End If;
7064: End If;
7065: Close is_cust_hdr_active;
7069: -- END BUG 4138244 --
7070:
7071:
7072: IF l_cust_not_found THEN
7073: OKC_API.set_message(
7074: p_app_name => G_APP_NAME,
7075: p_msg_name => G_CUST_MISSING
7076: );
7077: x_return_status := OKC_API.G_RET_STS_ERROR;
7073: OKC_API.set_message(
7074: p_app_name => G_APP_NAME,
7075: p_msg_name => G_CUST_MISSING
7076: );
7077: x_return_status := OKC_API.G_RET_STS_ERROR;
7078: END IF;
7079:
7080: -- Checks bill to and ship to account on each top line
7081: FOR get_cust_rec IN get_cust_acct_lines(p_chr_id) LOOP
7082: l_cust_acct_id := get_cust_rec.cust_acct_id;
7083: OPEN is_cust_active(l_cust_acct_id);
7084: FETCH is_cust_active INTO l_status;
7085: IF is_cust_active%NOTFOUND THEN
7086: OKC_API.set_message(
7087: p_app_name => G_APP_NAME,
7088: p_msg_name => G_CUST_NOT_ACTIVE, --Bill to customer account is inactive on line number ....
7089: p_token1 => 'LINE',
7090: p_token1_value => get_cust_rec.line_number
7088: p_msg_name => G_CUST_NOT_ACTIVE, --Bill to customer account is inactive on line number ....
7089: p_token1 => 'LINE',
7090: p_token1_value => get_cust_rec.line_number
7091: );
7092: x_return_status := OKC_API.G_RET_STS_ERROR;
7093: END IF;
7094: CLOSE is_cust_active;
7095: check_customer_avail_loop(l_return_status, l_cust_acct_id, p_chr_id, get_cust_rec.line_number, 'BTO');
7096: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7092: x_return_status := OKC_API.G_RET_STS_ERROR;
7093: END IF;
7094: CLOSE is_cust_active;
7095: check_customer_avail_loop(l_return_status, l_cust_acct_id, p_chr_id, get_cust_rec.line_number, 'BTO');
7096: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7097: x_return_status := l_return_status;
7098: END IF;
7099:
7100: --- Check ship to cust account
7103: IF get_ship_to_acct%FOUND THEN
7104: OPEN is_cust_active(l_ship_to_cust_acct_id);
7105: FETCH is_cust_active INTO l_status;
7106: IF is_cust_active%NOTFOUND THEN
7107: OKC_API.set_message(
7108: p_app_name => G_APP_NAME,
7109: p_msg_name => G_SHIP_CUST_NOT_ACTIVE, --Ship to customer account is inactive on line number ....
7110: p_token1 => 'LINE',
7111: p_token1_value => get_cust_rec.line_number
7109: p_msg_name => G_SHIP_CUST_NOT_ACTIVE, --Ship to customer account is inactive on line number ....
7110: p_token1 => 'LINE',
7111: p_token1_value => get_cust_rec.line_number
7112: );
7113: x_return_status := OKC_API.G_RET_STS_ERROR;
7114: END IF;
7115: CLOSE is_cust_active;
7116: check_customer_avail_loop(l_return_status, l_ship_to_cust_acct_id, p_chr_id, get_cust_rec.line_number, 'STO');
7117: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7113: x_return_status := OKC_API.G_RET_STS_ERROR;
7114: END IF;
7115: CLOSE is_cust_active;
7116: check_customer_avail_loop(l_return_status, l_ship_to_cust_acct_id, p_chr_id, get_cust_rec.line_number, 'STO');
7117: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7118: x_return_status := l_return_status;
7119: END IF;
7120: END IF;
7121: CLOSE get_ship_to_acct;
7123:
7124: END LOOP;
7125:
7126:
7127: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7128: OKC_API.set_message(
7129: p_app_name => G_APP_NAME,
7130: p_msg_name => 'OKS_QA_SUCCESS');
7131: END IF;
7124: END LOOP;
7125:
7126:
7127: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7128: OKC_API.set_message(
7129: p_app_name => G_APP_NAME,
7130: p_msg_name => 'OKS_QA_SUCCESS');
7131: END IF;
7132:
7135: -- no processing necessary; validation can continue with next column
7136: NULL;
7137: WHEN OTHERS THEN
7138: -- store SQL error message on message stack
7139: OKC_API.SET_MESSAGE(
7140: p_app_name => G_APP_NAME,
7141: p_msg_name => G_UNEXPECTED_ERROR,
7142: p_token1 => G_SQLCODE_TOKEN,
7143: p_token1_value => SQLCODE,
7143: p_token1_value => SQLCODE,
7144: p_token2 => G_SQLERRM_TOKEN,
7145: p_token2_value => SQLERRM);
7146: -- notify caller of an error as UNEXPETED error
7147: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7148: IF get_ship_to_acct%ISOPEN THEN
7149: CLOSE get_ship_to_acct;
7150: END IF;
7151:
7166: x_return_status OUT NOCOPY VARCHAR2,
7167: p_chr_id IN NUMBER
7168: )
7169: IS
7170: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7171:
7172: BEGIN
7173: -- initialize return status
7174: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7170: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7171:
7172: BEGIN
7173: -- initialize return status
7174: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7175:
7176: OKS_PM_PROGRAMS_PVT.check_pm_program_effectivity
7177: (x_return_status => l_return_status,
7178: p_chr_id => p_chr_id);
7177: (x_return_status => l_return_status,
7178: p_chr_id => p_chr_id);
7179:
7180:
7181: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7182: x_return_status := l_return_status;
7183: END IF;
7184:
7185: OKS_PM_PROGRAMS_PVT.check_pm_schedule
7185: OKS_PM_PROGRAMS_PVT.check_pm_schedule
7186: (x_return_status => l_return_status,
7187: p_chr_id => p_chr_id);
7188:
7189: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7190: x_return_status := l_return_status;
7191: END IF;
7192:
7193: OKS_PM_PROGRAMS_PVT.check_pm_new_activities
7193: OKS_PM_PROGRAMS_PVT.check_pm_new_activities
7194: (x_return_status => l_return_status,
7195: p_chr_id => p_chr_id);
7196:
7197: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7198: x_return_status := l_return_status;
7199: END IF;
7200:
7201: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7197: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7198: x_return_status := l_return_status;
7199: END IF;
7200:
7201: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7202: OKC_API.set_message(
7203: p_app_name => G_APP_NAME,
7204: p_msg_name => 'OKS_QA_SUCCESS');
7205: END IF;
7198: x_return_status := l_return_status;
7199: END IF;
7200:
7201: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7202: OKC_API.set_message(
7203: p_app_name => G_APP_NAME,
7204: p_msg_name => 'OKS_QA_SUCCESS');
7205: END IF;
7206:
7208: WHEN G_EXCEPTION_HALT_VALIDATION THEN
7209: NULL;
7210: WHEN OTHERS THEN
7211: -- store SQL error message on message stack
7212: OKC_API.SET_MESSAGE(
7213: p_app_name => G_APP_NAME,
7214: p_msg_name => G_UNEXPECTED_ERROR,
7215: p_token1 => G_SQLCODE_TOKEN,
7216: p_token1_value => SQLCODE,
7216: p_token1_value => SQLCODE,
7217: p_token2 => G_SQLERRM_TOKEN,
7218: p_token2_value => SQLERRM);
7219: -- notify caller of an error as UNEXPETED error
7220: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7221: END check_pm;
7222:
7223: /*============================================================================+
7224: | Procedure: Check_item_instance_valid
7276: l_valid_status VARCHAR2(30);
7277: l_valid_dates VARCHAR2(30);
7278:
7279: BEGIN
7280: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7281: -- Gets the sublines with covered products for p_chr_id
7282: FOR get_cp_rec IN get_cp_lines LOOP
7283: -- Gets the item instance
7284: OPEN get_item_inst(get_cp_rec.subline_id);
7286: IF get_item_inst%FOUND THEN
7287: OPEN get_subscr(l_inst_id);
7288: FETCH get_subscr INTO l_subscr_chr_id, l_subscr_cle_id;
7289: IF get_subscr%NOTFOUND THEN
7290: OKC_API.set_message(
7291: p_app_name => G_APP_NAME,
7292: p_msg_name => G_ITEM_INST_INVALID,
7293: p_token1 => 'LINE_NAME',
7294: p_token1_value => get_line_name(get_cp_rec.subline_id));
7291: p_app_name => G_APP_NAME,
7292: p_msg_name => G_ITEM_INST_INVALID,
7293: p_token1 => 'LINE_NAME',
7294: p_token1_value => get_line_name(get_cp_rec.subline_id));
7295: x_return_status := OKC_API.G_RET_STS_ERROR;
7296: END IF;
7297: CLOSE get_subscr;
7298: END IF; -- get_item_inst%FOUND
7299: CLOSE get_item_inst;
7298: END IF; -- get_item_inst%FOUND
7299: CLOSE get_item_inst;
7300: END LOOP;
7301:
7302: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7303: OKC_API.set_message(
7304: p_app_name => G_APP_NAME,
7305: p_msg_name => 'OKS_QA_SUCCESS');
7306: END IF;
7299: CLOSE get_item_inst;
7300: END LOOP;
7301:
7302: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7303: OKC_API.set_message(
7304: p_app_name => G_APP_NAME,
7305: p_msg_name => 'OKS_QA_SUCCESS');
7306: END IF;
7307:
7313: IF get_item_inst%ISOPEN THEN
7314: CLOSE get_item_inst;
7315: END IF;
7316: -- store SQL error message on message stack
7317: OKC_API.SET_MESSAGE(
7318: p_app_name => G_APP_NAME,
7319: p_msg_name => G_UNEXPECTED_ERROR,
7320: p_token1 => G_SQLCODE_TOKEN,
7321: p_token1_value => SQLCODE,
7321: p_token1_value => SQLCODE,
7322: p_token2 => G_SQLERRM_TOKEN,
7323: p_token2_value => SQLERRM);
7324: -- notify caller of an error as UNEXPETED error
7325: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7326:
7327: END Check_item_instance_valid;
7328:
7329: /*============================================================================+
7365: l_sh_id NUMBER;
7366: l_subscr_ele_id NUMBER;
7367:
7368: BEGIN
7369: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7370: FOR subscr_line_rec IN get_subscr_line LOOP
7371: OPEN is_tangible_item(subscr_line_rec.id);
7372: FETCH is_tangible_item INTO l_sh_id;
7373: IF is_tangible_item%FOUND THEN
7375: FETCH get_subscr_element INTO l_subscr_ele_id;
7376: --Bug 5702660. Added condition "subscr_line_rec.date_terminated is NULL"
7377: IF get_subscr_element%NOTFOUND and subscr_line_rec.date_terminated is NULL
7378: THEN
7379: OKC_API.set_message(
7380: p_app_name => G_APP_NAME,
7381: p_msg_name => G_SUBSCR_ELEM_MISS,
7382: p_token1 => 'LINE_NAME',
7383: p_token1_value => get_line_name(subscr_line_rec.id));
7380: p_app_name => G_APP_NAME,
7381: p_msg_name => G_SUBSCR_ELEM_MISS,
7382: p_token1 => 'LINE_NAME',
7383: p_token1_value => get_line_name(subscr_line_rec.id));
7384: x_return_status := OKC_API.G_RET_STS_ERROR;
7385: END IF;
7386: CLOSE get_subscr_element;
7387: END IF;
7388: CLOSE is_tangible_item;
7387: END IF;
7388: CLOSE is_tangible_item;
7389: END LOOP;
7390:
7391: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7392: OKC_API.set_message(
7393: p_app_name => G_APP_NAME,
7394: p_msg_name => 'OKS_QA_SUCCESS');
7395: END IF;
7388: CLOSE is_tangible_item;
7389: END LOOP;
7390:
7391: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7392: OKC_API.set_message(
7393: p_app_name => G_APP_NAME,
7394: p_msg_name => 'OKS_QA_SUCCESS');
7395: END IF;
7396:
7402: IF is_tangible_item%ISOPEN THEN
7403: CLOSE is_tangible_item;
7404: END IF;
7405: -- store SQL error message on message stack
7406: OKC_API.SET_MESSAGE(
7407: p_app_name => G_APP_NAME,
7408: p_msg_name => G_UNEXPECTED_ERROR,
7409: p_token1 => G_SQLCODE_TOKEN,
7410: p_token1_value => SQLCODE,
7409: p_token1 => G_SQLCODE_TOKEN,
7410: p_token1_value => SQLCODE,
7411: p_token2 => G_SQLERRM_TOKEN,
7412: p_token2_value => SQLERRM);
7413: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7414:
7415:
7416: END check_subscr_element_exist;
7417:
7465: l_ship_to_id NUMBER;
7466: l_location_id NUMBER;
7467:
7468: BEGIN
7469: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7470: -- Loops through each subscription line and checks if it's items are shipable.
7471: FOR subscr_line_rec IN get_subscr_lines LOOP
7472: OPEN get_shipable_flag(subscr_line_rec.id);
7473: FETCH get_shipable_flag INTO l_shipable;
7475: -- since the subscription item is shipable it has to have a ship to rule.
7476: OPEN get_ship_to_rule(subscr_line_rec.id);
7477: FETCH get_ship_to_rule INTO l_ship_to_id;
7478: IF get_ship_to_rule%NOTFOUND THEN
7479: OKC_API.set_message(
7480: p_app_name => G_APP_NAME,
7481: p_msg_name => G_SHIP_RULE_MISS,
7482: p_token1 => 'LINE_NAME',
7483: p_token1_value => get_line_name(subscr_line_rec.id));
7480: p_app_name => G_APP_NAME,
7481: p_msg_name => G_SHIP_RULE_MISS,
7482: p_token1 => 'LINE_NAME',
7483: p_token1_value => get_line_name(subscr_line_rec.id));
7484: x_return_status := OKC_API.G_RET_STS_ERROR;
7485: ELSE
7486: OPEN ship_to_address(l_ship_to_id, 'SHIP_TO');
7487: FETCH ship_to_address INTO l_location_id;
7488: IF ship_to_address%NOTFOUND THEN
7485: ELSE
7486: OPEN ship_to_address(l_ship_to_id, 'SHIP_TO');
7487: FETCH ship_to_address INTO l_location_id;
7488: IF ship_to_address%NOTFOUND THEN
7489: OKC_API.set_message(
7490: p_app_name => G_APP_NAME,
7491: p_msg_name => G_SHIP_ADDR_MISS,
7492: p_token1 => 'LINE_NAME',
7493: p_token1_value => get_line_name(subscr_line_rec.id));
7490: p_app_name => G_APP_NAME,
7491: p_msg_name => G_SHIP_ADDR_MISS,
7492: p_token1 => 'LINE_NAME',
7493: p_token1_value => get_line_name(subscr_line_rec.id));
7494: x_return_status := OKC_API.G_RET_STS_ERROR;
7495: END IF;
7496: CLOSE ship_to_address;
7497: END IF; -- get_ship_to_rule%NOTFOUND
7498: CLOSE get_ship_to_rule;
7499: END IF; -- get_shipable_flag%FOUND
7500: CLOSE get_shipable_flag;
7501: END LOOP;
7502:
7503: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7504: OKC_API.set_message(
7505: p_app_name => G_APP_NAME,
7506: p_msg_name => 'OKS_QA_SUCCESS');
7507: END IF;
7500: CLOSE get_shipable_flag;
7501: END LOOP;
7502:
7503: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7504: OKC_API.set_message(
7505: p_app_name => G_APP_NAME,
7506: p_msg_name => 'OKS_QA_SUCCESS');
7507: END IF;
7508:
7518: CLOSE ship_to_address;
7519: END IF;
7520:
7521: -- store SQL error message on message stack
7522: OKC_API.SET_MESSAGE(
7523: p_app_name => G_APP_NAME,
7524: p_msg_name => G_UNEXPECTED_ERROR,
7525: p_token1 => G_SQLCODE_TOKEN,
7526: p_token1_value => SQLCODE,
7525: p_token1 => G_SQLCODE_TOKEN,
7526: p_token1_value => SQLCODE,
7527: p_token2 => G_SQLERRM_TOKEN,
7528: p_token2_value => SQLERRM);
7529: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7530:
7531: END check_subscr_is_shipable;
7532:
7533: /*============================================================================+
7574: l_exact_item_inst Exact_Item_Inst%ROWTYPE;
7575:
7576:
7577: BEGIN
7578: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7579: -- This will loop through all the covered product.
7580: -- A contract can have several covered products
7581: FOR get_cp_rec IN Get_Covered_Prod(p_chr_id) LOOP
7582: -- Will get the item_instace
7592: OPEN Exact_Item_Inst(l_item_inst.object1_id1);
7593: FETCH Exact_Item_Inst INTO l_exact_item_inst;
7594: IF Exact_Item_Inst%NOTFOUND THEN
7595: -- G_ITEM_INST_MISS = Item instance ITEM_INST for line LINE_NAME is not found in installedbase.
7596: OKC_API.set_message(
7597: p_app_name => G_APP_NAME,
7598: p_msg_name => G_ITEM_INST_MISS,
7599: p_token1 => 'ITEM_INST',
7600: p_token1_value => l_item_inst.object1_id1,
7599: p_token1 => 'ITEM_INST',
7600: p_token1_value => l_item_inst.object1_id1,
7601: p_token2 => 'LINE_NAME',
7602: p_token2_value => get_line_name(get_cp_rec.id));
7603: x_return_status := OKC_API.G_RET_STS_ERROR;
7604: ELSE
7605: IF l_exact_item_inst.quantity <> l_item_inst.number_of_items
7606: AND l_exact_item_inst.unit_of_measure <> l_item_inst.uom_code THEN
7607: OKC_API.set_message(
7603: x_return_status := OKC_API.G_RET_STS_ERROR;
7604: ELSE
7605: IF l_exact_item_inst.quantity <> l_item_inst.number_of_items
7606: AND l_exact_item_inst.unit_of_measure <> l_item_inst.uom_code THEN
7607: OKC_API.set_message(
7608: p_app_name => G_APP_NAME,
7609: p_msg_name => G_QUANT_UOM_INVALID,
7610: p_token1 => 'LINE_NAME',
7611: p_token1_value => get_line_name(get_cp_rec.id),
7613: p_token2_value => l_exact_item_inst.quantity,
7614: p_token3 => 'UOM',
7615: p_token3_value => l_exact_item_inst.unit_of_measure);
7616:
7617: x_return_status := OKC_API.G_RET_STS_ERROR;
7618: ELSIF l_exact_item_inst.quantity <> l_item_inst.number_of_items THEN
7619: OKC_API.set_message(
7620: p_app_name => G_APP_NAME,
7621: p_msg_name => G_QUANT_INVALID,
7615: p_token3_value => l_exact_item_inst.unit_of_measure);
7616:
7617: x_return_status := OKC_API.G_RET_STS_ERROR;
7618: ELSIF l_exact_item_inst.quantity <> l_item_inst.number_of_items THEN
7619: OKC_API.set_message(
7620: p_app_name => G_APP_NAME,
7621: p_msg_name => G_QUANT_INVALID,
7622: p_token1 => 'LINE_NAME',
7623: p_token1_value => get_line_name(get_cp_rec.id),
7623: p_token1_value => get_line_name(get_cp_rec.id),
7624: p_token2 => 'QUANTITY',
7625: p_token2_value => l_exact_item_inst.quantity);
7626:
7627: x_return_status := OKC_API.G_RET_STS_ERROR;
7628: ELSIF l_exact_item_inst.unit_of_measure <> l_item_inst.uom_code THEN
7629: -- G_UOM_INVALID: Covered product unit of measure on line LINE_NAME does not match the value UOM stored in installedbase.
7630: OKC_API.set_message(
7631: p_app_name => G_APP_NAME,
7626:
7627: x_return_status := OKC_API.G_RET_STS_ERROR;
7628: ELSIF l_exact_item_inst.unit_of_measure <> l_item_inst.uom_code THEN
7629: -- G_UOM_INVALID: Covered product unit of measure on line LINE_NAME does not match the value UOM stored in installedbase.
7630: OKC_API.set_message(
7631: p_app_name => G_APP_NAME,
7632: p_msg_name => G_UOM_INVALID,
7633: p_token1 => 'LINE_NAME',
7634: p_token1_value => get_line_name(get_cp_rec.id),
7634: p_token1_value => get_line_name(get_cp_rec.id),
7635: p_token2 => 'UOM',
7636: p_token2_value => l_exact_item_inst.unit_of_measure);
7637:
7638: x_return_status := OKC_API.G_RET_STS_ERROR;
7639: END IF;
7640: END IF;
7641: CLOSE Exact_Item_Inst;
7642: END IF;
7643: CLOSE is_subscr_item;
7644:
7645: END LOOP;
7646:
7647: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
7648: THEN
7649: OKC_API.set_message(
7650: p_app_name => G_APP_NAME,
7651: p_msg_name => 'OKS_QA_SUCCESS');
7645: END LOOP;
7646:
7647: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
7648: THEN
7649: OKC_API.set_message(
7650: p_app_name => G_APP_NAME,
7651: p_msg_name => 'OKS_QA_SUCCESS');
7652: END IF;
7653:
7653:
7654: EXCEPTION
7655: WHEN OTHERS THEN
7656: -- store SQL error message on message stack
7657: OKC_API.SET_MESSAGE(
7658: p_app_name => G_APP_NAME,
7659: p_msg_name => G_UNEXPECTED_ERROR,
7660: p_token1 => G_SQLCODE_TOKEN,
7661: p_token1_value => SQLCODE,
7660: p_token1 => G_SQLCODE_TOKEN,
7661: p_token1_value => SQLCODE,
7662: p_token2 => G_SQLERRM_TOKEN,
7663: p_token2_value => SQLERRM);
7664: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7665:
7666: IF Exact_Item_Inst%ISOPEN THEN
7667: CLOSE Exact_Item_Inst;
7668: END IF;
7683: +============================================================================*/
7684: PROCEDURE check_required_PM(p_chr_id IN NUMBER,
7685: x_return_status OUT NOCOPY VARCHAR2)
7686: IS
7687: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7688: BEGIN
7689: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7690: OKS_PM_PROGRAMS_PVT.CHECK_PM_REQUIRED_VALUES(x_return_status => l_return_status,
7691: p_chr_id => p_chr_id);
7685: x_return_status OUT NOCOPY VARCHAR2)
7686: IS
7687: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7688: BEGIN
7689: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7690: OKS_PM_PROGRAMS_PVT.CHECK_PM_REQUIRED_VALUES(x_return_status => l_return_status,
7691: p_chr_id => p_chr_id);
7692:
7693: x_return_status := l_return_status;
7692:
7693: x_return_status := l_return_status;
7694:
7695:
7696: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7697: OKC_API.set_message(
7698: p_app_name => G_APP_NAME,
7699: p_msg_name => 'OKS_QA_SUCCESS');
7700: END IF;
7693: x_return_status := l_return_status;
7694:
7695:
7696: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7697: OKC_API.set_message(
7698: p_app_name => G_APP_NAME,
7699: p_msg_name => 'OKS_QA_SUCCESS');
7700: END IF;
7701:
7701:
7702: EXCEPTION
7703: WHEN OTHERS THEN
7704: -- store SQL error message on message stack
7705: OKC_API.SET_MESSAGE(
7706: p_app_name => G_APP_NAME,
7707: p_msg_name => G_UNEXPECTED_ERROR,
7708: p_token1 => G_SQLCODE_TOKEN,
7709: p_token1_value => SQLCODE,
7709: p_token1_value => SQLCODE,
7710: p_token2 => G_SQLERRM_TOKEN,
7711: p_token2_value => SQLERRM);
7712:
7713: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7714:
7715: END check_required_PM;
7716:
7717: /*============================================================================+
7765: --and prorate is not null; -- prorate is not mandatory
7766:
7767: l_old_chr_id NUMBER;
7768: l_id NUMBER;
7769: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7770:
7771: BEGIN
7772: l_return_status := OKC_API.G_RET_STS_SUCCESS;
7773:
7768: l_id NUMBER;
7769: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7770:
7771: BEGIN
7772: l_return_status := OKC_API.G_RET_STS_SUCCESS;
7773:
7774: OPEN get_old_chr_id(p_chr_id);
7775: FETCH get_old_chr_id INTO l_old_chr_id;
7776: CLOSE get_old_chr_id;
7781: LOOP
7782: OPEN check_lock(p_chr_id, get_new_id_rec.id);
7783: FETCH check_lock INTO l_id;
7784: IF check_lock%NOTFOUND THEN
7785: OKC_API.set_message(
7786: p_app_name => G_APP_NAME,
7787: p_msg_name => G_MISS_PRICE_LOCK,
7788: p_token1 => 'NEW_LINE',
7789: p_token1_value => get_line_number(get_new_id_rec.id),
7791: p_token2_value => get_contract_name(l_old_chr_id),
7792: p_token3 => 'OLD_LINE',
7793: p_token3_value => get_line_number(get_old_id_rec.cle_id)
7794: );
7795: l_return_status := OKC_API.G_RET_STS_ERROR;
7796: END IF;
7797: CLOSE check_lock;
7798: END LOOP;
7799:
7799:
7800: END LOOP;
7801: x_return_status := l_return_status;
7802:
7803: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7804: OKC_API.set_message(
7805: p_app_name => G_APP_NAME,
7806: p_msg_name => 'OKS_QA_SUCCESS');
7807: END IF;
7800: END LOOP;
7801: x_return_status := l_return_status;
7802:
7803: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7804: OKC_API.set_message(
7805: p_app_name => G_APP_NAME,
7806: p_msg_name => 'OKS_QA_SUCCESS');
7807: END IF;
7808:
7808:
7809: EXCEPTION
7810: WHEN OTHERS THEN
7811: -- store SQL error message on message stack
7812: OKC_API.SET_MESSAGE(
7813: p_app_name => G_APP_NAME,
7814: p_msg_name => G_UNEXPECTED_ERROR,
7815: p_token1 => G_SQLCODE_TOKEN,
7816: p_token1_value => SQLCODE,
7816: p_token1_value => SQLCODE,
7817: p_token2 => G_SQLERRM_TOKEN,
7818: p_token2_value => SQLERRM);
7819:
7820: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7821:
7822:
7823: END check_pirce_lock;
7824:
7859:
7860:
7861: BEGIN
7862:
7863: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7864:
7865: OPEN cur_renewal_source_lines (p_chr_id);
7866: FETCH cur_renewal_source_lines INTO l_dummy_data;
7867:
7866: FETCH cur_renewal_source_lines INTO l_dummy_data;
7867:
7868: IF cur_renewal_source_lines%FOUND THEN
7869:
7870: OKC_API.set_message(
7871: p_app_name => G_APP_NAME,
7872: p_msg_name => 'OKS_CHECK_REN_SOURCE_LINES'
7873: );
7874: -- notify caller of an error
7871: p_app_name => G_APP_NAME,
7872: p_msg_name => 'OKS_CHECK_REN_SOURCE_LINES'
7873: );
7874: -- notify caller of an error
7875: x_return_status := OKC_API.G_RET_STS_ERROR;
7876:
7877: END IF;
7878:
7879: CLOSE cur_renewal_source_lines;
7877: END IF;
7878:
7879: CLOSE cur_renewal_source_lines;
7880:
7881: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7882: OKC_API.set_message(
7883: p_app_name => G_APP_NAME,
7884: p_msg_name => 'OKS_QA_SUCCESS');
7885: END IF;
7878:
7879: CLOSE cur_renewal_source_lines;
7880:
7881: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7882: OKC_API.set_message(
7883: p_app_name => G_APP_NAME,
7884: p_msg_name => 'OKS_QA_SUCCESS');
7885: END IF;
7886:
7886:
7887: EXCEPTION
7888: WHEN OTHERS THEN
7889: -- store SQL error message on message stack
7890: OKC_API.SET_MESSAGE(
7891: p_app_name => G_APP_NAME,
7892: p_msg_name => G_UNEXPECTED_ERROR,
7893: p_token1 => G_SQLCODE_TOKEN,
7894: p_token1_value => SQLCODE,
7894: p_token1_value => SQLCODE,
7895: p_token2 => G_SQLERRM_TOKEN,
7896: p_token2_value => SQLERRM);
7897:
7898: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7899:
7900: END Check_Ren_Source_Lines;
7901:
7902:
7995:
7996:
7997: BEGIN
7998:
7999: x_return_status := OKC_API.G_RET_STS_SUCCESS;
8000:
8001: OPEN cur_is_K_renewed;
8002: FETCH cur_is_K_renewed INTO l_target_contract_id;
8003:
8019: OPEN cur_target_line_number(l_subject_cle_id);
8020: FETCH cur_target_line_number INTO l_target_line_number;
8021: CLOSE cur_target_line_number;
8022:
8023: OKC_API.SET_MESSAGE
8024: (
8025: p_app_name => G_APP_NAME,
8026: p_msg_name => 'OKS_CHECK_REN_TARGET_LINES',
8027: p_token1 => 'CURRENT_LINE_NUM',
8031: p_token3 => 'TARGET_CONTRACT_NUM',
8032: p_token3_value => l_target_contract_number
8033: );
8034:
8035: x_return_status := OKC_API.G_RET_STS_ERROR;
8036:
8037: END IF; --cur_is_topline_renewed%FOUND
8038: CLOSE cur_is_topline_renewed;
8039:
8063: FETCH cur_target_subline_number INTO l_target_subline_number;
8064: CLOSE cur_target_subline_number;
8065:
8066:
8067: OKC_API.SET_MESSAGE
8068: (
8069: p_app_name => G_APP_NAME,
8070: p_msg_name => 'OKS_CHECK_REN_TARGET_LINES',
8071: p_token1 => 'CURRENT_LINE_NUM',
8075: p_token3 => 'TARGET_CONTRACT_NUM',
8076: p_token3_value => l_target_contract_number
8077: );
8078:
8079: x_return_status := OKC_API.G_RET_STS_ERROR;
8080:
8081: END IF;
8082: CLOSE cur_is_subline_renewed;
8083:
8091:
8092: END IF; --cur_is_K_renewed %FOUND
8093: CLOSE cur_is_K_renewed;
8094:
8095: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
8096: OKC_API.set_message(
8097: p_app_name => G_APP_NAME,
8098: p_msg_name => 'OKS_QA_SUCCESS');
8099:
8092: END IF; --cur_is_K_renewed %FOUND
8093: CLOSE cur_is_K_renewed;
8094:
8095: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
8096: OKC_API.set_message(
8097: p_app_name => G_APP_NAME,
8098: p_msg_name => 'OKS_QA_SUCCESS');
8099:
8100: END IF;
8101:
8102: EXCEPTION
8103: WHEN OTHERS THEN
8104: -- store SQL error message on message stack
8105: OKC_API.SET_MESSAGE(
8106: p_app_name => G_APP_NAME,
8107: p_msg_name => G_UNEXPECTED_ERROR,
8108: p_token1 => G_SQLCODE_TOKEN,
8109: p_token1_value => SQLCODE,
8109: p_token1_value => SQLCODE,
8110: p_token2 => G_SQLERRM_TOKEN,
8111: p_token2_value => SQLERRM);
8112:
8113: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
8114:
8115: END Check_Ren_Target_Lines;
8116:
8117:
8150:
8151:
8152: BEGIN
8153:
8154: x_return_status := OKC_API.G_RET_STS_SUCCESS;
8155:
8156:
8157: SELECT concurrent_program_id, p.application_id
8158: INTO program_id, prog_appl_id
8168: CLOSE c_reprice_req;
8169:
8170: IF req_id is not null THEN
8171:
8172: OKC_API.set_message(
8173: p_app_name => G_APP_NAME,
8174: p_msg_name => 'OKS_HEADER_REPRICE_CHECK_FAIL',
8175: p_token1 => 'REQUEST_ID',
8176: p_token1_value => req_id
8175: p_token1 => 'REQUEST_ID',
8176: p_token1_value => req_id
8177: );
8178:
8179: x_return_status := OKC_API.G_RET_STS_ERROR;
8180:
8181: ELSE
8182:
8183: OKC_API.set_message(
8179: x_return_status := OKC_API.G_RET_STS_ERROR;
8180:
8181: ELSE
8182:
8183: OKC_API.set_message(
8184: p_app_name => G_APP_NAME,
8185: p_msg_name => 'OKS_QA_SUCCESS');
8186:
8187: END IF;
8190: EXCEPTION
8191: WHEN OTHERS THEN
8192:
8193: -- store SQL error message on message stack
8194: OKC_API.SET_MESSAGE(
8195: p_app_name => G_APP_NAME,
8196: p_msg_name => G_UNEXPECTED_ERROR,
8197: p_token1 => G_SQLCODE_TOKEN,
8198: p_token1_value => SQLCODE,
8198: p_token1_value => SQLCODE,
8199: p_token2 => G_SQLERRM_TOKEN,
8200: p_token2_value => SQLERRM);
8201:
8202: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
8203:
8204:
8205: END CHECK_REPRICE_REQUEST;
8206:
8234:
8235: BEGIN
8236:
8237: -- initialize return status
8238: x_return_status := OKC_API.G_RET_STS_SUCCESS;
8239:
8240: for check_covd_qty_rec in check_covd_qty(p_chr_id)
8241: LOOP
8242:
8239:
8240: for check_covd_qty_rec in check_covd_qty(p_chr_id)
8241: LOOP
8242:
8243: OKC_API.set_message
8244: (
8245: p_app_name => 'OKS',
8246: p_msg_name => 'OKS_INVALID_QTY_VALUE',
8247: p_token1 => 'LINE_NUMBER',
8248: p_token1_value =>
8249: check_covd_qty_rec.line_number||'.'||check_covd_qty_rec.sub_line_number);
8250:
8251: -- notify caller of an error
8252: x_return_status := OKC_API.G_RET_STS_ERROR;
8253: end loop;
8254:
8255: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
8256: OKC_API.set_message(
8251: -- notify caller of an error
8252: x_return_status := OKC_API.G_RET_STS_ERROR;
8253: end loop;
8254:
8255: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
8256: OKC_API.set_message(
8257: p_app_name => G_APP_NAME,
8258: p_msg_name => 'OKS_QA_SUCCESS');
8259: END IF;
8252: x_return_status := OKC_API.G_RET_STS_ERROR;
8253: end loop;
8254:
8255: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
8256: OKC_API.set_message(
8257: p_app_name => G_APP_NAME,
8258: p_msg_name => 'OKS_QA_SUCCESS');
8259: END IF;
8260:
8263: -- no processing necessary; validation can continue with next column
8264: NULL;
8265: WHEN OTHERS THEN
8266: -- store SQL error message on message stack
8267: OKC_API.SET_MESSAGE(
8268: p_app_name => G_APP_NAME,
8269: p_msg_name => G_UNEXPECTED_ERROR,
8270: p_token1 => G_SQLCODE_TOKEN,
8271: p_token1_value => SQLCODE,
8271: p_token1_value => SQLCODE,
8272: p_token2 => G_SQLERRM_TOKEN,
8273: p_token2_value => SQLERRM);
8274: -- notify caller of an error as UNEXPETED error
8275: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
8276:
8277: END check_qty;
8278:
8279: /*11687112 -Cascade ER*/
8312:
8313:
8314: BEGIN
8315:
8316: x_return_status := OKC_API.G_RET_STS_SUCCESS;
8317:
8318:
8319: SELECT concurrent_program_id, p.application_id
8320: INTO program_id, prog_appl_id
8330: CLOSE c_cascade_req;
8331:
8332: IF req_id is not null THEN
8333:
8334: OKC_API.set_message(
8335: p_app_name => G_APP_NAME,
8336: p_msg_name => 'OKS_H_L_CASCADE_CHECK_FAIL',
8337: p_token1 => 'REQUEST_ID',
8338: p_token1_value => req_id
8337: p_token1 => 'REQUEST_ID',
8338: p_token1_value => req_id
8339: );
8340:
8341: x_return_status := OKC_API.G_RET_STS_ERROR;
8342:
8343: ELSE
8344:
8345: OKC_API.set_message(
8341: x_return_status := OKC_API.G_RET_STS_ERROR;
8342:
8343: ELSE
8344:
8345: OKC_API.set_message(
8346: p_app_name => G_APP_NAME,
8347: p_msg_name => 'OKS_QA_SUCCESS');
8348:
8349: END IF;
8352: EXCEPTION
8353: WHEN OTHERS THEN
8354:
8355: -- store SQL error message on message stack
8356: OKC_API.SET_MESSAGE(
8357: p_app_name => G_APP_NAME,
8358: p_msg_name => G_UNEXPECTED_ERROR,
8359: p_token1 => G_SQLCODE_TOKEN,
8360: p_token1_value => SQLCODE,
8360: p_token1_value => SQLCODE,
8361: p_token2 => G_SQLERRM_TOKEN,
8362: p_token2_value => SQLERRM);
8363:
8364: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
8365:
8366:
8367: END CHECK_CASCADE_REQUEST;
8368: /*11687112 -Cascade ER*/