[Home] [Help]
105: PROCEDURE log_messages(log_msg_flag IN VARCHAR2 DEFAULT '',
106: p_transaction_number IN VARCHAR2 DEFAULT '',
107: p_contract_number IN VARCHAR2 DEFAULT '',
108: p_asset_number IN VARCHAR2 DEFAULT '',
109: p_date_effective IN DATE DEFAULT OKC_API.G_MISS_DATE,
110: p_quote_type IN VARCHAR2 DEFAULT '',
111: p_quote_reason IN VARCHAR2 DEFAULT '',
112: p_quote_number IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
113: msg_tbl IN msg_tbl_type
108: p_asset_number IN VARCHAR2 DEFAULT '',
109: p_date_effective IN DATE DEFAULT OKC_API.G_MISS_DATE,
110: p_quote_type IN VARCHAR2 DEFAULT '',
111: p_quote_reason IN VARCHAR2 DEFAULT '',
112: p_quote_number IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
113: msg_tbl IN msg_tbl_type
114: ) IS
115: val_count NUMBER := 0;
116: err_count NUMBER := 0;
472: END IF;
473: EXCEPTION
474: WHEN OTHERS THEN
475: -- store SQL error message on message stack for caller
476: OKC_API.set_message(p_app_name => g_app_name,
477: p_msg_name => g_unexpected_error,
478: p_token1 => g_sqlcode_token,
479: p_token1_value => sqlcode,
480: p_token2 => g_sqlerrm_token,
534: IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
535: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_quote_type_reason','Begin(+)');
536: END IF;
537:
538: x_return_status := OKC_API.G_RET_STS_SUCCESS;
539: lp_tif_tbl := p_tif_tbl;
540: IF(lp_tif_tbl.COUNT>0) THEN
541:
542:
572: LOOP
573: IF(lp_tif_tbl(record_number).quote_type_code = l_quote_type) THEN
574: -- quote type entered is invalid
575: lp_tif_tbl(record_number).status := 'ERROR';
576: OKC_API.set_message(p_app_name => g_app_name,
577: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
578: p_token1 => 'COL_NAME',
579: p_token1_value => 'QUOTE_TYPE_CODE');
580: l_msg_tbl(0).msg_text := 'quote type: '||l_quote_type||' entered for transaction number: '||lp_tif_tbl(record_number).transaction_number ||' is not valid ';
596: LOOP
597: IF(lp_tif_tbl(record_number).quote_type_code LIKE 'TER_MAN%') THEN
598: --manual quotes are not allowed
599: lp_tif_tbl(record_number).status := 'ERROR';
600: OKC_API.set_message(p_app_name => g_app_name,
601: p_msg_name => 'OKL_AM_MAN_QUOTE_TYPE',
602: p_token1 => 'TRANSACTION NUMBER',
603: p_token1_value => lp_tif_tbl(record_number).transaction_number);
604: l_msg_tbl(0).msg_text := 'manual quotes not allowed for transaction number '||lp_tif_tbl(record_number).transaction_number;
614: ELSIF(lp_tif_tbl(record_number).quote_type_code = 'TER_RELASE_WO_PURCHASE')
615: THEN
616: -- Release quotes are not allowed
617: lp_tif_tbl(record_number).status := 'ERROR';
618: OKC_API.set_message(p_app_name => g_app_name,
619: p_msg_name => 'OKL_AM_TER_INTF_RELEASE_QTE');
620: l_msg_tbl(0).msg_text := 'Creation of Release quote is not allowed for termination interface';
621: log_messages(log_msg_flag => 'V',
622: p_transaction_number => lp_tif_tbl(record_number).transaction_number,
651: LOOP
652: IF(lp_tif_tbl(record_number).quote_reason_code = l_quote_reason) THEN
653: -- quote reason entered is invalid
654: lp_tif_tbl(record_number).status := 'ERROR';
655: OKC_API.set_message(p_app_name => g_app_name,
656: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
657: p_token1 => 'COL_NAME',
658: p_token1_value => 'QUOTE_REASON_CODE');
659: l_msg_tbl(0).msg_text := 'quote reason '||l_quote_reason||' entered for transaction number '||lp_tif_tbl(record_number).transaction_number ||' is not valid ';
679: END IF;
680: EXCEPTION
681: WHEN OTHERS THEN
682: -- store SQL error message on message stack for caller
683: OKC_API.set_message(p_app_name => g_app_name,
684: p_msg_name => g_unexpected_error,
685: p_token1 => g_sqlcode_token,
686: p_token1_value => sqlcode,
687: p_token2 => g_sqlerrm_token,
685: p_token1 => g_sqlcode_token,
686: p_token1_value => sqlcode,
687: p_token2 => g_sqlerrm_token,
688: p_token2_value => sqlerrm);
689: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
690: l_msg_tbl(0).msg_text := 'validate_quote_type_and_reason: ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
691: log_messages(log_msg_flag => 'V',
692: msg_tbl => l_msg_tbl );
693: END validate_quote_type_and_reason;
717: message VARCHAR2(200);
718: l_msg_tbl msg_tbl_type;
719: BEGIN
720: x_tif_rec := p_tif_rec;
721: x_return_status := OKC_API.G_RET_STS_SUCCESS;
722: --checks whether asset id or asset number entered or not
723: IF((p_tif_rec.asset_id IS NULL) OR (p_tif_rec.asset_id = OKC_API.G_MISS_NUM)) THEN
724: IF((p_tif_rec.asset_number IS NULL) OR (p_tif_rec.asset_number = OKC_API.G_MISS_CHAR)) THEN
725: x_tif_rec.status := 'ERROR';
719: BEGIN
720: x_tif_rec := p_tif_rec;
721: x_return_status := OKC_API.G_RET_STS_SUCCESS;
722: --checks whether asset id or asset number entered or not
723: IF((p_tif_rec.asset_id IS NULL) OR (p_tif_rec.asset_id = OKC_API.G_MISS_NUM)) THEN
724: IF((p_tif_rec.asset_number IS NULL) OR (p_tif_rec.asset_number = OKC_API.G_MISS_CHAR)) THEN
725: x_tif_rec.status := 'ERROR';
726: OKC_API.set_message(p_app_name => g_app_name,
727: p_msg_name => 'OKC_AM_ASSET_REQUIRED',
720: x_tif_rec := p_tif_rec;
721: x_return_status := OKC_API.G_RET_STS_SUCCESS;
722: --checks whether asset id or asset number entered or not
723: IF((p_tif_rec.asset_id IS NULL) OR (p_tif_rec.asset_id = OKC_API.G_MISS_NUM)) THEN
724: IF((p_tif_rec.asset_number IS NULL) OR (p_tif_rec.asset_number = OKC_API.G_MISS_CHAR)) THEN
725: x_tif_rec.status := 'ERROR';
726: OKC_API.set_message(p_app_name => g_app_name,
727: p_msg_name => 'OKC_AM_ASSET_REQUIRED',
728: p_token1 => 'CONTRACT_NUMBER',
722: --checks whether asset id or asset number entered or not
723: IF((p_tif_rec.asset_id IS NULL) OR (p_tif_rec.asset_id = OKC_API.G_MISS_NUM)) THEN
724: IF((p_tif_rec.asset_number IS NULL) OR (p_tif_rec.asset_number = OKC_API.G_MISS_CHAR)) THEN
725: x_tif_rec.status := 'ERROR';
726: OKC_API.set_message(p_app_name => g_app_name,
727: p_msg_name => 'OKC_AM_ASSET_REQUIRED',
728: p_token1 => 'CONTRACT_NUMBER',
729: p_token1_value => p_tif_rec.contract_number);
730: message :='asset number and id not entered for contract_number '||p_tif_rec.contract_number;
732: END IF;
733: IF(x_tif_rec.status <> 'ERROR') THEN
734: /*checks whether asset is serialized or not and if serialized checks whether quantity is one or null
735: for non serialized assets checks whether quantity entered is greater than zero or not*/
736: IF((p_tif_rec.serial_number IS NOT NULL) AND (p_tif_rec.serial_number <> OKC_API.G_MISS_CHAR)) THEN
737: IF((p_tif_rec.units_to_terminate IS NOT NULL) AND (p_tif_rec.units_to_terminate <> OKC_API.G_MISS_NUM) AND (p_tif_rec.units_to_terminate <> 1)) THEN
738: x_tif_rec.status := 'ERROR';
739: OKC_API.set_message(p_app_name => g_app_name,
740: p_msg_name => 'OKL_AM_SER_ASSET_QTY',
733: IF(x_tif_rec.status <> 'ERROR') THEN
734: /*checks whether asset is serialized or not and if serialized checks whether quantity is one or null
735: for non serialized assets checks whether quantity entered is greater than zero or not*/
736: IF((p_tif_rec.serial_number IS NOT NULL) AND (p_tif_rec.serial_number <> OKC_API.G_MISS_CHAR)) THEN
737: IF((p_tif_rec.units_to_terminate IS NOT NULL) AND (p_tif_rec.units_to_terminate <> OKC_API.G_MISS_NUM) AND (p_tif_rec.units_to_terminate <> 1)) THEN
738: x_tif_rec.status := 'ERROR';
739: OKC_API.set_message(p_app_name => g_app_name,
740: p_msg_name => 'OKL_AM_SER_ASSET_QTY',
741: p_token1 => 'ASSET_NUMBER',
735: for non serialized assets checks whether quantity entered is greater than zero or not*/
736: IF((p_tif_rec.serial_number IS NOT NULL) AND (p_tif_rec.serial_number <> OKC_API.G_MISS_CHAR)) THEN
737: IF((p_tif_rec.units_to_terminate IS NOT NULL) AND (p_tif_rec.units_to_terminate <> OKC_API.G_MISS_NUM) AND (p_tif_rec.units_to_terminate <> 1)) THEN
738: x_tif_rec.status := 'ERROR';
739: OKC_API.set_message(p_app_name => g_app_name,
740: p_msg_name => 'OKL_AM_SER_ASSET_QTY',
741: p_token1 => 'ASSET_NUMBER',
742: p_token1_value => p_tif_rec.asset_number,
743: p_token2 => 'SERIAL_NUMBER',
747: ELSE
748: -- if asset quantity is less than 1
749: IF(p_tif_rec.units_to_terminate < 1) THEN
750: x_tif_rec.status := 'ERROR';
751: OKC_API.set_message(p_app_name => g_app_name,
752: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
753: p_token1 => 'COL_NAME',
754: p_token1_value => 'UNITS_TO_TERMINATE');
755: message :='asset '||p_tif_rec.asset_number||' '||p_tif_rec.asset_id||' can not have quantity less than one';
758: END IF;
759:
760: -- defaults date_effective_from to Sysdate if null and error out if past date entered.
761: IF(x_tif_rec.status <> 'ERROR') THEN
762: IF((p_tif_rec.date_effective_from IS NULL) OR (p_tif_rec.date_effective_from = OKC_API.G_MISS_DATE)) THEN
763: x_tif_rec.date_effective_from := p_sys_date;
764: --Bug 4136202 : Commented check for past date as this is now allowed (Effective dated termination impact)
765: /*ELSIF(p_tif_rec.date_effective_from < p_sys_date) THEN
766: x_tif_rec.status := 'ERROR';
763: x_tif_rec.date_effective_from := p_sys_date;
764: --Bug 4136202 : Commented check for past date as this is now allowed (Effective dated termination impact)
765: /*ELSIF(p_tif_rec.date_effective_from < p_sys_date) THEN
766: x_tif_rec.status := 'ERROR';
767: OKC_API.set_message(p_app_name => g_app_name,
768: p_msg_name => 'OKL_AM_DATE_EFF_FROM_PAST',
769: p_token1 => 'COL_NAME',
770: p_token1_value => 'DATE_EFFECTIVE_FROM');
771: message :='date_effective_from '||p_tif_rec.contract_number||' should not be a past date';*/
772: END IF;
773: END IF;
774: /* defaults the auto_accept_yn to 'N' */
775: IF(x_tif_rec.status <> 'ERROR')THEN
776: IF(x_tif_rec.auto_accept_yn IS NULL OR x_tif_rec.auto_accept_yn = OKC_API.G_MISS_CHAR)THEN
777: x_tif_rec.auto_accept_yn := 'N';
778: -- if some wrong charcter entered default it to 'N'
779: ELSIF(x_tif_rec.auto_accept_yn NOT IN ('Y','N')) THEN
780: x_tif_rec.auto_accept_yn := 'N';
799:
800: EXCEPTION
801: WHEN OTHERS THEN
802: -- store SQL error message on message stack for caller
803: OKC_API.set_message(p_app_name => g_app_name,
804: p_msg_name => g_unexpected_error,
805: p_token1 => g_sqlcode_token,
806: p_token1_value => sqlcode,
807: p_token2 => g_sqlerrm_token,
805: p_token1 => g_sqlcode_token,
806: p_token1_value => sqlcode,
807: p_token2 => g_sqlerrm_token,
808: p_token2_value => sqlerrm);
809: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
810: l_msg_tbl(0).msg_text := 'validate_required:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
811: log_messages(log_msg_flag => 'V',
812: msg_tbl => l_msg_tbl );
813: END validate_required;
898:
899: BEGIN
900: x_tif_rec := p_tif_rec;
901: x_tif_rec.status := 'WORKING';
902: x_return_status := OKC_API.G_RET_STS_SUCCESS;
903: -- fetching the contract_id,status using the cursor
904: FOR l_chr_dtl_csr IN get_chr_dtls_csr(p_chr_no => x_tif_rec.contract_number)
905: LOOP
906: l_contract_id := l_chr_dtl_csr.id;
907: l_contract_status := l_chr_dtl_csr.sts_code;
908: l_org_id := l_chr_dtl_csr.authoring_org_id;
909: END LOOP;
910: -- contract number entered not exists
911: IF(l_contract_id IS NULL OR l_contract_id = OKC_API.G_MISS_NUM) THEN
912: x_tif_rec.status := 'ERROR';
913: OKC_API.set_message(p_app_name => g_app_name,
914: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
915: p_token1 => 'COL_NAME',
909: END LOOP;
910: -- contract number entered not exists
911: IF(l_contract_id IS NULL OR l_contract_id = OKC_API.G_MISS_NUM) THEN
912: x_tif_rec.status := 'ERROR';
913: OKC_API.set_message(p_app_name => g_app_name,
914: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
915: p_token1 => 'COL_NAME',
916: p_token1_value => 'CONTRACT_NUMBER');
917: message :='contract_number '||x_tif_rec.contract_number||' entered not exists';
918: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
919: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
920: 'message 1 '|| message);
921: END IF;
922: ELSIF(x_tif_rec.contract_id IS NULL OR x_tif_rec.contract_id = OKC_API.G_MISS_NUM) THEN
923: x_tif_rec.contract_id := l_contract_id;
924: ELSIF(x_tif_rec.contract_id <> l_contract_id) THEN
925: x_tif_rec.status := 'ERROR';
926: -- contract_id and contract_number entered not matches
923: x_tif_rec.contract_id := l_contract_id;
924: ELSIF(x_tif_rec.contract_id <> l_contract_id) THEN
925: x_tif_rec.status := 'ERROR';
926: -- contract_id and contract_number entered not matches
927: OKC_API.set_message(p_app_name => g_app_name,
928: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
929: p_token1 => 'COL_NAME',
930: p_token1_value => 'CONTRACT_NUMBER');
931: message :='contract_number '||x_tif_rec.contract_number||' and contract_id '||x_tif_rec.contract_id ||' entered not matches';
937: IF(x_tif_rec.status <> 'ERROR' AND l_contract_status NOT IN('BOOKED','EVERGREEN')) THEN
938: x_tif_rec.status := 'ERROR';
939: -- contract status is not in BOOKED or EVERGREEN
940: -- this message is striked off in the DLD
941: OKC_API.set_message(p_app_name => g_app_name,
942: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
943: p_token1 => 'COL_NAME',
944: p_token1_value => 'CONTRACT_NUMBER');
945: message :='contract_number '||x_tif_rec.contract_number||' is not in booked state or in evergreen state';
964:
965: IF(l_count_asset >1) THEN
966: x_tif_rec.status := 'ERROR';
967: -- asset number and asset id not matches
968: OKC_API.set_message(p_app_name => g_app_name,
969: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
970: p_token1 => 'COL_NAME',
971: p_token1_value => 'ASSET_NUMBER');
972: message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
974: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
975: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
976: 'message 4'|| message);
977: END IF;
978: ELSIF(l_asset_id IS NULL OR l_asset_id = OKC_API.G_MISS_NUM) THEN
979: x_tif_rec.status := 'ERROR';
980: -- asset is not associated to contract
981: OKC_API.set_message(p_app_name => g_app_name,
982: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
977: END IF;
978: ELSIF(l_asset_id IS NULL OR l_asset_id = OKC_API.G_MISS_NUM) THEN
979: x_tif_rec.status := 'ERROR';
980: -- asset is not associated to contract
981: OKC_API.set_message(p_app_name => g_app_name,
982: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
983: p_token1 => 'COL_NAME',
984: p_token1_value => 'ASSET_NUMBER');
985: message :='asset is not associated to the contract_number '||x_tif_rec.contract_number||' entered';
986: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
987: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
988: 'message 5 '|| message);
989: END IF;
990: ELSIF(l_asset_number IS NULL OR l_asset_number = OKC_API.G_MISS_CHAR) THEN
991: x_tif_rec.status := 'ERROR';
992: -- asset is not associated to contract
993: OKC_API.set_message(p_app_name => g_app_name,
994: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
989: END IF;
990: ELSIF(l_asset_number IS NULL OR l_asset_number = OKC_API.G_MISS_CHAR) THEN
991: x_tif_rec.status := 'ERROR';
992: -- asset is not associated to contract
993: OKC_API.set_message(p_app_name => g_app_name,
994: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
995: p_token1 => 'COL_NAME',
996: p_token1_value => 'ASSET_NUMBER');
997: message :='asset is not associated to the contract_number '||x_tif_rec.contract_number||' entered';
999: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1000: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1001: 'message 6 '|| message);
1002: END IF;
1003: ELSIF(p_tif_rec.asset_number IS NOT NULL AND p_tif_rec.asset_number <> OKC_API.G_MISS_CHAR) THEN
1004: IF(p_tif_rec.asset_number <> l_asset_number) THEN
1005: x_tif_rec.status := 'ERROR';
1006: -- asset id and asset number entered not matches
1007: OKC_API.set_message(p_app_name => g_app_name,
1003: ELSIF(p_tif_rec.asset_number IS NOT NULL AND p_tif_rec.asset_number <> OKC_API.G_MISS_CHAR) THEN
1004: IF(p_tif_rec.asset_number <> l_asset_number) THEN
1005: x_tif_rec.status := 'ERROR';
1006: -- asset id and asset number entered not matches
1007: OKC_API.set_message(p_app_name => g_app_name,
1008: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1009: p_token1 => 'COL_NAME',
1010: p_token1_value => 'ASSET_NUMBER');
1011: message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
1012: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1013: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1014: 'message 7 '|| message);
1015: END IF;
1016: ELSIF(p_tif_rec.asset_id IS NOT NULL AND p_tif_rec.asset_id <> OKC_API.G_MISS_NUM) THEN
1017: IF(p_tif_rec.asset_id <> l_asset_id) THEN
1018: x_tif_rec.status := 'ERROR';
1019: -- asset id and asset number entered not matches
1020: OKC_API.set_message(p_app_name => g_app_name,
1016: ELSIF(p_tif_rec.asset_id IS NOT NULL AND p_tif_rec.asset_id <> OKC_API.G_MISS_NUM) THEN
1017: IF(p_tif_rec.asset_id <> l_asset_id) THEN
1018: x_tif_rec.status := 'ERROR';
1019: -- asset id and asset number entered not matches
1020: OKC_API.set_message(p_app_name => g_app_name,
1021: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1022: p_token1 => 'COL_NAME',
1023: p_token1_value => 'ASSET_NUMBER');
1024: message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
1029: END IF;
1030: ELSE
1031: x_tif_rec.asset_id := l_asset_id;
1032: END IF;
1033: ELSIF(p_tif_rec.asset_id IS NOT NULL AND p_tif_rec.asset_id <> OKC_API.G_MISS_NUM) THEN
1034: IF(p_tif_rec.asset_id <> l_asset_id) THEN
1035: x_tif_rec.status := 'ERROR';
1036: -- asset id and asset number entered not matches
1037: OKC_API.set_message(p_app_name => g_app_name,
1033: ELSIF(p_tif_rec.asset_id IS NOT NULL AND p_tif_rec.asset_id <> OKC_API.G_MISS_NUM) THEN
1034: IF(p_tif_rec.asset_id <> l_asset_id) THEN
1035: x_tif_rec.status := 'ERROR';
1036: -- asset id and asset number entered not matches
1037: OKC_API.set_message(p_app_name => g_app_name,
1038: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1039: p_token1 => 'COL_NAME',
1040: p_token1_value => 'ASSET_NUMBER');
1041: message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
1042: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1043: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1044: 'message 9 '|| message);
1045: END IF;
1046: ELSIF(p_tif_rec.asset_number IS NOT NULL AND p_tif_rec.asset_number <> OKC_API.G_MISS_CHAR) THEN
1047: IF(p_tif_rec.asset_number <> l_asset_number) THEN
1048: x_tif_rec.status := 'ERROR';
1049: -- asset id and asset number entered not matches
1050: OKC_API.set_message(p_app_name => g_app_name,
1046: ELSIF(p_tif_rec.asset_number IS NOT NULL AND p_tif_rec.asset_number <> OKC_API.G_MISS_CHAR) THEN
1047: IF(p_tif_rec.asset_number <> l_asset_number) THEN
1048: x_tif_rec.status := 'ERROR';
1049: -- asset id and asset number entered not matches
1050: OKC_API.set_message(p_app_name => g_app_name,
1051: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1052: p_token1 => 'COL_NAME',
1053: p_token1_value => 'ASSET_NUMBER');
1054: message :='asset_number '||x_tif_rec.asset_number||' and asset_id '||x_tif_rec.asset_id ||' entered not matches';
1063: END IF;
1064: ELSIF(l_asset_status NOT IN('BOOKED','EVERGREEN')) THEN
1065: x_tif_rec.status := 'ERROR';
1066: -- asset status is not in BOOKED or EVERGREEN
1067: OKC_API.set_message(p_app_name => g_app_name,
1068: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1069: p_token1 => 'COL_NAME',
1070: p_token1_value => 'ASSET_NUMBER');
1071: message :='asset_number '||x_tif_rec.asset_number||' is not in booked state or in evergreen state';
1074: 'message 11 '|| message);
1075: END IF;
1076: END IF;
1077: IF(x_tif_rec.status <> 'ERROR') THEN
1078: IF(x_tif_rec.serial_number IS NOT NULL AND x_tif_rec.serial_number <> OKC_API.G_MISS_CHAR) THEN
1079: x_tif_rec.status := 'ENTERED';
1080: --fetching instance_id using cursor
1081: FOR l_instance_id_csr IN get_instance_id_csr(p_ast_id => x_tif_rec.asset_id)
1082: LOOP
1080: --fetching instance_id using cursor
1081: FOR l_instance_id_csr IN get_instance_id_csr(p_ast_id => x_tif_rec.asset_id)
1082: LOOP
1083: l_instance_id := l_instance_id_csr.id;
1084: IF(l_instance_id IS NULL OR l_instance_id = OKC_API.G_MISS_NUM) THEN
1085: x_tif_rec.status := 'ERROR';
1086: -- instance line not present for the asset
1087: OKC_API.set_message(p_app_name => g_app_name,
1088: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1083: l_instance_id := l_instance_id_csr.id;
1084: IF(l_instance_id IS NULL OR l_instance_id = OKC_API.G_MISS_NUM) THEN
1085: x_tif_rec.status := 'ERROR';
1086: -- instance line not present for the asset
1087: OKC_API.set_message(p_app_name => g_app_name,
1088: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1089: p_token1 => 'COL_NAME',
1090: p_token1_value => 'SERIAL_NUMBER');
1091: message :='instance line is not present for the asset '||x_tif_rec.asset_number;
1098: FOR l_installbase_id_csr IN get_installbase_id_csr(p_instance_id => l_instance_id)
1099: LOOP
1100: l_installbase_id := l_installbase_id_csr.id;
1101: END LOOP;
1102: IF(l_installbase_id IS NULL OR l_installbase_id = OKC_API.G_MISS_NUM) THEN
1103: x_tif_rec.status := 'ERROR';
1104: -- installbase has no entries for the instance
1105: OKC_API.set_message(p_app_name => g_app_name,
1106: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1101: END LOOP;
1102: IF(l_installbase_id IS NULL OR l_installbase_id = OKC_API.G_MISS_NUM) THEN
1103: x_tif_rec.status := 'ERROR';
1104: -- installbase has no entries for the instance
1105: OKC_API.set_message(p_app_name => g_app_name,
1106: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1107: p_token1 => 'COL_NAME',
1108: p_token1_value => 'SERIAL_NUMBER');
1109: message :='installbase line is not present for the asset '||x_tif_rec.asset_number;
1121: IF(l_serial_number = x_tif_rec.serial_number) THEN
1122: x_tif_rec.status := 'WORKING';
1123: x_tif_rec.units_to_terminate := 1;
1124: EXIT;
1125: ELSIF(l_serial_number IS NULL OR l_serial_number = OKC_API.G_MISS_CHAR) THEN
1126: x_tif_rec.status := 'ERROR';
1127: -- asset is not serialized
1128: OKC_API.set_message(p_app_name => g_app_name,
1129: p_msg_name => 'OKC_AM_NO_SERIALIZED_ASSET',
1124: EXIT;
1125: ELSIF(l_serial_number IS NULL OR l_serial_number = OKC_API.G_MISS_CHAR) THEN
1126: x_tif_rec.status := 'ERROR';
1127: -- asset is not serialized
1128: OKC_API.set_message(p_app_name => g_app_name,
1129: p_msg_name => 'OKC_AM_NO_SERIALIZED_ASSET',
1130: p_token1 => 'COL_NAME',
1131: p_token1_value => 'ASSET_NUMBER');
1132: message :='asset is not serialized '||x_tif_rec.asset_number;
1141: END LOOP;
1142: IF(x_tif_rec.status = 'ENTERED') THEN
1143: x_tif_rec.status := 'ERROR';
1144: -- enter serial number associated for this asset
1145: OKC_API.set_message(p_app_name => g_app_name,
1146: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1147: p_token1 => 'COL_NAME',
1148: p_token1_value => 'SERIAL_NUMBER');
1149: message :='Enter serial number associated for this asset '||x_tif_rec.asset_number;
1160: END LOOP;
1161: IF(l_quantity < x_tif_rec.units_to_terminate) THEN
1162: x_tif_rec.status := 'ERROR';
1163: -- quantity entered is more than asset quantity
1164: OKC_API.set_message(p_app_name => g_app_name,
1165: p_msg_name => 'OKL_AM_INVALID_ASSET_QTY',
1166: p_token1 => 'ASSET_NUMBER',
1167: p_token1_value => x_tif_rec.asset_number);
1168: message :='no.of units entered to terminate is more than the quantity associated with asset '||x_tif_rec.asset_number;
1175: END IF; -- instance line
1176: END IF; -- asset if
1177: IF(x_tif_rec.status <> 'ERROR') THEN
1178: -- validate the session ORG_ID with the contract authoring_org_id, if org_id null then assign authoring_org_id
1179: IF((p_tif_rec.org_id IS NULL) OR (p_tif_rec.org_id = OKC_API.G_MISS_NUM)) THEN
1180: x_tif_rec.org_id := l_org_id;
1181: END IF;
1182: IF(x_tif_rec.org_id <> l_session_org_id) THEN
1183: x_tif_rec.status := 'ENTERED';
1181: END IF;
1182: IF(x_tif_rec.org_id <> l_session_org_id) THEN
1183: x_tif_rec.status := 'ENTERED';
1184: -- org_id not matches with session org_id so not processing it, but status remains in ENTERED
1185: OKC_API.set_message(p_app_name => g_app_name,
1186: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
1187: p_token1 => 'COL_NAME',
1188: p_token1_value => 'ORG_ID');
1189: message :='org id is not for this session, not errored out';
1207: IF p_tif_rec.quote_type_code LIKE 'TER_ROLL%'
1208: AND p_tif_rec.auto_accept_yn = 'Y'
1209: THEN
1210: x_tif_rec.status := 'ERROR';
1211: OKC_API.set_message(p_app_name => g_app_name,
1212: p_msg_name => 'OKL_NO_ACPT_ROLL_QTE');
1213: message := 'Rollover quotes can only be accepted from booking process of a rolled over contract';
1214: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1215: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_data.',
1235: x_return_status => x_return_status);
1236: EXCEPTION
1237: WHEN OTHERS THEN
1238: -- store SQL error message on message stack for caller
1239: OKC_API.set_message(p_app_name => g_app_name,
1240: p_msg_name => g_unexpected_error,
1241: p_token1 => g_sqlcode_token,
1242: p_token1_value => sqlcode,
1243: p_token2 => g_sqlerrm_token,
1241: p_token1 => g_sqlcode_token,
1242: p_token1_value => sqlcode,
1243: p_token2 => g_sqlerrm_token,
1244: p_token2_value => sqlerrm);
1245: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1246: l_msg_tbl(0).msg_text := 'validate_data: ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1247: log_messages(log_msg_flag => 'V',
1248: msg_tbl => l_msg_tbl );
1249: END validate_data;
1279: IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1280: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_record','Begin(+)');
1281: END IF;
1282:
1283: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1284: -- this procedure checks whether required fields are entered or not
1285: validate_required(p_api_version => p_api_version,
1286: p_init_msg_list => OKC_API.G_FALSE,
1287: x_msg_count => x_msg_count,
1282:
1283: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1284: -- this procedure checks whether required fields are entered or not
1285: validate_required(p_api_version => p_api_version,
1286: p_init_msg_list => OKC_API.G_FALSE,
1287: x_msg_count => x_msg_count,
1288: x_msg_data => x_msg_data,
1289: x_return_status => x_return_status,
1290: p_sys_date => p_sys_date,
1300: l_tif_rec := x_tif_rec;
1301: /*this procedure validates the data against the database and also
1302: populates the remaining columns */
1303: validate_data(p_api_version => p_api_version,
1304: p_init_msg_list => OKC_API.G_FALSE,
1305: x_msg_count => x_msg_count,
1306: x_msg_data => x_msg_data,
1307: x_return_status => x_return_status,
1308: p_tif_rec => l_tif_rec,
1320: END IF;
1321: EXCEPTION
1322: WHEN OTHERS THEN
1323: -- store SQL error message on message stack for caller
1324: OKC_API.set_message(p_app_name => g_app_name,
1325: p_msg_name => g_unexpected_error,
1326: p_token1 => g_sqlcode_token,
1327: p_token1_value => sqlcode,
1328: p_token2 => g_sqlerrm_token,
1326: p_token1 => g_sqlcode_token,
1327: p_token1_value => sqlcode,
1328: p_token2 => g_sqlerrm_token,
1329: p_token2_value => sqlerrm);
1330: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1331: l_msg_tbl(0).msg_text := 'validate_record: ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1332: log_messages(log_msg_flag => 'V',
1333: msg_tbl => l_msg_tbl );
1334: END validate_record;
1366: END IF;
1367:
1368:
1369: record_number:=0;
1370: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1371: IF (p_tif_tbl.COUNT > 0) THEN
1372: record_number := p_tif_tbl.FIRST;
1373: LOOP
1374: IF(p_tif_tbl(record_number).status <> 'ERROR') THEN
1381: 'calling validate_record..for rec number ..'||record_number);
1382: end if;
1383:
1384: validate_record(p_api_version => p_api_version,
1385: p_init_msg_list => OKC_API.G_FALSE,
1386: x_msg_count => x_msg_count,
1387: x_msg_data => x_msg_data,
1388: x_return_status => x_return_status,
1389: p_sys_date => p_sys_date,
1409: END IF;
1410: EXCEPTION
1411: WHEN OTHERS THEN
1412: -- store SQL error message on message stack for caller
1413: OKC_API.set_message(p_app_name => g_app_name,
1414: p_msg_name => g_unexpected_error,
1415: p_token1 => g_sqlcode_token,
1416: p_token1_value => sqlcode,
1417: p_token2 => g_sqlerrm_token,
1415: p_token1 => g_sqlcode_token,
1416: p_token1_value => sqlcode,
1417: p_token2 => g_sqlerrm_token,
1418: p_token2_value => sqlerrm);
1419: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1420: l_msg_tbl(0).msg_text := 'validate_transaction:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1421: log_messages(log_msg_flag => 'V',
1422: msg_tbl => l_msg_tbl );
1423: END validate_transaction;
1442: x_return_status OUT NOCOPY VARCHAR2,
1443: p_tif_rec IN tif_rec_type) IS
1444: l_api_version CONSTANT NUMBER := p_api_version;
1445: l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
1446: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1447: l_msg_tbl msg_tbl_type;
1448: BEGIN
1449: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1450: --Check API version, initialize message list and create savepoint.
1445: l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
1446: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1447: l_msg_tbl msg_tbl_type;
1448: BEGIN
1449: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1450: --Check API version, initialize message list and create savepoint.
1451: l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1452: G_PKG_NAME,
1453: p_init_msg_list,
1447: l_msg_tbl msg_tbl_type;
1448: BEGIN
1449: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1450: --Check API version, initialize message list and create savepoint.
1451: l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1452: G_PKG_NAME,
1453: p_init_msg_list,
1454: l_api_version,
1455: p_api_version,
1454: l_api_version,
1455: p_api_version,
1456: '_PVT',
1457: x_return_status);
1458: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1459: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1460: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1461: RAISE OKC_API.G_EXCEPTION_ERROR;
1462: END IF;
1455: p_api_version,
1456: '_PVT',
1457: x_return_status);
1458: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1459: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1460: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1461: RAISE OKC_API.G_EXCEPTION_ERROR;
1462: END IF;
1463:
1456: '_PVT',
1457: x_return_status);
1458: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1459: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1460: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1461: RAISE OKC_API.G_EXCEPTION_ERROR;
1462: END IF;
1463:
1464: UPDATE OKL_TERMNT_INTERFACE
1457: x_return_status);
1458: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1459: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1460: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1461: RAISE OKC_API.G_EXCEPTION_ERROR;
1462: END IF;
1463:
1464: UPDATE OKL_TERMNT_INTERFACE
1465: SET
1512: ,OKL_TERMNT_INTERFACE.LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1513: ,OKL_TERMNT_INTERFACE.GROUP_NUMBER = p_tif_rec.group_number
1514: WHERE
1515: OKL_TERMNT_INTERFACE.ROWID =p_tif_rec.row_id;
1516: OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1517:
1518: OKL_AM_UTIL_PVT.process_messages(p_trx_source_table => 'OKL_TERMNT_INTERFACE',
1519: p_trx_id => p_tif_rec.transaction_number,
1520: x_return_status => l_return_status);
1519: p_trx_id => p_tif_rec.transaction_number,
1520: x_return_status => l_return_status);
1521: x_return_status := l_return_status;
1522: EXCEPTION
1523: WHEN OKC_API.G_EXCEPTION_ERROR THEN
1524: x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
1525: G_PKG_NAME,
1526: 'OKC_API.G_RET_STS_ERROR',
1527: x_msg_count,
1520: x_return_status => l_return_status);
1521: x_return_status := l_return_status;
1522: EXCEPTION
1523: WHEN OKC_API.G_EXCEPTION_ERROR THEN
1524: x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
1525: G_PKG_NAME,
1526: 'OKC_API.G_RET_STS_ERROR',
1527: x_msg_count,
1528: x_msg_data,
1522: EXCEPTION
1523: WHEN OKC_API.G_EXCEPTION_ERROR THEN
1524: x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
1525: G_PKG_NAME,
1526: 'OKC_API.G_RET_STS_ERROR',
1527: x_msg_count,
1528: x_msg_data,
1529: '_PVT');
1530: --unexpected error
1527: x_msg_count,
1528: x_msg_data,
1529: '_PVT');
1530: --unexpected error
1531: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1532: x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
1533: G_PKG_NAME,
1534: 'OKC_API.G_RET_STS_UNEXP_ERROR',
1535: x_msg_count,
1528: x_msg_data,
1529: '_PVT');
1530: --unexpected error
1531: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1532: x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
1533: G_PKG_NAME,
1534: 'OKC_API.G_RET_STS_UNEXP_ERROR',
1535: x_msg_count,
1536: x_msg_data,
1530: --unexpected error
1531: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1532: x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
1533: G_PKG_NAME,
1534: 'OKC_API.G_RET_STS_UNEXP_ERROR',
1535: x_msg_count,
1536: x_msg_data,
1537: '_PVT');
1538: WHEN OTHERS THEN
1536: x_msg_data,
1537: '_PVT');
1538: WHEN OTHERS THEN
1539: -- store SQL error message on message stack for caller
1540: OKC_API.set_message(p_app_name => g_app_name,
1541: p_msg_name => g_unexpected_error,
1542: p_token1 => g_sqlcode_token,
1543: p_token1_value => sqlcode,
1544: p_token2 => g_sqlerrm_token,
1542: p_token1 => g_sqlcode_token,
1543: p_token1_value => sqlcode,
1544: p_token2 => g_sqlerrm_token,
1545: p_token2_value => sqlerrm);
1546: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1547: l_msg_tbl(0).msg_text := 'update_row:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1548: log_messages(log_msg_flag => 'V',
1549: msg_tbl => l_msg_tbl );
1550: END update_row;
1633: IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1634: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.change_status','Begin(+)');
1635: END IF;
1636: record_number:=0;
1637: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1638: IF (p_tif_tbl.COUNT > 0) THEN
1639: record_number := p_tif_tbl.FIRST;
1640: -- loops through the table of records to update the values
1641: LOOP
1639: record_number := p_tif_tbl.FIRST;
1640: -- loops through the table of records to update the values
1641: LOOP
1642: update_row(p_api_version => p_api_version
1643: ,p_init_msg_list => OKC_API.G_FALSE
1644: ,x_msg_data => x_msg_data
1645: ,x_msg_count => x_msg_count
1646: ,x_return_status => x_return_status
1647: ,p_tif_rec => p_tif_tbl(record_number));
1718:
1719: EXCEPTION
1720: WHEN OTHERS THEN
1721: -- store SQL error message on message stack for caller
1722: OKC_API.set_message(p_app_name => g_app_name,
1723: p_msg_name => g_unexpected_error,
1724: p_token1 => g_sqlcode_token,
1725: p_token1_value => sqlcode,
1726: p_token2 => g_sqlerrm_token,
1727: p_token2_value => sqlerrm);
1728: l_msg_tbl(0).msg_text := 'change_status:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1729: log_messages(log_msg_flag => 'V',
1730: msg_tbl => l_msg_tbl );
1731: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1732: END change_status;
1733:
1734:
1735: -- Start of comments
1768: SELECT current_units quantity
1769: FROM OKX_ASSETS_V
1770: WHERE asset_number = p_asset_number;
1771: BEGIN
1772: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1773: IF(p_tif_tbl.COUNT>0)THEN
1774: -- updates the values in the tbl to database and retrives the data with status='WORKING'
1775: change_status(p_api_version => p_api_version,
1776: p_init_msg_list => OKC_API.G_FALSE,
1772: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1773: IF(p_tif_tbl.COUNT>0)THEN
1774: -- updates the values in the tbl to database and retrives the data with status='WORKING'
1775: change_status(p_api_version => p_api_version,
1776: p_init_msg_list => OKC_API.G_FALSE,
1777: x_msg_count => x_msg_count,
1778: x_msg_data => x_msg_data,
1779: x_return_status => x_return_status,
1780: p_tif_tbl => p_tif_tbl,
1779: x_return_status => x_return_status,
1780: p_tif_tbl => p_tif_tbl,
1781: x_tif_tbl => lp_tif_tbl);
1782: END IF;
1783: l_contract_id := OKC_API.G_MISS_NUM;
1784: IF(lp_tif_tbl.COUNT>0 AND x_return_status = FND_API.G_RET_STS_SUCCESS)THEN
1785: -- loops through the interface records
1786: FOR term_rec IN lp_tif_tbl.FIRST..lp_tif_tbl.LAST
1787: LOOP
1789: (lp_tif_tbl(term_rec).asset_id = l_asset_id) AND
1790: (lp_tif_tbl(term_rec).date_effective_from = l_date_effective_from) AND
1791: (lp_tif_tbl(term_rec).quote_type_code = l_quote_type) AND
1792: (lp_tif_tbl(term_rec).quote_reason_code = l_reason_type) )THEN
1793: IF(lp_tif_tbl(term_rec).serial_number IS NULL OR lp_tif_tbl(term_rec).serial_number = OKC_API.G_MISS_CHAR) THEN
1794: IF(l_asset_qty
1796: -- while grouping if quantity exceeds asset quantity those entries will be ERROR out
1797: OKC_API.set_message(p_app_name => g_app_name,
1793: IF(lp_tif_tbl(term_rec).serial_number IS NULL OR lp_tif_tbl(term_rec).serial_number = OKC_API.G_MISS_CHAR) THEN
1794: IF(l_asset_qty
1796: -- while grouping if quantity exceeds asset quantity those entries will be ERROR out
1797: OKC_API.set_message(p_app_name => g_app_name,
1798: p_msg_name => 'OKL_AM_INVALID_ASSET_QTY',
1799: p_token1 => 'COL_NAME',
1800: p_token1_value => 'ASSET_NUMBER');
1801: message :='quote quantity exceeds asset quantity for asset '||lp_tif_tbl(term_rec).asset_number||' for transaction_number '||lp_tif_tbl(term_rec).transaction_number;
1805: ELSIF(l_serial_number IS NOT NULL) THEN
1806: IF(l_serial_number = lp_tif_tbl(term_rec).serial_number)THEN
1807: lp_tif_tbl(term_rec).status := 'ERROR';
1808: -- if same serial_number is repeated in a group tthen ERROR out
1809: OKC_API.set_message(p_app_name => g_app_name,
1810: p_msg_name => 'OKL_AM_DUP_LINE',
1811: p_token1 => 'SERIAL_NUMBER',
1812: p_token1_value => lp_tif_tbl(term_rec).serial_number);
1813: message :='duplicate record for serialized asset '||lp_tif_tbl(term_rec).asset_number||' for transaction_number '||lp_tif_tbl(term_rec).transaction_number;
1813: message :='duplicate record for serialized asset '||lp_tif_tbl(term_rec).asset_number||' for transaction_number '||lp_tif_tbl(term_rec).transaction_number;
1814: ELSIF(l_asset_qty
1816: -- while grouping if quantity exceeds asset quantity those entries will be ERROR out
1817: OKC_API.set_message(p_app_name => g_app_name,
1818: p_msg_name => 'OKL_AM_INVALID_ASSET_QTY',
1819: p_token1 => 'COL_NAME',
1820: p_token1_value => 'ASSET_NUMBER');
1821: message :='quote quantity exceeds asset quantity for asset '||lp_tif_tbl(term_rec).asset_number||' for transaction_number '||lp_tif_tbl(term_rec).transaction_number;
1850: END IF;
1851: END LOOP;
1852: -- updates the values in database and gets records with status='WORKING'
1853: change_status(p_api_version => p_api_version,
1854: p_init_msg_list => OKC_API.G_FALSE,
1855: x_msg_count => x_msg_count,
1856: x_msg_data => x_msg_data,
1857: x_return_status => x_return_status,
1858: p_tif_tbl => lp_tif_tbl,
1861: END IF;
1862: EXCEPTION
1863: WHEN OTHERS THEN
1864: -- store SQL error message on message stack for caller
1865: OKC_API.set_message(p_app_name => g_app_name,
1866: p_msg_name => g_unexpected_error,
1867: p_token1 => g_sqlcode_token,
1868: p_token1_value => sqlcode,
1869: p_token2 => g_sqlerrm_token,
1867: p_token1 => g_sqlcode_token,
1868: p_token1_value => sqlcode,
1869: p_token2 => g_sqlerrm_token,
1870: p_token2_value => sqlerrm);
1871: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1872: l_msg_tbl(0).msg_text := 'remove_duplicates:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
1873: log_messages(log_msg_flag => 'V',
1874: msg_tbl => l_msg_tbl );
1875: END remove_duplicates;
1956: IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
1957: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.create_quote','Begin(+)');
1958: END IF;
1959:
1960: x_return_status := OKC_API.G_RET_STS_SUCCESS;
1961: x_tif_tbl := p_tif_tbl;
1962: lp_quot_rec := p_quot_rec;
1963: lp_assn_tbl := p_assn_tbl;
1964: lp_qpyv_tbl := p_qpyv_tbl;
2000:
2001: --Added new parameter p_term_from_intf by sechawla for bug 7383445
2002: /* calls the create_termination_quote API and retrives the quote_id */
2003: OKL_AM_CREATE_QUOTE_PUB.create_terminate_quote(p_api_version => p_api_version
2004: ,p_init_msg_list => OKC_API.G_FALSE
2005: ,x_msg_data => x_msg_data
2006: ,x_msg_count => x_msg_count
2007: ,x_return_status => l_return_status
2008: ,p_quot_rec => lp_quot_rec
2085: IF(batch_offset = p_record_number) THEN
2086: IF(lp_qld_tbl.COUNT>0) THEN
2087: -- calls the quote_line_dtls API to create quote line details for the serialized assets
2088: OKL_AM_TERMNT_QUOTE_PUB.quote_line_dtls(p_api_version => p_api_version,
2089: p_init_msg_list => OKC_API.G_FALSE,
2090: x_return_status => l_return_status,
2091: x_msg_count => x_msg_count,
2092: x_msg_data => x_msg_data,
2093: p_qld_tbl => lp_qld_tbl);
2243:
2244: EXCEPTION
2245: WHEN OTHERS THEN
2246: -- store SQL error message on message stack for caller
2247: OKC_API.set_message(p_app_name => g_app_name,
2248: p_msg_name => g_unexpected_error,
2249: p_token1 => g_sqlcode_token,
2250: p_token1_value => sqlcode,
2251: p_token2 => g_sqlerrm_token,
2249: p_token1 => g_sqlcode_token,
2250: p_token1_value => sqlcode,
2251: p_token2 => g_sqlerrm_token,
2252: p_token2_value => sqlerrm);
2253: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2254: l_msg_tbl(0).msg_text := 'create_quote:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
2255: log_messages(log_msg_flag => 'V',
2256: msg_tbl => l_msg_tbl );
2257: END create_quote;
2316: batch_offset := 0;
2317: group_number := p_group_number;
2318: lp_tif_tbl := p_tif_tbl;
2319: lx_tif_tbl := p_tif_tbl;
2320: l_contract_number := OKC_API.G_MISS_CHAR;
2321: x_return_status := OKC_API.G_RET_STS_SUCCESS;
2322:
2323: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2324: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2317: group_number := p_group_number;
2318: lp_tif_tbl := p_tif_tbl;
2319: lx_tif_tbl := p_tif_tbl;
2320: l_contract_number := OKC_API.G_MISS_CHAR;
2321: x_return_status := OKC_API.G_RET_STS_SUCCESS;
2322:
2323: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2324: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2325: 'p_group_number : '||p_group_number);
2383: lp_quot_rec.qtp_code := p_tif_tbl(record_number-1).quote_type_code;
2384: lp_quot_rec.qrs_code := p_tif_tbl(record_number-1).quote_reason_code;
2385: create_quote(
2386: p_api_version => p_api_version
2387: ,p_init_msg_list => OKC_API.G_FALSE
2388: ,x_msg_count => x_msg_count
2389: ,x_msg_data => x_msg_data
2390: ,x_return_status => x_return_status
2391: ,p_tif_tbl => lp_tif_tbl
2529: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2530: 'last record .. qrs_code '||p_tif_tbl(record_number).quote_reason_code);
2531: END IF;
2532: create_quote(p_api_version => p_api_version
2533: ,p_init_msg_list => OKC_API.G_FALSE
2534: ,x_msg_count => x_msg_count
2535: ,x_msg_data => x_msg_data
2536: ,x_return_status => x_return_status
2537: ,p_tif_tbl => lp_tif_tbl
2577: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_quote.',
2578: 'record_number .. '||record_number);
2579: END IF;
2580: update_row(p_api_version => p_api_version
2581: ,p_init_msg_list => OKC_API.G_FALSE
2582: ,x_msg_data => x_msg_data
2583: ,x_msg_count => x_msg_count
2584: ,x_return_status => x_return_status
2585: ,p_tif_rec => x_tif_tbl(record_number));
2597:
2598: EXCEPTION
2599: WHEN OTHERS THEN
2600: -- store SQL error message on message stack for caller
2601: OKC_API.set_message(p_app_name => g_app_name,
2602: p_msg_name => g_unexpected_error,
2603: p_token1 => g_sqlcode_token,
2604: p_token1_value => sqlcode,
2605: p_token2 => g_sqlerrm_token,
2606: p_token2_value => sqlerrm);
2607: l_msg_tbl(0).msg_text := 'populate_quote:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
2608: log_messages(log_msg_flag => 'V',
2609: msg_tbl => l_msg_tbl );
2610: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2611: END populate_quote;
2612:
2613: /* RABHUPAT added party logic for termination interface logic, only recipient role can be overwritten not others
2614: and creates quote for each party record defaults the quote_role_code to 'RECIPIENT'*/
2693:
2694: x_tif_tbl := p_tif_tbl;
2695: lp_tip_tbl := p_tip_tbl;
2696: lx_tip_tbl := p_tip_tbl;
2697: x_return_status := OKC_API.G_RET_STS_SUCCESS;
2698: l_count := 0;
2699:
2700: IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
2701: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party', 'lp_tip_tbl.count = '|| lp_tip_tbl.count);
2737: lx_tip_tbl(record_number).status := 'ERROR';
2738: l_dup_flag := 'Y';
2739: --transaction_number repeated, ERROR out only the party record not the INTERFACE record
2740: -- striked off in the DLD
2741: OKC_API.set_message(p_app_name => g_app_name,
2742: p_msg_name => 'OKL_AM_DUP_TRAN_NUM',
2743: p_token1 => 'TRANSACTION_NUMBER',
2744: p_token1_value => lx_tip_tbl(record_number).transaction_number);
2745: message :='transaction_number '||lx_tip_tbl(record_number).transaction_number||' repeated in party table';
2749:
2750: ELSIF(lp_tip_tbl(record_number).contract_party_role IS NULL) THEN
2751: lx_tip_tbl(record_number).status := 'ERROR';
2752: -- contract_party_role should be entered
2753: OKC_API.set_message(p_app_name => g_app_name,
2754: p_msg_name => 'OKC_CONTRACTS_REQUIRED_VALUE',
2755: p_token1 => 'COL_NAME',
2756: p_token1_value => 'CONTRACT PARTY ROLE');
2757: message :='contract party role should be entered for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2780: IF(interface_dtls.status <> 'WORKING') THEN
2781: lx_tip_tbl(record_number).status := 'ERROR';
2782: -- corresponding transaction_number in INTERFACE table is ERRORED out
2783: -- striked off in the DLD
2784: OKC_API.set_message(p_app_name => g_app_name,
2785: p_msg_name => 'OKL_AM_ERR_INTERFACE',
2786: p_token1 => 'TRANSACTION_NUMBER',
2787: p_token1_value => lx_tip_tbl(record_number).transaction_number);
2788: message :='transaction_number in OKL_TERMNT_INTERFACE is ERROR for transaction number '||lx_tip_tbl(record_number).transaction_number;
2799: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party','lx_tip_tbl(record_number).status2 '
2800: ||lx_tip_tbl(record_number).status || 'l_chr_id = '|| l_chr_id);
2801: END IF;
2802:
2803: IF(l_chr_id IS NULL OR l_chr_id = OKC_API.G_MISS_NUM)THEN
2804: lx_tip_tbl(record_number).status := 'ERROR';
2805: -- wrong transaction number
2806: OKC_API.set_message(p_app_name => g_app_name,
2807: p_msg_name => 'OKL_AM_JOIN_MIS_MATCH',
2802:
2803: IF(l_chr_id IS NULL OR l_chr_id = OKC_API.G_MISS_NUM)THEN
2804: lx_tip_tbl(record_number).status := 'ERROR';
2805: -- wrong transaction number
2806: OKC_API.set_message(p_app_name => g_app_name,
2807: p_msg_name => 'OKL_AM_JOIN_MIS_MATCH',
2808: p_token1 => 'TRANSACTION_NUMBER',
2809: p_token1_value => lx_tip_tbl(record_number).transaction_number);
2810: message :='wrong transaction number '||lx_tip_tbl(record_number).transaction_number;
2818: ||lx_tip_tbl(record_number).status);
2819: END IF;
2820: -- if no role specified default it to RECIPIENT
2821: IF(lx_tip_tbl(record_number).status <> 'ERROR') THEN
2822: IF(lx_tip_tbl(record_number).quote_role_code IS NULL OR lx_tip_tbl(record_number).quote_role_code = OKC_API.G_MISS_CHAR) THEN
2823: lx_tip_tbl(record_number).quote_role_code := 'RECIPIENT';
2824: END IF;
2825: IF(lx_tip_tbl(record_number).quote_role_code = 'RECIPIENT') THEN
2826: -- retrive the quote_party information
2945:
2946: IF(l_count = 0) THEN
2947: lx_tip_tbl(record_number). status := 'ERROR';
2948: -- neither lessee nor vendor
2949: OKC_API.set_message(p_app_name => g_app_name,
2950: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2951: p_token1 => 'COL_NAME',
2952: p_token1_value => 'CONTRACT PARTY ROLE');
2953: message :='quote party should be a lessee or vendor for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2955: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.validate_party.',
2956: 'message11 '||message);
2957: END IF;
2958: ELSIF(l_count>1) THEN
2959: IF((lp_tip_tbl(record_number).contract_party_id IS NULL OR lp_tip_tbl(record_number).contract_party_id = OKC_API.G_MISS_NUM) AND
2960: (lp_tip_tbl(record_number).contract_party_number IS NULL OR lp_tip_tbl(record_number).contract_party_number = OKC_API.G_MISS_CHAR)) THEN
2961: lx_tip_tbl(record_number). status := 'ERROR';
2962: -- neither contract_party_number nor contract_party_id are entered
2963: OKC_API.set_message(p_app_name => g_app_name,
2956: 'message11 '||message);
2957: END IF;
2958: ELSIF(l_count>1) THEN
2959: IF((lp_tip_tbl(record_number).contract_party_id IS NULL OR lp_tip_tbl(record_number).contract_party_id = OKC_API.G_MISS_NUM) AND
2960: (lp_tip_tbl(record_number).contract_party_number IS NULL OR lp_tip_tbl(record_number).contract_party_number = OKC_API.G_MISS_CHAR)) THEN
2961: lx_tip_tbl(record_number). status := 'ERROR';
2962: -- neither contract_party_number nor contract_party_id are entered
2963: OKC_API.set_message(p_app_name => g_app_name,
2964: p_msg_name => 'OKC_CONTRACTS_REQUIRED_VALUE',
2959: IF((lp_tip_tbl(record_number).contract_party_id IS NULL OR lp_tip_tbl(record_number).contract_party_id = OKC_API.G_MISS_NUM) AND
2960: (lp_tip_tbl(record_number).contract_party_number IS NULL OR lp_tip_tbl(record_number).contract_party_number = OKC_API.G_MISS_CHAR)) THEN
2961: lx_tip_tbl(record_number). status := 'ERROR';
2962: -- neither contract_party_number nor contract_party_id are entered
2963: OKC_API.set_message(p_app_name => g_app_name,
2964: p_msg_name => 'OKC_CONTRACTS_REQUIRED_VALUE',
2965: p_token1 => 'COL_NAME',
2966: p_token1_value => 'CONTRACT_PARTY_NAME OR PARTY_ID');
2967: message :='contract_party_number or contract_party_id should be entered for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2972: ELSIF(lp_tip_tbl(record_number).contract_party_number IS NOT NULL) THEN
2973: IF(l_contract_party_number <> lp_tip_tbl(record_number).contract_party_number) THEN
2974: lx_tip_tbl(record_number). status := 'ERROR';
2975: -- contract party number entered is not valid
2976: OKC_API.set_message(p_app_name => g_app_name,
2977: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2978: p_token1 => 'COL_NAME',
2979: p_token1_value => 'CONTRACT_PARTY_NUMBER');
2980: message :='contract party number entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2986: ELSIF(lp_tip_tbl(record_number).contract_party_id IS NOT NULL) THEN
2987: IF(l_contract_party_id <> lp_tip_tbl(record_number).contract_party_id) THEN
2988: lx_tip_tbl(record_number). status := 'ERROR';
2989: -- contract party id entered is not valid
2990: OKC_API.set_message(p_app_name => g_app_name,
2991: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
2992: p_token1 => 'COL_NAME',
2993: p_token1_value => 'CONTRACT_PARTY_NUMBER');
2994: message :='contract party id entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
2999: END IF;
3000: END IF;
3001: ELSIF(l_count = 1) THEN
3002: -- if only one party is associated then assign those parties even the user not entered
3003: IF((lp_tip_tbl(record_number).contract_party_id IS NULL OR lp_tip_tbl(record_number).contract_party_id = OKC_API.G_MISS_NUM) AND
3004: (lp_tip_tbl(record_number).contract_party_number IS NULL OR lp_tip_tbl(record_number).contract_party_number = OKC_API.G_MISS_CHAR)) THEN
3005: lx_tip_tbl(record_number).qpy_id := l_qpy_id;
3006: lx_tip_tbl(record_number).contract_party_number := l_contract_party_number;
3007: lx_tip_tbl(record_number).contract_party_id := l_contract_party_id;
3000: END IF;
3001: ELSIF(l_count = 1) THEN
3002: -- if only one party is associated then assign those parties even the user not entered
3003: IF((lp_tip_tbl(record_number).contract_party_id IS NULL OR lp_tip_tbl(record_number).contract_party_id = OKC_API.G_MISS_NUM) AND
3004: (lp_tip_tbl(record_number).contract_party_number IS NULL OR lp_tip_tbl(record_number).contract_party_number = OKC_API.G_MISS_CHAR)) THEN
3005: lx_tip_tbl(record_number).qpy_id := l_qpy_id;
3006: lx_tip_tbl(record_number).contract_party_number := l_contract_party_number;
3007: lx_tip_tbl(record_number).contract_party_id := l_contract_party_id;
3008: lx_tip_tbl(record_number).contract_party_name := l_contract_party_name;
3024: ELSIF(lp_tip_tbl(record_number).contract_party_number IS NOT NULL) THEN
3025: IF(l_contract_party_number <> lp_tip_tbl(record_number).contract_party_number) THEN
3026: lx_tip_tbl(record_number). status := 'ERROR';
3027: -- contract party number entered is not valid
3028: OKC_API.set_message(p_app_name => g_app_name,
3029: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
3030: p_token1 => 'COL_NAME',
3031: p_token1_value => 'CONTRACT_PARTY_NUMBER');
3032: message :='contract party number entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
3039: ELSIF(lp_tip_tbl(record_number).contract_party_id IS NOT NULL) THEN
3040: IF(l_contract_party_id <> lp_tip_tbl(record_number).contract_party_id) THEN
3041: lx_tip_tbl(record_number). status := 'ERROR';
3042: -- contract party id entered is not valid
3043: OKC_API.set_message(p_app_name => g_app_name,
3044: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
3045: p_token1 => 'COL_NAME',
3046: p_token1_value => 'CONTRACT_PARTY_NUMBER');
3047: message :='contract party id entered not exists for transaction_number '||lx_tip_tbl(record_number).transaction_number;
3054: END IF;
3055: ELSE
3056: lx_tip_tbl(record_number).status := 'ERROR';
3057: -- not recipient
3058: OKC_API.set_message(p_app_name => g_app_name,
3059: p_msg_name => 'OKC_CONTRACTS_INVALID_VALUE',
3060: p_token1 => 'COL_NAME',
3061: p_token1_value => 'QUOTE_ROLE_CODE');
3062: message :='quote party type allowed is only RECIPIENT for transaction_number'||lx_tip_tbl(record_number).transaction_number;
3111:
3112: EXCEPTION
3113: WHEN OTHERS THEN
3114: -- store SQL error message on message stack for caller
3115: OKC_API.set_message(p_app_name => g_app_name,
3116: p_msg_name => g_unexpected_error,
3117: p_token1 => g_sqlcode_token,
3118: p_token1_value => sqlcode,
3119: p_token2 => g_sqlerrm_token,
3117: p_token1 => g_sqlcode_token,
3118: p_token1_value => sqlcode,
3119: p_token2 => g_sqlerrm_token,
3120: p_token2_value => sqlerrm);
3121: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3122: l_msg_tbl(0).msg_text := 'validate_party:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
3123: log_messages(log_msg_flag => 'V',
3124: msg_tbl => l_msg_tbl );
3125: END validate_party;
3141: x_return_status OUT NOCOPY VARCHAR2,
3142: p_tip_rec IN tip_rec_type) IS
3143: l_api_version CONSTANT NUMBER := p_api_version;
3144: l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
3145: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3146: l_msg_tbl msg_tbl_type;
3147: BEGIN
3148: x_return_status := OKC_API.G_RET_STS_SUCCESS;
3149: --Check API version, initialize message list and create savepoint.
3144: l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
3145: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3146: l_msg_tbl msg_tbl_type;
3147: BEGIN
3148: x_return_status := OKC_API.G_RET_STS_SUCCESS;
3149: --Check API version, initialize message list and create savepoint.
3150: l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3151: G_PKG_NAME,
3152: p_init_msg_list,
3146: l_msg_tbl msg_tbl_type;
3147: BEGIN
3148: x_return_status := OKC_API.G_RET_STS_SUCCESS;
3149: --Check API version, initialize message list and create savepoint.
3150: l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3151: G_PKG_NAME,
3152: p_init_msg_list,
3153: l_api_version,
3154: p_api_version,
3153: l_api_version,
3154: p_api_version,
3155: '_PVT',
3156: x_return_status);
3157: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3158: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3159: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3160: RAISE OKC_API.G_EXCEPTION_ERROR;
3161: END IF;
3154: p_api_version,
3155: '_PVT',
3156: x_return_status);
3157: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3158: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3159: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3160: RAISE OKC_API.G_EXCEPTION_ERROR;
3161: END IF;
3162: -- updates the party table
3155: '_PVT',
3156: x_return_status);
3157: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3158: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3159: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3160: RAISE OKC_API.G_EXCEPTION_ERROR;
3161: END IF;
3162: -- updates the party table
3163: UPDATE OKL_TERMNT_INTF_PTY
3156: x_return_status);
3157: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3158: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3159: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3160: RAISE OKC_API.G_EXCEPTION_ERROR;
3161: END IF;
3162: -- updates the party table
3163: UPDATE OKL_TERMNT_INTF_PTY
3164: SET
3203: OKL_TERMNT_INTF_PTY.QUOTE_ROLE_CODE = p_tip_rec.quote_role_code
3204: WHERE
3205: OKL_TERMNT_INTF_PTY.ROWID =p_tip_rec.row_id;
3206:
3207: OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3208: x_return_status := l_return_status;
3209: EXCEPTION
3210: WHEN OKC_API.G_EXCEPTION_ERROR THEN
3211: x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3206:
3207: OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3208: x_return_status := l_return_status;
3209: EXCEPTION
3210: WHEN OKC_API.G_EXCEPTION_ERROR THEN
3211: x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3212: G_PKG_NAME,
3213: 'OKC_API.G_RET_STS_ERROR',
3214: x_msg_count,
3207: OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3208: x_return_status := l_return_status;
3209: EXCEPTION
3210: WHEN OKC_API.G_EXCEPTION_ERROR THEN
3211: x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3212: G_PKG_NAME,
3213: 'OKC_API.G_RET_STS_ERROR',
3214: x_msg_count,
3215: x_msg_data,
3209: EXCEPTION
3210: WHEN OKC_API.G_EXCEPTION_ERROR THEN
3211: x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3212: G_PKG_NAME,
3213: 'OKC_API.G_RET_STS_ERROR',
3214: x_msg_count,
3215: x_msg_data,
3216: '_PVT');
3217: -- for unexpected error
3214: x_msg_count,
3215: x_msg_data,
3216: '_PVT');
3217: -- for unexpected error
3218: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3219: x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3220: G_PKG_NAME,
3221: 'OKC_API.G_RET_STS_UNEXP_ERROR',
3222: x_msg_count,
3215: x_msg_data,
3216: '_PVT');
3217: -- for unexpected error
3218: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3219: x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3220: G_PKG_NAME,
3221: 'OKC_API.G_RET_STS_UNEXP_ERROR',
3222: x_msg_count,
3223: x_msg_data,
3217: -- for unexpected error
3218: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3219: x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
3220: G_PKG_NAME,
3221: 'OKC_API.G_RET_STS_UNEXP_ERROR',
3222: x_msg_count,
3223: x_msg_data,
3224: '_PVT');
3225: WHEN OTHERS THEN
3223: x_msg_data,
3224: '_PVT');
3225: WHEN OTHERS THEN
3226: -- store SQL error message on message stack for caller
3227: OKC_API.set_message(p_app_name => g_app_name,
3228: p_msg_name => g_unexpected_error,
3229: p_token1 => g_sqlcode_token,
3230: p_token1_value => sqlcode,
3231: p_token2 => g_sqlerrm_token,
3229: p_token1 => g_sqlcode_token,
3230: p_token1_value => sqlcode,
3231: p_token2 => g_sqlerrm_token,
3232: p_token2_value => sqlerrm);
3233: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3234: l_msg_tbl(0).msg_text := 'update_party:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
3235: log_messages(log_msg_flag => 'V',
3236: msg_tbl => l_msg_tbl );
3237: END update_party;
3313: IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3314: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.select_party_info','Begin(+)');
3315: END IF;
3316: record_number:=0;
3317: x_return_status := OKC_API.G_RET_STS_SUCCESS;
3318:
3319: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3320: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.select_party_info.',
3321: 'p_tip_tbl.COUNT : '||p_tip_tbl.COUNT);
3335: || 'email address ' || p_tip_tbl(record_number).email_address);
3336: END IF;
3337:
3338: update_party(p_api_version => p_api_version
3339: ,p_init_msg_list => OKC_API.G_FALSE
3340: ,x_msg_data => x_msg_data
3341: ,x_msg_count => x_msg_count
3342: ,x_return_status => x_return_status
3343: ,p_tip_rec => p_tip_tbl(record_number));
3417:
3418: EXCEPTION
3419: WHEN OTHERS THEN
3420: -- store SQL error message on message stack for caller
3421: OKC_API.set_message(p_app_name => g_app_name,
3422: p_msg_name => g_unexpected_error,
3423: p_token1 => g_sqlcode_token,
3424: p_token1_value => sqlcode,
3425: p_token2 => g_sqlerrm_token,
3426: p_token2_value => sqlerrm);
3427: l_msg_tbl(0).msg_text := 'select_party_info:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
3428: log_messages(log_msg_flag => 'V',
3429: msg_tbl => l_msg_tbl );
3430: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3431: END select_party_info;
3432:
3433: -- Start of comments
3434: --
3496: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote',
3497: 'Begin(+)');
3498: END IF;
3499: x_group_number := 0;
3500: x_return_status := OKC_API.G_RET_STS_SUCCESS;
3501:
3502: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3503: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3504: 'p_tip_tbl count :'||p_tip_tbl.count||' p_tif_tbl count : '||p_tif_tbl.count);
3573: lp_quot_rec.qtp_code := p_tif_tbl(l_contract_record).quote_type_code;
3574: lp_quot_rec.qrs_code := p_tif_tbl(l_contract_record).quote_reason_code;
3575: OKL_AM_PARTIES_PVT.fetch_rule_quote_parties
3576: ( p_api_version => p_api_version,
3577: p_init_msg_list => OKC_API.G_FALSE,
3578: x_msg_count => x_msg_count,
3579: x_msg_data => x_msg_data,
3580: x_return_status => x_return_status,
3581: p_qtev_rec => lp_quot_rec,
3636: END IF;
3637: END LOOP;
3638: END IF;
3639: --errorout_vg('Create Quote Party status = '|| x_return_status );
3640: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3641: lx_tif_tbl.DELETE;
3642: create_quote(
3643: p_api_version => p_api_version ,
3644: p_init_msg_list => OKC_API.G_FALSE,
3640: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3641: lx_tif_tbl.DELETE;
3642: create_quote(
3643: p_api_version => p_api_version ,
3644: p_init_msg_list => OKC_API.G_FALSE,
3645: x_msg_count => x_msg_count ,
3646: x_msg_data => x_msg_data ,
3647: x_return_status => x_return_status ,
3648: p_tif_tbl => l_lessee_tif_tbl ,
3662: l_lessee_tif_tbl.delete;
3663: l_lessee_tif_tbl := lx_tif_tbl;
3664: lx_tif_tbl.delete;
3665:
3666: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) AND (l_lessee_tif_tbl(0).status = 'PROCESSED') THEN
3667: FOR i IN l_lessee_tip_tbl.FIRST..l_lessee_tip_tbl.LAST
3668: LOOP
3669: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3670: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3672: END IF;
3673: l_lessee_tip_tbl(i).status :='PROCESSED';
3674: update_party(
3675: p_api_version => p_api_version ,
3676: p_init_msg_list => OKC_API.G_FALSE ,
3677: x_msg_data => x_msg_data ,
3678: x_msg_count => x_msg_count ,
3679: x_return_status => x_return_status ,
3680: p_tip_rec => l_lessee_tip_tbl(i));
3692:
3693: l_lessee_tif_tbl(i).group_number := group_number;
3694: update_row(
3695: p_api_version => p_api_version ,
3696: p_init_msg_list => OKC_API.G_FALSE ,
3697: x_msg_data => x_msg_data ,
3698: x_msg_count => x_msg_count ,
3699: x_return_status => x_return_status ,
3700: p_tif_rec => l_lessee_tif_tbl(i));
3722:
3723: l_lessee_tip_tbl(i).status :='ERROR';
3724: update_party(
3725: p_api_version => p_api_version ,
3726: p_init_msg_list => OKC_API.G_FALSE ,
3727: x_msg_data => x_msg_data ,
3728: x_msg_count => x_msg_count ,
3729: x_return_status => x_return_status ,
3730: p_tip_rec => l_lessee_tip_tbl(i));
3775:
3776: l_lessee_tip_tbl(i).status :='ERROR';
3777: update_party(
3778: p_api_version => p_api_version ,
3779: p_init_msg_list => OKC_API.G_FALSE ,
3780: x_msg_data => x_msg_data ,
3781: x_msg_count => x_msg_count ,
3782: x_return_status => x_return_status ,
3783: p_tip_rec => l_lessee_tip_tbl(i));
3837: END LOOP;
3838: END IF;
3839:
3840: --errorout_vg('Create quote parties status = '|| x_return_status);
3841: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3842: lx_tif_tbl.DELETE;
3843: create_quote(
3844: p_api_version => p_api_version ,
3845: p_init_msg_list => OKC_API.G_FALSE,
3841: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
3842: lx_tif_tbl.DELETE;
3843: create_quote(
3844: p_api_version => p_api_version ,
3845: p_init_msg_list => OKC_API.G_FALSE,
3846: x_msg_count => x_msg_count ,
3847: x_msg_data => x_msg_data ,
3848: x_return_status => x_return_status ,
3849: p_tif_tbl => l_vendor_tif_tbl ,
3863: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3864: 'Create Quote status = ' || x_return_status );
3865: END IF;
3866:
3867: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) AND (l_vendor_tif_tbl(0).status = 'PROCESSED' ) THEN
3868: FOR i IN l_vendor_tip_tbl.FIRST..l_vendor_tip_tbl.LAST
3869: LOOP
3870: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
3871: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
3874:
3875: l_vendor_tip_tbl(i).status := 'PROCESSED';
3876: update_party(
3877: p_api_version => p_api_version ,
3878: p_init_msg_list => OKC_API.G_FALSE ,
3879: x_msg_data => x_msg_data ,
3880: x_msg_count => x_msg_count ,
3881: x_return_status => x_return_status ,
3882: p_tip_rec => l_vendor_tip_tbl(i));
3893: END IF;
3894: l_vendor_tif_tbl(i).group_number := group_number;
3895: update_row(
3896: p_api_version => p_api_version ,
3897: p_init_msg_list => OKC_API.G_FALSE ,
3898: x_msg_data => x_msg_data ,
3899: x_msg_count => x_msg_count ,
3900: x_return_status => x_return_status ,
3901: p_tif_rec => l_vendor_tif_tbl(i));
3919:
3920: l_vendor_tip_tbl(i).status := 'ERROR';
3921: update_party(
3922: p_api_version => p_api_version ,
3923: p_init_msg_list => OKC_API.G_FALSE ,
3924: x_msg_data => x_msg_data ,
3925: x_msg_count => x_msg_count ,
3926: x_return_status => x_return_status ,
3927: p_tip_rec => l_vendor_tip_tbl(i));
3961:
3962: l_vendor_tip_tbl(i).status := 'ERROR';
3963: update_party(
3964: p_api_version => p_api_version ,
3965: p_init_msg_list => OKC_API.G_FALSE ,
3966: x_msg_data => x_msg_data ,
3967: x_msg_count => x_msg_count ,
3968: x_return_status => x_return_status ,
3969: p_tip_rec => l_vendor_tip_tbl(i));
4064: lp_quot_rec.qrs_code := p_tif_tbl(record_number).quote_reason_code;
4065:
4066: OKL_AM_PARTIES_PVT.fetch_rule_quote_parties (
4067: p_api_version => p_api_version,
4068: p_init_msg_list => OKC_API.G_FALSE,
4069: x_msg_count => x_msg_count,
4070: x_msg_data => x_msg_data,
4071: x_return_status => x_return_status,
4072: p_qtev_rec => lp_quot_rec,
4130: END IF;
4131: END LOOP;
4132: END IF;
4133:
4134: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4135: lx_tif_tbl.DELETE;
4136: create_quote(
4137: p_api_version => p_api_version ,
4138: p_init_msg_list => OKC_API.G_FALSE,
4134: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4135: lx_tif_tbl.DELETE;
4136: create_quote(
4137: p_api_version => p_api_version ,
4138: p_init_msg_list => OKC_API.G_FALSE,
4139: x_msg_count => x_msg_count ,
4140: x_msg_data => x_msg_data ,
4141: x_return_status => x_return_status ,
4142: p_tif_tbl => l_lessee_tif_tbl ,
4156: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4157: 'Create Quote status = ' || x_return_status );
4158: END IF;
4159:
4160: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) AND (l_lessee_tif_tbl(0).status = 'PROCESSED' ) THEN
4161: FOR i IN l_lessee_tip_tbl.FIRST..l_lessee_tip_tbl.LAST
4162: LOOP
4163: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4164: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4168: --errorout_vg('Update Party to processed = ' || l_lessee_tip_tbl(i).transaction_number);
4169: l_lessee_tip_tbl(i).status := 'PROCESSED';
4170: update_party(
4171: p_api_version => p_api_version ,
4172: p_init_msg_list => OKC_API.G_FALSE ,
4173: x_msg_data => x_msg_data ,
4174: x_msg_count => x_msg_count ,
4175: x_return_status => x_return_status ,
4176: p_tip_rec => l_lessee_tip_tbl(i));
4188: --errorout_vg('Update row group number = ' || l_lessee_tip_tbl(i).transaction_number || 'group number =' ||group_number );
4189: l_lessee_tif_tbl(i).group_number := group_number;
4190: update_row(
4191: p_api_version => p_api_version ,
4192: p_init_msg_list => OKC_API.G_FALSE ,
4193: x_msg_data => x_msg_data ,
4194: x_msg_count => x_msg_count ,
4195: x_return_status => x_return_status ,
4196: p_tif_rec => l_lessee_tif_tbl(i));
4213:
4214: l_lessee_tip_tbl(i).status := 'ERROR';
4215: update_party(
4216: p_api_version => p_api_version ,
4217: p_init_msg_list => OKC_API.G_FALSE ,
4218: x_msg_data => x_msg_data ,
4219: x_msg_count => x_msg_count ,
4220: x_return_status => x_return_status ,
4221: p_tip_rec => l_lessee_tip_tbl(i));
4255:
4256: l_lessee_tip_tbl(i).status := 'ERROR';
4257: update_party(
4258: p_api_version => p_api_version ,
4259: p_init_msg_list => OKC_API.G_FALSE ,
4260: x_msg_data => x_msg_data ,
4261: x_msg_count => x_msg_count ,
4262: x_return_status => x_return_status ,
4263: p_tip_rec => l_lessee_tip_tbl(i));
4322: END IF;
4323: END LOOP;
4324: END IF;
4325:
4326: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4327: lx_tif_tbl.DELETE;
4328: create_quote(
4329: p_api_version => p_api_version ,
4330: p_init_msg_list => OKC_API.G_FALSE,
4326: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
4327: lx_tif_tbl.DELETE;
4328: create_quote(
4329: p_api_version => p_api_version ,
4330: p_init_msg_list => OKC_API.G_FALSE,
4331: x_msg_count => x_msg_count ,
4332: x_msg_data => x_msg_data ,
4333: x_return_status => x_return_status ,
4334: p_tif_tbl => l_vendor_tif_tbl ,
4348: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4349: 'Create Quote status = ' || x_return_status );
4350: END IF;
4351:
4352: IF(x_return_status = OKC_API.G_RET_STS_SUCCESS) AND (l_vendor_tif_tbl(0).status = 'PROCESSED' ) THEN
4353: FOR i IN l_vendor_tip_tbl.FIRST..l_vendor_tip_tbl.LAST
4354: LOOP
4355: IF (G_DEBUG_LEVEL_STATEMENT >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4356: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,'OKL_AM_TERMNT_INTERFACE_PVT.populate_party_for_quote.',
4359:
4360: l_vendor_tip_tbl(i).status := 'PROCESSED';
4361: update_party(
4362: p_api_version => p_api_version ,
4363: p_init_msg_list => OKC_API.G_FALSE ,
4364: x_msg_data => x_msg_data ,
4365: x_msg_count => x_msg_count ,
4366: x_return_status => x_return_status ,
4367: p_tip_rec => l_vendor_tip_tbl(i));
4381:
4382: l_vendor_tif_tbl(i).group_number := group_number;
4383: update_row(
4384: p_api_version => p_api_version ,
4385: p_init_msg_list => OKC_API.G_FALSE ,
4386: x_msg_data => x_msg_data ,
4387: x_msg_count => x_msg_count ,
4388: x_return_status => x_return_status ,
4389: p_tif_rec => l_vendor_tif_tbl(i));
4407:
4408: l_vendor_tip_tbl(i).status := 'ERROR';
4409: update_party(
4410: p_api_version => p_api_version ,
4411: p_init_msg_list => OKC_API.G_FALSE ,
4412: x_msg_data => x_msg_data ,
4413: x_msg_count => x_msg_count ,
4414: x_return_status => x_return_status ,
4415: p_tip_rec => l_vendor_tip_tbl(i));
4448:
4449: l_vendor_tip_tbl(i).status := 'ERROR';
4450: update_party(
4451: p_api_version => p_api_version ,
4452: p_init_msg_list => OKC_API.G_FALSE ,
4453: x_msg_data => x_msg_data ,
4454: x_msg_count => x_msg_count ,
4455: x_return_status => x_return_status ,
4456: p_tip_rec => l_vendor_tip_tbl(i));
4492:
4493: EXCEPTION
4494: WHEN OTHERS THEN
4495: -- store SQL error message on message stack for caller
4496: OKC_API.set_message(
4497: p_app_name => g_app_name,
4498: p_msg_name => g_unexpected_error,
4499: p_token1 => g_sqlcode_token,
4500: p_token1_value => SQLCODE,
4502: p_token2_value => sqlerrm);
4503:
4504: l_msg_tbl(0).msg_text := 'populate_party_for_quote:ended with unexpected error sqlcode: '||SQLCODE||' sqlerrm: '||sqlerrm;
4505: log_messages(log_msg_flag => 'V', msg_tbl => l_msg_tbl );
4506: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4507: END populate_party_for_quote;
4508: /*PARTY_LOGIC END*/
4509:
4510:
4587: ORDER BY CONTRACT_NUMBER,TRANSACTION_NUMBER; --sechawla 02-Aug-2011 12745559 : added transaction_number
4588:
4589: l_api_version CONSTANT NUMBER := 1;
4590: l_api_name CONSTANT VARCHAR2(30) := 'terminate_interface';
4591: l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4592: record_number NUMBER;
4593: x_installbase_id NUMBER;
4594: l_sys_date DATE;
4595: l_tif_tbl tif_tbl_type;
4604: IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4605: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface','Begin(+)');
4606: END IF;
4607:
4608: x_return_status := OKC_API.G_RET_STS_SUCCESS;
4609: --Check API version, initialize message list and create savepoint.
4610: l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4611: G_PKG_NAME,
4612: p_init_msg_list,
4606: END IF;
4607:
4608: x_return_status := OKC_API.G_RET_STS_SUCCESS;
4609: --Check API version, initialize message list and create savepoint.
4610: l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4611: G_PKG_NAME,
4612: p_init_msg_list,
4613: l_api_version,
4614: p_api_version,
4613: l_api_version,
4614: p_api_version,
4615: '_PVT',
4616: x_return_status);
4617: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4618: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4619: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4620: RAISE OKC_API.G_EXCEPTION_ERROR;
4621: END IF;
4614: p_api_version,
4615: '_PVT',
4616: x_return_status);
4617: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4618: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4619: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4620: RAISE OKC_API.G_EXCEPTION_ERROR;
4621: END IF;
4622: -- this retrives the sysdate and passes to a local variable
4615: '_PVT',
4616: x_return_status);
4617: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4618: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4619: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4620: RAISE OKC_API.G_EXCEPTION_ERROR;
4621: END IF;
4622: -- this retrives the sysdate and passes to a local variable
4623: SELECT trunc(SYSDATE) INTO l_sys_date FROM DUAL;
4616: x_return_status);
4617: IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4618: RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4619: ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4620: RAISE OKC_API.G_EXCEPTION_ERROR;
4621: END IF;
4622: -- this retrives the sysdate and passes to a local variable
4623: SELECT trunc(SYSDATE) INTO l_sys_date FROM DUAL;
4624: record_number:=0;
4698: end if;
4699:
4700: -- validates quote_type and quote_reason and populates WHO columns
4701: validate_quote_type_and_reason(p_api_version => l_api_version,
4702: p_init_msg_list => OKC_API.G_TRUE,
4703: x_msg_count => x_msg_count,
4704: x_msg_data => x_msg_data,
4705: x_return_status => x_return_status,
4706: p_tif_tbl => l_tif_tbl,
4719: end if;
4720:
4721: -- validates contract and asset details entered, populates the remaining calls required
4722: validate_transaction(p_api_version => l_api_version,
4723: p_init_msg_list => OKC_API.G_FALSE,
4724: x_msg_count => x_msg_count,
4725: x_msg_data => x_msg_data,
4726: x_return_status => x_return_status,
4727: p_tif_tbl => l_tif_tbl,
4747: END IF;
4748: */
4749: -- updates the values in database and gets records with status='WORKING'
4750: change_status(p_api_version => l_api_version,
4751: p_init_msg_list => OKC_API.G_FALSE,
4752: x_msg_count => x_msg_count,
4753: x_msg_data => x_msg_data,
4754: x_return_status => x_return_status,
4755: p_tif_tbl => l_tif_tbl,
4779: 'Before select_party_info : '||l_tip_tbl.count || 'l_tif_tbl count'|| l_tif_tbl.count);
4780: END IF;
4781:
4782: select_party_info(p_api_version => l_api_version,
4783: p_init_msg_list => OKC_API.G_FALSE,
4784: x_msg_count => x_msg_count,
4785: x_msg_data => x_msg_data,
4786: x_return_status => x_return_status,
4787: p_tip_tbl => l_tip_tbl,
4808: l_tip_tbl := lx_tip_tbl;
4809: lx_tip_tbl.DELETE;
4810: -- validates party information
4811: validate_party(p_api_version => l_api_version,
4812: p_init_msg_list => OKC_API.G_FALSE,
4813: x_msg_count => x_msg_count,
4814: x_msg_data => x_msg_data,
4815: x_return_status => x_return_status,
4816: p_tif_tbl => l_tif_tbl,
4837: l_tif_tbl := lx_tif_tbl;
4838: lx_tif_tbl.DELETE;
4839: -- updates values in database and gets records with status='WORKING'
4840: select_party_info(p_api_version => l_api_version,
4841: p_init_msg_list => OKC_API.G_FALSE,
4842: x_msg_count => x_msg_count,
4843: x_msg_data => x_msg_data,
4844: x_return_status => x_return_status,
4845: p_tip_tbl => l_tip_tbl,
4861: l_tip_tbl := lx_tip_tbl;
4862: lx_tip_tbl.DELETE;
4863: -- populates the fields required for quote and creates quote for entries in party table
4864: populate_party_for_quote(p_api_version => l_api_version,
4865: p_init_msg_list => OKC_API.G_FALSE,
4866: x_msg_count => x_msg_count,
4867: x_msg_data => x_msg_data,
4868: x_return_status => x_return_status,
4869: p_tip_tbl => l_tip_tbl,
4891: l_tif_tbl := lx_tif_tbl;
4892: lx_tif_tbl.DELETE;
4893: -- updates the values to database and removes the duplicates while grouping
4894: remove_duplicates(p_api_version => l_api_version,
4895: p_init_msg_list => OKC_API.G_FALSE,
4896: x_msg_count => x_msg_count,
4897: x_msg_data => x_msg_data,
4898: x_return_status => x_return_status,
4899: p_tif_tbl => l_tif_tbl,
4908: l_tif_tbl := lx_tif_tbl;
4909: lx_tif_tbl.DELETE;
4910: -- creates groups and calls create_quote
4911: populate_quote(p_api_version => l_api_version,
4912: p_init_msg_list => OKC_API.G_FALSE,
4913: x_msg_count => x_msg_count,
4914: x_msg_data => x_msg_data,
4915: x_return_status => x_return_status,
4916: p_tif_tbl => l_tif_tbl,
4925: IF (G_DEBUG_LEVEL_PROCEDURE >= G_DEBUG_CURRENT_RUNTIME_LEVEL) THEN
4926: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,'OKL_AM_TERMNT_INTERFACE_PVT.termination_interface ','End(-)');
4927: END IF;
4928:
4929: OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4930: log_messages(log_msg_flag => 'Y',
4931: p_quote_number => tot_rec_processed,
4932: msg_tbl => l_msg_tbl );
4933: log_messages(log_msg_flag => 'O',
4933: log_messages(log_msg_flag => 'O',
4934: p_quote_number => tot_rec_processed,
4935: msg_tbl => l_msg_tbl );
4936: EXCEPTION
4937: WHEN OKC_API.G_EXCEPTION_ERROR THEN
4938: x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
4939: G_PKG_NAME,
4940: 'OKC_API.G_RET_STS_ERROR',
4941: x_msg_count,
4934: p_quote_number => tot_rec_processed,
4935: msg_tbl => l_msg_tbl );
4936: EXCEPTION
4937: WHEN OKC_API.G_EXCEPTION_ERROR THEN
4938: x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
4939: G_PKG_NAME,
4940: 'OKC_API.G_RET_STS_ERROR',
4941: x_msg_count,
4942: x_msg_data,
4936: EXCEPTION
4937: WHEN OKC_API.G_EXCEPTION_ERROR THEN
4938: x_return_status := OKC_API.HANDLE_EXCEPTIONS(l_api_name,
4939: G_PKG_NAME,
4940: 'OKC_API.G_RET_STS_ERROR',
4941: x_msg_count,
4942: x_msg_data,
4943: '_PVT');
4944: -- unexpected error
4941: x_msg_count,
4942: x_msg_data,
4943: '_PVT');
4944: -- unexpected error
4945: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4946: x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
4947: G_PKG_NAME,
4948: 'OKC_API.G_RET_STS_UNEXP_ERROR',
4949: x_msg_count,
4942: x_msg_data,
4943: '_PVT');
4944: -- unexpected error
4945: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4946: x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
4947: G_PKG_NAME,
4948: 'OKC_API.G_RET_STS_UNEXP_ERROR',
4949: x_msg_count,
4950: x_msg_data,
4944: -- unexpected error
4945: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4946: x_return_status :=OKC_API.HANDLE_EXCEPTIONS(l_api_name,
4947: G_PKG_NAME,
4948: 'OKC_API.G_RET_STS_UNEXP_ERROR',
4949: x_msg_count,
4950: x_msg_data,
4951: '_PVT');
4952: WHEN OTHERS THEN
4950: x_msg_data,
4951: '_PVT');
4952: WHEN OTHERS THEN
4953: -- store SQL error message on message stack for caller
4954: OKC_API.set_message(p_app_name => g_app_name,
4955: p_msg_name => g_unexpected_error,
4956: p_token1 => g_sqlcode_token,
4957: p_token1_value => sqlcode,
4958: p_token2 => g_sqlerrm_token,
4956: p_token1 => g_sqlcode_token,
4957: p_token1_value => sqlcode,
4958: p_token2 => g_sqlerrm_token,
4959: p_token2_value => sqlerrm);
4960: x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4961: l_msg_tbl(0).msg_text := 'termination_interface:ended with unexpected error sqlcode: '||sqlcode||' sqlerrm: '||sqlerrm;
4962: log_messages(log_msg_flag => 'Y',
4963: p_quote_number => tot_rec_processed,
4964: msg_tbl => l_msg_tbl );