[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;
1190: ) IS
1191:
1192: /***
1193: Bug 4767013: commented out
1194: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1195: l_dummy VARCHAR2(1) := '?';
1196: l_count NUMBER := 0;
1197:
1198: CURSOR l_cle_csr IS
1248: TYPE chr640_tbl_type IS TABLE OF VARCHAR2(640) INDEX BY BINARY_INTEGER;
1249: TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1250:
1251: CURSOR c_chk_overlap(cp_chr_id IN NUMBER, cp_date IN DATE) IS
1252: SELECT trgh.contract_number, nvl(trgh.contract_number_modifier, OKC_API.G_MISS_CHAR),
1253: nvl(rtrim(trgtl.line_number) || '.' || RTRIM(trgsl.line_number) ||', '|| RTRIM(trgst.name) ||' '|| RTRIM(trgsl.name), OKC_API.G_MISS_CHAR) trg_name,
1254: nvl(rtrim(srctl.line_number) || '.' || RTRIM(srcsl.line_number) ||', '|| RTRIM(srcst.name) ||' '|| RTRIM(srcsl.name), OKC_API.G_MISS_CHAR) src_name,
1255: srcsl.id srcsl_id
1256: FROM okc_k_headers_all_b trgh,
1249: TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1250:
1251: CURSOR c_chk_overlap(cp_chr_id IN NUMBER, cp_date IN DATE) IS
1252: SELECT trgh.contract_number, nvl(trgh.contract_number_modifier, OKC_API.G_MISS_CHAR),
1253: nvl(rtrim(trgtl.line_number) || '.' || RTRIM(trgsl.line_number) ||', '|| RTRIM(trgst.name) ||' '|| RTRIM(trgsl.name), OKC_API.G_MISS_CHAR) trg_name,
1254: nvl(rtrim(srctl.line_number) || '.' || RTRIM(srcsl.line_number) ||', '|| RTRIM(srcst.name) ||' '|| RTRIM(srcsl.name), OKC_API.G_MISS_CHAR) src_name,
1255: srcsl.id srcsl_id
1256: FROM okc_k_headers_all_b trgh,
1257: okc_k_lines_v trgsl,
1250:
1251: CURSOR c_chk_overlap(cp_chr_id IN NUMBER, cp_date IN DATE) IS
1252: SELECT trgh.contract_number, nvl(trgh.contract_number_modifier, OKC_API.G_MISS_CHAR),
1253: nvl(rtrim(trgtl.line_number) || '.' || RTRIM(trgsl.line_number) ||', '|| RTRIM(trgst.name) ||' '|| RTRIM(trgsl.name), OKC_API.G_MISS_CHAR) trg_name,
1254: nvl(rtrim(srctl.line_number) || '.' || RTRIM(srcsl.line_number) ||', '|| RTRIM(srcst.name) ||' '|| RTRIM(srcsl.name), OKC_API.G_MISS_CHAR) src_name,
1255: srcsl.id srcsl_id
1256: FROM okc_k_headers_all_b trgh,
1257: okc_k_lines_v trgsl,
1258: okc_k_items trgi,
1316: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
1317: END IF;
1318:
1319: -- initialize return status
1320: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1321: l_date := SYSDATE;
1322:
1323: OPEN c_chk_overlap(p_chr_id, l_date);
1324: LOOP
1328: EXIT WHEN (l_k_num_tbl.COUNT = 0);
1329:
1330: FOR i IN l_k_num_tbl.FIRST..l_k_num_tbl.LAST LOOP
1331:
1332: x_return_status := OKC_API.G_RET_STS_ERROR;
1333:
1334: 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
1335: -- the source subline has changed so we need to dump information (if any) for the last source subline processed
1336:
1341: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'previous l_count_contr: ' || l_count_contr);
1342: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'previous l_contr_num_list: ' || l_contr_num_list);
1343: END IF;
1344:
1345: OKC_API.set_message
1346: (
1347: p_app_name => 'OKS',
1348: p_msg_name => G_COVERAGE_OVERLAP_LIST,
1349: p_token1 => 'LINE_NAME',
1382: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_count_contr: ' || l_count_contr);
1383: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_contr_num_list: ' || l_contr_num_list);
1384: END IF;
1385:
1386: OKC_API.set_message
1387: (
1388: p_app_name => 'OKS',
1389: p_msg_name => G_COVERAGE_OVERLAP_LIST,
1390: p_token1 => 'LINE_NAME',
1414: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_count_contr: ' || l_count_contr);
1415: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_contr_num_list: ' || l_contr_num_list);
1416: END IF;
1417:
1418: OKC_API.set_message
1419: (
1420: p_app_name => 'OKS',
1421: p_msg_name => G_COVERAGE_OVERLAP_LIST,
1422: p_token1 => 'LINE_NAME',
1439: /***
1440: Bug 4767013 commented out
1441:
1442: -- initialize return status
1443: l_return_status := OKC_API.G_RET_STS_SUCCESS;
1444:
1445: -- Get Top Lines
1446: OPEN l_cle_csr;
1447: LOOP
1464: ,l_cve_rec.object1_id2
1465: ,l_cve_rec.start_date
1466: ,nvl(l_cve_rec.date_terminated, l_cve_rec.end_date)
1467: );
1468: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1469: x_return_status := l_return_status;
1470: END IF;
1471:
1472: END LOOP;
1476: CLOSE l_cle_csr;
1477: x_return_status := l_return_status;
1478: ***/
1479:
1480: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1481: OKC_API.set_message(
1482: p_app_name => G_APP_NAME,
1483: p_msg_name => 'OKS_QA_SUCCESS');
1484: END IF;
1477: x_return_status := l_return_status;
1478: ***/
1479:
1480: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1481: OKC_API.set_message(
1482: p_app_name => G_APP_NAME,
1483: p_msg_name => 'OKS_QA_SUCCESS');
1484: END IF;
1485:
1498: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'WHEN OTHERS: setting message after encountering error: ' || SQLCODE || ' ' || SQLERRM);
1499: END IF;
1500:
1501: -- store SQL error message on message stack
1502: OKC_API.SET_MESSAGE(
1503: p_app_name => G_APP_NAME,
1504: p_msg_name => G_UNEXPECTED_ERROR,
1505: p_token1 => G_SQLCODE_TOKEN,
1506: p_token1_value => SQLCODE,
1506: p_token1_value => SQLCODE,
1507: p_token2 => G_SQLERRM_TOKEN,
1508: p_token2_value => SQLERRM);
1509: -- notify caller of an error as UNEXPETED error
1510: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1511: -- verify that cursor was closed
1512: IF c_chk_overlap%ISOPEN THEN
1513: CLOSE c_chk_overlap;
1514: END IF;
1542: x_return_status OUT NOCOPY VARCHAR2,
1543: p_chr_id IN NUMBER
1544: ) IS
1545:
1546: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1547: l_dummy VARCHAR2(1) := '?';
1548: l_count NUMBER := 0;
1549:
1550: CURSOR l_cle_csr IS
1708: l_org_id NUMBER;
1709:
1710: BEGIN
1711: -- initialize return status
1712: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1713:
1714: -- The following code added for bug # 2364436 - Anupama
1715:
1716: OPEN l_cvd_lvl_csr (p_chr_id);
1721: OPEN l_cvd_item_csr (l_cvd_lvl_rec.id);
1722: FETCH l_cvd_item_csr INTO l_cvd_item_rec;
1723: IF (l_cvd_item_csr%NOTFOUND) OR (l_cvd_item_rec.object1_id1 IS NULL) THEN
1724: /**
1725: OKC_API.set_message(
1726: p_app_name => G_APP_NAME,
1727: p_msg_name => G_REQUIRED_LINE_VALUE,
1728: p_token1 => G_COL_NAME_TOKEN,
1729: p_token1_value => 'Covered Level Name',
1730: p_token2 => 'LINE_NAME',
1731: p_token2_value => get_line_name(l_cvd_lvl_rec.id));
1732: **/
1733:
1734: OKC_API.set_message(
1735: p_app_name => G_APP_NAME,
1736: p_msg_name => G_COVERED_LINE_REQUIRED,
1737: p_token1 => 'LINE_NAME',
1738: p_token1_value => get_line_name(l_cvd_lvl_rec.id));
1737: p_token1 => 'LINE_NAME',
1738: p_token1_value => get_line_name(l_cvd_lvl_rec.id));
1739:
1740: -- notify caller of an error
1741: x_return_status := OKC_API.G_RET_STS_ERROR;
1742: END IF;
1743: CLOSE l_cvd_item_csr;
1744: END LOOP;
1745: CLOSE l_cvd_lvl_csr;
1760: IF l_cve_rec.lty_code <> 'INST_CTR' THEN
1761: -- Negotiated amount at covered levels is required.
1762: IF l_cve_rec.PRICE_NEGOTIATED IS NULL THEN
1763: /**
1764: OKC_API.set_message(
1765: p_app_name => G_APP_NAME,
1766: p_msg_name => G_REQUIRED_LINE_VALUE,
1767: p_token1 => G_COL_NAME_TOKEN,
1768: p_token1_value => 'Final Price',
1769: p_token2 => 'LINE_NAME',
1770: p_token2_value => get_line_name(l_cve_rec.id));
1771: **/
1772:
1773: OKC_API.set_message(
1774: p_app_name => G_APP_NAME,
1775: p_msg_name => G_NEG_AMT_REQUIRED,
1776: p_token1 => 'LINE_NAME',
1777: p_token1_value => get_line_name(l_cve_rec.id));
1775: p_msg_name => G_NEG_AMT_REQUIRED,
1776: p_token1 => 'LINE_NAME',
1777: p_token1_value => get_line_name(l_cve_rec.id));
1778: -- notify caller of an error
1779: x_return_status := OKC_API.G_RET_STS_ERROR;
1780: END IF;
1781: END IF;
1782:
1783:
1790: FETCH l_default_csr INTO l_default_qty;
1791: CLOSE l_default_csr;
1792:
1793: IF l_usage_type = 'VRT' AND l_default_qty IS NULL THEN
1794: x_return_status := OKC_API.G_RET_STS_ERROR;
1795:
1796: OKC_API.set_message
1797: (
1798: p_app_name => G_APP_NAME,
1792:
1793: IF l_usage_type = 'VRT' AND l_default_qty IS NULL THEN
1794: x_return_status := OKC_API.G_RET_STS_ERROR;
1795:
1796: OKC_API.set_message
1797: (
1798: p_app_name => G_APP_NAME,
1799: p_msg_name => G_DEFAULT_READING,
1800: p_token1 => 'TOKEN',
1808: -- if statement added for subscription lines.
1809: IF l_cle_rec.lse_id <> 46 THEN
1810: -- A Contract Line must have at least 1 covered level line
1811: IF l_cve_csr%ROWCOUNT <= 0 THEN
1812: OKC_API.set_message(
1813: p_app_name => G_APP_NAME,
1814: p_msg_name => G_REQUIRED_COVERED_LINE,
1815: p_token1 => 'LINE_NAME',
1816: p_token1_value => get_line_name(l_cle_rec.id));
1814: p_msg_name => G_REQUIRED_COVERED_LINE,
1815: p_token1 => 'LINE_NAME',
1816: p_token1_value => get_line_name(l_cle_rec.id));
1817: -- notify caller of an error
1818: x_return_status := OKC_API.G_RET_STS_ERROR;
1819: END IF;
1820: END IF; -- l_cle_rec.lse_id <> 46
1821: CLOSE l_cve_csr;
1822:
1826: CLOSE l_cva_csr;
1827:
1828: IF l_count > 1 THEN
1829: -- A Service Line must have at most 1 coverage line
1830: OKC_API.set_message(
1831: p_app_name => G_APP_NAME,
1832: p_msg_name => G_INVALID_COVERAGE_LINE,
1833: p_token1 => 'LINE_NAME',
1834: p_token1_value => get_line_name(l_cle_rec.id));
1832: p_msg_name => G_INVALID_COVERAGE_LINE,
1833: p_token1 => 'LINE_NAME',
1834: p_token1_value => get_line_name(l_cle_rec.id));
1835: -- notify caller of an error
1836: x_return_status := OKC_API.G_RET_STS_ERROR;
1837: END IF;
1838: END IF;
1839:
1840:
1868:
1869: IF l_sc_percent <> 100 THEN
1870:
1871: /**
1872: OKC_API.set_message(
1873: p_app_name => G_APP_NAME,
1874: p_msg_name => G_REQUIRED_LINE_VALUE,
1875: p_token1 => G_COL_NAME_TOKEN,
1876: p_token1_value => 'Quota Sales Credit at line must be assigned to 100%',
1876: p_token1_value => 'Quota Sales Credit at line must be assigned to 100%',
1877: p_token2 => 'LINE_NAME',
1878: p_token2_value => get_line_name(l_cle_rec.id));
1879: **/
1880: OKC_API.set_message( -- Bug 4708540 (changed message)
1881: p_app_name => G_APP_NAME,
1882: p_msg_name => 'OKS_INCOMP_QUOTA_SALES_CREDIT',
1883: p_token1 => 'LINE_NAME',
1884: p_token1_value => get_line_name(l_cle_rec.id));
1883: p_token1 => 'LINE_NAME',
1884: p_token1_value => get_line_name(l_cle_rec.id));
1885:
1886: -- notify caller of an error
1887: x_return_status := OKC_API.G_RET_STS_ERROR;
1888:
1889: END IF;
1890:
1891: END IF;
1911: -- Bug 4558172--
1912: IF nvl(l_cust_id, 0) <> nvl(l_customer_id, - 99) THEN
1913: -- Bug 4558172--
1914: /**
1915: OKC_API.set_message(
1916: p_app_name => G_APP_NAME,
1917: p_msg_name => G_REQUIRED_VALUE,
1918: p_token1 => G_COL_NAME_TOKEN,
1919: p_token1_value => 'INVALID BILLING CONTACTS ');
1918: p_token1 => G_COL_NAME_TOKEN,
1919: p_token1_value => 'INVALID BILLING CONTACTS ');
1920: **/
1921:
1922: OKC_API.set_message(
1923: p_app_name => G_APP_NAME,
1924: p_msg_name => G_INVALID_BILLING_CONTACTS);
1925:
1926: -- notify caller of an error
1923: p_app_name => G_APP_NAME,
1924: p_msg_name => G_INVALID_BILLING_CONTACTS);
1925:
1926: -- notify caller of an error
1927: x_return_status := OKC_API.G_RET_STS_ERROR;
1928: END IF;
1929:
1930: END IF;
1931:
1936: -- A Contract must have at least 1 Contract Line
1937:
1938: IF l_cle_csr%ROWCOUNT <= 0 THEN
1939: /**
1940: OKC_API.set_message(
1941: p_app_name => G_APP_NAME,
1942: p_msg_name => G_REQUIRED_VALUE,
1943: p_token1 => G_COL_NAME_TOKEN,
1944: p_token1_value => 'Contract Line');
1943: p_token1 => G_COL_NAME_TOKEN,
1944: p_token1_value => 'Contract Line');
1945: **/
1946:
1947: OKC_API.set_message(
1948: p_app_name => G_APP_NAME,
1949: p_msg_name => G_LINE_REQUIRED);
1950:
1951:
1949: p_msg_name => G_LINE_REQUIRED);
1950:
1951:
1952: -- notify caller of an error
1953: x_return_status := OKC_API.G_RET_STS_ERROR;
1954: END IF;
1955:
1956:
1957: CLOSE l_cle_csr;
1965: FETCH l_salesperson_contact INTO l_contact_id;
1966: CLOSE l_salesperson_contact;
1967:
1968: IF l_contact_id IS NULL THEN
1969: OKC_API.set_message(
1970: p_app_name => G_APP_NAME,
1971: p_msg_name => G_MISSING_SALESREP);
1972:
1973: -- notify caller of an error
1970: p_app_name => G_APP_NAME,
1971: p_msg_name => G_MISSING_SALESREP);
1972:
1973: -- notify caller of an error
1974: x_return_status := OKC_API.G_RET_STS_ERROR;
1975: END IF;
1976:
1977: END IF;
1978:
1977: END IF;
1978:
1979:
1980:
1981: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1982: OKC_API.set_message(
1983: p_app_name => G_APP_NAME,
1984: p_msg_name => 'OKS_QA_SUCCESS');
1985: END IF;
1978:
1979:
1980:
1981: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1982: OKC_API.set_message(
1983: p_app_name => G_APP_NAME,
1984: p_msg_name => 'OKS_QA_SUCCESS');
1985: END IF;
1986:
1991: -- no processing necessary; validation can continue with next column
1992: NULL;
1993: WHEN OTHERS THEN
1994: -- store SQL error message on message stack
1995: OKC_API.SET_MESSAGE(
1996: p_app_name => G_APP_NAME,
1997: p_msg_name => G_UNEXPECTED_ERROR,
1998: p_token1 => G_SQLCODE_TOKEN,
1999: p_token1_value => SQLCODE,
1999: p_token1_value => SQLCODE,
2000: p_token2 => G_SQLERRM_TOKEN,
2001: p_token2_value => SQLERRM);
2002: -- notify caller of an error as UNEXPETED error
2003: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2004: -- verify that cursor was closed
2005: IF l_cle_csr%ISOPEN THEN
2006: CLOSE l_cle_csr;
2007: END IF;
2031: x_return_status OUT NOCOPY VARCHAR2,
2032: p_chr_id IN NUMBER
2033: ) IS
2034:
2035: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2036: l_dummy VARCHAR2(1) := '?';
2037: l_count NUMBER := 0;
2038:
2039: CURSOR l_cle_csr IS
2235:
2236:
2237: BEGIN
2238: -- initialize return status
2239: x_return_status := OKC_API.G_RET_STS_SUCCESS;
2240:
2241: --changes for passing org_id in is_service_available
2242: OPEN get_auth_org_csr;
2243: FETCH get_auth_org_csr INTO get_auth_org_rec;
2255: FROM okc_k_lines_b
2256: WHERE id = l_cle_rec.cle_id;
2257:
2258: IF l_date_cancelled IS NULL THEN
2259: OKC_API.set_message(
2260: p_app_name => G_APP_NAME,
2261: p_msg_name => G_INVALID_LINE_ITEM,
2262: p_token1 => 'LINE_NAME',
2263: --p_token1_value => get_line_name(l_cle_rec.id));
2264: p_token1_value => get_line_name(l_cle_rec.cle_id));
2265:
2266:
2267: -- notify caller of an error
2268: x_return_status := OKC_API.G_RET_STS_ERROR;
2269: END IF;
2270:
2271: END LOOP;
2272: CLOSE l_cle_csr;
2276: --OPEN l_cle1_csr;
2277: --LOOP
2278: -- FETCH l_cle1_csr INTO l_cle1_rec;
2279: -- EXIT WHEN l_cle1_csr%NOTFOUND;
2280: -- OKC_API.set_message(
2281: -- p_app_name => G_APP_NAME,
2282: -- p_msg_name => G_REQUIRED_LINE_VALUE,
2283: -- p_token1 => G_COL_NAME_TOKEN,
2284: -- p_token1_value => 'Name',
2285: -- p_token2 => 'LINE_NAME',
2286: -- p_token2_value => get_line_name(l_cle1_rec.id));
2287:
2288: -- -- notify caller of an error
2289: -- x_return_status := OKC_API.G_RET_STS_ERROR;
2290: --END LOOP;
2291: --CLOSE l_cle1_csr;
2292: OPEN l_qa_csr;
2293: FETCH l_qa_csr INTO l_k_no, l_qcl, l_contr_category;
2293: FETCH l_qa_csr INTO l_k_no, l_qcl, l_contr_category;
2294: CLOSE l_qa_csr;
2295:
2296: IF l_qcl IS NULL THEN
2297: OKC_API.set_message(
2298: p_app_name => G_APP_NAME,
2299: p_msg_name => G_QA_CHECK,
2300: p_token1 => 'TOKEN',
2301: p_token1_value => l_k_no);
2300: p_token1 => 'TOKEN',
2301: p_token1_value => l_k_no);
2302:
2303: -- notify caller of an error
2304: x_return_status := OKC_API.G_RET_STS_ERROR;
2305: END IF;
2306:
2307: IF l_contr_category = 'SUBSCRIPTION' THEN
2308: l_customer := 'SUBSCRIBER';
2316: FETCH l_cpl_csr INTO l_count;
2317: CLOSE l_cpl_csr;
2318:
2319: IF (l_count > 1) THEN
2320: OKC_API.set_message(
2321: p_app_name => G_APP_NAME,
2322: p_msg_name => G_PARTY_ROLE,
2323: p_token1 => 'ROLE',
2324: p_token1_value => l_customer);
2322: p_msg_name => G_PARTY_ROLE,
2323: p_token1 => 'ROLE',
2324: p_token1_value => l_customer);
2325: -- notify caller of an error
2326: x_return_status := OKC_API.G_RET_STS_ERROR;
2327: END IF;
2328:
2329: -- It is required that one customer or subscriber may be attached to
2330: -- a contract. Added by mkhayer on 05/03/2002
2328:
2329: -- It is required that one customer or subscriber may be attached to
2330: -- a contract. Added by mkhayer on 05/03/2002
2331: IF (l_count < 1) THEN
2332: OKC_API.set_message(
2333: p_app_name => G_APP_NAME,
2334: p_msg_name => G_PARTY_MISSING_ROLE,
2335: p_token1 => 'ROLE',
2336: p_token1_value => l_customer);
2334: p_msg_name => G_PARTY_MISSING_ROLE,
2335: p_token1 => 'ROLE',
2336: p_token1_value => l_customer);
2337: -- notify caller of an error (The contract has no Customer attached to it)
2338: x_return_status := OKC_API.G_RET_STS_ERROR;
2339: END IF;
2340:
2341: -- At most only 1 vendor or merchant may be attached to the Contract
2342: OPEN l_cpl_csr(l_vedor);
2343: FETCH l_cpl_csr INTO l_count;
2344: CLOSE l_cpl_csr;
2345:
2346: IF (l_count > 1) THEN
2347: OKC_API.set_message(
2348: p_app_name => G_APP_NAME,
2349: p_msg_name => G_PARTY_ROLE,
2350: p_token1 => 'ROLE',
2351: p_token1_value => l_vedor);
2349: p_msg_name => G_PARTY_ROLE,
2350: p_token1 => 'ROLE',
2351: p_token1_value => l_vedor);
2352: -- notify caller of an error
2353: x_return_status := OKC_API.G_RET_STS_ERROR;
2354: END IF;
2355:
2356: -- It is required that one vendor or merchant may be attached to a contract.
2357: -- Added by mkhayer on 05/03/2002
2355:
2356: -- It is required that one vendor or merchant may be attached to a contract.
2357: -- Added by mkhayer on 05/03/2002
2358: IF (l_count < 1) THEN
2359: OKC_API.set_message(
2360: p_app_name => G_APP_NAME,
2361: p_msg_name => G_PARTY_MISSING_ROLE,
2362: p_token1 => 'ROLE',
2363: p_token1_value => l_vedor);
2361: p_msg_name => G_PARTY_MISSING_ROLE,
2362: p_token1 => 'ROLE',
2363: p_token1_value => l_vedor);
2364: -- notify caller of an error (The contract has no Vendor attached to it)
2365: x_return_status := OKC_API.G_RET_STS_ERROR;
2366: END IF;
2367:
2368: OPEN l_desc_csr;
2369: FETCH l_desc_csr INTO l_k_no, l_desc;
2369: FETCH l_desc_csr INTO l_k_no, l_desc;
2370: CLOSE l_desc_csr;
2371:
2372: IF l_desc IS NULL THEN
2373: OKC_API.set_message(
2374: p_app_name => G_APP_NAME,
2375: p_msg_name => G_SHORT_DESC,
2376: p_token1 => 'TOKEN',
2377: p_token1_value => l_k_no);
2375: p_msg_name => G_SHORT_DESC,
2376: p_token1 => 'TOKEN',
2377: p_token1_value => l_k_no);
2378: -- notify caller of an error
2379: x_return_status := OKC_API.G_RET_STS_ERROR;
2380: END IF;
2381:
2382: OPEN l_price_csr;
2383: FETCH l_price_csr INTO l_price_id;
2383: FETCH l_price_csr INTO l_price_id;
2384: CLOSE l_price_csr;
2385:
2386: IF l_price_id IS NULL THEN
2387: OKC_API.set_message(
2388: p_app_name => G_APP_NAME,
2389: p_msg_name => G_PRICE_LIST,
2390: p_token1 => 'TOKEN',
2391: --p_token1_value => 'BILLING HEADER');
2390: p_token1 => 'TOKEN',
2391: --p_token1_value => 'BILLING HEADER');
2392: p_token1_value => l_k_no);
2393: -- notify caller of an error
2394: x_return_status := OKC_API.G_RET_STS_ERROR;
2395: END IF;
2396:
2397:
2398: OPEN l_k_grp_csr;
2399: FETCH l_k_grp_csr INTO l_grp_id;
2400:
2401: IF l_grp_id IS NULL THEN
2402:
2403: OKC_API.set_message(
2404: p_app_name => G_APP_NAME,
2405: p_msg_name => G_K_GROUP,
2406: p_token1 => 'TOKEN',
2407: --p_token1_value => 'Header Admin');
2408: p_token1_value => l_k_no);
2409:
2410:
2411: -- notify caller of an error
2412: x_return_status := OKC_API.G_RET_STS_ERROR;
2413: END IF;
2414:
2415: CLOSE l_k_grp_csr;
2416:
2420: CLOSE l_wf_csr;
2421:
2422: IF l_wf_id IS NULL THEN
2423:
2424: OKC_API.set_message(
2425: p_app_name => G_APP_NAME,
2426: p_msg_name => G_WORKFLOW );
2427:
2428:
2426: p_msg_name => G_WORKFLOW );
2427:
2428:
2429: -- notify caller of an error
2430: x_return_status := OKC_API.G_RET_STS_ERROR;
2431: END IF;
2432:
2433: /* check for product availability*/
2434: /*** Get customer_id ****/
2522: FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT , G_PKG_NAME||'.'||l_api_name,'l_org_id: '||get_auth_org_rec.authoring_org_id);
2523: END IF;
2524:
2525: If l_available_yn = 'N' then
2526: x_return_status := OKC_API.G_RET_STS_ERROR;
2527:
2528: OKC_API.set_message
2529: (
2530: p_app_name => 'OKS',
2524:
2525: If l_available_yn = 'N' then
2526: x_return_status := OKC_API.G_RET_STS_ERROR;
2527:
2528: OKC_API.set_message
2529: (
2530: p_app_name => 'OKS',
2531: p_msg_name => 'OKS_PRODUCT_AVAILABILITY',
2532: p_token1 => 'TOKEN1',
2559: IF (l_get_sub_line_rec.price_unit IS NULL OR l_get_item_rec.uom_code IS NULL)
2560: THEN
2561:
2562: -- store SQL error message on message stack
2563: OKC_API.SET_MESSAGE
2564: (
2565: p_app_name => G_APP_NAME,
2566: p_msg_name => 'OKS_UOM_VALUES',
2567: p_token1 => 'TOKEN1',
2566: p_msg_name => 'OKS_UOM_VALUES',
2567: p_token1 => 'TOKEN1',
2568: p_token1_value => get_line_name(l_get_sub_line_rec.id));
2569:
2570: x_return_status := OKC_API.G_RET_STS_ERROR;
2571:
2572: END IF; -- IF (l_get_sub_line_rec.price_unit IS NULL OR l_get_item_rec.uom_code IS NULL)
2573:
2574: END LOOP; --FOR l_get_item_rec in l_get_item_csr
2590: IF (l_get_item_rec.uom_code IS NULL)
2591: THEN
2592:
2593: -- store SQL error message on message stack
2594: OKC_API.SET_MESSAGE
2595: (
2596: p_app_name => G_APP_NAME,
2597: p_msg_name => 'OKS_UOM_VALUES',
2598: p_token1 => 'TOKEN1',
2597: p_msg_name => 'OKS_UOM_VALUES',
2598: p_token1 => 'TOKEN1',
2599: p_token1_value => get_line_name(l_get_sub_line_rec.id));
2600:
2601: x_return_status := OKC_API.G_RET_STS_ERROR;
2602:
2603: END IF; -- IF (l_get_sub_line_rec.price_unit IS NULL OR l_get_item_rec.uom_code IS NULL)
2604:
2605: END LOOP; --FOR l_get_item_rec in l_get_item_csr
2631:
2632: IF (l_price_unit_tbl(i) IS NULL OR l_uom_code_tbl(i) IS NULL)
2633: THEN
2634: -- store SQL error message on message stack
2635: OKC_API.SET_MESSAGE
2636: (
2637: p_app_name => G_APP_NAME,
2638: p_msg_name => 'OKS_UOM_VALUES',
2639: p_token1 => 'TOKEN1',
2638: p_msg_name => 'OKS_UOM_VALUES',
2639: p_token1 => 'TOKEN1',
2640: p_token1_value => get_line_name(l_sub_line_id_tbl(i)));
2641:
2642: x_return_status := OKC_API.G_RET_STS_ERROR;
2643:
2644: END IF;
2645:
2646: ELSE
2647: Bug #6317316 commented the above IF part */
2648: IF (l_uom_code_tbl(i) IS NULL)
2649: THEN
2650: -- store SQL error message on message stack
2651: OKC_API.SET_MESSAGE
2652: (
2653: p_app_name => G_APP_NAME,
2654: p_msg_name => 'OKS_UOM_VALUES',
2655: p_token1 => 'TOKEN1',
2654: p_msg_name => 'OKS_UOM_VALUES',
2655: p_token1 => 'TOKEN1',
2656: p_token1_value => get_line_name(l_sub_line_id_tbl(i)));
2657:
2658: x_return_status := OKC_API.G_RET_STS_ERROR;
2659:
2660: END IF;
2661: --commented for bug#6317316 harlaksh
2662: --END IF;
2670:
2671:
2672:
2673: /* Commented out because it's getting called in check_required_values
2674: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2675: OKC_API.set_message(
2676: p_app_name => G_APP_NAME,
2677: p_msg_name => 'OKS_QA_SUCCESS');
2678: END IF;
2671:
2672:
2673: /* Commented out because it's getting called in check_required_values
2674: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2675: OKC_API.set_message(
2676: p_app_name => G_APP_NAME,
2677: p_msg_name => 'OKS_QA_SUCCESS');
2678: END IF;
2679: */
2682: -- no processing necessary; validation can continue with next column
2683: NULL;
2684: WHEN OTHERS THEN
2685: -- store SQL error message on message stack
2686: OKC_API.SET_MESSAGE(
2687: p_app_name => G_APP_NAME,
2688: p_msg_name => G_UNEXPECTED_ERROR,
2689: p_token1 => G_SQLCODE_TOKEN,
2690: p_token1_value => SQLCODE,
2690: p_token1_value => SQLCODE,
2691: p_token2 => G_SQLERRM_TOKEN,
2692: p_token2_value => SQLERRM);
2693: -- notify caller of an error as UNEXPETED error
2694: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2695: -- verify that cursor was closed
2696:
2697: IF l_cle_csr%ISOPEN THEN
2698: CLOSE l_cle_csr;
2799:
2800: RETURN l_cust_trx_type_id;
2801: EXCEPTION
2802: WHEN OTHERS THEN
2803: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN, SQLERRM);
2804: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2805: FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, G_PKG_NAME || '.' || l_api_name,'Exception OTHERS: '|| SQLERRM);
2806: END IF;
2807:
3069: RAISE FND_API.g_exc_error;
3070: END IF;
3071:
3072: -- initialize return status
3073: x_return_status := OKC_API.G_RET_STS_SUCCESS;
3074:
3075:
3076:
3077: --modified top_line_csr to use bulk collect for bug 5442886
3218:
3219:
3220: IF l_valid_flag <> 'Y' THEN
3221:
3222: OKC_API.set_message(p_app_name => G_APP_NAME,
3223: p_msg_name => G_INVALID_TAX_EXEMPT_DATE,
3224: p_token1 => 'LINE_NUM',
3225: p_token1_value => l_number_tbl(i),
3226: p_token2 => 'EXEMPT_NUM',
3226: p_token2 => 'EXEMPT_NUM',
3227: p_token2_value => l_exempt_cert_number_tbl(i)
3228: );
3229:
3230: x_return_status := OKC_API.G_RET_STS_ERROR;
3231:
3232:
3233: -- GCHADHA --
3234: -- IKON Enhancement --
3280: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_mod_name,'l_legal_entity_id: '|| l_legal_entity_id);
3281: END IF;
3282:
3283: IF l_valid_flag <> 'Y' THEN
3284: OKC_API.set_message(
3285: p_app_name => G_APP_NAME,
3286: p_msg_name => 'OKS_PARTIAL_TAX_EXEMPT_LINE',
3287: p_token1 => 'EXEMPT_NUM',
3288: p_token1_value => l_exempt_cert_number_tbl(i),
3288: p_token1_value => l_exempt_cert_number_tbl(i),
3289: p_token2 => 'LINE_NUM',
3290: p_token2_value => l_number_tbl(i));
3291: -- notify caller of an error
3292: x_return_status := OKC_API.G_RET_STS_ERROR;
3293:
3294: /** not needed for new R12 flow bug 5264786
3295: ELSE
3296:
3300: IF l_tax_exempt_acct <> l_cust_acct_id THEN
3301: OPEN Get_Customer_Name (l_cust_acct_id);
3302: FETCH Get_Customer_Name INTO l_cust_acct_name;
3303: CLOSE Get_Customer_Name;
3304: OKC_API.set_message(
3305: p_app_name => G_APP_NAME,
3306: p_msg_name => G_INVALID_TAX_EXEMPT_LINE,
3307: p_token1 => 'TOKEN1',
3308: p_token1_value => l_exempt_certificate_number,
3309: p_token2 => 'TOKEN2',
3310: p_token2_value => l_cust_acct_name,
3311: p_token3 => 'TOKEN3',
3312: p_token3_value => l_number);
3313: x_return_status := OKC_API.G_RET_STS_ERROR;
3314: END IF;
3315: **/
3316:
3317: -- END GCHADHA --
3331: -- if found means date does not fall between the start date/end date of the exemption
3332: IF old_valid_exemption_csr%FOUND THEN
3333: -- Line LINE_NUM start date does not fall within the effective dates
3334: -- for Tax Exemption EXEMPT_NUM. The Tax Exemption will not be applied to this line.
3335: OKC_API.set_message(p_app_name => G_APP_NAME,
3336: p_msg_name => G_INVALID_TAX_EXEMPT_DATE,
3337: p_token1 => 'LINE_NUM',
3338: p_token1_value => l_number_tbl(i),
3339: p_token2 => 'EXEMPT_NUM',
3338: p_token1_value => l_number_tbl(i),
3339: p_token2 => 'EXEMPT_NUM',
3340: p_token2_value => l_tax_exemption_number
3341: );
3342: x_return_status := OKC_API.G_RET_STS_ERROR;
3343:
3344: ELSE
3345: OPEN trx_date_csr(l_cle_id_tbl(i));
3346: FETCH trx_date_csr INTO l_trx_date;
3352: IF trx_date_csr%found AND l_tax_exemption_id_tbl(i) IS NOT NULL THEN
3353: OPEN old_exemption_period_csr (l_line_start_date_tbl(i), l_tax_exemption_id_tbl(i), l_trx_date) ;
3354: FETCH old_exemption_period_csr INTO l_tax_exemption_number, l_start_date, l_end_date ;
3355: IF old_exemption_period_csr%FOUND THEN
3356: OKC_API.set_message(
3357: p_app_name => G_APP_NAME,
3358: p_msg_name => G_INVALID_TAX_EXEMPT,
3359: p_token1 => 'EXEMPTNUMBER',
3360: p_token1_value => l_tax_exemption_number,
3362: p_token2_value => l_number_tbl(i),
3363: p_token3 => 'DATE',
3364: p_token3_value => l_end_date);
3365: -- notify caller of an error
3366: x_return_status := OKC_API.G_RET_STS_ERROR;
3367: -- GCHADHA --
3368: -- IKON Enhancement --
3369: -- 5/6/2005 --
3370: ELSE
3374: IF l_tax_exempt_acct <> l_cust_acct_id_tbl(i) THEN
3375: OPEN Get_Customer_Name (l_cust_acct_id_tbl(i));
3376: FETCH Get_Customer_Name INTO l_cust_acct_name;
3377: CLOSE Get_Customer_Name;
3378: OKC_API.set_message(
3379: p_app_name => G_APP_NAME,
3380: p_msg_name => G_INVALID_TAX_EXEMPT_LINE,
3381: p_token1 => 'TOKEN1',
3382: p_token1_value => l_tax_exemption_number,
3383: p_token2 => 'TOKEN2',
3384: p_token2_value => l_cust_acct_name,
3385: p_token3 => 'TOKEN3',
3386: p_token3_value => l_number_tbl(i));
3387: x_return_status := OKC_API.G_RET_STS_ERROR;
3388: END IF;
3389: -- END GCHADHA --
3390: -- 5/6/2005 --
3391: -- IKON Enhancement --
3402: IF old_approved_exemption_csr%FOUND THEN
3403: -- Line LINE_NUM has an unapproved exemption. Billing this
3404: -- line will result in creation of an invoice with an
3405: -- unapproved tax exemption.
3406: OKC_API.set_message(p_app_name => G_APP_NAME,
3407: p_msg_name => G_UNAPPROVED_TAX_EXEMPT,
3408: p_token1 => 'LINE_NUM',
3409: p_token1_value => l_number_tbl(i)
3410: );
3407: p_msg_name => G_UNAPPROVED_TAX_EXEMPT,
3408: p_token1 => 'LINE_NUM',
3409: p_token1_value => l_number_tbl(i)
3410: );
3411: x_return_status := OKC_API.G_RET_STS_ERROR;
3412: END IF;
3413: CLOSE old_approved_exemption_csr;
3414:
3415: END IF;
3428: IF hdr_tax_exemp_rec.tax_exemption_id IS NOT NULL THEN
3429: OPEN old_approved_exemption_csr(hdr_tax_exemp_rec.tax_exemption_id);
3430: FETCH old_approved_exemption_csr INTO l_status;
3431: IF old_approved_exemption_csr%FOUND THEN
3432: OKC_API.set_message(p_app_name => G_APP_NAME,
3433: p_msg_name => G_UNAPPROVED_HDR_TAX_EXEMPT
3434: );
3435: x_return_status := OKC_API.G_RET_STS_ERROR;
3436: END IF;
3431: IF old_approved_exemption_csr%FOUND THEN
3432: OKC_API.set_message(p_app_name => G_APP_NAME,
3433: p_msg_name => G_UNAPPROVED_HDR_TAX_EXEMPT
3434: );
3435: x_return_status := OKC_API.G_RET_STS_ERROR;
3436: END IF;
3437: CLOSE old_approved_exemption_csr;
3438: ELSIF hdr_tax_exemp_rec.exempt_certificate_number IS NOT NULL THEN
3439: -- This contract has an unapproved exemption. Billing this
3533: END IF;
3534:
3535:
3536: IF l_valid_flag <> 'Y' THEN
3537: OKC_API.set_message(
3538: p_app_name => G_APP_NAME,
3539: p_msg_name => 'OKS_INVALID_TAX_EXEMPT_HEADER',
3540: p_token1 => 'EXEMPT_NUM',
3541: p_token1_value => hdr_tax_exemp_rec.exempt_certificate_number);
3539: p_msg_name => 'OKS_INVALID_TAX_EXEMPT_HEADER',
3540: p_token1 => 'EXEMPT_NUM',
3541: p_token1_value => hdr_tax_exemp_rec.exempt_certificate_number);
3542:
3543: x_return_status := OKC_API.G_RET_STS_ERROR;
3544:
3545: ELSE
3546: ZX_TCM_VALIDATE_EXEMPT_PKG.VALIDATE_TAX_EXEMPTIONS
3547: (p_tax_exempt_number => hdr_tax_exemp_rec.exempt_certificate_number,
3568: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_mod_name,'l_valid_flag: '|| l_valid_flag);
3569: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_mod_name,'l_legal_entity_id: '|| l_legal_entity_id);
3570: END IF;
3571: IF l_valid_flag <> 'Y' THEN
3572: OKC_API.set_message(
3573: p_app_name => G_APP_NAME,
3574: p_msg_name => 'OKS_PARTIAL_TAX_EXEMPT_HEADER',
3575: p_token1 => 'EXEMPT_NUM',
3576: p_token1_value => hdr_tax_exemp_rec.exempt_certificate_number);
3574: p_msg_name => 'OKS_PARTIAL_TAX_EXEMPT_HEADER',
3575: p_token1 => 'EXEMPT_NUM',
3576: p_token1_value => hdr_tax_exemp_rec.exempt_certificate_number);
3577:
3578: x_return_status := OKC_API.G_RET_STS_ERROR;
3579: END IF;
3580:
3581:
3582: END IF;
3586: END IF;
3587: END IF;
3588: CLOSE get_hdr_tax_exemp;
3589:
3590: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3591: OKC_API.set_message(
3592: p_app_name => G_APP_NAME,
3593: p_msg_name => 'OKS_QA_SUCCESS');
3594: END IF;
3587: END IF;
3588: CLOSE get_hdr_tax_exemp;
3589:
3590: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3591: OKC_API.set_message(
3592: p_app_name => G_APP_NAME,
3593: p_msg_name => 'OKS_QA_SUCCESS');
3594: END IF;
3595:
3602: -- no processing necessary; validation can continue with next column
3603: NULL;
3604: WHEN OTHERS THEN
3605: -- store SQL error message on message stack
3606: OKC_API.SET_MESSAGE(
3607: p_app_name => G_APP_NAME,
3608: p_msg_name => G_UNEXPECTED_ERROR,
3609: p_token1 => G_SQLCODE_TOKEN,
3610: p_token1_value => SQLCODE,
3610: p_token1_value => SQLCODE,
3611: p_token2 => G_SQLERRM_TOKEN,
3612: p_token2_value => SQLERRM);
3613: -- notify caller of an error as UNEXPETED error
3614: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3615: IF top_line_csr%ISOPEN THEN
3616: CLOSE top_line_csr;
3617: END IF;
3618:
3713:
3714: BEGIN
3715:
3716: -- initialize return status
3717: x_return_status := OKC_API.G_RET_STS_SUCCESS;
3718:
3719: OPEN check_cust_on_credit_hold;
3720: FETCH check_cust_on_credit_hold INTO v_customer_name;
3721:
3721:
3722: IF check_cust_on_credit_hold%FOUND
3723: THEN
3724:
3725: OKC_API.set_message(
3726: p_app_name => G_APP_NAME,
3727: p_msg_name => G_CUSTOMER_ON_CREDIT_HOLD,
3728: p_token1 => 'CUSTOMER_NAME',
3729: p_token1_value => v_customer_name);
3728: p_token1 => 'CUSTOMER_NAME',
3729: p_token1_value => v_customer_name);
3730:
3731: -- notify caller of an error
3732: x_return_status := OKC_API.G_RET_STS_ERROR;
3733:
3734: END IF;
3735:
3736: CLOSE check_cust_on_credit_hold;
3734: END IF;
3735:
3736: CLOSE check_cust_on_credit_hold;
3737:
3738: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3739: OKC_API.set_message(
3740: p_app_name => G_APP_NAME,
3741: p_msg_name => 'OKS_QA_SUCCESS');
3742: END IF;
3735:
3736: CLOSE check_cust_on_credit_hold;
3737:
3738: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
3739: OKC_API.set_message(
3740: p_app_name => G_APP_NAME,
3741: p_msg_name => 'OKS_QA_SUCCESS');
3742: END IF;
3743:
3746: -- no processing necessary; validation can continue with next column
3747: NULL;
3748: WHEN OTHERS THEN
3749: -- store SQL error message on message stack
3750: OKC_API.SET_MESSAGE(
3751: p_app_name => G_APP_NAME,
3752: p_msg_name => G_UNEXPECTED_ERROR,
3753: p_token1 => G_SQLCODE_TOKEN,
3754: p_token1_value => SQLCODE,
3754: p_token1_value => SQLCODE,
3755: p_token2 => G_SQLERRM_TOKEN,
3756: p_token2_value => SQLERRM);
3757: -- notify caller of an error as UNEXPETED error
3758: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3759:
3760: END check_cust_credit_hold;
3761:
3762: /*============================================================================+
3897: -- END GCHADHA --
3898: BEGIN
3899:
3900: -- initialize return status
3901: x_return_status := OKC_API.G_RET_STS_SUCCESS;
3902:
3903: -- Bug 4138244 --
3904: -- GCHADHA --
3905: OPEN get_billto_shipto(p_chr_id);
3910: OPEN check_address_header(l_bill_to_site_use_id, 'BILL_TO');
3911: FETCH check_address_header INTO l_bto_address_rec;
3912: IF check_address_header%FOUND THEN
3913: IF l_bto_address_rec.status <> 'A' THEN
3914: OKC_API.set_message(
3915: p_app_name => G_APP_NAME,
3916: p_msg_name => G_BTO_INVALID_HEAD
3917: );
3918:
3916: p_msg_name => G_BTO_INVALID_HEAD
3917: );
3918:
3919: -- notify caller of an error
3920: x_return_status := OKC_API.G_RET_STS_ERROR;
3921: ELSE
3922: OPEN check_site (l_bto_address_rec.cust_acct_site_id);
3923: FETCH check_site INTO l_status;
3924: IF check_site%FOUND THEN
3922: OPEN check_site (l_bto_address_rec.cust_acct_site_id);
3923: FETCH check_site INTO l_status;
3924: IF check_site%FOUND THEN
3925: IF l_status <> 'A' THEN
3926: OKC_API.set_message(
3927: p_app_name => G_APP_NAME,
3928: p_msg_name => G_BTO_INVALID_HEAD
3929: );
3930: -- notify caller of an error
3927: p_app_name => G_APP_NAME,
3928: p_msg_name => G_BTO_INVALID_HEAD
3929: );
3930: -- notify caller of an error
3931: x_return_status := OKC_API.G_RET_STS_ERROR;
3932: END IF;
3933: END IF;
3934: CLOSE check_site;
3935: END IF; -- If l_bto_address_rec.status <> 'A' THEN
3942: OPEN check_address_header(l_ship_to_site_use_id, 'SHIP_TO');
3943: FETCH check_address_header INTO l_sto_address_rec;
3944: IF check_address_header%FOUND THEN
3945: IF l_sto_address_rec.status <> 'A' THEN
3946: OKC_API.set_message(
3947: p_app_name => G_APP_NAME,
3948: p_msg_name => G_STO_INVALID_HEAD
3949: );
3950:
3948: p_msg_name => G_STO_INVALID_HEAD
3949: );
3950:
3951: -- notify caller of an error
3952: x_return_status := OKC_API.G_RET_STS_ERROR;
3953: ELSE
3954: OPEN check_site (l_sto_address_rec.cust_acct_site_id);
3955: FETCH check_site INTO l_status;
3956: IF check_site%FOUND THEN
3954: OPEN check_site (l_sto_address_rec.cust_acct_site_id);
3955: FETCH check_site INTO l_status;
3956: IF check_site%FOUND THEN
3957: IF l_status <> 'A' THEN
3958: OKC_API.set_message(
3959: p_app_name => G_APP_NAME,
3960: p_msg_name => G_STO_INVALID_HEAD
3961: );
3962: -- notify caller of an error
3959: p_app_name => G_APP_NAME,
3960: p_msg_name => G_STO_INVALID_HEAD
3961: );
3962: -- notify caller of an error
3963: x_return_status := OKC_API.G_RET_STS_ERROR;
3964: END IF;
3965: END IF;
3966: CLOSE check_site;
3967: END IF; -- If l_sto_address_rec.status <> 'A' THEN
3984: FETCH check_address INTO l_bto_address_rec;
3985:
3986: IF check_address%NOTFOUND THEN
3987:
3988: OKC_API.set_message(
3989: p_app_name => G_APP_NAME,
3990: p_msg_name => G_BTO_REQUIRED,
3991: p_token1 => 'LINE',
3992: p_token1_value => line_rec.line_number);
3991: p_token1 => 'LINE',
3992: p_token1_value => line_rec.line_number);
3993:
3994: -- notify caller of an error
3995: x_return_status := OKC_API.G_RET_STS_ERROR;
3996: ELSIF l_bto_address_rec.status <> 'A' THEN
3997: -- If bill to address is entered then
3998: -- check if it is of active status
3999:
3996: ELSIF l_bto_address_rec.status <> 'A' THEN
3997: -- If bill to address is entered then
3998: -- check if it is of active status
3999:
4000: OKC_API.set_message(
4001: p_app_name => G_APP_NAME,
4002: p_msg_name => G_BTO_INVALID,
4003: p_token1 => 'LINE',
4004: p_token1_value => line_rec.line_number);
4003: p_token1 => 'LINE',
4004: p_token1_value => line_rec.line_number);
4005:
4006: -- notify caller of an error
4007: x_return_status := OKC_API.G_RET_STS_ERROR;
4008: ELSE
4009: OPEN check_site (l_bto_address_rec.cust_acct_site_id);
4010: FETCH check_site INTO l_status;
4011: IF check_site%FOUND THEN
4009: OPEN check_site (l_bto_address_rec.cust_acct_site_id);
4010: FETCH check_site INTO l_status;
4011: IF check_site%FOUND THEN
4012: IF l_status <> 'A' THEN
4013: OKC_API.set_message(
4014: p_app_name => G_APP_NAME,
4015: p_msg_name => G_BTO_INVALID,
4016: p_token1 => 'LINE',
4017: p_token1_value => line_rec.line_number);
4015: p_msg_name => G_BTO_INVALID,
4016: p_token1 => 'LINE',
4017: p_token1_value => line_rec.line_number);
4018: -- notify caller of an error
4019: x_return_status := OKC_API.G_RET_STS_ERROR;
4020: END IF;
4021: END IF;
4022: CLOSE check_site;
4023: END IF; -- Elsif l_bto_address_rec.status <> 'A' THEN
4026: OPEN check_address(line_id, 'SHIP_TO');
4027: FETCH check_address INTO l_sto_address_rec;
4028: IF check_address%FOUND THEN
4029: IF l_sto_address_rec.status <> 'A' THEN
4030: OKC_API.set_message(
4031: p_app_name => G_APP_NAME,
4032: p_msg_name => G_STO_INVALID,
4033: p_token1 => 'LINE',
4034: p_token1_value => line_rec.line_number);
4033: p_token1 => 'LINE',
4034: p_token1_value => line_rec.line_number);
4035:
4036: -- notify caller of an error
4037: x_return_status := OKC_API.G_RET_STS_ERROR;
4038: ELSE
4039: OPEN check_site (l_sto_address_rec.cust_acct_site_id);
4040: FETCH check_site INTO l_status;
4041: IF check_site%FOUND THEN
4039: OPEN check_site (l_sto_address_rec.cust_acct_site_id);
4040: FETCH check_site INTO l_status;
4041: IF check_site%FOUND THEN
4042: IF l_status <> 'A' THEN
4043: OKC_API.set_message(
4044: p_app_name => G_APP_NAME,
4045: p_msg_name => G_STO_INVALID,
4046: p_token1 => 'LINE',
4047: p_token1_value => line_rec.line_number);
4045: p_msg_name => G_STO_INVALID,
4046: p_token1 => 'LINE',
4047: p_token1_value => line_rec.line_number);
4048: -- notify caller of an error
4049: x_return_status := OKC_API.G_RET_STS_ERROR;
4050: END IF;
4051: END IF;
4052: CLOSE check_site;
4053: END IF; -- If l_sto_address_rec.status <> 'A' THEN
4074:
4075: IF l_site_use_id_tbl(i) IS NULL AND l_use_code_tbl(i) = 'BILL_TO' THEN
4076: -- Check if Bill to/Ship to Address is entered
4077:
4078: OKC_API.set_message(
4079: p_app_name => G_APP_NAME,
4080: p_msg_name => G_BTO_REQUIRED,
4081: p_token1 => 'LINE',
4082: p_token1_value => l_line_number_tbl(i));
4081: p_token1 => 'LINE',
4082: p_token1_value => l_line_number_tbl(i));
4083:
4084: -- notify caller of an error
4085: x_return_status := OKC_API.G_RET_STS_ERROR;
4086:
4087: ELSIF l_site_use_status_tbl(i) <> 'A' THEN
4088:
4089: IF l_use_code_tbl(i) = 'BILL_TO' THEN
4087: ELSIF l_site_use_status_tbl(i) <> 'A' THEN
4088:
4089: IF l_use_code_tbl(i) = 'BILL_TO' THEN
4090: -- If bill to address is entered then check if it is of active status
4091: OKC_API.set_message(
4092: p_app_name => G_APP_NAME,
4093: p_msg_name => G_BTO_INVALID,
4094: p_token1 => 'LINE',
4095: p_token1_value => l_line_number_tbl(i));
4095: p_token1_value => l_line_number_tbl(i));
4096:
4097: ELSIF l_site_use_id_tbl(i) IS NOT NULL AND l_use_code_tbl(i) = 'SHIP_TO' THEN
4098: -- If ship to address is entered then check if it is of active status
4099: OKC_API.set_message(
4100: p_app_name => G_APP_NAME,
4101: p_msg_name => G_STO_INVALID,
4102: p_token1 => 'LINE',
4103: p_token1_value => l_line_number_tbl(i));
4103: p_token1_value => l_line_number_tbl(i));
4104: END IF;
4105:
4106: -- notify caller of an error
4107: x_return_status := OKC_API.G_RET_STS_ERROR;
4108: ELSE
4109: IF l_cust_acct_site_id_tbl(i) IS NOT NULL AND l_site_status_tbl(i) <> 'A' THEN
4110:
4111: IF l_use_code_tbl(i) = 'BILL_TO' THEN
4108: ELSE
4109: IF l_cust_acct_site_id_tbl(i) IS NOT NULL AND l_site_status_tbl(i) <> 'A' THEN
4110:
4111: IF l_use_code_tbl(i) = 'BILL_TO' THEN
4112: OKC_API.set_message(
4113: p_app_name => G_APP_NAME,
4114: p_msg_name => G_BTO_INVALID,
4115: p_token1 => 'LINE',
4116: p_token1_value => l_line_number_tbl(i));
4115: p_token1 => 'LINE',
4116: p_token1_value => l_line_number_tbl(i));
4117:
4118: ELSIF l_use_code_tbl(i) = 'SHIP_TO' THEN
4119: OKC_API.set_message(
4120: p_app_name => G_APP_NAME,
4121: p_msg_name => G_STO_INVALID,
4122: p_token1 => 'LINE',
4123: p_token1_value => l_line_number_tbl(i));
4124: END IF;
4125:
4126:
4127: -- notify caller of an error
4128: x_return_status := OKC_API.G_RET_STS_ERROR;
4129:
4130: END IF;
4131: END IF;
4132:
4135: END LOOP;
4136: CLOSE check_top_line_address;
4137:
4138:
4139: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
4140: THEN
4141: OKC_API.set_message(
4142: p_app_name => G_APP_NAME,
4143: p_msg_name => 'OKS_QA_SUCCESS');
4137:
4138:
4139: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
4140: THEN
4141: OKC_API.set_message(
4142: p_app_name => G_APP_NAME,
4143: p_msg_name => 'OKS_QA_SUCCESS');
4144: END IF;
4145:
4148: -- no processing necessary; validation can continue with next column
4149: NULL;
4150: WHEN OTHERS THEN
4151: -- store SQL error message on message stack
4152: OKC_API.SET_MESSAGE(
4153: p_app_name => G_APP_NAME,
4154: p_msg_name => G_UNEXPECTED_ERROR,
4155: p_token1 => G_SQLCODE_TOKEN,
4156: p_token1_value => SQLCODE,
4156: p_token1_value => SQLCODE,
4157: p_token2 => G_SQLERRM_TOKEN,
4158: p_token2_value => SQLERRM);
4159: -- notify caller of an error as UNEXPETED error
4160: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4161: -- verify that cursor was closed
4162: IF check_address_header%ISOPEN THEN
4163: CLOSE check_address_header;
4164: END IF;
4327:
4328: BEGIN
4329:
4330: -- initialize return status
4331: x_return_status := OKC_API.G_RET_STS_SUCCESS;
4332: -- Get Contract Lines
4333:
4334: OPEN l_org_csr;
4335: FETCH l_org_csr INTO l_org_id, l_organization_id;
4349: CLOSE l_status_csr;
4350:
4351: IF l_status IS NULL THEN
4352: /**
4353: OKC_API.set_message(
4354: p_app_name => G_APP_NAME,
4355: p_msg_name => G_REQUIRED_LINE_VALUE,
4356: p_token1 => G_COL_NAME_TOKEN,
4357: p_token1_value => 'Inventory item status not active',
4358: p_token2 => 'LINE_NAME',
4359: p_token2_value => get_line_name(l_cle_rec.id));
4360: **/
4361:
4362: OKC_API.set_message(
4363: p_app_name => G_APP_NAME,
4364: p_msg_name => G_INACTIVE_INVENTORY_ITEM,
4365: p_token1 => 'LINE_NAME',
4366: p_token1_value => get_line_name(l_cle_rec.id));
4365: p_token1 => 'LINE_NAME',
4366: p_token1_value => get_line_name(l_cle_rec.id));
4367:
4368: -- notify caller of an error
4369: x_return_status := OKC_API.G_RET_STS_ERROR;
4370:
4371: END IF; -- if l_status is NULL
4372: END IF; -- if (l_cle_rec.date_terminated is null )AND (l_cle_rec.sts_code = 'ENTERED')
4373:
4382: OR (l_chk_service_prod_rec.customer_order_enabled_flag = 'N' AND l_chk_service_prod_rec.internal_order_enabled_flag = 'N')
4383: OR l_chk_service_prod_rec.invoice_enabled_flag = 'N'))
4384: THEN
4385:
4386: OKC_API.SET_MESSAGE
4387: (
4388: p_app_name => G_APP_NAME,
4389: p_msg_name => 'OKS_INVALID_SERVICE_ITEM',
4390: p_token1 => 'ITEM',
4389: p_msg_name => 'OKS_INVALID_SERVICE_ITEM',
4390: p_token1 => 'ITEM',
4391: p_token1_value => get_line_name(l_cle_rec.id)
4392: );
4393: x_return_status := OKC_API.G_RET_STS_ERROR;
4394:
4395: END IF; --l_chk_service_prod_rec.service_item_flag = 'Y' and l_chk_service_prod_rec.vendor_warranty_flag = 'N'
4396:
4397: --for USAGE
4400: OR (l_chk_service_prod_rec.customer_order_enabled_flag = 'N' AND l_chk_service_prod_rec.internal_order_enabled_flag = 'N')
4401: OR l_chk_service_prod_rec.invoice_enabled_flag = 'N'))
4402: THEN
4403:
4404: OKC_API.SET_MESSAGE
4405: (
4406: p_app_name => G_APP_NAME,
4407: p_msg_name => 'OKS_INVALID_USAGE_ITEM',
4408: p_token1 => 'ITEM',
4407: p_msg_name => 'OKS_INVALID_USAGE_ITEM',
4408: p_token1 => 'ITEM',
4409: p_token1_value => get_line_name(l_cle_rec.id)
4410: );
4411: x_return_status := OKC_API.G_RET_STS_ERROR;
4412:
4413: END IF; --l_chk_service_prod_rec.usage_item_flag = 'Y'
4414:
4415: CLOSE l_chk_service_prod_csr;
4434: --for product
4435: IF (l_chk_service_prod_rec.serviceable_product_flag = 'N' AND l_cve_rec.lse_id IN (9, 25))
4436: THEN
4437:
4438: OKC_API.SET_MESSAGE
4439: (
4440: p_app_name => G_APP_NAME,
4441: p_msg_name => 'OKS_PRODUCT_INACTIVE',
4442: p_token1 => 'ITEM',
4441: p_msg_name => 'OKS_PRODUCT_INACTIVE',
4442: p_token1 => 'ITEM',
4443: p_token1_value => get_line_name(l_cve_rec.id)
4444: );
4445: x_return_status := OKC_API.G_RET_STS_ERROR;
4446:
4447:
4448: END IF; -- IF (l_chk_service_prod_rec.service_item_flag = 'Y'
4449: CLOSE l_chk_service_prod_csr;
4458: CLOSE l_covitm_csr;
4459:
4460: IF nvl(l_stat, 'X') <> 'A' THEN --Bug 5583158
4461: /**
4462: OKC_API.set_message(
4463: p_app_name => G_APP_NAME,
4464: p_msg_name => G_REQUIRED_LINE_VALUE,
4465: p_token1 => G_COL_NAME_TOKEN,
4466: p_token1_value => 'Covered item status not active',
4467: p_token2 => 'LINE_NAME',
4468: p_token2_value => get_line_name(l_cve_rec.id));
4469: **/
4470:
4471: OKC_API.set_message(
4472: p_app_name => G_APP_NAME,
4473: p_msg_name => G_INACTIVE_COVERED_ITEM,
4474: p_token1 => 'LINE_NAME',
4475: p_token1_value => get_line_name(l_cve_rec.id));
4474: p_token1 => 'LINE_NAME',
4475: p_token1_value => get_line_name(l_cve_rec.id));
4476:
4477: -- notify caller of an error
4478: x_return_status := OKC_API.G_RET_STS_ERROR;
4479:
4480: END IF; -- If l_stat <> 'A' Then
4481: END IF; -- If l_cve_rec.lse_id = 7 Then
4482:
4486: FETCH l_covparty_csr INTO l_stat;
4487: CLOSE l_covparty_csr;
4488: IF nvl(l_stat, 'X') <> 'A' THEN --Bug 5583158
4489: /**
4490: OKC_API.set_message(
4491: p_app_name => G_APP_NAME,
4492: p_msg_name => G_REQUIRED_LINE_VALUE,
4493: p_token1 => G_COL_NAME_TOKEN,
4494: p_token1_value => 'Covered Party status not active',
4495: p_token2 => 'LINE_NAME',
4496: p_token2_value => get_line_name(l_cve_rec.id));
4497: **/
4498:
4499: OKC_API.set_message(
4500: p_app_name => G_APP_NAME,
4501: p_msg_name => G_INACTIVE_COVERED_PARTY,
4502: p_token1 => 'LINE_NAME',
4503: p_token1_value => get_line_name(l_cve_rec.id));
4502: p_token1 => 'LINE_NAME',
4503: p_token1_value => get_line_name(l_cve_rec.id));
4504:
4505: -- notify caller of an error
4506: x_return_status := OKC_API.G_RET_STS_ERROR;
4507:
4508: END IF;
4509: END IF;
4510:
4521:
4522: IF nvl(l_sts_code, 'X') <> 'QA_HOLD' THEN
4523: IF nvl(l_stat, 'X') <> 'Y' THEN --Bug 5583158
4524: /**
4525: OKC_API.set_message(
4526: p_app_name => G_APP_NAME,
4527: p_msg_name => G_REQUIRED_LINE_VALUE,
4528: p_token1 => G_COL_NAME_TOKEN,
4529: p_token1_value => 'Covered Product status not active',
4530: p_token2 => 'LINE_NAME',
4531: p_token2_value => get_line_name(l_cve_rec.id));
4532: **/
4533:
4534: OKC_API.set_message(
4535: p_app_name => G_APP_NAME,
4536: p_msg_name => G_INACTIVE_COVERED_PRODUCT,
4537: p_token1 => 'LINE_NAME',
4538: p_token1_value => get_line_name(l_cve_rec.id));
4537: p_token1 => 'LINE_NAME',
4538: p_token1_value => get_line_name(l_cve_rec.id));
4539:
4540: -- notify caller of an error
4541: x_return_status := OKC_API.G_RET_STS_ERROR;
4542: END IF;
4543: ELSE
4544: IF nvl(l_stat, 'X') <> 'Y' AND --Bug5583158
4545: nvl(l_cve_rec.date_terminated, l_cve_rec.end_date) > nvl(l_end_date, SYSDATE) THEN
4543: ELSE
4544: IF nvl(l_stat, 'X') <> 'Y' AND --Bug5583158
4545: nvl(l_cve_rec.date_terminated, l_cve_rec.end_date) > nvl(l_end_date, SYSDATE) THEN
4546: /**
4547: OKC_API.set_message(
4548: p_app_name => G_APP_NAME,
4549: p_msg_name => G_REQUIRED_LINE_VALUE,
4550: p_token1 => G_COL_NAME_TOKEN,
4551: p_token1_value => 'Covered Product status not active',
4552: p_token2 => 'LINE_NAME',
4553: p_token2_value => get_line_name(l_cve_rec.id));
4554: **/
4555:
4556: OKC_API.set_message(
4557: p_app_name => G_APP_NAME,
4558: p_msg_name => G_INACTIVE_COVERED_PRODUCT,
4559: p_token1 => 'LINE_NAME',
4560: p_token1_value => get_line_name(l_cve_rec.id));
4559: p_token1 => 'LINE_NAME',
4560: p_token1_value => get_line_name(l_cve_rec.id));
4561:
4562: -- notify caller of an error
4563: x_return_status := OKC_API.G_RET_STS_ERROR;
4564: END IF;
4565: END IF;
4566: END IF;
4567:
4573: FETCH l_covsys_csr INTO l_stat;
4574: CLOSE l_covsys_csr;
4575: IF nvl(l_stat, 'X') <> 'A' THEN
4576: /**
4577: OKC_API.set_message(
4578: p_app_name => G_APP_NAME,
4579: p_msg_name => G_REQUIRED_LINE_VALUE,
4580: p_token1 => G_COL_NAME_TOKEN,
4581: p_token1_value => 'Covered System status not active',
4582: p_token2 => 'LINE_NAME',
4583: p_token2_value => get_line_name(l_cve_rec.id));
4584: **/
4585:
4586: OKC_API.set_message(
4587: p_app_name => G_APP_NAME,
4588: p_msg_name => G_INACTIVE_COVERED_SYSTEM,
4589: p_token1 => 'LINE_NAME',
4590: p_token1_value => get_line_name(l_cve_rec.id));
4589: p_token1 => 'LINE_NAME',
4590: p_token1_value => get_line_name(l_cve_rec.id));
4591:
4592: -- notify caller of an error
4593: x_return_status := OKC_API.G_RET_STS_ERROR;
4594:
4595: END IF;
4596: END IF;
4597:
4603: FETCH l_covsit_csr INTO l_stat;
4604: CLOSE l_covsit_csr;
4605: IF nvl(l_stat, 'X') <> 'A' THEN --Bug 5583158
4606: /**
4607: OKC_API.set_message(
4608: p_app_name => G_APP_NAME,
4609: p_msg_name => G_REQUIRED_LINE_VALUE,
4610: p_token1 => G_COL_NAME_TOKEN,
4611: p_token1_value => 'Covered Site status not active',
4612: p_token2 => 'LINE_NAME',
4613: p_token2_value => get_line_name(l_cle_rec.id));
4614: **/
4615:
4616: OKC_API.set_message(
4617: p_app_name => G_APP_NAME,
4618: p_msg_name => G_INACTIVE_COVERED_SITE,
4619: p_token1 => 'LINE_NAME',
4620: p_token1_value => get_line_name(l_cle_rec.id));
4619: p_token1 => 'LINE_NAME',
4620: p_token1_value => get_line_name(l_cle_rec.id));
4621:
4622: -- notify caller of an error
4623: x_return_status := OKC_API.G_RET_STS_ERROR;
4624: END IF;
4625: END IF;
4626: END IF;
4627: END LOOP;
4629: CLOSE l_cve_csr;
4630:
4631: END LOOP;
4632: CLOSE l_cle_csr;
4633: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
4634: OKC_API.set_message(
4635: p_app_name => G_APP_NAME,
4636: p_msg_name => 'OKS_QA_SUCCESS');
4637: END IF;
4630:
4631: END LOOP;
4632: CLOSE l_cle_csr;
4633: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
4634: OKC_API.set_message(
4635: p_app_name => G_APP_NAME,
4636: p_msg_name => 'OKS_QA_SUCCESS');
4637: END IF;
4638:
4641: -- no processing necessary; validation can continue with next column
4642: NULL;
4643: WHEN OTHERS THEN
4644: -- store SQL error message on message stack
4645: OKC_API.SET_MESSAGE(
4646: p_app_name => G_APP_NAME,
4647: p_msg_name => G_UNEXPECTED_ERROR,
4648: p_token1 => G_SQLCODE_TOKEN,
4649: p_token1_value => SQLCODE,
4649: p_token1_value => SQLCODE,
4650: p_token2 => G_SQLERRM_TOKEN,
4651: p_token2_value => SQLERRM);
4652: -- notify caller of an error as UNEXPETED error
4653: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4654: IF l_cve_csr%ISOPEN THEN
4655: CLOSE l_cve_csr;
4656: END IF;
4657:
4826: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_auth_result.auth_code: '|| l_auth_result.auth_code);
4827: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_auth_result.auth_id: '|| l_auth_result.auth_id);
4828: END IF;
4829:
4830: IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
4831: --show IBY error message on standard FND stack in QA results UI
4832: x_return_status := OKC_API.G_RET_STS_ERROR;
4833:
4834:
4828: END IF;
4829:
4830: IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
4831: --show IBY error message on standard FND stack in QA results UI
4832: x_return_status := OKC_API.G_RET_STS_ERROR;
4833:
4834:
4835: --also pick up IBY message in l_response and place onto standard FND message stack
4836: IF NVL(l_response.Result_Category,'x') <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
4846: --delete the 'AUTH_SUCCESS' message put on stack by IBY
4847: FND_MSG_PUB.delete_msg(x_msg_count);
4848:
4849: --put success message to display in QA result list for header
4850: OKC_API.set_message(
4851: p_app_name => G_APP_NAME,
4852: p_msg_name => 'OKS_QA_SUCCESS');
4853:
4854: COMMIT; --the authorization is always committed by IBY regardless so this commit is necessary to retain the record
4893: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Category: '|| l_response.Result_Category);
4894: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Message: '|| l_response.Result_Message);
4895: END IF;
4896:
4897: IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
4898: -- show IBY error message on standard FND stack in QA results UI
4899: x_return_status := OKC_API.G_RET_STS_ERROR;
4900:
4901:
4895: END IF;
4896:
4897: IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
4898: -- show IBY error message on standard FND stack in QA results UI
4899: x_return_status := OKC_API.G_RET_STS_ERROR;
4900:
4901:
4902: --also pick up IBY message in l_response and place onto standard FND message stack
4903: IF NVL(l_response.Result_Category,'x') <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
4913: --delete the 'AUTH_SUCCESS' message put on stack by IBY
4914: FND_MSG_PUB.delete_msg(x_msg_count);
4915:
4916: --put success message to display in QA result list for line
4917: OKC_API.set_message(
4918: p_app_name => G_APP_NAME,
4919: p_msg_name => 'OKS_QA_SUCCESS');
4920:
4921:
4937: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4938: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name,'Entering '|| G_PKG_NAME || '.' || l_api_name);
4939: END IF;
4940:
4941: x_return_status := OKC_API.G_RET_STS_SUCCESS;
4942:
4943:
4944:
4945: -- If OKS: Credit Card Validation Level profile does not hold value 'Authorize Payment', skip check
4954: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Profile is turned off, no need to perform any check');
4955: END IF;
4956:
4957: -- skip the process, No Authorization required
4958: x_return_status := OKC_API.G_RET_STS_SUCCESS;
4959: RAISE G_SUCCESS_HALT_VALIDATION;
4960: END IF;
4961:
4962:
5048: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Message: '|| l_response.Result_Message);
5049: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_auth_result.auth_id: '|| l_auth_result.auth_id);
5050: END IF;
5051:
5052: IF x_return_status = OKC_API.G_RET_STS_SUCCESS AND l_response.Result_Code = IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN
5053: --authorization already exists
5054:
5055: --delete the 'SUCCESS' message put on stack by IBY
5056: FND_MSG_PUB.delete_msg(x_msg_count);
5059: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5060: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Authorization already exists so returning success for header');
5061: END IF;
5062:
5063: OKC_API.set_message(
5064: p_app_name => G_APP_NAME,
5065: p_msg_name => 'OKS_QA_SUCCESS');
5066:
5067:
5075:
5076: create_header_authorization;
5077:
5078: ELSIF NVL(l_response.Result_Category,'x') <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
5079: x_return_status := OKC_API.G_RET_STS_ERROR; --show IBY error message (should be in stack) in QA results UI
5080:
5081: --also pick up IBY message in l_response and place onto standard FND message stack
5082: IF l_response.Result_Category <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
5083: FND_MSG_PUB.add_exc_msg(
5178: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Result_Message: '|| l_response.Result_Message);
5179: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_auth_result.auth_id: '|| l_auth_result.auth_id);
5180: END IF;
5181:
5182: IF x_return_status = OKC_API.G_RET_STS_SUCCESS AND l_response.Result_Code = IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN
5183: --authorization already exists
5184:
5185: --delete the 'SUCCESS' message put on stack by IBY
5186: FND_MSG_PUB.delete_msg(x_msg_count);
5189: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5190: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'Authorization already exists so returning success for line');
5191: END IF;
5192:
5193: OKC_API.set_message(
5194: p_app_name => G_APP_NAME,
5195: p_msg_name => 'OKS_QA_SUCCESS');
5196:
5197:
5212:
5213: ELSIF NVL(l_response.Result_Category,'x') <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
5214:
5215: --show IBY error message (should be in stack) in QA results UI
5216: x_return_status := OKC_API.G_RET_STS_ERROR;
5217:
5218: --also pick up IBY message in l_response and place onto standard FND message stack
5219: IF l_response.Result_Category <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS THEN --'SUCCESS'
5220: FND_MSG_PUB.add_exc_msg(
5263: CLOSE cust_account_csr;
5264: END IF;
5265:
5266: x_return_status := x_return_status;
5267: OKC_API.set_message(
5268: p_app_name => G_APP_NAME,
5269: p_msg_name => 'OKS_QA_SUCCESS');
5270:
5271: WHEN G_ERROR_HALT_VALIDATION THEN
5298: IF cust_account_csr%ISOPEN THEN
5299: CLOSE cust_account_csr;
5300: END IF;
5301:
5302: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5303: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN,'Check_Authorize_Payment:- '|| SQLERRM);
5304:
5305: END Check_Authorize_Payment;
5306:
5299: CLOSE cust_account_csr;
5300: END IF;
5301:
5302: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5303: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR, G_SQLCODE_TOKEN, SQLCODE, G_SQLERRM_TOKEN,'Check_Authorize_Payment:- '|| SQLERRM);
5304:
5305: END Check_Authorize_Payment;
5306:
5307:
5510:
5511: BEGIN
5512:
5513:
5514: x_return_status := OKC_API.G_RET_STS_SUCCESS;
5515: --adding the following code for Bug#4069048
5516: -- get renewal status to see if this is called for Electronic renewal
5517: -- if so bypass the Billing Schedule validation
5518: l_renewal_status := get_renewal_status(p_chr_id);
5545: IF l_top_line_SLL = 0
5546: THEN
5547:
5548: -- store SQL error message on message stack
5549: OKC_API.SET_MESSAGE
5550: (
5551: p_app_name => G_APP_NAME,
5552: p_msg_name => 'OKS_TOPLINE_SLL_NOT_EXISTS',
5553: p_token1 => 'TOKEN1',
5553: p_token1 => 'TOKEN1',
5554: --p_token1_value => top_line_grp_rec.line_number
5555: p_token1_value => get_line_name(top_line_grp_rec.id)
5556: );
5557: x_return_status := OKC_API.G_RET_STS_ERROR;
5558: ELSE
5559:
5560: --l_SLL_end_date := top_line_grp_rec.start_date;
5561: --l_rules_ctr := 0;
5594: IF l_bs_rec = 0
5595: THEN
5596:
5597: -- store SQL error message on message stack
5598: OKC_API.SET_MESSAGE
5599: (
5600: p_app_name => G_APP_NAME,
5601: p_msg_name => 'OKS_LVL_ELEMENT_NOT_EXISTS',
5602: p_token1 => 'TOKEN1',
5602: p_token1 => 'TOKEN1',
5603: --p_token1_value => top_line_grp_rec.line_number
5604: p_token1_value => get_line_name(top_line_grp_rec.id)
5605: );
5606: x_return_status := OKC_API.G_RET_STS_ERROR;
5607:
5608: END IF; --end if of l_bs_rec = 0
5609:
5610:
5620: IF ((rules_rec.level_periods <> level_elements_amt_rec.lvl_count) AND l_ETP_flag <> 'T')
5621: THEN
5622:
5623: -- store SQL error message on message stack
5624: OKC_API.SET_MESSAGE
5625: (
5626: p_app_name => G_APP_NAME,
5627: p_msg_name => 'OKS_SL_LVL_PERIOD_MISMATCH',
5628: p_token1 => 'TOKEN1',
5631: p_token2_value => rules_rec.level_periods, --rule_information3,
5632: p_token3 => 'TOKEN3',
5633: p_token3_value => level_elements_amt_rec.lvl_count
5634: );
5635: x_return_status := OKC_API.G_RET_STS_ERROR;
5636:
5637: END IF;
5638: **/
5639:
5660: oks_extwar_util_pvt.round_currency_amt(NVL(l_lvl_total_amt, 0), l_currency) <>
5661: oks_extwar_util_pvt.round_currency_amt(NVL(top_line_grp_rec.price_negotiated, 0),
5662: l_currency) THEN
5663: */
5664: OKC_API.SET_MESSAGE
5665: (
5666: p_app_name => G_APP_NAME,
5667: p_msg_name => 'OKS_LVL_TOTAL_PRICE_MIS',
5668: p_token1 => 'TOKEN1',
5671: p_token2_value => top_line_grp_rec.price_negotiated,
5672: p_token3 => 'TOKEN3',
5673: p_token3_value => l_lvl_total_amt
5674: );
5675: x_return_status := OKC_API.G_RET_STS_ERROR;
5676: END IF;
5677:
5678:
5679: --Check for Product billing cycle end date equal to lines end date
5681: IF TRUNC(l_SLL_end_date) < TRUNC(top_line_grp_rec.end_date) OR
5682: TRUNC(l_SLL_start_date) <> TRUNC(top_line_grp_rec.start_date)
5683: THEN
5684:
5685: OKC_API.SET_MESSAGE
5686: (
5687: p_app_name => G_APP_NAME,
5688: p_msg_name => 'OKS_TOPLINE_SLL_PERIOD_INVALID',
5689: p_token1 => 'TOKEN1',
5688: p_msg_name => 'OKS_TOPLINE_SLL_PERIOD_INVALID',
5689: p_token1 => 'TOKEN1',
5690: p_token1_value => get_line_name(top_line_grp_rec.id)
5691: );
5692: x_return_status := OKC_API.G_RET_STS_ERROR;
5693: END IF;
5694:
5695: -- It's possible in Top level billing type that the SLL end date > top line end date
5696: IF l_ETP_flag <> 'T' AND TRUNC(l_SLL_end_date) > TRUNC(top_line_grp_rec.end_date)
5695: -- It's possible in Top level billing type that the SLL end date > top line end date
5696: IF l_ETP_flag <> 'T' AND TRUNC(l_SLL_end_date) > TRUNC(top_line_grp_rec.end_date)
5697: THEN
5698:
5699: OKC_API.SET_MESSAGE
5700: (
5701: p_app_name => G_APP_NAME,
5702: p_msg_name => 'OKS_TOPLINE_SLL_PERIOD_INVALID',
5703: p_token1 => 'TOKEN1',
5702: p_msg_name => 'OKS_TOPLINE_SLL_PERIOD_INVALID',
5703: p_token1 => 'TOKEN1',
5704: p_token1_value => get_line_name(top_line_grp_rec.id)
5705: );
5706: x_return_status := OKC_API.G_RET_STS_ERROR;
5707:
5708: END IF;
5709:
5710: END IF; --end if of l_top_line_SLL = 0
5722: OPEN check_lvl_element(top_line_grp_rec.id);
5723: FETCH check_lvl_element INTO l_amount;
5724: IF check_lvl_element%FOUND THEN
5725: -- The billing schedule amount for line number LINE_NO is negative.
5726: OKC_API.SET_MESSAGE
5727: (
5728: p_app_name => G_APP_NAME,
5729: p_msg_name => 'OKS_NEG_LVL_ELEM',
5730: p_token1 => 'LINE_NO',
5729: p_msg_name => 'OKS_NEG_LVL_ELEM',
5730: p_token1 => 'LINE_NO',
5731: p_token1_value => get_line_number(top_line_grp_rec.id)
5732: );
5733: x_return_status := OKC_API.G_RET_STS_ERROR;
5734: END IF;
5735: CLOSE check_lvl_element;
5736: END IF;
5737: -- End modification for bug # 4053552
5754: IF (oks_extwar_util_pvt.round_currency_amt(NVL(l_temp_total_billed, 0), l_currency) >
5755: oks_extwar_util_pvt.round_currency_amt(NVL(l_price_negotiated, 0), l_currency))
5756:
5757: THEN
5758: OKC_API.SET_MESSAGE
5759: (
5760: p_app_name => G_APP_NAME,
5761: p_msg_name => 'OKS_SUBSCR_BILLED_AMT_MISMATCH',
5762: p_token1 => 'TOKEN1',
5765: p_token2_value => nvl(l_line_price, 0),
5766: p_token3 => 'TOKEN3',
5767: p_token3_value => nvl(l_total_billed, 0)
5768: );
5769: x_return_status := OKC_API.G_RET_STS_ERROR;
5770: END IF; -- IF ((oks_extwar_util_pvt.round_currency_amt(NVL(l_lvl_total_billed_amt,0),l_currency) >
5771: END IF; -- nvl(l_total_billed, 0) <> 0
5772: l_temp_total_billed := 0;
5773: END IF;
5794: IF get_ETP_rec.BILLING_SCHEDULE_TYPE <> l_ETP_flag
5795: THEN
5796:
5797: -- store SQL error message on message stack
5798: OKC_API.SET_MESSAGE
5799: (
5800: p_app_name => G_APP_NAME,
5801: p_msg_name => 'OKS_BILLING_FLAG_MISMATCH'
5802: );
5799: (
5800: p_app_name => G_APP_NAME,
5801: p_msg_name => 'OKS_BILLING_FLAG_MISMATCH'
5802: );
5803: x_return_status := OKC_API.G_RET_STS_ERROR;
5804:
5805: END IF; --end if of get_ETP_rec
5806: END LOOP;
5807:
5811: OR TRUNC(top_line_grp_rec.end_date) <> TRUNC(line_grp_rec.end_date))
5812: THEN
5813:
5814: -- store SQL error message on message stack
5815: OKC_API.SET_MESSAGE
5816: (
5817: p_app_name => G_APP_NAME,
5818: p_msg_name => 'OKS_TOP_SUB_LINE_DATE_MISMATCH'
5819: );
5816: (
5817: p_app_name => G_APP_NAME,
5818: p_msg_name => 'OKS_TOP_SUB_LINE_DATE_MISMATCH'
5819: );
5820: x_return_status := OKC_API.G_RET_STS_ERROR;
5821:
5822: END IF; --IF l_ETP_flag = 'E' and top_line_grp_rec.start_date <> line_grp_rec.start_date
5823:
5824: IF l_sub_line_SLL = 0
5824: IF l_sub_line_SLL = 0
5825: THEN
5826:
5827: -- store SQL error message on message stack
5828: OKC_API.SET_MESSAGE
5829: (
5830: p_app_name => G_APP_NAME,
5831: p_msg_name => 'OKS_SUBLINE_SLL_NOT_EXISTS',
5832: p_token1 => 'TOKEN1',
5832: p_token1 => 'TOKEN1',
5833: --p_token1_value => top_line_grp_rec.line_number||'.'||line_grp_rec.line_number
5834: p_token1_value => get_line_name(line_grp_rec.id)
5835: );
5836: x_return_status := OKC_API.G_RET_STS_ERROR;
5837:
5838: ELSE
5839:
5840: OPEN sll_end_date_csr(line_grp_rec.id);
5870: WHERE cle_id = line_grp_rec.id; -- rul_id = rules_rec.id;
5871:
5872: IF l_bs_rec = 0 THEN
5873: -- store SQL error message on message stack
5874: OKC_API.SET_MESSAGE
5875: (
5876: p_app_name => G_APP_NAME,
5877: p_msg_name => 'OKS_LVL_ELEMENT_NOT_EXISTS',
5878: p_token1 => 'TOKEN1',
5878: p_token1 => 'TOKEN1',
5879: --p_token1_value => top_line_grp_rec.line_number||'.'||line_grp_rec.line_number
5880: p_token1_value => get_line_name(line_grp_rec.id)
5881: );
5882: x_return_status := OKC_API.G_RET_STS_ERROR;
5883:
5884: END IF; --end if of l_bs_rec = 0
5885:
5886: --level element validation
5894: IF ((rules_rec.level_periods <> level_elements_amt_rec.lvl_count) AND l_ETP_flag <> 'T')
5895: THEN
5896:
5897: -- store SQL error message on message stack
5898: OKC_API.SET_MESSAGE
5899: (
5900: p_app_name => G_APP_NAME,
5901: p_msg_name => 'OKS_SL_LVL_PERIOD_MISMATCH',
5902: p_token1 => 'TOKEN1',
5905: p_token2_value => rules_rec.level_periods, --rule_information3,
5906: p_token3 => 'TOKEN3',
5907: p_token3_value => level_elements_amt_rec.lvl_count
5908: );
5909: x_return_status := OKC_API.G_RET_STS_ERROR;
5910:
5911: END IF;
5912: **/
5913:
5928: AND line_grp_rec.lse_id <> 13)
5929: THEN
5930:
5931: -- store SQL error message on message stack
5932: OKC_API.SET_MESSAGE
5933: (
5934: p_app_name => G_APP_NAME,
5935: p_msg_name => 'OKS_SL_LVL_TOT_AMT_MISMATCH',
5936: p_token1 => 'TOKEN1',
5939: p_token2_value => line_grp_rec.price_negotiated,
5940: p_token3 => 'TOKEN3',
5941: p_token3_value => l_lvl_total_amt
5942: );
5943: x_return_status := OKC_API.G_RET_STS_ERROR;
5944:
5945: END IF;
5946:
5947: -- Added for bug # 4053552
5950: OPEN check_lvl_element(line_grp_rec.id);
5951: FETCH check_lvl_element INTO l_amount;
5952: IF check_lvl_element%FOUND THEN
5953: -- The billing schedule amount for line number LINE_NO is negative.
5954: OKC_API.SET_MESSAGE
5955: (
5956: p_app_name => G_APP_NAME,
5957: p_msg_name => 'OKS_NEG_LVL_ELEM',
5958: p_token1 => 'LINE_NO',
5957: p_msg_name => 'OKS_NEG_LVL_ELEM',
5958: p_token1 => 'LINE_NO',
5959: p_token1_value => get_line_number(line_grp_rec.id)
5960: );
5961: x_return_status := OKC_API.G_RET_STS_ERROR;
5962: END IF;
5963: CLOSE check_lvl_element;
5964: END IF;
5965: -- End modification for bug # 4053552
5991: IF (oks_extwar_util_pvt.round_currency_amt(NVL(l_temp_total_billed, 0), l_currency) >
5992: oks_extwar_util_pvt.round_currency_amt(NVL(l_price_negotiated, 0), l_currency))
5993:
5994: THEN
5995: OKC_API.SET_MESSAGE
5996: (
5997: p_app_name => G_APP_NAME,
5998: p_msg_name => 'OKS_SL_LVL_BILLED_AMT_MISMATCH',
5999: p_token1 => 'TOKEN1',
6002: p_token2_value => nvl(l_line_price, 0),
6003: p_token3 => 'TOKEN3',
6004: p_token3_value => nvl(l_total_billed, 0)
6005: );
6006: x_return_status := OKC_API.G_RET_STS_ERROR;
6007: END IF; -- IF ((oks_extwar_util_pvt.round_currency_amt(NVL(l_lvl_total_billed_amt,0),l_currency) >
6008: END IF; -- nvl(l_total_billed, 0) <> 0
6009: END IF; -- line_grp_rec.lse_id <> 13
6010: /*
6025: AND line_grp_rec.lse_id <> 13)
6026: THEN
6027:
6028: -- store SQL error message on message stack
6029: OKC_API.SET_MESSAGE
6030: (
6031: p_app_name => G_APP_NAME,
6032: p_msg_name => 'OKS_SL_LVL_BILLED_AMT_MISMATCH',
6033: p_token1 => 'TOKEN1',
6036: p_token2_value => line_grp_rec.price_negotiated,
6037: p_token3 => 'TOKEN3',
6038: p_token3_value => l_lvl_total_billed_amt
6039: );
6040: x_return_status := OKC_API.G_RET_STS_ERROR;
6041:
6042: END IF;
6043: End If; -- if there is a billed amount
6044:
6052: OR
6053: (l_ETP_flag = 'P' AND TRUNC(l_SLL_start_date) <> TRUNC(line_grp_rec.start_date) )
6054: THEN
6055:
6056: OKC_API.SET_MESSAGE
6057: (
6058: p_app_name => G_APP_NAME,
6059: p_msg_name => 'OKS_SUBLINE_SLL_PERIOD_INVALID',
6060: p_token1 => 'TOKEN1',
6059: p_msg_name => 'OKS_SUBLINE_SLL_PERIOD_INVALID',
6060: p_token1 => 'TOKEN1',
6061: p_token1_value => get_line_name(line_grp_rec.id)
6062: );
6063: x_return_status := OKC_API.G_RET_STS_ERROR;
6064: END IF;
6065:
6066:
6067: END IF; --end if of l_sub_line_SLL = 0
6074: oks_extwar_util_pvt.round_currency_amt(NVL(line_grp_rec.price_negotiated,0),l_currency))
6075:
6076: THEN
6077:
6078: OKC_API.SET_MESSAGE
6079: (
6080: p_app_name => G_APP_NAME,
6081: p_msg_name => 'OKS_LVL_TOTAL_PRICE_MIS',
6082: p_token1 => 'TOKEN1',
6085: p_token2_value => line_grp_rec.price_negotiated, -- each negotiated sub line
6086: p_token3 => 'TOKEN3',
6087: p_token3_value => l_lvl_total_amt -- sum of subline level elements
6088: );
6089: x_return_status := OKC_API.G_RET_STS_ERROR;
6090: END IF;
6091: */
6092:
6093: END LOOP; --end loop of line_grp_rec
6095:
6096:
6097: END LOOP; --end loop of top_line_grp_rec
6098: END IF;
6099: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
6100: THEN
6101: OKC_API.set_message(
6102: p_app_name => G_APP_NAME,
6103: p_msg_name => 'OKS_QA_SUCCESS');
6097: END LOOP; --end loop of top_line_grp_rec
6098: END IF;
6099: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
6100: THEN
6101: OKC_API.set_message(
6102: p_app_name => G_APP_NAME,
6103: p_msg_name => 'OKS_QA_SUCCESS');
6104: END IF; -- IF x_return_status = OKC_API.G_RET_STS_SUCCESS
6105:
6100: THEN
6101: OKC_API.set_message(
6102: p_app_name => G_APP_NAME,
6103: p_msg_name => 'OKS_QA_SUCCESS');
6104: END IF; -- IF x_return_status = OKC_API.G_RET_STS_SUCCESS
6105:
6106:
6107: EXCEPTION
6108: WHEN OTHERS THEN
6105:
6106:
6107: EXCEPTION
6108: WHEN OTHERS THEN
6109: x_return_status := OKC_API.G_RET_STS_ERROR;
6110: OKC_API.SET_MESSAGE
6111: (
6112: p_app_name => G_APP_NAME,
6113: p_msg_name => G_UNEXPECTED_ERROR,
6106:
6107: EXCEPTION
6108: WHEN OTHERS THEN
6109: x_return_status := OKC_API.G_RET_STS_ERROR;
6110: OKC_API.SET_MESSAGE
6111: (
6112: p_app_name => G_APP_NAME,
6113: p_msg_name => G_UNEXPECTED_ERROR,
6114: p_token1 => G_SQLCODE_TOKEN,
6214: l_lse_id NUMBER;
6215:
6216: BEGIN
6217:
6218: X_return_status := OKC_API.G_RET_STS_SUCCESS;
6219: /*** Get customer_id ****/
6220:
6221: l_service_id := NULL;
6222: l_sts_code := NULL;
6316: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_available_YN: '|| l_available_YN);
6317: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME || '.' || l_api_name,'l_org_id: '|| get_auth_org_rec.authoring_org_id);
6318: END IF;
6319: IF l_available_yn = 'N' THEN
6320: x_return_status := OKC_API.G_RET_STS_ERROR;
6321:
6322: OKC_API.set_message
6323: (
6324: p_app_name => 'OKS',
6318: END IF;
6319: IF l_available_yn = 'N' THEN
6320: x_return_status := OKC_API.G_RET_STS_ERROR;
6321:
6322: OKC_API.set_message
6323: (
6324: p_app_name => 'OKS',
6325: p_msg_name => 'OKS_PRODUCT_AVAILABILITY',
6326: p_token1 => 'TOKEN1',
6338: ----------------- Product line id loop -------------------------------
6339:
6340: END LOOP; /** End loop get service line id **/
6341: CLOSE l_csr_get_service_line_id;
6342: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
6343: OKC_API.set_message(
6344: p_app_name => G_APP_NAME,
6345: p_msg_name => 'OKS_QA_SUCCESS');
6346: END IF;
6339:
6340: END LOOP; /** End loop get service line id **/
6341: CLOSE l_csr_get_service_line_id;
6342: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
6343: OKC_API.set_message(
6344: p_app_name => G_APP_NAME,
6345: p_msg_name => 'OKS_QA_SUCCESS');
6346: END IF;
6347:
6352: -- no processing necessary; validation can continue with next column
6353: NULL;
6354: WHEN OTHERS THEN
6355: -- store SQL error message on message stack
6356: OKC_API.SET_MESSAGE(
6357: p_app_name => G_APP_NAME,
6358: p_msg_name => G_UNEXPECTED_ERROR,
6359: p_token1 => G_SQLCODE_TOKEN,
6360: p_token1_value => SQLCODE,
6360: p_token1_value => SQLCODE,
6361: p_token2 => G_SQLERRM_TOKEN,
6362: p_token2_value => SQLERRM);
6363: -- notify caller of an error as UNEXPETED error
6364: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6365: -- verify that cursor was closed
6366:
6367:
6368: END Check_product_availability;
6440: AND A.status = 'A'
6441: AND A.org_id = orgId;
6442:
6443: BEGIN
6444: x_return_status := OKC_API.G_RET_STS_SUCCESS;
6445: -- Gets the account name of the party owning the line
6446: OPEN get_cust_id(l_cust_acct_id);
6447: FETCH get_cust_id INTO l_acct_name, l_cust_party_id;
6448: CLOSE get_cust_id;
6479: END LOOP;
6480: -- Bug Fix end 4253417
6481: IF l_rel_cust_flag = 'N' THEN
6482: IF p_usage = 'BTO' THEN
6483: OKC_API.set_message(
6484: p_app_name => G_APP_NAME,
6485: p_msg_name => G_BTO_ACCT,
6486: p_token1 => 'ACCOUNT_NAME',
6487: p_token1_value => l_acct_name,
6487: p_token1_value => l_acct_name,
6488: p_token2 => 'LINE_NUMBER',
6489: p_token2_value => p_line_number);
6490: ELSE
6491: OKC_API.set_message(
6492: p_app_name => G_APP_NAME,
6493: p_msg_name => G_STO_ACCT,
6494: p_token1 => 'ACCOUNT_NAME',
6495: p_token1_value => l_acct_name,
6495: p_token1_value => l_acct_name,
6496: p_token2 => 'LINE_NUMBER',
6497: p_token2_value => p_line_number);
6498: END IF;
6499: x_return_status := OKC_API.G_RET_STS_ERROR;
6500: END IF;
6501:
6502: END IF; -- cust_exist_csr%NOTFOUND
6503: CLOSE cust_exist_csr;
6510: -- no processing necessary; validation can continue with next column
6511: NULL;
6512: WHEN OTHERS THEN
6513: -- store SQL error message on message stack
6514: OKC_API.SET_MESSAGE(
6515: p_app_name => G_APP_NAME,
6516: p_msg_name => G_UNEXPECTED_ERROR,
6517: p_token1 => G_SQLCODE_TOKEN,
6518: p_token1_value => SQLCODE,
6518: p_token1_value => SQLCODE,
6519: p_token2 => G_SQLERRM_TOKEN,
6520: p_token2_value => SQLERRM);
6521: -- notify caller of an error as UNEXPETED error
6522: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6523:
6524: END check_customer_avail_loop;
6525:
6526: /*============================================================================+
6705: l_cust_acct_id NUMBER;
6706: l_line_number VARCHAR2(150);
6707: l_status VARCHAR2(30);
6708: l_name VARCHAR2(360);
6709: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
6710: l_ship_to_cust_acct_id NUMBER;
6711: l_cust_not_found BOOLEAN;
6712:
6713:
6712:
6713:
6714:
6715: BEGIN
6716: x_return_status := OKC_API.G_RET_STS_SUCCESS;
6717:
6718: l_cust_not_found := TRUE;
6719: -- GCHADHA --
6720: -- BUG 4138244 ---
6734: CLOSE is_cust_hdr_active;
6735: l_cust_inactive := 1;
6736: -- Get the customer name
6737: IF get_cust_rec.rle_code = 'CUSTOMER' THEN
6738: OKC_API.set_message(
6739: p_app_name => G_APP_NAME,
6740: p_msg_name => G_CUST_INACTIVE, -- Customer is not acitive.
6741: p_token1 => 'NAME',
6742: p_token1_value => l_name
6740: p_msg_name => G_CUST_INACTIVE, -- Customer is not acitive.
6741: p_token1 => 'NAME',
6742: p_token1_value => l_name
6743: );
6744: x_return_status := OKC_API.G_RET_STS_ERROR;
6745: ELSIF get_cust_rec.rle_code = 'THIRD_PARTY' THEN
6746: OKC_API.set_message(
6747: p_app_name => G_APP_NAME,
6748: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
6742: p_token1_value => l_name
6743: );
6744: x_return_status := OKC_API.G_RET_STS_ERROR;
6745: ELSIF get_cust_rec.rle_code = 'THIRD_PARTY' THEN
6746: OKC_API.set_message(
6747: p_app_name => G_APP_NAME,
6748: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
6749: p_token1 => 'NAME',
6750: p_token1_value => l_name
6748: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
6749: p_token1 => 'NAME',
6750: p_token1_value => l_name
6751: );
6752: x_return_status := OKC_API.G_RET_STS_ERROR;
6753: ELSIF get_cust_rec.rle_code = 'SUBSCRIBER' THEN
6754: OKC_API.set_message(
6755: p_app_name => G_APP_NAME,
6756: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
6750: p_token1_value => l_name
6751: );
6752: x_return_status := OKC_API.G_RET_STS_ERROR;
6753: ELSIF get_cust_rec.rle_code = 'SUBSCRIBER' THEN
6754: OKC_API.set_message(
6755: p_app_name => G_APP_NAME,
6756: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
6757: p_token1 => 'NAME',
6758: p_token1_value => l_name
6756: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
6757: p_token1 => 'NAME',
6758: p_token1_value => l_name
6759: );
6760: x_return_status := OKC_API.G_RET_STS_ERROR;
6761: END IF;
6762:
6763: END IF;
6764: CLOSE Get_Status_Party_Main;
6800: OPEN Get_Status_Party (l_cust_acct_id, l_temp_party_id);
6801: FETCH Get_Status_Party INTO l_cust_acct_id, l_status, l_name;
6802: IF Get_Status_Party%FOUND THEN
6803: IF l_status <> 'A' AND l_bto_flag <> 1 THEN
6804: OKC_API.set_message(
6805: p_app_name => G_APP_NAME,
6806: p_msg_name => G_BILL_CUST_INACTIVE -- Customer is not acitive.
6807:
6808: );
6805: p_app_name => G_APP_NAME,
6806: p_msg_name => G_BILL_CUST_INACTIVE -- Customer is not acitive.
6807:
6808: );
6809: x_return_status := OKC_API.G_RET_STS_ERROR;
6810: END IF;
6811: END IF;
6812: CLOSE Get_Status_Party;
6813:
6841: OPEN Get_Status_Party (l_cust_acct_id, l_temp_party_id);
6842: FETCH Get_Status_Party INTO l_cust_acct_id, l_status, l_name;
6843: IF Get_Status_Party%FOUND THEN
6844: IF l_status <> 'A' AND l_sto_flag <> 2 THEN
6845: OKC_API.set_message(
6846: p_app_name => G_APP_NAME,
6847: p_msg_name => G_SHIP_CUST_INACTIVE -- Bill to Customer Account is not acitive.
6848: );
6849: x_return_status := OKC_API.G_RET_STS_ERROR;
6845: OKC_API.set_message(
6846: p_app_name => G_APP_NAME,
6847: p_msg_name => G_SHIP_CUST_INACTIVE -- Bill to Customer Account is not acitive.
6848: );
6849: x_return_status := OKC_API.G_RET_STS_ERROR;
6850: END IF;
6851: END IF;
6852: CLOSE Get_Status_Party;
6853:
6859: -- billto/shipto account is a related customer and, this customer is
6860: -- inactive.
6861: IF l_flag = 1 THEN
6862: IF l_bto_flag = 1 THEN -- IF THE RELATED CUSTOMER IS IN BILL TO
6863: OKC_API.set_message(
6864: p_app_name => G_APP_NAME,
6865: p_msg_name => G_BILL_CUST_INACTIVE -- Bill To Related Customer is not acitive.
6866: );
6867: x_return_status := OKC_API.G_RET_STS_ERROR;
6863: OKC_API.set_message(
6864: p_app_name => G_APP_NAME,
6865: p_msg_name => G_BILL_CUST_INACTIVE -- Bill To Related Customer is not acitive.
6866: );
6867: x_return_status := OKC_API.G_RET_STS_ERROR;
6868: END IF;
6869: IF l_sto_flag = 2 THEN -- SHIP TO
6870: OKC_API.set_message(
6871: p_app_name => G_APP_NAME,
6866: );
6867: x_return_status := OKC_API.G_RET_STS_ERROR;
6868: END IF;
6869: IF l_sto_flag = 2 THEN -- SHIP TO
6870: OKC_API.set_message(
6871: p_app_name => G_APP_NAME,
6872: p_msg_name => G_SHIP_CUST_INACTIVE -- Ship To Related Customer is not acitive.
6873: );
6874: x_return_status := OKC_API.G_RET_STS_ERROR;
6870: OKC_API.set_message(
6871: p_app_name => G_APP_NAME,
6872: p_msg_name => G_SHIP_CUST_INACTIVE -- Ship To Related Customer is not acitive.
6873: );
6874: x_return_status := OKC_API.G_RET_STS_ERROR;
6875: END IF;
6876: l_sto_flag := 0;
6877: l_bto_flag := 0;
6878: END IF;
6886: Fetch is_cust_hdr_active into l_cust_acct_id, l_status, l_name;
6887: If is_cust_hdr_active%FOUND Then
6888: If l_status <> 'A' Then
6889: If get_cust_rec.rle_code = 'CUSTOMER' Then
6890: OKC_API.set_message(
6891: p_app_name => G_APP_NAME,
6892: p_msg_name => G_CUST_INACTIVE, -- Customer is not acitive.
6893: p_token1 => 'NAME',
6894: p_token1_value => l_name
6892: p_msg_name => G_CUST_INACTIVE, -- Customer is not acitive.
6893: p_token1 => 'NAME',
6894: p_token1_value => l_name
6895: );
6896: x_return_status := OKC_API.G_RET_STS_ERROR;
6897: Elsif get_cust_rec.rle_code = 'THIRD_PARTY' Then
6898: OKC_API.set_message(
6899: p_app_name => G_APP_NAME,
6900: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
6894: p_token1_value => l_name
6895: );
6896: x_return_status := OKC_API.G_RET_STS_ERROR;
6897: Elsif get_cust_rec.rle_code = 'THIRD_PARTY' Then
6898: OKC_API.set_message(
6899: p_app_name => G_APP_NAME,
6900: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
6901: p_token1 => 'NAME',
6902: p_token1_value => l_name
6900: p_msg_name => G_THIRD_PARTY_INACTIVE, -- Third party is not active.
6901: p_token1 => 'NAME',
6902: p_token1_value => l_name
6903: );
6904: x_return_status := OKC_API.G_RET_STS_ERROR;
6905: Elsif get_cust_rec.rle_code = 'SUBSCRIBER' Then
6906: OKC_API.set_message(
6907: p_app_name => G_APP_NAME,
6908: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
6902: p_token1_value => l_name
6903: );
6904: x_return_status := OKC_API.G_RET_STS_ERROR;
6905: Elsif get_cust_rec.rle_code = 'SUBSCRIBER' Then
6906: OKC_API.set_message(
6907: p_app_name => G_APP_NAME,
6908: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
6909: p_token1 => 'NAME',
6910: p_token1_value => l_name
6908: p_msg_name => G_SUB_INACTIVE, -- Subscriber is not active.
6909: p_token1 => 'NAME',
6910: p_token1_value => l_name
6911: );
6912: x_return_status := OKC_API.G_RET_STS_ERROR;
6913: End If;
6914: End If;
6915: End If;
6916: Close is_cust_hdr_active;
6920: -- END BUG 4138244 --
6921:
6922:
6923: IF l_cust_not_found THEN
6924: OKC_API.set_message(
6925: p_app_name => G_APP_NAME,
6926: p_msg_name => G_CUST_MISSING
6927: );
6928: x_return_status := OKC_API.G_RET_STS_ERROR;
6924: OKC_API.set_message(
6925: p_app_name => G_APP_NAME,
6926: p_msg_name => G_CUST_MISSING
6927: );
6928: x_return_status := OKC_API.G_RET_STS_ERROR;
6929: END IF;
6930:
6931: -- Checks bill to and ship to account on each top line
6932: FOR get_cust_rec IN get_cust_acct_lines(p_chr_id) LOOP
6933: l_cust_acct_id := get_cust_rec.cust_acct_id;
6934: OPEN is_cust_active(l_cust_acct_id);
6935: FETCH is_cust_active INTO l_status;
6936: IF is_cust_active%NOTFOUND THEN
6937: OKC_API.set_message(
6938: p_app_name => G_APP_NAME,
6939: p_msg_name => G_CUST_NOT_ACTIVE, --Bill to customer account is inactive on line number ....
6940: p_token1 => 'LINE',
6941: p_token1_value => get_cust_rec.line_number
6939: p_msg_name => G_CUST_NOT_ACTIVE, --Bill to customer account is inactive on line number ....
6940: p_token1 => 'LINE',
6941: p_token1_value => get_cust_rec.line_number
6942: );
6943: x_return_status := OKC_API.G_RET_STS_ERROR;
6944: END IF;
6945: CLOSE is_cust_active;
6946: check_customer_avail_loop(l_return_status, l_cust_acct_id, p_chr_id, get_cust_rec.line_number, 'BTO');
6947: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
6943: x_return_status := OKC_API.G_RET_STS_ERROR;
6944: END IF;
6945: CLOSE is_cust_active;
6946: check_customer_avail_loop(l_return_status, l_cust_acct_id, p_chr_id, get_cust_rec.line_number, 'BTO');
6947: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
6948: x_return_status := l_return_status;
6949: END IF;
6950:
6951: --- Check ship to cust account
6954: IF get_ship_to_acct%FOUND THEN
6955: OPEN is_cust_active(l_ship_to_cust_acct_id);
6956: FETCH is_cust_active INTO l_status;
6957: IF is_cust_active%NOTFOUND THEN
6958: OKC_API.set_message(
6959: p_app_name => G_APP_NAME,
6960: p_msg_name => G_SHIP_CUST_NOT_ACTIVE, --Ship to customer account is inactive on line number ....
6961: p_token1 => 'LINE',
6962: p_token1_value => get_cust_rec.line_number
6960: p_msg_name => G_SHIP_CUST_NOT_ACTIVE, --Ship to customer account is inactive on line number ....
6961: p_token1 => 'LINE',
6962: p_token1_value => get_cust_rec.line_number
6963: );
6964: x_return_status := OKC_API.G_RET_STS_ERROR;
6965: END IF;
6966: CLOSE is_cust_active;
6967: check_customer_avail_loop(l_return_status, l_ship_to_cust_acct_id, p_chr_id, get_cust_rec.line_number, 'STO');
6968: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
6964: x_return_status := OKC_API.G_RET_STS_ERROR;
6965: END IF;
6966: CLOSE is_cust_active;
6967: check_customer_avail_loop(l_return_status, l_ship_to_cust_acct_id, p_chr_id, get_cust_rec.line_number, 'STO');
6968: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
6969: x_return_status := l_return_status;
6970: END IF;
6971: END IF;
6972: CLOSE get_ship_to_acct;
6974:
6975: END LOOP;
6976:
6977:
6978: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
6979: OKC_API.set_message(
6980: p_app_name => G_APP_NAME,
6981: p_msg_name => 'OKS_QA_SUCCESS');
6982: END IF;
6975: END LOOP;
6976:
6977:
6978: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
6979: OKC_API.set_message(
6980: p_app_name => G_APP_NAME,
6981: p_msg_name => 'OKS_QA_SUCCESS');
6982: END IF;
6983:
6986: -- no processing necessary; validation can continue with next column
6987: NULL;
6988: WHEN OTHERS THEN
6989: -- store SQL error message on message stack
6990: OKC_API.SET_MESSAGE(
6991: p_app_name => G_APP_NAME,
6992: p_msg_name => G_UNEXPECTED_ERROR,
6993: p_token1 => G_SQLCODE_TOKEN,
6994: p_token1_value => SQLCODE,
6994: p_token1_value => SQLCODE,
6995: p_token2 => G_SQLERRM_TOKEN,
6996: p_token2_value => SQLERRM);
6997: -- notify caller of an error as UNEXPETED error
6998: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6999: IF get_ship_to_acct%ISOPEN THEN
7000: CLOSE get_ship_to_acct;
7001: END IF;
7002:
7017: x_return_status OUT NOCOPY VARCHAR2,
7018: p_chr_id IN NUMBER
7019: )
7020: IS
7021: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7022:
7023: BEGIN
7024: -- initialize return status
7025: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7021: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7022:
7023: BEGIN
7024: -- initialize return status
7025: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7026:
7027: OKS_PM_PROGRAMS_PVT.check_pm_program_effectivity
7028: (x_return_status => l_return_status,
7029: p_chr_id => p_chr_id);
7028: (x_return_status => l_return_status,
7029: p_chr_id => p_chr_id);
7030:
7031:
7032: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7033: x_return_status := l_return_status;
7034: END IF;
7035:
7036: OKS_PM_PROGRAMS_PVT.check_pm_schedule
7036: OKS_PM_PROGRAMS_PVT.check_pm_schedule
7037: (x_return_status => l_return_status,
7038: p_chr_id => p_chr_id);
7039:
7040: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7041: x_return_status := l_return_status;
7042: END IF;
7043:
7044: OKS_PM_PROGRAMS_PVT.check_pm_new_activities
7044: OKS_PM_PROGRAMS_PVT.check_pm_new_activities
7045: (x_return_status => l_return_status,
7046: p_chr_id => p_chr_id);
7047:
7048: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7049: x_return_status := l_return_status;
7050: END IF;
7051:
7052: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7048: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
7049: x_return_status := l_return_status;
7050: END IF;
7051:
7052: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7053: OKC_API.set_message(
7054: p_app_name => G_APP_NAME,
7055: p_msg_name => 'OKS_QA_SUCCESS');
7056: END IF;
7049: x_return_status := l_return_status;
7050: END IF;
7051:
7052: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7053: OKC_API.set_message(
7054: p_app_name => G_APP_NAME,
7055: p_msg_name => 'OKS_QA_SUCCESS');
7056: END IF;
7057:
7059: WHEN G_EXCEPTION_HALT_VALIDATION THEN
7060: NULL;
7061: WHEN OTHERS THEN
7062: -- store SQL error message on message stack
7063: OKC_API.SET_MESSAGE(
7064: p_app_name => G_APP_NAME,
7065: p_msg_name => G_UNEXPECTED_ERROR,
7066: p_token1 => G_SQLCODE_TOKEN,
7067: p_token1_value => SQLCODE,
7067: p_token1_value => SQLCODE,
7068: p_token2 => G_SQLERRM_TOKEN,
7069: p_token2_value => SQLERRM);
7070: -- notify caller of an error as UNEXPETED error
7071: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7072: END check_pm;
7073:
7074: /*============================================================================+
7075: | Procedure: Check_item_instance_valid
7127: l_valid_status VARCHAR2(30);
7128: l_valid_dates VARCHAR2(30);
7129:
7130: BEGIN
7131: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7132: -- Gets the sublines with covered products for p_chr_id
7133: FOR get_cp_rec IN get_cp_lines LOOP
7134: -- Gets the item instance
7135: OPEN get_item_inst(get_cp_rec.subline_id);
7137: IF get_item_inst%FOUND THEN
7138: OPEN get_subscr(l_inst_id);
7139: FETCH get_subscr INTO l_subscr_chr_id, l_subscr_cle_id;
7140: IF get_subscr%NOTFOUND THEN
7141: OKC_API.set_message(
7142: p_app_name => G_APP_NAME,
7143: p_msg_name => G_ITEM_INST_INVALID,
7144: p_token1 => 'LINE_NAME',
7145: p_token1_value => get_line_name(get_cp_rec.subline_id));
7142: p_app_name => G_APP_NAME,
7143: p_msg_name => G_ITEM_INST_INVALID,
7144: p_token1 => 'LINE_NAME',
7145: p_token1_value => get_line_name(get_cp_rec.subline_id));
7146: x_return_status := OKC_API.G_RET_STS_ERROR;
7147: END IF;
7148: CLOSE get_subscr;
7149: END IF; -- get_item_inst%FOUND
7150: CLOSE get_item_inst;
7149: END IF; -- get_item_inst%FOUND
7150: CLOSE get_item_inst;
7151: END LOOP;
7152:
7153: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7154: OKC_API.set_message(
7155: p_app_name => G_APP_NAME,
7156: p_msg_name => 'OKS_QA_SUCCESS');
7157: END IF;
7150: CLOSE get_item_inst;
7151: END LOOP;
7152:
7153: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7154: OKC_API.set_message(
7155: p_app_name => G_APP_NAME,
7156: p_msg_name => 'OKS_QA_SUCCESS');
7157: END IF;
7158:
7164: IF get_item_inst%ISOPEN THEN
7165: CLOSE get_item_inst;
7166: END IF;
7167: -- store SQL error message on message stack
7168: OKC_API.SET_MESSAGE(
7169: p_app_name => G_APP_NAME,
7170: p_msg_name => G_UNEXPECTED_ERROR,
7171: p_token1 => G_SQLCODE_TOKEN,
7172: p_token1_value => SQLCODE,
7172: p_token1_value => SQLCODE,
7173: p_token2 => G_SQLERRM_TOKEN,
7174: p_token2_value => SQLERRM);
7175: -- notify caller of an error as UNEXPETED error
7176: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7177:
7178: END Check_item_instance_valid;
7179:
7180: /*============================================================================+
7216: l_sh_id NUMBER;
7217: l_subscr_ele_id NUMBER;
7218:
7219: BEGIN
7220: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7221: FOR subscr_line_rec IN get_subscr_line LOOP
7222: OPEN is_tangible_item(subscr_line_rec.id);
7223: FETCH is_tangible_item INTO l_sh_id;
7224: IF is_tangible_item%FOUND THEN
7226: FETCH get_subscr_element INTO l_subscr_ele_id;
7227: --Bug 5702660. Added condition "subscr_line_rec.date_terminated is NULL"
7228: IF get_subscr_element%NOTFOUND and subscr_line_rec.date_terminated is NULL
7229: THEN
7230: OKC_API.set_message(
7231: p_app_name => G_APP_NAME,
7232: p_msg_name => G_SUBSCR_ELEM_MISS,
7233: p_token1 => 'LINE_NAME',
7234: p_token1_value => get_line_name(subscr_line_rec.id));
7231: p_app_name => G_APP_NAME,
7232: p_msg_name => G_SUBSCR_ELEM_MISS,
7233: p_token1 => 'LINE_NAME',
7234: p_token1_value => get_line_name(subscr_line_rec.id));
7235: x_return_status := OKC_API.G_RET_STS_ERROR;
7236: END IF;
7237: CLOSE get_subscr_element;
7238: END IF;
7239: CLOSE is_tangible_item;
7238: END IF;
7239: CLOSE is_tangible_item;
7240: END LOOP;
7241:
7242: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7243: OKC_API.set_message(
7244: p_app_name => G_APP_NAME,
7245: p_msg_name => 'OKS_QA_SUCCESS');
7246: END IF;
7239: CLOSE is_tangible_item;
7240: END LOOP;
7241:
7242: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7243: OKC_API.set_message(
7244: p_app_name => G_APP_NAME,
7245: p_msg_name => 'OKS_QA_SUCCESS');
7246: END IF;
7247:
7253: IF is_tangible_item%ISOPEN THEN
7254: CLOSE is_tangible_item;
7255: END IF;
7256: -- store SQL error message on message stack
7257: OKC_API.SET_MESSAGE(
7258: p_app_name => G_APP_NAME,
7259: p_msg_name => G_UNEXPECTED_ERROR,
7260: p_token1 => G_SQLCODE_TOKEN,
7261: p_token1_value => SQLCODE,
7260: p_token1 => G_SQLCODE_TOKEN,
7261: p_token1_value => SQLCODE,
7262: p_token2 => G_SQLERRM_TOKEN,
7263: p_token2_value => SQLERRM);
7264: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7265:
7266:
7267: END check_subscr_element_exist;
7268:
7316: l_ship_to_id NUMBER;
7317: l_location_id NUMBER;
7318:
7319: BEGIN
7320: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7321: -- Loops through each subscription line and checks if it's items are shipable.
7322: FOR subscr_line_rec IN get_subscr_lines LOOP
7323: OPEN get_shipable_flag(subscr_line_rec.id);
7324: FETCH get_shipable_flag INTO l_shipable;
7326: -- since the subscription item is shipable it has to have a ship to rule.
7327: OPEN get_ship_to_rule(subscr_line_rec.id);
7328: FETCH get_ship_to_rule INTO l_ship_to_id;
7329: IF get_ship_to_rule%NOTFOUND THEN
7330: OKC_API.set_message(
7331: p_app_name => G_APP_NAME,
7332: p_msg_name => G_SHIP_RULE_MISS,
7333: p_token1 => 'LINE_NAME',
7334: p_token1_value => get_line_name(subscr_line_rec.id));
7331: p_app_name => G_APP_NAME,
7332: p_msg_name => G_SHIP_RULE_MISS,
7333: p_token1 => 'LINE_NAME',
7334: p_token1_value => get_line_name(subscr_line_rec.id));
7335: x_return_status := OKC_API.G_RET_STS_ERROR;
7336: ELSE
7337: OPEN ship_to_address(l_ship_to_id, 'SHIP_TO');
7338: FETCH ship_to_address INTO l_location_id;
7339: IF ship_to_address%NOTFOUND THEN
7336: ELSE
7337: OPEN ship_to_address(l_ship_to_id, 'SHIP_TO');
7338: FETCH ship_to_address INTO l_location_id;
7339: IF ship_to_address%NOTFOUND THEN
7340: OKC_API.set_message(
7341: p_app_name => G_APP_NAME,
7342: p_msg_name => G_SHIP_ADDR_MISS,
7343: p_token1 => 'LINE_NAME',
7344: p_token1_value => get_line_name(subscr_line_rec.id));
7341: p_app_name => G_APP_NAME,
7342: p_msg_name => G_SHIP_ADDR_MISS,
7343: p_token1 => 'LINE_NAME',
7344: p_token1_value => get_line_name(subscr_line_rec.id));
7345: x_return_status := OKC_API.G_RET_STS_ERROR;
7346: END IF;
7347: CLOSE ship_to_address;
7348: END IF; -- get_ship_to_rule%NOTFOUND
7349: CLOSE get_ship_to_rule;
7350: END IF; -- get_shipable_flag%FOUND
7351: CLOSE get_shipable_flag;
7352: END LOOP;
7353:
7354: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7355: OKC_API.set_message(
7356: p_app_name => G_APP_NAME,
7357: p_msg_name => 'OKS_QA_SUCCESS');
7358: END IF;
7351: CLOSE get_shipable_flag;
7352: END LOOP;
7353:
7354: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7355: OKC_API.set_message(
7356: p_app_name => G_APP_NAME,
7357: p_msg_name => 'OKS_QA_SUCCESS');
7358: END IF;
7359:
7369: CLOSE ship_to_address;
7370: END IF;
7371:
7372: -- store SQL error message on message stack
7373: OKC_API.SET_MESSAGE(
7374: p_app_name => G_APP_NAME,
7375: p_msg_name => G_UNEXPECTED_ERROR,
7376: p_token1 => G_SQLCODE_TOKEN,
7377: p_token1_value => SQLCODE,
7376: p_token1 => G_SQLCODE_TOKEN,
7377: p_token1_value => SQLCODE,
7378: p_token2 => G_SQLERRM_TOKEN,
7379: p_token2_value => SQLERRM);
7380: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7381:
7382: END check_subscr_is_shipable;
7383:
7384: /*============================================================================+
7425: l_exact_item_inst Exact_Item_Inst%ROWTYPE;
7426:
7427:
7428: BEGIN
7429: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7430: -- This will loop through all the covered product.
7431: -- A contract can have several covered products
7432: FOR get_cp_rec IN Get_Covered_Prod(p_chr_id) LOOP
7433: -- Will get the item_instace
7443: OPEN Exact_Item_Inst(l_item_inst.object1_id1);
7444: FETCH Exact_Item_Inst INTO l_exact_item_inst;
7445: IF Exact_Item_Inst%NOTFOUND THEN
7446: -- G_ITEM_INST_MISS = Item instance ITEM_INST for line LINE_NAME is not found in installedbase.
7447: OKC_API.set_message(
7448: p_app_name => G_APP_NAME,
7449: p_msg_name => G_ITEM_INST_MISS,
7450: p_token1 => 'ITEM_INST',
7451: p_token1_value => l_item_inst.object1_id1,
7450: p_token1 => 'ITEM_INST',
7451: p_token1_value => l_item_inst.object1_id1,
7452: p_token2 => 'LINE_NAME',
7453: p_token2_value => get_line_name(get_cp_rec.id));
7454: x_return_status := OKC_API.G_RET_STS_ERROR;
7455: ELSE
7456: IF l_exact_item_inst.quantity <> l_item_inst.number_of_items
7457: AND l_exact_item_inst.unit_of_measure <> l_item_inst.uom_code THEN
7458: OKC_API.set_message(
7454: x_return_status := OKC_API.G_RET_STS_ERROR;
7455: ELSE
7456: IF l_exact_item_inst.quantity <> l_item_inst.number_of_items
7457: AND l_exact_item_inst.unit_of_measure <> l_item_inst.uom_code THEN
7458: OKC_API.set_message(
7459: p_app_name => G_APP_NAME,
7460: p_msg_name => G_QUANT_UOM_INVALID,
7461: p_token1 => 'LINE_NAME',
7462: p_token1_value => get_line_name(get_cp_rec.id),
7464: p_token2_value => l_exact_item_inst.quantity,
7465: p_token3 => 'UOM',
7466: p_token3_value => l_exact_item_inst.unit_of_measure);
7467:
7468: x_return_status := OKC_API.G_RET_STS_ERROR;
7469: ELSIF l_exact_item_inst.quantity <> l_item_inst.number_of_items THEN
7470: OKC_API.set_message(
7471: p_app_name => G_APP_NAME,
7472: p_msg_name => G_QUANT_INVALID,
7466: p_token3_value => l_exact_item_inst.unit_of_measure);
7467:
7468: x_return_status := OKC_API.G_RET_STS_ERROR;
7469: ELSIF l_exact_item_inst.quantity <> l_item_inst.number_of_items THEN
7470: OKC_API.set_message(
7471: p_app_name => G_APP_NAME,
7472: p_msg_name => G_QUANT_INVALID,
7473: p_token1 => 'LINE_NAME',
7474: p_token1_value => get_line_name(get_cp_rec.id),
7474: p_token1_value => get_line_name(get_cp_rec.id),
7475: p_token2 => 'QUANTITY',
7476: p_token2_value => l_exact_item_inst.quantity);
7477:
7478: x_return_status := OKC_API.G_RET_STS_ERROR;
7479: ELSIF l_exact_item_inst.unit_of_measure <> l_item_inst.uom_code THEN
7480: -- G_UOM_INVALID: Covered product unit of measure on line LINE_NAME does not match the value UOM stored in installedbase.
7481: OKC_API.set_message(
7482: p_app_name => G_APP_NAME,
7477:
7478: x_return_status := OKC_API.G_RET_STS_ERROR;
7479: ELSIF l_exact_item_inst.unit_of_measure <> l_item_inst.uom_code THEN
7480: -- G_UOM_INVALID: Covered product unit of measure on line LINE_NAME does not match the value UOM stored in installedbase.
7481: OKC_API.set_message(
7482: p_app_name => G_APP_NAME,
7483: p_msg_name => G_UOM_INVALID,
7484: p_token1 => 'LINE_NAME',
7485: p_token1_value => get_line_name(get_cp_rec.id),
7485: p_token1_value => get_line_name(get_cp_rec.id),
7486: p_token2 => 'UOM',
7487: p_token2_value => l_exact_item_inst.unit_of_measure);
7488:
7489: x_return_status := OKC_API.G_RET_STS_ERROR;
7490: END IF;
7491: END IF;
7492: CLOSE Exact_Item_Inst;
7493: END IF;
7494: CLOSE is_subscr_item;
7495:
7496: END LOOP;
7497:
7498: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
7499: THEN
7500: OKC_API.set_message(
7501: p_app_name => G_APP_NAME,
7502: p_msg_name => 'OKS_QA_SUCCESS');
7496: END LOOP;
7497:
7498: IF x_return_status = OKC_API.G_RET_STS_SUCCESS
7499: THEN
7500: OKC_API.set_message(
7501: p_app_name => G_APP_NAME,
7502: p_msg_name => 'OKS_QA_SUCCESS');
7503: END IF;
7504:
7504:
7505: EXCEPTION
7506: WHEN OTHERS THEN
7507: -- store SQL error message on message stack
7508: OKC_API.SET_MESSAGE(
7509: p_app_name => G_APP_NAME,
7510: p_msg_name => G_UNEXPECTED_ERROR,
7511: p_token1 => G_SQLCODE_TOKEN,
7512: p_token1_value => SQLCODE,
7511: p_token1 => G_SQLCODE_TOKEN,
7512: p_token1_value => SQLCODE,
7513: p_token2 => G_SQLERRM_TOKEN,
7514: p_token2_value => SQLERRM);
7515: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7516:
7517: IF Exact_Item_Inst%ISOPEN THEN
7518: CLOSE Exact_Item_Inst;
7519: END IF;
7534: +============================================================================*/
7535: PROCEDURE check_required_PM(p_chr_id IN NUMBER,
7536: x_return_status OUT NOCOPY VARCHAR2)
7537: IS
7538: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7539: BEGIN
7540: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7541: OKS_PM_PROGRAMS_PVT.CHECK_PM_REQUIRED_VALUES(x_return_status => l_return_status,
7542: p_chr_id => p_chr_id);
7536: x_return_status OUT NOCOPY VARCHAR2)
7537: IS
7538: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7539: BEGIN
7540: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7541: OKS_PM_PROGRAMS_PVT.CHECK_PM_REQUIRED_VALUES(x_return_status => l_return_status,
7542: p_chr_id => p_chr_id);
7543:
7544: x_return_status := l_return_status;
7543:
7544: x_return_status := l_return_status;
7545:
7546:
7547: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7548: OKC_API.set_message(
7549: p_app_name => G_APP_NAME,
7550: p_msg_name => 'OKS_QA_SUCCESS');
7551: END IF;
7544: x_return_status := l_return_status;
7545:
7546:
7547: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7548: OKC_API.set_message(
7549: p_app_name => G_APP_NAME,
7550: p_msg_name => 'OKS_QA_SUCCESS');
7551: END IF;
7552:
7552:
7553: EXCEPTION
7554: WHEN OTHERS THEN
7555: -- store SQL error message on message stack
7556: OKC_API.SET_MESSAGE(
7557: p_app_name => G_APP_NAME,
7558: p_msg_name => G_UNEXPECTED_ERROR,
7559: p_token1 => G_SQLCODE_TOKEN,
7560: p_token1_value => SQLCODE,
7560: p_token1_value => SQLCODE,
7561: p_token2 => G_SQLERRM_TOKEN,
7562: p_token2_value => SQLERRM);
7563:
7564: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7565:
7566: END check_required_PM;
7567:
7568: /*============================================================================+
7616: --and prorate is not null; -- prorate is not mandatory
7617:
7618: l_old_chr_id NUMBER;
7619: l_id NUMBER;
7620: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7621:
7622: BEGIN
7623: l_return_status := OKC_API.G_RET_STS_SUCCESS;
7624:
7619: l_id NUMBER;
7620: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
7621:
7622: BEGIN
7623: l_return_status := OKC_API.G_RET_STS_SUCCESS;
7624:
7625: OPEN get_old_chr_id(p_chr_id);
7626: FETCH get_old_chr_id INTO l_old_chr_id;
7627: CLOSE get_old_chr_id;
7632: LOOP
7633: OPEN check_lock(p_chr_id, get_new_id_rec.id);
7634: FETCH check_lock INTO l_id;
7635: IF check_lock%NOTFOUND THEN
7636: OKC_API.set_message(
7637: p_app_name => G_APP_NAME,
7638: p_msg_name => G_MISS_PRICE_LOCK,
7639: p_token1 => 'NEW_LINE',
7640: p_token1_value => get_line_number(get_new_id_rec.id),
7642: p_token2_value => get_contract_name(l_old_chr_id),
7643: p_token3 => 'OLD_LINE',
7644: p_token3_value => get_line_number(get_old_id_rec.cle_id)
7645: );
7646: l_return_status := OKC_API.G_RET_STS_ERROR;
7647: END IF;
7648: CLOSE check_lock;
7649: END LOOP;
7650:
7650:
7651: END LOOP;
7652: x_return_status := l_return_status;
7653:
7654: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7655: OKC_API.set_message(
7656: p_app_name => G_APP_NAME,
7657: p_msg_name => 'OKS_QA_SUCCESS');
7658: END IF;
7651: END LOOP;
7652: x_return_status := l_return_status;
7653:
7654: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7655: OKC_API.set_message(
7656: p_app_name => G_APP_NAME,
7657: p_msg_name => 'OKS_QA_SUCCESS');
7658: END IF;
7659:
7659:
7660: EXCEPTION
7661: WHEN OTHERS THEN
7662: -- store SQL error message on message stack
7663: OKC_API.SET_MESSAGE(
7664: p_app_name => G_APP_NAME,
7665: p_msg_name => G_UNEXPECTED_ERROR,
7666: p_token1 => G_SQLCODE_TOKEN,
7667: p_token1_value => SQLCODE,
7667: p_token1_value => SQLCODE,
7668: p_token2 => G_SQLERRM_TOKEN,
7669: p_token2_value => SQLERRM);
7670:
7671: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7672:
7673:
7674: END check_pirce_lock;
7675:
7710:
7711:
7712: BEGIN
7713:
7714: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7715:
7716: OPEN cur_renewal_source_lines (p_chr_id);
7717: FETCH cur_renewal_source_lines INTO l_dummy_data;
7718:
7717: FETCH cur_renewal_source_lines INTO l_dummy_data;
7718:
7719: IF cur_renewal_source_lines%FOUND THEN
7720:
7721: OKC_API.set_message(
7722: p_app_name => G_APP_NAME,
7723: p_msg_name => 'OKS_CHECK_REN_SOURCE_LINES'
7724: );
7725: -- notify caller of an error
7722: p_app_name => G_APP_NAME,
7723: p_msg_name => 'OKS_CHECK_REN_SOURCE_LINES'
7724: );
7725: -- notify caller of an error
7726: x_return_status := OKC_API.G_RET_STS_ERROR;
7727:
7728: END IF;
7729:
7730: CLOSE cur_renewal_source_lines;
7728: END IF;
7729:
7730: CLOSE cur_renewal_source_lines;
7731:
7732: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7733: OKC_API.set_message(
7734: p_app_name => G_APP_NAME,
7735: p_msg_name => 'OKS_QA_SUCCESS');
7736: END IF;
7729:
7730: CLOSE cur_renewal_source_lines;
7731:
7732: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7733: OKC_API.set_message(
7734: p_app_name => G_APP_NAME,
7735: p_msg_name => 'OKS_QA_SUCCESS');
7736: END IF;
7737:
7737:
7738: EXCEPTION
7739: WHEN OTHERS THEN
7740: -- store SQL error message on message stack
7741: OKC_API.SET_MESSAGE(
7742: p_app_name => G_APP_NAME,
7743: p_msg_name => G_UNEXPECTED_ERROR,
7744: p_token1 => G_SQLCODE_TOKEN,
7745: p_token1_value => SQLCODE,
7745: p_token1_value => SQLCODE,
7746: p_token2 => G_SQLERRM_TOKEN,
7747: p_token2_value => SQLERRM);
7748:
7749: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7750:
7751: END Check_Ren_Source_Lines;
7752:
7753:
7846:
7847:
7848: BEGIN
7849:
7850: x_return_status := OKC_API.G_RET_STS_SUCCESS;
7851:
7852: OPEN cur_is_K_renewed;
7853: FETCH cur_is_K_renewed INTO l_target_contract_id;
7854:
7870: OPEN cur_target_line_number(l_subject_cle_id);
7871: FETCH cur_target_line_number INTO l_target_line_number;
7872: CLOSE cur_target_line_number;
7873:
7874: OKC_API.SET_MESSAGE
7875: (
7876: p_app_name => G_APP_NAME,
7877: p_msg_name => 'OKS_CHECK_REN_TARGET_LINES',
7878: p_token1 => 'CURRENT_LINE_NUM',
7882: p_token3 => 'TARGET_CONTRACT_NUM',
7883: p_token3_value => l_target_contract_number
7884: );
7885:
7886: x_return_status := OKC_API.G_RET_STS_ERROR;
7887:
7888: END IF; --cur_is_topline_renewed%FOUND
7889: CLOSE cur_is_topline_renewed;
7890:
7914: FETCH cur_target_subline_number INTO l_target_subline_number;
7915: CLOSE cur_target_subline_number;
7916:
7917:
7918: OKC_API.SET_MESSAGE
7919: (
7920: p_app_name => G_APP_NAME,
7921: p_msg_name => 'OKS_CHECK_REN_TARGET_LINES',
7922: p_token1 => 'CURRENT_LINE_NUM',
7926: p_token3 => 'TARGET_CONTRACT_NUM',
7927: p_token3_value => l_target_contract_number
7928: );
7929:
7930: x_return_status := OKC_API.G_RET_STS_ERROR;
7931:
7932: END IF;
7933: CLOSE cur_is_subline_renewed;
7934:
7942:
7943: END IF; --cur_is_K_renewed %FOUND
7944: CLOSE cur_is_K_renewed;
7945:
7946: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7947: OKC_API.set_message(
7948: p_app_name => G_APP_NAME,
7949: p_msg_name => 'OKS_QA_SUCCESS');
7950:
7943: END IF; --cur_is_K_renewed %FOUND
7944: CLOSE cur_is_K_renewed;
7945:
7946: IF x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
7947: OKC_API.set_message(
7948: p_app_name => G_APP_NAME,
7949: p_msg_name => 'OKS_QA_SUCCESS');
7950:
7951: END IF;
7952:
7953: EXCEPTION
7954: WHEN OTHERS THEN
7955: -- store SQL error message on message stack
7956: OKC_API.SET_MESSAGE(
7957: p_app_name => G_APP_NAME,
7958: p_msg_name => G_UNEXPECTED_ERROR,
7959: p_token1 => G_SQLCODE_TOKEN,
7960: p_token1_value => SQLCODE,
7960: p_token1_value => SQLCODE,
7961: p_token2 => G_SQLERRM_TOKEN,
7962: p_token2_value => SQLERRM);
7963:
7964: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
7965:
7966: END Check_Ren_Target_Lines;
7967:
7968: