[Home] [Help]
402: * p_cust_account_rec Customer account record.
403: * p_rowid Rowid of the record (used only in update mode).
404: * IN/OUT:
405: * x_return_status Return status after the call. The status can
406: * be FND_API.G_RET_STS_SUCCESS (success),
407: * FND_API.G_RET_STS_ERROR (error),
408: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
409: *
410: * NOTES
403: * p_rowid Rowid of the record (used only in update mode).
404: * IN/OUT:
405: * x_return_status Return status after the call. The status can
406: * be FND_API.G_RET_STS_SUCCESS (success),
407: * FND_API.G_RET_STS_ERROR (error),
408: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
409: *
410: * NOTES
411: *
404: * IN/OUT:
405: * x_return_status Return status after the call. The status can
406: * be FND_API.G_RET_STS_SUCCESS (success),
407: * FND_API.G_RET_STS_ERROR (error),
408: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
409: *
410: * NOTES
411: *
412: * MODIFICATION HISTORY
529: -- If primary key value is not passed, it will be generated
530: -- from sequence by table handler.
531:
532: IF p_cust_account_rec.cust_account_id IS NOT NULL AND
533: p_cust_account_rec.cust_account_id <> FND_API.G_MISS_NUM
534: THEN
535: BEGIN
536: SELECT 'Y' INTO l_dummy
537: FROM HZ_CUST_ACCOUNTS
539:
540: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
541: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id' );
542: FND_MSG_PUB.ADD;
543: x_return_status := FND_API.G_RET_STS_ERROR;
544: EXCEPTION
545: WHEN NO_DATA_FOUND THEN
546: NULL;
547: END;
591: --END IF;
592:
593: EXCEPTION
594: WHEN NO_DATA_FOUND THEN
595: x_return_status := FND_API.G_RET_STS_ERROR;
596: END ;
597:
598: /* IF G_DEBUG THEN
599: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
613: -- value, error out;
614:
615: IF l_profile = 'Y' THEN
616: IF p_cust_account_rec.account_number IS NOT NULL AND
617: p_cust_account_rec.account_number <> FND_API.G_MISS_CHAR
618: THEN
619: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_ACCOUNT_NUMBER_AUTO_ON' );
620: FND_MSG_PUB.ADD;
621: x_return_status := FND_API.G_RET_STS_ERROR;
617: p_cust_account_rec.account_number <> FND_API.G_MISS_CHAR
618: THEN
619: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_ACCOUNT_NUMBER_AUTO_ON' );
620: FND_MSG_PUB.ADD;
621: x_return_status := FND_API.G_RET_STS_ERROR;
622: END IF;
623:
624: -- the account_number will be generated from sequence by table handler.
625:
638:
639: ELSIF l_profile = 'N' /* Bug 5118546 */
640: THEN
641: IF p_cust_account_rec.account_number IS NULL OR
642: p_cust_account_rec.account_number = FND_API.G_MISS_CHAR
643: THEN
644: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
645: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'account_number' );
646: FND_MSG_PUB.ADD;
643: THEN
644: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
645: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'account_number' );
646: FND_MSG_PUB.ADD;
647: x_return_status := FND_API.G_RET_STS_ERROR;
648: END IF;
649:
650: /*IF G_DEBUG THEN
651: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
717: END IF;
718:
719: -- account_number is unique.
720: IF p_cust_account_rec.account_number IS NOT NULL AND
721: p_cust_account_rec.account_number <> FND_API.G_MISS_CHAR
722: THEN
723: BEGIN
724: SELECT ROWID INTO l_rowid
725: FROM HZ_CUST_ACCOUNTS
730: THEN
731: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
732: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'account_number' );
733: FND_MSG_PUB.ADD;
734: x_return_status := FND_API.G_RET_STS_ERROR;
735: END IF;
736: EXCEPTION
737: WHEN NO_DATA_FOUND THEN
738: NULL;
781: -- during creation.
782:
783: IF p_create_update_flag = 'C' AND
784: p_cust_account_rec.orig_system_reference IS NOT NULL AND
785: p_cust_account_rec.orig_system_reference <> FND_API.G_MISS_CHAR
786: THEN
787: BEGIN
788: SELECT 'Y' INTO l_dummy
789: FROM HZ_CUST_ACCOUNTS
791:
792: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
793: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'orig_system_reference' );
794: FND_MSG_PUB.ADD;
795: x_return_status := FND_API.G_RET_STS_ERROR;
796: EXCEPTION
797: WHEN NO_DATA_FOUND THEN
798: NULL;
799: END;
823: else
824: l_orig_system_reference1 := p_cust_account_rec.orig_system_reference;
825: end if;
826:
827: IF (p_cust_account_rec.orig_system is not null and p_cust_account_rec.orig_system <>fnd_api.g_miss_char)
828: and (p_cust_account_rec.orig_system_reference is not null and p_cust_account_rec.orig_system_reference <>fnd_api.g_miss_char)
829: and p_create_update_flag = 'U'
830: then
831: hz_orig_system_ref_pub.get_owner_table_id
824: l_orig_system_reference1 := p_cust_account_rec.orig_system_reference;
825: end if;
826:
827: IF (p_cust_account_rec.orig_system is not null and p_cust_account_rec.orig_system <>fnd_api.g_miss_char)
828: and (p_cust_account_rec.orig_system_reference is not null and p_cust_account_rec.orig_system_reference <>fnd_api.g_miss_char)
829: and p_create_update_flag = 'U'
830: then
831: hz_orig_system_ref_pub.get_owner_table_id
832: (p_orig_system => p_cust_account_rec.orig_system,
834: p_owner_table_name => 'HZ_CUST_ACCOUNTS',
835: x_owner_table_id => l_mosr_owner_table_id,
836: x_return_status => l_temp_return_status);
837:
838: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
839: l_mosr_owner_table_id= nvl(p_cust_account_rec.cust_account_id,l_mosr_owner_table_id))
840: THEN
841: l_validate_flag := 'N';
842: END IF;
844: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
845: -- x_return_status. Set x_return_status to error, ONLY if there is error.
846: -- In case of success, leave it to carry over previous value as before this call.
847: -- Fix for Bug 5498116 (29-AUG-2006)
848: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
849: x_return_status := l_temp_return_status;
850: END IF;
851:
852: END IF;
922: /****Logical APIs - validation not required****/
923: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
924: -- status is lookup code in lookup type CODE_STATUS
925: IF p_cust_account_rec.status IS NOT NULL AND
926: p_cust_account_rec.status <> FND_API.G_MISS_CHAR AND
927: ( p_create_update_flag = 'C' OR
928: ( p_create_update_flag = 'U' AND
929: p_cust_account_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
930: THEN
925: IF p_cust_account_rec.status IS NOT NULL AND
926: p_cust_account_rec.status <> FND_API.G_MISS_CHAR AND
927: ( p_create_update_flag = 'C' OR
928: ( p_create_update_flag = 'U' AND
929: p_cust_account_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
930: THEN
931: validate_lookup (
932: p_column => 'status',
933: p_lookup_type => 'CODE_STATUS',
969: --------------------------------------
970:
971: -- customer_type is lookup code in lookup type CUSTOMER_TYPE
972: IF p_cust_account_rec.customer_type IS NOT NULL AND
973: p_cust_account_rec.customer_type <> FND_API.G_MISS_CHAR AND
974: ( p_create_update_flag = 'C' OR
975: ( p_create_update_flag = 'U' AND
976: p_cust_account_rec.customer_type <> NVL( l_customer_type, FND_API.G_MISS_CHAR ) ) )
977: THEN
972: IF p_cust_account_rec.customer_type IS NOT NULL AND
973: p_cust_account_rec.customer_type <> FND_API.G_MISS_CHAR AND
974: ( p_create_update_flag = 'C' OR
975: ( p_create_update_flag = 'U' AND
976: p_cust_account_rec.customer_type <> NVL( l_customer_type, FND_API.G_MISS_CHAR ) ) )
977: THEN
978: validate_lookup (
979: p_column => 'customer_type',
980: p_lookup_type => 'CUSTOMER_TYPE',
1013: --------------------------------------
1014:
1015: -- sales_channel_code is lookup code in lookup type SALES_CHANNEL in so_lookups
1016: IF p_cust_account_rec.sales_channel_code IS NOT NULL AND
1017: p_cust_account_rec.sales_channel_code <> FND_API.G_MISS_CHAR AND
1018: ( p_create_update_flag = 'C' OR
1019: ( p_create_update_flag = 'U' AND
1020: p_cust_account_rec.sales_channel_code <> NVL( l_sales_channel_code, FND_API.G_MISS_CHAR ) ) )
1021: THEN
1016: IF p_cust_account_rec.sales_channel_code IS NOT NULL AND
1017: p_cust_account_rec.sales_channel_code <> FND_API.G_MISS_CHAR AND
1018: ( p_create_update_flag = 'C' OR
1019: ( p_create_update_flag = 'U' AND
1020: p_cust_account_rec.sales_channel_code <> NVL( l_sales_channel_code, FND_API.G_MISS_CHAR ) ) )
1021: THEN
1022: validate_lookup (
1023: p_column => 'sales_channel_code',
1024: p_lookup_table => 'SO_LOOKUPS',
1058: --------------------------------------
1059:
1060: -- freight_term is lookup code in lookup type FREIGHT_TERMS in so_lookups
1061: IF p_cust_account_rec.freight_term IS NOT NULL AND
1062: p_cust_account_rec.freight_term <> FND_API.G_MISS_CHAR AND
1063: ( p_create_update_flag = 'C' OR
1064: ( p_create_update_flag = 'U' AND
1065: p_cust_account_rec.freight_term <> NVL( l_freight_term, FND_API.G_MISS_CHAR ) ) )
1066: THEN
1061: IF p_cust_account_rec.freight_term IS NOT NULL AND
1062: p_cust_account_rec.freight_term <> FND_API.G_MISS_CHAR AND
1063: ( p_create_update_flag = 'C' OR
1064: ( p_create_update_flag = 'U' AND
1065: p_cust_account_rec.freight_term <> NVL( l_freight_term, FND_API.G_MISS_CHAR ) ) )
1066: THEN
1067: validate_lookup (
1068: p_column => 'freight_term',
1069: p_lookup_table => 'SO_LOOKUPS',
1105: /****Logical APIs - validation not required****/
1106: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1107: -- tax_header_level_flag is lookup code in lookup type YES/NO
1108: IF p_cust_account_rec.tax_header_level_flag IS NOT NULL AND
1109: p_cust_account_rec.tax_header_level_flag <> FND_API.G_MISS_CHAR
1110: THEN
1111: validate_lookup (
1112: p_column => 'tax_header_level_flag',
1113: p_lookup_type => 'YES/NO',
1148:
1149: -- Bug 4713150 : tax_code is now validated against the view zx_output_classifications_v
1150: /*
1151: IF p_cust_account_rec.tax_code IS NOT NULL AND
1152: p_cust_account_rec.tax_code <> FND_API.G_MISS_CHAR
1153: THEN
1154: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1155: FND_MESSAGE.SET_TOKEN('COLUMN','TAX_CODE');
1156: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1154: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1155: FND_MESSAGE.SET_TOKEN('COLUMN','TAX_CODE');
1156: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1157: FND_MSG_PUB.ADD;
1158: x_return_status := FND_API.G_RET_STS_ERROR;
1159: END IF;
1160: */
1161:
1162: IF p_cust_account_rec.tax_code is NOT NULL AND
1159: END IF;
1160: */
1161:
1162: IF p_cust_account_rec.tax_code is NOT NULL AND
1163: p_cust_account_rec.tax_code <> FND_API.G_MISS_CHAR AND
1164: ( p_create_update_flag = 'C' OR
1165: ( p_create_update_flag = 'U' AND
1166: p_cust_account_rec.tax_code <> NVL( l_tax_code, FND_API.G_MISS_CHAR ) ) )
1167: THEN
1162: IF p_cust_account_rec.tax_code is NOT NULL AND
1163: p_cust_account_rec.tax_code <> FND_API.G_MISS_CHAR AND
1164: ( p_create_update_flag = 'C' OR
1165: ( p_create_update_flag = 'U' AND
1166: p_cust_account_rec.tax_code <> NVL( l_tax_code, FND_API.G_MISS_CHAR ) ) )
1167: THEN
1168: check_tax_code(
1169: p_column => 'tax_code',
1170: p_column_value => p_cust_account_rec.tax_code,
1190: ----------------------------------------------------------
1191: /* 3456489. Order type is no longer set at the Customer Account Level. */
1192:
1193: IF p_cust_account_rec.order_type_id IS NOT NULL AND
1194: p_cust_account_rec.order_type_id <> FND_API.G_MISS_NUM
1195: THEN
1196: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1197: FND_MESSAGE.SET_TOKEN('COLUMN','ORDER_TYPE_ID');
1198: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1196: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1197: FND_MESSAGE.SET_TOKEN('COLUMN','ORDER_TYPE_ID');
1198: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1199: FND_MSG_PUB.ADD;
1200: x_return_status := FND_API.G_RET_STS_ERROR;
1201: END IF;
1202:
1203:
1204: /*
1204: /*
1205: | --order_type_id should be a valid value defined in OE_ORDER_TYPES_V
1206: |
1207: | IF p_cust_account_rec.order_type_id is NOT NULL AND
1208: | p_cust_account_rec.order_type_id <> FND_API.G_MISS_NUM AND
1209: | ( p_create_update_flag = 'C' OR
1210: | ( p_create_update_flag = 'U' AND
1211: | p_cust_account_rec.order_type_id <> NVL( l_order_type_id, FND_API.G_MISS_NUM ) ) )
1212: | THEN
1207: | IF p_cust_account_rec.order_type_id is NOT NULL AND
1208: | p_cust_account_rec.order_type_id <> FND_API.G_MISS_NUM AND
1209: | ( p_create_update_flag = 'C' OR
1210: | ( p_create_update_flag = 'U' AND
1211: | p_cust_account_rec.order_type_id <> NVL( l_order_type_id, FND_API.G_MISS_NUM ) ) )
1212: | THEN
1213: | check_ord_type(
1214: | p_column => 'order_type_id',
1215: | p_column_value => p_cust_account_rec.order_type_id,
1246: ----------------------------------------------------------
1247: /* 3456489. Sales Rep is no longer set at the Customer Account Level. */
1248:
1249: IF p_cust_account_rec.primary_salesrep_id IS NOT NULL AND
1250: p_cust_account_rec.primary_salesrep_id <> FND_API.G_MISS_NUM
1251: THEN
1252: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1253: FND_MESSAGE.SET_TOKEN('COLUMN','PRIMARY_SALESREP_ID');
1254: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1252: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1253: FND_MESSAGE.SET_TOKEN('COLUMN','PRIMARY_SALESREP_ID');
1254: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1255: FND_MSG_PUB.ADD;
1256: x_return_status := FND_API.G_RET_STS_ERROR;
1257: END IF;
1258:
1259: /*
1260: | --Primary_salesrep_id should be a valid value defined in RA_SALESREPS
1259: /*
1260: | --Primary_salesrep_id should be a valid value defined in RA_SALESREPS
1261: |
1262: | IF p_cust_account_rec.primary_salesrep_id is NOT NULL AND
1263: | p_cust_account_rec.primary_salesrep_id <> FND_API.G_MISS_NUM
1264: | AND ( p_create_update_flag = 'C' OR
1265: | ( p_create_update_flag = 'U' AND
1266: | p_cust_account_rec.primary_salesrep_id <> NVL( l_primary_salesrep_id, FND_API.G_MISS_NUM ) ) )
1267: | THEN
1262: | IF p_cust_account_rec.primary_salesrep_id is NOT NULL AND
1263: | p_cust_account_rec.primary_salesrep_id <> FND_API.G_MISS_NUM
1264: | AND ( p_create_update_flag = 'C' OR
1265: | ( p_create_update_flag = 'U' AND
1266: | p_cust_account_rec.primary_salesrep_id <> NVL( l_primary_salesrep_id, FND_API.G_MISS_NUM ) ) )
1267: | THEN
1268: | check_prim_salesrep(
1269: | p_column => 'primary_salesrep_id',
1270: | p_column_value => p_cust_account_rec.primary_salesrep_id,
1303: -------------------------------------------------------
1304: -- ship_partial is a lookup code in lookup type YES/NO
1305:
1306: IF p_cust_account_rec.ship_partial IS NOT NULL AND
1307: p_cust_account_rec.ship_partial <> FND_API.G_MISS_CHAR
1308: AND ( p_create_update_flag = 'C' OR
1309: ( p_create_update_flag = 'U' AND
1310: p_cust_account_rec.ship_partial <> NVL( l_ship_partial, FND_API.G_MISS_CHAR) ) )
1311: THEN
1306: IF p_cust_account_rec.ship_partial IS NOT NULL AND
1307: p_cust_account_rec.ship_partial <> FND_API.G_MISS_CHAR
1308: AND ( p_create_update_flag = 'C' OR
1309: ( p_create_update_flag = 'U' AND
1310: p_cust_account_rec.ship_partial <> NVL( l_ship_partial, FND_API.G_MISS_CHAR) ) )
1311: THEN
1312: validate_lookup (
1313: p_column => 'ship_partial',
1314: p_lookup_type => 'YES/NO',
1336: -----------------------------------------------------------
1337: --tax_rounding_rule is a lookup_code in lookup type TAX_ROUNDING_RULE
1338:
1339: IF p_cust_account_rec.tax_rounding_rule is NOT NULL AND
1340: p_cust_account_rec.tax_rounding_rule <> FND_API.G_MISS_CHAR
1341: AND ( p_create_update_flag = 'C' OR
1342: ( p_create_update_flag = 'U' AND
1343: p_cust_account_rec.tax_rounding_rule <> NVL( l_tax_rounding_rule, FND_API.G_MISS_CHAR) ) )
1344: THEN
1339: IF p_cust_account_rec.tax_rounding_rule is NOT NULL AND
1340: p_cust_account_rec.tax_rounding_rule <> FND_API.G_MISS_CHAR
1341: AND ( p_create_update_flag = 'C' OR
1342: ( p_create_update_flag = 'U' AND
1343: p_cust_account_rec.tax_rounding_rule <> NVL( l_tax_rounding_rule, FND_API.G_MISS_CHAR) ) )
1344: THEN
1345: validate_lookup(
1346: p_column =>'tax_rounding_rule',
1347: p_lookup_type =>'TAX_ROUNDING_RULE',
1380: -------------------------------------------------------
1381: --Customer_class_code is a lookup_code in lookup type CUSTOMER_CLASS
1382:
1383: IF p_cust_account_rec.customer_class_code is NOT NULL AND
1384: p_cust_account_rec.customer_class_code <> FND_API.G_MISS_CHAR
1385: AND ( p_create_update_flag = 'C' OR
1386: ( p_create_update_flag = 'U' AND
1387: p_cust_account_rec.customer_class_code <> NVL( l_customer_class_code, FND_API.G_MISS_CHAR) ) )
1388: THEN
1383: IF p_cust_account_rec.customer_class_code is NOT NULL AND
1384: p_cust_account_rec.customer_class_code <> FND_API.G_MISS_CHAR
1385: AND ( p_create_update_flag = 'C' OR
1386: ( p_create_update_flag = 'U' AND
1387: p_cust_account_rec.customer_class_code <> NVL( l_customer_class_code, FND_API.G_MISS_CHAR) ) )
1388: THEN
1389: validate_lookup(
1390: p_column =>'customer_class_code',
1391: p_lookup_type =>'CUSTOMER CLASS',
1422: ---------------------------------------------------
1423: --Invoice_quantity_rule is lookup_code in lookup type INVOICE_BASIS
1424:
1425: IF p_cust_account_rec.invoice_quantity_rule is NOT NULL AND
1426: p_cust_account_rec.invoice_quantity_rule <> FND_API.G_MISS_CHAR
1427: AND ( p_create_update_flag = 'C' OR
1428: ( p_create_update_flag = 'U' AND
1429: p_cust_account_rec.invoice_quantity_rule <> NVL( l_invoice_quantity_rule, FND_API.G_MISS_CHAR) ) )
1430: THEN
1425: IF p_cust_account_rec.invoice_quantity_rule is NOT NULL AND
1426: p_cust_account_rec.invoice_quantity_rule <> FND_API.G_MISS_CHAR
1427: AND ( p_create_update_flag = 'C' OR
1428: ( p_create_update_flag = 'U' AND
1429: p_cust_account_rec.invoice_quantity_rule <> NVL( l_invoice_quantity_rule, FND_API.G_MISS_CHAR) ) )
1430: THEN
1431: validate_lookup(
1432: p_column =>'invoice_quantity_rule',
1433: p_lookup_table =>'OE_LOOKUPS',
1467: ----------------------------------------------------------
1468: --price_list_id should be a valid value defined in SO_PRICE_LISTS
1469:
1470: IF p_cust_account_rec.price_list_id is NOT NULL AND
1471: p_cust_account_rec.price_list_id <> FND_API.G_MISS_NUM
1472: AND ( p_create_update_flag = 'C' OR
1473: ( p_create_update_flag = 'U' AND
1474: p_cust_account_rec.price_list_id <> NVL( l_price_list_id, FND_API.G_MISS_NUM) ) )
1475: THEN
1470: IF p_cust_account_rec.price_list_id is NOT NULL AND
1471: p_cust_account_rec.price_list_id <> FND_API.G_MISS_NUM
1472: AND ( p_create_update_flag = 'C' OR
1473: ( p_create_update_flag = 'U' AND
1474: p_cust_account_rec.price_list_id <> NVL( l_price_list_id, FND_API.G_MISS_NUM) ) )
1475: THEN
1476: check_price_list_fk(
1477: p_column => 'price_list_id',
1478: p_column_value => p_cust_account_rec.price_list_id,
1508: ----------------------------------------------------------
1509: --fob_point is lookup_code in lookup type FOB
1510:
1511: IF p_cust_account_rec.fob_point is NOT NULL AND
1512: p_cust_account_rec.fob_point <> FND_API.G_MISS_CHAR
1513: AND ( p_create_update_flag = 'C' OR
1514: ( p_create_update_flag = 'U' AND
1515: p_cust_account_rec.fob_point <> NVL( l_fob_point, FND_API.G_MISS_CHAR) ) )
1516: THEN
1511: IF p_cust_account_rec.fob_point is NOT NULL AND
1512: p_cust_account_rec.fob_point <> FND_API.G_MISS_CHAR
1513: AND ( p_create_update_flag = 'C' OR
1514: ( p_create_update_flag = 'U' AND
1515: p_cust_account_rec.fob_point <> NVL( l_fob_point, FND_API.G_MISS_CHAR) ) )
1516: THEN
1517: validate_lookup(
1518: p_column =>'fob_point',
1519: p_lookup_type =>'FOB',
1551: ----------------------------------------------------------
1552: --item_cross_ref_pref should be a value defined in MTL_CROSS_REFERENCE_TYPES or should have value 'INT' or 'CUST'
1553:
1554: IF p_cust_account_rec.item_cross_ref_pref is NOT NULL AND
1555: p_cust_account_rec.item_cross_ref_pref <> FND_API.G_MISS_CHAR
1556: AND ( p_create_update_flag = 'C' OR
1557: ( p_create_update_flag = 'U' AND
1558: p_cust_account_rec.item_cross_ref_pref <> NVL( l_item_cross_ref_pref, FND_API.G_MISS_CHAR) ) )
1559: THEN
1554: IF p_cust_account_rec.item_cross_ref_pref is NOT NULL AND
1555: p_cust_account_rec.item_cross_ref_pref <> FND_API.G_MISS_CHAR
1556: AND ( p_create_update_flag = 'C' OR
1557: ( p_create_update_flag = 'U' AND
1558: p_cust_account_rec.item_cross_ref_pref <> NVL( l_item_cross_ref_pref, FND_API.G_MISS_CHAR) ) )
1559: THEN
1560: IF p_cust_account_rec.item_cross_ref_pref NOT IN('INT','CUST')
1561: THEN
1562: check_item_cross_ref(
1596: ----------------------------------------------------------
1597: --warehouse_id should be a value defined in ORG_ORGANIZATION_DEFINITIONS
1598:
1599: IF p_cust_account_rec.warehouse_id is NOT NULL AND
1600: p_cust_account_rec.warehouse_id <> FND_API.G_MISS_NUM
1601: AND ( p_create_update_flag = 'C' OR
1602: ( p_create_update_flag = 'U' AND
1603: p_cust_account_rec.warehouse_id <> NVL( l_warehouse_id, FND_API.G_MISS_NUM) ) )
1604: THEN
1599: IF p_cust_account_rec.warehouse_id is NOT NULL AND
1600: p_cust_account_rec.warehouse_id <> FND_API.G_MISS_NUM
1601: AND ( p_create_update_flag = 'C' OR
1602: ( p_create_update_flag = 'U' AND
1603: p_cust_account_rec.warehouse_id <> NVL( l_warehouse_id, FND_API.G_MISS_NUM) ) )
1604: THEN
1605: check_warehouse(
1606: p_column => 'warehouse_id',
1607: p_column_value => p_cust_account_rec.warehouse_id,
1639: ----------------------------------------------------------
1640: --date_type_preference is a lookup_code in lookup_type REQUEST_DATE_TYPE in oe_lookups
1641:
1642: IF p_cust_account_rec.date_type_preference is NOT NULL AND
1643: p_cust_account_rec.date_type_preference <> FND_API.G_MISS_CHAR
1644: AND ( p_create_update_flag = 'C' OR
1645: ( p_create_update_flag = 'U' AND
1646: p_cust_account_rec.date_type_preference <> NVL( l_date_type_preference, FND_API.G_MISS_CHAR) ) )
1647: THEN
1642: IF p_cust_account_rec.date_type_preference is NOT NULL AND
1643: p_cust_account_rec.date_type_preference <> FND_API.G_MISS_CHAR
1644: AND ( p_create_update_flag = 'C' OR
1645: ( p_create_update_flag = 'U' AND
1646: p_cust_account_rec.date_type_preference <> NVL( l_date_type_preference, FND_API.G_MISS_CHAR) ) )
1647: THEN
1648: validate_lookup(
1649: p_column =>'date_type_preference',
1650: p_lookup_table =>'OE_LOOKUPS',
1682: --------------------------------------
1683:
1684: -- primary_specialist_id is foreign key to per_all_people_f
1685: IF p_cust_account_rec.primary_specialist_id IS NOT NULL AND
1686: p_cust_account_rec.primary_specialist_id <> FND_API.G_MISS_NUM
1687: THEN
1688: check_per_all_people_f_fk (
1689: p_column => 'primary_specialist_id',
1690: p_column_value => p_cust_account_rec.primary_specialist_id,
1723: --------------------------------------
1724:
1725: -- secondary_specialist_id is foreign key to per_all_people_f
1726: IF p_cust_account_rec.secondary_specialist_id IS NOT NULL AND
1727: p_cust_account_rec.secondary_specialist_id <> FND_API.G_MISS_NUM
1728: THEN
1729: check_per_all_people_f_fk (
1730: p_column => 'secondary_specialist_id',
1731: p_column_value => p_cust_account_rec.secondary_specialist_id,
1764: --------------------------------------
1765: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1766: -- account_liable_flag is lookup code in lookup type YES/NO
1767: IF p_cust_account_rec.account_liable_flag IS NOT NULL AND
1768: p_cust_account_rec.account_liable_flag <> FND_API.G_MISS_CHAR
1769: THEN
1770: validate_lookup (
1771: p_column => 'account_liable_flag',
1772: p_lookup_type => 'YES/NO',
1795: /****Logical APIs - validation not required****/
1796: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1797: -- hold_bill_flag is lookup code in lookup type YES/NO
1798: IF p_cust_account_rec.hold_bill_flag IS NOT NULL AND
1799: p_cust_account_rec.hold_bill_flag <> FND_API.G_MISS_CHAR
1800: THEN
1801: validate_lookup (
1802: p_column => 'hold_bill_flag',
1803: p_lookup_type => 'YES/NO',
1828: --------------------------------------
1829: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1830: -- dormant_account_flag is lookup code in lookup type YES/NO
1831: IF p_cust_account_rec.dormant_account_flag IS NOT NULL AND
1832: p_cust_account_rec.dormant_account_flag <> FND_API.G_MISS_CHAR
1833: THEN
1834: validate_lookup (
1835: p_column => 'dormant_account_flag',
1836: p_lookup_type => 'YES/NO',
1859: /****Logical APIs - validation not required****/
1860: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1861: -- ship_sets_include_lines_flag is lookup code in lookup type YES/NO
1862: IF p_cust_account_rec.ship_sets_include_lines_flag IS NOT NULL AND
1863: p_cust_account_rec.ship_sets_include_lines_flag <> FND_API.G_MISS_CHAR
1864: AND ( p_create_update_flag = 'C' OR
1865: ( p_create_update_flag = 'U' AND
1866: p_cust_account_rec.ship_sets_include_lines_flag <> NVL( l_ship_sets_inc_lines_f, FND_API.G_MISS_CHAR) ) )
1867: THEN
1862: IF p_cust_account_rec.ship_sets_include_lines_flag IS NOT NULL AND
1863: p_cust_account_rec.ship_sets_include_lines_flag <> FND_API.G_MISS_CHAR
1864: AND ( p_create_update_flag = 'C' OR
1865: ( p_create_update_flag = 'U' AND
1866: p_cust_account_rec.ship_sets_include_lines_flag <> NVL( l_ship_sets_inc_lines_f, FND_API.G_MISS_CHAR) ) )
1867: THEN
1868: validate_lookup (
1869: p_column => 'ship_sets_include_lines_flag',
1870: p_lookup_type => 'YES/NO',
1890: --is always set to N.
1891:
1892: IF (p_create_update_flag = 'C' or
1893: (p_create_update_flag ='U' and
1894: p_cust_account_rec.ship_sets_include_lines_flag <> NVL(l_ship_sets_inc_lines_f,FND_API.G_MISS_CHAR)))
1895: THEN
1896: IF p_cust_account_rec.ship_sets_include_lines_flag = 'Y'
1897: THEN
1898: BEGIN
1908: FND_MESSAGE.SET_TOKEN('VALUE1','Y');
1909: FND_MESSAGE.SET_TOKEN('COLUMN2','arrivalsets_include_lines_flag');
1910: FND_MESSAGE.SET_TOKEN('VALUE2','N');
1911: FND_MSG_PUB.ADD;
1912: x_return_status := FND_API.G_RET_STS_ERROR;
1913: END IF;
1914: END ;
1915: END IF;
1916: END IF;
1947: /****Logical APIs - validation not required****/
1948: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1949: -- arrivalsets_include_lines_flag is lookup code in lookup type YES/NO
1950: IF p_cust_account_rec.arrivalsets_include_lines_flag IS NOT NULL AND
1951: p_cust_account_rec.arrivalsets_include_lines_flag <> FND_API.G_MISS_CHAR
1952: AND ( p_create_update_flag = 'C' OR
1953: ( p_create_update_flag = 'U' AND
1954: p_cust_account_rec.arrivalsets_include_lines_flag <> NVL( l_arrivalsets_inc_lines_f, FND_API.G_MISS_CHAR) ) )
1955: THEN
1950: IF p_cust_account_rec.arrivalsets_include_lines_flag IS NOT NULL AND
1951: p_cust_account_rec.arrivalsets_include_lines_flag <> FND_API.G_MISS_CHAR
1952: AND ( p_create_update_flag = 'C' OR
1953: ( p_create_update_flag = 'U' AND
1954: p_cust_account_rec.arrivalsets_include_lines_flag <> NVL( l_arrivalsets_inc_lines_f, FND_API.G_MISS_CHAR) ) )
1955: THEN
1956: validate_lookup (
1957: p_column => 'arrivalsets_include_lines_flag',
1958: p_lookup_type => 'YES/NO',
1978: --is always set to N.
1979:
1980: IF (p_create_update_flag ='C' or
1981: (p_create_update_flag ='U' and
1982: p_cust_account_rec.arrivalsets_include_lines_flag <> NVL(l_arrivalsets_inc_lines_f,FND_API.G_MISS_CHAR)))
1983: THEN
1984: IF p_cust_account_rec.arrivalsets_include_lines_flag = 'Y'
1985: THEN
1986: BEGIN
1997: FND_MESSAGE.SET_TOKEN('VALUE1','Y');
1998: FND_MESSAGE.SET_TOKEN('COLUMN2','ship_sets_include_lines_flag');
1999: FND_MESSAGE.SET_TOKEN('VALUE2','N');
2000: FND_MSG_PUB.ADD;
2001: x_return_status := FND_API.G_RET_STS_ERROR;
2002: END IF;
2003: END ;
2004: END IF;
2005: END IF;
2035: /****Logical APIs - validation not required****/
2036: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
2037: -- sched_date_push_flag is lookup code in lookup type YES/NO
2038: IF p_cust_account_rec.sched_date_push_flag IS NOT NULL AND
2039: p_cust_account_rec.sched_date_push_flag <> FND_API.G_MISS_CHAR
2040: THEN
2041: validate_lookup (
2042: p_column => 'sched_date_push_flag',
2043: p_lookup_type => 'YES/NO',
2146: /****Logical APIs - validation not required****/
2147: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
2148: -- autopay_flag is lookup code in lookup type YES/NO
2149: IF p_cust_account_rec.autopay_flag IS NOT NULL AND
2150: p_cust_account_rec.autopay_flag <> FND_API.G_MISS_CHAR
2151: THEN
2152: validate_lookup (
2153: p_column => 'autopay_flag',
2154: p_lookup_type => 'YES/NO',
2189: --------------------------------------
2190: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
2191: -- notify_flag is lookup code in lookup type YES/NO
2192: IF p_cust_account_rec.notify_flag IS NOT NULL AND
2193: p_cust_account_rec.notify_flag <> FND_API.G_MISS_CHAR
2194: THEN
2195: validate_lookup (
2196: p_column => 'notify_flag',
2197: p_lookup_type => 'YES/NO',
2220:
2221: -- ship_via is foreign key to oe_ship_methods_v
2222: -- can be used only in single org case.
2223: IF p_cust_account_rec.ship_via IS NOT NULL AND
2224: p_cust_account_rec.ship_via <> FND_API.G_MISS_CHAR AND
2225: ( p_create_update_flag = 'C' OR
2226: ( p_create_update_flag = 'U' AND
2227: p_cust_account_rec.ship_via <> NVL( l_ship_via, FND_API.G_MISS_CHAR ) ) )
2228: THEN
2223: IF p_cust_account_rec.ship_via IS NOT NULL AND
2224: p_cust_account_rec.ship_via <> FND_API.G_MISS_CHAR AND
2225: ( p_create_update_flag = 'C' OR
2226: ( p_create_update_flag = 'U' AND
2227: p_cust_account_rec.ship_via <> NVL( l_ship_via, FND_API.G_MISS_CHAR ) ) )
2228: THEN
2229: check_oe_ship_methods_v_fk (
2230: p_entity => 'ACCOUNT',
2231: p_column => 'ship_via',
2259: --------------------------------------
2260:
2261: -- selling_party_id is foreign key of hz_parties
2262: IF p_cust_account_rec.selling_party_id IS NOT NULL AND
2263: p_cust_account_rec.selling_party_id <> FND_API.G_MISS_NUM
2264: THEN
2265: check_party_fk (
2266: p_column => 'selling_party_id',
2267: p_column_value => p_cust_account_rec.selling_party_id,
2354: * p_cust_account_rec Customer account relate record.
2355: * p_rowid Rowid of the record (used only in update mode).
2356: * IN/OUT:
2357: * x_return_status Return status after the call. The status can
2358: * be FND_API.G_RET_STS_SUCCESS (success),
2359: * FND_API.G_RET_STS_ERROR (error),
2360: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2361: *
2362: * NOTES
2355: * p_rowid Rowid of the record (used only in update mode).
2356: * IN/OUT:
2357: * x_return_status Return status after the call. The status can
2358: * be FND_API.G_RET_STS_SUCCESS (success),
2359: * FND_API.G_RET_STS_ERROR (error),
2360: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2361: *
2362: * NOTES
2363: *
2356: * IN/OUT:
2357: * x_return_status Return status after the call. The status can
2358: * be FND_API.G_RET_STS_SUCCESS (success),
2359: * FND_API.G_RET_STS_ERROR (error),
2360: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2361: *
2362: * NOTES
2363: *
2364: * MODIFICATION HISTORY
2437:
2438: -- cust_account_id is foreign key of hz_cust_accounts
2439: IF p_create_update_flag = 'C' AND
2440: p_cust_acct_relate_rec.cust_account_id IS NOT NULL AND
2441: p_cust_acct_relate_rec.cust_account_id <> FND_API.G_MISS_NUM
2442: THEN
2443: check_cust_account_fk (
2444: p_column => 'cust_account_id',
2445: p_column_value => p_cust_acct_relate_rec.cust_account_id,
2488:
2489: -- related_cust_account_id is foreign key of hz_cust_accounts
2490: IF p_create_update_flag = 'C' AND
2491: p_cust_acct_relate_rec.related_cust_account_id IS NOT NULL AND
2492: p_cust_acct_relate_rec.related_cust_account_id <> FND_API.G_MISS_NUM
2493: THEN
2494: check_cust_account_fk (
2495: p_column => 'related_cust_account_id',
2496: p_column_value => p_cust_acct_relate_rec.related_cust_account_id,
2519: AND ORG_ID = p_cust_acct_relate_rec.org_id; -- Bug 3456489
2520:
2521: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_REL_ALREADY_EXISTS' );
2522: FND_MSG_PUB.ADD;
2523: x_return_status := FND_API.G_RET_STS_ERROR;
2524:
2525: EXCEPTION
2526: WHEN NO_DATA_FOUND THEN
2527: NULL;
2576: -- checking during create.
2577:
2578: IF p_create_update_flag = 'C' AND
2579: p_cust_acct_relate_rec.customer_reciprocal_flag IS NOT NULL AND
2580: p_cust_acct_relate_rec.customer_reciprocal_flag <> FND_API.G_MISS_CHAR
2581: THEN
2582: validate_lookup (
2583: p_column => 'customer_reciprocal_flag',
2584: p_lookup_type => 'YES/NO',
2607: --------------------------------------
2608:
2609: -- relationship_type is lookup code in lookup type RELATIONSHIP_TYPE
2610: IF p_cust_acct_relate_rec.relationship_type IS NOT NULL AND
2611: p_cust_acct_relate_rec.relationship_type <> FND_API.G_MISS_CHAR AND
2612: ( p_create_update_flag = 'C' OR
2613: ( p_create_update_flag = 'U' AND
2614: p_cust_acct_relate_rec.relationship_type <> NVL( l_relationship_type, FND_API.G_MISS_CHAR ) ) )
2615: THEN
2610: IF p_cust_acct_relate_rec.relationship_type IS NOT NULL AND
2611: p_cust_acct_relate_rec.relationship_type <> FND_API.G_MISS_CHAR AND
2612: ( p_create_update_flag = 'C' OR
2613: ( p_create_update_flag = 'U' AND
2614: p_cust_acct_relate_rec.relationship_type <> NVL( l_relationship_type, FND_API.G_MISS_CHAR ) ) )
2615: THEN
2616: validate_lookup (
2617: p_column => 'relationship_type',
2618: p_lookup_type => 'RELATIONSHIP_TYPE',
2659: /****Logical APIs - validation not required****/
2660: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
2661: -- status is lookup code in lookup type CODE_STATUS
2662: IF p_cust_acct_relate_rec.status IS NOT NULL AND
2663: p_cust_acct_relate_rec.status <> FND_API.G_MISS_CHAR AND
2664: ( p_create_update_flag = 'C' OR
2665: ( p_create_update_flag = 'U' AND
2666: p_cust_acct_relate_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
2667: THEN
2662: IF p_cust_acct_relate_rec.status IS NOT NULL AND
2663: p_cust_acct_relate_rec.status <> FND_API.G_MISS_CHAR AND
2664: ( p_create_update_flag = 'C' OR
2665: ( p_create_update_flag = 'U' AND
2666: p_cust_acct_relate_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
2667: THEN
2668: validate_lookup (
2669: p_column => 'status',
2670: p_lookup_type => 'CODE_STATUS',
2737: * p_customer_profile_rec Customer profile record.
2738: * p_rowid Rowid of the record (used only in update mode).
2739: * IN/OUT:
2740: * x_return_status Return status after the call. The status can
2741: * be FND_API.G_RET_STS_SUCCESS (success),
2742: * FND_API.G_RET_STS_ERROR (error),
2743: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2744: *
2745: * NOTES
2738: * p_rowid Rowid of the record (used only in update mode).
2739: * IN/OUT:
2740: * x_return_status Return status after the call. The status can
2741: * be FND_API.G_RET_STS_SUCCESS (success),
2742: * FND_API.G_RET_STS_ERROR (error),
2743: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2744: *
2745: * NOTES
2746: *
2739: * IN/OUT:
2740: * x_return_status Return status after the call. The status can
2741: * be FND_API.G_RET_STS_SUCCESS (success),
2742: * FND_API.G_RET_STS_ERROR (error),
2743: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2744: *
2745: * NOTES
2746: *
2747: * MODIFICATION HISTORY
2900: -- If primary key value is not passed, it will be generated
2901: -- from sequence by table handler.
2902:
2903: IF p_customer_profile_rec.cust_account_profile_id IS NOT NULL AND
2904: p_customer_profile_rec.cust_account_profile_id <> FND_API.G_MISS_NUM
2905: THEN
2906: BEGIN
2907: SELECT 'Y' INTO l_dummy
2908: FROM HZ_CUSTOMER_PROFILES
2910:
2911: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
2912: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_profile_id' );
2913: FND_MSG_PUB.ADD;
2914: x_return_status := FND_API.G_RET_STS_ERROR;
2915: EXCEPTION
2916: WHEN NO_DATA_FOUND THEN
2917: NULL;
2918: END;
3037: -- check FK during creation.
3038:
3039: IF p_create_update_flag = 'C' AND
3040: p_customer_profile_rec.cust_account_id IS NOT NULL AND
3041: p_customer_profile_rec.cust_account_id <> FND_API.G_MISS_NUM
3042: THEN
3043: check_cust_account_fk (
3044: p_column => 'cust_account_id',
3045: p_column_value => p_customer_profile_rec.cust_account_id,
3065: -------------------------------------
3066:
3067: IF p_create_update_flag = 'C' AND
3068: ( p_customer_profile_rec.site_use_id IS NULL OR
3069: p_customer_profile_rec.site_use_id = FND_API.G_MISS_NUM ) AND
3070: p_customer_profile_rec.cust_account_id <> -1
3071: THEN
3072: BEGIN
3073: SELECT 'Y' INTO l_dummy
3078:
3079: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
3080: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id' );
3081: FND_MSG_PUB.ADD;
3082: x_return_status := FND_API.G_RET_STS_ERROR;
3083: EXCEPTION
3084: WHEN NO_DATA_FOUND THEN
3085: NULL;
3086: END;
3095:
3096:
3097: ELSIF p_create_update_flag = 'C' AND
3098: ( p_customer_profile_rec.site_use_id IS NULL OR
3099: p_customer_profile_rec.site_use_id = FND_API.G_MISS_NUM ) AND
3100: p_customer_profile_rec.cust_account_id = -1
3101: THEN
3102: -- 2310474 in this case we are creating a customer profile for party
3103: -- One party can only have 1 and only 1 profile directly attachment to the party
3110:
3111: FND_MESSAGE.SET_NAME( 'AR', 'HZ_ONLY_ONE_PROF_AT_PARTY_LEV' );
3112: FND_MESSAGE.SET_TOKEN( 'PARTY_ID', p_customer_profile_rec.party_id );
3113: FND_MSG_PUB.ADD;
3114: x_return_status := FND_API.G_RET_STS_ERROR;
3115: EXCEPTION
3116: WHEN NO_DATA_FOUND THEN
3117: NULL;
3118: END;
3144: ---------------------------------
3145:
3146: -- Validate lookup_code of the REVIEW_CYCLE
3147: IF p_customer_profile_rec.review_cycle IS NOT NULL AND
3148: p_customer_profile_rec.review_cycle <> FND_API.G_MISS_CHAR AND
3149: ( p_create_update_flag = 'C' OR
3150: ( p_create_update_flag = 'U' AND
3151: p_customer_profile_rec.review_cycle <> NVL( l_review_cycle, FND_API.G_MISS_CHAR ) ) )
3152: THEN
3147: IF p_customer_profile_rec.review_cycle IS NOT NULL AND
3148: p_customer_profile_rec.review_cycle <> FND_API.G_MISS_CHAR AND
3149: ( p_create_update_flag = 'C' OR
3150: ( p_create_update_flag = 'U' AND
3151: p_customer_profile_rec.review_cycle <> NVL( l_review_cycle, FND_API.G_MISS_CHAR ) ) )
3152: THEN
3153: validate_lookup (
3154: p_column => 'review_cycle',
3155: p_lookup_table => 'AR_LOOKUPS',
3193: END IF;
3194:
3195: -- collector_id is foreign key to ar_collectors
3196: IF p_customer_profile_rec.collector_id IS NOT NULL AND
3197: p_customer_profile_rec.collector_id <> FND_API.G_MISS_NUM
3198: THEN
3199: check_collector_fk (
3200: p_column => 'collector_id',
3201: p_column_value => p_customer_profile_rec.collector_id,
3244:
3245:
3246: -- credit_checking is lookup code in lookup type YES/NO
3247: IF p_customer_profile_rec.credit_checking IS NOT NULL AND
3248: p_customer_profile_rec.credit_checking <> FND_API.G_MISS_CHAR
3249: THEN
3250: validate_lookup (
3251: p_column => 'credit_checking',
3252: p_lookup_type => 'YES/NO',
3297: /****Logical APIs - validation not required****/
3298: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3299: -- discount_terms is lookup code in lookup type YES/NO
3300: IF p_customer_profile_rec.discount_terms IS NOT NULL AND
3301: p_customer_profile_rec.discount_terms <> FND_API.G_MISS_CHAR
3302: AND ( p_create_update_flag = 'C' OR
3303: ( p_create_update_flag = 'U' AND
3304: p_customer_profile_rec.discount_terms <> NVL( l_discount_terms, FND_API.G_MISS_CHAR ) ) )
3305: THEN
3300: IF p_customer_profile_rec.discount_terms IS NOT NULL AND
3301: p_customer_profile_rec.discount_terms <> FND_API.G_MISS_CHAR
3302: AND ( p_create_update_flag = 'C' OR
3303: ( p_create_update_flag = 'U' AND
3304: p_customer_profile_rec.discount_terms <> NVL( l_discount_terms, FND_API.G_MISS_CHAR ) ) )
3305: THEN
3306: validate_lookup (
3307: p_column => 'discount_terms',
3308: p_lookup_type => 'YES/NO',
3357: ----------------------------------------------
3358:
3359: -- profile_class_id is foreign key to hz_cust_profile_classes
3360: IF p_customer_profile_rec.profile_class_id IS NOT NULL AND
3361: p_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM
3362: THEN
3363: IF p_customer_profile_rec.profile_class_id < 0 THEN
3364: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NEGATIVE_PROFILE_CLASS' );
3365: FND_MSG_PUB.ADD;
3362: THEN
3363: IF p_customer_profile_rec.profile_class_id < 0 THEN
3364: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NEGATIVE_PROFILE_CLASS' );
3365: FND_MSG_PUB.ADD;
3366: x_return_status := FND_API.G_RET_STS_ERROR;
3367: ELSE
3368: BEGIN
3369: SELECT STATUS, NAME INTO l_class_status, l_profile_class_name
3370: FROM HZ_CUST_PROFILE_CLASSES
3373: IF l_class_status <> 'A' THEN
3374: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INACTIVE_PROFILE_CLASS' );
3375: FND_MESSAGE.SET_TOKEN( 'NAME', l_profile_class_name );
3376: FND_MSG_PUB.ADD;
3377: x_return_status := FND_API.G_RET_STS_ERROR;
3378: END IF;
3379: EXCEPTION
3380: WHEN NO_DATA_FOUND THEN
3381: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
3382: FND_MESSAGE.SET_TOKEN( 'FK', 'profile_class_id' );
3383: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'profile_class_id' );
3384: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_profile_classes');
3385: FND_MSG_PUB.ADD;
3386: x_return_status := FND_API.G_RET_STS_ERROR;
3387: END;
3388: END IF;
3389:
3390: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3460:
3461:
3462: IF p_create_update_flag = 'C' AND
3463: p_customer_profile_rec.site_use_id IS NOT NULL AND
3464: p_customer_profile_rec.site_use_id <> FND_API.G_MISS_NUM AND
3465: p_customer_profile_rec.cust_account_id <> -1
3466: THEN
3467: BEGIN
3468:
3476: MO_GLOBAL.validate_orgid_pub_api(l_org_id,'N',l_return_status);
3477: EXCEPTION
3478: WHEN OTHERS
3479: THEN
3480: x_return_status := FND_API.G_RET_STS_ERROR;
3481: END;
3482:
3483: -- cust_account_id should be same as cust_account_id site_use_id belongs to.
3484: SELECT CUST_ACCOUNT_ID INTO l_cust_account_id
3487:
3488: IF l_cust_account_id <> p_customer_profile_rec.cust_account_id THEN
3489: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_ACCT_SITEUSE_MISMATCH' );
3490: FND_MSG_PUB.ADD;
3491: x_return_status := FND_API.G_RET_STS_ERROR;
3492: END IF;
3493:
3494: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3495: hz_utility_v2pub.debug(p_message=>'cust_account_id should be same as cust_account_id site_use_id belongs to. ' ||
3504: FND_MESSAGE.SET_TOKEN( 'FK', 'site_use_id' );
3505: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'site_use_id' );
3506: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_site_uses' );
3507: FND_MSG_PUB.ADD;
3508: x_return_status := FND_API.G_RET_STS_ERROR;
3509: END;
3510:
3511: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3512: hz_utility_v2pub.debug(p_message=>'site_use_id is foreign key to hz_cust_site_uses. ' ||
3525:
3526: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
3527: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id' );
3528: FND_MSG_PUB.ADD;
3529: x_return_status := FND_API.G_RET_STS_ERROR;
3530: EXCEPTION
3531: WHEN NO_DATA_FOUND THEN
3532: NULL;
3533: END;
3543:
3544: -- 2310474: Profile can not be at account site level if it is created at party level
3545: ELSIF p_create_update_flag = 'C' AND
3546: p_customer_profile_rec.site_use_id IS NOT NULL AND
3547: p_customer_profile_rec.site_use_id <> FND_API.G_MISS_NUM AND
3548: p_customer_profile_rec.cust_account_id = -1
3549: THEN
3550: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_SITE_PROF_AT_PTY_LEV' );
3551: FND_MESSAGE.SET_TOKEN( 'PARTY_ID', p_customer_profile_rec.party_id );
3550: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_SITE_PROF_AT_PTY_LEV' );
3551: FND_MESSAGE.SET_TOKEN( 'PARTY_ID', p_customer_profile_rec.party_id );
3552: FND_MESSAGE.SET_TOKEN( 'SITE_USE_ID', p_customer_profile_rec.site_use_id );
3553: FND_MSG_PUB.ADD;
3554: x_return_status := FND_API.G_RET_STS_ERROR;
3555:
3556:
3557: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3558: hz_utility_v2pub.debug(p_message=>'profile at party level cannot be assign to a site. ' ||
3585: ----------------------------------------------
3586:
3587: -- credit_rating is lookup code in lookup type CREDIT_RATING
3588: IF p_customer_profile_rec.credit_rating IS NOT NULL AND
3589: p_customer_profile_rec.credit_rating <> FND_API.G_MISS_CHAR AND
3590: ( p_create_update_flag = 'C' OR
3591: ( p_create_update_flag = 'U' AND
3592: p_customer_profile_rec.credit_rating <> NVL( l_credit_rating, FND_API.G_MISS_CHAR ) ) )
3593: THEN
3588: IF p_customer_profile_rec.credit_rating IS NOT NULL AND
3589: p_customer_profile_rec.credit_rating <> FND_API.G_MISS_CHAR AND
3590: ( p_create_update_flag = 'C' OR
3591: ( p_create_update_flag = 'U' AND
3592: p_customer_profile_rec.credit_rating <> NVL( l_credit_rating, FND_API.G_MISS_CHAR ) ) )
3593: THEN
3594: validate_lookup (
3595: p_column => 'credit_rating',
3596: p_lookup_type => 'CREDIT_RATING',
3620: ----------------------------------------------
3621:
3622: -- risk_code is lookup code in lookup type RISK_CODE
3623: IF p_customer_profile_rec.risk_code IS NOT NULL AND
3624: p_customer_profile_rec.risk_code <> FND_API.G_MISS_CHAR AND
3625: ( p_create_update_flag = 'C' OR
3626: ( p_create_update_flag = 'U' AND
3627: p_customer_profile_rec.risk_code <> NVL( l_risk_code, FND_API.G_MISS_CHAR ) ) )
3628: THEN
3623: IF p_customer_profile_rec.risk_code IS NOT NULL AND
3624: p_customer_profile_rec.risk_code <> FND_API.G_MISS_CHAR AND
3625: ( p_create_update_flag = 'C' OR
3626: ( p_create_update_flag = 'U' AND
3627: p_customer_profile_rec.risk_code <> NVL( l_risk_code, FND_API.G_MISS_CHAR ) ) )
3628: THEN
3629: validate_lookup (
3630: p_column => 'risk_code',
3631: p_lookup_type => 'RISK_CODE',
3674:
3675:
3676: -- auto_rec_incl_disputed_flag is lookup code in lookup type YES/NO
3677: IF p_customer_profile_rec.auto_rec_incl_disputed_flag IS NOT NULL AND
3678: p_customer_profile_rec.auto_rec_incl_disputed_flag <> FND_API.G_MISS_CHAR
3679: THEN
3680: validate_lookup (
3681: p_column => 'auto_rec_incl_disputed_flag',
3682: p_lookup_type => 'YES/NO',
3709: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3710: -- charge_on_finance_charge_flag is lookup code in lookup type YES/NO
3711: -- As a part of late charge policy project changing the lookup type to AR_FORMULAE (bug # 5065436)
3712: IF p_customer_profile_rec.charge_on_finance_charge_flag IS NOT NULL AND
3713: p_customer_profile_rec.charge_on_finance_charge_flag <> FND_API.G_MISS_CHAR
3714: THEN
3715: validate_lookup (
3716: p_column => 'charge_on_finance_charge_flag',
3717: p_lookup_type => 'AR_FORMULAE',
3741: /****Logical APIs - validation not required****/
3742: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3743: -- cons_inv_flag is lookup code in lookup type YES/NO
3744: IF p_customer_profile_rec.cons_inv_flag IS NOT NULL AND
3745: p_customer_profile_rec.cons_inv_flag <> FND_API.G_MISS_CHAR
3746: THEN
3747: validate_lookup (
3748: p_column => 'cons_inv_flag',
3749: p_lookup_type => 'YES/NO',
3813: /****Logical APIs - validation not required****/
3814: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3815: -- status is lookup code in lookup type CODE_STATUS
3816: IF p_customer_profile_rec.status IS NOT NULL AND
3817: p_customer_profile_rec.status <> FND_API.G_MISS_CHAR AND
3818: ( p_create_update_flag = 'C' OR
3819: ( p_create_update_flag = 'U' AND
3820: p_customer_profile_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
3821: THEN
3816: IF p_customer_profile_rec.status IS NOT NULL AND
3817: p_customer_profile_rec.status <> FND_API.G_MISS_CHAR AND
3818: ( p_create_update_flag = 'C' OR
3819: ( p_create_update_flag = 'U' AND
3820: p_customer_profile_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
3821: THEN
3822: validate_lookup (
3823: p_column => 'status',
3824: p_lookup_type => 'CODE_STATUS',
3868:
3869:
3870: -- dunning_letters is lookup code in lookup type YES/NO
3871: IF p_customer_profile_rec.dunning_letters IS NOT NULL AND
3872: p_customer_profile_rec.dunning_letters <> FND_API.G_MISS_CHAR
3873: AND ( p_create_update_flag = 'C' OR
3874: ( p_create_update_flag = 'U' AND
3875: p_customer_profile_rec.dunning_letters <> NVL( l_dunning_letters, FND_API.G_MISS_CHAR ) ) )
3876: THEN
3871: IF p_customer_profile_rec.dunning_letters IS NOT NULL AND
3872: p_customer_profile_rec.dunning_letters <> FND_API.G_MISS_CHAR
3873: AND ( p_create_update_flag = 'C' OR
3874: ( p_create_update_flag = 'U' AND
3875: p_customer_profile_rec.dunning_letters <> NVL( l_dunning_letters, FND_API.G_MISS_CHAR ) ) )
3876: THEN
3877: validate_lookup (
3878: p_column => 'dunning_letters',
3879: p_lookup_type => 'YES/NO',
3923: /****Logical APIs - validation not required****/
3924: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3925: -- interest_charges is lookup code in lookup type YES/NO
3926: IF p_customer_profile_rec.interest_charges IS NOT NULL AND
3927: p_customer_profile_rec.interest_charges <> FND_API.G_MISS_CHAR
3928: AND ( p_create_update_flag = 'C' OR
3929: ( p_create_update_flag = 'U' AND
3930: p_customer_profile_rec.interest_charges <> NVL( l_interest_charges, FND_API.G_MISS_CHAR ) ) )
3931: THEN
3926: IF p_customer_profile_rec.interest_charges IS NOT NULL AND
3927: p_customer_profile_rec.interest_charges <> FND_API.G_MISS_CHAR
3928: AND ( p_create_update_flag = 'C' OR
3929: ( p_create_update_flag = 'U' AND
3930: p_customer_profile_rec.interest_charges <> NVL( l_interest_charges, FND_API.G_MISS_CHAR ) ) )
3931: THEN
3932: validate_lookup (
3933: p_column => 'interest_charges',
3934: p_lookup_type => 'YES/NO',
3980: /****Logical APIs - validation not required****/
3981: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3982: -- send_statements is lookup code in lookup type YES/NO
3983: IF p_customer_profile_rec.send_statements IS NOT NULL AND
3984: p_customer_profile_rec.send_statements <> FND_API.G_MISS_CHAR
3985: AND ( p_create_update_flag = 'C' OR
3986: ( p_create_update_flag = 'U' AND
3987: p_customer_profile_rec.send_statements <> NVL( l_send_statements, FND_API.G_MISS_CHAR ) ) )
3988: THEN
3983: IF p_customer_profile_rec.send_statements IS NOT NULL AND
3984: p_customer_profile_rec.send_statements <> FND_API.G_MISS_CHAR
3985: AND ( p_create_update_flag = 'C' OR
3986: ( p_create_update_flag = 'U' AND
3987: p_customer_profile_rec.send_statements <> NVL( l_send_statements, FND_API.G_MISS_CHAR ) ) )
3988: THEN
3989: validate_lookup (
3990: p_column => 'send_statements',
3991: p_lookup_type => 'YES/NO',
4038: /****Logical APIs - validation not required****/
4039: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
4040: -- credit_balance_statements is lookup code in lookup type YES/NO
4041: IF p_customer_profile_rec.credit_balance_statements IS NOT NULL AND
4042: p_customer_profile_rec.credit_balance_statements <> FND_API.G_MISS_CHAR
4043: THEN
4044: validate_lookup (
4045: p_column => 'credit_balance_statements',
4046: p_lookup_type => 'YES/NO',
4092: /****Logical APIs - validation not required****/
4093: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
4094: -- credit_hold is lookup code in lookup type YES/NO
4095: IF p_customer_profile_rec.credit_hold IS NOT NULL AND
4096: p_customer_profile_rec.credit_hold <> FND_API.G_MISS_CHAR
4097: THEN
4098: validate_lookup (
4099: p_column => 'credit_hold',
4100: p_lookup_type => 'YES/NO',
4142: ------------------------------------------
4143: ----*** Select fields to validate ***----
4144: ------------------------------------------
4145: IF p_customer_profile_rec.profile_class_id IS NOT NULL AND
4146: p_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM
4147: THEN
4148:
4149: SELECT * INTO l_profile_class_rec
4150: FROM HZ_CUST_PROFILE_CLASSES
4155: ------------------------------------------
4156: ---*** Account Status Validation ***---
4157: ------------------------------------------
4158: IF p_customer_profile_rec.account_status IS NOT NULL AND
4159: p_customer_profile_rec.account_status <> FND_API.G_MISS_CHAR
4160: AND ( p_create_update_flag = 'C' OR
4161: ( p_create_update_flag = 'U' AND
4162: p_customer_profile_rec.account_status <> NVL( l_account_status, FND_API.G_MISS_CHAR ) ) )
4163: THEN
4158: IF p_customer_profile_rec.account_status IS NOT NULL AND
4159: p_customer_profile_rec.account_status <> FND_API.G_MISS_CHAR
4160: AND ( p_create_update_flag = 'C' OR
4161: ( p_create_update_flag = 'U' AND
4162: p_customer_profile_rec.account_status <> NVL( l_account_status, FND_API.G_MISS_CHAR ) ) )
4163: THEN
4164: validate_lookup (
4165: p_column => 'account_status',
4166: p_lookup_type => 'ACCOUNT_STATUS',
4187: ---------------------------------------
4188: ------***Tolerance Validation ***------
4189: ---------------------------------------
4190: IF p_customer_profile_rec.tolerance IS NOT NULL AND
4191: p_customer_profile_rec.tolerance <> FND_API.G_MISS_NUM
4192: AND ( p_create_update_flag = 'C' OR
4193: ( p_create_update_flag = 'U' AND
4194: p_customer_profile_rec.tolerance <> NVL( l_tolerance, FND_API.G_MISS_NUM ) ) )
4195: THEN
4190: IF p_customer_profile_rec.tolerance IS NOT NULL AND
4191: p_customer_profile_rec.tolerance <> FND_API.G_MISS_NUM
4192: AND ( p_create_update_flag = 'C' OR
4193: ( p_create_update_flag = 'U' AND
4194: p_customer_profile_rec.tolerance <> NVL( l_tolerance, FND_API.G_MISS_NUM ) ) )
4195: THEN
4196: IF p_customer_profile_rec.tolerance > 100 OR
4197: p_customer_profile_rec.tolerance < -100
4198: THEN
4200: FND_MESSAGE.SET_TOKEN( 'COLUMN','tolerance');
4201: FND_MESSAGE.SET_TOKEN( 'VALUE1', '-100' );
4202: FND_MESSAGE.SET_TOKEN( 'VALUE2', '100' );
4203: FND_MSG_PUB.ADD;
4204: x_return_status := FND_API.G_RET_STS_ERROR;
4205: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4206: hz_utility_v2pub.debug(p_message=>'Tolerance should be between -100 and 100 .' ||
4207: 'x_return_status = ' || x_return_status,
4208: p_prefix =>l_debug_prefix,
4223: -------------------------------------------------
4224: ---*** Validating Percent_Collectable ***---
4225: -------------------------------------------------
4226: IF p_customer_profile_rec.percent_collectable IS NOT NULL AND
4227: p_customer_profile_rec.percent_collectable <> FND_API.G_MISS_NUM
4228: AND ( p_create_update_flag = 'C' OR
4229: ( p_create_update_flag = 'U' AND
4230: p_customer_profile_rec.percent_collectable <> NVL( l_percent_collectable, FND_API.G_MISS_NUM ) ) )
4231: THEN
4226: IF p_customer_profile_rec.percent_collectable IS NOT NULL AND
4227: p_customer_profile_rec.percent_collectable <> FND_API.G_MISS_NUM
4228: AND ( p_create_update_flag = 'C' OR
4229: ( p_create_update_flag = 'U' AND
4230: p_customer_profile_rec.percent_collectable <> NVL( l_percent_collectable, FND_API.G_MISS_NUM ) ) )
4231: THEN
4232: IF p_customer_profile_rec.percent_collectable > 100 OR
4233: p_customer_profile_rec.percent_collectable < 0
4234: THEN
4236: FND_MESSAGE.SET_TOKEN( 'COLUMN','percent_collectable');
4237: FND_MESSAGE.SET_TOKEN( 'VALUE1', '0' );
4238: FND_MESSAGE.SET_TOKEN( 'VALUE2', '100' );
4239: FND_MSG_PUB.ADD;
4240: x_return_status := FND_API.G_RET_STS_ERROR;
4241:
4242: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4243: hz_utility_v2pub.debug(p_message=>'Percent_Collectable should be between 0 and 100 .' ||
4244: 'x_return_status = ' || x_return_status,
4261: ---*** Validating Override_Terms ***---
4262: -----------------------------------------
4263: -- override_terms is lookup code in lookup type YES/NO
4264: IF p_customer_profile_rec.override_terms IS NOT NULL AND
4265: p_customer_profile_rec.override_terms <> FND_API.G_MISS_CHAR
4266: AND ( p_create_update_flag = 'C' OR
4267: ( p_create_update_flag = 'U' AND
4268: p_customer_profile_rec.override_terms <> NVL( l_override_terms, FND_API.G_MISS_CHAR) ) )
4269: THEN
4264: IF p_customer_profile_rec.override_terms IS NOT NULL AND
4265: p_customer_profile_rec.override_terms <> FND_API.G_MISS_CHAR
4266: AND ( p_create_update_flag = 'C' OR
4267: ( p_create_update_flag = 'U' AND
4268: p_customer_profile_rec.override_terms <> NVL( l_override_terms, FND_API.G_MISS_CHAR) ) )
4269: THEN
4270: validate_lookup (
4271: p_column => 'override_terms',
4272: p_lookup_type => 'YES/NO',
4294: ----------------------------------------------
4295: ---***Validating lockbox_matching_option***---
4296: ----------------------------------------------
4297: IF p_customer_profile_rec.lockbox_matching_option IS NOT NULL AND
4298: p_customer_profile_rec.lockbox_matching_option <> FND_API.G_MISS_CHAR
4299: AND ( p_create_update_flag = 'C' OR
4300: ( p_create_update_flag = 'U' AND
4301: p_customer_profile_rec.lockbox_matching_option <> NVL( l_lockbox_matching_option, FND_API.G_MISS_CHAR) ) )
4302: THEN
4297: IF p_customer_profile_rec.lockbox_matching_option IS NOT NULL AND
4298: p_customer_profile_rec.lockbox_matching_option <> FND_API.G_MISS_CHAR
4299: AND ( p_create_update_flag = 'C' OR
4300: ( p_create_update_flag = 'U' AND
4301: p_customer_profile_rec.lockbox_matching_option <> NVL( l_lockbox_matching_option, FND_API.G_MISS_CHAR) ) )
4302: THEN
4303: validate_lookup (
4304: p_column => 'lockbox_matching_option',
4305: p_lookup_type => 'ARLPLB_MATCHING_OPTION',
4327: --------------------------------------------
4328: --*** Validating autocash_hierarchy_id***---
4329: --------------------------------------------
4330: IF p_customer_profile_rec.autocash_hierarchy_id IS NOT NULL AND
4331: p_customer_profile_rec.autocash_hierarchy_id <> FND_API.G_MISS_NUM
4332: AND ( p_create_update_flag = 'C' OR
4333: ( p_create_update_flag = 'U' AND
4334: p_customer_profile_rec.autocash_hierarchy_id <> NVL( l_autocash_hierarchy_id, FND_API.G_MISS_NUM) ) )
4335: THEN
4330: IF p_customer_profile_rec.autocash_hierarchy_id IS NOT NULL AND
4331: p_customer_profile_rec.autocash_hierarchy_id <> FND_API.G_MISS_NUM
4332: AND ( p_create_update_flag = 'C' OR
4333: ( p_create_update_flag = 'U' AND
4334: p_customer_profile_rec.autocash_hierarchy_id <> NVL( l_autocash_hierarchy_id, FND_API.G_MISS_NUM) ) )
4335: THEN
4336: check_auto_hierid_fk (
4337: p_column => 'autocash_hierarchy_id',
4338: p_column_value => p_customer_profile_rec.autocash_hierarchy_id,
4359: ---------------------------------------------------------
4360: -----*** Validating autocash_hierarchy_id_for_adr***-----
4361: ---------------------------------------------------------
4362: IF p_customer_profile_rec.autocash_hierarchy_id_for_adr IS NOT NULL AND
4363: p_customer_profile_rec.autocash_hierarchy_id_for_adr <> FND_API.G_MISS_NUM
4364: AND ( p_create_update_flag = 'C' OR
4365: ( p_create_update_flag = 'U' AND
4366: p_customer_profile_rec.autocash_hierarchy_id_for_adr<> NVL( l_autocash_hierarchy_id_for_ad, FND_API.G_MISS_NUM) ) )
4367: THEN
4362: IF p_customer_profile_rec.autocash_hierarchy_id_for_adr IS NOT NULL AND
4363: p_customer_profile_rec.autocash_hierarchy_id_for_adr <> FND_API.G_MISS_NUM
4364: AND ( p_create_update_flag = 'C' OR
4365: ( p_create_update_flag = 'U' AND
4366: p_customer_profile_rec.autocash_hierarchy_id_for_adr<> NVL( l_autocash_hierarchy_id_for_ad, FND_API.G_MISS_NUM) ) )
4367: THEN
4368: check_auto_hierid_fk (
4369: p_column => 'autocash_hierarchy_id_for_adr',
4370: p_column_value => p_customer_profile_rec.autocash_hierarchy_id_for_adr,
4390: --------------------------------------------
4391: ---*** Validating statement_cycle_id ***---
4392: --------------------------------------------
4393: IF p_customer_profile_rec.statement_cycle_id IS NOT NULL AND
4394: p_customer_profile_rec.statement_cycle_id <> FND_API.G_MISS_NUM
4395: AND ( p_create_update_flag = 'C' OR
4396: ( p_create_update_flag = 'U' AND
4397: p_customer_profile_rec.statement_cycle_id <> NVL( l_statement_cycle_id, FND_API.G_MISS_NUM) ) )
4398: THEN
4393: IF p_customer_profile_rec.statement_cycle_id IS NOT NULL AND
4394: p_customer_profile_rec.statement_cycle_id <> FND_API.G_MISS_NUM
4395: AND ( p_create_update_flag = 'C' OR
4396: ( p_create_update_flag = 'U' AND
4397: p_customer_profile_rec.statement_cycle_id <> NVL( l_statement_cycle_id, FND_API.G_MISS_NUM) ) )
4398: THEN
4399: check_stat_cycid_fk (
4400: p_column => 'statement_cycle_id',
4401: p_column_value => p_customer_profile_rec.statement_cycle_id,
4422: ---------------------------------------------
4423: --------------*** Clearing Days***-----------
4424: ---------------------------------------------
4425: IF p_customer_profile_rec.clearing_days IS NOT NULL AND
4426: p_customer_profile_rec.clearing_days <> FND_API.G_MISS_NUM
4427: AND ( p_create_update_flag = 'C' OR
4428: ( p_create_update_flag = 'U' AND
4429: p_customer_profile_rec.clearing_days <> NVL( l_clearing_days, FND_API.G_MISS_NUM) ) )
4430: THEN
4425: IF p_customer_profile_rec.clearing_days IS NOT NULL AND
4426: p_customer_profile_rec.clearing_days <> FND_API.G_MISS_NUM
4427: AND ( p_create_update_flag = 'C' OR
4428: ( p_create_update_flag = 'U' AND
4429: p_customer_profile_rec.clearing_days <> NVL( l_clearing_days, FND_API.G_MISS_NUM) ) )
4430: THEN
4431: -- Error
4432: check_positive_value (
4433: p_column => 'clearing_days',
4453: ---------------------------------------------
4454: ------------*** Payment_grace_days***--------
4455: ---------------------------------------------
4456: IF p_customer_profile_rec.payment_grace_days IS NOT NULL AND
4457: p_customer_profile_rec.payment_grace_days <> FND_API.G_MISS_NUM
4458: AND ( p_create_update_flag = 'C' OR
4459: ( p_create_update_flag = 'U' AND
4460: p_customer_profile_rec.payment_grace_days <> NVL( l_payment_grace_days, FND_API.G_MISS_NUM) ) )
4461: THEN
4456: IF p_customer_profile_rec.payment_grace_days IS NOT NULL AND
4457: p_customer_profile_rec.payment_grace_days <> FND_API.G_MISS_NUM
4458: AND ( p_create_update_flag = 'C' OR
4459: ( p_create_update_flag = 'U' AND
4460: p_customer_profile_rec.payment_grace_days <> NVL( l_payment_grace_days, FND_API.G_MISS_NUM) ) )
4461: THEN
4462: check_positive_value (
4463: p_column => 'payment_grace_days',
4464: p_column_value => p_customer_profile_rec.payment_grace_days,
4484: ---------------------------------------------
4485: -----------***Interest_period_day***---------
4486: ---------------------------------------------
4487: IF p_customer_profile_rec.interest_period_days IS NOT NULL AND
4488: p_customer_profile_rec.interest_period_days <> FND_API.G_MISS_NUM
4489: THEN
4490: check_greater_than_zero (
4491: p_column => 'interest_period_days',
4492: p_column_value => p_customer_profile_rec.interest_period_days,
4512: --------------------------------------------
4513: ---***Validating dunning_letter_set_id***---
4514: --------------------------------------------
4515: IF p_customer_profile_rec.dunning_letter_set_id IS NOT NULL AND
4516: p_customer_profile_rec.dunning_letter_set_id <> FND_API.G_MISS_NUM
4517: THEN
4518: /* Bug 5095478. DUNNING_LETTER_SET_ID is obsoleted.
4519: This validation is changed to throw an error if user passes the
4520: DUNNING_LETTER_SET_ID */
4535: THEN
4536: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OBSOLETE_COLUMN');
4537: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dunning_letter_set_id' );
4538: FND_MSG_PUB.ADD;
4539: x_return_status := FND_API.G_RET_STS_ERROR;
4540: END IF; */
4541:
4542: END IF;
4543:
4551: --------------------------------------------
4552: ---*** Validating tax_printing_option ***---
4553: --------------------------------------------
4554: IF p_customer_profile_rec.tax_printing_option IS NOT NULL AND
4555: p_customer_profile_rec.tax_printing_option <> FND_API.G_MISS_CHAR
4556: AND ( p_create_update_flag = 'C' OR
4557: ( p_create_update_flag = 'U' AND
4558: p_customer_profile_rec.tax_printing_option <> NVL( l_tax_printing_option, FND_API.G_MISS_CHAR ) ))
4559: THEN
4554: IF p_customer_profile_rec.tax_printing_option IS NOT NULL AND
4555: p_customer_profile_rec.tax_printing_option <> FND_API.G_MISS_CHAR
4556: AND ( p_create_update_flag = 'C' OR
4557: ( p_create_update_flag = 'U' AND
4558: p_customer_profile_rec.tax_printing_option <> NVL( l_tax_printing_option, FND_API.G_MISS_CHAR ) ))
4559: THEN
4560: validate_lookup (
4561: p_column => 'tax_printing_option',
4562: p_lookup_type => 'TAX_PRINTING_OPTION',
4584: ----------------------------------------
4585: ----*** Validating standard_terms***---
4586: ----------------------------------------
4587: IF p_customer_profile_rec.standard_terms IS NOT NULL AND
4588: p_customer_profile_rec.standard_terms <> FND_API.G_MISS_NUM
4589: AND ( p_create_update_flag = 'C' OR
4590: ( p_create_update_flag = 'U' AND
4591: p_customer_profile_rec.standard_terms <> NVL( l_standard_terms, FND_API.G_MISS_NUM ) ))
4592: THEN
4587: IF p_customer_profile_rec.standard_terms IS NOT NULL AND
4588: p_customer_profile_rec.standard_terms <> FND_API.G_MISS_NUM
4589: AND ( p_create_update_flag = 'C' OR
4590: ( p_create_update_flag = 'U' AND
4591: p_customer_profile_rec.standard_terms <> NVL( l_standard_terms, FND_API.G_MISS_NUM ) ))
4592: THEN
4593: l_ckeck_acc_bfb_enabled := 'N';
4594: /* Commented the below code and added new set of validations below
4595: for enhancement # 5529044
4596: -- When Updating a BFB enabled site use profile which has a null payment term id
4597: -- with a new payment term id check that the payment term id is a BFB payment term
4598: -- if BFB is enabled for the account profile and check that the payment term is
4599: -- a non BFB payment term if BFB is disabled for the Account profile.
4600: if (p_customer_profile_rec.site_use_id is NOT NULL and p_customer_profile_rec.site_use_id <> FND_API.G_MISS_NUM
4601: and p_create_update_flag = 'U' )then
4602: l_ckeck_acc_bfb_enabled := 'Y';
4603: ll_cons_inv_flag := 'N';
4604: begin
4669: -------------------------------------------
4670: ---*** Validating grouping_rule_id ***---
4671: -------------------------------------------
4672: IF p_customer_profile_rec.grouping_rule_id IS NOT NULL AND
4673: p_customer_profile_rec.grouping_rule_id <> FND_API.G_MISS_NUM
4674: AND ( p_create_update_flag = 'C' OR
4675: ( p_create_update_flag = 'U' AND
4676: p_customer_profile_rec.grouping_rule_id <> NVL( l_grouping_rule_id, FND_API.G_MISS_NUM ) ))
4677:
4672: IF p_customer_profile_rec.grouping_rule_id IS NOT NULL AND
4673: p_customer_profile_rec.grouping_rule_id <> FND_API.G_MISS_NUM
4674: AND ( p_create_update_flag = 'C' OR
4675: ( p_create_update_flag = 'U' AND
4676: p_customer_profile_rec.grouping_rule_id <> NVL( l_grouping_rule_id, FND_API.G_MISS_NUM ) ))
4677:
4678: THEN
4679: check_grouping_ruleid_fk (
4680: p_column => 'grouping_rule_id',
4699: -------------------------------------------
4700: ---*** Validating Automatch Set Id ***---
4701: -------------------------------------------
4702: IF p_customer_profile_rec.automatch_set_id IS NOT NULL AND
4703: p_customer_profile_rec.automatch_set_id <> FND_API.G_MISS_NUM
4704: AND ( p_create_update_flag = 'C' OR
4705: ( p_create_update_flag = 'U' AND
4706: p_customer_profile_rec.automatch_set_id <> NVL( l_automatch_set_id, FND_API.G_MISS_NUM ) ))
4707:
4702: IF p_customer_profile_rec.automatch_set_id IS NOT NULL AND
4703: p_customer_profile_rec.automatch_set_id <> FND_API.G_MISS_NUM
4704: AND ( p_create_update_flag = 'C' OR
4705: ( p_create_update_flag = 'U' AND
4706: p_customer_profile_rec.automatch_set_id <> NVL( l_automatch_set_id, FND_API.G_MISS_NUM ) ))
4707:
4708: THEN
4709: check_automatch_set_id_fk (
4710: p_column => 'automatch_set_id',
4730: -------------------------------------------------
4731: ------------*** Discount_grace_days **---------------
4732: -------------------------------------------------
4733: IF v_customer_profile_rec.discount_grace_days IS NOT NULL AND
4734: v_customer_profile_rec.discount_grace_days <> FND_API.G_MISS_NUM
4735: AND ( p_create_update_flag = 'C' OR
4736: ( p_create_update_flag = 'U' AND
4737: p_customer_profile_rec.discount_grace_days <> NVL( l_discount_grace_days, FND_API.G_MISS_NUM ) ))
4738: THEN
4733: IF v_customer_profile_rec.discount_grace_days IS NOT NULL AND
4734: v_customer_profile_rec.discount_grace_days <> FND_API.G_MISS_NUM
4735: AND ( p_create_update_flag = 'C' OR
4736: ( p_create_update_flag = 'U' AND
4737: p_customer_profile_rec.discount_grace_days <> NVL( l_discount_grace_days, FND_API.G_MISS_NUM ) ))
4738: THEN
4739: check_positive_value (
4740: p_column => 'discount_grace_days',
4741: p_column_value => v_customer_profile_rec.discount_grace_days,
4752:
4753: IF p_create_update_flag = 'C' OR
4754: (p_create_update_flag = 'U' AND
4755: v_customer_profile_rec.profile_class_id IS NOT NULL AND
4756: v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM)
4757: THEN
4758:
4759: IF v_customer_profile_rec.discount_terms IS NULL THEN
4760: v_customer_profile_rec.discount_terms :=
4785: IF v_customer_profile_rec.discount_terms = 'N' OR
4786: v_customer_profile_rec.discount_terms is NULL
4787: THEN
4788: IF v_customer_profile_rec.discount_grace_days >= 0 AND
4789: v_customer_profile_rec.discount_grace_days <> FND_API.G_MISS_NUM
4790: THEN
4791: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_DEP_FIELDS' );
4792: FND_MESSAGE.SET_TOKEN( 'COLUMN1', 'discount_terms' );
4793: FND_MESSAGE.SET_TOKEN( 'VALUE1', 'N' );
4793: FND_MESSAGE.SET_TOKEN( 'VALUE1', 'N' );
4794: FND_MESSAGE.SET_TOKEN( 'COLUMN2', 'discount_grace_days');
4795: FND_MESSAGE.SET_TOKEN( 'VALUE2', 'NULL' );
4796: FND_MSG_PUB.ADD;
4797: x_return_status := FND_API.G_RET_STS_ERROR;
4798:
4799: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4800: hz_utility_v2pub.debug(p_message=>'discount_grace_days should be null when discount_terms is N.' ||
4801: 'x_return_status = ' || x_return_status,
4820:
4821: IF p_create_update_flag = 'C' OR
4822: (p_create_update_flag = 'U' AND
4823: v_customer_profile_rec.profile_class_id IS NOT NULL AND
4824: v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM)
4825: THEN
4826: IF v_customer_profile_rec.dunning_letters IS NULL THEN
4827: v_customer_profile_rec.dunning_letters :=
4828: l_profile_class_rec.dunning_letters;
4852:
4853: /*Bug 5095478 the column dunning_letter_set_id is obsolete in R12
4854: IF v_customer_profile_rec.dunning_letters = 'Y'
4855: THEN
4856: IF v_customer_profile_rec.dunning_letter_set_id = FND_API.G_MISS_NUM OR
4857: v_customer_profile_rec.dunning_letter_set_id IS NULL
4858: THEN
4859: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MAND_DEP_FIELDS' );
4860: FND_MESSAGE.SET_TOKEN('COLUMN1', 'dunning_letters');
4860: FND_MESSAGE.SET_TOKEN('COLUMN1', 'dunning_letters');
4861: FND_MESSAGE.SET_TOKEN('VALUE1', 'Y');
4862: FND_MESSAGE.SET_TOKEN('COLUMN2','dunning_letter_set_id');
4863: FND_MSG_PUB.ADD;
4864: x_return_status := FND_API.G_RET_STS_ERROR;
4865:
4866: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4867: hz_utility_v2pub.debug(p_message=>'dunning_letter_set_id cannot be NULL when dunning_letters is Y.' ||
4868: 'x_return_status = ' || x_return_status,
4876: IF v_customer_profile_rec.dunning_letters = 'N' OR
4877: v_customer_profile_rec.dunning_letters IS NULL
4878: THEN
4879: IF v_customer_profile_rec.dunning_letter_set_id >= 0 AND
4880: v_customer_profile_rec.dunning_letter_set_id <> FND_API.G_MISS_NUM
4881: THEN
4882: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_DEP_FIELDS' );
4883: FND_MESSAGE.SET_TOKEN('COLUMN1','dunning_letters');
4884: FND_MESSAGE.SET_TOKEN('VALUE1','N');
4884: FND_MESSAGE.SET_TOKEN('VALUE1','N');
4885: FND_MESSAGE.SET_TOKEN('COLUMN2','dunning_letter_set_id');
4886: FND_MESSAGE.SET_TOKEN('VALUE2','NULL');
4887: FND_MSG_PUB.ADD;
4888: x_return_status := FND_API.G_RET_STS_ERROR;
4889:
4890: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4891: hz_utility_v2pub.debug(p_message=>'dunning_letter_set_id should be NULL when dunning_letters is N.' ||
4892: 'x_return_status = ' || x_return_status,
4910:
4911: IF p_create_update_flag = 'C' OR
4912: ( p_create_update_flag = 'U' AND
4913: v_customer_profile_rec.profile_class_id is not null AND
4914: v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM )
4915: THEN
4916:
4917: IF v_customer_profile_rec.send_statements IS NULL THEN
4918: v_customer_profile_rec.send_statements :=
4953: END IF;
4954: END IF;
4955:
4956: IF v_customer_profile_rec.send_statements = 'Y' THEN
4957: IF v_customer_profile_rec.statement_cycle_id = FND_API.G_MISS_NUM OR
4958: v_customer_profile_rec.statement_cycle_id IS NULL
4959: THEN
4960: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MAND_DEP_FIELDS' );
4961: FND_MESSAGE.SET_TOKEN( 'COLUMN1','send_statements');
4961: FND_MESSAGE.SET_TOKEN( 'COLUMN1','send_statements');
4962: FND_MESSAGE.SET_TOKEN( 'VALUE1','Y');
4963: FND_MESSAGE.SET_TOKEN( 'COLUMN2','statement_cycle_id');
4964: FND_MSG_PUB.ADD;
4965: x_return_status := FND_API.G_RET_STS_ERROR;
4966:
4967: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4968: hz_utility_v2pub.debug(p_message=>'statement_cycle_id cannot be NULL when send_statements
4969: is Y.' || 'x_return_status = ' || x_return_status,
4977: FND_MESSAGE.SET_TOKEN( 'COLUMN1','send_statements');
4978: FND_MESSAGE.SET_TOKEN( 'VALUE1','Y');
4979: FND_MESSAGE.SET_TOKEN( 'COLUMN2','credit_balance_statements');
4980: FND_MSG_PUB.ADD;
4981: x_return_status := FND_API.G_RET_STS_ERROR;
4982:
4983: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4984: hz_utility_v2pub.debug(p_message=>'credit_balance_statements cannot be NULL when
4985: send_statements is Y .' || 'x_return_status = ' ||
4994: IF v_customer_profile_rec.send_statements = 'N' OR
4995: v_customer_profile_rec.send_statements IS NULL
4996: THEN
4997: IF v_customer_profile_rec.statement_cycle_id >= 0 AND
4998: v_customer_profile_rec.statement_cycle_id <> FND_API.G_MISS_NUM
4999: THEN
5000: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_DEP_FIELDS' );
5001: FND_MESSAGE.SET_TOKEN('COLUMN1', 'send_statements');
5002: FND_MESSAGE.SET_TOKEN('VALUE1', 'N');
5002: FND_MESSAGE.SET_TOKEN('VALUE1', 'N');
5003: FND_MESSAGE.SET_TOKEN('COLUMN2','statement_cycle_id');
5004: FND_MESSAGE.SET_TOKEN('VALUE2', 'NULL');
5005: FND_MSG_PUB.ADD;
5006: x_return_status := FND_API.G_RET_STS_ERROR;
5007:
5008: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5009: hz_utility_v2pub.debug(p_message=>'statement_cycle_id should be NULL when send_statements
5010: is N .' || 'x_return_status = ' || x_return_status,
5021: FND_MESSAGE.SET_TOKEN('VALUE1','N');
5022: FND_MESSAGE.SET_TOKEN('COLUMN2','credit_balance_statements');
5023: FND_MESSAGE.SET_TOKEN('VALUE2','N');
5024: FND_MSG_PUB.ADD;
5025: x_return_status := FND_API.G_RET_STS_ERROR;
5026:
5027: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5028: hz_utility_v2pub.debug(p_message=>'credit_balance_statements should be N when send_statements
5029: is N .' || 'x_return_status = ' || x_return_status,
5049:
5050: IF p_create_update_flag = 'C' OR
5051: ( p_create_update_flag = 'U' AND
5052: v_customer_profile_rec.profile_class_id is not null AND
5053: v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM )
5054: THEN
5055:
5056: IF v_customer_profile_rec.interest_charges IS NULL THEN
5057: v_customer_profile_rec.interest_charges :=
5102: IF v_customer_profile_rec.interest_charges = 'Y' THEN
5103: -- Bug 5400422 : Validate only for few late_charge_calculation_trx
5104: IF v_customer_profile_rec.late_charge_calculation_trx IN
5105: ('LATE', 'OVERDUE_LATE', 'OVERDUE') THEN
5106: IF v_customer_profile_rec.interest_period_days = FND_API.G_MISS_NUM OR
5107: v_customer_profile_rec.interest_period_days IS NULL
5108: THEN
5109: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_INT_CHARGES_Y' );
5110: FND_MSG_PUB.ADD;
5107: v_customer_profile_rec.interest_period_days IS NULL
5108: THEN
5109: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_INT_CHARGES_Y' );
5110: FND_MSG_PUB.ADD;
5111: x_return_status := FND_API.G_RET_STS_ERROR;
5112: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5113: hz_utility_v2pub.debug(p_message=>'interest_period_days cannot be NULL when interest_charges is Y.' || 'x_return_status = ' || x_return_status,
5114: p_prefix =>l_debug_prefix,
5115: p_msg_level=>fnd_log.level_statement);
5139: IF v_customer_profile_rec.interest_charges = 'N' OR
5140: v_customer_profile_rec.interest_charges IS NULL
5141: THEN
5142: IF v_customer_profile_rec.interest_period_days >= 0 AND
5143: v_customer_profile_rec.interest_period_days <> FND_API.G_MISS_NUM
5144: THEN
5145: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_DEP_FIELDS' );
5146: FND_MESSAGE.SET_TOKEN( 'COLUMN1','interest_charges');
5147: FND_MESSAGE.SET_TOKEN( 'VALUE1','N');
5147: FND_MESSAGE.SET_TOKEN( 'VALUE1','N');
5148: FND_MESSAGE.SET_TOKEN( 'COLUMN2','interest_period_days');
5149: FND_MESSAGE.SET_TOKEN( 'VALUE2','NULL');
5150: FND_MSG_PUB.ADD;
5151: x_return_status := FND_API.G_RET_STS_ERROR;
5152:
5153: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5154: hz_utility_v2pub.debug(p_message=>'Interest_period_days should be NULL when interest_charges
5155: is N .' || 'x_return_status = ' || x_return_status,
5165: FND_MESSAGE.SET_TOKEN( 'VALUE1','N');
5166: FND_MESSAGE.SET_TOKEN( 'COLUMN2','charge_on_finance_charge_flag');
5167: FND_MESSAGE.SET_TOKEN( 'VALUE2','N');
5168: FND_MSG_PUB.ADD;
5169: x_return_status := FND_API.G_RET_STS_ERROR;
5170:
5171: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5172: hz_utility_v2pub.debug(p_message=>'charge_on_finance_charge_flag cannot be Y when
5173: interest_charges is N .' || 'x_return_status ='
5190: -- Credit_classification
5191: -----------------------------------------------------------------------
5192: -- Validate lookup_code of the CREDIT_CLASSIFICATION
5193: IF p_customer_profile_rec.credit_classification IS NOT NULL AND
5194: p_customer_profile_rec.credit_classification <> FND_API.G_MISS_CHAR AND
5195: ( p_create_update_flag = 'C' OR
5196: ( p_create_update_flag = 'U' AND
5197: p_customer_profile_rec.credit_classification <> NVL( l_credit_classification, FND_API.G_MISS_CHAR ) ) )
5198: THEN
5193: IF p_customer_profile_rec.credit_classification IS NOT NULL AND
5194: p_customer_profile_rec.credit_classification <> FND_API.G_MISS_CHAR AND
5195: ( p_create_update_flag = 'C' OR
5196: ( p_create_update_flag = 'U' AND
5197: p_customer_profile_rec.credit_classification <> NVL( l_credit_classification, FND_API.G_MISS_CHAR ) ) )
5198: THEN
5199: validate_lookup (
5200: p_column => 'credit_classification',
5201: p_lookup_table => 'AR_LOOKUPS',
5228: ----------------------------------------------
5229:
5230: -- LATE_CHARGE_CALCULATION_TRX is lookup code in lookup type AR_MANDATORY_LATE_CHARGES
5231: IF p_customer_profile_rec.late_charge_calculation_trx IS NOT NULL AND
5232: p_customer_profile_rec.late_charge_calculation_trx <> FND_API.G_MISS_CHAR AND
5233: ( p_create_update_flag = 'C' OR
5234: ( p_create_update_flag = 'U' AND
5235: p_customer_profile_rec.late_charge_calculation_trx <> NVL( l_late_charge_calculation_trx, FND_API.G_MISS_CHAR ) ) )
5236: THEN
5231: IF p_customer_profile_rec.late_charge_calculation_trx IS NOT NULL AND
5232: p_customer_profile_rec.late_charge_calculation_trx <> FND_API.G_MISS_CHAR AND
5233: ( p_create_update_flag = 'C' OR
5234: ( p_create_update_flag = 'U' AND
5235: p_customer_profile_rec.late_charge_calculation_trx <> NVL( l_late_charge_calculation_trx, FND_API.G_MISS_CHAR ) ) )
5236: THEN
5237: validate_lookup (
5238: p_column => 'late_charge_calculation_trx',
5239: p_lookup_type => 'AR_MANDATORY_LATE_CHARGES',
5261: ----------------------------------------------
5262:
5263: -- CREDIT_ITEMS_FLAG is lookup code in lookup type YES/NO
5264: IF p_customer_profile_rec.credit_items_flag IS NOT NULL AND
5265: p_customer_profile_rec.credit_items_flag <> FND_API.G_MISS_CHAR
5266: AND ( p_create_update_flag = 'C' OR
5267: ( p_create_update_flag = 'U' AND
5268: p_customer_profile_rec.credit_items_flag <> NVL( l_credit_items_flag, FND_API.G_MISS_CHAR ) ) )
5269: THEN
5264: IF p_customer_profile_rec.credit_items_flag IS NOT NULL AND
5265: p_customer_profile_rec.credit_items_flag <> FND_API.G_MISS_CHAR
5266: AND ( p_create_update_flag = 'C' OR
5267: ( p_create_update_flag = 'U' AND
5268: p_customer_profile_rec.credit_items_flag <> NVL( l_credit_items_flag, FND_API.G_MISS_CHAR ) ) )
5269: THEN
5270: validate_lookup (
5271: p_column => 'credit_items_flag',
5272: p_lookup_type => 'YES/NO',
5295: ----------------------------------------------
5296:
5297: -- DISPUTED_TRANSACTIONS_FLAG is lookup code in lookup type YES/NO
5298: IF p_customer_profile_rec.disputed_transactions_flag IS NOT NULL AND
5299: p_customer_profile_rec.disputed_transactions_flag <> FND_API.G_MISS_CHAR
5300: AND ( p_create_update_flag = 'C' OR
5301: ( p_create_update_flag = 'U' AND
5302: p_customer_profile_rec.disputed_transactions_flag <> NVL( l_disputed_transactions_flag, FND_API.G_MISS_CHAR ) ) )
5303: THEN
5298: IF p_customer_profile_rec.disputed_transactions_flag IS NOT NULL AND
5299: p_customer_profile_rec.disputed_transactions_flag <> FND_API.G_MISS_CHAR
5300: AND ( p_create_update_flag = 'C' OR
5301: ( p_create_update_flag = 'U' AND
5302: p_customer_profile_rec.disputed_transactions_flag <> NVL( l_disputed_transactions_flag, FND_API.G_MISS_CHAR ) ) )
5303: THEN
5304: validate_lookup (
5305: p_column => 'disputed_transactions_flag',
5306: p_lookup_type => 'YES/NO',
5329: ----------------------------------------------
5330:
5331: -- LATE_CHARGE_TYPE is lookup code in lookup type AR_LATE_CHARGE_TYPE
5332: IF p_customer_profile_rec.late_charge_type IS NOT NULL AND
5333: p_customer_profile_rec.late_charge_type <> FND_API.G_MISS_CHAR AND
5334: ( p_create_update_flag = 'C' OR
5335: ( p_create_update_flag = 'U' AND
5336: p_customer_profile_rec.late_charge_type <> NVL( l_late_charge_type, FND_API.G_MISS_CHAR ) ) )
5337: THEN
5332: IF p_customer_profile_rec.late_charge_type IS NOT NULL AND
5333: p_customer_profile_rec.late_charge_type <> FND_API.G_MISS_CHAR AND
5334: ( p_create_update_flag = 'C' OR
5335: ( p_create_update_flag = 'U' AND
5336: p_customer_profile_rec.late_charge_type <> NVL( l_late_charge_type, FND_API.G_MISS_CHAR ) ) )
5337: THEN
5338: validate_lookup (
5339: p_column => 'late_charge_type',
5340: p_lookup_type => 'AR_LATE_CHARGE_TYPE',
5362: ----------------------------------------------
5363:
5364: -- INTEREST_CALCULATION_PERIOD is lookup code in lookup type AR_CALCULATION_PERIOD
5365: IF p_customer_profile_rec.interest_calculation_period IS NOT NULL AND
5366: p_customer_profile_rec.interest_calculation_period <> FND_API.G_MISS_CHAR AND
5367: ( p_create_update_flag = 'C' OR
5368: ( p_create_update_flag = 'U' AND
5369: p_customer_profile_rec.interest_calculation_period <> NVL( l_interest_calculation_period, FND_API.G_MISS_CHAR ) ) )
5370: THEN
5365: IF p_customer_profile_rec.interest_calculation_period IS NOT NULL AND
5366: p_customer_profile_rec.interest_calculation_period <> FND_API.G_MISS_CHAR AND
5367: ( p_create_update_flag = 'C' OR
5368: ( p_create_update_flag = 'U' AND
5369: p_customer_profile_rec.interest_calculation_period <> NVL( l_interest_calculation_period, FND_API.G_MISS_CHAR ) ) )
5370: THEN
5371: validate_lookup (
5372: p_column => 'interest_calculation_period',
5373: p_lookup_type => 'AR_CALCULATION_PERIOD',
5395: ----------------------------------------------
5396:
5397: -- HOLD_CHARGED_INVOICES_FLAG is lookup code in lookup type YES/NO
5398: IF p_customer_profile_rec.hold_charged_invoices_flag IS NOT NULL AND
5399: p_customer_profile_rec.hold_charged_invoices_flag <> FND_API.G_MISS_CHAR
5400: AND ( p_create_update_flag = 'C' OR
5401: ( p_create_update_flag = 'U' AND
5402: p_customer_profile_rec.hold_charged_invoices_flag <> NVL( l_hold_charged_invoices_flag, FND_API.G_MISS_CHAR ) ) )
5403: THEN
5398: IF p_customer_profile_rec.hold_charged_invoices_flag IS NOT NULL AND
5399: p_customer_profile_rec.hold_charged_invoices_flag <> FND_API.G_MISS_CHAR
5400: AND ( p_create_update_flag = 'C' OR
5401: ( p_create_update_flag = 'U' AND
5402: p_customer_profile_rec.hold_charged_invoices_flag <> NVL( l_hold_charged_invoices_flag, FND_API.G_MISS_CHAR ) ) )
5403: THEN
5404: validate_lookup (
5405: p_column => 'hold_charged_invoices_flag',
5406: p_lookup_type => 'YES/NO',
5429: ----------------------------------------------
5430:
5431: -- MULTIPLE_INTEREST_RATES_FLAG is lookup code in lookup type YES/NO
5432: IF p_customer_profile_rec.multiple_interest_rates_flag IS NOT NULL AND
5433: p_customer_profile_rec.multiple_interest_rates_flag <> FND_API.G_MISS_CHAR
5434: AND ( p_create_update_flag = 'C' OR
5435: ( p_create_update_flag = 'U' AND
5436: p_customer_profile_rec.multiple_interest_rates_flag <> NVL( l_multiple_interest_rates_flag, FND_API.G_MISS_CHAR ) ) )
5437: THEN
5432: IF p_customer_profile_rec.multiple_interest_rates_flag IS NOT NULL AND
5433: p_customer_profile_rec.multiple_interest_rates_flag <> FND_API.G_MISS_CHAR
5434: AND ( p_create_update_flag = 'C' OR
5435: ( p_create_update_flag = 'U' AND
5436: p_customer_profile_rec.multiple_interest_rates_flag <> NVL( l_multiple_interest_rates_flag, FND_API.G_MISS_CHAR ) ) )
5437: THEN
5438: validate_lookup (
5439: p_column => 'multiple_interest_rates_flag',
5440: p_lookup_type => 'YES/NO',
5463: ------------------------------------
5464: -- 1. late_charge_term_id should be a valid value defined in RA_TERMS
5465: -- 2. Late charge payment term is required when late charge type is debit memo or interest invoice
5466:
5467: IF (((p_customer_profile_rec.late_charge_term_id IS NULL OR p_customer_profile_rec.late_charge_term_id = FND_API.G_MISS_NUM) AND
5468: (p_customer_profile_rec.late_charge_type = 'DM' OR
5469: p_customer_profile_rec.late_charge_type = 'INV')) OR
5470: ((p_customer_profile_rec.late_charge_type = 'ADJ') AND
5471: (p_customer_profile_rec.late_charge_term_id IS NOT NULL AND p_customer_profile_rec.late_charge_term_id <> FND_API.G_MISS_NUM))) THEN
5467: IF (((p_customer_profile_rec.late_charge_term_id IS NULL OR p_customer_profile_rec.late_charge_term_id = FND_API.G_MISS_NUM) AND
5468: (p_customer_profile_rec.late_charge_type = 'DM' OR
5469: p_customer_profile_rec.late_charge_type = 'INV')) OR
5470: ((p_customer_profile_rec.late_charge_type = 'ADJ') AND
5471: (p_customer_profile_rec.late_charge_term_id IS NOT NULL AND p_customer_profile_rec.late_charge_term_id <> FND_API.G_MISS_NUM))) THEN
5472: -- Late charge payment term is required when
5473: -- late charge type is debit memo or interest invoice
5474: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LC_PAY_TERM' );
5475: FND_MSG_PUB.ADD;
5472: -- Late charge payment term is required when
5473: -- late charge type is debit memo or interest invoice
5474: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LC_PAY_TERM' );
5475: FND_MSG_PUB.ADD;
5476: x_return_status := FND_API.G_RET_STS_ERROR;
5477: END IF;
5478:
5479: IF p_customer_profile_rec.late_charge_term_id is NOT NULL AND
5480: p_customer_profile_rec.late_charge_term_id <> FND_API.G_MISS_NUM
5476: x_return_status := FND_API.G_RET_STS_ERROR;
5477: END IF;
5478:
5479: IF p_customer_profile_rec.late_charge_term_id is NOT NULL AND
5480: p_customer_profile_rec.late_charge_term_id <> FND_API.G_MISS_NUM
5481: AND ( p_create_update_flag = 'C' OR
5482: ( p_create_update_flag = 'U' AND
5483: p_customer_profile_rec.late_charge_term_id <> NVL( l_late_charge_term_id, FND_API.G_MISS_NUM ) ) )
5484: THEN
5479: IF p_customer_profile_rec.late_charge_term_id is NOT NULL AND
5480: p_customer_profile_rec.late_charge_term_id <> FND_API.G_MISS_NUM
5481: AND ( p_create_update_flag = 'C' OR
5482: ( p_create_update_flag = 'U' AND
5483: p_customer_profile_rec.late_charge_term_id <> NVL( l_late_charge_term_id, FND_API.G_MISS_NUM ) ) )
5484: THEN
5485: check_late_charge_term_id_fk(
5486: p_column => 'payment_term_id',
5487: p_column_value => p_customer_profile_rec.late_charge_term_id,
5510: -- 1. message_text_id should be a valid value defined in AR_STANDARD_TEXT
5511: -- 2. Message text can be entered only when late charge type is debit memo or interest invoice
5512:
5513: IF ((p_customer_profile_rec.late_charge_type = 'ADJ') AND
5514: (p_customer_profile_rec.message_text_id IS NOT NULL AND p_customer_profile_rec.message_text_id <> FND_API.G_MISS_NUM)) THEN
5515: -- Message text can be entered only when
5516: -- late charge type is debit memo or interest invoice
5517: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_MSG_FOR_LC_TYPE' );
5518: FND_MSG_PUB.ADD;
5515: -- Message text can be entered only when
5516: -- late charge type is debit memo or interest invoice
5517: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_MSG_FOR_LC_TYPE' );
5518: FND_MSG_PUB.ADD;
5519: x_return_status := FND_API.G_RET_STS_ERROR;
5520: END IF;
5521:
5522: IF p_customer_profile_rec.message_text_id is NOT NULL AND
5523: p_customer_profile_rec.message_text_id <> FND_API.G_MISS_NUM
5519: x_return_status := FND_API.G_RET_STS_ERROR;
5520: END IF;
5521:
5522: IF p_customer_profile_rec.message_text_id is NOT NULL AND
5523: p_customer_profile_rec.message_text_id <> FND_API.G_MISS_NUM
5524: AND ( p_create_update_flag = 'C' OR
5525: ( p_create_update_flag = 'U' AND
5526: p_customer_profile_rec.message_text_id <> NVL( l_message_text_id, FND_API.G_MISS_NUM ) ) )
5527: THEN
5522: IF p_customer_profile_rec.message_text_id is NOT NULL AND
5523: p_customer_profile_rec.message_text_id <> FND_API.G_MISS_NUM
5524: AND ( p_create_update_flag = 'C' OR
5525: ( p_create_update_flag = 'U' AND
5526: p_customer_profile_rec.message_text_id <> NVL( l_message_text_id, FND_API.G_MISS_NUM ) ) )
5527: THEN
5528:
5529: check_message_text_id_fk(
5530: p_column => 'message_text_id',
5574: * p_cust_profile_amt_rec Customer profile amount record.
5575: * p_rowid Rowid of the record (used only in update mode).
5576: * IN/OUT:
5577: * x_return_status Return status after the call. The status can
5578: * be FND_API.G_RET_STS_SUCCESS (success),
5579: * FND_API.G_RET_STS_ERROR (error),
5580: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5581: *
5582: * NOTES
5575: * p_rowid Rowid of the record (used only in update mode).
5576: * IN/OUT:
5577: * x_return_status Return status after the call. The status can
5578: * be FND_API.G_RET_STS_SUCCESS (success),
5579: * FND_API.G_RET_STS_ERROR (error),
5580: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5581: *
5582: * NOTES
5583: *
5576: * IN/OUT:
5577: * x_return_status Return status after the call. The status can
5578: * be FND_API.G_RET_STS_SUCCESS (success),
5579: * FND_API.G_RET_STS_ERROR (error),
5580: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5581: *
5582: * NOTES
5583: *
5584: * MODIFICATION HISTORY
5682: -- If primary key value is not passed, it will be generated
5683: -- from sequence by table handler.
5684:
5685: IF p_cust_profile_amt_rec.cust_acct_profile_amt_id IS NOT NULL AND
5686: p_cust_profile_amt_rec.cust_acct_profile_amt_id <> FND_API.G_MISS_NUM
5687: THEN
5688: BEGIN
5689: SELECT 'Y' INTO l_dummy
5690: FROM HZ_CUST_PROFILE_AMTS
5692:
5693: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
5694: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_profile_amt_id' );
5695: FND_MSG_PUB.ADD;
5696: x_return_status := FND_API.G_RET_STS_ERROR;
5697: EXCEPTION
5698: WHEN NO_DATA_FOUND THEN
5699: NULL;
5700: END;
5770: p_msg_level=>fnd_log.level_statement);
5771: END IF;
5772:
5773: IF p_create_update_flag = 'C' AND
5774: p_check_foreign_key = FND_API.G_TRUE AND
5775: p_cust_profile_amt_rec.cust_account_profile_id IS NOT NULL AND
5776: p_cust_profile_amt_rec.cust_account_profile_id <> FND_API.G_MISS_NUM
5777: THEN
5778: BEGIN
5772:
5773: IF p_create_update_flag = 'C' AND
5774: p_check_foreign_key = FND_API.G_TRUE AND
5775: p_cust_profile_amt_rec.cust_account_profile_id IS NOT NULL AND
5776: p_cust_profile_amt_rec.cust_account_profile_id <> FND_API.G_MISS_NUM
5777: THEN
5778: BEGIN
5779: -- select cust_account_id, site_use_id for later cross reference checking
5780: -- Bug 4770111 : select created_by_module also
5790: FND_MESSAGE.SET_TOKEN( 'FK', 'cust_account_profile_id' );
5791: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_profile_id' );
5792: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_customer_profiles' );
5793: FND_MSG_PUB.ADD;
5794: x_return_status := FND_API.G_RET_STS_ERROR;
5795: END;
5796:
5797: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5798: hz_utility_v2pub.debug(p_message=>'cust_account_profile_id is foreign key to hz_customer_profiles. ' ||
5864: p_msg_level=>fnd_log.level_statement);
5865: END IF;
5866:
5867: IF p_create_update_flag = 'C' AND
5868: p_check_foreign_key = FND_API.G_TRUE AND
5869: p_cust_profile_amt_rec.cust_account_id IS NOT NULL AND
5870: p_cust_profile_amt_rec.cust_account_id <> FND_API.G_MISS_NUM
5871: THEN
5872: check_cust_account_fk (
5866:
5867: IF p_create_update_flag = 'C' AND
5868: p_check_foreign_key = FND_API.G_TRUE AND
5869: p_cust_profile_amt_rec.cust_account_id IS NOT NULL AND
5870: p_cust_profile_amt_rec.cust_account_id <> FND_API.G_MISS_NUM
5871: THEN
5872: check_cust_account_fk (
5873: p_column => 'cust_account_id',
5874: p_column_value => p_cust_profile_amt_rec.cust_account_id,
5886: IF p_cust_profile_amt_rec.cust_account_id <> l_cust_account_id THEN
5887: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_PROF_AMT_IDS_MISMATCH' );
5888: FND_MESSAGE.SET_TOKEN( 'ENTITY', 'customer' );
5889: FND_MSG_PUB.ADD;
5890: x_return_status := FND_API.G_RET_STS_ERROR;
5891: END IF;
5892:
5893: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5894: hz_utility_v2pub.debug(p_message=>'cust_account_id should be the same as cust_account_id in corresponding customer profile. ' ||
5953: -- we only need to check FK during creation.
5954:
5955: IF p_create_update_flag = 'C' AND
5956: p_cust_profile_amt_rec.currency_code IS NOT NULL AND
5957: p_cust_profile_amt_rec.currency_code <> FND_API.G_MISS_CHAR
5958: THEN
5959: check_currency_fk (
5960: p_column => 'currency_code',
5961: p_column_value => p_cust_profile_amt_rec.currency_code,
5985:
5986: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
5987: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_profile_id - currency_code' );
5988: FND_MSG_PUB.ADD;
5989: x_return_status := FND_API.G_RET_STS_ERROR;
5990: EXCEPTION
5991: WHEN NO_DATA_FOUND THEN
5992: NULL;
5993: END;
6034: -- Since site_use_id is non-updateable, we only need to
6035: -- check FK during creation.
6036:
6037: IF p_create_update_flag = 'C' AND
6038: p_check_foreign_key = FND_API.G_TRUE
6039: THEN
6040: IF p_cust_profile_amt_rec.site_use_id IS NOT NULL AND
6041: p_cust_profile_amt_rec.site_use_id <> FND_API.G_MISS_NUM
6042: THEN
6037: IF p_create_update_flag = 'C' AND
6038: p_check_foreign_key = FND_API.G_TRUE
6039: THEN
6040: IF p_cust_profile_amt_rec.site_use_id IS NOT NULL AND
6041: p_cust_profile_amt_rec.site_use_id <> FND_API.G_MISS_NUM
6042: THEN
6043: check_cust_site_use_fk (
6044: p_column => 'site_use_id',
6045: p_column_value => p_cust_profile_amt_rec.site_use_id,
6055:
6056: -- cross reference checking
6057:
6058: IF ( p_cust_profile_amt_rec.site_use_id IS NOT NULL
6059: AND p_cust_profile_amt_rec.site_use_id <> FND_API.G_MISS_NUM AND
6060: ( l_site_use_id IS NULL OR
6061: l_site_use_id <> p_cust_profile_amt_rec.site_use_id ) ) OR
6062: ( ( p_cust_profile_amt_rec.site_use_id IS NULL OR
6063: p_cust_profile_amt_rec.site_use_id = FND_API.G_MISS_NUM ) AND
6059: AND p_cust_profile_amt_rec.site_use_id <> FND_API.G_MISS_NUM AND
6060: ( l_site_use_id IS NULL OR
6061: l_site_use_id <> p_cust_profile_amt_rec.site_use_id ) ) OR
6062: ( ( p_cust_profile_amt_rec.site_use_id IS NULL OR
6063: p_cust_profile_amt_rec.site_use_id = FND_API.G_MISS_NUM ) AND
6064: l_site_use_id IS NOT NULL )
6065: THEN
6066: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_PROF_AMT_IDS_MISMATCH' );
6067: FND_MESSAGE.SET_TOKEN( 'ENTITY', 'site use' );
6065: THEN
6066: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_PROF_AMT_IDS_MISMATCH' );
6067: FND_MESSAGE.SET_TOKEN( 'ENTITY', 'site use' );
6068: FND_MSG_PUB.ADD;
6069: x_return_status := FND_API.G_RET_STS_ERROR;
6070: END IF;
6071:
6072: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6073: hz_utility_v2pub.debug(p_message=>'site_use_id should be the same as site_use_id site_use_id in corresponding customer profile. ' ||
6119: ------------------------------------
6120: --*** Credit Limit Validations ***--
6121: ------------------------------------
6122:
6123: IF p_cust_profile_amt_rec.overall_credit_limit <> FND_API.G_MISS_NUM AND
6124: p_cust_profile_amt_rec.overall_credit_limit IS NOT NULL
6125: THEN
6126: IF p_cust_profile_amt_rec.trx_credit_limit <> FND_API.G_MISS_NUM AND
6127: p_cust_profile_amt_rec.trx_credit_limit IS NOT NULL
6122:
6123: IF p_cust_profile_amt_rec.overall_credit_limit <> FND_API.G_MISS_NUM AND
6124: p_cust_profile_amt_rec.overall_credit_limit IS NOT NULL
6125: THEN
6126: IF p_cust_profile_amt_rec.trx_credit_limit <> FND_API.G_MISS_NUM AND
6127: p_cust_profile_amt_rec.trx_credit_limit IS NOT NULL
6128: THEN
6129:
6130: IF ( p_create_update_flag = 'C' OR
6129:
6130: IF ( p_create_update_flag = 'C' OR
6131: ( p_create_update_flag = 'U' AND
6132: (
6133: (p_cust_profile_amt_rec.overall_credit_limit <> NVL( l_overall_credit_limit, FND_API.G_MISS_NUM ))
6134: OR
6135: (p_cust_profile_amt_rec.trx_credit_limit <> NVL(l_trx_credit_limit,FND_API.G_MISS_NUM))
6136: )
6137: )
6131: ( p_create_update_flag = 'U' AND
6132: (
6133: (p_cust_profile_amt_rec.overall_credit_limit <> NVL( l_overall_credit_limit, FND_API.G_MISS_NUM ))
6134: OR
6135: (p_cust_profile_amt_rec.trx_credit_limit <> NVL(l_trx_credit_limit,FND_API.G_MISS_NUM))
6136: )
6137: )
6138: )
6139: THEN
6140: IF p_cust_profile_amt_rec.overall_credit_limit < p_cust_profile_amt_rec.trx_credit_limit
6141: THEN
6142: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_OVERALL_CREDIT' );
6143: FND_MSG_PUB.ADD;
6144: x_return_status := FND_API.G_RET_STS_ERROR;
6145:
6146: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6147: hz_utility_v2pub.debug(p_message=>'overall_credit_limit should be greater than the trx_credit_limit. ' ||
6148: 'x_return_status = ' || x_return_status,
6165: ----------------------------------------------
6166:
6167: -- MIN_FC_INVOICE_OVERDUE_TYPE is lookup code in lookup type AR_AMOUNT_PERCENT
6168: IF p_cust_profile_amt_rec.min_fc_invoice_overdue_type IS NOT NULL AND
6169: p_cust_profile_amt_rec.min_fc_invoice_overdue_type <> FND_API.G_MISS_CHAR AND
6170: ( p_create_update_flag = 'C' OR
6171: ( p_create_update_flag = 'U' AND
6172: p_cust_profile_amt_rec.min_fc_invoice_overdue_type <> NVL( l_min_fc_invoice_overdue_type, FND_API.G_MISS_CHAR ) ) )
6173: THEN
6168: IF p_cust_profile_amt_rec.min_fc_invoice_overdue_type IS NOT NULL AND
6169: p_cust_profile_amt_rec.min_fc_invoice_overdue_type <> FND_API.G_MISS_CHAR AND
6170: ( p_create_update_flag = 'C' OR
6171: ( p_create_update_flag = 'U' AND
6172: p_cust_profile_amt_rec.min_fc_invoice_overdue_type <> NVL( l_min_fc_invoice_overdue_type, FND_API.G_MISS_CHAR ) ) )
6173: THEN
6174: validate_lookup (
6175: p_column => 'min_fc_invoice_overdue_type',
6176: p_lookup_table => 'AR_LOOKUPS',
6199: ----------------------------------------------
6200:
6201: -- MIN_FC_BALANCE_OVERDUE_TYPE is lookup code in lookup type AR_AMOUNT_PERCENT
6202: IF p_cust_profile_amt_rec.min_fc_balance_overdue_type IS NOT NULL AND
6203: p_cust_profile_amt_rec.min_fc_balance_overdue_type <> FND_API.G_MISS_CHAR AND
6204: ( p_create_update_flag = 'C' OR
6205: ( p_create_update_flag = 'U' AND
6206: p_cust_profile_amt_rec.min_fc_balance_overdue_type <> NVL( l_min_fc_balance_overdue_type, FND_API.G_MISS_CHAR ) ) )
6207: THEN
6202: IF p_cust_profile_amt_rec.min_fc_balance_overdue_type IS NOT NULL AND
6203: p_cust_profile_amt_rec.min_fc_balance_overdue_type <> FND_API.G_MISS_CHAR AND
6204: ( p_create_update_flag = 'C' OR
6205: ( p_create_update_flag = 'U' AND
6206: p_cust_profile_amt_rec.min_fc_balance_overdue_type <> NVL( l_min_fc_balance_overdue_type, FND_API.G_MISS_CHAR ) ) )
6207: THEN
6208: validate_lookup (
6209: p_column => 'min_fc_balance_overdue_type',
6210: p_lookup_table => 'AR_LOOKUPS',
6233: ----------------------------------------------
6234:
6235: -- INTEREST_TYPE is lookup code in lookup type AR_INTEREST_PENALTY_TYPE
6236: IF p_cust_profile_amt_rec.interest_type IS NOT NULL AND
6237: p_cust_profile_amt_rec.interest_type <> FND_API.G_MISS_CHAR AND
6238: ( p_create_update_flag = 'C' OR
6239: ( p_create_update_flag = 'U' AND
6240: p_cust_profile_amt_rec.interest_type <> NVL( l_interest_type, FND_API.G_MISS_CHAR ) ) )
6241: THEN
6236: IF p_cust_profile_amt_rec.interest_type IS NOT NULL AND
6237: p_cust_profile_amt_rec.interest_type <> FND_API.G_MISS_CHAR AND
6238: ( p_create_update_flag = 'C' OR
6239: ( p_create_update_flag = 'U' AND
6240: p_cust_profile_amt_rec.interest_type <> NVL( l_interest_type, FND_API.G_MISS_CHAR ) ) )
6241: THEN
6242: validate_lookup (
6243: p_column => 'interest_type',
6244: p_lookup_table => 'AR_LOOKUPS',
6267: ----------------------------------------------
6268:
6269: -- PENALTY_TYPE is lookup code in lookup type AR_INTEREST_PENALTY_TYPE
6270: IF p_cust_profile_amt_rec.penalty_type IS NOT NULL AND
6271: p_cust_profile_amt_rec.penalty_type <> FND_API.G_MISS_CHAR AND
6272: ( p_create_update_flag = 'C' OR
6273: ( p_create_update_flag = 'U' AND
6274: p_cust_profile_amt_rec.penalty_type <> NVL( l_penalty_type, FND_API.G_MISS_CHAR ) ) )
6275: THEN
6270: IF p_cust_profile_amt_rec.penalty_type IS NOT NULL AND
6271: p_cust_profile_amt_rec.penalty_type <> FND_API.G_MISS_CHAR AND
6272: ( p_create_update_flag = 'C' OR
6273: ( p_create_update_flag = 'U' AND
6274: p_cust_profile_amt_rec.penalty_type <> NVL( l_penalty_type, FND_API.G_MISS_CHAR ) ) )
6275: THEN
6276: validate_lookup (
6277: p_column => 'penalty_type',
6278: p_lookup_table => 'AR_LOOKUPS',
6310: -- exchange_rate_type should be a valid value defined in GL_DAILY_CONVERSION_TYPES
6311: -- The type USER is excluded.
6312:
6313: IF p_cust_profile_amt_rec.exchange_rate_type is NOT NULL AND
6314: p_cust_profile_amt_rec.exchange_rate_type <> FND_API.G_MISS_CHAR
6315: AND ( p_create_update_flag = 'C' OR
6316: ( p_create_update_flag = 'U' AND
6317: p_cust_profile_amt_rec.exchange_rate_type <> NVL( l_exchange_rate_type, FND_API.G_MISS_CHAR ) ) )
6318: THEN
6313: IF p_cust_profile_amt_rec.exchange_rate_type is NOT NULL AND
6314: p_cust_profile_amt_rec.exchange_rate_type <> FND_API.G_MISS_CHAR
6315: AND ( p_create_update_flag = 'C' OR
6316: ( p_create_update_flag = 'U' AND
6317: p_cust_profile_amt_rec.exchange_rate_type <> NVL( l_exchange_rate_type, FND_API.G_MISS_CHAR ) ) )
6318: THEN
6319:
6320: check_exchange_rate_type_fk(
6321: p_column => 'exchange_rate_type',
6354: v_cust_profile_amt_rec.min_fc_invoice_overdue_type := l_min_fc_invoice_overdue_type;
6355: END IF;
6356: IF p_cust_profile_amt_rec.min_fc_invoice_amount is NULL THEN
6357: v_cust_profile_amt_rec.min_fc_invoice_amount := l_min_fc_invoice_amount;
6358: ELSIF p_cust_profile_amt_rec.min_fc_invoice_amount = FND_API.G_MISS_NUM THEN
6359: v_cust_profile_amt_rec.min_fc_invoice_amount := NULL;
6360: END IF;
6361: IF p_cust_profile_amt_rec.min_fc_invoice_percent is NULL THEN
6362: v_cust_profile_amt_rec.min_fc_invoice_percent := l_min_fc_invoice_percent;
6359: v_cust_profile_amt_rec.min_fc_invoice_amount := NULL;
6360: END IF;
6361: IF p_cust_profile_amt_rec.min_fc_invoice_percent is NULL THEN
6362: v_cust_profile_amt_rec.min_fc_invoice_percent := l_min_fc_invoice_percent;
6363: ELSIF p_cust_profile_amt_rec.min_fc_invoice_percent = FND_API.G_MISS_NUM THEN
6364: v_cust_profile_amt_rec.min_fc_invoice_percent := NULL;
6365: END IF;
6366: END IF;
6367:
6365: END IF;
6366: END IF;
6367:
6368: IF p_create_update_flag = 'C' THEN
6369: IF p_cust_profile_amt_rec.min_fc_invoice_overdue_type = FND_API.G_MISS_CHAR THEN
6370: v_cust_profile_amt_rec.min_fc_invoice_overdue_type := NULL;
6371: END IF;
6372: IF p_cust_profile_amt_rec.min_fc_invoice_amount = FND_API.G_MISS_NUM THEN
6373: v_cust_profile_amt_rec.min_fc_invoice_amount := NULL;
6368: IF p_create_update_flag = 'C' THEN
6369: IF p_cust_profile_amt_rec.min_fc_invoice_overdue_type = FND_API.G_MISS_CHAR THEN
6370: v_cust_profile_amt_rec.min_fc_invoice_overdue_type := NULL;
6371: END IF;
6372: IF p_cust_profile_amt_rec.min_fc_invoice_amount = FND_API.G_MISS_NUM THEN
6373: v_cust_profile_amt_rec.min_fc_invoice_amount := NULL;
6374: END IF;
6375: IF p_cust_profile_amt_rec.min_fc_invoice_percent = FND_API.G_MISS_NUM THEN
6376: v_cust_profile_amt_rec.min_fc_invoice_percent := NULL;
6371: END IF;
6372: IF p_cust_profile_amt_rec.min_fc_invoice_amount = FND_API.G_MISS_NUM THEN
6373: v_cust_profile_amt_rec.min_fc_invoice_amount := NULL;
6374: END IF;
6375: IF p_cust_profile_amt_rec.min_fc_invoice_percent = FND_API.G_MISS_NUM THEN
6376: v_cust_profile_amt_rec.min_fc_invoice_percent := NULL;
6377: END IF;
6378: END IF;
6379:
6381: IF (v_cust_profile_amt_rec.min_fc_invoice_amount IS NULL OR
6382: v_cust_profile_amt_rec.min_fc_invoice_percent IS NOT NULL) THEN
6383: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PERCENT' );
6384: FND_MSG_PUB.ADD;
6385: x_return_status := FND_API.G_RET_STS_ERROR;
6386: END IF;
6387: ELSIF v_cust_profile_amt_rec.min_fc_invoice_overdue_type = 'PERCENT' THEN
6388: IF (v_cust_profile_amt_rec.min_fc_invoice_amount IS NOT NULL OR
6389: v_cust_profile_amt_rec.min_fc_invoice_percent IS NULL) THEN
6388: IF (v_cust_profile_amt_rec.min_fc_invoice_amount IS NOT NULL OR
6389: v_cust_profile_amt_rec.min_fc_invoice_percent IS NULL) THEN
6390: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PERCENT' );
6391: FND_MSG_PUB.ADD;
6392: x_return_status := FND_API.G_RET_STS_ERROR;
6393: ELSE
6394: check_less_than_100 (
6395: p_column => 'min_fc_invoice_percent',
6396: p_column_value => v_cust_profile_amt_rec.min_fc_invoice_percent,
6407: IF (v_cust_profile_amt_rec.min_fc_invoice_amount IS NOT NULL OR
6408: v_cust_profile_amt_rec.min_fc_invoice_percent IS NOT NULL) THEN
6409: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PERCENT' );
6410: FND_MSG_PUB.ADD;
6411: x_return_status := FND_API.G_RET_STS_ERROR;
6412: END IF;
6413: END IF;
6414:
6415: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6435: v_cust_profile_amt_rec.min_fc_balance_overdue_type := l_min_fc_balance_overdue_type;
6436: END IF;
6437: IF p_cust_profile_amt_rec.min_fc_balance_amount is NULL THEN
6438: v_cust_profile_amt_rec.min_fc_balance_amount := l_min_fc_balance_amount;
6439: ELSIF p_cust_profile_amt_rec.min_fc_balance_amount = FND_API.G_MISS_NUM THEN
6440: v_cust_profile_amt_rec.min_fc_balance_amount := NULL;
6441: END IF;
6442: IF p_cust_profile_amt_rec.min_fc_balance_percent is NULL THEN
6443: v_cust_profile_amt_rec.min_fc_balance_percent := l_min_fc_balance_percent;
6440: v_cust_profile_amt_rec.min_fc_balance_amount := NULL;
6441: END IF;
6442: IF p_cust_profile_amt_rec.min_fc_balance_percent is NULL THEN
6443: v_cust_profile_amt_rec.min_fc_balance_percent := l_min_fc_balance_percent;
6444: ELSIF p_cust_profile_amt_rec.min_fc_balance_percent = FND_API.G_MISS_NUM THEN
6445: v_cust_profile_amt_rec.min_fc_balance_percent := NULL;
6446: END IF;
6447: END IF;
6448:
6446: END IF;
6447: END IF;
6448:
6449: IF p_create_update_flag = 'C' THEN
6450: IF p_cust_profile_amt_rec.min_fc_balance_overdue_type = FND_API.G_MISS_CHAR THEN
6451: v_cust_profile_amt_rec.min_fc_balance_overdue_type := NULL;
6452: END IF;
6453: IF p_cust_profile_amt_rec.min_fc_balance_amount = FND_API.G_MISS_NUM THEN
6454: v_cust_profile_amt_rec.min_fc_balance_amount := NULL;
6449: IF p_create_update_flag = 'C' THEN
6450: IF p_cust_profile_amt_rec.min_fc_balance_overdue_type = FND_API.G_MISS_CHAR THEN
6451: v_cust_profile_amt_rec.min_fc_balance_overdue_type := NULL;
6452: END IF;
6453: IF p_cust_profile_amt_rec.min_fc_balance_amount = FND_API.G_MISS_NUM THEN
6454: v_cust_profile_amt_rec.min_fc_balance_amount := NULL;
6455: END IF;
6456: IF p_cust_profile_amt_rec.min_fc_balance_percent = FND_API.G_MISS_NUM THEN
6457: v_cust_profile_amt_rec.min_fc_balance_percent := NULL;
6452: END IF;
6453: IF p_cust_profile_amt_rec.min_fc_balance_amount = FND_API.G_MISS_NUM THEN
6454: v_cust_profile_amt_rec.min_fc_balance_amount := NULL;
6455: END IF;
6456: IF p_cust_profile_amt_rec.min_fc_balance_percent = FND_API.G_MISS_NUM THEN
6457: v_cust_profile_amt_rec.min_fc_balance_percent := NULL;
6458: END IF;
6459: END IF;
6460:
6462: IF (v_cust_profile_amt_rec.min_fc_balance_amount IS NULL OR
6463: v_cust_profile_amt_rec.min_fc_balance_percent IS NOT NULL) THEN
6464: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_BAL_AMT' );
6465: FND_MSG_PUB.ADD;
6466: x_return_status := FND_API.G_RET_STS_ERROR;
6467: END IF;
6468: ELSIF v_cust_profile_amt_rec.min_fc_balance_overdue_type = 'PERCENT' THEN
6469: IF (v_cust_profile_amt_rec.min_fc_balance_amount IS NOT NULL OR
6470: v_cust_profile_amt_rec.min_fc_balance_percent IS NULL) THEN
6469: IF (v_cust_profile_amt_rec.min_fc_balance_amount IS NOT NULL OR
6470: v_cust_profile_amt_rec.min_fc_balance_percent IS NULL) THEN
6471: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_BAL_AMT' );
6472: FND_MSG_PUB.ADD;
6473: x_return_status := FND_API.G_RET_STS_ERROR;
6474: ELSE
6475: check_less_than_100 (
6476: p_column => 'min_fc_balance_percent',
6477: p_column_value => v_cust_profile_amt_rec.min_fc_balance_percent,
6488: IF (v_cust_profile_amt_rec.min_fc_balance_amount IS NOT NULL OR
6489: v_cust_profile_amt_rec.min_fc_balance_percent IS NOT NULL) THEN
6490: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_BAL_AMT' );
6491: FND_MSG_PUB.ADD;
6492: x_return_status := FND_API.G_RET_STS_ERROR;
6493: END IF;
6494: END IF;
6495:
6496: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6515: v_cust_profile_amt_rec.interest_type := l_interest_type;
6516: END IF;
6517: IF p_cust_profile_amt_rec.interest_rate is NULL THEN
6518: v_cust_profile_amt_rec.interest_rate := l_interest_rate;
6519: ELSIF p_cust_profile_amt_rec.interest_rate = FND_API.G_MISS_NUM THEN
6520: v_cust_profile_amt_rec.interest_rate := NULL;
6521: END IF;
6522: IF p_cust_profile_amt_rec.interest_fixed_amount is NULL THEN
6523: v_cust_profile_amt_rec.interest_fixed_amount := l_interest_fixed_amount;
6520: v_cust_profile_amt_rec.interest_rate := NULL;
6521: END IF;
6522: IF p_cust_profile_amt_rec.interest_fixed_amount is NULL THEN
6523: v_cust_profile_amt_rec.interest_fixed_amount := l_interest_fixed_amount;
6524: ELSIF p_cust_profile_amt_rec.interest_fixed_amount = FND_API.G_MISS_NUM THEN
6525: v_cust_profile_amt_rec.interest_fixed_amount := NULL;
6526: END IF;
6527: IF p_cust_profile_amt_rec.interest_schedule_id is NULL THEN
6528: v_cust_profile_amt_rec.interest_schedule_id := l_interest_schedule_id;
6525: v_cust_profile_amt_rec.interest_fixed_amount := NULL;
6526: END IF;
6527: IF p_cust_profile_amt_rec.interest_schedule_id is NULL THEN
6528: v_cust_profile_amt_rec.interest_schedule_id := l_interest_schedule_id;
6529: ELSIF p_cust_profile_amt_rec.interest_schedule_id = FND_API.G_MISS_NUM THEN
6530: v_cust_profile_amt_rec.interest_schedule_id := NULL;
6531: END IF;
6532: END IF;
6533:
6531: END IF;
6532: END IF;
6533:
6534: IF p_create_update_flag = 'C' THEN
6535: IF p_cust_profile_amt_rec.interest_type = FND_API.G_MISS_CHAR THEN
6536: v_cust_profile_amt_rec.interest_type := NULL;
6537: END IF;
6538: IF p_cust_profile_amt_rec.interest_rate = FND_API.G_MISS_NUM THEN
6539: v_cust_profile_amt_rec.interest_rate := NULL;
6534: IF p_create_update_flag = 'C' THEN
6535: IF p_cust_profile_amt_rec.interest_type = FND_API.G_MISS_CHAR THEN
6536: v_cust_profile_amt_rec.interest_type := NULL;
6537: END IF;
6538: IF p_cust_profile_amt_rec.interest_rate = FND_API.G_MISS_NUM THEN
6539: v_cust_profile_amt_rec.interest_rate := NULL;
6540: END IF;
6541: IF p_cust_profile_amt_rec.interest_fixed_amount = FND_API.G_MISS_NUM THEN
6542: v_cust_profile_amt_rec.interest_fixed_amount := NULL;
6537: END IF;
6538: IF p_cust_profile_amt_rec.interest_rate = FND_API.G_MISS_NUM THEN
6539: v_cust_profile_amt_rec.interest_rate := NULL;
6540: END IF;
6541: IF p_cust_profile_amt_rec.interest_fixed_amount = FND_API.G_MISS_NUM THEN
6542: v_cust_profile_amt_rec.interest_fixed_amount := NULL;
6543: END IF;
6544: IF p_cust_profile_amt_rec.interest_schedule_id = FND_API.G_MISS_NUM THEN
6545: v_cust_profile_amt_rec.interest_schedule_id := NULL;
6540: END IF;
6541: IF p_cust_profile_amt_rec.interest_fixed_amount = FND_API.G_MISS_NUM THEN
6542: v_cust_profile_amt_rec.interest_fixed_amount := NULL;
6543: END IF;
6544: IF p_cust_profile_amt_rec.interest_schedule_id = FND_API.G_MISS_NUM THEN
6545: v_cust_profile_amt_rec.interest_schedule_id := NULL;
6546: END IF;
6547: END IF;
6548:
6551: v_cust_profile_amt_rec.interest_fixed_amount IS NOT NULL OR
6552: v_cust_profile_amt_rec.interest_schedule_id IS NOT NULL) THEN
6553: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_INT_RATE' );
6554: FND_MSG_PUB.ADD;
6555: x_return_status := FND_API.G_RET_STS_ERROR;
6556: ELSE
6557: check_less_than_100 (
6558: p_column => 'interest_rate',
6559: p_column_value => v_cust_profile_amt_rec.interest_rate,
6571: v_cust_profile_amt_rec.interest_fixed_amount IS NULL OR
6572: v_cust_profile_amt_rec.interest_schedule_id IS NOT NULL) THEN
6573: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_INT_RATE' );
6574: FND_MSG_PUB.ADD;
6575: x_return_status := FND_API.G_RET_STS_ERROR;
6576: END IF;
6577: /*6469663 Late charge enhacement handling case oftier basis calculation*/
6578: ELSIF v_cust_profile_amt_rec.interest_type IN ( 'CHARGES_SCHEDULE','CHARGE_PER_TIER') THEN
6579: IF (v_cust_profile_amt_rec.interest_rate IS NOT NULL OR
6580: v_cust_profile_amt_rec.interest_fixed_amount IS NOT NULL OR
6581: v_cust_profile_amt_rec.interest_schedule_id IS NULL) THEN
6582: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_INT_RATE' );
6583: FND_MSG_PUB.ADD;
6584: x_return_status := FND_API.G_RET_STS_ERROR;
6585: ELSE
6586: check_charge_schedule_id_fk (
6587: p_column => 'interest_schedule_id',
6588: p_column_value => v_cust_profile_amt_rec.interest_schedule_id,
6600: v_cust_profile_amt_rec.interest_fixed_amount IS NOT NULL OR
6601: v_cust_profile_amt_rec.interest_schedule_id IS NOT NULL) THEN
6602: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_INT_RATE' );
6603: FND_MSG_PUB.ADD;
6604: x_return_status := FND_API.G_RET_STS_ERROR;
6605: END IF;
6606: END IF;
6607:
6608: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6627: v_cust_profile_amt_rec.penalty_type := l_penalty_type;
6628: END IF;
6629: IF p_cust_profile_amt_rec.penalty_rate is NULL THEN
6630: v_cust_profile_amt_rec.penalty_rate := l_penalty_rate;
6631: ELSIF p_cust_profile_amt_rec.penalty_rate = FND_API.G_MISS_NUM THEN
6632: v_cust_profile_amt_rec.penalty_rate := NULL;
6633: END IF;
6634: IF p_cust_profile_amt_rec.penalty_fixed_amount is NULL THEN
6635: v_cust_profile_amt_rec.penalty_fixed_amount := l_penalty_fixed_amount;
6632: v_cust_profile_amt_rec.penalty_rate := NULL;
6633: END IF;
6634: IF p_cust_profile_amt_rec.penalty_fixed_amount is NULL THEN
6635: v_cust_profile_amt_rec.penalty_fixed_amount := l_penalty_fixed_amount;
6636: ELSIF p_cust_profile_amt_rec.penalty_fixed_amount = FND_API.G_MISS_NUM THEN
6637: v_cust_profile_amt_rec.penalty_fixed_amount := NULL;
6638: END IF;
6639: IF p_cust_profile_amt_rec.penalty_schedule_id is NULL THEN
6640: v_cust_profile_amt_rec.penalty_schedule_id := l_penalty_schedule_id;
6637: v_cust_profile_amt_rec.penalty_fixed_amount := NULL;
6638: END IF;
6639: IF p_cust_profile_amt_rec.penalty_schedule_id is NULL THEN
6640: v_cust_profile_amt_rec.penalty_schedule_id := l_penalty_schedule_id;
6641: ELSIF p_cust_profile_amt_rec.penalty_schedule_id = FND_API.G_MISS_NUM THEN
6642: v_cust_profile_amt_rec.penalty_schedule_id := NULL;
6643: END IF;
6644: END IF;
6645:
6643: END IF;
6644: END IF;
6645:
6646: IF p_create_update_flag = 'C' THEN
6647: IF p_cust_profile_amt_rec.penalty_type = FND_API.G_MISS_CHAR THEN
6648: v_cust_profile_amt_rec.penalty_type := NULL;
6649: END IF;
6650: IF p_cust_profile_amt_rec.penalty_rate = FND_API.G_MISS_NUM THEN
6651: v_cust_profile_amt_rec.penalty_rate := NULL;
6646: IF p_create_update_flag = 'C' THEN
6647: IF p_cust_profile_amt_rec.penalty_type = FND_API.G_MISS_CHAR THEN
6648: v_cust_profile_amt_rec.penalty_type := NULL;
6649: END IF;
6650: IF p_cust_profile_amt_rec.penalty_rate = FND_API.G_MISS_NUM THEN
6651: v_cust_profile_amt_rec.penalty_rate := NULL;
6652: END IF;
6653: IF p_cust_profile_amt_rec.penalty_fixed_amount = FND_API.G_MISS_NUM THEN
6654: v_cust_profile_amt_rec.penalty_fixed_amount := NULL;
6649: END IF;
6650: IF p_cust_profile_amt_rec.penalty_rate = FND_API.G_MISS_NUM THEN
6651: v_cust_profile_amt_rec.penalty_rate := NULL;
6652: END IF;
6653: IF p_cust_profile_amt_rec.penalty_fixed_amount = FND_API.G_MISS_NUM THEN
6654: v_cust_profile_amt_rec.penalty_fixed_amount := NULL;
6655: END IF;
6656: IF p_cust_profile_amt_rec.penalty_schedule_id = FND_API.G_MISS_NUM THEN
6657: v_cust_profile_amt_rec.penalty_schedule_id := NULL;
6652: END IF;
6653: IF p_cust_profile_amt_rec.penalty_fixed_amount = FND_API.G_MISS_NUM THEN
6654: v_cust_profile_amt_rec.penalty_fixed_amount := NULL;
6655: END IF;
6656: IF p_cust_profile_amt_rec.penalty_schedule_id = FND_API.G_MISS_NUM THEN
6657: v_cust_profile_amt_rec.penalty_schedule_id := NULL;
6658: END IF;
6659: END IF;
6660:
6663: v_cust_profile_amt_rec.penalty_fixed_amount IS NOT NULL OR
6664: v_cust_profile_amt_rec.penalty_schedule_id IS NOT NULL) THEN
6665: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PEN_RATE' );
6666: FND_MSG_PUB.ADD;
6667: x_return_status := FND_API.G_RET_STS_ERROR;
6668: ELSE
6669: check_less_than_100 (
6670: p_column => 'penalty_rate',
6671: p_column_value => v_cust_profile_amt_rec.penalty_rate,
6683: v_cust_profile_amt_rec.penalty_fixed_amount IS NULL OR
6684: v_cust_profile_amt_rec.penalty_schedule_id IS NOT NULL) THEN
6685: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PEN_RATE' );
6686: FND_MSG_PUB.ADD;
6687: x_return_status := FND_API.G_RET_STS_ERROR;
6688: END IF;
6689: /*6469663 Late charge enhacement handling case oftier basis calculation*/
6690: ELSIF v_cust_profile_amt_rec.penalty_type IN ('CHARGES_SCHEDULE','CHARGE_PER_TIER') THEN
6691: IF (v_cust_profile_amt_rec.penalty_rate IS NOT NULL OR
6692: v_cust_profile_amt_rec.penalty_fixed_amount IS NOT NULL OR
6693: v_cust_profile_amt_rec.penalty_schedule_id IS NULL) THEN
6694: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PEN_RATE' );
6695: FND_MSG_PUB.ADD;
6696: x_return_status := FND_API.G_RET_STS_ERROR;
6697: ELSE
6698: check_charge_schedule_id_fk (
6699: p_column => 'penalty_schedule_id',
6700: p_column_value => v_cust_profile_amt_rec.penalty_schedule_id,
6712: v_cust_profile_amt_rec.penalty_fixed_amount IS NOT NULL OR
6713: v_cust_profile_amt_rec.penalty_schedule_id IS NOT NULL) THEN
6714: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PEN_RATE' );
6715: FND_MSG_PUB.ADD;
6716: x_return_status := FND_API.G_RET_STS_ERROR;
6717: END IF;
6718: END IF;
6719:
6720: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6745: * IN:
6746: * p_cust_acct_site_rec Customer account site record.
6747: * IN/OUT:
6748: * x_return_status Return status after the call. The status can
6749: * be FND_API.G_RET_STS_SUCCESS (success),
6750: * FND_API.G_RET_STS_ERROR (error),
6751: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6752: *
6753: * NOTES
6746: * p_cust_acct_site_rec Customer account site record.
6747: * IN/OUT:
6748: * x_return_status Return status after the call. The status can
6749: * be FND_API.G_RET_STS_SUCCESS (success),
6750: * FND_API.G_RET_STS_ERROR (error),
6751: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6752: *
6753: * NOTES
6754: *
6747: * IN/OUT:
6748: * x_return_status Return status after the call. The status can
6749: * be FND_API.G_RET_STS_SUCCESS (success),
6750: * FND_API.G_RET_STS_ERROR (error),
6751: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6752: *
6753: * NOTES
6754: *
6755: * MODIFICATION HISTORY
6863: FND_MESSAGE.SET_TOKEN( 'ADDRESS_PARTY_NAME', l_site_party_name );
6864: FND_MESSAGE.SET_TOKEN( 'ACCOUNT_PARTY_NAME', l_account_party_name );
6865: FND_MESSAGE.SET_TOKEN( 'ACCOUNT_NUMBER', l_account_number );
6866: FND_MSG_PUB.ADD;
6867: x_return_status := FND_API.G_RET_STS_ERROR;
6868: END;
6869: ELSE
6870:
6871: BEGIN
6891: FND_MESSAGE.SET_TOKEN( 'ADDRESS_PARTY_NAME', l_site_party_name );
6892: FND_MESSAGE.SET_TOKEN( 'ACCOUNT_PARTY_NAME', l_account_party_name );
6893: FND_MESSAGE.SET_TOKEN( 'ACCOUNT_NUMBER', l_account_number );
6894: FND_MSG_PUB.ADD;
6895: x_return_status := FND_API.G_RET_STS_ERROR;
6896: END;
6897: END IF;
6898:
6899: EXCEPTION
6946: * p_cust_acct_site_rec Customer account site record.
6947: * p_rowid Rowid of the record (used only in update mode).
6948: * IN/OUT:
6949: * x_return_status Return status after the call. The status can
6950: * be FND_API.G_RET_STS_SUCCESS (success),
6951: * FND_API.G_RET_STS_ERROR (error),
6952: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6953: *
6954: * NOTES
6947: * p_rowid Rowid of the record (used only in update mode).
6948: * IN/OUT:
6949: * x_return_status Return status after the call. The status can
6950: * be FND_API.G_RET_STS_SUCCESS (success),
6951: * FND_API.G_RET_STS_ERROR (error),
6952: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6953: *
6954: * NOTES
6955: *
6948: * IN/OUT:
6949: * x_return_status Return status after the call. The status can
6950: * be FND_API.G_RET_STS_SUCCESS (success),
6951: * FND_API.G_RET_STS_ERROR (error),
6952: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6953: *
6954: * NOTES
6955: *
6956: * MODIFICATION HISTORY
7024: -- If primary key value is not passed, it will be generated
7025: -- from sequence by table handler.
7026:
7027: IF p_cust_acct_site_rec.cust_acct_site_id IS NOT NULL AND
7028: p_cust_acct_site_rec.cust_acct_site_id <> FND_API.G_MISS_NUM
7029: THEN
7030: BEGIN
7031: SELECT 'Y' INTO l_dummy
7032: FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
7034:
7035: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
7036: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id' );
7037: FND_MSG_PUB.ADD;
7038: x_return_status := FND_API.G_RET_STS_ERROR;
7039: EXCEPTION
7040: WHEN NO_DATA_FOUND THEN
7041: NULL;
7042: END;
7111: -- non-updateable.
7112: IF p_create_update_flag = 'C' THEN
7113: IF p_cust_acct_site_rec.cust_account_id <> -1 AND
7114: p_cust_acct_site_rec.cust_account_id IS NOT NULL AND
7115: p_cust_acct_site_rec.cust_account_id <> FND_API.G_MISS_NUM
7116: THEN
7117: check_cust_account_fk (
7118: p_column => 'cust_account_id',
7119: p_column_value => p_cust_acct_site_rec.cust_account_id,
7188: -- Do not need to check during update because party_site_id is
7189: -- non-updateable.
7190: IF p_create_update_flag = 'C' AND
7191: p_cust_acct_site_rec.party_site_id IS NOT NULL AND
7192: p_cust_acct_site_rec.party_site_id <> FND_API.G_MISS_NUM
7193: THEN
7194: check_party_site_fk (
7195: p_column => 'party_site_id',
7196: p_column_value => p_cust_acct_site_rec.party_site_id,
7226: EXCEPTION
7227: WHEN NO_DATA_FOUND THEN
7228: FND_MESSAGE.SET_NAME( 'AR', 'HZ_LOC_CONTENT_INVALID' );
7229: FND_MSG_PUB.ADD;
7230: x_return_status := FND_API.G_RET_STS_ERROR;
7231: END;
7232:
7233: -- Debug info.
7234: IF G_DEBUG THEN
7270:
7271: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
7272: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id - party_site_id' );
7273: FND_MSG_PUB.ADD;
7274: x_return_status := FND_API.G_RET_STS_ERROR;
7275: EXCEPTION
7276: WHEN NO_DATA_FOUND THEN
7277: NULL;
7278: END;
7308:
7309: -- Bug 3456489. Post MOAC, this uniqeness should be within orgs.
7310: IF p_create_update_flag = 'C' AND
7311: p_cust_acct_site_rec.orig_system_reference IS NOT NULL AND
7312: p_cust_acct_site_rec.orig_system_reference <> FND_API.G_MISS_CHAR
7313: THEN
7314: BEGIN
7315: SELECT 'Y' INTO l_dummy
7316: FROM HZ_CUST_ACCT_SITES_ALL
7319:
7320: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
7321: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'orig_system_reference' );
7322: FND_MSG_PUB.ADD;
7323: x_return_status := FND_API.G_RET_STS_ERROR;
7324: EXCEPTION
7325: WHEN NO_DATA_FOUND THEN
7326: NULL;
7327: END;
7349: else
7350: l_orig_system_reference1 := p_cust_acct_site_rec.orig_system_reference;
7351: end if;
7352:
7353: IF (p_cust_acct_site_rec.orig_system is not null and p_cust_acct_site_rec.orig_system <>fnd_api.g_miss_char)
7354: and (p_cust_acct_site_rec.orig_system_reference is not null and p_cust_acct_site_rec.orig_system_reference <>fnd_api.g_miss_char)
7355: and p_create_update_flag = 'U'
7356: then
7357: hz_orig_system_ref_pub.get_owner_table_id
7350: l_orig_system_reference1 := p_cust_acct_site_rec.orig_system_reference;
7351: end if;
7352:
7353: IF (p_cust_acct_site_rec.orig_system is not null and p_cust_acct_site_rec.orig_system <>fnd_api.g_miss_char)
7354: and (p_cust_acct_site_rec.orig_system_reference is not null and p_cust_acct_site_rec.orig_system_reference <>fnd_api.g_miss_char)
7355: and p_create_update_flag = 'U'
7356: then
7357: hz_orig_system_ref_pub.get_owner_table_id
7358: (p_orig_system => p_cust_acct_site_rec.orig_system,
7360: p_owner_table_name => 'HZ_CUST_ACCT_SITES_ALL',
7361: x_owner_table_id => l_mosr_owner_table_id,
7362: x_return_status => l_temp_return_status);
7363:
7364: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
7365: l_mosr_owner_table_id= nvl(p_cust_acct_site_rec.cust_acct_site_id,l_mosr_owner_table_id))
7366: THEN
7367: l_validate_flag := 'N';
7368: END IF;
7370: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
7371: -- x_return_status. Set x_return_status to error, ONLY if there is error.
7372: -- In case of success, leave it to carry over previous value as before this call.
7373: -- Fix for Bug 5498116 (29-AUG-2006)
7374: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
7375: x_return_status := l_temp_return_status;
7376: END IF;
7377:
7378: end if;
7411:
7412: -- We have unique index on tp_header_id
7413:
7414: IF p_cust_acct_site_rec.tp_header_id IS NOT NULL AND
7415: p_cust_acct_site_rec.tp_header_id <> FND_API.G_MISS_NUM
7416: THEN
7417: BEGIN
7418: SELECT ROWID INTO l_rowid
7419: FROM HZ_CUST_ACCT_SITES_ALL
7425: THEN
7426: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
7427: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'tp_header_id' );
7428: FND_MSG_PUB.ADD;
7429: x_return_status := FND_API.G_RET_STS_ERROR;
7430: END IF;
7431: EXCEPTION
7432: WHEN NO_DATA_FOUND THEN
7433: NULL;
7473: /****Logical APIs - validation not required****/
7474: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
7475: -- status is lookup code in lookup type CODE_STATUS
7476: IF p_cust_acct_site_rec.status IS NOT NULL AND
7477: p_cust_acct_site_rec.status <> FND_API.G_MISS_CHAR AND
7478: ( p_create_update_flag = 'C' OR
7479: ( p_create_update_flag = 'U' AND
7480: p_cust_acct_site_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
7481: THEN
7476: IF p_cust_acct_site_rec.status IS NOT NULL AND
7477: p_cust_acct_site_rec.status <> FND_API.G_MISS_CHAR AND
7478: ( p_create_update_flag = 'C' OR
7479: ( p_create_update_flag = 'U' AND
7480: p_cust_acct_site_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
7481: THEN
7482: validate_lookup (
7483: p_column => 'status',
7484: p_lookup_type => 'CODE_STATUS',
7507: ----------------------------------------------
7508:
7509: -- customer_category_code is lookup code in lookup type ADDRESS_CATEGORY
7510: IF p_cust_acct_site_rec.customer_category_code IS NOT NULL AND
7511: p_cust_acct_site_rec.customer_category_code <> FND_API.G_MISS_CHAR AND
7512: ( p_create_update_flag = 'C' OR
7513: ( p_create_update_flag = 'U' AND
7514: p_cust_acct_site_rec.customer_category_code <> NVL( l_customer_category_code, FND_API.G_MISS_CHAR ) ) )
7515: THEN
7510: IF p_cust_acct_site_rec.customer_category_code IS NOT NULL AND
7511: p_cust_acct_site_rec.customer_category_code <> FND_API.G_MISS_CHAR AND
7512: ( p_create_update_flag = 'C' OR
7513: ( p_create_update_flag = 'U' AND
7514: p_cust_acct_site_rec.customer_category_code <> NVL( l_customer_category_code, FND_API.G_MISS_CHAR ) ) )
7515: THEN
7516: validate_lookup (
7517: p_column => 'customer_category_code',
7518: p_lookup_type => 'ADDRESS_CATEGORY',
7540: ----------------------------------------------
7541:
7542: -- language is foreign key of fnd installed languages
7543: IF p_cust_acct_site_rec.language IS NOT NULL AND
7544: p_cust_acct_site_rec.language <> FND_API.G_MISS_CHAR
7545: THEN
7546: BEGIN
7547: SELECT 'Y' INTO l_dummy
7548: FROM FND_LANGUAGES
7555: FND_MESSAGE.SET_TOKEN( 'FK', 'language' );
7556: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'language_code' );
7557: FND_MESSAGE.SET_TOKEN( 'TABLE', 'fnd_languages(installed)' );
7558: FND_MSG_PUB.ADD;
7559: x_return_status := FND_API.G_RET_STS_ERROR;
7560: END;
7561:
7562: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
7563: hz_utility_v2pub.debug(p_message=>'language is foreign key of fnd installed languages. ' ||
7581: --------------------------------------
7582:
7583: -- primary_specialist_id is foreign key to per_all_people_f
7584: IF p_cust_acct_site_rec.primary_specialist_id IS NOT NULL AND
7585: p_cust_acct_site_rec.primary_specialist_id <> FND_API.G_MISS_NUM
7586: THEN
7587: check_per_all_people_f_fk (
7588: p_column => 'primary_specialist_id',
7589: p_column_value => p_cust_acct_site_rec.primary_specialist_id,
7609: --------------------------------------
7610:
7611: -- secondary_specialist_id is foreign key to per_all_people_f
7612: IF p_cust_acct_site_rec.secondary_specialist_id IS NOT NULL AND
7613: p_cust_acct_site_rec.secondary_specialist_id <> FND_API.G_MISS_NUM
7614: THEN
7615: /* Bug 3591694 Changed foreign key validation from check_cust_account_fk to
7616: check_per_all_people_f_fk.
7617: */
7661: --------------------------------------
7662: IF p_create_update_flag = 'C' THEN
7663: IF p_cust_acct_site_rec.cust_account_id <> -1 AND
7664: p_cust_acct_site_rec.cust_account_id IS NOT NULL AND
7665: p_cust_acct_site_rec.cust_account_id <> FND_API.G_MISS_NUM
7666: THEN
7667: IF p_cust_acct_site_rec.ece_tp_location_code IS NOT NULL AND
7668: p_cust_acct_site_rec.ece_tp_location_code <> FND_API.G_MISS_CHAR
7669: THEN
7664: p_cust_acct_site_rec.cust_account_id IS NOT NULL AND
7665: p_cust_acct_site_rec.cust_account_id <> FND_API.G_MISS_NUM
7666: THEN
7667: IF p_cust_acct_site_rec.ece_tp_location_code IS NOT NULL AND
7668: p_cust_acct_site_rec.ece_tp_location_code <> FND_API.G_MISS_CHAR
7669: THEN
7670: select count(1)
7671: into l_count
7672: from hz_cust_acct_sites_all addr -- Bug 3456489
7676:
7677: IF l_count > 0 THEN
7678: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_ADDR_EDI_LOC_EXISTS' );
7679: FND_MSG_PUB.ADD;
7680: x_return_status := FND_API.G_RET_STS_ERROR;
7681: END IF;
7682: END IF;
7683: END IF;
7684: ELSIF p_create_update_flag = 'U' THEN
7682: END IF;
7683: END IF;
7684: ELSIF p_create_update_flag = 'U' THEN
7685: IF p_cust_acct_site_rec.ece_tp_location_code IS NOT NULL AND
7686: p_cust_acct_site_rec.ece_tp_location_code <> FND_API.G_MISS_CHAR
7687: THEN
7688: select count(1)
7689: into l_count
7690: from hz_cust_acct_sites_all addr -- Bug 3456489
7695:
7696: IF l_count > 0 THEN
7697: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_ADDR_EDI_LOC_EXISTS' );
7698: FND_MSG_PUB.ADD;
7699: x_return_status := FND_API.G_RET_STS_ERROR;
7700: END IF;
7701: END IF;
7702: END IF;
7703:
7741: * p_cust_site_use_rec Customer account site use record.
7742: * p_rowid Rowid of the record (used only in update mode).
7743: * IN/OUT:
7744: * x_return_status Return status after the call. The status can
7745: * be FND_API.G_RET_STS_SUCCESS (success),
7746: * FND_API.G_RET_STS_ERROR (error),
7747: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7748: *
7749: * NOTES
7742: * p_rowid Rowid of the record (used only in update mode).
7743: * IN/OUT:
7744: * x_return_status Return status after the call. The status can
7745: * be FND_API.G_RET_STS_SUCCESS (success),
7746: * FND_API.G_RET_STS_ERROR (error),
7747: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7748: *
7749: * NOTES
7750: *
7743: * IN/OUT:
7744: * x_return_status Return status after the call. The status can
7745: * be FND_API.G_RET_STS_SUCCESS (success),
7746: * FND_API.G_RET_STS_ERROR (error),
7747: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7748: *
7749: * NOTES
7750: *
7751: * MODIFICATION HISTORY
7867:
7868: --Bug Fix 2399491, Checking for duplicates in the _all table.
7869:
7870: IF p_cust_site_use_rec.site_use_id IS NOT NULL AND
7871: p_cust_site_use_rec.site_use_id <> FND_API.G_MISS_NUM
7872: THEN
7873: BEGIN
7874: SELECT 'Y' INTO l_dummy
7875: FROM HZ_CUST_SITE_USES_ALL
7877:
7878: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
7879: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_site_use_id' );
7880: FND_MSG_PUB.ADD;
7881: x_return_status := FND_API.G_RET_STS_ERROR;
7882: EXCEPTION
7883: WHEN NO_DATA_FOUND THEN
7884: NULL;
7885: END;
7957:
7958: -- cust_acct_site_id is foreign key to hz_cust_acct_sites
7959: IF p_create_update_flag = 'C' AND
7960: p_cust_site_use_rec.cust_acct_site_id IS NOT NULL AND
7961: p_cust_site_use_rec.cust_acct_site_id <> FND_API.G_MISS_NUM
7962: THEN
7963: check_cust_acct_site_fk (
7964: p_column => 'cust_acct_site_id',
7965: p_column_value => p_cust_site_use_rec.cust_acct_site_id,
8014: /****Logical APIs - validation not required****/
8015: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8016: -- status is lookup code in lookup type CODE_STATUS
8017: IF p_cust_site_use_rec.status IS NOT NULL AND
8018: p_cust_site_use_rec.status <> FND_API.G_MISS_CHAR AND
8019: ( p_create_update_flag = 'C' OR
8020: ( p_create_update_flag = 'U' AND
8021: p_cust_site_use_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
8022: THEN
8017: IF p_cust_site_use_rec.status IS NOT NULL AND
8018: p_cust_site_use_rec.status <> FND_API.G_MISS_CHAR AND
8019: ( p_create_update_flag = 'C' OR
8020: ( p_create_update_flag = 'U' AND
8021: p_cust_site_use_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
8022: THEN
8023: validate_lookup (
8024: p_column => 'status',
8025: p_lookup_type => 'CODE_STATUS',
8091: -- during creation.
8092:
8093: IF p_create_update_flag = 'C' AND
8094: p_cust_site_use_rec.site_use_code IS NOT NULL AND
8095: p_cust_site_use_rec.site_use_code <> FND_API.G_MISS_CHAR
8096: THEN
8097: validate_lookup (
8098: p_column => 'site_use_code',
8099: p_lookup_type => 'SITE_USE_CODE',
8112:
8113: IF p_cust_site_use_rec.status = 'A' OR
8114: ( p_create_update_flag = 'C' AND
8115: ( p_cust_site_use_rec.status IS NULL OR
8116: p_cust_site_use_rec.status = FND_API.G_MISS_CHAR ) ) OR
8117: ( p_create_update_flag = 'U' AND
8118: p_cust_site_use_rec.status IS NULL AND
8119: l_status = 'A' )
8120: THEN
8144: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_ONE_ACTIVE_STMTS_SITE' );
8145: END IF;
8146:
8147: FND_MSG_PUB.ADD;
8148: x_return_status := FND_API.G_RET_STS_ERROR;
8149:
8150: EXCEPTION
8151: WHEN NO_DATA_FOUND THEN
8152: NULL;
8161: END IF;
8162: END IF;
8163:
8164: -- cust_acct_site_id and site_use_code together should be unique.
8165: if p_cust_site_use_rec.status <> 'I' or (p_create_update_flag = 'C' and (p_cust_site_use_rec.status is null or p_cust_site_use_rec.status = fnd_api.g_miss_char ))
8166: -- bug 8331061, BO API allows to pass in multiple site uses at same time such as 'A', 'I', null for status
8167:
8168: then
8169: -- Bug 3988537.
8171: SELECT 'Y' INTO l_dummy
8172: FROM HZ_CUST_SITE_USES_ALL -- Bug 3456489
8173: WHERE CUST_ACCT_SITE_ID = nvl(p_cust_site_use_rec.cust_acct_site_id,l_cust_acct_site_id)
8174: AND SITE_USE_CODE = nvl(p_cust_site_use_rec.site_use_code,l_site_use_code)
8175: AND SITE_USE_ID <> nvl(p_cust_site_use_rec.site_use_id,fnd_api.g_miss_num)
8176: AND STATUS = 'A'
8177: AND ORG_ID = p_cust_site_use_rec.org_id -- Bug 3456489
8178: AND ROWNUM = 1;
8179:
8179:
8180: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
8181: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id - site_use_code' );
8182: FND_MSG_PUB.ADD;
8183: x_return_status := FND_API.G_RET_STS_ERROR;
8184: EXCEPTION
8185: WHEN NO_DATA_FOUND THEN
8186: NULL;
8187: END;
8219: p_cust_site_use_rec.org_id);
8220: END IF;
8221: EXCEPTION
8222: WHEN NO_DATA_FOUND THEN
8223: x_return_status := FND_API.G_RET_STS_ERROR;
8224: END ;
8225:
8226:
8227: IF p_create_update_flag = 'C' THEN
8225:
8226:
8227: IF p_create_update_flag = 'C' THEN
8228: IF p_cust_site_use_rec.location IS NULL OR
8229: p_cust_site_use_rec.location = FND_API.G_MISS_CHAR
8230: THEN
8231: IF l_profile = 'N' THEN
8232: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
8233: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'location' );
8231: IF l_profile = 'N' THEN
8232: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
8233: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'location' );
8234: FND_MSG_PUB.ADD;
8235: x_return_status := FND_API.G_RET_STS_ERROR;
8236: END IF;
8237:
8238: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
8239: hz_utility_v2pub.debug(p_message=>'location is mandatory if autonumbering is set to N. ' ||
8263:
8264: -- location must be unique within a customer account/site_use_type
8265:
8266: IF p_cust_site_use_rec.location IS NOT NULL AND
8267: p_cust_site_use_rec.location <> FND_API.G_MISS_CHAR
8268: THEN
8269: BEGIN
8270:
8271: --Bug No : 2998504. Status check added to the where clause.
8286:
8287:
8288: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_DUP_CODE_LOCATION' );
8289: FND_MSG_PUB.ADD;
8290: x_return_status := FND_API.G_RET_STS_ERROR;
8291:
8292: EXCEPTION
8293: WHEN NO_DATA_FOUND THEN
8294: NULL;
8313: ----------------------------------------------
8314: -- validate contact_id
8315: ----------------------------------------------
8316: IF p_cust_site_use_rec.contact_id IS NOT NULL AND
8317: p_cust_site_use_rec.contact_id <> FND_API.G_MISS_NUM
8318: THEN
8319: check_cust_site_use_cont_fk (
8320: p_column => 'cust_account_role_id',
8321: p_column_value => p_cust_site_use_rec.contact_id,
8338: -- BILL_TO site use of this account and its related account.
8339: -- For other type of site uses, this column should be NULL.
8340: -- Bug No : 2266165. Added the check for bill_to_flag.
8341: IF p_cust_site_use_rec.bill_to_site_use_id IS NOT NULL AND
8342: p_cust_site_use_rec.bill_to_site_use_id <> FND_API.G_MISS_NUM
8343: THEN
8344: IF l_site_use_code = 'SHIP_TO' THEN
8345: BEGIN
8346: SELECT 'Y' INTO l_dummy
8364: EXCEPTION
8365: WHEN NO_DATA_FOUND THEN
8366: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_BILL_TO_SITE_USE_F' );
8367: FND_MSG_PUB.ADD;
8368: x_return_status := FND_API.G_RET_STS_ERROR;
8369: END;
8370:
8371: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
8372: hz_utility_v2pub.debug(p_message=>'For SHIP_TO site use, bill_to_site_use_id should be a valid BILL_TO site use of this account and its related account. ' ||
8377:
8378: ELSE
8379: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_BILL_TO_SITE_USE_S' );
8380: FND_MSG_PUB.ADD;
8381: x_return_status := FND_API.G_RET_STS_ERROR;
8382: END IF;
8383: END IF;
8384:
8385:
8403: AND rownum = 1;
8404:
8405: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_SHIP_TO_SITES_EXIST' );
8406: FND_MSG_PUB.ADD;
8407: x_return_status := FND_API.G_RET_STS_ERROR;
8408:
8409: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
8410: hz_utility_v2pub.debug(p_message=>'For BILL_TO site use, inactivation not allowed if a SHIP_TO address uses this BILL_TO address ' ||
8411: 'x_return_status = ' || x_return_status,
8432: -- validate orig_system_reference
8433: ----------------------------------------------
8434: /****Logical APIs - validation not required****/
8435: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8436: IF (p_cust_site_use_rec.orig_system is not null and p_cust_site_use_rec.orig_system <>fnd_api.g_miss_char)
8437: and (p_cust_site_use_rec.orig_system_reference is not null and p_cust_site_use_rec.orig_system_reference <>fnd_api.g_miss_char)
8438: and p_create_update_flag = 'U'
8439: THEN
8440:
8433: ----------------------------------------------
8434: /****Logical APIs - validation not required****/
8435: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8436: IF (p_cust_site_use_rec.orig_system is not null and p_cust_site_use_rec.orig_system <>fnd_api.g_miss_char)
8437: and (p_cust_site_use_rec.orig_system_reference is not null and p_cust_site_use_rec.orig_system_reference <>fnd_api.g_miss_char)
8438: and p_create_update_flag = 'U'
8439: THEN
8440:
8441: hz_orig_system_ref_pub.get_owner_table_id
8444: p_owner_table_name =>'HZ_CUST_SITE_USES_ALL',
8445: x_owner_table_id => l_mosr_owner_table_id,
8446: x_return_status => l_temp_return_status);
8447:
8448: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
8449: l_mosr_owner_table_id= nvl(p_cust_site_use_rec.site_use_id,l_mosr_owner_table_id))
8450: THEN
8451: l_validate_flag := 'N';
8452: END IF;
8454: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
8455: -- x_return_status. Set x_return_status to error, ONLY if there is error.
8456: -- In case of success, leave it to carry over previous value as before this call.
8457: -- Fix for Bug 5498116 (29-AUG-2006)
8458: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
8459: x_return_status := l_temp_return_status;
8460: END IF;
8461:
8462: end if;
8492: ----------------------------------------------
8493:
8494: -- freight_term is lookup code in lookup type FREIGHT_TERMS in so_lookups
8495: IF p_cust_site_use_rec.freight_term IS NOT NULL AND
8496: p_cust_site_use_rec.freight_term <> FND_API.G_MISS_CHAR AND
8497: ( p_create_update_flag = 'C' OR
8498: ( p_create_update_flag = 'U' AND
8499: p_cust_site_use_rec.freight_term <> NVL( l_freight_term, FND_API.G_MISS_CHAR ) ) )
8500: THEN
8495: IF p_cust_site_use_rec.freight_term IS NOT NULL AND
8496: p_cust_site_use_rec.freight_term <> FND_API.G_MISS_CHAR AND
8497: ( p_create_update_flag = 'C' OR
8498: ( p_create_update_flag = 'U' AND
8499: p_cust_site_use_rec.freight_term <> NVL( l_freight_term, FND_API.G_MISS_CHAR ) ) )
8500: THEN
8501: validate_lookup (
8502: p_column => 'freight_term',
8503: p_lookup_table => 'SO_LOOKUPS',
8545: /****Logical APIs - validation not required****/
8546: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8547: -- primary_flag is lookup code in lookup type YES/NO
8548: IF p_cust_site_use_rec.primary_flag IS NOT NULL AND
8549: p_cust_site_use_rec.primary_flag <> FND_API.G_MISS_CHAR
8550: THEN
8551: validate_lookup (
8552: p_column => 'primary_flag',
8553: p_lookup_type => 'YES/NO',
8588:
8589: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_ONE_PRIMARY_SU' );
8590: FND_MESSAGE.SET_TOKEN( 'SITE_CODE', l_site_use_code );
8591: FND_MSG_PUB.ADD;
8592: x_return_status := FND_API.G_RET_STS_ERROR;
8593:
8594: EXCEPTION
8595: WHEN NO_DATA_FOUND THEN
8596: NULL;
8609: -- check to ensure that an inactive site use is never marked
8610: -- as primary.
8611: IF p_create_update_flag = 'C' THEN
8612: IF p_cust_site_use_rec.primary_flag IS NULL OR
8613: p_cust_site_use_rec.primary_flag = fnd_api.g_miss_char
8614: THEN
8615: l_primary_flag := 'N';
8616: ELSE
8617: l_primary_flag := p_cust_site_use_rec.primary_flag;
8617: l_primary_flag := p_cust_site_use_rec.primary_flag;
8618: END IF;
8619:
8620: IF p_cust_site_use_rec.status IS NULL OR
8621: p_cust_site_use_rec.status = fnd_api.g_miss_char
8622: THEN
8623: l_status := 'A';
8624: ELSE
8625: l_status := p_cust_site_use_rec.status;
8644: fnd_message.set_token('ENTITY', 'Site Use');
8645: -- fnd_message.set_token('COLUMN1', 'primary_flag');
8646: -- fnd_message.set_token('COLUMN2', 'status');
8647: fnd_msg_pub.add;
8648: x_return_status := FND_API.G_RET_STS_ERROR;
8649: -- reset l_error for later use.
8650: l_error := FALSE;
8651: END IF;
8652:
8673: ----------------------------------------------
8674:
8675: -- ship_partial is lookup code in lookup type YES/NO
8676: IF p_cust_site_use_rec.ship_partial IS NOT NULL AND
8677: p_cust_site_use_rec.ship_partial <> FND_API.G_MISS_CHAR
8678: THEN
8679: validate_lookup (
8680: p_column => 'ship_partial',
8681: p_lookup_type => 'YES/NO',
8705: /****Logical APIs - validation not required****/
8706: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8707: -- ship_sets_include_lines_flag is lookup code in lookup type YES/NO
8708: IF p_cust_site_use_rec.ship_sets_include_lines_flag IS NOT NULL AND
8709: p_cust_site_use_rec.ship_sets_include_lines_flag <> FND_API.G_MISS_CHAR
8710: THEN
8711: validate_lookup (
8712: p_column => 'ship_sets_include_lines_flag',
8713: p_lookup_type => 'YES/NO',
8727: --If ship_sets_include_lines_flag is set to Y then arrivalsets_include_lines_flag
8728: --is always set to N.
8729: IF (p_create_update_flag = 'C' or
8730: (p_create_update_flag ='U' and
8731: p_cust_site_use_rec.ship_sets_include_lines_flag <> NVL(l_ship_sets_inc_lines_f,FND_API.G_MISS_CHAR)))
8732: THEN
8733: IF p_cust_site_use_rec.ship_sets_include_lines_flag = 'Y'
8734: THEN
8735: BEGIN
8746: FND_MESSAGE.SET_TOKEN('VALUE1','Y');
8747: FND_MESSAGE.SET_TOKEN('COLUMN2','arrivalsets_include_lines_flag');
8748: FND_MESSAGE.SET_TOKEN('VALUE2','N');
8749: FND_MSG_PUB.ADD;
8750: x_return_status := FND_API.G_RET_STS_ERROR;
8751: END IF;
8752: END ;
8753: END IF;
8754: END IF;
8773: /****Logical APIs - validation not required****/
8774: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8775: -- arrivalsets_include_lines_flag is lookup code in lookup type YES/NO
8776: IF p_cust_site_use_rec.arrivalsets_include_lines_flag IS NOT NULL AND
8777: p_cust_site_use_rec.arrivalsets_include_lines_flag <> FND_API.G_MISS_CHAR
8778: THEN
8779: validate_lookup (
8780: p_column => 'arrivalsets_include_lines_flag',
8781: p_lookup_type => 'YES/NO',
8796: --is always set to N.
8797:
8798: IF (p_create_update_flag ='C' or
8799: (p_create_update_flag ='U' and
8800: p_cust_site_use_rec.arrivalsets_include_lines_flag <> NVL(l_arrivalsets_inc_lines_f,FND_API.G_MISS_CHAR)))
8801: THEN
8802: IF p_cust_site_use_rec.arrivalsets_include_lines_flag = 'Y'
8803: THEN
8804: BEGIN
8815: FND_MESSAGE.SET_TOKEN('VALUE1','Y');
8816: FND_MESSAGE.SET_TOKEN('COLUMN2','ship_sets_include_lines_flag');
8817: FND_MESSAGE.SET_TOKEN('VALUE2','N');
8818: FND_MSG_PUB.ADD;
8819: x_return_status := FND_API.G_RET_STS_ERROR;
8820: END IF;
8821: END ;
8822: END IF;
8823: END IF;
8841: ------------------------------------------
8842: --Tax_code should be a valid value defined in zx_output_classifications_v
8843:
8844: IF p_cust_site_use_rec.tax_code is NOT NULL AND
8845: p_cust_site_use_rec.tax_code <> FND_API.G_MISS_CHAR
8846: AND ( p_create_update_flag = 'C' OR
8847: ( p_create_update_flag = 'U' AND
8848: p_cust_site_use_rec.tax_code <> NVL( l_tax_code, FND_API.G_MISS_CHAR ) ) )
8849: THEN
8844: IF p_cust_site_use_rec.tax_code is NOT NULL AND
8845: p_cust_site_use_rec.tax_code <> FND_API.G_MISS_CHAR
8846: AND ( p_create_update_flag = 'C' OR
8847: ( p_create_update_flag = 'U' AND
8848: p_cust_site_use_rec.tax_code <> NVL( l_tax_code, FND_API.G_MISS_CHAR ) ) )
8849: THEN
8850: check_tax_code(
8851: p_column => 'tax_code',
8852: p_column_value => p_cust_site_use_rec.tax_code,
8875: ------------------------------------
8876: --Payment_term_id should be a valid value defined in RA_TERMS
8877:
8878: IF p_cust_site_use_rec.payment_term_id is NOT NULL AND
8879: p_cust_site_use_rec.payment_term_id <> FND_API.G_MISS_NUM
8880: AND ( p_create_update_flag = 'C' OR
8881: ( p_create_update_flag = 'U' AND
8882: p_cust_site_use_rec.payment_term_id <> NVL( l_payment_term_id, FND_API.G_MISS_NUM ) ) )
8883: THEN
8878: IF p_cust_site_use_rec.payment_term_id is NOT NULL AND
8879: p_cust_site_use_rec.payment_term_id <> FND_API.G_MISS_NUM
8880: AND ( p_create_update_flag = 'C' OR
8881: ( p_create_update_flag = 'U' AND
8882: p_cust_site_use_rec.payment_term_id <> NVL( l_payment_term_id, FND_API.G_MISS_NUM ) ) )
8883: THEN
8884: check_payterm_id_fk(
8885: p_column => 'payment_term_id',
8886: p_column_value => p_cust_site_use_rec.payment_term_id,
8906: ---------------------------------------------
8907: --Demand_class_code is lookup_code in lookup_type DEMAND_CLASS in fnd_common_lookups
8908:
8909: IF p_cust_site_use_rec.demand_class_code is NOT NULL AND
8910: p_cust_site_use_rec.demand_class_code <> FND_API.G_MISS_CHAR
8911: AND ( p_create_update_flag = 'C' OR
8912: ( p_create_update_flag = 'U' AND
8913: p_cust_site_use_rec.demand_class_code <> NVL(l_demand_class_code,FND_API.G_MISS_CHAR) ) )
8914: THEN
8909: IF p_cust_site_use_rec.demand_class_code is NOT NULL AND
8910: p_cust_site_use_rec.demand_class_code <> FND_API.G_MISS_CHAR
8911: AND ( p_create_update_flag = 'C' OR
8912: ( p_create_update_flag = 'U' AND
8913: p_cust_site_use_rec.demand_class_code <> NVL(l_demand_class_code,FND_API.G_MISS_CHAR) ) )
8914: THEN
8915: validate_lookup(
8916: p_column =>'demand_class_code',
8917: p_lookup_table =>'FND_COMMON_LOOKUPS',
8938: -----------------------------------------------
8939: --Primary_salesrep_id should be a valid value defined in RA_SALESREPS
8940:
8941: IF p_cust_site_use_rec.primary_salesrep_id is NOT NULL AND
8942: p_cust_site_use_rec.primary_salesrep_id <> FND_API.G_MISS_NUM
8943: AND ( p_create_update_flag = 'C' OR
8944: ( p_create_update_flag = 'U' AND
8945: p_cust_site_use_rec.primary_salesrep_id <> NVL(l_primary_salesrep_id,FND_API.G_MISS_NUM) ) )
8946: THEN
8941: IF p_cust_site_use_rec.primary_salesrep_id is NOT NULL AND
8942: p_cust_site_use_rec.primary_salesrep_id <> FND_API.G_MISS_NUM
8943: AND ( p_create_update_flag = 'C' OR
8944: ( p_create_update_flag = 'U' AND
8945: p_cust_site_use_rec.primary_salesrep_id <> NVL(l_primary_salesrep_id,FND_API.G_MISS_NUM) ) )
8946: THEN
8947: check_prim_salesrep(
8948: p_column => 'primary_salesrep_id',
8949: p_column_value => p_cust_site_use_rec.primary_salesrep_id,
8968: ----------------------------------------------------
8969: --validate finchrg_receivables_trx_id
8970: ----------------------------------------------------
8971: IF p_cust_site_use_rec.finchrg_receivables_trx_id is NOT NULL AND
8972: p_cust_site_use_rec.finchrg_receivables_trx_id <> FND_API.G_MISS_NUM
8973: AND ( p_create_update_flag = 'C' OR
8974: ( p_create_update_flag = 'U' AND
8975: p_cust_site_use_rec.finchrg_receivables_trx_id <> NVL(l_finchrg_receivables_trx_id,FND_API.G_MISS_NUM) ) )
8976: THEN
8971: IF p_cust_site_use_rec.finchrg_receivables_trx_id is NOT NULL AND
8972: p_cust_site_use_rec.finchrg_receivables_trx_id <> FND_API.G_MISS_NUM
8973: AND ( p_create_update_flag = 'C' OR
8974: ( p_create_update_flag = 'U' AND
8975: p_cust_site_use_rec.finchrg_receivables_trx_id <> NVL(l_finchrg_receivables_trx_id,FND_API.G_MISS_NUM) ) )
8976: THEN
8977: check_finchrg_trx_fk(
8978: p_column => 'finchrg_receivables_trx_id',
8979: p_column_value => p_cust_site_use_rec.finchrg_receivables_trx_id,
9004: -------------------------------------------
9005: --order_type_id should be a valid value defined in OE_ORDER_TYPES_V
9006:
9007: IF p_cust_site_use_rec.order_type_id is NOT NULL AND
9008: p_cust_site_use_rec.order_type_id <> FND_API.G_MISS_NUM
9009: AND ( p_create_update_flag = 'C' OR
9010: ( p_create_update_flag = 'U' AND
9011: p_cust_site_use_rec.order_type_id <> NVL(l_order_type_id,FND_API.G_MISS_NUM) ) )
9012: THEN
9007: IF p_cust_site_use_rec.order_type_id is NOT NULL AND
9008: p_cust_site_use_rec.order_type_id <> FND_API.G_MISS_NUM
9009: AND ( p_create_update_flag = 'C' OR
9010: ( p_create_update_flag = 'U' AND
9011: p_cust_site_use_rec.order_type_id <> NVL(l_order_type_id,FND_API.G_MISS_NUM) ) )
9012: THEN
9013: check_ord_type(
9014: p_column => 'order_type_id',
9015: p_column_value => p_cust_site_use_rec.order_type_id,
9038: ----------------------------------------------------------
9039: --price_list_id should be a valid value defined in SO_PRICE_LISTS
9040:
9041: IF p_cust_site_use_rec.price_list_id is NOT NULL AND
9042: p_cust_site_use_rec.price_list_id <> FND_API.G_MISS_NUM
9043: AND ( p_create_update_flag = 'C' OR
9044: ( p_create_update_flag = 'U' AND
9045: p_cust_site_use_rec.price_list_id <> NVL(l_price_list_id,FND_API.G_MISS_NUM) ) )
9046: THEN
9041: IF p_cust_site_use_rec.price_list_id is NOT NULL AND
9042: p_cust_site_use_rec.price_list_id <> FND_API.G_MISS_NUM
9043: AND ( p_create_update_flag = 'C' OR
9044: ( p_create_update_flag = 'U' AND
9045: p_cust_site_use_rec.price_list_id <> NVL(l_price_list_id,FND_API.G_MISS_NUM) ) )
9046: THEN
9047: check_price_list_fk(
9048: p_column => 'price_list_id',
9049: p_column_value => p_cust_site_use_rec.price_list_id,
9071: ----------------------------------------------
9072: --item_cross_ref_pref should be a value defined in MTL_CROSS_REFERENCE_TYPES or should have value 'INT' or 'CUST'
9073:
9074: IF p_cust_site_use_rec.item_cross_ref_pref IS NOT NULL AND
9075: p_cust_site_use_rec.item_cross_ref_pref <> FND_API.G_MISS_CHAR
9076: AND ( p_create_update_flag = 'C' OR
9077: ( p_create_update_flag = 'U' AND
9078: p_cust_site_use_rec.item_cross_ref_pref <> NVL(l_item_cross_ref_pref,FND_API.G_MISS_CHAR) ) )
9079: THEN
9074: IF p_cust_site_use_rec.item_cross_ref_pref IS NOT NULL AND
9075: p_cust_site_use_rec.item_cross_ref_pref <> FND_API.G_MISS_CHAR
9076: AND ( p_create_update_flag = 'C' OR
9077: ( p_create_update_flag = 'U' AND
9078: p_cust_site_use_rec.item_cross_ref_pref <> NVL(l_item_cross_ref_pref,FND_API.G_MISS_CHAR) ) )
9079: THEN
9080: IF p_cust_site_use_rec.item_cross_ref_pref NOT IN('INT','CUST')
9081: THEN
9082: check_item_cross_ref(
9106: --------------------------------------------------
9107: --warehouse_id should be a value defined in ORG_ORGANIZATION_DEFINITIONS
9108:
9109: IF p_cust_site_use_rec.warehouse_id is NOT NULL AND
9110: p_cust_site_use_rec.warehouse_id <> FND_API.G_MISS_NUM
9111: AND ( p_create_update_flag = 'C' OR
9112: ( p_create_update_flag = 'U' AND
9113: p_cust_site_use_rec.warehouse_id <> NVL(l_warehouse_id,FND_API.G_MISS_NUM) ) )
9114: THEN
9109: IF p_cust_site_use_rec.warehouse_id is NOT NULL AND
9110: p_cust_site_use_rec.warehouse_id <> FND_API.G_MISS_NUM
9111: AND ( p_create_update_flag = 'C' OR
9112: ( p_create_update_flag = 'U' AND
9113: p_cust_site_use_rec.warehouse_id <> NVL(l_warehouse_id,FND_API.G_MISS_NUM) ) )
9114: THEN
9115: check_warehouse(
9116: p_column => 'warehouse_id',
9117: p_column_value => p_cust_site_use_rec.warehouse_id,
9140: ----------------------------------------------
9141: --fob_point is lookup_code in lookup type FOB
9142:
9143: IF p_cust_site_use_rec.fob_point is NOT NULL AND
9144: p_cust_site_use_rec.fob_point <> FND_API.G_MISS_CHAR
9145: AND ( p_create_update_flag = 'C' OR
9146: ( p_create_update_flag = 'U' AND
9147: p_cust_site_use_rec.fob_point <> NVL(l_fob_point,FND_API.G_MISS_CHAR) ) )
9148: THEN
9143: IF p_cust_site_use_rec.fob_point is NOT NULL AND
9144: p_cust_site_use_rec.fob_point <> FND_API.G_MISS_CHAR
9145: AND ( p_create_update_flag = 'C' OR
9146: ( p_create_update_flag = 'U' AND
9147: p_cust_site_use_rec.fob_point <> NVL(l_fob_point,FND_API.G_MISS_CHAR) ) )
9148: THEN
9149: validate_lookup(
9150: p_column =>'fob_point',
9151: p_lookup_type =>'FOB',
9175: ----------------------------------------------------------
9176: --date_type_preference is a lookup_code in lookup_type REQUEST_DATE_TYPE in oe_lookups
9177:
9178: IF p_cust_site_use_rec.date_type_preference IS NOT NULL AND
9179: p_cust_site_use_rec.date_type_preference <> FND_API.G_MISS_CHAR
9180: AND ( p_create_update_flag = 'C' OR
9181: ( p_create_update_flag = 'U' AND
9182: p_cust_site_use_rec.date_type_preference <> NVL(l_date_type_preference,FND_API.G_MISS_CHAR) ) )
9183: THEN
9178: IF p_cust_site_use_rec.date_type_preference IS NOT NULL AND
9179: p_cust_site_use_rec.date_type_preference <> FND_API.G_MISS_CHAR
9180: AND ( p_create_update_flag = 'C' OR
9181: ( p_create_update_flag = 'U' AND
9182: p_cust_site_use_rec.date_type_preference <> NVL(l_date_type_preference,FND_API.G_MISS_CHAR) ) )
9183: THEN
9184: validate_lookup(
9185: p_column =>'date_type_preference',
9186: p_lookup_table =>'OE_LOOKUPS',
9211: ----------------------------------------------
9212:
9213: -- ship_partial is lookup code in lookup type YES/NO
9214: IF p_cust_site_use_rec.ship_partial IS NOT NULL AND
9215: p_cust_site_use_rec.ship_partial <> FND_API.G_MISS_CHAR
9216: AND ( p_create_update_flag = 'C' OR
9217: ( p_create_update_flag = 'U' AND
9218: p_cust_site_use_rec.ship_partial <> NVL(l_ship_partial,FND_API.G_MISS_CHAR) ) )
9219: THEN
9214: IF p_cust_site_use_rec.ship_partial IS NOT NULL AND
9215: p_cust_site_use_rec.ship_partial <> FND_API.G_MISS_CHAR
9216: AND ( p_create_update_flag = 'C' OR
9217: ( p_create_update_flag = 'U' AND
9218: p_cust_site_use_rec.ship_partial <> NVL(l_ship_partial,FND_API.G_MISS_CHAR) ) )
9219: THEN
9220: validate_lookup (
9221: p_column => 'ship_partial',
9222: p_lookup_type => 'YES/NO',
9246: /****Logical APIs - validation not required****/
9247: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
9248: -- sched_date_push_flag is lookup code in lookup type YES/NO
9249: IF p_cust_site_use_rec.sched_date_push_flag IS NOT NULL AND
9250: p_cust_site_use_rec.sched_date_push_flag <> FND_API.G_MISS_CHAR
9251: THEN
9252: validate_lookup (
9253: p_column => 'sched_date_push_flag',
9254: p_lookup_type => 'YES/NO',
9278: /****Logical APIs - validation not required****/
9279: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
9280: -- gsa_indicator is lookup code in lookup type YES/NO
9281: IF p_cust_site_use_rec.gsa_indicator IS NOT NULL AND
9282: p_cust_site_use_rec.gsa_indicator <> FND_API.G_MISS_CHAR
9283: THEN
9284: validate_lookup (
9285: p_column => 'gsa_indicator',
9286: p_lookup_type => 'YES/NO',
9314: IF p_cust_site_use_rec.site_use_code <> 'BILL_TO' OR
9315: ( p_cust_site_use_rec.site_use_code IS NULL AND l_site_use_code <> 'BILL_TO' )
9316: THEN
9317: IF ( p_cust_site_use_rec.gl_id_rev IS NOT NULL AND
9318: p_cust_site_use_rec.gl_id_rev <> FND_API.G_MISS_NUM ) OR
9319: ( p_cust_site_use_rec.gl_id_tax IS NOT NULL AND
9320: p_cust_site_use_rec.gl_id_tax <> FND_API.G_MISS_NUM ) OR
9321: ( p_cust_site_use_rec.gl_id_freight IS NOT NULL AND
9322: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM ) OR
9316: THEN
9317: IF ( p_cust_site_use_rec.gl_id_rev IS NOT NULL AND
9318: p_cust_site_use_rec.gl_id_rev <> FND_API.G_MISS_NUM ) OR
9319: ( p_cust_site_use_rec.gl_id_tax IS NOT NULL AND
9320: p_cust_site_use_rec.gl_id_tax <> FND_API.G_MISS_NUM ) OR
9321: ( p_cust_site_use_rec.gl_id_freight IS NOT NULL AND
9322: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM ) OR
9323: ( p_cust_site_use_rec.gl_id_clearing IS NOT NULL AND
9324: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM ) OR
9318: p_cust_site_use_rec.gl_id_rev <> FND_API.G_MISS_NUM ) OR
9319: ( p_cust_site_use_rec.gl_id_tax IS NOT NULL AND
9320: p_cust_site_use_rec.gl_id_tax <> FND_API.G_MISS_NUM ) OR
9321: ( p_cust_site_use_rec.gl_id_freight IS NOT NULL AND
9322: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM ) OR
9323: ( p_cust_site_use_rec.gl_id_clearing IS NOT NULL AND
9324: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM ) OR
9325: ( p_cust_site_use_rec.gl_id_unbilled IS NOT NULL AND
9326: p_cust_site_use_rec.gl_id_unbilled <> FND_API.G_MISS_NUM ) OR
9320: p_cust_site_use_rec.gl_id_tax <> FND_API.G_MISS_NUM ) OR
9321: ( p_cust_site_use_rec.gl_id_freight IS NOT NULL AND
9322: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM ) OR
9323: ( p_cust_site_use_rec.gl_id_clearing IS NOT NULL AND
9324: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM ) OR
9325: ( p_cust_site_use_rec.gl_id_unbilled IS NOT NULL AND
9326: p_cust_site_use_rec.gl_id_unbilled <> FND_API.G_MISS_NUM ) OR
9327: ( p_cust_site_use_rec.gl_id_unearned IS NOT NULL AND
9328: p_cust_site_use_rec.gl_id_unearned <> FND_API.G_MISS_NUM )
9322: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM ) OR
9323: ( p_cust_site_use_rec.gl_id_clearing IS NOT NULL AND
9324: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM ) OR
9325: ( p_cust_site_use_rec.gl_id_unbilled IS NOT NULL AND
9326: p_cust_site_use_rec.gl_id_unbilled <> FND_API.G_MISS_NUM ) OR
9327: ( p_cust_site_use_rec.gl_id_unearned IS NOT NULL AND
9328: p_cust_site_use_rec.gl_id_unearned <> FND_API.G_MISS_NUM )
9329: THEN
9330: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9324: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM ) OR
9325: ( p_cust_site_use_rec.gl_id_unbilled IS NOT NULL AND
9326: p_cust_site_use_rec.gl_id_unbilled <> FND_API.G_MISS_NUM ) OR
9327: ( p_cust_site_use_rec.gl_id_unearned IS NOT NULL AND
9328: p_cust_site_use_rec.gl_id_unearned <> FND_API.G_MISS_NUM )
9329: THEN
9330: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9331: FND_MSG_PUB.ADD;
9332: x_return_status := FND_API.G_RET_STS_ERROR;
9328: p_cust_site_use_rec.gl_id_unearned <> FND_API.G_MISS_NUM )
9329: THEN
9330: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9331: FND_MSG_PUB.ADD;
9332: x_return_status := FND_API.G_RET_STS_ERROR;
9333: END IF;
9334:
9335: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
9336: hz_utility_v2pub.debug(p_message=>'If site_use_code is not BILL_TO, then Revenue, Tax, Freight, Clearing, ' ||
9348: IF p_cust_site_use_rec.site_use_code <> 'DRAWEE' OR
9349: ( p_cust_site_use_rec.site_use_code IS NULL AND l_site_use_code <> 'DRAWEE' )
9350: THEN
9351: IF ( p_cust_site_use_rec.gl_id_unpaid_rec IS NOT NULL AND
9352: p_cust_site_use_rec.gl_id_unpaid_rec <> FND_API.G_MISS_NUM ) OR
9353: ( p_cust_site_use_rec.gl_id_remittance IS NOT NULL AND
9354: p_cust_site_use_rec.gl_id_remittance <> FND_API.G_MISS_NUM ) OR
9355: ( p_cust_site_use_rec.gl_id_factor IS NOT NULL AND
9356: p_cust_site_use_rec.gl_id_factor <> FND_API.G_MISS_NUM )
9350: THEN
9351: IF ( p_cust_site_use_rec.gl_id_unpaid_rec IS NOT NULL AND
9352: p_cust_site_use_rec.gl_id_unpaid_rec <> FND_API.G_MISS_NUM ) OR
9353: ( p_cust_site_use_rec.gl_id_remittance IS NOT NULL AND
9354: p_cust_site_use_rec.gl_id_remittance <> FND_API.G_MISS_NUM ) OR
9355: ( p_cust_site_use_rec.gl_id_factor IS NOT NULL AND
9356: p_cust_site_use_rec.gl_id_factor <> FND_API.G_MISS_NUM )
9357: THEN
9358: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9352: p_cust_site_use_rec.gl_id_unpaid_rec <> FND_API.G_MISS_NUM ) OR
9353: ( p_cust_site_use_rec.gl_id_remittance IS NOT NULL AND
9354: p_cust_site_use_rec.gl_id_remittance <> FND_API.G_MISS_NUM ) OR
9355: ( p_cust_site_use_rec.gl_id_factor IS NOT NULL AND
9356: p_cust_site_use_rec.gl_id_factor <> FND_API.G_MISS_NUM )
9357: THEN
9358: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9359: FND_MSG_PUB.ADD;
9360: x_return_status := FND_API.G_RET_STS_ERROR;
9356: p_cust_site_use_rec.gl_id_factor <> FND_API.G_MISS_NUM )
9357: THEN
9358: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9359: FND_MSG_PUB.ADD;
9360: x_return_status := FND_API.G_RET_STS_ERROR;
9361: END IF;
9362:
9363: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
9364: hz_utility_v2pub.debug(p_message=>'If site_use_code is not DRAWEE, then Unpaid Bills Receivables, ' ||
9375: IF p_cust_site_use_rec.site_use_code NOT IN ('BILL_TO', 'DRAWEE') OR
9376: ( p_cust_site_use_rec.site_use_code IS NULL AND l_site_use_code NOT IN ('BILL_TO', 'DRAWEE') )
9377: THEN
9378: IF ( p_cust_site_use_rec.gl_id_rec IS NOT NULL AND
9379: p_cust_site_use_rec.gl_id_rec <> FND_API.G_MISS_NUM )
9380: THEN
9381: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9382: FND_MSG_PUB.ADD;
9383: x_return_status := FND_API.G_RET_STS_ERROR;
9379: p_cust_site_use_rec.gl_id_rec <> FND_API.G_MISS_NUM )
9380: THEN
9381: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9382: FND_MSG_PUB.ADD;
9383: x_return_status := FND_API.G_RET_STS_ERROR;
9384: END IF;
9385:
9386: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
9387: hz_utility_v2pub.debug(p_message=>'If site_use_code is not BILL_TO OR DRAWEE, then Bills Receivables field should not be populated. ' ||
9398: l_coa_id := HZ_MO_GLOBAL_CACHE.get_chart_of_accounts_id(
9399: p_cust_site_use_rec.org_id);
9400: EXCEPTION
9401: WHEN NO_DATA_FOUND THEN
9402: x_return_status := FND_API.G_RET_STS_ERROR;
9403: END;
9404:
9405: /* 3456489. Validate accounts only if Chart of Accounts ID is not null. */
9406: IF (l_coa_id IS NOT NULL) THEN
9411:
9412:
9413: -- gl_id_rec is validate gl field.
9414: IF p_cust_site_use_rec.gl_id_rec IS NOT NULL AND
9415: p_cust_site_use_rec.gl_id_rec <> FND_API.G_MISS_NUM
9416: THEN
9417: validate_gl_id (
9418: p_gl_name => 'REC',
9419: p_column_value => p_cust_site_use_rec.gl_id_rec,
9434: ----------------------------------------------
9435:
9436: -- gl_id_rev is validate gl field.
9437: IF p_cust_site_use_rec.gl_id_rev IS NOT NULL AND
9438: p_cust_site_use_rec.gl_id_rev <> FND_API.G_MISS_NUM
9439: THEN
9440: validate_gl_id (
9441: p_gl_name => 'REV',
9442: p_column_value => p_cust_site_use_rec.gl_id_rev,
9457: ----------------------------------------------
9458:
9459: -- gl_id_tax is validate gl field.
9460: IF p_cust_site_use_rec.gl_id_tax IS NOT NULL AND
9461: p_cust_site_use_rec.gl_id_tax <> FND_API.G_MISS_NUM
9462: THEN
9463: validate_gl_id (
9464: p_gl_name => 'TAX',
9465: p_column_value => p_cust_site_use_rec.gl_id_tax,
9480: ----------------------------------------------
9481:
9482: -- gl_id_freight is validate gl field.
9483: IF p_cust_site_use_rec.gl_id_freight IS NOT NULL AND
9484: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM
9485: THEN
9486: validate_gl_id (
9487: p_gl_name => 'FREIGHT',
9488: p_column_value => p_cust_site_use_rec.gl_id_freight,
9503: ----------------------------------------------
9504:
9505: -- gl_id_clearing is validate gl field.
9506: IF p_cust_site_use_rec.gl_id_clearing IS NOT NULL AND
9507: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM
9508: THEN
9509: validate_gl_id (
9510: p_gl_name => 'CLEARING',
9511: p_column_value => p_cust_site_use_rec.gl_id_clearing,
9526: ----------------------------------------------
9527:
9528: -- gl_id_unbilled is validate gl field.
9529: IF p_cust_site_use_rec.gl_id_unbilled IS NOT NULL AND
9530: p_cust_site_use_rec.gl_id_unbilled <> FND_API.G_MISS_NUM
9531: THEN
9532: validate_gl_id (
9533: p_gl_name => 'UNBILLED',
9534: p_column_value => p_cust_site_use_rec.gl_id_unbilled,
9549: ----------------------------------------------
9550:
9551: -- gl_id_unearned is validate gl field.
9552: IF p_cust_site_use_rec.gl_id_unearned IS NOT NULL AND
9553: p_cust_site_use_rec.gl_id_unearned <> FND_API.G_MISS_NUM
9554: THEN
9555: validate_gl_id (
9556: p_gl_name => 'UNEARNED',
9557: p_column_value => p_cust_site_use_rec.gl_id_unearned,
9572: ----------------------------------------------
9573:
9574: -- gl_id_unpaid_rec is validate gl field.
9575: IF p_cust_site_use_rec.gl_id_unpaid_rec IS NOT NULL AND
9576: p_cust_site_use_rec.gl_id_unpaid_rec <> FND_API.G_MISS_NUM
9577: THEN
9578: validate_gl_id (
9579: p_gl_name => 'UNPAID_REC',
9580: p_column_value => p_cust_site_use_rec.gl_id_unpaid_rec,
9595: ----------------------------------------------
9596:
9597: -- gl_id_remittance is validate gl field.
9598: IF p_cust_site_use_rec.gl_id_remittance IS NOT NULL AND
9599: p_cust_site_use_rec.gl_id_remittance <> FND_API.G_MISS_NUM
9600: THEN
9601: validate_gl_id (
9602: p_gl_name => 'REMITTANCE',
9603: p_column_value => p_cust_site_use_rec.gl_id_remittance,
9618: ----------------------------------------------
9619:
9620: -- gl_id_factor is validate gl field.
9621: IF p_cust_site_use_rec.gl_id_factor IS NOT NULL AND
9622: p_cust_site_use_rec.gl_id_factor <> FND_API.G_MISS_NUM
9623: THEN
9624: validate_gl_id (
9625: p_gl_name => 'FACTOR',
9626: p_column_value => p_cust_site_use_rec.gl_id_factor,
9650:
9651: -- ship_via is foreign key to oe_ship_methods_v
9652: -- can be used in both single and multi org case.
9653: IF p_cust_site_use_rec.ship_via IS NOT NULL AND
9654: p_cust_site_use_rec.ship_via <> FND_API.G_MISS_CHAR AND
9655: ( p_create_update_flag = 'C' OR
9656: ( p_create_update_flag = 'U' AND
9657: p_cust_site_use_rec.ship_via <> NVL( l_ship_via, FND_API.G_MISS_CHAR ) ) )
9658: THEN
9653: IF p_cust_site_use_rec.ship_via IS NOT NULL AND
9654: p_cust_site_use_rec.ship_via <> FND_API.G_MISS_CHAR AND
9655: ( p_create_update_flag = 'C' OR
9656: ( p_create_update_flag = 'U' AND
9657: p_cust_site_use_rec.ship_via <> NVL( l_ship_via, FND_API.G_MISS_CHAR ) ) )
9658: THEN
9659: check_oe_ship_methods_v_fk (
9660: p_entity => 'SITE_USE',
9661: p_column => 'ship_via',
9729: * p_cust_account_role_rec Customer account role record.
9730: * p_rowid Rowid of the record (used only in update mode).
9731: * IN/OUT:
9732: * x_return_status Return status after the call. The status can
9733: * be FND_API.G_RET_STS_SUCCESS (success),
9734: * FND_API.G_RET_STS_ERROR (error),
9735: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9736: *
9737: * NOTES
9730: * p_rowid Rowid of the record (used only in update mode).
9731: * IN/OUT:
9732: * x_return_status Return status after the call. The status can
9733: * be FND_API.G_RET_STS_SUCCESS (success),
9734: * FND_API.G_RET_STS_ERROR (error),
9735: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9736: *
9737: * NOTES
9738: *
9731: * IN/OUT:
9732: * x_return_status Return status after the call. The status can
9733: * be FND_API.G_RET_STS_SUCCESS (success),
9734: * FND_API.G_RET_STS_ERROR (error),
9735: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9736: *
9737: * NOTES
9738: *
9739: * MODIFICATION HISTORY
9805: -- If primary key value is not passed, it will be generated
9806: -- from sequence by table handler.
9807:
9808: IF p_cust_account_role_rec.cust_account_role_id IS NOT NULL AND
9809: p_cust_account_role_rec.cust_account_role_id <> FND_API.G_MISS_NUM
9810: THEN
9811: BEGIN
9812: SELECT 'Y' INTO l_dummy
9813: FROM HZ_CUST_ACCOUNT_ROLES
9815:
9816: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
9817: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_role_id' );
9818: FND_MSG_PUB.ADD;
9819: x_return_status := FND_API.G_RET_STS_ERROR;
9820: EXCEPTION
9821: WHEN NO_DATA_FOUND THEN
9822: NULL;
9823: END;
9896: -- check FK during creation.
9897:
9898: IF p_create_update_flag = 'C' AND
9899: p_cust_account_role_rec.party_id IS NOT NULL AND
9900: p_cust_account_role_rec.party_id <> FND_API.G_MISS_NUM
9901: THEN
9902: check_party_fk (
9903: p_column => 'party_id',
9904: p_column_value => p_cust_account_role_rec.party_id,
9970: -- we only need to check FK during creation.
9971:
9972: IF p_create_update_flag = 'C' AND
9973: p_cust_account_role_rec.cust_account_id IS NOT NULL AND
9974: p_cust_account_role_rec.cust_account_id <> FND_API.G_MISS_NUM
9975: THEN
9976: check_cust_account_fk (
9977: p_column => 'cust_account_id',
9978: p_column_value => p_cust_account_role_rec.cust_account_id,
10026: -- check FK during creation.
10027:
10028: IF p_create_update_flag = 'C' AND
10029: p_cust_account_role_rec.cust_acct_site_id IS NOT NULL AND
10030: p_cust_account_role_rec.cust_acct_site_id <> FND_API.G_MISS_NUM
10031: THEN
10032: BEGIN
10033:
10034: SELECT CUST_ACCOUNT_ID,ORG_ID
10041: MO_GLOBAL.validate_orgid_pub_api(l_org_id,'N',l_return_status);
10042: EXCEPTION
10043: WHEN OTHERS
10044: THEN
10045: x_return_status := FND_API.G_RET_STS_ERROR;
10046: END;
10047:
10048: IF l_cust_account_id <> p_cust_account_role_rec.cust_account_id THEN
10049: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_ACCT_SITE_MISSMATCH' );
10047:
10048: IF l_cust_account_id <> p_cust_account_role_rec.cust_account_id THEN
10049: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_ACCT_SITE_MISSMATCH' );
10050: FND_MSG_PUB.ADD;
10051: x_return_status := FND_API.G_RET_STS_ERROR;
10052: END IF;
10053:
10054: EXCEPTION
10055: WHEN NO_DATA_FOUND THEN
10057: FND_MESSAGE.SET_TOKEN( 'FK', 'cust_acct_site_id' );
10058: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id' );
10059: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_acct_sites' );
10060: FND_MSG_PUB.ADD;
10061: x_return_status := FND_API.G_RET_STS_ERROR;
10062: END;
10063:
10064: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
10065: hz_utility_v2pub.debug(p_message=>'cust_acct_site_id is foreign key to hz_cust_acct_sites and cust_account_id in hz_cust_acct_sites should be same as we put in cust_account_id in hz_cust_account_roles.' ||
10126: -- during creation.
10127:
10128: IF p_create_update_flag = 'C' AND
10129: p_cust_account_role_rec.role_type IS NOT NULL AND
10130: p_cust_account_role_rec.role_type <> FND_API.G_MISS_CHAR
10131: THEN
10132: validate_lookup (
10133: p_column => 'role_type',
10134: p_lookup_type => 'ACCT_ROLE_TYPE',
10150: -- three columns are non-updateable.
10151:
10152: IF p_create_update_flag = 'C' THEN
10153: IF p_cust_account_role_rec.cust_acct_site_id IS NULL OR
10154: p_cust_account_role_rec.cust_acct_site_id = FND_API.G_MISS_NUM
10155: THEN
10156: BEGIN
10157: SELECT 'Y' INTO l_dummy
10158: FROM HZ_CUST_ACCOUNT_ROLES
10165:
10166: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
10167: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id - party_id - role_type' );
10168: FND_MSG_PUB.ADD;
10169: x_return_status := FND_API.G_RET_STS_ERROR;
10170: EXCEPTION
10171: WHEN NO_DATA_FOUND THEN
10172: NULL;
10173: END;
10191:
10192: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
10193: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id - party_id - role_type' );
10194: FND_MSG_PUB.ADD;
10195: x_return_status := FND_API.G_RET_STS_ERROR;
10196: EXCEPTION
10197: WHEN NO_DATA_FOUND THEN
10198: NULL;
10199: END;
10226:
10227: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
10228: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id - party_id - role_type' );
10229: FND_MSG_PUB.ADD;
10230: x_return_status := FND_API.G_RET_STS_ERROR;
10231: EXCEPTION
10232: WHEN NO_DATA_FOUND THEN
10233: NULL;
10234: END;
10255:
10256: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
10257: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id - party_id - role_type' );
10258: FND_MSG_PUB.ADD;
10259: x_return_status := FND_API.G_RET_STS_ERROR;
10260: EXCEPTION
10261: WHEN NO_DATA_FOUND THEN
10262: NULL;
10263: END;
10285: -- validate orig_system_reference
10286: ----------------------------------------------
10287: /****Logical APIs - validation not required****/
10288: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10289: IF (p_cust_account_role_rec.orig_system is not null and p_cust_account_role_rec.orig_system <>fnd_api.g_miss_char)
10290: and (p_cust_account_role_rec.orig_system_reference is not null and p_cust_account_role_rec.orig_system_reference <>fnd_api.g_miss_char)
10291: and p_create_update_flag = 'U'
10292: then
10293: hz_orig_system_ref_pub.get_owner_table_id
10286: ----------------------------------------------
10287: /****Logical APIs - validation not required****/
10288: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10289: IF (p_cust_account_role_rec.orig_system is not null and p_cust_account_role_rec.orig_system <>fnd_api.g_miss_char)
10290: and (p_cust_account_role_rec.orig_system_reference is not null and p_cust_account_role_rec.orig_system_reference <>fnd_api.g_miss_char)
10291: and p_create_update_flag = 'U'
10292: then
10293: hz_orig_system_ref_pub.get_owner_table_id
10294: (p_orig_system => p_cust_account_role_rec.orig_system,
10296: p_owner_table_name => 'HZ_CUST_ACCOUNT_ROLES',
10297: x_owner_table_id => l_mosr_owner_table_id,
10298: x_return_status => l_temp_return_status);
10299:
10300: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
10301: l_mosr_owner_table_id= nvl(p_cust_account_role_rec.cust_account_role_id,l_mosr_owner_table_id))
10302: THEN
10303: l_validate_flag := 'N';
10304: END IF;
10306: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
10307: -- x_return_status. Set x_return_status to error, ONLY if there is error.
10308: -- In case of success, leave it to carry over previous value as before this call.
10309: -- Fix for Bug 5498116 (29-AUG-2006)
10310: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
10311: x_return_status := l_temp_return_status;
10312: END IF;
10313:
10314: end if;
10347: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10348:
10349: -- primary_flag is lookup code in lookup type YES/NO
10350: IF p_cust_account_role_rec.primary_flag IS NOT NULL AND
10351: p_cust_account_role_rec.primary_flag <> FND_API.G_MISS_CHAR
10352: THEN
10353: validate_lookup (
10354: p_column => 'primary_flag',
10355: p_lookup_type => 'YES/NO',
10374: l_cust_acct_site_id := p_cust_account_role_rec.cust_acct_site_id;
10375: END IF;
10376:
10377: IF l_cust_acct_site_id IS NULL OR
10378: l_cust_acct_site_id = FND_API.G_MISS_NUM
10379: THEN
10380: BEGIN
10381: SELECT 'Y' INTO l_dummy
10382: FROM HZ_CUST_ACCOUNT_ROLES
10389:
10390: FND_MESSAGE.SET_NAME( 'AR', 'HZ_CUST_ACCT_ROLE_PRIMARY' );
10391: FND_MESSAGE.SET_TOKEN( 'ENTITY', 'account' );
10392: FND_MSG_PUB.ADD;
10393: x_return_status := FND_API.G_RET_STS_ERROR;
10394: EXCEPTION
10395: WHEN NO_DATA_FOUND THEN
10396: NULL;
10397: END;
10416:
10417: FND_MESSAGE.SET_NAME( 'AR', 'HZ_CUST_ACCT_ROLE_PRIMARY' );
10418: FND_MESSAGE.SET_TOKEN( 'ENTITY', 'account site' );
10419: FND_MSG_PUB.ADD;
10420: x_return_status := FND_API.G_RET_STS_ERROR;
10421: EXCEPTION
10422: WHEN NO_DATA_FOUND THEN
10423: NULL;
10424: END;
10467: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10468: -- status is lookup code in lookup type REGISTRY_STATUS because
10469: -- the status is used in party merge.
10470: IF p_cust_account_role_rec.status IS NOT NULL AND
10471: p_cust_account_role_rec.status <> FND_API.G_MISS_CHAR AND
10472: ( p_create_update_flag = 'C' OR
10473: ( p_create_update_flag = 'U' AND
10474: p_cust_account_role_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
10475: THEN
10470: IF p_cust_account_role_rec.status IS NOT NULL AND
10471: p_cust_account_role_rec.status <> FND_API.G_MISS_CHAR AND
10472: ( p_create_update_flag = 'C' OR
10473: ( p_create_update_flag = 'U' AND
10474: p_cust_account_role_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
10475: THEN
10476: validate_lookup (
10477: p_column => 'status',
10478: p_lookup_type => 'REGISTRY_STATUS',
10546: * p_role_responsibility_rec Customer account role responsibility record.
10547: * p_rowid Rowid of the record (used only in update mode).
10548: * IN/OUT:
10549: * x_return_status Return status after the call. The status can
10550: * be FND_API.G_RET_STS_SUCCESS (success),
10551: * FND_API.G_RET_STS_ERROR (error),
10552: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10553: *
10554: * NOTES
10547: * p_rowid Rowid of the record (used only in update mode).
10548: * IN/OUT:
10549: * x_return_status Return status after the call. The status can
10550: * be FND_API.G_RET_STS_SUCCESS (success),
10551: * FND_API.G_RET_STS_ERROR (error),
10552: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10553: *
10554: * NOTES
10555: *
10548: * IN/OUT:
10549: * x_return_status Return status after the call. The status can
10550: * be FND_API.G_RET_STS_SUCCESS (success),
10551: * FND_API.G_RET_STS_ERROR (error),
10552: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10553: *
10554: * NOTES
10555: *
10556: * MODIFICATION HISTORY
10609: -- If primary key value is not passed, it will be generated
10610: -- from sequence by table handler.
10611:
10612: IF p_role_responsibility_rec.responsibility_id IS NOT NULL AND
10613: p_role_responsibility_rec.responsibility_id <> FND_API.G_MISS_NUM
10614: THEN
10615: BEGIN
10616: SELECT 'Y' INTO l_dummy
10617: FROM HZ_ROLE_RESPONSIBILITY
10619:
10620: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
10621: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'responsibility_id' );
10622: FND_MSG_PUB.ADD;
10623: x_return_status := FND_API.G_RET_STS_ERROR;
10624: EXCEPTION
10625: WHEN NO_DATA_FOUND THEN
10626: NULL;
10627: END;
10667:
10668: -- responsibility_type is lookup code in lookup type SITE_USE_CODE
10669:
10670: IF p_role_responsibility_rec.responsibility_type IS NOT NULL AND
10671: p_role_responsibility_rec.responsibility_type <> FND_API.G_MISS_CHAR AND
10672: ( p_create_update_flag = 'C' OR
10673: ( p_create_update_flag = 'U' AND
10674: p_role_responsibility_rec.responsibility_type <> NVL( l_responsibility_type, FND_API.G_MISS_CHAR ) ) )
10675: THEN
10670: IF p_role_responsibility_rec.responsibility_type IS NOT NULL AND
10671: p_role_responsibility_rec.responsibility_type <> FND_API.G_MISS_CHAR AND
10672: ( p_create_update_flag = 'C' OR
10673: ( p_create_update_flag = 'U' AND
10674: p_role_responsibility_rec.responsibility_type <> NVL( l_responsibility_type, FND_API.G_MISS_CHAR ) ) )
10675: THEN
10676: validate_lookup (
10677: p_column => 'responsibility_type',
10678: p_lookup_type => 'SITE_USE_CODE',
10748: -- we only need to check FK during creation.
10749:
10750: IF p_create_update_flag = 'C' AND
10751: p_role_responsibility_rec.cust_account_role_id IS NOT NULL AND
10752: p_role_responsibility_rec.cust_account_role_id <> FND_API.G_MISS_NUM
10753: THEN
10754: check_cust_account_role_fk (
10755: p_column => 'cust_account_role_id',
10756: p_column_value => p_role_responsibility_rec.cust_account_role_id,
10790:
10791: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
10792: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_role_id - responsibility_type' );
10793: FND_MSG_PUB.ADD;
10794: x_return_status := FND_API.G_RET_STS_ERROR;
10795: EXCEPTION
10796: WHEN NO_DATA_FOUND THEN
10797: NULL;
10798: END;
10851: /****Logical APIs - validation not required****/
10852: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10853: -- primary_flag is lookup code in lookup type YES/NO
10854: IF p_role_responsibility_rec.primary_flag IS NOT NULL AND
10855: p_role_responsibility_rec.primary_flag <> FND_API.G_MISS_CHAR
10856: THEN
10857: validate_lookup (
10858: p_column => 'primary_flag',
10859: p_lookup_type => 'YES/NO',
10886: AND ROWNUM = 1;
10887:
10888: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_ROLE_PRIMARY' );
10889: FND_MSG_PUB.ADD;
10890: x_return_status := FND_API.G_RET_STS_ERROR;
10891: EXCEPTION
10892: WHEN NO_DATA_FOUND THEN
10893: NULL;
10894: END;
10970: FND_MESSAGE.SET_TOKEN( 'FK', 'cust_account_id' );
10971: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10972: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_accounts' );
10973: FND_MSG_PUB.ADD;
10974: x_return_status := FND_API.G_RET_STS_ERROR;
10975:
10976: END check_cust_account_fk;
10977:
10978: PROCEDURE check_cust_acct_site_fk (
11005: FND_MESSAGE.SET_TOKEN( 'FK', 'cust_acct_site_id' );
11006: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11007: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_acct_sites' );
11008: FND_MSG_PUB.ADD;
11009: x_return_status := FND_API.G_RET_STS_ERROR;
11010:
11011: END check_cust_acct_site_fk;
11012:
11013: PROCEDURE check_cust_site_use_fk (
11029: MO_GLOBAL.validate_orgid_pub_api(l_org_id,'N',l_return_status);
11030: EXCEPTION
11031: WHEN OTHERS
11032: THEN
11033: x_return_status := FND_API.G_RET_STS_ERROR;
11034: END;
11035:
11036: EXCEPTION
11037: WHEN NO_DATA_FOUND THEN
11039: FND_MESSAGE.SET_TOKEN( 'FK', 'site_use_id' );
11040: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11041: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_site_uses' );
11042: FND_MSG_PUB.ADD;
11043: x_return_status := FND_API.G_RET_STS_ERROR;
11044:
11045: END check_cust_site_use_fk;
11046: PROCEDURE check_cust_site_use_cont_fk(
11047: p_column IN VARCHAR2,
11063: FND_MESSAGE.SET_TOKEN( 'FK', 'contact_id' );
11064: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11065: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_account_roles' );
11066: FND_MSG_PUB.ADD;
11067: x_return_status := FND_API.G_RET_STS_ERROR;
11068:
11069: END check_cust_site_use_cont_fk;
11070:
11071: PROCEDURE check_cust_account_role_fk (
11087: FND_MESSAGE.SET_TOKEN( 'FK', 'cust_account_role_id' );
11088: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11089: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_account_roles' );
11090: FND_MSG_PUB.ADD;
11091: x_return_status := FND_API.G_RET_STS_ERROR;
11092:
11093: END check_cust_account_role_fk;
11094:
11095: PROCEDURE check_per_all_people_f_fk (
11120: FND_MESSAGE.SET_TOKEN( 'FK', 'person_id' );
11121: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11122: FND_MESSAGE.SET_TOKEN( 'TABLE', 'per_all_people_f' );
11123: FND_MSG_PUB.ADD;
11124: x_return_status := FND_API.G_RET_STS_ERROR;
11125:
11126: END check_per_all_people_f_fk;
11127:
11128: PROCEDURE check_collector_fk (
11145: FND_MESSAGE.SET_TOKEN( 'FK', 'collector_id' );
11146: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11147: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_collectors');
11148: FND_MSG_PUB.ADD;
11149: x_return_status := FND_API.G_RET_STS_ERROR;
11150:
11151: END check_collector_fk;
11152:
11153: PROCEDURE check_party_fk (
11170: FND_MESSAGE.SET_TOKEN( 'FK', 'party_id' );
11171: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11172: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_parties' );
11173: FND_MSG_PUB.ADD;
11174: x_return_status := FND_API.G_RET_STS_ERROR;
11175:
11176: END check_party_fk;
11177:
11178: PROCEDURE check_party_site_fk (
11195: FND_MESSAGE.SET_TOKEN( 'FK', 'party_site_id' );
11196: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11197: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_party_sites' );
11198: FND_MSG_PUB.ADD;
11199: x_return_status := FND_API.G_RET_STS_ERROR;
11200: END;
11201:
11202: END check_party_site_fk;
11203:
11225: FND_MESSAGE.SET_TOKEN( 'FK', 'currency_code' );
11226: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11227: FND_MESSAGE.SET_TOKEN( 'TABLE', 'fnd_currencies' );
11228: FND_MSG_PUB.ADD;
11229: x_return_status := FND_API.G_RET_STS_ERROR;
11230:
11231: END check_currency_fk;
11232:
11233: PROCEDURE check_oe_ship_methods_v_fk (
11253: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_COLUMN_SHOULD_BE_NULL' );
11254: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11255: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_accounts' );
11256: FND_MSG_PUB.ADD;
11257: x_return_status := FND_API.G_RET_STS_ERROR;
11258:
11259: RETURN;
11260: END IF;
11261:
11263: EXCEPTION
11264: WHEN NO_DATA_FOUND THEN
11265: FND_MESSAGE.SET_NAME( 'AR', 'AR_NO_ROW_IN_SYSTEM_PARAMETERS');
11266: FND_MSG_PUB.ADD;
11267: x_return_status := FND_API.G_RET_STS_ERROR;
11268: END ;
11269: END IF;
11270: **************************************************************************/
11271: validate_lookup (
11308: FND_MESSAGE.SET_TOKEN( 'FK', 'payment_term_id' );
11309: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11310: FND_MESSAGE.SET_TOKEN( 'TABLE', 'RA_TERMS' );
11311: FND_MSG_PUB.ADD;
11312: x_return_status := FND_API.G_RET_STS_ERROR;
11313: END check_payterm_id_fk;
11314:
11315: PROCEDURE check_late_charge_term_id_fk (
11316: p_column IN VARCHAR2,
11339: -- Balance forward billing has not been enabled.
11340: -- Pass a late charge payment term that does not have a balance forward billing cycle.
11341: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_PAY_TERM_DISABLED' );
11342: FND_MSG_PUB.ADD;
11343: x_return_status := FND_API.G_RET_STS_ERROR;
11344: end;
11345: elsif p_cons_inv_flag = 'Y' then
11346: begin
11347: SELECT 'Y'
11357: -- Balance forward billing has been enabled.
11358: -- Pass a late charge payment term that has a balance forward billing cycle.
11359: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_PAY_TERM_ENABLED' );
11360: FND_MSG_PUB.ADD;
11361: x_return_status := FND_API.G_RET_STS_ERROR;
11362: end;
11363: end if;
11364:
11365: END check_late_charge_term_id_fk;
11386: FND_MESSAGE.SET_TOKEN( 'FK', 'standard_text_id' );
11387: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11388: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_standard_text' );
11389: FND_MSG_PUB.ADD;
11390: x_return_status := FND_API.G_RET_STS_ERROR;
11391: END check_message_text_id_fk;
11392:
11393: PROCEDURE check_exchange_rate_type_fk (
11394: p_column IN VARCHAR2,
11412: FND_MESSAGE.SET_TOKEN( 'FK', 'conversion_type' );
11413: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11414: FND_MESSAGE.SET_TOKEN( 'TABLE', 'gl_daily_conversion_types' );
11415: FND_MSG_PUB.ADD;
11416: x_return_status := FND_API.G_RET_STS_ERROR;
11417: END check_exchange_rate_type_fk;
11418:
11419: PROCEDURE check_charge_schedule_id_fk (
11420: p_column IN VARCHAR2,
11437: FND_MESSAGE.SET_TOKEN( 'FK', 'schedule_id' );
11438: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11439: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_charge_schedules' );
11440: FND_MSG_PUB.ADD;
11441: x_return_status := FND_API.G_RET_STS_ERROR;
11442: END check_charge_schedule_id_fk;
11443:
11444: PROCEDURE check_finchrg_trx_fk (
11445: p_column IN VARCHAR2,
11460: FND_MESSAGE.SET_TOKEN( 'FK', 'finchrg_receivables_trx_id' );
11461: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11462: FND_MESSAGE.SET_TOKEN( 'TABLE', 'AR_RECEIVABLES_TRX' );
11463: FND_MSG_PUB.ADD;
11464: x_return_status := FND_API.G_RET_STS_ERROR;
11465: END check_finchrg_trx_fk;
11466:
11467: PROCEDURE check_price_list_fk (
11468: p_column IN VARCHAR2,
11494: -- Bug 3591694 FND_MESSAGE.SET_TOKEN( 'TABLE', 'SO_PRICE_LISTS' );
11495: FND_MESSAGE.SET_TOKEN( 'TABLE', 'QP_LIST_HEADERS_B' );
11496:
11497: FND_MSG_PUB.ADD;
11498: x_return_status := FND_API.G_RET_STS_ERROR;
11499: END check_price_list_fk;
11500:
11501: PROCEDURE check_item_cross_ref(
11502: p_column IN VARCHAR2,
11514: FND_MESSAGE.SET_TOKEN( 'FK', 'item_cross_ref_pref' );
11515: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11516: FND_MESSAGE.SET_TOKEN( 'TABLE', 'mtl_cross_reference_types' );
11517: FND_MSG_PUB.ADD;
11518: x_return_status := FND_API.G_RET_STS_ERROR;
11519: END check_item_cross_ref;
11520:
11521: PROCEDURE check_tax_code(
11522: p_column IN VARCHAR2,
11571: FND_MESSAGE.SET_TOKEN( 'FK', 'tax_code' );
11572: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11573: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ZX_OUTPUT_CLASSIFICATIONS_V' );
11574: FND_MSG_PUB.ADD;
11575: x_return_status := FND_API.G_RET_STS_ERROR;
11576: END check_tax_code;
11577:
11578: PROCEDURE check_warehouse(
11579: p_column IN VARCHAR2,
11592: FND_MESSAGE.SET_TOKEN( 'FK', 'warehouse_id' );
11593: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11594: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ORG_ORGANIZATION_DEFINITIONS' );
11595: FND_MSG_PUB.ADD;
11596: x_return_status := FND_API.G_RET_STS_ERROR;
11597: END check_warehouse;
11598:
11599: PROCEDURE check_prim_salesrep(
11600: p_column IN VARCHAR2,
11622: FND_MESSAGE.SET_TOKEN( 'FK', 'primary_salesrep_id' );
11623: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11624: FND_MESSAGE.SET_TOKEN( 'TABLE', 'JTF_RS_SALESREPS' );
11625: FND_MSG_PUB.ADD;
11626: x_return_status := FND_API.G_RET_STS_ERROR;
11627: */
11628: x_return_status := FND_API.G_RET_STS_SUCCESS;
11629: END check_prim_salesrep;
11630: --Bug 7695461 : Commented x_return_status value setting and added NULL.
11624: FND_MESSAGE.SET_TOKEN( 'TABLE', 'JTF_RS_SALESREPS' );
11625: FND_MSG_PUB.ADD;
11626: x_return_status := FND_API.G_RET_STS_ERROR;
11627: */
11628: x_return_status := FND_API.G_RET_STS_SUCCESS;
11629: END check_prim_salesrep;
11630: --Bug 7695461 : Commented x_return_status value setting and added NULL.
11631: PROCEDURE check_ord_type(
11632: p_column IN VARCHAR2,
11661: FND_MESSAGE.SET_TOKEN( 'FK', 'order_type_id' );
11662: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11663: FND_MESSAGE.SET_TOKEN( 'TABLE', 'OE_TRANSACTIONS_TYPES_VL' );
11664: FND_MSG_PUB.ADD;
11665: x_return_status := FND_API.G_RET_STS_ERROR;
11666: */
11667: --x_return_status := FND_API.G_RET_STS_SUCCESS;
11668: NULL;
11669: END check_ord_type;
11663: FND_MESSAGE.SET_TOKEN( 'TABLE', 'OE_TRANSACTIONS_TYPES_VL' );
11664: FND_MSG_PUB.ADD;
11665: x_return_status := FND_API.G_RET_STS_ERROR;
11666: */
11667: --x_return_status := FND_API.G_RET_STS_SUCCESS;
11668: NULL;
11669: END check_ord_type;
11670:
11671: PROCEDURE check_partial_mandatory_column (
11675: ) IS
11676:
11677: BEGIN
11678:
11679: IF p_column_value = FND_API.G_MISS_NUM THEN
11680: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
11681: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11682: FND_MSG_PUB.ADD;
11683: x_return_status := FND_API.G_RET_STS_ERROR;
11679: IF p_column_value = FND_API.G_MISS_NUM THEN
11680: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
11681: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11682: FND_MSG_PUB.ADD;
11683: x_return_status := FND_API.G_RET_STS_ERROR;
11684: END IF;
11685:
11686: END check_partial_mandatory_column;
11687:
11692: ) IS
11693:
11694: BEGIN
11695:
11696: IF p_column_value = FND_API.G_MISS_CHAR THEN
11697: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
11698: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11699: FND_MSG_PUB.ADD;
11700: x_return_status := FND_API.G_RET_STS_ERROR;
11696: IF p_column_value = FND_API.G_MISS_CHAR THEN
11697: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
11698: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11699: FND_MSG_PUB.ADD;
11700: x_return_status := FND_API.G_RET_STS_ERROR;
11701: END IF;
11702:
11703: END check_partial_mandatory_column;
11704:
11711:
11712: BEGIN
11713:
11714: IF p_column_value IS NOT NULL AND
11715: p_column_value <> FND_API.G_MISS_NUM
11716: THEN
11717: IF NOT FND_FLEX_KEYVAL.validate_ccid (
11718: appl_short_name => 'SQLGL',
11719: key_flex_code => 'GL#',
11724: THEN
11725: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_' ||
11726: UPPER( p_gl_name ) || '_CCID_INVALID' );
11727: FND_MSG_PUB.ADD;
11728: x_return_status := FND_API.G_RET_STS_ERROR;
11729: END IF;
11730: END IF;
11731:
11732: END validate_gl_id;
11752: FND_MESSAGE.SET_TOKEN( 'FK', p_column );
11753: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11754: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_autocash_hierarchies');
11755: FND_MSG_PUB.ADD;
11756: x_return_status := FND_API.G_RET_STS_ERROR;
11757:
11758: END check_auto_hierid_fk;
11759:
11760: PROCEDURE check_stat_cycid_fk (
11777: FND_MESSAGE.SET_TOKEN( 'FK', 'statement_cycle_id' );
11778: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11779: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_statement_cycles');
11780: FND_MSG_PUB.ADD;
11781: x_return_status := FND_API.G_RET_STS_ERROR;
11782:
11783: END check_stat_cycid_fk;
11784:
11785: PROCEDURE check_dunning_letid_fk (
11802: FND_MESSAGE.SET_TOKEN( 'FK', 'dunning_letter_set_id' );
11803: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11804: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_dunning_letter_sets');
11805: FND_MSG_PUB.ADD;
11806: x_return_status := FND_API.G_RET_STS_ERROR;
11807:
11808: END check_dunning_letid_fk;
11809:
11810: PROCEDURE check_standard_terms_fk (
11851: FND_MESSAGE.SET_TOKEN( 'FK', 'standard_terms' );
11852: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11853: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ra_terms_b');
11854: FND_MSG_PUB.ADD;
11855: x_return_status := FND_API.G_RET_STS_ERROR;
11856: end;
11857: elsif p_cons_inv_flag = 'Y' then
11858: begin
11859: SELECT 'Y'
11869: -- Balance forward billing has been enabled. Pass a payment term
11870: -- with a balance forward billing cycle associated.
11871: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_BAL_FWD_BILL_ENABLED' );
11872: FND_MSG_PUB.ADD;
11873: x_return_status := FND_API.G_RET_STS_ERROR;
11874: end;
11875: end if;
11876:
11877: /*
11888: FND_MESSAGE.SET_TOKEN( 'FK', 'standard_terms' );
11889: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11890: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ra_terms');
11891: FND_MSG_PUB.ADD;
11892: x_return_status := FND_API.G_RET_STS_ERROR;
11893: */
11894: END check_standard_terms_fk;
11895:
11896: PROCEDURE check_grouping_ruleid_fk (
11915: FND_MESSAGE.SET_TOKEN( 'FK', 'grouping_rule_id' );
11916: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11917: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ra_grouping_rules');
11918: FND_MSG_PUB.ADD;
11919: x_return_status := FND_API.G_RET_STS_ERROR;
11920:
11921: END check_grouping_ruleid_fk;
11922:
11923: PROCEDURE check_automatch_set_id_fk (
11946: FND_MESSAGE.SET_TOKEN( 'FK', 'Automatch_Set_Id' );
11947: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11948: FND_MESSAGE.SET_TOKEN( 'TABLE', 'AR_CASH_AUTO_RULE_SETS');
11949: FND_MSG_PUB.ADD;
11950: x_return_status := FND_API.G_RET_STS_ERROR;
11951:
11952: END check_automatch_set_id_fk;
11953:
11954: PROCEDURE check_positive_value (
11961: IF p_column_value < 0 THEN
11962: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LESS_THAN_ZERO' );
11963: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11964: FND_MSG_PUB.ADD;
11965: x_return_status := FND_API.G_RET_STS_ERROR;
11966: END IF;
11967:
11968: END check_positive_value;
11969:
11977: IF p_column_value <= 0 THEN
11978: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_GREATER_THAN_ZERO' );
11979: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11980: FND_MSG_PUB.ADD;
11981: x_return_status := FND_API.G_RET_STS_ERROR;
11982: END IF;
11983:
11984: END check_greater_than_zero;
11985:
11993: IF p_column_value > 100 THEN
11994: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_GREATER_THAN_100' );
11995: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11996: FND_MSG_PUB.ADD;
11997: x_return_status := FND_API.G_RET_STS_ERROR;
11998: END IF;
11999:
12000: END check_less_than_100;
12001:
12363:
12364: IF p_restricted = 'N' THEN
12365: IF ( p_create_update_flag = 'C' AND
12366: ( p_column_value IS NULL OR
12367: p_column_value = FND_API.G_MISS_CHAR ) ) OR
12368: ( p_create_update_flag = 'U' AND
12369: p_column_value = FND_API.G_MISS_CHAR )
12370: THEN
12371: l_error := TRUE;
12365: IF ( p_create_update_flag = 'C' AND
12366: ( p_column_value IS NULL OR
12367: p_column_value = FND_API.G_MISS_CHAR ) ) OR
12368: ( p_create_update_flag = 'U' AND
12369: p_column_value = FND_API.G_MISS_CHAR )
12370: THEN
12371: l_error := TRUE;
12372: END IF;
12373: ELSE
12371: l_error := TRUE;
12372: END IF;
12373: ELSE
12374: IF ( p_column_value IS NULL OR
12375: p_column_value = FND_API.G_MISS_CHAR )
12376: THEN
12377: l_error := TRUE;
12378: END IF;
12379: END IF;
12381: IF l_error THEN
12382: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
12383: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12384: FND_MSG_PUB.ADD;
12385: x_return_status := FND_API.G_RET_STS_ERROR;
12386: END IF;
12387:
12388: END validate_mandatory;
12389:
12401:
12402: IF p_restricted = 'N' THEN
12403: IF ( p_create_update_flag = 'C' AND
12404: ( p_column_value IS NULL OR
12405: p_column_value = FND_API.G_MISS_NUM ) ) OR
12406: ( p_create_update_flag = 'U' AND
12407: p_column_value = FND_API.G_MISS_NUM )
12408: THEN
12409: l_error := TRUE;
12403: IF ( p_create_update_flag = 'C' AND
12404: ( p_column_value IS NULL OR
12405: p_column_value = FND_API.G_MISS_NUM ) ) OR
12406: ( p_create_update_flag = 'U' AND
12407: p_column_value = FND_API.G_MISS_NUM )
12408: THEN
12409: l_error := TRUE;
12410: END IF;
12411: ELSE
12409: l_error := TRUE;
12410: END IF;
12411: ELSE
12412: IF ( p_column_value IS NULL OR
12413: p_column_value = FND_API.G_MISS_NUM )
12414: THEN
12415: l_error := TRUE;
12416: END IF;
12417: END IF;
12419: IF l_error THEN
12420: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
12421: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12422: FND_MSG_PUB.ADD;
12423: x_return_status := FND_API.G_RET_STS_ERROR;
12424: END IF;
12425:
12426: END validate_mandatory;
12427:
12439:
12440: IF p_restricted = 'N' THEN
12441: IF ( p_create_update_flag = 'C' AND
12442: ( p_column_value IS NULL OR
12443: p_column_value = FND_API.G_MISS_DATE ) ) OR
12444: ( p_create_update_flag = 'U' AND
12445: p_column_value = FND_API.G_MISS_DATE )
12446: THEN
12447: l_error := TRUE;
12441: IF ( p_create_update_flag = 'C' AND
12442: ( p_column_value IS NULL OR
12443: p_column_value = FND_API.G_MISS_DATE ) ) OR
12444: ( p_create_update_flag = 'U' AND
12445: p_column_value = FND_API.G_MISS_DATE )
12446: THEN
12447: l_error := TRUE;
12448: END IF;
12449: ELSE
12447: l_error := TRUE;
12448: END IF;
12449: ELSE
12450: IF ( p_column_value IS NULL OR
12451: p_column_value = FND_API.G_MISS_DATE )
12452: THEN
12453: l_error := TRUE;
12454: END IF;
12455: END IF;
12457: IF l_error THEN
12458: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
12459: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12460: FND_MSG_PUB.ADD;
12461: x_return_status := FND_API.G_RET_STS_ERROR;
12462: END IF;
12463:
12464: END validate_mandatory;
12465:
12476: BEGIN
12477:
12478: IF p_column_value IS NOT NULL THEN
12479: IF p_restricted = 'Y' THEN
12480: IF ( p_column_value <> FND_API.G_MISS_CHAR OR
12481: p_old_column_value IS NOT NULL ) AND
12482: ( p_old_column_value IS NULL OR
12483: p_column_value <> p_old_column_value )
12484: THEN
12485: l_error := TRUE;
12486: END IF;
12487: ELSE
12488: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053.
12489: p_old_column_value <> FND_API.G_MISS_CHAR)
12490: AND
12491: ( p_column_value = FND_API.G_MISS_CHAR OR
12492: p_column_value <> p_old_column_value )
12493: THEN
12487: ELSE
12488: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053.
12489: p_old_column_value <> FND_API.G_MISS_CHAR)
12490: AND
12491: ( p_column_value = FND_API.G_MISS_CHAR OR
12492: p_column_value <> p_old_column_value )
12493: THEN
12494: l_error := TRUE;
12495: END IF;
12499: IF l_error THEN
12500: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
12501: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12502: FND_MSG_PUB.ADD;
12503: x_return_status := FND_API.G_RET_STS_ERROR;
12504: END IF;
12505:
12506: END validate_nonupdateable;
12507:
12518: BEGIN
12519:
12520: IF p_column_value IS NOT NULL THEN
12521: IF p_restricted = 'Y' THEN
12522: IF ( p_column_value <> FND_API.G_MISS_NUM OR
12523: p_old_column_value IS NOT NULL ) AND
12524: ( p_old_column_value IS NULL OR
12525: p_column_value <> p_old_column_value )
12526: THEN
12527: l_error := TRUE;
12528: END IF;
12529: ELSE
12530: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053
12531: p_old_column_value <> FND_API.G_MISS_NUM)
12532: AND
12533: ( p_column_value = FND_API.G_MISS_NUM OR
12534: p_column_value <> p_old_column_value )
12535: THEN
12529: ELSE
12530: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053
12531: p_old_column_value <> FND_API.G_MISS_NUM)
12532: AND
12533: ( p_column_value = FND_API.G_MISS_NUM OR
12534: p_column_value <> p_old_column_value )
12535: THEN
12536: l_error := TRUE;
12537: END IF;
12541: IF l_error THEN
12542: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
12543: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12544: FND_MSG_PUB.ADD;
12545: x_return_status := FND_API.G_RET_STS_ERROR;
12546: END IF;
12547:
12548: END validate_nonupdateable;
12549:
12560: BEGIN
12561:
12562: IF p_column_value IS NOT NULL THEN
12563: IF p_restricted = 'Y' THEN
12564: IF ( p_column_value <> FND_API.G_MISS_DATE OR
12565: p_old_column_value IS NOT NULL ) AND
12566: ( p_old_column_value IS NULL OR
12567: p_column_value <> p_old_column_value )
12568: THEN
12569: l_error := TRUE;
12570: END IF;
12571: ELSE
12572: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053
12573: p_old_column_value <> FND_API.G_MISS_DATE)
12574: AND
12575: ( p_column_value = FND_API.G_MISS_DATE OR
12576: p_column_value <> p_old_column_value )
12577: THEN
12571: ELSE
12572: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053
12573: p_old_column_value <> FND_API.G_MISS_DATE)
12574: AND
12575: ( p_column_value = FND_API.G_MISS_DATE OR
12576: p_column_value <> p_old_column_value )
12577: THEN
12578: l_error := TRUE;
12579: END IF;
12583: IF l_error THEN
12584: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
12585: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12586: FND_MSG_PUB.ADD;
12587: x_return_status := FND_API.G_RET_STS_ERROR;
12588: END IF;
12589:
12590: END validate_nonupdateable;
12591:
12610: l_end_date := p_end_date;
12611: ELSIF p_create_update_flag = 'U' THEN
12612: IF p_start_date IS NOT NULL
12613: THEN
12614: IF p_start_date = FND_API.G_MISS_DATE THEN
12615: l_start_date := NULL;
12616: ELSE
12617: l_start_date := p_start_date;
12618: END IF;
12619: END IF;
12620:
12621: IF p_end_date IS NOT NULL
12622: THEN
12623: IF p_end_date = FND_API.G_MISS_DATE THEN
12624: l_end_date := NULL;
12625: ELSE
12626: l_end_date := p_end_date;
12627: END IF;
12628: END IF;
12629: END IF;
12630:
12631: IF l_end_date IS NOT NULL AND
12632: l_end_date <> FND_API.G_MISS_DATE AND
12633: ( l_start_date IS NULL OR
12634: l_start_date = FND_API.G_MISS_DATE OR
12635: l_start_date > l_end_date )
12636: THEN
12630:
12631: IF l_end_date IS NOT NULL AND
12632: l_end_date <> FND_API.G_MISS_DATE AND
12633: ( l_start_date IS NULL OR
12634: l_start_date = FND_API.G_MISS_DATE OR
12635: l_start_date > l_end_date )
12636: THEN
12637: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DATE_GREATER' );
12638: FND_MESSAGE.SET_TOKEN( 'DATE2', p_end_date_column_name );
12637: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DATE_GREATER' );
12638: FND_MESSAGE.SET_TOKEN( 'DATE2', p_end_date_column_name );
12639: FND_MESSAGE.SET_TOKEN( 'DATE1', p_start_date_column_name );
12640: FND_MSG_PUB.ADD;
12641: x_return_status := FND_API.G_RET_STS_ERROR;
12642: END IF;
12643:
12644: END validate_start_end_date;
12645:
12650: ) IS
12651:
12652: BEGIN
12653:
12654: IF p_column_value = FND_API.G_MISS_CHAR THEN
12655: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12656: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12657: FND_MSG_PUB.ADD;
12658: x_return_status := FND_API.G_RET_STS_ERROR;
12654: IF p_column_value = FND_API.G_MISS_CHAR THEN
12655: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12656: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12657: FND_MSG_PUB.ADD;
12658: x_return_status := FND_API.G_RET_STS_ERROR;
12659: END IF;
12660:
12661: END validate_cannot_update_to_null;
12662:
12667: ) IS
12668:
12669: BEGIN
12670:
12671: IF p_column_value = FND_API.G_MISS_NUM THEN
12672: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12673: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12674: FND_MSG_PUB.ADD;
12675: x_return_status := FND_API.G_RET_STS_ERROR;
12671: IF p_column_value = FND_API.G_MISS_NUM THEN
12672: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12673: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12674: FND_MSG_PUB.ADD;
12675: x_return_status := FND_API.G_RET_STS_ERROR;
12676: END IF;
12677:
12678: END validate_cannot_update_to_null;
12679:
12684: ) IS
12685:
12686: BEGIN
12687:
12688: IF p_column_value = FND_API.G_MISS_DATE THEN
12689: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12690: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12691: FND_MSG_PUB.ADD;
12692: x_return_status := FND_API.G_RET_STS_ERROR;
12688: IF p_column_value = FND_API.G_MISS_DATE THEN
12689: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12690: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12691: FND_MSG_PUB.ADD;
12692: x_return_status := FND_API.G_RET_STS_ERROR;
12693: END IF;
12694:
12695: END validate_cannot_update_to_null;
12696:
12706:
12707: BEGIN
12708:
12709: IF p_column_value IS NOT NULL AND
12710: p_column_value <> FND_API.G_MISS_CHAR THEN
12711:
12712: IF p_lookup_type = 'YES/NO' THEN
12713: IF p_column_value NOT IN ('Y', 'N') THEN
12714: l_error := TRUE;
12726: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
12727: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12728: FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', p_lookup_type );
12729: FND_MSG_PUB.ADD;
12730: x_return_status := FND_API.G_RET_STS_ERROR;
12731: END IF;
12732: END IF;
12733:
12734: END validate_lookup;
12804: END IF;
12805:
12806: -- created_by_module is lookup code in lookup type HZ_CREATED_BY_MODULES
12807: IF p_created_by_module IS NOT NULL AND
12808: p_created_by_module <> fnd_api.g_miss_char AND
12809: (p_create_update_flag = 'C' OR
12810: (p_create_update_flag = 'U' AND
12811: (p_old_created_by_module IS NULL OR
12812: p_created_by_module <> p_old_created_by_module)))
12917: if (p_customer_profile_rec.cons_bill_level IS NULL
12918: OR p_customer_profile_rec.cons_inv_type IS NULL) then
12919: FND_MESSAGE.SET_NAME('AR', 'HZ_API_BILL_LVL_TYPE_INVALID');
12920: FND_MSG_PUB.ADD;
12921: x_return_status := FND_API.G_RET_STS_ERROR;
12922: else
12923: if p_customer_profile_rec.cons_bill_level IS NOT NULL then
12924: SELECT COUNT(*)
12925: INTO l_bill_level_count
12931:
12932: IF (l_bill_level_count = 0) THEN
12933: FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_BILL_LEVEL');
12934: FND_MSG_PUB.ADD;
12935: x_return_status := FND_API.G_RET_STS_ERROR;
12936: END IF;
12937: end if;
12938: end if;
12939:
12950:
12951: IF (l_bill_TYPE_count = 0) THEN
12952: FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ACCT_BILL_TYPE');
12953: FND_MSG_PUB.ADD;
12954: x_return_status := FND_API.G_RET_STS_ERROR;
12955: END IF;
12956: -- For Site use bill level, use Summary, Detail, or Imported as bill type.
12957: else
12958: SELECT COUNT(*)
12965:
12966: IF (l_bill_TYPE_count = 0) THEN
12967: FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ACCT_BILL_TYPE');
12968: FND_MSG_PUB.ADD;
12969: x_return_status := FND_API.G_RET_STS_ERROR;
12970: END IF;
12971: end if;
12972: end if;
12973: END validate_bill_level;
13020: END IF;
13021:
13022: -- Federal Entity Type Validation
13023: IF p_cust_account_rec.federal_entity_type IS NOT NULL AND
13024: p_cust_account_rec.federal_entity_type <> FND_API.G_MISS_CHAR AND
13025: ( p_create_update_flag = 'C' OR
13026: ( p_create_update_flag = 'U' AND
13027: p_cust_account_rec.federal_entity_type <> NVL( l_federal_entity_type, FND_API.G_MISS_CHAR ) ) )
13028: THEN
13023: IF p_cust_account_rec.federal_entity_type IS NOT NULL AND
13024: p_cust_account_rec.federal_entity_type <> FND_API.G_MISS_CHAR AND
13025: ( p_create_update_flag = 'C' OR
13026: ( p_create_update_flag = 'U' AND
13027: p_cust_account_rec.federal_entity_type <> NVL( l_federal_entity_type, FND_API.G_MISS_CHAR ) ) )
13028: THEN
13029: validate_lookup (
13030: p_column => 'federal_entity_type',
13031: p_lookup_table => 'fv_lookup_codes',
13044: -- Trading Partner Agency Id Validation
13045: --If Federal Entity Type is null and trading partner agency id is not null then raise error
13046: IF ( p_create_update_flag = 'C' AND (p_cust_account_rec.federal_entity_type IS NULL
13047: OR p_cust_account_rec.federal_entity_type <> 'F'
13048: OR p_cust_account_rec.federal_entity_type = FND_API.G_MISS_CHAR)
13049: AND (p_cust_account_rec.trading_partner_agency_id IS NOT NULL
13050: AND p_cust_account_rec.trading_partner_agency_id <> FND_API.G_MISS_CHAR
13051: )
13052: )
13046: IF ( p_create_update_flag = 'C' AND (p_cust_account_rec.federal_entity_type IS NULL
13047: OR p_cust_account_rec.federal_entity_type <> 'F'
13048: OR p_cust_account_rec.federal_entity_type = FND_API.G_MISS_CHAR)
13049: AND (p_cust_account_rec.trading_partner_agency_id IS NOT NULL
13050: AND p_cust_account_rec.trading_partner_agency_id <> FND_API.G_MISS_CHAR
13051: )
13052: )
13053: OR
13054: ( p_create_update_flag = 'U' AND (NVL(p_cust_account_rec.federal_entity_type,l_federal_entity_type) IS NULL
13052: )
13053: OR
13054: ( p_create_update_flag = 'U' AND (NVL(p_cust_account_rec.federal_entity_type,l_federal_entity_type) IS NULL
13055: OR NVL(p_cust_account_rec.federal_entity_type,l_federal_entity_type) <> 'F'
13056: OR p_cust_account_rec.federal_entity_type = FND_API.G_MISS_CHAR)
13057: AND (nvl(p_cust_account_rec.trading_partner_agency_id,l_trading_partner_agency_id) IS NOT NULL
13058: AND NVL(p_cust_account_rec.trading_partner_agency_id,l_trading_partner_agency_id) <> FND_API.G_MISS_CHAR )
13059: )
13060: THEN
13054: ( p_create_update_flag = 'U' AND (NVL(p_cust_account_rec.federal_entity_type,l_federal_entity_type) IS NULL
13055: OR NVL(p_cust_account_rec.federal_entity_type,l_federal_entity_type) <> 'F'
13056: OR p_cust_account_rec.federal_entity_type = FND_API.G_MISS_CHAR)
13057: AND (nvl(p_cust_account_rec.trading_partner_agency_id,l_trading_partner_agency_id) IS NOT NULL
13058: AND NVL(p_cust_account_rec.trading_partner_agency_id,l_trading_partner_agency_id) <> FND_API.G_MISS_CHAR )
13059: )
13060: THEN
13061: FND_MESSAGE.SET_NAME('AR', 'HZ_TPAI_IS_NOT_NULL');
13062: FND_MSG_PUB.ADD;
13059: )
13060: THEN
13061: FND_MESSAGE.SET_NAME('AR', 'HZ_TPAI_IS_NOT_NULL');
13062: FND_MSG_PUB.ADD;
13063: x_return_status := FND_API.G_RET_STS_ERROR;
13064: --If Federal Entity Type is 'Federal' and trading partner agency id is null then raise error
13065: ELSIF ( p_create_update_flag = 'C' AND p_cust_account_rec.federal_entity_type = 'F'
13066: AND (p_cust_account_rec.trading_partner_agency_id IS NULL
13067: OR p_cust_account_rec.trading_partner_agency_id = FND_API.G_MISS_CHAR )
13063: x_return_status := FND_API.G_RET_STS_ERROR;
13064: --If Federal Entity Type is 'Federal' and trading partner agency id is null then raise error
13065: ELSIF ( p_create_update_flag = 'C' AND p_cust_account_rec.federal_entity_type = 'F'
13066: AND (p_cust_account_rec.trading_partner_agency_id IS NULL
13067: OR p_cust_account_rec.trading_partner_agency_id = FND_API.G_MISS_CHAR )
13068: )
13069: OR
13070: ( p_create_update_flag = 'U' AND NVL(p_cust_account_rec.federal_entity_type,l_federal_entity_type) = 'F'
13071: AND (nvl(p_cust_account_rec.trading_partner_agency_id,l_trading_partner_agency_id) IS NULL
13068: )
13069: OR
13070: ( p_create_update_flag = 'U' AND NVL(p_cust_account_rec.federal_entity_type,l_federal_entity_type) = 'F'
13071: AND (nvl(p_cust_account_rec.trading_partner_agency_id,l_trading_partner_agency_id) IS NULL
13072: OR nvl(p_cust_account_rec.trading_partner_agency_id,l_trading_partner_agency_id) = FND_API.G_MISS_CHAR )
13073: )
13074: THEN
13075: FND_MESSAGE.SET_NAME('AR', 'HZ_TPAI_IS_NULL');
13076: FND_MSG_PUB.ADD;
13073: )
13074: THEN
13075: FND_MESSAGE.SET_NAME('AR', 'HZ_TPAI_IS_NULL');
13076: FND_MSG_PUB.ADD;
13077: x_return_status := FND_API.G_RET_STS_ERROR;
13078: -- Verify FK value from FV_TP_TREASURY_SYMBOLS table
13079: ELSIF p_cust_account_rec.trading_partner_agency_id IS NOT NULL AND p_cust_account_rec.trading_partner_agency_id <> FND_API.G_MISS_CHAR THEN
13080: BEGIN
13081: SELECT 'Y'
13075: FND_MESSAGE.SET_NAME('AR', 'HZ_TPAI_IS_NULL');
13076: FND_MSG_PUB.ADD;
13077: x_return_status := FND_API.G_RET_STS_ERROR;
13078: -- Verify FK value from FV_TP_TREASURY_SYMBOLS table
13079: ELSIF p_cust_account_rec.trading_partner_agency_id IS NOT NULL AND p_cust_account_rec.trading_partner_agency_id <> FND_API.G_MISS_CHAR THEN
13080: BEGIN
13081: SELECT 'Y'
13082: into l_temp
13083: FROM fv_tp_treasury_symbols
13090: FND_MESSAGE.SET_TOKEN( 'FK', 'Trading Partner Agency Id' );
13091: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'agency_id' );
13092: FND_MESSAGE.SET_TOKEN( 'TABLE', 'FV_TP_TREASURY_SYMBOLS');
13093: FND_MSG_PUB.ADD;
13094: x_return_status := FND_API.G_RET_STS_ERROR;
13095: END;
13096:
13097: END IF;
13098:
13097: END IF;
13098:
13099: -- DUNS Extension Validation
13100: IF p_cust_account_rec.duns_extension IS NOT NULL AND
13101: p_cust_account_rec.duns_extension <> FND_API.G_MISS_CHAR AND
13102: ( p_create_update_flag = 'C' OR
13103: ( p_create_update_flag = 'U' AND
13104: p_cust_account_rec.duns_extension <> NVL( l_duns_extension, FND_API.G_MISS_CHAR ) ) )
13105: THEN
13100: IF p_cust_account_rec.duns_extension IS NOT NULL AND
13101: p_cust_account_rec.duns_extension <> FND_API.G_MISS_CHAR AND
13102: ( p_create_update_flag = 'C' OR
13103: ( p_create_update_flag = 'U' AND
13104: p_cust_account_rec.duns_extension <> NVL( l_duns_extension, FND_API.G_MISS_CHAR ) ) )
13105: THEN
13106: IF TRANSLATE(p_cust_account_rec.duns_extension, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') <> 'XXXX'
13107: THEN
13108: FND_MESSAGE.SET_NAME('AR', 'HZ_DUNS_EXTENSION_IS_INVALID');
13106: IF TRANSLATE(p_cust_account_rec.duns_extension, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') <> 'XXXX'
13107: THEN
13108: FND_MESSAGE.SET_NAME('AR', 'HZ_DUNS_EXTENSION_IS_INVALID');
13109: FND_MSG_PUB.ADD;
13110: x_return_status := FND_API.G_RET_STS_ERROR;
13111: END IF;
13112:
13113: END IF;
13114:
13113: END IF;
13114:
13115: -- Advance Payment Validation
13116: IF p_cust_account_rec.advance_payment_indicator IS NOT NULL AND
13117: p_cust_account_rec.advance_payment_indicator <> FND_API.G_MISS_CHAR AND
13118: ( p_create_update_flag = 'C' OR
13119: ( p_create_update_flag = 'U' AND
13120: p_cust_account_rec.advance_payment_indicator <> NVL( l_advance_payment_indicator, FND_API.G_MISS_CHAR ) ) )
13121: THEN
13116: IF p_cust_account_rec.advance_payment_indicator IS NOT NULL AND
13117: p_cust_account_rec.advance_payment_indicator <> FND_API.G_MISS_CHAR AND
13118: ( p_create_update_flag = 'C' OR
13119: ( p_create_update_flag = 'U' AND
13120: p_cust_account_rec.advance_payment_indicator <> NVL( l_advance_payment_indicator, FND_API.G_MISS_CHAR ) ) )
13121: THEN
13122: validate_lookup (
13123: p_column => 'advance_payment_indicator',
13124: p_lookup_table => 'fv_lookup_codes',